50 Data Practitioner Practice Questions: Question Bank 2025

You need to store and query semi-structured event data (variable fields per event) coming from a web app. Analysts want to run SQL queries with minimal data modeling effort. Which storage option is the best fit?

A team needs to ingest streaming IoT sensor readings, perform simple filtering and enrichment, and write results to BigQuery with near real-time availability for dashboards. Which approach is most appropriate?

A dataset in BigQuery contains a column with sensitive identifiers (e.g., national ID). Analysts must be able to join on the identifier but must not see the raw values. What should you implement?

You are building a Looker Studio dashboard on top of BigQuery. The dashboard should show one row per customer, with total spend across all orders. Which BigQuery construct is most appropriate to create the summarized dataset for the dashboard?

A Dataflow pipeline is reading from Pub/Sub and writing to BigQuery. The pipeline starts failing with errors indicating BigQuery insert failures due to missing required fields. What is the most likely cause and best next step?

Your organization uses a BigQuery dataset for multiple teams. You want analysts to query only a subset of columns containing non-sensitive data, without duplicating tables. What should you use?

A product manager asks for a weekly report showing the percent change in revenue week-over-week and highlighting unusual spikes. Which BigQuery approach best supports this analysis?

You have CSV files landing in Cloud Storage every hour. The schema sometimes adds new optional columns. You need a repeatable pipeline that loads into BigQuery and tolerates schema evolution with minimal manual work. What is the recommended design?

A compliance policy requires that only a small security team can decrypt sensitive fields in BigQuery, while analysts can query aggregated results. You also need centralized key control and auditing. Which solution best meets these requirements?

You are designing an analytics platform. Raw events must be retained immutably for reprocessing, while curated tables in BigQuery should be optimized for fast queries. You also need to re-run transformations if business logic changes. Which architecture is most appropriate?

Your team stores CSV extracts in Cloud Storage and wants to load them into BigQuery. Some CSV files contain a few extra columns over time. You want the load to succeed without manual intervention while keeping a stable schema for downstream dashboards. What is the best approach?

A healthcare analytics team needs to restrict who can view patient identifiers in BigQuery, while still allowing analysts to query aggregate metrics on the full dataset. What should you implement?

You are designing a data pipeline that ingests events from Pub/Sub into BigQuery for near-real-time reporting. The product team needs the freshest possible data with minimal operational overhead. Which approach best meets the requirement?

A data analyst created a BigQuery view that joins two tables, but users report the query is slow and scans a large amount of data. The tables are partitioned by event_date, but the view does not filter by date. What is the best recommendation to reduce scanned data for common dashboard queries?

You need to store raw clickstream events where each event includes common fields (timestamp, user_id) and a varying set of attributes depending on event type. Analysts need to query both common fields and some nested attributes. Which BigQuery schema approach is most appropriate?

A team wants to allow business users to explore curated datasets and understand field meanings, owners, and data sensitivity classifications. They also want to search for datasets across projects. Which Google Cloud capability best addresses this need?

You are using BigQuery to analyze IoT data. Queries often filter by device_id and time range. The table is already partitioned by event_date. To further improve performance for device-specific queries, what should you do?

A company must ensure that data processed in BigQuery is encrypted with customer-managed encryption keys (CMEK) and that key usage is auditable. What should they do?

A Dataflow streaming pipeline writes to BigQuery. You notice occasional duplicate rows when Pub/Sub delivers messages more than once. The business requires exactly-once results in BigQuery. What is the best solution?

Your organization wants to share a BigQuery dataset with an external partner. The partner should only be able to query a subset of rows (for example, only their own customer records) and must not be able to infer or access other rows. What is the most secure approach?

A retail analyst receives a CSV extract where a column named "store_id" sometimes contains values like "0012" and other times like "12". They need to preserve leading zeros for downstream reporting in BigQuery. What is the best approach?

You need a serverless way to run an ad-hoc SQL query over files stored in Cloud Storage (CSV/Parquet) without first loading them into tables. Which Google Cloud feature should you use?

A team wants to share a BigQuery dataset with another team in the same organization so they can run queries, but they must not be able to modify tables or delete data. What is the simplest IAM role to grant on the dataset?

A data engineer is using Cloud Data Fusion to ingest from Cloud Storage to BigQuery. Some pipeline runs fail due to intermittent parsing errors when a few rows contain unexpected delimiters. The team wants the pipeline to continue processing while capturing bad records for later review. What should they do?

A marketing team built a BigQuery report that joins a fact table to a dimension table. Query latency has increased as data grew. The dimension table is small (tens of MB) and frequently joined. What is the best optimization in BigQuery?

