Google PDE: BigQuery Analytics and Warehouse Engineering — Study Guide

By Laurynas Brazys · 21 August 2026 · #google #PDE #certification #exam-prep #study-guide

Part of the Google Professional Data Engineer — Study Guide. Practice with verified answers in the Google exam hub, or take timed practice tests on ExamRoll.io.

Overview

BigQuery is a serverless, columnar, MPP analytics warehouse that separates storage from compute, providing near-infinite scale, ANSI SQL, and integrated governance. Warehouse engineering on BigQuery balances schema design (partitioning, clustering, denormalization vs normalization, nested records), ingestion patterns (batch loads, streaming, Storage Write API), and workload management (on-demand vs capacity-based editions and reservations). Robust security (authorized views, row/column policies, policy tags) coexists with cost controls and performance tooling to minimize bytes scanned and reduce latency. This section covers core design, operations, and failure modes you must anticipate in production.

Storage and Semantics: Datasets, Tables, Views, and Lake Access

• Datasets, tables, views:

  • Datasets scope IAM and governance. Keep per-tenant datasets for isolation and billing clarity.
  • Standard tables store data natively; partitions and clustering govern layout and pruning.
  • Views encapsulate SQL logic without storing data. Authorized views let a view owner expose restricted subsets to other projects or tenants while hiding underlying tables.
  • Materialized views (MVs) persist precomputed results and automatically refresh. Query rewrite uses MVs transparently when compatible; incompatible predicates or functions bypass them.
  • External tables reference data in Cloud Storage, Google Drive, or Google Sheets. They avoid ingestion but trade throughput and function support for convenience. For repeated analytics, ingest to native tables.

• Partitioning, clustering, and nested records:

  • Partition by ingestion time, DATE/TIMESTAMP/DATETIME, or integer range to prune scans. Use WHERE filters on the partition column or decorators like _PARTITIONDATE to enable pruning.
  • Cluster on high-cardinality, frequently filtered or joined columns (up to 8). BigQuery reclusters automatically; repeated small DML can temporarily degrade clustering quality.
  • Nested and repeated records (STRUCT, ARRAY) model one-to-many relationships without join overhead. Use UNNEST judiciously; repeated UNNEST on large arrays can fan out dramatically.

• Denormalization vs normalization:

  • Denormalize dimension attributes into fact tables to minimize joins and exploit columnar scans; this is ideal for read-heavy analytics.
  • Normalize when write amplification, update hotspots, or self-joins cause contention or complexity (for example, separating master patient and visit tables to avoid self-join blowups). Consider a hybrid: normalized core entities with wide, denormalized facts or nested children.

• Materialized views: design considerations

  • Best for stable, incremental aggregates over partitioned base tables. MV refresh is asynchronous; downstream users should tolerate staleness windows or query base tables as fallback.
  • Filter and group by the partition column for incremental refresh. Non-deterministic functions, non-supported joins, or UDFs may disqualify MVs from rewrite.

• Federated queries, BigLake, and pushdown:

  • Federated queries read external systems (for example, Cloud SQL) directly with SQL. They are convenient for light joins or one-off exploration but have higher latency and tighter quotas; extract to BigQuery for heavy analytics.
  • BigLake tables unify lake and warehouse governance with column- and row-level controls over data in Cloud Storage or open table formats (like Parquet). Predicate and projection pushdown reduce bytes downloaded; large scans still favor ingestion into native tables for maximum performance.

• Wildcard tables and legacy shards:

  • Wildcard queries are a legacy pattern for date-sharded tables. Prefer native partitioning, but when needed: SELECT … FROM bigquery-public-data.noaa_gsod.gsod* WHERE _TABLE_SUFFIX >= ‘2010’.

Query Optimization and Workload Management

• Partition pruning and clustering:

  • Always filter on the partition column to avoid scanning cold partitions. Use BETWEEN with narrow windows.
  • Order clustering keys by selectivity; earlier keys should match frequent filters and joins. Avoid clustering on columns with very low cardinality.

• Query-plan optimization:

  • Use EXPLAIN and execution details to find skewed joins, large shuffles, or non-pruned scans.
  • Reduce columns early with SELECT lists and subqueries; BigQuery is columnar and drops unused columns efficiently.
  • Prefer approximate aggregations (for example, APPROX_QUANTILES) for speed/cost trade-offs on large data.
  • Apply deduplication with window functions when ingest sources may repeat events:
    • SELECT * EXCEPT(rn) FROM (SELECT t.*, ROW_NUMBER() OVER (PARTITION BY unique_id ORDER BY event_ts DESC) rn FROM my_table t) WHERE rn = 1;

• Joins and denormalization:

  • Co-locate join keys as cluster keys to reduce shuffle. Bloom filters or pre-aggregation can help with extreme skew.
  • Denormalize small, slowly changing dimensions into facts to avoid hot joins. For very wide dimensions with frequent updates, normalize and rely on cluster keys and materialized joins.

