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

A report built from five nested CTEs passed syntax validation, but the final total didn’t match the ledger. The Prediction Chamber decided to treat analytical SQL as a program, subjecting it to programmatic review.

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

WITH terminal_missions AS (
    -- Grain: one row per terminal mission
    SELECT mission_id, fortress_id, terminal_status, completed_at
    FROM missions
    WHERE terminal_status IN ('completed', 'failed')
),
fortress_daily AS (
    -- Grain: one row per fortress per UTC date
    SELECT
        fortress_id,
        CAST(completed_at AS DATE) AS completed_date,
        COUNT(*) AS attempts,
        SUM(CASE WHEN terminal_status = 'completed' THEN 1 ELSE 0 END) AS successes
    FROM terminal_missions
    GROUP BY fortress_id, CAST(completed_at AS DATE)
),
scored AS (
    SELECT
        *,
        1.0 * successes / NULLIF(attempts, 0) AS completion_rate
    FROM fortress_daily
)
SELECT * FROM scored;

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.

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.

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,
    fortress_id,
    SUM(cost) AS total_cost,
    GROUPING(mission_type) AS all_mission_types,
    GROUPING(fortress_id) AS all_fortresses
FROM mission_facts
GROUP BY GROUPING SETS (
    (mission_type, fortress_id),
    (mission_type),
    (fortress_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.

4. Versioned Dimensions Require an as-of Join

Dimension table:

fortress_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 fortress_history AS d
  ON f.fortress_id = d.fortress_id
 AND f.occurred_at >= d.valid_from
 AND f.occurred_at < COALESCE(d.valid_to, TIMESTAMP '9999-12-31 00:00:00');

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.

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.

8. Cost Optimization Cannot Alter the Data Scope

Pre-aggregation, approximate distinct counts, sampling, and materialized views can reduce computational cost, but they compromise freshness, accuracy, or drill-down capability. Reports must clearly 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.