The difference between a senior analyst and a junior one is rarely SQL fluency. It’s the five-minute sweep the senior runs before sending a report — the reflex check that catches the NULL in a denominator, the duplicate customer row, the refund that snuck into gross revenue. Those are the bugs that make a stakeholder stop trusting you for a quarter. Shipping clean queries means catching them first.

This post is the five-check pre-flight audit. Each one takes under a minute. Each one has saved a senior analyst from a bad week multiple times.

Before you send any number to a stakeholder, run these five in order:

That’s it. No tool, no dbt tests, no data observability product. Just five queries you can type from memory in under five minutes total. The rest of this post is the muscle memory.

Every time you join two tables or aggregate a metric column, ask: "what does NULL mean here, and how many do I have?" Silent NULLs on a join key mean rows silently drop from the result. Silent NULLs in a SUM() mean they’re treated as zero — which might be what you want, or might underreport by 15%.

-- Quick null census for a given table. Adjust columns to taste.
SELECT
  COUNT(*)                                   AS rows,
  COUNT(customer_id)                         AS customer_id_non_null,
  COUNT(*) - COUNT(customer_id)              AS customer_id_null,
  COUNT(purchase_amount)                     AS amount_non_null,
  COUNT(*) - COUNT(purchase_amount)          AS amount_null,
  COUNT(DISTINCT customer_id)                AS distinct_customers
FROM fact_purchases;

`COUNT(column)` skips NULLs; `COUNT(*)` counts every row. The difference tells you exactly how many rows have a NULL in that column. Do this for every join key in every fact table you touch. If 2% of `fact_sessions.customer_id` is NULL, your "sessions per customer" number is low by about 2% after you join to `dim_customers` — and nobody told you.

If you INNER JOIN on a nullable column, NULL rows drop silently. If you LEFT JOIN, NULL rows stay but their right-side columns are NULL — and if you then SUM a right-side column, those NULLs are quietly ignored. Both cases produce a number that looks correct and is not. The NULL census is how you catch it.

The NULL-join gotcha that fools everyone

Dimension tables are supposed to have one row per entity. In practice, they routinely don’t — merge conflicts, botched ETL reruns, customers who signed up twice with different capitalizations. A duplicate in a dimension silently doubles every aggregate that joins to it.

The senior-level dedup check isn’t `SELECT COUNT(DISTINCT id) FROM table` — that only catches exact ID duplicates. The real check uses ROW_NUMBER to find near-duplicates on business-key columns:

SELECT *
FROM (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY LOWER(email), LOWER(first_name || ' ' || last_name)
      ORDER BY signed_up_at ASC
    ) AS dup_rank
  FROM dim_customers
) x
WHERE dup_rank > 1;

That finds every row whose (email, full name) combination is a second, third, fourth occurrence. If the query returns 0 rows, the dimension is clean. If it returns a handful, escalate to the data team (and deduplicate in your own query before joining).

This is the one that burns people. Most fact tables include both positive transactions (purchases) and negative ones (refunds, chargebacks, reversals). A naive `SUM(purchase_amount)` looks right — until you realize it already nets refunds out, which is NOT what the stakeholder who asked for "total revenue" meant. Or the other way: a table where refunds live as separate rows with a `type = 'refund'` flag, and the naive sum overcounts revenue by ignoring refunds entirely.

-- Audit: how much negative/reversal value is hiding in the aggregate?
SELECT
  SUM(CASE WHEN purchase_amount > 0 THEN purchase_amount ELSE 0 END) AS gross_positive,
  SUM(CASE WHEN purchase_amount < 0 THEN purchase_amount ELSE 0 END) AS gross_negative,
  SUM(purchase_amount)                                               AS net,
  COUNT(CASE WHEN purchase_amount < 0 THEN 1 END)                    AS refund_row_count,
  COUNT(*)                                                           AS total_rows
FROM fact_purchases
WHERE purchased_at >= DATE('now', '-30 days');

That one query tells you: how much gross positive revenue there was, how much negative (refunds) is netting in, and how many refund rows exist. If the stakeholder wanted gross revenue, use the first. If they wanted net, use the third. **Always ask which one they meant** — it’s the single clarifying question that saves the most report rewrites.

The last two checks are small but catch a disproportionate number of bugs. First: **what date range does the query actually cover?** Stakeholders say "last 30 days" and mean something different than `CURRENT_DATE - 30`. Ask.

SELECT
  MIN(purchased_at) AS first_row,
  MAX(purchased_at) AS last_row,
  COUNT(*)          AS rows_in_range,
  COUNT(DISTINCT DATE(purchased_at)) AS distinct_days