• Workload management, slots, editions, and autoscaling:

  • On-demand: BigQuery elastically scales compute per query; you pay per TB scanned. Control cost with maximum bytes billed and partition pruning.
  • Capacity-based with BigQuery editions (Standard, Enterprise, Enterprise Plus) uses slot reservations. Purchase baseline commitments, create reservations, and assign projects or folders. Autoscaling can add slots during peaks and release them when demand falls; use separate reservations for ETL vs BI to prevent interference.
  • Job priority: interactive (default) for low latency; batch for backfills and scheduled queries. Batch jobs queue until idle capacity is available in the reservation or in the service, then run at normal cost.

• Concurrency and quotas:

  • Use reservations and assignments to isolate critical workloads. For mixed tenants, place them in distinct reservations or projects with tailored concurrency limits.
  • Label jobs for attribution; monitor INFORMATION_SCHEMA.JOBS and Cloud Monitoring metrics for slot utilization and queueing delays.

• BI caching and freshness:

  • Query results cache improves latency/cost for identical queries; disable in clients needing sub-hour freshness. Some BI tools cache data independently—disable report caching to display the latest results.

Ingestion, Federation, and Recovery

• Load jobs:

  • Batch loads from Cloud Storage (Avro/Parquet preferred) are reliable and cost-effective. Set schema and encoding explicitly; mismatched CSV encodings are a common cause of byte-for-byte discrepancies.
  • Use partition decorators or load to partitioned tables to avoid merges. For large loads, parallelize by partition.

• Streaming ingestion and the Storage Write API:

  • Legacy streaming inserts are simple but have stricter quotas and can exhibit eventual consistency for a few seconds; time travel on very recent data may lag.
  • Storage Write API is the recommended path for high-throughput, low-latency writes with better deduplication controls. Use idempotency (stream offsets) to prevent duplicates.
  • Application design should tolerate in-flight events: delay interactive queries by expected availability (for example, 2× observed latency) or use watermarks on ingestion-time partitions.

• Dataflow and dead-letter design:

  • For partner-delivered CSVs with malformed rows, use Dataflow to parse and validate, write valid records to BigQuery via the Storage Write API, and route errors to a dead-letter table for inspection.
  • When reading BigQuery at scale, prefer query-based reads (fromQuery) to select only necessary fields and reduce shuffle.

• Scheduled queries and transformations:

  • Use scheduled queries for ELT transforms, incremental rollups, and table maintenance. Prefer writing to partitioned, clustered targets. Scheduled queries default to batch priority and integrate with reservations.

• Notifications and observability:

  • Export BigQuery audit logs with a log sink to Pub/Sub to trigger alerts on specific table insert jobs:
    • Filter example: resource.type=“bigquery_resource” AND protoPayload.methodName=“jobservice.jobCompleted” AND jsonPayload.jobChange.job.jobConfiguration.load.destinationTable.tableId=“target_table”
  • Use Cloud Logging audit logs and INFORMATION_SCHEMA views to discover usage patterns and enforce governance.

• Time travel, snapshots, and clones:

  • Time travel lets you query a table at a prior timestamp (default 7 days). Use FOR SYSTEM_TIME AS OF to read past states.
  • Table snapshots capture a point-in-time view with copy-on-write; use for consistent backfills or recovery. Table clones provide near-instant metadata copies for development or what-if analysis with minimal storage until divergence.
  • Recovery choices:
    • Small mistakes: query using time travel and INSERT…SELECT to restore.
    • Large restore: create from snapshot or clone, then swap.
  • Set table and partition expiration to enforce retention; verify that retention aligns with time-travel needs.

• Federated sources:

  • Use Cloud SQL federation for light joins; for sustained analytics or large scans, schedule extract-load into native tables.
  • BigLake tables over Parquet/ORC in Cloud Storage can enforce policy tags and push down filters and column projections; still expect higher latency than native storage.

Security, Governance, and Cost Control

• IAM and least privilege:

  • Grant dataset-level roles minimally (BigQuery Data Viewer, Data Editor) to approved users only; restrict API access to service accounts and curated groups.
  • Segregate clients and environments by dataset and project for isolation. Assign reservations per project or folder to prevent noisy neighbors.

• Authorized views and row-level security:

  • Authorized views expose only selected columns/rows to external projects while the view’s project retains table access. Keep the view and source in the same dataset or use dataset-level authorization to the target project.
  • Row-level security with row access policies filters rows per user or group at query time; combine with authorized views for layered control.

• Column-level security and policy tags:

  • Use Data Catalog policy tags to protect sensitive columns and enable data masking. Assign access to tags (not tables) to align with data classification. For partner access, mask or deny PII columns via tags.

• BigQuery ML and in-warehouse analytics:

  • Train and serve models directly in BigQuery (for example, linear/logistic regression, XGBoost, K-means, time series) with CREATE MODEL and ML.PREDICT. Store features in partitioned tables and use scheduled retraining.
  • Remote models let you invoke Vertex AI or external endpoints from SQL for scoring within federated governance. Cache outputs to tables to amortize latency for repeated queries.

