PDE BigQuery Deep Dive: Optimization & Cost (2026)

We organize the BigQuery domain — the largest share of the PDE exam — by the points that show up most often. This covers partitioning, clustering, Editions, ingestion API choices, BigQuery ML, cost optimization, and the latest BigLake / Omni / Gemini integrations.

BigQuery Pricing Models

Model	Price	Use Case
On-demand	$6.25 per TB scanned	Ad-hoc queries, small-scale use
Editions Standard	Per slot-hour	SQL only, low cost
Editions Enterprise	Slot-hour + CMEK / Workload Mgmt	Full production use
Editions Enterprise Plus	+ DR / Disaster Recovery	Regulated workloads and large enterprises
Storage	Active $0.02/GB, Long-term $0.01/GB	Auto-converts to Long-term after 90 days of no changes

Partitioning and Clustering

Partitioning Types

Time-unit column: partition on a DATETIME / TIMESTAMP / DATE column
Ingestion time: automatic partitioning by ingestion time (_PARTITIONTIME)
Integer range: integer range partitioning (e.g., IDs)

Clustering

Specify up to 4 columns as cluster keys
Physical sort within blocks → faster filters and aggregations
Combining partition + clustering is the canonical pattern (e.g., partition=date, cluster=user_id, country)

Data Ingestion Methods

Method	Use Case	Throughput
Batch Load (CLI / API)	Free, scheduled batches	Unlimited
Storage Write API	Standard for new development	High, exactly-once
Legacy Streaming Insert	Being phased out	Paid
BigQuery Data Transfer	SaaS integration (GA / Ads / S3)	Scheduled execution
Datastream	CDC (Oracle / MySQL / PostgreSQL)	Near real-time

Key BigQuery ML Models

Linear / Logistic Regression: CREATE MODEL ... OPTIONS(model_type='linear_reg')
K-means: clustering
Matrix Factorization: recommendation systems
Boosted Tree: XGBoost-based
Deep Neural Network: TensorFlow integration
AutoML Tables: automated model selection
TensorFlow Import: import existing models
Gemini integration: ML.GENERATE_TEXT, ML.GENERATE_EMBEDDING, Remote Model

BigQuery Omni / BigLake

Feature	BigQuery Omni	BigLake
Target Data	AWS S3 / Azure Blob	GCS / S3 / Azure
Format	BigQuery native	Iceberg / Parquet / ORC
Processing Location	Anthos on AWS/Azure	GCP region
Primary Use Case	Multi-cloud SQL	Lakehouse unification

Cost Optimization Checklist

Ban SELECT *; specify only the columns you need
Require partition filters in the WHERE clause (require_partition_filter)
Accelerate hot queries with Materialized Views / BI Engine
Custom Cost Controls (daily limits per project / user)
Surface expensive queries via INFORMATION_SCHEMA.JOBS
Use Editions reservations for predictable costs
Manage unmodified tables with Long-term storage transition in mind

Common Tricky Exam Topics

Materialized View limitations: aggregation functions only, no INNER JOIN, and so on
Streaming buffer: UPDATE/DELETE not allowed for ~90 minutes after ingestion
Wildcard table: control scan volume with a TABLE_SUFFIX filter
Query result cache: valid for 24h, free for identical queries
Reservation Assignment: Folder / Project / Reservation hierarchy

How much of the PDE exam covers BigQuery?

Roughly 30-40%. Expect questions across data ingestion, partitioning, clustering, cost optimization, BigQuery ML, and BI Engine.

Does the exam test on-demand pricing or Editions?

Both. The Editions tiers (Standard / Enterprise / Enterprise Plus) introduced in 2023 are frequent topics in the new blueprint. Understand them as slot-based, predictable cost management.

What is the difference between partitioning and clustering?

Partitions (DATE / RANGE) physically split the table, while clustering physically sorts data within a partition. Choose them based on the columns you filter on. Combining both is the canonical pattern.

What is the difference between BigQuery Omni and BigLake?

Omni queries data in AWS S3 / Azure Blob across clouds. BigLake provides unified management of Iceberg / Parquet / ORC on GCS / S3 / Azure. The distinction is a frequent exam topic.

Should I use Streaming Insert or Storage Write API?

Use Storage Write API for new development (high throughput, lower cost, exactly-once). Legacy Streaming Insert is being phased out.

What can you build with BigQuery ML?

Linear/logistic regression, k-means, matrix factorization, AutoML, TensorFlow model imports, and recently Gemini integration (ML.GENERATE_TEXT). The key strength is implementing ML entirely in SQL.

What are the best practices for preventing runaway costs?

Avoid SELECT *, require partition filters, use reserved slots via Editions, set Cost Controls (custom quotas), and monitor queries via INFORMATION_SCHEMA.

What is the difference between a Materialized View and an Authorized View?

Materialized Views are precomputed (cache + auto-refresh); Authorized Views are logical views that act as a permission boundary. They are tested on different points, so understanding both is essential.

Related: PDE / BigQuery

Professional Data Engineer (PDE): Complete Guide (2026)

Pass the PDE exam — BigQuery, Dataflow, Composer, Pub/Sub, ML pipelines. The data engineering Professional cert.

Professional Cloud Network Engineer (PCNE): Guide (2026)

Pass the PCNE exam — VPC, Cloud Interconnect, Cloud VPN, Cloud Load Balancing, hybrid networking.

Professional Cloud Developer (PCD): Complete Guide (2026)

Pass the PCD exam — Cloud Run, GKE, App Engine, Cloud SQL/Spanner. The developer-focused Professional cert.

Professional Cloud DevOps Engineer (PCDOE): Guide (2026)

Pass the PCDOE exam — SRE, observability, Cloud Build, Cloud Deploy, incident response. The DevOps Professional cert.

* Google Cloud and BigQuery are trademarks of Google LLC. For the latest information, see the official BigQuery documentation.

Check what you learned with practice questions

Practice with certification-focused question sets

View GCP exam prep

Author

NicheeLab Editorial Team

NicheeLab editorial team focused on data engineering and cloud certification learning. Content is structured around practical study needs and official exam domains.

GCP PDE Exam Prep: BigQuery Deep Dive — Partitioning, Editions, and BigQuery ML