4.3 CTE, Grouping Sets, and Query Validation: Treat Analytical SQL Like a Program

Nested CTEs can clarify complex queries, but they can also propagate the same granularity error through five layers. Like any program, analytical SQL requires interfaces, testing, execution plans, and pre-deployment reconciliation.

Learning Objectives

• Use CTEs to define semantic stages rather than stacking aliases;
• Use GROUPING SETS, ROLLUP, and CUBE;
• Correctly join slowly changing dimensions;
• Validate results and costs using invariants, comparison queries, and execution plans.

1. Each CTE Has Only One Responsibility

Use a fresh DuckDB 1.5 connection for the following fixture. terminal_at is a timezone-aware timestamp for both success and failure; the fixture’s timezone-free fact and dimension timestamps are explicitly UTC.

CREATE TEMP TABLE missions AS
SELECT * FROM (VALUES
 ('M1','S1','completed', TIMESTAMPTZ '2026-08-01 23:30:00+00'),
 ('M2','S1','failed', TIMESTAMPTZ '2026-08-01 23:40:00+00'),
 ('M3','S1','completed', TIMESTAMPTZ '2026-08-02 00:00:00+00')
) AS t(mission_id,system_id,terminal_status,terminal_at);
CREATE TEMP TABLE mission_facts AS
SELECT * FROM (VALUES
 ('M1','survey','S1',10,TIMESTAMP '2026-08-01 23:30:00'),
 ('M2',NULL,'S1',20,TIMESTAMP '2026-08-01 23:40:00'),
 ('M3','survey','S1',30,TIMESTAMP '2026-08-02 00:00:00')
) AS t(mission_id,mission_type,system_id,cost,occurred_at);
CREATE TEMP TABLE system_history AS
SELECT * FROM (VALUES
 ('S1','north',TIMESTAMP '2026-08-01 00:00:00',TIMESTAMP '2026-08-02 00:00:00'),
 ('S1','south',TIMESTAMP '2026-08-02 00:00:00',NULL)
) AS t(system_id,region,valid_from,valid_to);

WITH terminal_missions AS (
    -- Grain: one row per terminal mission; terminal_at is required
    SELECT mission_id, system_id, terminal_status, terminal_at
    FROM missions
    WHERE terminal_status IN ('completed', 'failed')
),
system_daily AS (
    -- Grain: one row per system per UTC terminal date
    SELECT
        system_id,
        CAST(terminal_at AT TIME ZONE 'UTC' AS DATE) AS terminal_date,
        COUNT(*) AS attempts,
        SUM(CASE WHEN terminal_status = 'completed' THEN 1 ELSE 0 END) AS successes
    FROM terminal_missions
    GROUP BY system_id, CAST(terminal_at AT TIME ZONE 'UTC' AS DATE)
),
scored AS (
    SELECT system_id, terminal_date, attempts, successes,
           1.0 * successes / NULLIF(attempts, 0) AS completion_rate
    FROM system_daily
)
SELECT system_id, terminal_date, attempts, successes, completion_rate
FROM scored
ORDER BY system_id, terminal_date;

A CTE name describes the business result, comments specify the granularity, and fields are explicitly listed. step1, temp2, and nested SELECT * can cause schema changes to propagate silently.

The result is 1 success in 2 terminal tasks on August 1, and 1 in 1 on August 2. A completion-only timestamp cannot date failures correctly; a missing terminal timestamp must fail a quality rule rather than vanish through a filter. Convert timezone-aware timestamps to the metric timezone before casting to a date, so a session setting cannot move events across days.

2. Whether CTE is materialized depends on the database

Some query optimizers inline CTEs, while others materialize them under certain versions or configurations. Some databases apply different strategies for recursive or repeatedly referenced CTEs. A CTE is first and foremost a query structure, do not assume it inherently improves or degrades performance.

For example, PostgreSQL’s CTE documentation distinguishes side-effect-free nonrecursive queries, repeated references, and MATERIALIZED / NOT MATERIALIZED. Those options and defaults are engine-specific; confirm the version and inspect its plan.

Examine the actual execution plan, including scan bytes, shuffle, spill, and runtime. If intermediate results are reused multiple times or require quality validation, explicitly materialize them into versioned models. Doing so introduces costs related to storage, freshness, and updates.

3. GROUPING SETS Expressing Multiple Granularities in One Statement

SELECT
    mission_type,
    system_id,
    SUM(cost) AS total_cost,
    GROUPING(mission_type) AS all_mission_types,
    GROUPING(system_id) AS all_systems
FROM mission_facts
GROUP BY GROUPING SETS (
    (mission_type, system_id),
    (mission_type),
    (system_id),
    ()
);

ROLLUP(a,b) Typically generates hierarchical combinations (a,b), (a), (); CUBE(a,b) generates all possible combinations. The exact syntax and GROUPING_ID support vary by dialect.

When using NULL as a placeholder for aggregate rows, it's essential to use GROUPING() to distinguish between "all categories" and actual NULL values in the source data. A direct COALESCE(col,'ALL') would merge these two cases together.