FROM fact_purchases
WHERE purchased_at >= DATE('now', '-30 days');

Second: **timezones**. Is `purchased_at` stored in UTC or the user’s local timezone? Does "today’s revenue" mean today-in-UTC or today-in-user-local? If the answer is "it depends," you’re going to produce a chart that disagrees with Finance by ~4% on any given day. Many warehouses store both — the raw UTC timestamp and a pre-converted local one — so check which column you’re filtering on before you ship.

Keep a scratch notebook (or dbt docs tab, or just a `.sql` file) open while you build the real query. Paste each of these five checks, run them against the same date range, and only send the final report once all five look right. It adds 5 minutes. It saves hours of "wait, why doesn’t this match Finance’s number?" follow-up.

The senior habit: run these in a scratch cell

None of these checks requires a data observability tool, a dbt test, or a production pipeline. They’re all just SQL, run ad-hoc, by a human who has seen enough bad reports to know what to look for. That’s the trait companies hire senior analysts for — and it’s the trait that caseSQL’s intentionally-messy schemas are designed to train.

The five quick SQL checks senior analysts run before shipping any report — NULL audits, duplicate detection, refund/reversal leakage, date-range sanity, and timezone sanity. With runnable exercises.

SQL Data Quality Checks: The Analyst’s Pre-Report Audit | caseSQL

The SQL Data-Quality Checks Senior Analysts Run Before Every Report

A stakeholder pings you Monday at 9:47 AM: "The revenue number on the exec dashboard says $4.2M, but Finance closed the quarter at $4.7M. Can you take a look?" You now have 30 minutes before the exec meeting. This post is the triage playbook — the order of checks to run, from highest-likelihood/lowest-cost to lowest-likelihood/highest-cost, so you find the bug (or confirm the number) in the time you actually have.

The wrong move is to immediately open the dashboard and start drilling in. The right move is to get two more pieces of information from the stakeholder before you query anything:

Never start a triage on someone else’s ambiguous number without pinning down what they’re actually comparing to. About 30% of "the dashboard is wrong" incidents turn out to be "we were comparing to a different definition." Find that out before you write any SQL.

Two minutes of clarification saves two hours of rework

Triage and investigation are different jobs. Triage is ordered by cheap-to-expensive; investigation is ordered by "let’s understand this thoroughly." In the first 30 minutes you’re doing triage: quickly rule out the common causes and isolate where the bug lives, not fully fix it. Investigation happens after you’ve shipped an interim answer.

The senior-analyst move is to return something to the stakeholder before the meeting: "The gap is in the timezone handling on the close, not the dashboard logic — dashboard is right, Finance’s number is using a different cutoff. I’ll send a full writeup this afternoon." That’s triage. The full writeup is the investigation that comes after.

Run these in order. The first one that finds a gap is usually the cause. Each takes 2–5 minutes; if you’ve run all seven and the gap persists, you’re in real-investigation territory.

Gross revenue includes refunds; net revenue subtracts them. Booked revenue is when the deal closed; recognized revenue is when the period earns it. A $500k gap on a $4.5M base is _exactly_ the size of the difference between "booked" and "recognized" for a SaaS company in their second quarter. Check this first.

Finance closes on the last business day; the dashboard may include the weekend. Finance uses fiscal quarters starting February; the dashboard uses calendar quarters. The timezone on the `purchased_at` column is UTC; Finance’s working calendar is local. All of these produce discrepancies that look like bugs and are actually definition gaps.

3. Are you comparing the same entity set?

The dashboard may be scoped to "active customers;" Finance may include churned ones. The dashboard may be scoped to one subsidiary; Finance may consolidate all entities. If the stakeholder’s reference is a consolidated number and your dashboard is a single-entity view, you’ll never match.

4. Has the source data changed since the number was computed?

Backfills, corrections, and late-arriving data are common. The dashboard pulled a snapshot at 7 AM; a backfill job ran at 9 AM; Finance’s number is based on the post-backfill state. Re-run the dashboard’s query now and compare to its cached value. If they differ, staleness is the cause — the dashboard is correct in its window, just behind.

5. Is there a known JOIN fanout in the dashboard query?

Pull the query that powers the dashboard tile. Look for joins on nullable or many-sided columns. Run the component queries unjoined and count rows; run the joined query and count rows. If the joined count is much bigger than expected, you’ve found the bug — revenue is being multiplied by memberships or tags. This is the single most common root cause of wrong-number dashboards.

Check NOT IN against a nullable column (see anti-joins post for why this silently breaks). Check that `WHERE status = 'active'` matches the casing of the actual values. Check that a date filter is using the correct column (authored_at vs published_at).

