Medium PDE practice questions

Your company publishes a curated BigQuery dataset as an Analytics Hub listing so that external partner organizations can subscribe to it. Leadership wants to know which partners are actually querying the shared data and how often, so they can justify continued investment and identify inactive subscribers. What is the most appropriate way to obtain this usage information?

A retail analytics company wants to monetize a curated BigQuery dataset by sharing it with dozens of external partner organizations. Requirements: partners must query the data from their own projects (paying for their own compute), the provider must be able to revoke access and publish updates centrally without copying data to each partner, and the provider must track which organizations have subscribed. Which approach best meets these requirements?

A retail company is migrating a nightly on-premises ETL job to Google Cloud. During the migration cutover period, both the legacy on-premises pipeline and the new Dataflow pipeline will run in parallel against the same source data for two weeks to build confidence before decommissioning the old system. The data engineering team must prove that the new pipeline produces identical aggregated results to the legacy system before switching over. Which approach best validates output fidelity during this parallel-run period?

Your finance team uses a Looker dashboard backed by BigQuery that runs the same set of aggregation queries against a 400 GB partitioned sales table hundreds of times each morning. Executives complain that each dashboard tile takes 6-9 seconds to load, and on-demand query costs are climbing. You need sub-second dashboard response times with minimal changes to the existing SQL and no separate infrastructure to manage. What should you do?

A retail analytics team stores a large customer transactions table in a BigQuery dataset called 'raw_finance'. A separate marketing team needs read access only to aggregated, non-PII summary data derived from that table. The finance team must retain full control of the underlying dataset and must not grant the marketing team any direct access to 'raw_finance'. What is the most appropriate way to design this data-sharing solution?

A data engineering team runs hundreds of ETL queries every night against an on-demand BigQuery project. Occasionally, queries fail immediately with a 'resources exceeded' or concurrency-related error because too many interactive queries are submitted at once and hit the per-project concurrent query limit. These jobs are not time-sensitive as long as they complete before the morning business reports run. Which change should the team make to improve reliability without moving to a flat-rate reservation?

A retail analytics team has a 12 TB BigQuery table already partitioned by DATE(order_timestamp). Most dashboard queries filter on a specific date range AND a specific customer_id (there are roughly 8 million distinct customer IDs). Currently these queries scan the entire day's partition even when only one customer is needed, driving up on-demand costs. Without changing the partitioning strategy, what is the most effective way to reduce bytes scanned for these filtered queries?

A retail analytics team is designing a BigQuery table to store customer orders. Each order has one customer and multiple line items (product, quantity, price). Analysts run frequent aggregations across orders and line items, and query cost and performance are top priorities. The team is deciding how to model the relationship between orders and their line items in BigQuery. What is the recommended schema design?

A financial services company stores critical transactional data in a BigQuery dataset located in the us-central1 region. Compliance requires that the company be able to recover the dataset with a Recovery Point Objective (RPO) of no more than 4 hours if the entire region becomes unavailable. The team wants a solution that is automated, cost-effective, and requires minimal ongoing operational overhead. Which approach should they implement?

Your team runs BigQuery in an Enterprise edition reservation for a project that executes heavy ETL only between 01:00 and 05:00 UTC each night. Outside that window, the project runs almost no queries. Finance wants to eliminate the cost of idle capacity during the 20 idle hours per day while still guaranteeing fast throughput during the nightly batch. What is the most cost-effective reservation configuration?

A retail analytics team lands raw daily CSV and Parquet files in a Cloud Storage data lake. Analysts run ad-hoc exploratory queries only a few times a week over recent files, and the raw files are frequently overwritten by an upstream process. The team wants to minimize storage duplication and avoid an ETL step, while accepting slower query performance for these occasional queries. What is the most appropriate way to make this data queryable in BigQuery?

A retail company runs a mix of workloads in BigQuery. Their nightly ETL jobs consume large, predictable volumes of slots, and their finance team requires stable, predictable monthly billing with no risk of surprise overages. Meanwhile, a small data science team runs occasional ad hoc exploratory queries that are sporadic and low-volume. The data engineering lead must recommend how to organize these workloads to meet the business requirements while minimizing total cost. What should they recommend?

A retail analytics team is designing a BigQuery dataset for daily sales reporting. Analysts frequently query aggregated revenue by product category, region, and date. The team debates whether to keep normalized dimension tables (products, stores) separate from a large fact table or to consolidate the data. Query cost and performance on large scans are the primary concerns, and the dimension tables change infrequently. Which schema approach best fits BigQuery's architecture?

