Data Practitioner Advanced Practice Exam: Hard Questions 2025

You ingest event data from multiple product teams into BigQuery. Some teams send timestamps as ISO-8601 strings with time zones, others send Unix seconds, and others send local-time strings without offsets. Analysts are getting inconsistent daily aggregates across regions. You must standardize time handling with minimal downstream query changes and ensure late-arriving events are attributed to the correct event day (not ingestion day). What is the best approach?

A data lake on Cloud Storage holds JSON logs from many services. The schema drifts frequently: new fields appear, some fields change type (e.g., numeric IDs become strings), and nested structures evolve. You need a robust approach to analyze logs in BigQuery without breaking existing queries, while still allowing access to newly added fields. What design best meets these requirements?

You are building a streaming pipeline that reads from Pub/Sub and writes into BigQuery. A downstream financial reconciliation requires that each message is written exactly once per unique event_id, even during Dataflow worker restarts and Pub/Sub redeliveries. Events can arrive out of order and late. Which design best satisfies the requirement?

A team runs daily Dataflow batch jobs that read 50 TB from Cloud Storage and write curated Parquet back to Cloud Storage. The job intermittently fails with 'out of memory' errors and severe skew: a small number of workers run much longer than others. The input files are partitioned by date but contain highly uneven key distributions. What is the best remediation strategy?

You have a BigQuery dataset used by analysts and an ML feature store table refreshed hourly. Analysts often run exploratory queries that scan large ranges and occasionally saturate slots, delaying the feature refresh SLA. You need to protect the hourly refresh while still allowing exploratory analysis, without duplicating data. What should you do?

A dataset in BigQuery must be shared with a partner. The partner should see only rows where region = 'EU' and only a subset of columns. You must ensure the partner cannot bypass the restriction by querying the base table or using other tables in the dataset. What is the most secure approach?

Your organization stores PII in BigQuery and must ensure that only specific roles can view raw identifiers (email, phone). Analysts should be able to join on these identifiers for deduplication but must not see the plaintext values. The solution should be centralized and reusable across many tables and projects. What should you implement?

A BigQuery query that calculates 7-day rolling active users over billions of events is slow and expensive. The current approach uses a self-join of the events table to generate date windows. Data is partitioned by event_date and clustered by user_id. You need to improve performance while keeping results exact. What is the best query strategy?

A product team built a dashboard on BigQuery showing near-real-time conversion rate. They notice sudden spikes and drops that later 'correct' themselves within an hour. Investigation shows events can arrive up to 45 minutes late and the dashboard uses the last 15 minutes of event time. You need to make the metric stable without hiding real changes. What is the best solution?

A regulated customer requires an auditable lineage of how a BigQuery table was produced, including source objects in Cloud Storage, transformation logic, and who triggered changes. You already use Dataflow and scheduled queries, and multiple teams deploy pipelines via CI/CD. You need a solution that provides searchable lineage and integrates with access controls. What should you implement?