A pipeline writes daily files to Cloud Storage. The analytics team wants BigQuery queries to automatically include the newest files without updating table definitions each day. What design should they use?

A data analyst created a BigQuery view that contains sensitive columns (e.g., email). They need to share the view with a partner team, but the partner team must not be able to query the underlying base tables directly. What is the recommended approach?

You manage a BigQuery dataset that contains regulated data. Security requires that only users from a specific group can decrypt and view certain columns, even if others can query the table. What should you implement?

A company receives high-volume clickstream events and must run near-real-time aggregations and anomaly detection. They want minimal operations overhead and need to handle out-of-order events with event-time windowing. Which solution fits best?

A data science team wants to train a model using BigQuery ML, but they discovered that their training data includes a label that is derived from future information (data leakage), causing unrealistically high accuracy. Which change best addresses this issue?

You receive a dataset of customer records where a field called "preferences" sometimes contains multiple values like "email|sms|push" and sometimes is empty. You want to analyze opt-in rates by channel in BigQuery. What is the best way to model this field in BigQuery?

A small team wants to quickly explore a CSV file in Cloud Storage, run SQL queries, and share interactive results with non-technical stakeholders. They do not need to build a full data pipeline. Which Google Cloud tool is the best fit?

You are using BigQuery and notice that a dashboard query is slow because it scans a very large table for only the last 7 days of data. The table has a timestamp column named event_time. What is the recommended design improvement?

A data analyst needs to ensure that a dataset used for monthly reporting does not change after the report is finalized. What is the simplest BigQuery approach to preserve a point-in-time copy?

Your company ingests clickstream events to Pub/Sub. Some subscribers occasionally fall behind, but you must keep the pipeline running and avoid message loss while allowing late processing. Which Pub/Sub behavior best supports this requirement?

A team wants to run federated queries from BigQuery over data stored in Cloud Storage in open formats (such as Parquet) without loading it into BigQuery first. What should they use?

A marketing team wants to understand which acquisition channel drives the highest 30-day conversion rate. They have a table with user_id, acquisition_channel, signup_date, and conversion_date (nullable). Which approach provides the most appropriate metric?

You created a BigQuery view that joins a sensitive table containing PII with a non-sensitive table. You granted analysts access to the view, but they are still able to query the underlying PII table directly. What is the best practice to prevent direct access while still allowing the view?

A regulated organization must ensure that BigQuery datasets containing personal data cannot be exfiltrated by writing query results to unauthorized external destinations. They want a centrally managed control. What should they implement?

A dataset contains a column "amount" that sometimes includes non-numeric values like "N/A" and empty strings. A Dataflow pipeline fails when parsing the value as a number. What is the best troubleshooting/fix approach?

You are asked to choose a storage format for files that will be queried frequently in BigQuery from Cloud Storage. The dataset is large, columns are often filtered, and storage cost and query performance matter. Which format is the best choice?

A team wants to allow analysts to explore data in BigQuery but must prevent them from seeing rows that contain PII (such as full email addresses). The analysts still need to see aggregate results across the whole dataset. What is the best BigQuery feature to use?

You load a CSV into BigQuery and notice that some values like "00123" become "123" in query results. The leading zeros must be preserved. What should you do?

A data engineer created a BigQuery table by loading JSON files. Queries fail with errors about inconsistent types in a field (sometimes a number, sometimes a string). What is the most reliable way to prevent this in future ingestions?

Your organization wants a governed way to discover datasets across projects, see technical metadata (schemas, owners), and search for data assets. Which Google Cloud service best fits this requirement?

A pipeline ingests events via Pub/Sub and writes to BigQuery. During a traffic spike, you see duplicates in the BigQuery table. The publisher might retry messages. What is the best approach to minimize duplicates while keeping the pipeline near real-time?

Analysts complain that a BigQuery query is slow and expensive because it scans the entire fact table even when filtering by date. The table has a column event_date that is used in most filters. What is the recommended table design improvement?

A team needs to share a BigQuery dataset with an external partner. The partner should only be able to run queries, not modify tables or create new ones. What is the least-privilege IAM role to grant at the dataset level?

You need to build a repeatable transformation workflow that runs SQL in BigQuery, manages dependencies between steps, supports version control, and is easy for analysts to maintain. Which tool is the best fit?

A regulated workload uses BigQuery and Cloud Storage. Security requires that data at rest is encrypted with keys fully controlled by the organization, including key rotation and the ability to disable access by disabling the key. What is the best solution?