The fixture’s true null mission_type subtotal is 20 with GROUPING(mission_type)=0; the grand total is 60 with both grouping flags equal to 1. Summing every output row would count several aggregate levels together. With k grouping columns, CUBE can describe 2^k grouping sets, so its output and cost can grow quickly. See DuckDB grouping sets.

4. Versioned Dimensions Require an as-of Join

Dimension table:

system_id, region, valid_from, valid_to

Facts should be joined to the dimension table based on the version active at the time of the event:

SELECT f.mission_id, d.region
FROM mission_facts AS f
JOIN system_history AS d
  ON f.system_id = d.system_id
 AND f.occurred_at >= d.valid_from
 AND (d.valid_to IS NULL OR f.occurred_at < d.valid_to);

The validity intervals in the dimension table must not overlap for the same key; otherwise, a fact would match multiple versions. Use tests to verify both overlap and gaps, and clearly define whether valid_to is inclusive or exclusive; half-open intervals are easier to align and join.

M3 occurs exactly at the boundary and therefore matches only the new south version. The open end uses IS NULL rather than a far-future sentinel. This inner join drops facts that have no valid dimension version; audit those rows or retain them with a left join and a match flag. Effective-time history answers which version applied then; reconstructing what was known then also requires recording-time history.

5. Start Query Testing with Small Counterexamples

Construct minimal fixtures:

• A task with no tags;
• A task with two tags;
• Two records with identical scores;
• A missing partition on a specific day;
• A denominator of zero;
• A dimension boundary exactly at valid_to;
• A NULL key;
• A late-event backfill spanning across dates.

Manually compute the expected results for each case. In large datasets, "the numbers look reasonable" is not sufficient for testing.

6. Invariants and Reference Queries

Verifiable:

Total cost is conserved before and after aggregation
Number of successes ≤ number of attempts
Completion rate lies in the range [0, 1]
Business keys in output are unique
Number of records lost in inner join equals number of explicitly rejected records
Grouped sum equals total sum (when groups are non-overlapping)

Write a simple, straightforward reference query for key metrics, and compare it against the optimized version using sample data and recent partitions. Even when both queries share the same erroneous logic, they may still produce incorrect results independently, therefore, additional validation is required by reconciling with the metric contract and the source system.

7. Read the Execution Plan Instead of Guessing Performance

Focus on:

• Full table scans versus partition pruning;
• Join algorithms and the build/probe sides;
• Discrepancies between actual and estimated row counts;
• Shuffle data volume;
• Sort and window spills;
• Repeated scans of the same large table;
• Whether filtering is pushed down;
• Data skew on key columns.

WHERE DATE(timestamp_col)=... May prevent certain systems from leveraging raw time partitions or indexes. Using half-open ranges is often more precise:

WHERE occurred_at >= TIMESTAMP '2026-08-01 00:00:00'
  AND occurred_at <  TIMESTAMP '2026-09-01 00:00:00'

Whether actual pruning occurs still depends on the specific platform's execution plan.

EXPLAIN describes a plan; EXPLAIN ANALYZE executes the query to collect runtime measurements. A small local fixture verifies semantics, not production partition pruning or distributed resource cost.

8. Cost Optimization Cannot Alter the Data Scope

Preaggregation and materialized views can preserve exact metrics within their supported grain, while changing refresh behavior or drill-down options. Approximate distinct counts and sampling introduce statistical error. Treat these tradeoffs separately and state:

• The data cutoff time;
• Whether the result is approximate and, if so, the guaranteed error margin;
• The refresh frequency;
• The model version used;
• Which dimensions cannot be further broken down.

Do not silently replace user-level distinct counts with event counts solely to scan fewer bytes.

9. SQL Release Checklist

1. The granularity of each CTE is explicitly documented;
2. Join cardinality has been validated;
3. Numerator, denominator, and NULL semantics are formally specified in the contract;
4. Time ranges use a consistent timezone and half-open boundaries;
5. Window ordering and frame definitions are clearly defined;
6. Fixtures and invariants have passed verification;
7. The query has been reconciled with reference metrics;
8. Execution plan and resource usage are within budget;
9. Output includes owner, freshness, and version for traceability.

Common Misconceptions

• CTEs are automatically materialized or optimized: Behavior depends on the database and version.
• All NULLs in ROLLUP represent totals: NULLs in the source data must be distinguished using GROUPING.
• Dimension tables can be joined using simple key equality: Historical facts require time-based versioning.
• A faster query means optimization has succeeded: The definition and precision of metrics cannot be silently altered.

Exercise

1. Write out the granularity of each layer in a five-layer CTE, and identify where the first change occurs.
2. Use GROUPING SETS to simultaneously compute region, type, and total, and distinguish true NULLs.
3. Construct two overlapping dimension versions and observe how an as-of join replicates facts.
4. Write minimal fixtures, conservation laws, and a reference implementation for a completion rate query.

Summary

Analyzing SQL resembles writing deployable data programs. CTEs separate semantic steps, grouping sets express multiple granularities, and as-of joins preserve historical semantics. Fixtures, invariants, reconciliation, and execution plans verify correctness and operational robustness.

The next chapter moves into statistical inference: when judging differences in a population from sample data, the sampling process, uncertainty, and decision thresholds must all be explicitly included in the conclusion.