Analytics — GCP DE Exam Prep

BigQuery Architecture & Pricing

BigQuery is GCP's serverless, columnar data warehouse. It separates compute from storage, making it possible to query petabytes without managing infrastructure.

Pricing Models

On-demand (per-query): ~$5 per TB of data scanned. Ideal for sporadic, unpredictable workloads. Scan estimation with --dry_run before executing.
Flat-rate / Capacity commitments: Purchase slots (units of compute). Predictable cost for high, sustained usage. Available as 1-minute flex slots, monthly, or annual commitments.
BigQuery editions (2023+): Standard, Enterprise, Enterprise Plus — differ by autoscale limits, BI Engine inclusion, and CMEK support.

Exam tip: SELECT * is the enemy of on-demand cost. BigQuery bills by columns scanned, not rows. Always SELECT only needed columns and use partitioning + clustering to skip irrelevant partitions.

Partitioning & Clustering

These are the two primary mechanisms for reducing query cost and improving performance in BigQuery.

Partitioning

Time-unit column partitioning: Partition on a DATE, TIMESTAMP, or DATETIME column. Query only scans partitions matching the WHERE clause filter. Partition pruning eliminates irrelevant partitions entirely from the query plan.
Ingestion-time partitioning: Rows are partitioned by when they were loaded (_PARTITIONTIME). Useful when source data has no timestamp column.
Integer range partitioning: Partition on an integer column by a defined range and interval. Useful for customer IDs or hash-bucketed keys.
Partition expiration: Automatically delete partitions older than N days. Set on the table or per-partition.

Clustering

Clustering sorts rows within each partition by up to 4 columns. When a query filters or aggregates on clustered columns, BigQuery skips blocks of data that can't match — this is called block pruning.

Clustering is free — no extra storage cost. BigQuery automatically re-clusters over time.
Best columns to cluster: columns used frequently in WHERE, JOIN, or GROUP BY. High-cardinality columns cluster better than low-cardinality ones.
Clustering alone (without partitioning) still provides significant scan reduction on large tables.

Partitioning vs. Clustering: Partitioning provides hard partition pruning (guaranteed cost reduction). Clustering provides best-effort block pruning (cost estimates may not reflect full savings). For columns with <1,000 distinct values, clustering is often more efficient than partitioning.

Partition pruningWhole partitions skipped

Block pruningWithin-partition skip

Max partitions4,000 per table

Cluster colsUp to 4 columns

Materialized Views & Query Optimization

Materialized Views

Materialized views pre-compute and cache query results. BigQuery automatically refreshes them when the base table changes (within 5 minutes by default). Key characteristics:

BigQuery's query optimizer can transparently rewrite queries to use a materialized view even when the query doesn't reference the view directly — called smart tuning.
Incremental refresh: only changed partitions are recomputed, not the full table.
Materialized views cannot include JOINs across different datasets in the same table. Non-deterministic functions (NOW(), RAND()) are not allowed.

INFORMATION_SCHEMA

BigQuery's INFORMATION_SCHEMA is a set of system views for metadata and operational analysis. Key views for the exam:

INFORMATION_SCHEMA.JOBS_BY_PROJECT — query history, bytes processed, slot usage, errors
INFORMATION_SCHEMA.TABLE_STORAGE — table size, row counts, partition counts
INFORMATION_SCHEMA.PARTITIONS — per-partition row count and last-modified time
INFORMATION_SCHEMA.COLUMNS — schema metadata for all tables in a dataset

Exam tip: "How do you find which queries are scanning the most data?" → INFORMATION_SCHEMA.JOBS_BY_PROJECT ordered by total_bytes_processed.

Slot Utilization & Reservations

In flat-rate mode, slots are shared across all jobs in a reservation. Use INFORMATION_SCHEMA.JOBS_TIMELINE_BY_PROJECT to identify slot contention and peak usage periods. Autoscaling reservations (in newer editions) automatically add slots during demand spikes.

BI Engine & Looker

BigQuery BI Engine

BI Engine is an in-memory analysis service that accelerates BigQuery queries for BI tools. It caches frequently accessed tables and columns in memory, delivering sub-second query response times without changing SQL or application code.

Priced per GB of reserved memory (not per query).
Works transparently with Looker, Looker Studio, and any tool using the BigQuery API.
Reservation applies at the project level. Tables exceeding the reservation fall back to standard BigQuery execution.

