Free Professional Data Engineer practice test: 30 questions with answers

Pub/Sub delivers at least once, so the pipeline must tolerate repeats, and deduplicating on a business key such as the transaction ID is the reliable pattern. Exactly-once delivery is a per-subscription option with conditions and does not cover every upstream retry. BigQuery insertId deduplication is best effort within a short window, so it cannot be relied on as a permanent guarantee.

Policy tags attach fine-grained access control to specific columns, so revoking access is a policy change rather than a data copy. Maintaining redacted copies duplicates data and drifts over time. Encryption does not stop users who can read the column from reading the ciphertext through BigQuery, and KMS keys are not a column access control on their own.

Spanner provides relational semantics, strong consistency and synchronous replication across regions with automatic failover. A cross-region replica in Cloud SQL is asynchronous and requires manual promotion. Bigtable has no SQL transactions across rows, and BigQuery is not an OLTP store.

Dataproc runs Hadoop and Spark with few code changes, and Cloud Storage replaces HDFS as the data store, which fits a first phase. Rewriting everything before moving data delays value. Bigtable does not run Hive SQL, and a streaming API is a poor route for 2 PB of historical data.

Keeping the pipeline components in the same region removes the cross-region egress charges and most of the added latency. More workers do not reduce network distance, and compression helps only partly. Replicating the topic adds copies without fixing the placement of the consumer and sink.

Dataflow tracks progress and Pub/Sub messages are acknowledged only after the pipeline has taken responsibility for them, so a failed worker's work is retried rather than lost. Local disk on a worker is lost with the worker. A maximum ack deadline delays redelivery rather than preventing loss, and one worker is a throughput risk, not a loss control.

Bigtable handles very high write throughput with low-latency key-based reads and range scans, and a reversed or hashed prefix spreads writes so they do not hotspot. BigQuery is an analytical store and is not built for millisecond point reads. A timestamp-first key in Spanner would concentrate writes on one split.

Partitioning on the date column the queries filter on prunes old partitions, and clustering by customer_id sorts blocks within each partition for the second filter. Partitioning by ingestion time does not match the business date, so filters on order_date still scan extra data. Date-suffixed sharded tables are the legacy pattern that partitioned tables replace.

BigLake tables expose files in Cloud Storage to BigQuery with a connection and support fine-grained controls such as row and column security, so governance stays consistent with the warehouse. Plain external tables without a connection cannot apply those controls the same way. Per-team buckets multiply copies, and deleting the lake loses the open-format storage the team wants to keep.

A dataset location fixes where the data resides, and a dataset default KMS key covers new tables and gives customer-managed encryption. Replicating from us-central1 moves data out of the EU. IAM conditions control who queries, not where data sits, and external tables do not use the customer's key by default.

A prefix that spreads writes across the key space avoids hotspots, and a timestamp suffix supports range scans per user. Bigtable column families are a schema-level concept, so creating one per user is not workable, and keeping them few is the recommended practice. Sequential prefixes create hotspots, and unbounded cells in one row degrade performance.

Date partitioning keeps the 30-day queries cheap, and a seven-year partition expiration meets retention while BigQuery's long-term storage pricing applies to partitions not modified for 90 days. A 30-day expiration would delete data the regulator requires. Manual DML deletes are costly and error-prone, and the Cloud SQL archive adds a second system to manage.

Streaming Dataflow jobs can be updated in place when the change is compatible, which preserves the job and its state. Cancelling loses in-flight work, and draining is for stopping a job cleanly rather than preserving the same job through a compatible update. Running two jobs writing to one table risks duplicates and inconsistent output.

A Dataproc Workflow Template defines the steps, their order and an ephemeral managed cluster that is removed after the job, which cuts idle cost and keeps the dependency. A permanent cluster wastes money between runs. Scheduling both steps at the same time breaks the required order, and Cloud Functions cannot host a Spark job.

