Google Associate Data Practitioner

Incorrect. CMEK lets you control encryption keys via Cloud KMS and can add controls (e.g., key rotation, disabling keys), but it does not by itself restrict which principals can query BigQuery tables. IAM permissions still determine who can read/query the dataset. CMEK is a defense-in-depth measure, not an access control substitute.

Incorrect for this exam scenario. BigQuery supports SQL GRANT/REVOKE for certain fine-grained permissions, but IAM is the standard, primary mechanism for controlling dataset/table access in BigQuery and is what the exam typically targets. Also, regardless of GRANT, users still need permission to create query jobs to run SELECT statements.

Incorrect. Exporting sensitive transaction tables to Cloud Storage introduces data duplication and governance risk (data sprawl), adds operational overhead, and is not necessary to meet the requirement. Signed URLs control object access, but they bypass BigQuery’s centralized access model and auditing for query activity, and do not align with least-privilege BigQuery querying.

Cloud Run jobs can be scheduled (often via Cloud Scheduler) and monitored, but this is not an open-source orchestration solution and does not inherently provide Airflow-style task-level run history, dependency management, and retry visibility across multiple steps. It also typically requires containerizing the script and ensuring access to the NFS data source, which may introduce additional rework and networking complexity.

Dataflow is a managed service for Apache Beam pipelines and is ideal for scalable, parallel ETL. However, it generally requires rewriting the existing Bash/Python ETL into a Beam pipeline (or at least significant refactoring). While Dataflow provides job monitoring, it does not match the requirement to avoid rewriting the ETL code and to use open-source orchestration tooling with DAG/task-level retry visibility.

Dataproc can execute scripts on managed Hadoop/Spark clusters and can be triggered by Cloud Scheduler, but it is not primarily an orchestration platform. You would still lack a unified DAG view with task-level logs and retries unless you add another orchestrator. Additionally, Dataproc introduces cluster management considerations (startup time, costs, autoscaling, ephemeral clusters) that are unnecessary for a simple daily script.

Cloud Run can receive Pub/Sub messages and execute custom transformation logic, but this approach requires writing and maintaining application code rather than using a managed data processing pipeline. At 120,000 events per minute, you would need to carefully manage concurrency, retries, idempotency, batching, and BigQuery write behavior to avoid operational issues and inconsistent throughput. It also does not satisfy the stated preference for a visual, low-code approach, because the transformation logic lives in custom service code. While technically feasible, it is less aligned with the requirements than a managed streaming ETL service like Dataflow.

A BigQuery subscription from Pub/Sub is useful when messages can be written directly into a table with little or no transformation. In this scenario, the events must be cleaned, enriched with a derived region_code, and flattened from nested JSON into a columnar schema before loading, which this direct path does not provide. Because the transformation step is mandatory, direct Pub/Sub-to-BigQuery ingestion is insufficient even if it can meet the latency target. This option is therefore too limited for the required preprocessing.

Writing events to Cloud Storage and then querying them through an external table is fundamentally a batch-oriented analytics pattern rather than a near real-time streaming design. A scheduled daily BigQuery transformation job is far outside the required freshness target of under 90 seconds end to end, so it fails the primary SLA immediately. External tables also leave data in object storage and are not the best fit for continuously refreshed operational dashboards that depend on transformed, query-optimized BigQuery tables. This option is therefore incorrect on both latency and architecture fit.

BigQuery Job User at the project level only allows the analyst to create and run query jobs; it does not grant permission to read any tables or views. Even if the analyst can submit a query, BigQuery will deny access to datasets they are not authorized to read. This option also provides no mechanism to restrict columns to non-PHI fields or to hide the underlying source tables. It is therefore incomplete and does not satisfy the access-control requirement.

Copying approved data into a separate project could isolate PHI from the analyst, but it is not the best answer because it creates unnecessary data duplication and additional pipeline and governance overhead. More importantly, granting BigQuery Data Owner gives the analyst excessive privileges to create, modify, and delete data resources, which violates least-privilege principles. The question asks for controlled query access to sanitized metrics, and a shared view pattern achieves that more cleanly. On certification exams, avoid owner-level roles for read-only analyst scenarios unless explicitly required.

