Data Analytics SQL Review

Analytical SQL review

Purpose

Analytical SQL fails quietly. A join fans out and the revenue total doubles; an inner join drops the rows with no matching dimension and the count is 4% low; a filter in the WHERE clause turns a left join into an inner one. None of these raise an error, and all of them produce a number someone will act on. This skill is the ordered checklist for those failure classes, plus the cost review that stops a correct query from being unaffordable.

Prerequisites

• Inputs: the query; the grain each source table is at (one row per what?); expected row counts or an order of magnitude for the result; the metric definition it implements.
• Access: ability to run the query's plan and row counts against a representative dataset. Reviewing SQL by reading alone catches syntax and obvious logic errors, but not fan-out — that needs counts.

If nobody can state the grain of each source table, establish that first. Almost every fan-out and row-loss bug is a grain misunderstanding, and no amount of reading the SQL surfaces it.

Procedure

Pass 1 — Correctness

1. State the grain of every source and the intended grain of the output. Write it down as a sentence per table: "one row per order line", "one row per customer per day". Then check every join: joining a one-row-per-customer table to a one-row-per-order table produces one row per order — if an aggregate downstream sums a customer-level column, it is now multiplied by order count.

2. Test for fan-out with counts, not by reading. Compare the row count before and after each join. Any increase is fan-out and must be intentional. The classic silent version: a dimension table with a slowly-changing history, so the join matches multiple versions unless the validity window is in the join condition.

3. Test for row loss. Count distinct keys on the driving table before and after. An inner join used where a left join was meant, or a join on a column containing nulls, drops rows without complaint. Both fan-out and loss can occur in the same join and partially cancel, which is why counts must be checked per key, not only in total.

4. Check filter placement against join semantics.

   Pattern	Effect	Usually intended?
   Left join, filter on the right table in WHERE	Becomes an inner join; unmatched rows vanish	No — move it into the ON clause
   Left join, filter on the right table in ON	Preserves unmatched rows with nulls	Usually yes
   Filter on an aggregated column in WHERE	Applies before aggregation	Rarely; HAVING or a subquery is meant
   NOT IN against a column containing nulls	Returns no rows at all	Never — use NOT EXISTS

5. Audit null semantics. Nulls do not compare equal, are skipped by most aggregates but not by COUNT(*), and propagate through arithmetic. Check each: join keys that may be null; equality comparisons intended to include nulls; averages where nulls should have counted as zero; and string concatenation that nulls out an entire field.

6. Check the date and time handling. Which column is the event actually timestamped by — created, updated, ingested, effective? Is the range boundary inclusive at both ends (a BETWEEN on a timestamp typically loses the last day's rows unless the upper bound is handled as an exclusive next-day boundary)? Is a timezone conversion applied consistently to both the data and the boundaries? Mixed-timezone comparison is invisible in the result and shows up as a persistent small discrepancy.

7. Review window functions for frame and partition errors. Confirm the PARTITION BY matches the intended grouping grain, the ORDER BY makes the result deterministic (ties without a tiebreaker give different answers per run), and the frame clause is explicit — the default frame changes behaviour between a running total and a whole-partition aggregate.

8. Check deduplication is deterministic. A row-number-per-key deduplication with a non-unique ordering column picks arbitrarily and produces a different answer on re-run. Add a tiebreaker.

9. Confirm it implements the stated metric definition. Population, exclusions, dedup, window and timezone — read the contract rows against the query. A technically correct query implementing the wrong definition is the most expensive kind of correct.

Pass 2 — Cost

10. Read the plan for the biggest scan. Cost is dominated by the volume read, not by query length.

    Symptom in the plan	Cause	Fix
    Full scan of a partitioned table	Partition column absent from the filter, or wrapped in a function	Filter on the raw partition column
    Very large intermediate result	Join before filtering	Push predicates down before joining
    Repeated scans of one table	The same subquery evaluated multiple times	Materialise once
    Skew: one worker far slower	Join key heavily concentrated (nulls or a default value)	Salt or exclude the degenerate key
    Broadcast of a large table	Optimiser statistics stale	Refresh statistics; reorder joins

11. Check the shape of the output. SELECT * into a downstream table, an unbounded result set feeding a dashboard, or ordering a large result with no limit — each costs disproportionately for no analytical value.

12. Decide incremental vs full recompute. If this runs on a schedule over growing data, full recompute cost grows with history. Incremental needs a watermark column and a defined late-arrival window — and if the source can be updated in place, incremental will silently miss those updates unless the watermark is on the update time.

Failure modes this skill exists to prevent

• Doubled revenue from a fan-out join that looks like growth.
• The disappearing left join, caused by a WHERE-clause filter on the right table.
• NOT IN with nulls returning zero rows, read as "no matches exist".
• Non-deterministic dedup, giving a different answer on each run and destroying trust in the whole pipeline.
• Function-wrapped partition filters, turning a cheap query into a full scan while returning identical results — invisible until the bill arrives.

Data handling

Classification: inherits from the tables queried; treat as Confidential where personal, financial, or client-level data is involved. Review queries against schema, plans, and counts — not by pasting result rows containing customer data. Never embed credentials or connection strings in a query or in the review; sample outputs shown for illustration must use synthetic values. If real customer records, account numbers, or positions are supplied to support the review, flag it and do not proceed until they are removed or masked.

Boundaries

• The output is finished and the question is whether the report can ship — data-analytics-report-qa.
• The disagreement is about what the metric should mean — data-analytics-metric-definition.
• The SQL is application code inside a service (transaction boundaries, ORM behaviour, injection risk) — engineering-code-review covers that; this skill targets analytical queries.
• Schema or migration changes to the underlying tables need it-change-management for the production window.

Hand-offs

• Receives from: data-analytics-metric-definition (the contract this query must implement); engineering-code-review (query-heavy diffs referred for deeper analysis).
• Routes to: data-analytics-report-qa (a query cleared here still needs the output-level checks before publication).