13.1 Advanced SQL in BigQuery: Window Analytic Functions, ARRAY/STRUCT, and PIVOT/UNPIVOT

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

1. 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).
2. 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).
3. 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:

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

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

1. Cleaner, Maintainable SQL: Removes unnecessary intermediate subqueries and CTE layers.
2. 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:

1. Repetition Level: Records at what depth in the schema tree the value repeats (distinguishing items belonging to the same order vs. the next order).
2. 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:

1. Requires an explicit aggregate function (SUM, AVG, COUNT, MAX, MIN).
2. 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.
3. 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