Granting BigQuery Data Viewer at the project level is too broad because it can allow the analyst to read datasets and tables across the project, including those that contain PHI. That directly conflicts with the requirement that they must not access sensitive fields or underlying base tables. It also does nothing to enforce a curated projection of only approved columns and the last 180 days. For sensitive healthcare data, dataset-level sharing of a curated view is the safer and more precise pattern.

Cloud Composer is an orchestration service (managed Airflow), not a streaming processing engine. Pulling Pub/Sub every minute introduces batch latency that violates sub–5-second p95 requirements and risks message backlog at 50,000 msg/s. A custom Python script also lacks built-in event-time windowing, watermark handling, and scalable parallelism. This design is operationally fragile and not aligned with Google’s recommended streaming analytics patterns.

Dataproc with Spark Structured Streaming can process Pub/Sub streams and write to BigQuery, but it requires managing clusters, tuning executors, handling autoscaling policies, and ensuring reliability/latency under bursty loads. For an exam “Google-recommended design” emphasizing easy autoscaling and managed operations, Dataflow is preferred. Dataproc is more appropriate when you need Hadoop/Spark ecosystem compatibility or lift-and-shift workloads.

Cloud Run can scale on Pub/Sub push subscriptions, but implementing 1-minute per-container windowed aggregations with correct event-time semantics, late data handling, and consistent aggregation outputs is complex. You would need external state (e.g., Redis/Firestore/Bigtable) and careful idempotency, increasing operational risk and latency. At 50k msg/s, instance scaling, concurrency tuning, and BigQuery streaming quotas become harder to manage than using Dataflow’s native streaming model.

Creating groups and subfolders is good, but granting only View to each squad’s subfolder does not meet the requirement. With View access, users can open dashboards but cannot move, delete, or generally manage content in that folder. This option would prevent destructive actions everywhere (including their own squad area), so it fails the “only lets each squad move or delete dashboards that belong to their own squad” requirement.

Setting the parent folder to View for All Users is correct, but granting Manage Access/Edit to each individual squad member does not scale for 160 users. It increases administrative overhead and the risk of misconfiguration (someone accidentally gets access to the wrong subfolder or retains access after role changes). The question explicitly asks for an easy-to-manage setup, which strongly favors group-based permissions.

Moving squad dashboards to personal folders breaks the shared reporting model and makes governance harder, not easier. Personal folders are tied to individuals, which complicates ownership, continuity, and discoverability. It also contradicts the requirement that everyone can view everything in Global Reports, since content would be scattered across personal spaces and not centrally managed within the Global Reports shared structure.

Partially reasonable, but unnecessary and potentially harmful. AutoML NLP can learn directly from raw text; aggressive SQL preprocessing may strip emojis, casing, or slang patterns that carry sentiment. It also adds extra engineering steps and risk within a two-week timeline. Use preprocessing only when you have a clear, validated need (e.g., removing boilerplate), not as a default for AutoML.

Incorrect for the constraints. Building a custom TensorFlow sentiment model requires data labeling strategy, model architecture choices, training infrastructure, hyperparameter tuning, and ongoing serving/monitoring. Deploying on Compute Engine increases operational overhead (scaling, patching, reliability) compared to managed Vertex AI endpoints. This is unlikely to be completed robustly in two weeks with minimal MLOps.

Incorrect due to high operational complexity. Dataproc clusters require provisioning, tuning, job orchestration, dependency management (Spark NLP), and ongoing cost control. While Spark can process large text corpora, it’s not the fastest path to a managed sentiment solution. It also shifts you toward custom modeling and pipeline maintenance, conflicting with the “minimal ML operations overhead” requirement.