Looker & Looker Studio

Looker Studio (formerly Data Studio): Free drag-and-drop visualization tool. Connects directly to BigQuery, GCS, and 800+ data sources. Best for quick dashboards and sharing.
Looker: Enterprise BI platform. Uses LookML (a YAML-based modeling language) to define business metrics, dimensions, and relationships. Enables a single source of truth for business definitions. Supports embedded analytics, API access, and governance.
When to use Looker vs. Looker Studio: Looker for governed enterprise metrics with version-controlled models. Looker Studio for fast ad-hoc dashboards.

📝 Practice Questions — Analytics

Question 1 — Partitioning Strategy

You manage a BigQuery table containing 5 years of e-commerce order data (~500 billion rows, 80 TB). The table is currently unpartitioned. Analysts run queries that always filter by order_date (a DATE column) for date ranges of 1–30 days. Queries are taking 3–5 minutes and costing ~$400 each. You want to reduce query cost by at least 90% without changing analyst SQL. What should you do?

AAdd a clustering on order_date — BigQuery will use block pruning to skip irrelevant data

BRe-create the table with time-unit column partitioning on order_date (DAY granularity) — queries filtering on order_date will only scan the matching day partitions

CCreate a materialized view that pre-aggregates data by order_date and direct analysts to query the view

DEnable BI Engine with a 100 GB reservation to cache the most accessed data in memory

Answer: B. Time-unit column partitioning on order_date provides guaranteed partition pruning — a 30-day query against a 5-year table scans roughly 30/1825 ≈ 1.6% of data, reducing costs from ~$400 to ~$7 without any SQL changes. Clustering (A) provides block-level pruning which is less precise and wouldn't guarantee 90% reduction on its own. Materialized views (C) require schema changes and analyst behavior change. BI Engine (D) is for sub-second latency on smaller hot datasets, not petabyte-scale scan reduction.

Question 2 — Materialized Views

Your analytics team runs a complex query hundreds of times per day joining three tables: orders (5 TB), products (100 MB), and customers (500 MB). The query aggregates revenue by product category and customer region for the current month. Data in all three tables is updated via streaming inserts. Queries are taking 45 seconds and costing $2,500/day. Which approach best reduces cost and latency?

ACluster the orders table on product_id and customer_id

BCreate a scheduled query that runs every hour and writes results to a summary table

CCreate a materialized view that pre-computes the aggregation — BigQuery will auto-refresh it as base tables change and can rewrite queries transparently

DUse BigQuery BI Engine with a 6 GB reservation to cache the result in memory

Answer: C. A materialized view pre-computes the JOIN and aggregation. Incremental refresh means only newly inserted rows are reprocessed, not the full 5 TB table each time. BigQuery's smart tuning can transparently rewrite queries to hit the materialized view even if analysts don't change their SQL. Scheduled queries (B) introduce up to 1-hour staleness and require manual coordination. Clustering (A) reduces scan but doesn't eliminate the JOIN cost. BI Engine (D) accelerates sub-second queries on cached data but doesn't eliminate compute for the initial complex join.

Question 3 — Pricing Model Selection

Your organization runs two types of BigQuery workloads: (1) a production dashboard that runs 500 standardized queries per hour, 24/7; (2) a data science team running ad-hoc exploratory queries — volume is unpredictable, anywhere from 10 to 1,000 queries per day. You want to optimize total cost. What pricing strategy should you use?

AUse flat-rate capacity commitments (annual or monthly) for the production dashboard workload; use on-demand pricing for the data science team's ad-hoc queries

BUse on-demand pricing for all workloads — it is simpler to manage

CUse flat-rate pricing for all workloads to get predictable billing

DUse flex slots for the production dashboard since they can be purchased minute-by-minute

Answer: A. High-volume, predictable production workloads benefit from flat-rate/capacity commitments — the fixed slot cost is lower than per-query on-demand pricing at sustained throughput. Ad-hoc workloads with unpredictable volume are better served by on-demand pricing since there's no minimum commitment. Option B (all on-demand) overpays for predictable production volume. Option C (all flat-rate) may over-provision slots for sporadic ad-hoc work. Option D (flex slots) are per-minute and more expensive per slot-hour than monthly/annual commitments — not suitable for 24/7 production.