Study guide
13.1 Advanced SQL in BigQuery: Window Analytic Functions, ARRAY/STRUCT, and PIVOT/UNPIVOT
Google Cloud Certified Professional Data Engineer Exam
Track your progress as you read
Finished sections get a check mark.
1 Chapter 1: Security, IAM, and Data Protection
1.1 Cloud IAM, Service Accounts, and Resource Hierarchy in GCP Data Systems 1.2 Data Encryption, Key Management (CMEK), and Cloud KMS 1.3 Sensitive Data Protection: Cloud DLP, De-identification, and Masking
2 Chapter 2: Reliability and Compliance Architecture
2.1 High Availability, Regional vs Multi-Regional Deployments, and Disaster Recovery 2.2 Data Residency, Sovereignty, and Regulatory Compliance (HIPAA, GDPR) 2.3 Modern Data Architecture Patterns: Data Mesh, Lakehouse, and Modern Data Stack
3 Chapter 3: Cross-Cloud and Federated Governance
3.1 BigLake Architecture: Unified Multicloud Storage and Fine-Grained Access Control 3.2 BigQuery Omni: Multicloud Analytics Across AWS S3, Azure ADLS, and Google Cloud 3.3 Analytics Hub and Federated Computational Governance
4 Chapter 4: Data Ingestion and Pipeline Planning
4.1 Data Pipeline Planning: Sources, Sinks, and Network Architecture (VPC, PSC, Interconnect) 4.2 Batch vs Streaming Processing Trade-Offs and Architecture Selection 4.3 Change Data Capture (CDC) with Datastream for Continuous Database Ingestion
5 Chapter 5: Streaming Ingestion with Cloud Pub/Sub
5.1 Cloud Pub/Sub Core Architecture: Topics, Subscriptions, and Message Lifecycle 5.2 Pub/Sub Message Ordering, Exactly-Once Delivery, and Deduplication 5.3 Pub/Sub BigQuery/Cloud Storage Direct Subscriptions and Dead-Letter Topics
6 Chapter 6: Pipeline Processing with Apache Beam and Cloud Dataflow
6.1 Apache Beam Programming Model: Pipelines, PCollections, and Core PTransforms 6.2 Cloud Dataflow Architecture: Autoscaling, Worker Allocation, and Dataflow Prime 6.3 Windowing Strategies in Streaming Dataflow: Fixed, Sliding, and Session Windows
7 Chapter 7: Advanced Dataflow: Robustness and Exactly-Once Semantics
7.1 Watermarks, Triggers, Accumulating vs Discarding Panes, and Late Data Handling 7.2 Side Inputs, Broadcast Joins, and Stateful Stream Processing 7.3 Pipeline Robustness: Dead-Letter Queues, Error Handling, and Pipeline Lifecycle Management
8 Chapter 8: Managed Data Processing: Cloud Dataproc and Cloud Data Fusion
8.1 Cloud Dataproc: Architecture, Ephemeral vs Persistent Clusters, and Spark Tuning 8.2 Dataproc Serverless and Autoscaling Policies for Batch Processing 8.3 Cloud Data Fusion: Visual ETL/ELT Pipelines, CDAP Plugins, and Data Lineage
9 Chapter 9: Storage Selection and Cloud Storage Architecture
9.1 GCP Storage Selection Decision Matrix: Relational, NoSQL, Object, and Analytical Stores 9.2 Cloud Storage Architecture: Storage Classes, Lifecycle Rules, and Object Versioning 9.3 Cloud Storage Security: Bucket Lock (WORM), Access Control, and Data Migration Tools
10 Chapter 10: Relational Cloud Databases: Cloud Spanner and Cloud SQL
10.1 Cloud Spanner Architecture: TrueTime, External Consistency, and Global Scalability 10.2 Cloud Spanner Schema Design: Primary Keys, Interleaved Tables, and Query Tuning 10.3 Cloud SQL: High Availability, Read Replicas, Maintenance, and Point-in-Time Recovery
11 Chapter 11: Scalable NoSQL Databases: Cloud Bigtable and Firestore
11.1 Cloud Bigtable Architecture: Storage Model, Tablet Splitting, and Cluster Management 11.2 Cloud Bigtable Row Key Design: Lexicographical Ordering, Salt Keys, and Anti-Patterns 11.3 Cloud Firestore: Document Model, Real-Time Sync, and ACID Collections
12 Chapter 12: Enterprise Data Warehousing with BigQuery
12.1 BigQuery Architecture: Capacitor Columnar Format, Jupiter Network, and Borg Slots 12.2 BigQuery Partitioning: Ingestion-Time, Date/Timestamp, and Integer Range Strategies 12.3 BigQuery Clustering: Cardinality, Multi-Column Ordering, and Performance Optimization 12.4 Data Warehouse Modeling: Normalization, Star Schemas, and Slowly Changing Dimensions
13 Chapter 13: Advanced Analytical Queries and Transformations in BigQuery
13.1 Advanced SQL in BigQuery: Window Analytic Functions, ARRAY/STRUCT, and PIVOT/UNPIVOT 13.2 Modern SQL Orchestration with Dataform: DAG Modeling, Testing, and Documentation 13.3 BI Engine In-Memory Acceleration and Enterprise Looker/LookML Semantic Modeling
14 Chapter 14: Machine Learning and Feature Engineering with BigQuery ML
14.1 BigQuery ML Architecture: In-Database Training, Model Topologies, and SQL Analytics 14.2 Feature Engineering: ML.TRANSFORM, Preprocessing Pipelines, and Vertex AI Feature Store 14.3 MLOps, Model Governance, Continuous Monitoring, and Deployment Lifecycles
15 Chapter 15: Operations, Orchestration, and Data Governance
15.1 Workflow Orchestration with Cloud Composer: Managed Airflow, DAG Design, and Dependencies 15.2 Unified Data Governance with Dataplex: Lakes, Zones, Data Quality, and Lineage 15.3 Pipeline Observability, FinOps, and Cost Optimization Across GCP Data Services
13.1 Advanced SQL in BigQuery: Window Analytic Functions, ARRAY/STRUCT, and PIVOT/UNPIVOT
Key Takeaways Explain This Topic AI
Analytic window functions compute aggregated, ranked, or navigational values across a configurable window frame while preserving the granular row count and individual identity of every row in the result set.
When an ORDER BY clause is present without an explicit frame clause, BigQuery applies the default window frame: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, treating duplicate ordering values as peers and producing identical cumulative values.
Capacitor shreds strongly-typed STRUCT and repeated ARRAY fields into isolated columnar vectors on Colossus; UNNEST requires LEFT JOIN ... ON TRUE to prevent silently dropping parent rows containing empty or NULL arrays.
The QUALIFY clause evaluates directly after analytic window functions, eliminating verbose nested subqueries and Common Table Expressions (CTEs) while allowing the query optimizer to prune execution via bounded in-memory heaps.
Native PIVOT rotates row values into discrete columns using aggregation expressions, while UNPIVOT normalizes cross-tabulated columnar data back into row pairs without requiring multiple table scans via UNION ALL.
Last updated: September 2026
13.1 Advanced SQL in BigQuery: Window Analytic Functions, ARRAY/STRUCT, and PIVOT/UNPIVOT
Exam Focus: The Google Cloud Professional Data Engineer exam rigorously evaluates your ability to write performant, production-grade analytical SQL in BigQuery. You must master the execution mechanics of the OVER clause, the critical performance and correctness distinction between physical ( ROWS ) and logical ( RANGE ) framing, how tie-breaking ranking functions ( ROW_NUMBER , RANK , DENSE_RANK ) behave in deduplication pipelines, how to correctly avoid the LAST_VALUE() current-row pitfall, how to eliminate expensive CTE boilerplate using the QUALIFY clause, how to query nested and repeated data ( STRUCT , ARRAY , UNNEST ) without losing records, and how to reshape data matrices using native PIVOT and UNPIVOT operators.
Traditional relational aggregations relying on GROUP BY collapse multiple records into a single summary row. While grouping is foundational for high-level dimensional rollups, it irreversibly destroys the granular identity, attributes, and cardinality of individual records. Advanced analytical engineering in modern cloud warehouses frequently requires computing running metrics, localized rankings, moving averages, and cross-row navigational offsets while retaining every individual record's identity .
In BigQuery's distributed Dremel engine, understanding these advanced SQL primitives is not merely an aesthetic or syntactical preference—it directly impacts compute slot utilization, memory allocation, and financial query costs. Unpartitioned window functions can force massive, single-node shuffle bottlenecks across the Jupiter network fabric, whereas correctly framed queries execute in parallel with linear scalability.
1. Anatomy of an Analytic Window Function: The OVER Clause
An analytic window function computes values over a specified group of rows, termed a window frame , while returning a calculated result for every single row in the input relation. analytic_function([arguments]) OVER (
- [PARTITION BY partition_expression [, ...]]
- [ORDER BY sort_expression [{ASC | DESC}] [{NULLS FIRST | NULLS LAST}] [, ...]]
- [window_frame_clause]
- ) +─────────────────────────────────────────────────────────────────────────────────+
- | WINDOW FUNCTION ANATOMY |
- +─────────────────────────────────────────────────────────────────────────────────+
- | SUM(transaction_amount) OVER ( |
- | PARTITION BY customer_id <-- Distributes rows into independent slices |
- | ORDER BY transaction_time <-- Establishes deterministic sequence in slice |
- | ROWS BETWEEN 2 PRECEDING <-- Defines explicit physical sliding frame |
- | AND CURRENT ROW |
- | ) |
- +─────────────────────────────────────────────────────────────────────────────────+
- The Core Structural Elements
PARTITION BY (Distributed Slice Allocation): Divides the incoming dataset into discrete, independent subsets. The analytic calculation evaluates independently within each partition and restarts from scratch across partition boundaries. Under the hood, Dremel hashes the partition keys to distribute records across worker slots via the Jupiter network. Critical Architectural Warning: If PARTITION BY is omitted, the entire dataset is treated as a single monolithic partition. BigQuery is forced to route all rows to a single mixer/slot for evaluation, which on multi-million or billion-row tables will trigger immediate out-of-memory query failures ( Resources exceeded during query execution ).
ORDER BY (Intra-Partition Sorting): Establishes the logical sequence of rows within each partition. Ordering is mandatory for ranking functions ( RANK , DENSE_RANK , ROW_NUMBER ) and navigation functions ( LEAD , LAG ).
Window Frame Clause ( ROWS vs. RANGE ): Defines the exact boundary of rows relative to the current row that are included in the calculation.
2. Window Framing: Physical ROWS vs. Logical RANGE
The window frame clause specifies the boundary of calculation relative to the current row. BigQuery supports two primary framing mechanisms: physical row counts ( ROWS ) and logical value ranges ( RANGE ). DATASET: (ORDER BY transaction_date)
- Row 1: 2026-03-01 | $100
- Row 2: 2026-03-02 | $200
- Row 3: 2026-03-02 | $300 <-- Duplicate Date (Logical Value Peers)
- Row 4: 2026-03-05 | $400
CALCULATING RUNNING SUM AT ROW 3:
- Explicit Physical Framing (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW):
Evaluates Rows 1, 2, and 3 -> Sum = $100 + $200 + $300 = $600
- Implicit Logical Framing (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW):
Evaluates Rows 1, 2, 3, AND 4 (Row 2 and Row 3 are identical peers!):
Row 2 and Row 3 both output $600 because their order key is identical.
Frame Boundary Terminology
UNBOUNDED PRECEDING : Extends the frame to the very first row of the partition.
n PRECEDING : Evaluates n physical rows (under ROWS ) or n numeric/date units (under RANGE ) prior to the current row.
CURRENT ROW : The row currently undergoing calculation.
n FOLLOWING : Evaluates n physical rows or units subsequent to the current row.
UNBOUNDED FOLLOWING : Extends the frame to the final row of the partition.
The Critical Default Framing Trap
A classic GCP Data Engineer certification question tests the implicit window frame applied when you specify ORDER BY but omit the frame clause:
- Specification in OVER Clause Default Window Frame Applied by BigQuery
- ORDER BY is present, but frame is omitted RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
- Both ORDER BY and frame are omitted ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
Exam Trap: Because the default frame with ORDER BY is RANGE (logical) rather than ROWS (physical), any duplicate values in the ORDER BY column are treated as peers . BigQuery calculates the aggregate across all peers simultaneously. In a running total of sales ordered by sale_date , if five transactions occur on the same date, all five rows will display the identical cumulative total (the sum including all five sales) rather than incrementing line-by-line.
- To ensure deterministic, step-by-step physical running sums, you must explicitly declare: SUM(amount) OVER (
- PARTITION BY account_id
- ORDER BY transaction_date, transaction_id
- ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
- )
- 3. Ranking Functions: ROW_NUMBER , RANK , DENSE_RANK , PERCENT_RANK , and NTILE
Ranking functions assign relative ordinal values to records within a partition based on an ORDER BY criteria. Choosing the correct ranking function is critical when writing deduplication pipelines and top-N filters. SCORE DATA: [100, 95, 95, 90, 80]
- Function Row 1 (100) Row 2 (95) Row 3 (95) Row 4 (90) Row 5 (80)
- ---------------------------------------------------------------------------
- ROW_NUMBER() 1 2 3 4 5
- RANK() 1 2 2 4 5
- DENSE_RANK() 1 2 2 3 4
- PERCENT_RANK() 0.0 0.25 0.25 0.75 1.0
- NTILE(2) 1 1 1 2 2
- Comparative Ranking Behavior Matrix
Function Tie Handling Behavior Subsequent Number Sequence Determinism Guarantee Canonical Exam Use Case
ROW_NUMBER() Arbitrary unless order keys are unique Strictly contiguous ( 1, 2, 3, 4... ) with zero duplicates Non-deterministic on ties unless secondary unique tie-breaker key is provided Strict Deduplication: Emitting exactly one unique record per business key ( QUALIFY ROW_NUMBER() ... = 1 ).
RANK() Assigns identical rank to ties Skips numbers corresponding to tie count ( 1, 2, 2, 4... ) Deterministic Olympic Podium / Competition: Awarding positions where ties displace subsequent ranks.
DENSE_RANK() Assigns identical rank to ties Never skips numbers ( 1, 2, 2, 3, 4... ) Deterministic Top-N Distinct Values: Selecting the top 3 distinct price points or top 5 highest-paid salary tiers.
PERCENT_RANK() Identical on ties; range [0.0, 1.0] Evaluated as: rank − 1 total_rows − 1 \frac{\text{rank} - 1}{\text{total\_rows} - 1} total_rows − 1 rank − 1 Deterministic Percentile Placement: Identifying outliers in the top 1% or bottom 5% of transaction volumes.
NTILE(n) Distributes rows evenly into n buckets Increments bucket ID from 1 to n Deterministic if order is unique Equal-Sized Cohorting: Dividing customers into quartiles ( NTILE(4) ) or deciles ( NTILE(10) ).
Production SQL Example: Top-3 Salaries per Department
If you want the top 3 highest distinct compensation levels per department regardless of how many employees share that salary, use DENSE_RANK() : SELECT
- department_id,
- employee_id,
- salary,
- DENSE_RANK() OVER (
- PARTITION BY department_id
- ORDER BY salary DESC
- ) as salary_rank
- FROM `enterprise_hr.employees`
- QUALIFY salary_rank <= 3;
- 4. Navigation Functions: LEAD , LAG , FIRST_VALUE , and LAST_VALUE
Navigation functions allow queries to inspect values from preceding, succeeding, or boundary rows without executing costly self-joins.
LEAD() and LAG() : Relative Row Offsets
LAG(expression [, offset [, default_value]]) : Accesses a row prior to the current row by offset (default is 1). If no such row exists, returns default_value (or NULL ).
LEAD(expression [, offset [, default_value]]) : Accesses a row subsequent to the current row by offset (default is 1). -- Calculating Session Inactivity Duration between successive events
- SELECT
- user_id,
- event_timestamp,
- LAG(event_timestamp, 1) OVER (
- PARTITION BY user_id
- ORDER BY event_timestamp ASC
- ) AS previous_event_timestamp,
- TIMESTAMP_DIFF(
- event_timestamp,
- LAG(event_timestamp, 1) OVER (
- PARTITION BY user_id
- ORDER BY event_timestamp ASC
- ),
- SECOND
- ) AS idle_seconds
- FROM `clickstream.events`;
- FIRST_VALUE() and LAST_VALUE() : Boundary Value Retrieval
FIRST_VALUE(expression [{RESPECT | IGNORE} NULLS]) : Retrieves the expression evaluated for the first row in the window frame.
LAST_VALUE(expression [{RESPECT | IGNORE} NULLS]) : Retrieves the expression evaluated for the last row in the window frame.
The Infamous LAST_VALUE() Pitfall
A pervasive bug in enterprise SQL and a standard GCP certification trap arises from calling LAST_VALUE() without an explicit window frame clause.
Consider this query intended to return the latest status of an order alongside every historical event: -- INCORRECT: Returns current row value, NOT the last row of the partition!
- SELECT
- order_id,
- event_time,
- status,
- LAST_VALUE(status) OVER (
- PARTITION BY order_id
- ORDER BY event_time ASC
) AS latest_status -- FAILS! Outputs current status!
FROM `logistics.order_status_history`;
Why it fails: Remember the default window frame when ORDER BY is present: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW . At every row, the "last row" in the active frame is the current row . Therefore, LAST_VALUE() simply mirrors the current row's status.
The Solution: You must expand the frame to include future rows up to the partition end: -- CORRECT: Frame covers the entire partition
- SELECT
- order_id,
- event_time,
- status,
- LAST_VALUE(status) OVER (
- PARTITION BY order_id
- ORDER BY event_time ASC
- ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
- ) AS latest_status
- FROM `logistics.order_status_history`;
- Alternatively, reverse the sort order and use FIRST_VALUE : FIRST_VALUE(status) OVER (
- PARTITION BY order_id
- ORDER BY event_time DESC
- ) AS latest_status
- 5. The QUALIFY Clause: Zero-Subquery Window Filtering
In standard ANSI SQL, filtering results based on an analytic window function requires wrapping the query inside a subquery or Common Table Expression (CTE), because window functions are evaluated after the WHERE and HAVING clauses.
- Logical Query Processing Order in GoogleSQL 1. FROM --> Identifies tables, evaluates JOINs, unrolls arrays
- 2. WHERE --> Filters raw base rows (before grouping or windows)
- 3. GROUP BY --> Groups rows into aggregate buckets
- 4. HAVING --> Filters aggregate groups
- 5. WINDOW --> Evaluates all analytic OVER () functions
- 6. QUALIFY --> Filters rows based on analytic window results
- 7. DISTINCT --> Eliminates duplicate output rows
- 8. ORDER BY --> Sorts final formatted result set
- 9. LIMIT --> Truncates output row count
Because WHERE executes at Step 2 and window functions evaluate at Step 5, running WHERE ROW_NUMBER() OVER (...) = 1 causes a compilation error: "Analytic functions not allowed in WHERE clause."
- Legacy CTE Pattern vs. Modern QUALIFY Syntax -- LEGACY APPROACH: Requires verbose CTE or subquery boilerplate
- WITH ranked_records AS (
- SELECT
- device_id,
- payload,
- ingestion_time,
- ROW_NUMBER() OVER (
- PARTITION BY device_id
- ORDER BY ingestion_time DESC
- ) as rn
- FROM `iot.streaming_ingest`
- )
- SELECT device_id, payload, ingestion_time
- FROM ranked_records
- WHERE rn = 1;
- - MODERN BIGQUERY APPROACH: Direct filtering via QUALIFY
- SELECT
- device_id,
- payload,
- ingestion_time
- FROM `iot.streaming_ingest`
- QUALIFY ROW_NUMBER() OVER (
- PARTITION BY device_id
- ORDER BY ingestion_time DESC
- ) = 1;
- Performance Advantages of QUALIFY
Cleaner, Maintainable SQL: Removes unnecessary intermediate subqueries and CTE layers.
Optimizer Pushdown: BigQuery's query planner detects QUALIFY ROW_NUMBER() ... = 1 and actively prunes execution trees. In stages where sorting occurs, worker slots can maintain a top-1 bounded heap in memory rather than sorting hundreds of millions of historical rows in Colossus shuffle storage, cutting slot-hours significantly.
6. Semi-Structured Data: STRUCT , ARRAY , and UNNEST
To achieve petabyte-scale throughput, BigQuery embraces denormalization via semi-structured data primitives. By embedding parent-child hierarchies into a single table using nested structures ( STRUCT ) and repeated collections ( ARRAY ), BigQuery colocates related data physically on disk, eliminating the network overhead of distributed joins.
Definitions and DDL Syntax
STRUCT (Record): A container of ordered fields, each with a mandatory type and optional field name. Analogous to an object or struct in programming languages.
ARRAY (Repeated Field): An ordered list of zero or more elements sharing identical data types. Arrays can hold scalar primitives ( ARRAY<STRING> ) or complex structures ( ARRAY<STRUCT<...>> ). Arrays cannot directly contain arrays (nested arrays like ARRAY<ARRAY<INT64>> are invalid; you must wrap the inner array in a STRUCT ). CREATE OR REPLACE TABLE `ecommerce.orders` (
- order_id STRING,
- order_timestamp TIMESTAMP,
- customer STRUCT<
- customer_id STRING,
- tier STRING,
- shipping_address STRUCT<
- city STRING,
- postal_code STRING
- >
- >,
- line_items ARRAY<STRUCT<
- sku STRING,
- quantity INT64,
- unit_price NUMERIC
- >>
- );
- Capacitor Record Shredding Mechanics
How does BigQuery store this table without suffering relational join penalties? Capacitor implements Google's Dremel record shredding algorithm. Each leaf field—such as customer.shipping_address.city and line_items.sku —is stored in its own isolated columnar vector on Colossus.
Along with the value vectors, Capacitor stores two lightweight integer metadata streams:
Repetition Level: Records at what depth in the schema tree the value repeats (distinguishing items belonging to the same order vs. the next order).
Definition Level: Records how many optional ancestor fields in the path are defined (tracking NULL semantics).
When a query executes SELECT SUM(item.quantity) FROM ecommerce.orders, UNNEST(line_items) AS item , BigQuery reads only the line_items.quantity column block from disk. Unreferenced fields ( customer , order_timestamp , item.unit_price ) are completely bypassed.
Querying Arrays: CROSS JOIN UNNEST vs. LEFT JOIN UNNEST ... ON TRUE
The UNNEST operator takes an ARRAY and returns a virtual table with one row for each element in the array. -- Implicit Cross Join Syntax
- SELECT
- order_id,
- customer.customer_id,
- item.sku,
- item.quantity
- FROM `ecommerce.orders`,
- UNNEST(line_items) AS item;
Exam Trap: CROSS JOIN UNNEST performs an inner Cartesian product between the parent row and its array elements. If an order contains an empty array ( line_items = [] ) or a NULL array, the parent order is completely eliminated from the result set! In financial reporting, calculating total revenue or order counts using CROSS JOIN UNNEST will silently drop all cancelled, refunded, or service orders with zero line items, corrupting financial reports.
The Production Solution: LEFT JOIN UNNEST ... ON TRUE
To preserve parent rows whose arrays are empty or NULL , you must use a LEFT JOIN with the join condition ON TRUE : -- Preserves parent orders even if line_items is empty or NULL
- SELECT
- o.order_id,
- o.order_timestamp,
- item.sku,
- COALESCE(item.quantity, 0) AS quantity
- FROM `ecommerce.orders` AS o
- LEFT JOIN UNNEST(o.line_items) AS item ON TRUE;
- Correlated Subqueries on Arrays (In-Place Transformation)
Rather than flattening an array via UNNEST , performing transformations, and re-aggregating using ARRAY_AGG (which incurs expensive shuffle overhead), BigQuery allows you to query arrays in-place using correlated subqueries inside the SELECT projection: -- Apply a 10% discount to all items inside the nested array without flattening the table
- SELECT
- order_id,
- ARRAY(
- SELECT AS STRUCT
- item.sku,
- item.quantity,
- ROUND(item.unit_price * 0.90, 2) AS discounted_price
- FROM UNNEST(line_items) AS item
- ) AS discounted_line_items
- FROM `ecommerce.orders`;
- 7. Reshaping Data: PIVOT and UNPIVOT
BigQuery provides native PIVOT and UNPIVOT operators to cross-tabulate and reshape relational tables without requiring verbose, multi-pass CASE statements or UNION ALL scans.
The PIVOT Operator (Rows to Columns)
PIVOT rotates rows into columns by aggregating a metric expression for each distinct value specified in an input list: -- Syntax Structure
SELECT * FROM from_item
PIVOT (aggregate_function(value_column) FOR pivot_column IN (value1, value2, ...)) -- Production Example: Rotating quarterly sales by region
- SELECT *
- FROM (
- SELECT region, quarter, sales_amount
- FROM `retail.quarterly_sales`
- )
- PIVOT (
- SUM(sales_amount)
- FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4')
- );
Key Characteristics of PIVOT :
Requires an explicit aggregate function ( SUM , AVG , COUNT , MAX , MIN ).
The values specified in the IN clause must be constant literals (static compilation). Dynamic pivot lists generated at runtime are not supported in standard GoogleSQL; dynamic pivots require procedural SQL execution via EXECUTE IMMEDIATE .
Any column in the input table not referenced in the aggregate expression or the pivot column automatically becomes an implicit GROUP BY grouping key (e.g., region ).
The UNPIVOT Operator (Columns to Rows)
UNPIVOT normalizes a wide, cross-tabulated dataset into narrow key-value rows. This is essential when ingesting wide spreadsheets or denormalized dimension matrices into star schemas: -- Production Example: Normalizing quarterly columns back into time-series rows
- SELECT
- region,
- quarter_name,
- revenue
- FROM `retail.wide_quarterly_reports`
- UNPIVOT (
- revenue FOR quarter_name IN (Q1_revenue AS 'Q1', Q2_revenue AS 'Q2', Q3_revenue AS 'Q3', Q4_revenue AS 'Q4')
- );
Handling Nulls in UNPIVOT :
UNPIVOT EXCLUDE NULLS (Default): Rows where the unpivoted metric value is NULL are automatically filtered out of the result set.
UNPIVOT INCLUDE NULLS : Preserves rows with NULL metric values, emitting explicit null records for missing quarters.
8. Architectural Anti-Patterns and Exam Traps
Production Scenario Architectural Anti-Pattern Correct Google Cloud Architecture
Global Window Sorting ; An engineer executes ROW_NUMBER() OVER (ORDER BY event_time) on a 2 billion-row clickstream table without a PARTITION BY clause to generate an auto-incrementing ID. Omitting PARTITION BY on petabyte-scale datasets forces all records across all slots to route through the Jupiter network to a single mixer/slot for serial evaluation, causing out-of-memory errors. If an absolute sequential ID is not mandatory, use GENERATE_UUID() for unique keys. If partitioning is required, always partition by high-cardinality keys ( PARTITION BY tenant_id, session_id ) to distribute computation across slots.
Deduplication with Ties ; A financial pipeline uses QUALIFY RANK() OVER (PARTITION BY transaction_id ORDER BY ingested_at DESC) = 1 to deduplicate incoming streaming ledger entries. If two events arrive with identical millisecond timestamps, RANK() assigns 1 to both records. As a result, both duplicate records survive the filter, violating database uniqueness constraints. Use ROW_NUMBER() . ROW_NUMBER() guarantees strictly unique 1-based sequential integers ( 1, 2... ), ensuring exactly one record survives. Add a secondary tie-breaker if deterministic selection is required ( ORDER BY ingested_at DESC, payload_hash ASC ).
Navigating Partition Endpoints ; A pipeline computes customer retention by comparing a user's initial signup status with their most recent status using LAST_VALUE(status) OVER (PARTITION BY user_id ORDER BY event_time) . Relying on default framing causes LAST_VALUE() to evaluate RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW , returning the current row's value instead of the latest partition state. Explicitly append ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to the window specification, or utilize FIRST_VALUE(status) OVER (PARTITION BY user_id ORDER BY event_time DESC) .
Missing Parent Rows in Analytics ; A financial audit query calculates daily customer transaction volume by running SELECT c.id, t.amount FROM customers c, UNNEST(c.transactions) t . Customers who opened accounts but made zero transactions disappear from the report. Using implicit comma join syntax ( FROM c, UNNEST(...) ) which executes a CROSS JOIN , dropping rows where the repeated field is empty or NULL . Use LEFT JOIN UNNEST(c.transactions) AS t ON TRUE . This ensures customers with zero transactions are retained in the output with NULL transaction values.