7. Is the BI layer doing something unexpected?

Last, because it’s rarest: the BI tool may be re-aggregating, applying a user-level filter, or using a cached extract that’s not what the SQL says. If you’ve run the raw SQL from the BI tool’s query-log and gotten the dashboard’s number, the bug is in the data, not the BI layer. If the raw SQL produces a different number than the dashboard, the bug is in the BI layer.

Juniors often jump to "the BI tool is doing something weird" because that’s the mysterious-sounding failure mode. It’s also the rarest. Run checks 1–6 first; the odds are 85% the bug is in one of those.

Don’t start from check 7

Reporting back without losing credibility

However the triage goes, the stakeholder needs a response before the 10:15 meeting. Three templates, pick the one that matches what you found:

Dashboard is correct for its scope. The $500k gap to Finance’s number
is because Finance is reporting recognized revenue (GAAP), and the
dashboard is reporting booked revenue (CRM). Both are "right" — they’re
different metrics.

If you want recognized revenue on the dashboard, I can add a tile this
week. Otherwise the cleanest fix is a footnote clarifying which one
this tile shows.

Template B: The dashboard is wrong, you know why

Dashboard is wrong. The revenue query is double-counting orders that
have multiple line items — the JOIN to order_items is fanning out.
Inflating the Q1 total by ~8%.

Temporary fix: I’ve noted the issue on the tile. Permanent fix: I’ll
roll up order_items to one row per order before the JOIN and push
the corrected version this afternoon.

Initial check suggests the gap is in the timezone handling on the
quarter-close cutoff, not in the dashboard query itself — but I want
to verify before the meeting. Can we hold the discussion on the tile
until I confirm? I’ll have an answer by noon.

The template-C move is the most underrated. Experienced analysts don’t pretend to know what they don’t know — they triage, communicate their confidence honestly, and buy the time to be right. That’s the trait that makes a stakeholder trust the next dashboard number you hand them, even when it disagrees with a reference.

A senior analyst’s triage playbook for diagnosing a wrong dashboard — the seven checks, in order, that isolate the bug without spending four hours chasing the wrong suspect.

How to Diagnose a Dashboard That Looks Wrong (Triage Playbook) | caseSQL

How to Diagnose a Dashboard That Looks Wrong

The question "what’s on one side but not the other" is asked every day in analytics teams. Users who signed up but haven’t purchased. Campaigns that fired but never delivered. Accounts with open invoices but no recent activity. Each of those is an anti-join, and each of them is a query that juniors write wrong slightly-too-often.

This post is a deliberate short read on the one pattern: what it is, the three ways to write it, why two of the three are booby traps, and when the pattern shows up in real analyst work.

An anti-join returns rows from the left table that have NO matching row on the right table. "Give me every customer who has never purchased" is the canonical example. Contrast with an inner join ("give me every customer who HAS purchased") or a left join without a filter ("give me every customer, plus their purchases if any").

The word "anti-join" itself is database-engine terminology — PostgreSQL and SQL Server will physically plan it as a special join type internally — but the pattern in analyst SQL is written as a regular join plus a filter.

The three ways to write it — only one is safe

All three of these return the same result against clean data. Exactly one of them survives real data with NULLs in it.

Form 1: LEFT JOIN … IS NULL (the safe form)

SELECT c.*
FROM dim_customers c
LEFT JOIN fact_purchases p USING (customer_id)
WHERE p.customer_id IS NULL;

The LEFT JOIN keeps every customer; the WHERE filter on the right-side column `IS NULL` keeps only the rows that didn’t match. This form is correct regardless of NULLs anywhere in the data, is readable once you’ve seen it a few times, and is how every senior analyst writes the pattern. Memorize this form; use it reflexively.

Form 2: NOT EXISTS (also safe, often faster)

SELECT c.*
FROM dim_customers c
WHERE NOT EXISTS (
  SELECT 1
  FROM fact_purchases p
  WHERE p.customer_id = c.customer_id
);

NOT EXISTS is equivalently correct and often a better query plan on large tables. It’s the form you’ll see in production pipelines where the optimizer cost matters. Slightly harder to read than the LEFT JOIN form; pick whichever your team already uses.

SELECT *
FROM dim_customers
WHERE customer_id NOT IN (
  SELECT customer_id FROM fact_purchases
);

And nobody will tell you. The query will run, return an empty set, and your dashboard will say "0 customers never purchased" the day one bad row gets inserted into fact_purchases. This has bitten every analyst at least once.

This query returns zero rows the moment one purchase has a NULL customer_id