Your company runs on-demand BigQuery for most of its ad hoc analytics, but every month-end there is a 3-day period where the finance team executes very heavy scheduled batch reports, causing costs to spike unpredictably and occasionally hitting on-demand concurrency limits. Outside of month-end, query volume is low and steady. Management wants predictable, minimized cost for the month-end workload without over-committing capacity for the rest of the month. What should you do?

A retail analytics team stores a large fact table in BigQuery keyed by a numeric customer_id (values 1 to roughly 5,000,000). Nearly all analytical queries filter on a specific range of customer_id to process a subset of customers at a time, but the table has no meaningful timestamp column that queries filter on. The team wants to prune data scanned and reduce query cost without changing how analysts write their WHERE clauses. Which BigQuery design should they use?

Your finance team wants a weekly automated report attributing BigQuery on-demand query costs to individual users and teams so they can identify the most expensive recurring queries. You need a low-maintenance approach that does not require enabling additional export pipelines and can be scheduled to run without external tooling. What should you do?

Your organization runs BigQuery workloads for several departments (marketing, finance, and engineering) within a single billing account. Finance leadership wants a repeatable, automated way to break down monthly BigQuery costs by department for chargeback, without creating separate projects for each team. What is the most effective approach?

A retail analytics team runs a dashboard that refreshes every few minutes. The underlying query aggregates daily sales totals by store and product category from a large, continuously appended BigQuery fact table (billions of rows). The same aggregation is executed hundreds of times per hour, and the team is concerned about repeatedly scanning the full table and the associated query cost. They want the aggregated results to stay automatically up to date as new rows arrive, with minimal maintenance overhead. What should they do?

Your data engineering team runs an on-demand BigQuery project where analysts frequently write ad hoc queries. Last month a single mistyped query scanned 45 TB and caused an unexpected cost spike. Leadership wants a repeatable, automated guardrail that prevents any individual query from processing more than a set data volume, and rejects the query before it runs if it would exceed that limit. What should you implement?

A retail analytics team uses BigQuery ML to forecast weekly sales. Business stakeholders want to understand how much of each forecast is driven by trend, weekly seasonality, and holiday effects so they can validate the model with domain knowledge. The team already has a trained ARIMA_PLUS model. Which approach lets them retrieve the decomposed forecast components alongside the point forecasts with the least effort?

A retail analytics team stores three years of daily sales records in BigQuery, including columns for date, store_id, product_category, and units_sold. They want to produce a 90-day forecast of units_sold per store and product category directly in BigQuery, without exporting data to another platform. The analysts are SQL-proficient but have no experience managing ML infrastructure. Which approach best meets these requirements?

A data scientist is building a logistic regression model in BigQuery ML to predict customer churn. The training table includes a numeric 'monthly_spend' column with a wide range of values and a 'subscription_tier' column containing text values like 'Basic', 'Pro', and 'Enterprise'. They want the same preprocessing (standardizing the numeric column and one-hot encoding the categorical column) to be automatically applied during both training and prediction without maintaining separate SQL logic. What is the most appropriate approach?

A data science team trained a boosted tree classifier using BigQuery ML to predict customer churn. Batch scoring in BigQuery works well for their nightly reports, but the product team now needs sub-100ms online predictions served from a customer-facing web application. What is the most appropriate way to meet this low-latency serving requirement while reusing the already-trained model?

A data engineer is building a BigQuery ML logistic regression model to predict ride-cancellation likelihood. Analysts note that cancellation behavior depends heavily on the *combination* of pickup_city and time_of_day (e.g., 'downtown at rush hour' behaves very differently from 'suburb at midnight'), rather than either feature alone. The engineer wants the model to learn these interaction effects directly during training without manually pre-joining string values in a staging table. Which approach should the engineer use inside the CREATE MODEL statement?

A retail analytics team uses BigQuery to store transaction data. A data scientist wants to build a churn prediction model using Vertex AI's custom training with scikit-learn, but the raw features live in BigQuery. The team needs to generate a training dataset where numeric columns are standardized and categorical columns are one-hot encoded, and they want this preprocessing logic to be reusable and version-controlled outside of BigQuery ML. What is the most appropriate approach to prepare this data for AI/ML?