Not the best choice for this requirement. Colab Enterprise with a custom Python model can forecast, but it introduces extra steps: exporting/reading data, managing training runs, versioning, scheduling, and writing results back to BigQuery. For an exam scenario emphasizing scalable use of historical seasonality and direct BigQuery output, BigQuery ML time series is the simpler, more managed solution.

Incorrect. BigQuery ML linear regression is not inherently a time series forecasting model. It does not automatically capture autocorrelation or seasonal structure unless you manually create lag features, day-of-week indicators, and seasonal terms, then manage feature generation for each district. This is more complex and less robust than ARIMA_PLUS for daily demand forecasting with clear seasonality.

Incorrect. Logistic regression is for binary or multi-class classification (predicting categories/probabilities), not forecasting continuous numeric values like liters_used. Even if you transformed the problem into classes (e.g., high/low demand), it would not meet the requirement to forecast daily demand quantities for capacity planning and would discard important numeric information.

Dataproc + Spark can translate and load data, but Dataproc requires cluster lifecycle management (create/scale/patch) unless using ephemeral clusters, which still adds operational overhead. It is not the best match for “fully serverless” and “minimal maintenance.” Also, spinning clusters for only ~5 files/day is inefficient and can increase cost and complexity compared to Dataflow.

BigQuery ML is not designed to train a high-quality multilingual translation model from scratch using viewer comments, and it does not replace the managed Cloud Translation API. Training and maintaining a translation model would be complex, data-hungry, and unlikely to meet accuracy requirements. This also doesn’t address event-driven processing; it shifts complexity into model training and SQL workflows.

BigQuery remote functions can call external APIs, but translating row-by-row via scheduled queries is operationally and cost-wise suboptimal and can be slower/unpredictable at scale. Scheduled queries every 15 minutes introduce latency and do not guarantee completion within 30 minutes of file arrival under load. It also increases coupling between ingestion and transformation and can hit API quota limits from BigQuery execution patterns.

Dataproc (Hadoop/Spark) is overkill for a 180 MB daily CSV and simple filter/aggregate logic. You must provision and manage a cluster (or at least ephemeral clusters), handle job submission, and pay for compute resources while the cluster runs. Dataproc is best for existing Spark/Hadoop workloads, complex distributed processing, or when you need specific open-source ecosystem tools—not for simple daily ELT into BigQuery.

Cloud Data Fusion provides a visual ETL interface and many connectors, but it has higher operational overhead and baseline cost (instance-based pricing) compared to simply using BigQuery SQL. For a single small CSV and basic transformations, Data Fusion’s pipeline design, runtime environment, and management are unnecessary. It’s more appropriate when you need many sources, complex ETL patterns, governance, or a low-code approach at larger scale.

Dataflow (Apache Beam) is excellent for scalable batch/stream pipelines, windowing, and complex transformations, but it introduces more development and operational complexity than needed here. You must build and maintain a Beam pipeline, manage templates, and pay for worker resources during execution. For a small daily CSV with simple filtering and aggregation, BigQuery SQL is simpler, cheaper, and easier to operate.

Compute Engine scripts increase operational overhead (VM management, scaling, patching, monitoring) and make it harder to reliably meet p99 latency under bursts. Implementing correct windowed deduplication and fault-tolerant processing (checkpointing, replay handling, exactly-once semantics) becomes complex. You would also need to design your own autoscaling and backpressure strategy, which is risky for a fraud pipeline with strict latency requirements.

Cloud Run triggered by Cloud Storage is a file-based, batch-oriented pattern and does not match a continuous Pub/Sub event stream. You would need an intermediate step to land events into files, adding buffering delay and likely violating the 4-second p99 latency requirement. While Cloud Run can autoscale, it is not designed for stateful, windowed deduplication across a 10-minute horizon without external state stores and additional complexity.

Streaming raw events into BigQuery and cleaning later with scheduled queries fails the latency requirement because scheduled queries run on intervals and are not designed for sub-second to few-second end-to-end processing. It also allows invalid records and unmasked PII to land in BigQuery, creating compliance and governance risk. Deduplication in SQL after ingestion is possible, but it is reactive, can be costly at scale, and doesn’t prevent downstream consumers from seeing duplicates.