• Cost controls and performance troubleshooting:

  • Reduce bytes scanned:
    • Partition and cluster; always filter on these keys.
    • SELECT only needed columns; avoid SELECT *.
    • Use materialized views and result caching where applicable.
    • Set maximum_bytes_billed to cap cost.
  • Troubleshoot performance:
    • Inspect job execution details for skew, non-pruned partitions, or shuffle hotspots. Re-key joins or pre-aggregate to reduce shuffle.
    • Validate MV rewrites; ensure compatible predicates and deterministic functions.
    • For dashboards missing fresh streaming data, account for consistency lag or disable client-side caching.
  • Governance visibility: audit with Cloud Logging, INFORMATION_SCHEMA, and fine-grained job labels to charge back and identify outliers.

Practical Problem Scenario

NovaCare Health operates a regional telemedicine platform. A single-table patient_and_visit design supported a pilot, but at 100× scale reports time out, duplicates appear from streaming upserts, and partners require strict data isolation.

Approach:

1. Redesign the schema and layout

   • Create normalized core tables: patients(patient_id, demographics, updated_at) and visits(visit_id, patient_id, visit_ts, metrics, updated_at).
   • Make visits a partitioned table on DATE(visit_ts), clustered by patient_id and visit_id. Keep small reference dimensions denormalized into visits for dashboard speed.
   • Rationale: Normalizing avoids expensive self-joins and heavy row updates on a single hot table. Partitioning prunes historical scans; clustering co-locates joins and filters on patient_id, reducing shuffle.

2. Ingest with the Storage Write API and enforce idempotency

   • Use a Dataflow pipeline to parse inbound events, validate, and write to named streams in the Storage Write API with idempotent offsets.
   • Route malformed events to a dead-letter BigQuery table for triage.
   • Rationale: The Storage Write API provides higher throughput, lower latency, and better dedup guarantees than legacy streaming. Dead-lettering preserves visibility into partner data quality issues.

3. Design for freshness and deduplication in queries

   • For interactive analytics, add a short watermark (for example, 2× observed availability) before querying the latest partition; or filter by _PARTITIONDATE where partition_date <= CURRENT_DATE() to exclude in-flight rows.
   • Use ROW_NUMBER() OVER (PARTITION BY visit_id ORDER BY event_ts DESC) = 1 in views that must tolerate upstream retries.
   • Rationale: Streaming is eventually consistent for a brief interval. Watermarking and window-based dedup protect dashboards from transient gaps and duplicates.

4. Accelerate common aggregates with materialized views

   • Create partition-aligned MVs over visits for daily KPIs grouped by DATE(visit_ts) and patient cohorts. Ensure predicates are compatible with rewrite.
   • Rationale: MVs reduce latency and scanned bytes for recurring reports; BigQuery rewrites queries to use the MV transparently.

5. Enforce tenant isolation and fine-grained security

   • Place each partner in a dedicated dataset. Grant least-privilege dataset roles to partner groups.
   • Publish authorized views for shared, cross-partner benchmarks without revealing raw tables.
   • Apply policy tags to PII columns and add row access policies to visits to restrict access by partner_id for internal multi-tenant analytics.
   • Rationale: Dataset-per-tenant segmentation plus authorized views and policy tags enforce least privilege while enabling curated sharing.

6. Manage workloads with editions, reservations, and autoscaling

   • Purchase capacity in BigQuery editions and create two reservations: etl (Dataflow sinks, scheduled transforms) and bi (ad hoc/reporting). Assign projects accordingly and enable autoscaling to absorb peaks.
   • Schedule ELT queries as batch with clear SLAs; set maximum_bytes_billed for interactive projects.
   • Rationale: Separate reservations prevent ETL from starving BI. Autoscaling accommodates bursty loads without overprovisioning.

7. Govern cost and observe usage

   • Require filters on visit_ts; reject SELECT * in shared views. Use INFORMATION_SCHEMA.JOBS to detect non-pruned scans and skewed joins.
   • Export BigQuery audit logs to Pub/Sub with a log sink filtered to insert jobs on visits to trigger monitoring alerts for unexpected surges.
   • Rationale: Byte-pruning and column projection control cost; audit logs surface access patterns and anomalies in near real time.

8. Plan recovery and backfills

   • Enable default table expiration policies that align with compliance and time-travel needs. For large edits, create a snapshot, run changes, and roll back quickly if necessary. Use table clones for dev/test what-if analyses without duplicating storage.
   • Rationale: Snapshots and clones provide fast, space-efficient safety nets; time travel covers small corrective restores.

9. Integrate in-warehouse ML

   • Store engineered features in partitioned tables and train BigQuery ML classification models for readmission risk. For external models hosted on Vertex AI, create remote models and cache predictions in a clustered table for low-latency joins.
   • Rationale: Keeping ML close to data reduces movement and governance complexity; caching remote inference amortizes latency and cost.

With this design, NovaCare achieves predictable performance under 100× load, strong tenant isolation, and governed costs, while preserving low-latency analytics and reproducible recovery.

← Data Storage · All domains · Stream Processing with Dataflow and Apache Beam →

Practice these questions → · Timed practice on ExamRoll.io →

Browse Google →