SQL for Cleaning Transaction Data

This is sample teaching content. Publication dates and transaction records are illustrative.

Our sample shop sends the same transaction twice after a retry. Another row contains spaces where the identifier should be. Before calculating daily totals, decide which row represents the current transaction.

State the contract first

Assume PostgreSQL 18 and a raw table with typed numeric amounts and timestamps. Every raw row has a unique, increasing ingest_id. A transaction_id identifies one transaction. A later updated_at replaces an earlier version; if timestamps tie, the higher ingest_id wins. Confirm your source's revision semantics before adopting this rule.

Normalize before ranking

WITH normalized AS (
  SELECT NULLIF(btrim(transaction_id), '') AS transaction_id,
         amount, updated_at, ingest_id
  FROM raw_transactions
), ranked AS (
  SELECT *, row_number() OVER (
    PARTITION BY transaction_id
    ORDER BY updated_at DESC NULLS LAST, ingest_id DESC
  ) AS position
  FROM normalized
  WHERE transaction_id IS NOT NULL
)
SELECT transaction_id, amount
FROM ranked
WHERE position = 1 AND amount IS NOT NULL;

NULLIF turns an empty trimmed string into SQL null. Window ranking assigns a position within each identifier without aggregating rows. The ingest_id tie-breaker makes the choice repeatable. A missing timestamp loses to a known timestamp under NULLS LAST.

We reject a missing amount after choosing the latest revision. Filtering it earlier could silently resurrect an older amount. Negative amounts remain valid because our sample permits refunds. Another source may require a different rule.

Reconcile the result

Keep raw data and a rejection table containing ingest_id and reason. Count missing identifiers, superseded revisions, invalid winners, and accepted winners as disjoint groups. Their sum must equal the raw count. Re-run the same input and compare accepted rows, not only totals: two wrong rows can cancel each other out.

This query demonstrates cleaning rather than ingestion or a complete financial model. Currency conversion, malformed text amounts, and late corrections need separate contracts. The references below explain window ordering and NULLIF.