Lifecycle transitions are correct for cost optimization, but Object Versioning does not satisfy immutability by itself. Versioning keeps prior versions when objects are overwritten or deleted, yet an authorized user can still delete versions (or delete the live object and versions) unless retention policies/holds are applied. This option also doesn’t address the explicit 7-year evidence immutability requirement with the proper compliance control.

Moving objects to different storage classes based on age/access patterns is directionally correct, but using Cloud KMS with CMEK does not enforce immutability or retention. CMEK only manages encryption keys; it cannot prevent deletion or modification of objects. This is a common confusion between encryption/compliance and WORM retention controls. It also doesn’t provide the managed automation mechanism (lifecycle rules) explicitly.

A Cloud Run function that inspects metadata and moves objects daily is custom orchestration and adds operational overhead, which contradicts the requirement for a managed, low-overhead approach. While object holds are relevant for preventing deletion, you don’t need Cloud Run to implement storage class transitions because Cloud Storage Lifecycle Management can do this natively and more reliably at scale.

Incorrect. Table-level expiration deletes the entire table after seven years. For a system that continuously ingests meter readings, this would eventually remove all historical and current data at once, breaking billing/analytics workflows. It does not implement a rolling retention window; it’s meant for temporary or short-lived tables, not regulated long-term datasets.

Incorrect. Dataset-level default table expiration applies a TTL to newly created tables in the dataset, deleting whole tables after seven years. This is risky because it can unintentionally delete important tables and still does not provide rolling deletion of old data within a table. It’s best for controlling sprawl of temporary tables, not compliance retention for time-series data.

Incorrect for “low operations overhead” and primary retention in BigQuery. Exporting daily to Cloud Storage plus lifecycle/retention rules adds pipeline complexity, monitoring, and potential rehydration steps for audits/analytics. While Cloud Storage retention policies can support compliance, it shifts the system toward archival storage and complicates querying compared to using BigQuery partition expiration directly.

Cloud Composer can orchestrate the Spark job and downstream email delivery, but it is a managed Airflow environment and therefore a separate orchestration platform. That directly conflicts with the requirement to avoid standing up a separate orchestrator and to keep operational overhead low. Composer is more appropriate when you need complex cross-service DAGs, branching, and rich workflow management across many systems.

Cloud Run is useful for custom logic and can call Dataproc APIs or send emails, but by itself it does not provide built-in cron-style scheduling. You would still need Cloud Scheduler or another trigger, and you would need to write code for job submission, monitoring, and failure handling. That is more custom integration work than using a Dataproc workflow template for a Dataproc-centric batch pipeline.

Cloud Scheduler plus Cloud Run can absolutely be used to trigger processing and send the email, but it requires stitching together multiple services with custom code for job submission, completion tracking, retries, and error handling. That makes it more operationally involved than using a Dataproc workflow template to encapsulate the Dataproc-side processing. It is a valid architecture, but not the easiest or most Dataproc-native choice for a straightforward daily batch report.

Incorrect. Dynamic data masking changes what certain users can see at query time; it is an authorization/presentation control, not irreversible destruction. Revoking the departing employee’s permissions is irrelevant because the employee is not the threat model—compliance requires the organization to make the data unreadable even to authorized internal users. Privileged users could still access unmasked data, and the underlying stored data remains readable.

Incorrect. A single CMEK for the dataset/table encrypts all data with the same key. Deleting that CMEK would make the entire dataset unreadable, affecting access to other employees’ records and violating the requirement to avoid impacting others. CMEK is excellent for customer-controlled encryption and key rotation, but it does not provide per-employee selective crypto-shredding unless you partition data into separate tables/datasets per employee (impractical).

Incorrect. Column-level access controls with policy tags restrict which principals can view sensitive columns, but they do not make the data irreversibly unreadable. Revoking the departing employee’s permissions again misses the requirement: the company must ensure the terminated employee’s personal records cannot be read by anyone after the deadline, while still retaining stored data for audits. Policy tags are for governance and least privilege, not cryptographic erasure.

