# Data Analytics SQL Review

> Reviews analytical SQL for correctness and cost: the join fan-out and row-loss traps, filters that silently change join semantics, window and grain errors, null and timezone handling, then scan volume, partition pruning, and predicate placement. Use when a query is about to feed a report or model, when results look wrong or duplicated, when a query is expensive or slow, or when reviewing someone else's analytical SQL. Trigger on 'review this query', 'why are my rows duplicated', 'this query is expensive', 'check my SQL', 'the join is wrong', 'why is this so slow'. Not for verifying a finished report's outputs against an independent source — that is data-analytics-report-qa; not for deciding what the metric should mean, which is data-analytics-metric-definition.

- Skill: `alihusains/data-analytics-sql-review` (Agent Skill)
- Install (CLI): `npx skillmds@latest add alihusains/data-analytics-sql-review`
- Raw SKILL.md: https://api.skillmd.com/api/skills/alihusains/data-analytics-sql-review/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: alihusains (https://skillmd.com/u/alihusains)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/alihusains/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).