Data Practitioner Intermediate Practice Exam: Medium Difficulty 2025

A retail company ingests clickstream events into BigQuery. The events include nested arrays (items viewed) and sometimes missing fields (e.g., campaignId). Analysts need consistent queries and predictable schema evolution without breaking downstream dashboards. What is the best approach?

A healthcare analytics team receives two datasets: (1) a daily CSV extract of patient encounters, and (2) a set of free-text physician notes. They need to analyze encounter metrics in SQL and run text search and NLP later, while keeping raw inputs for audit. What storage and data type strategy best meets these needs?

A marketing team stores campaign performance data in BigQuery. They frequently join fact tables with a calendar dimension and compute month-over-month metrics. They notice inconsistent results due to timestamps stored in different time zones. What is the most reliable fix?

A product team needs a pipeline that ingests Pub/Sub messages, validates and enriches them, and writes to BigQuery with low latency. They also want the same codebase to run batch backfills from Cloud Storage. Which Google Cloud tool best fits this requirement?

A data team must orchestrate a daily workflow: load files from Cloud Storage to BigQuery, run a Dataform/SQL transformation step, and then trigger a Data Quality check. They want retry logic, dependencies, and a single pane of glass for monitoring. What should they use?

A team wants to share curated analytics datasets with multiple departments using BigQuery. They need to ensure consumers can query only approved columns and rows (e.g., excluding internal cost fields and limiting some teams to their region). What is the best approach?

An analyst needs to calculate a 7-day rolling average of daily active users (DAU) in BigQuery while ignoring days with missing data. The source table has one row per day with columns (event_date, dau). What is the most appropriate BigQuery approach?

A team built a BigQuery dashboard that became slow after data volume increased. The largest table is partitioned by ingestion time but queries filter on an event_date column and often select only a few columns. What should they do to improve performance and cost while keeping the same analytics capability?

A company wants to detect anomalous spikes in transaction counts per store every hour. They need near-real-time insights and a simple way for analysts to query results with SQL. Which design is most appropriate?

A financial services company stores PII in BigQuery and must ensure only a small security group can view raw identifiers. Most analysts should see masked values but still be able to join datasets consistently. They also need to audit access to sensitive tables. What is the best solution?