SQL has three-valued logic: TRUE, FALSE, and UNKNOWN. Any comparison involving NULL returns UNKNOWN. `NOT IN` is logically equivalent to a chain of `<> x AND <> y AND <> z`; if any element in the subquery is NULL, the chain becomes `... AND <> NULL`, which evaluates to UNKNOWN, which the WHERE clause treats as "not true," which drops the row from the result.

Concretely: if `fact_purchases` has 10,000 rows and ONE of them has `customer_id IS NULL`, the NOT IN query returns zero rows instead of the correct answer. Your dashboard is silently wrong. The LEFT JOIN … IS NULL form doesn’t suffer from this because it’s not using `NOT IN` — it’s checking whether a specific join produced a match.

The "safe" fix for NOT IN is to add `WHERE customer_id IS NOT NULL` to the subquery. That works. It’s also the third and fourth thing a tired analyst at 5 pm on a Thursday forgets to add. Better to never write the pattern at all.

When anti-joins show up in real analyst work

Once you have the pattern in your reflex, you’ll start noticing how often the business question IS an anti-join. A partial list from real analyst tickets:

Anti-joins are one of maybe five SQL patterns that show up in every analyst’s weekly work. Internalizing the LEFT JOIN … IS NULL form (or the NOT EXISTS equivalent) and never writing NOT IN against a nullable column will quietly eliminate an entire class of wrong-dashboard incidents from your career.

The correct way to find rows that exist on one side of a relationship but not the other — and why NOT IN silently breaks the same query when NULLs enter.

Anti-Joins in SQL: The LEFT JOIN ... IS NULL Pattern | caseSQL

Anti-Joins and the LEFT JOIN ... IS NULL Pattern

Every nonprofit with more than a few thousand donors has a duplication problem. Sometimes it is small — a stray Jen vs Jennifer in the spring appeal list. Sometimes it is large enough to misstate annual giving on the 990. The work of finding and merging those duplicates lives almost entirely in SQL, because the underlying matching logic is row-level set work that no CRM does well by default. This piece is a working playbook — five layers of matching, a survivorship strategy, and the operational guardrails that keep a dedup pass from becoming a one-way data destruction event.

The schema we will work against has three tables. `donors(donor_id, first_name, last_name, email, phone, address1, city, state, zip, created_at)` is the master list. `gifts(gift_id, donor_id, amount, gift_date, campaign_code)` records every contribution. `donor_aliases(alias_id, donor_id, source, alias_text)` is the audit table you will build along the way — every time you decide two records are the same person, you log the prior identifier here so the merge is reversible. Reversibility is the single feature that distinguishes a serious dedup project from a destructive one.

The duplicates are not random. They cluster around three intake patterns. **Multi-channel intake** is the biggest source — the same person gives once on the website (autocompleted "Jennifer M Lopez", lowercased email), then mails a check next quarter (data-entry tech types "Jen Lopez", uppercase email), then attends a gala where the badge says "Jenn Lopez". Three records, one human. **Household conflation** is the second — spouses share an address and sometimes a phone, and a careless import treats the household as a single donor or splits a couple into duplicates depending on which way the schema bends. **Imports from acquired lists** are the third — when a chapter merger or a lapsed-donor reactivation campaign brings in a third-party list, the new rows almost always overlap the existing master.

Notice that all three of these produce duplicates whose fields **do not match exactly**. Nicknames, casing drift, missing middle initials, mistyped ZIPs, gendered last-name changes. A naive `GROUP BY first_name, last_name` will miss most of them. That is why the playbook is layered.

Three concrete costs justify the work. They are also the three numbers a dev director will quote in a board meeting if asked why a data analyst is spending a week on this.

The queries in this guide use ANSI SQL plus three extensions: `LOWER`, `TRIM`, and `REGEXP_REPLACE` (universal); `SOUNDEX` (Postgres has it as a contrib function via the fuzzystrmatch extension; SQL Server ships it natively); and `LEVENSHTEIN` (Postgres fuzzystrmatch, BigQuery `EDIT_DISTANCE`, Snowflake `EDITDISTANCE`). Where dialects differ I have called out the substitution.

The dialect note

Layer 1 — Exact match (the false floor)

Every dedup pass starts with the trivially obvious matches: rows where every important field is byte-identical. They almost never exist in raw data, but the query is the floor — a sanity check, and the place to confirm the schema before layering anything more interesting.

-- Layer 1: rows that are byte-identical on the high-signal fields.
-- In practice this finds maybe 1-2% of true duplicates. The point is to
-- shake out CRM ingestion bugs (the same record imported twice).
SELECT
  LOWER(email)                                  AS norm_email,
  LOWER(first_name) || ' ' || LOWER(last_name)  AS norm_name,
  COUNT(*)                                      AS rec_count,
  STRING_AGG(donor_id::text, ', ')              AS donor_ids