Cloud Monitoring alerting policies evaluate Dataflow metrics such as system lag and notify channels in real time. A morning review of logs is too slow for a lag alert. Polling with a scheduler writes a file that nobody watches, and Audit Logs record access events rather than pipeline health.

Dataform assertions run as part of the workflow and can stop downstream steps when a check fails, which prevents publishing bad totals. A scheduled query without checks overwrites production with whatever it produces. A dashboard icon and morning spot checks both react after the bad data is already visible.

Partitioning and clustering reduce bytes scanned, column lists avoid scanning unused columns, and bytes-billed limits stop runaway queries from costing more than expected. Capacity pricing changes the billing model and is not a usage-reducing action on its own in this scenario, and exporting nightly does not reduce the cost of the analysis queries.

A skewed key overloads the single worker that owns it, so pre-aggregating with a combiner and splitting the hot key into sub-keys spreads the work. Adding workers does not divide one key across machines. Turning off autoscaling does not change the skew, and retrying indefinitely wastes resources while hiding the cause.

Event-time windows assign each event to the hour the user acted, and allowed lateness keeps late events that arrive within the 10-minute delay. Processing-time windows would put late events into the wrong hour, and publish time is not the user action time. Session windows answer a different question from hourly counts.

Storage Transfer Service runs managed, resumable transfers from POSIX sources through an on-premises agent and can be scheduled inside the off-peak window to protect the shared link. A single laptop over VPN is a bottleneck with no resume logic. Writing through a FUSE mount during business hours competes with production traffic and gives no validation.

A periodically refreshed side input lets the pipeline join small, slowly changing reference data in memory with low latency. Per-ping queries without pooling overload the database under streaming volume. A nightly batch join fails the latency requirement, and per-message writes through Cloud Run create connection pressure on Cloud SQL.

Load jobs tolerate a configured number of bad records, and a dead-letter step keeps rejected rows for review, which satisfies both goals. Forcing every column to STRING lets malformed rows through as bad data. Federated queries do not solve ingestion, and failing on the first bad row is the opposite of the requirement.

Datastream reads change streams from supported databases and writes them to BigQuery or Cloud Storage with low latency. Storage Transfer Service moves files, not database changes. Hourly full selects are the expensive pattern the team wants to avoid, and a custom binary-log reader adds maintenance work that a managed service removes.

Event-time windowing with appropriate allowed lateness handles out-of-order arrival correctly, and deduplication on a stable event ID removes repeated deliveries. Batch mode is a different workload, extending the ack deadline does not address duplicates, and more threads do not restore order.

Dataform compiles SQL transformation workflows for BigQuery with dependency graphs, assertions and Git integration. Cloud Data Fusion is an ETL integration service aimed at pipeline building rather than SQL transformation projects. Looker Studio is a visualization tool, and Datastream replicates change data from databases.

BigQuery ML trains models with SQL inside BigQuery, so the data never leaves the warehouse. Exporting to Cloud Storage for a custom training job adds steps and infrastructure. A Spark cluster also needs data movement and cluster management, and an image API does not fit tabular data.

Looker's access controls and governed models can restrict data by group, and an aggregated view keeps names out of the path entirely. Hiding a chart column in Looker Studio does not protect the underlying data, which a viewer can still reach through the report. Public exports and shared spreadsheets expose the full data.

A materialized view stores the precomputed aggregation and BigQuery applies incremental refresh, and the optimizer can rewrite matching queries to use it. A logical view still computes the aggregation each time. Nightly full rebuilds are stale for a few-minute change rate, and CSV exports make queries slower.

BigQuery ML trains models inside the warehouse, notebooks can read BigQuery directly through supported APIs, and curated tables published by Dataform or scheduled SQL give teams one governed source. Exporting to laptops and public buckets breaks governance and copies sensitive data.

Matching data types on both sides lets the join use the native key without converting every row and lets the optimizer plan it efficiently. UNION ALL does not perform a join at all, and a LIMIT on each side changes the result set. Loading into a notebook moves the work off the warehouse and leaves the mismatch in place.