Incorrect. A scheduled UPDATE that flags rows is not true deletion and adds ongoing maintenance (scheduled jobs) and query complexity (must use a view or add predicates). BigQuery DML on large tables can be costly and may increase storage due to changed blocks. It also doesn’t guarantee storage reduction unless you actually delete data/partitions.

Incorrect. A view that filters out rows older than 180 days only hides data at query time; it does not remove underlying partitions or reduce storage costs. It also relies on users querying the view (not the base table) and still leaves compliance/retention unmet because the data remains stored.

Incorrect. Requiring a partition filter is a cost-control and performance safeguard to prevent accidental full table scans. It does not delete old partitions or enforce a retention policy. It can be a good complementary setting, but by itself it does not meet the requirement to automatically remove data older than 180 days.

Incorrect. BigQuery load jobs plus scheduled queries can implement transformations and joins, but data quality profiling/insights are not inherently built into the ingestion workflow. You would need to author additional SQL checks, store results, and build monitoring yourself. It can work for simple ELT, but it does not best satisfy the requirement for built-in profiling of nulls/outliers/schema anomalies during ingestion within a day.

Incorrect. Loading the CSV into BigQuery first and then using Data Fusion from BigQuery to BigQuery adds an unnecessary staging step and duplicates storage/processing. It also postpones data quality insights until after the initial load, conflicting with “during ingestion.” If Data Fusion is the chosen tool, it should typically ingest directly from Cloud Storage and apply transformations before landing in the curated partitioned table.

Incorrect. Dataflow templates (Cloud Storage CSV to BigQuery) provide scalable ingestion, but they are primarily focused on data movement and basic parsing. Implementing reference data joins and especially built-in profiling/quality insights (null/outlier/schema anomaly reporting) generally requires custom Apache Beam code and additional monitoring/quality frameworks. That increases delivery time and complexity, making it less suitable for the “within one day” and “built-in data quality insights” requirements.

Incorrect. Policy tags in BigQuery (via Data Catalog) provide column-level security: they control who can see a column’s values, not which rows are returned. Tagging dept_code could hide the dept_code column from some users, but it would not automatically filter rows so that advisors only see their department’s records. It also doesn’t implement group-to-department row filtering.

Incorrect. Dynamic data masking changes how sensitive column values are displayed (e.g., nulling, hashing, partial reveal) based on the user, but it does not restrict access to entire rows. Masking dept_code would still allow advisors to see enrollment rows from other departments (just with masked dept_code), which violates the requirement to only see matching rows.

Incorrect. Granting BigQuery Data Viewer on the dataset (or table) provides read access to all rows in the table. This is coarse-grained IAM and does not enforce per-department row filtering. It may seem appealing because it enables querying, but it fails the core security requirement of restricting visibility to only the advisor’s department.

Cloud Data Fusion is a managed ETL/ELT tool best suited for batch pipelines, CDC, and orchestrated transformations, but it is not typically the first choice for strict sub-2-second streaming enrichment and dedup at high event rates. Writing the curated output to Cloud Storage also doesn’t directly satisfy “ad hoc SQL analysis” without an additional query engine (BigQuery external tables, Dataproc, etc.), adding latency and complexity.

ELT to Cloud Storage then Bigtable is a mismatch for the stated goals. Bigtable is a low-latency operational NoSQL database optimized for key/value access patterns, not ad hoc SQL analytics. Also, ELT implies transforming after loading, but the requirement is to deduplicate/validate/enrich with <2s end-to-end latency; doing this after landing raw data in storage typically increases latency and operational complexity.

Cloud SQL is not appropriate for ingesting and processing high-rate IoT event streams with low latency; it can become a bottleneck and is not designed for streaming transformations at this scale. Analytics Hub is a data sharing/exchange service, not a pipeline processing or storage destination for streaming events. This option does not address real-time deduplication/enrichment or the need for an analytical warehouse for ad hoc SQL and ML training.