FROM donors
WHERE email IS NOT NULL
GROUP BY 1, 2
HAVING COUNT(*) > 1
ORDER BY rec_count DESC;

Two design choices in that query are worth calling out. The first: even at "exact match" we are already lowercasing, because the email pipeline almost certainly mixes cases. Treating `Jen@example.com` and `jen@example.com` as different rows is a CRM bug, not a feature. The second: we group by the pair (email, name) rather than just email — because a shared family email (`smiths@example.com` for the parents) is a real-world pattern and we do not want to merge a household into a single donor by accident.

Most "exact" dupes are actually exact-after-normalization. Strip whitespace, lowercase everything, drop punctuation, and a surprising fraction of the duplicate set falls out. The trick is to compute the normalized fields once, in a CTE, and then group on those.

WITH normalized AS (
  SELECT
    donor_id,
    LOWER(TRIM(email))                                            AS n_email,
    LOWER(REGEXP_REPLACE(TRIM(first_name), '[^a-z]', '', 'gi'))   AS n_first,
    LOWER(REGEXP_REPLACE(TRIM(last_name),  '[^a-z]', '', 'gi'))   AS n_last,
    REGEXP_REPLACE(COALESCE(zip, ''), '\D', '', 'g')             AS n_zip5
  FROM donors
)
SELECT
  n_email,
  n_first,
  n_last,
  LEFT(n_zip5, 5)              AS n_zip5,
  COUNT(*)                     AS rec_count,
  ARRAY_AGG(donor_id ORDER BY donor_id) AS donor_ids
FROM normalized
GROUP BY 1, 2, 3, 4
HAVING COUNT(*) > 1
ORDER BY rec_count DESC;

Two things change here. We strip every non-alpha character from names — apostrophes, hyphens, periods after middle initials, accent marks if your collation passes them through — because every one of those is a vector for two genuine duplicates to look unequal. We also normalize the ZIP to a five-digit prefix; ZIP+4 variants are a common reason a household pair fails to match on a literal `=`.

Normalization gets you the duplicates that are typographically close. It does not get you the ones where the name was **typed by ear** — a phone-bank volunteer transcribing a pledge, an event check-in writing what they hear. "Catherine" and "Kathryn" share zero characters in their first three letters but hash to the same Soundex code. Phonetic indexing is the cheapest way to catch that class of dupe.

-- Layer 3: phonetic match on last name within the same ZIP5.
-- The ZIP5 anchor is essential — Soundex collisions are common
-- (Smith/Smyth, but also Lee/Lay), so we need a second axis to keep
-- the candidate set tractable.
SELECT
  SOUNDEX(last_name)        AS last_soundex,
  LEFT(REGEXP_REPLACE(zip, '\D', '', 'g'), 5) AS zip5,
  COUNT(*)                  AS rec_count,
  ARRAY_AGG(donor_id || ' (' || first_name || ' ' || last_name || ')'
            ORDER BY donor_id)                  AS candidates
FROM donors
WHERE last_name IS NOT NULL
  AND zip       IS NOT NULL
GROUP BY 1, 2
HAVING COUNT(*) > 1
ORDER BY rec_count DESC;

Soundex hashes "Robert" and "Rupert" to the same code. It is meant to produce a small set of candidates that a human (or a higher-precision metric like Levenshtein) reviews. Never auto-merge on Soundex alone. The point of the layer is to surface candidates the lower layers missed, not to make decisions.

Soundex is a candidate generator, not a verdict

Where Soundex falls short — and it does, on names of non-English origin in particular — Metaphone and Double Metaphone are the next step up. Postgres ships both via fuzzystrmatch. Snowflake users typically wrap a JavaScript UDF; BigQuery users live with Soundex. The shape of the query is identical: hash the name, group by the hash within a ZIP or city, surface clusters with more than one row.

Layer 4 — Edit distance with Levenshtein

Phonetic codes catch sound-alikes; edit distance catches typo-alikes. Levenshtein returns the minimum number of single-character edits — insert, delete, substitute — that turn one string into another. "Marshall" vs "Marshal" is distance 1. "Stephen" vs "Steven" is distance 2. The metric is well-understood and ships in every modern warehouse, so the SQL is the easy part. Choosing the threshold and the candidate gate is where the work lives.

-- Layer 4: Levenshtein within a ZIP5 cohort.
-- A self-join on donors is O(n^2) without a gating predicate, so we
-- anchor on ZIP5 first to keep candidate pairs tractable.
SELECT
  a.donor_id          AS donor_a,
  b.donor_id          AS donor_b,
  a.first_name || ' ' || a.last_name AS name_a,
  b.first_name || ' ' || b.last_name AS name_b,
  LEVENSHTEIN(LOWER(a.last_name),  LOWER(b.last_name))  AS last_dist,
  LEVENSHTEIN(LOWER(a.first_name), LOWER(b.first_name)) AS first_dist
FROM donors a
JOIN donors b
  ON a.donor_id < b.donor_id
 AND LEFT(REGEXP_REPLACE(a.zip, '\D', '', 'g'), 5)
   = LEFT(REGEXP_REPLACE(b.zip, '\D', '', 'g'), 5)
WHERE LEVENSHTEIN(LOWER(a.last_name),  LOWER(b.last_name))  <= 2
  AND LEVENSHTEIN(LOWER(a.first_name), LOWER(b.first_name)) <= 2
ORDER BY last_dist + first_dist, name_a;

Two thresholds are conservative defaults. **Distance ≤ 1** on last name plus **distance ≤ 2** on first name catches the majority of single-keystroke typos and short nicknames without a flood of false positives. The right threshold for your data is empirical — generate the candidate set, sample 50 pairs, count how many are real duplicates, and tune. Most analysts settle around (1, 2) for last + first; teams with very dirty intake data go to (2, 3) and pay for it with a longer review queue.

A self-join on a 200,000-row donor table without any gating predicate is 200,000 × 200,000 = 40 billion candidate pairs. Almost any column you can anchor on — ZIP5, last-name first-letter, phonetic code — collapses that to thousands or millions. The choice of anchor is a tradeoff between recall (an anchor that is too tight misses cross-ZIP duplicates from people who moved) and runtime. ZIP5 plus Soundex is the most defensible default.

Why the ZIP5 gate matters

No single layer is decisive. The serious version of donor dedup is a composite score across four to six signals — name similarity, email similarity, address similarity, phone match, household-anchor match — each weighted by how informative it is, with a threshold above which a pair is auto-merged and a band below which a pair goes to a review queue.

-- Layer 5: a composite score in [0, 100]. Each signal contributes
-- a fixed weight; weights are tuned empirically and version-controlled
-- (think dbt model with an annotated yaml of weights).
WITH pairs AS (
  SELECT
    a.donor_id AS donor_a, b.donor_id AS donor_b,
    LEVENSHTEIN(LOWER(a.last_name),  LOWER(b.last_name))  AS last_dist,
    LEVENSHTEIN(LOWER(a.first_name), LOWER(b.first_name)) AS first_dist,
    CASE WHEN LOWER(a.email) = LOWER(b.email)             THEN 1 ELSE 0 END AS email_eq,
    CASE WHEN REGEXP_REPLACE(a.phone, '\D', '', 'g')
            = REGEXP_REPLACE(b.phone, '\D', '', 'g')     THEN 1 ELSE 0 END AS phone_eq,
    CASE WHEN LEFT(REGEXP_REPLACE(a.zip, '\D', '', 'g'), 5)
            = LEFT(REGEXP_REPLACE(b.zip, '\D', '', 'g'), 5) THEN 1 ELSE 0 END AS zip_eq,
    CASE WHEN SOUNDEX(a.last_name) = SOUNDEX(b.last_name) THEN 1 ELSE 0 END AS soundex_eq
  FROM donors a
  JOIN donors b ON a.donor_id < b.donor_id
   AND LEFT(REGEXP_REPLACE(a.zip, '\D', '', 'g'), 5)
     = LEFT(REGEXP_REPLACE(b.zip, '\D', '', 'g'), 5)
)
SELECT
  donor_a, donor_b,
  -- Each signal contributes up to its weight; max possible = 100.
  (CASE WHEN last_dist  = 0 THEN 30 WHEN last_dist  <= 2 THEN 20 ELSE 0 END) +
  (CASE WHEN first_dist = 0 THEN 20 WHEN first_dist <= 2 THEN 12 ELSE 0 END) +
  (email_eq   * 25) +
  (phone_eq   * 15) +
  (zip_eq     *  5) +
  (soundex_eq *  5)        AS match_score
FROM pairs
WHERE
  -- Pre-filter: at least one strong signal must fire, or we waste cycles.
  (last_dist <= 2 OR email_eq = 1 OR phone_eq = 1)
ORDER BY match_score DESC;

A score above ~70 with no contradicting field (different birthdate, different known-good phone) is a safe auto-merge. A score in the 40–70 band goes to a review queue — a human looks at the candidate pair, picks merge or no-merge, and the decision feeds back into the audit log. Below 40, drop. Calibrate the bands against a hand-labeled validation set of a few hundred pairs; the weights and thresholds drift over time as the underlying intake patterns change.

Once two records are flagged as the same person, you have to choose which one survives. The **survivorship rule** is a deterministic policy that picks one row per cluster as the master and folds the others into it. The rule should be written down once and applied uniformly — that is the difference between a defensible dedup pass and one a board member can rip apart in a meeting.

-- A practical survivorship policy: keep the row with the most non-null
-- high-signal fields, breaking ties by lifetime giving, then by oldest
-- created_at (preserves the historical donor_id whenever possible).
WITH cluster_members AS (
  SELECT
    cluster_id,                        -- assigned by the dedup workflow
    d.donor_id,
    d.created_at,
    (CASE WHEN d.email   IS NOT NULL THEN 1 ELSE 0 END
   + CASE WHEN d.phone   IS NOT NULL THEN 1 ELSE 0 END
   + CASE WHEN d.address1 IS NOT NULL THEN 1 ELSE 0 END
   + CASE WHEN d.zip     IS NOT NULL THEN 1 ELSE 0 END) AS field_completeness,
    COALESCE((SELECT SUM(amount) FROM gifts g WHERE g.donor_id = d.donor_id), 0)
                                                       AS lifetime_giving
  FROM donor_clusters c
  JOIN donors         d ON d.donor_id = c.donor_id
)
SELECT DISTINCT ON (cluster_id)
  cluster_id,
  donor_id    AS surviving_donor_id
FROM cluster_members
ORDER BY
  cluster_id,
  field_completeness DESC,
  lifetime_giving    DESC,
  created_at         ASC;     -- oldest record wins ties

Two notes. First, `DISTINCT ON` is Postgres-specific; in Snowflake or BigQuery you would use a `ROW_NUMBER() OVER (PARTITION BY cluster_id ORDER BY ...)` window with a `WHERE rn = 1` filter. Same shape, different syntax. Second, **never delete the merged-away rows.** Soft-delete by setting a `merged_into` foreign key on each non-surviving record pointing at the surviving one. Future imports will hit those tombstone rows when they collide on legacy donor_id, and the system can resolve them automatically. Reversibility is the lever that lets you sleep at night.

A one-off SQL pass is a science project. The dedup workflow becomes infrastructure when three pieces are in place:

The composite query reruns weekly — on a schedule, not manually. New donor records flow in through the week; old ones get edits; the candidate set shifts. Treating the dedup pass as a recurring batch (a `dbt build --select donor_dedup` or a `cron` line) is what turns it from a project into a process.

These are the failure modes that show up in every nonprofit dedup retrospective. Knowing them in advance is most of the battle.

The single best instinct to develop is **bias toward the review queue.** When in doubt, surface the pair to a human. The cost of an extra item in the review queue is a few seconds of reviewer time. The cost of an incorrect auto-merge — collapsing two real donors into one and losing the giving history of one of them — is a phone call to the development director and a multi-hour reconstruction job. Set the auto-merge threshold high. Trust the queue.

If you want hands-on practice with the underlying patterns — Levenshtein, Soundex, candidate-set gating, composite scoring — the AML / Fraud Analytics path on caseSQL uses the same techniques against a sanctions-screening problem. Different domain, identical SQL. Once you are fluent in the matching layer, swapping the data is a thirty-minute job.

How to deduplicate donor records in SQL: exact match, normalized strings, soundex, Levenshtein, and composite scoring. Includes a survivorship strategy and a review-queue workflow.

Donor Deduplication in SQL: A Practical Playbook | caseSQL

Donor Deduplication in SQL: A Practical Playbook

Senior analysts don’t have a magical SQL skill juniors lack. They have a specific workflow they run before they trust a single number the database returns. The workflow comes from being burned — every senior analyst can tell you about the quarterly review where their number was off by 30% because of a silent join. This post is that workflow, distilled. Five steps, in order, with exercises.

Databases are messy because they’re alive

A database is not a dataset. A dataset is a snapshot: frozen in time, reasoned about as a static artifact, teachable by a course. A database is a living system: being written to by ten different services, backfilled by Tuesday’s cron, shaped by three product managers’ choices over five years, with a schema that still carries scars from the 2021 migration that went halfway.

When you query a database, you’re asking a question of a system that has layers of sediment: the original schema, the first emergency hotfix, the second schema migration that nobody named, the columns added for a feature that shipped and was deprecated, the NULLs that mean "not recorded" in one table and "not applicable" in another. Every one of those layers is a potential lie.

Senior analysts know this in their bones. They treat every new table as hostile until proven otherwise. That’s not paranoia — it’s correctness.

Tables lie by omission (NULL instead of missing), by duplication (same entity counted twice with different IDs), and by drift (column semantics changed on a deploy you weren’t told about). All three are invisible to the query engine. Only the analyst can catch them.

The three lies a database tells

Start with the data dictionary (or the lack of one)

The first thing a senior analyst does in a new database is not write a query. It’s look for the data dictionary — the document that says what each table and column means, who owns it, and what the known gotchas are.

About 30% of companies have a real, maintained data dictionary. Another 30% have a stale one in a wiki page that was last updated in 2022. The remaining 40% have nothing — you ask the person who built the schema, if they still work there, or you reverse-engineer it from dbt models, or you piece it together from SQL written by the analyst who was there before you.

Either way, step 1 is to write your own. It doesn’t have to be elaborate. For every table you’re going to query, spend five minutes writing: what does each column mean in one sentence, what’s the grain (one row per what?), and what joins are expected. Do this in a `schema.md` file in your work repo. Six months from now, future-you will thank present-you.

This takes five minutes and surfaces three things almost every time: a table that’s much bigger than you expected (hint: probably double-writes), a time range that doesn’t go back as far as you thought (hint: the backfill stopped in 2023), and a table you didn’t know existed (hint: it’s the one you actually need).

Profile: NULLs, duplicates, inconsistencies

Once you know what tables exist, profile the ones you’ll actually query. Profiling is the five-minute cousin of a full data-quality audit. You’re not trying to be thorough — you’re trying to surface the obvious lies before they bite you.

Notice the `LOWER(TRIM(...))` in the duplicate check. Email case-sensitivity and leading-whitespace bugs are the two ways duplicate emails most commonly slip past the uniqueness check. Never trust `COUNT(DISTINCT email)` on a raw column; always normalize first.

Every team has folklore — numbers that circulate in slide decks and all-hands meetings, numbers everyone "just knows," numbers that were true in 2023 and have been repeated ever since. Part of the senior-analyst job is reconciling what the data says with what the folklore says.

The reconciliation itself is the skill. The folklore usually has a kernel of truth, which means the answer isn’t "the data is right, the folklore is wrong." It’s "the folklore was right _under these assumptions_; under current assumptions, the number is different; here’s why." That’s a useful answer. "Your number is wrong" is not a useful answer.

Concrete move: when a stakeholder cites a number you can’t reproduce from the database, your first question is "what definition was this using?" You’ll usually find that the 2023 version filtered differently, used a different time window, or excluded a segment the current number includes. Half the value of a senior analyst is being the person who writes the new definition down so nobody has to re-litigate it.

If your org has a dashboard everyone trusts, and you’re about to report a number that contradicts it, stop. Don’t publish the new number. First reproduce the dashboard number from the database, figure out why it’s different from your new query, and write up the delta. Reporting a conflicting number without explanation is the fastest way to lose stakeholder trust, even if your number is actually more correct.

The dashboard-of-record problem

A working mental model: assume the data lies

Tie it together with one mental model you can drill into reflex: **assume the data lies until proven honest.** Every query is innocent until guilty-until-proven-innocent.

In practice this means: before you hand a number to anyone, you’ve done three things. You’ve checked the scale (does the order of magnitude match a rough back-of-envelope?). You’ve checked the delta (is the change from last period believable without an external event?). You’ve checked the top row (does the biggest contributor make sense?). All three take 90 seconds. All three catch the vast majority of query bugs.

The first time you use this pass, it catches a bug. You find a join fanning out, or a filter that was tighter than you thought, or a campaign name that broke out of its category. After the hundredth time, it’s muscle memory: every headline number gets the three-check pass before it leaves your laptop. That’s the workflow. That’s what "senior analyst" looks like when you zoom all the way in.

After a year or two on any production database, you’ll develop a mental taxonomy of the bugs that come up again and again. The categories differ by org, but the shape is always similar:

Every one of these has the same fix — you catch it by running the sanity pass, then you write a CTE or a filter that documents the guard explicitly at the top of your query, with a comment explaining why. Six months later, when another analyst copies your query, the guard carries forward. That’s how institutional knowledge gets encoded in a database team.

Real databases are inconsistent, half-documented, and full of edge cases. A senior-analyst workflow for profiling, sanity-checking, and trusting-but-verifying before you report numbers.

Data Quality

The SQL Data-Quality Checks Senior Analysts Run Before Every Report

How to Diagnose a Dashboard That Looks Wrong

Anti-Joins and the LEFT JOIN ... IS NULL Pattern

Donor Deduplication in SQL: A Practical Playbook

How to Read a Messy Database Like a Senior Analyst