Data reconciliation in ETL: counts, sums and hashes for every load

#Why a green job proves nothing

An ETL job reports success when every step ran without an error. It does not report that every row arrived. An inner join to a dimension that is missing a key, a WHERE clause that is slightly too strict, or a lookup that returns nothing all drop rows quietly, and the job still goes green.

The techniques themselves, from row counts to row hashes, are in data reconciliation: techniques and a worked retail example. This post is about where they go in a pipeline: after every load, per batch, written down, and able to stop the job.

#The balance every batch must pass

A load does four things with a row. It loads it, it rejects it into an error table, it filters it out on purpose, or it loses it. The first three are accounted for. The fourth is the one you are looking for.

So every batch must satisfy:

extracted = filtered + rejected + loaded

once in rows, and once in the sum of a measure. When the two sides differ, the difference is rows the pipeline lost without telling anyone.

Three design rules make that equation possible:

1. Give every run a batch id, and stamp it on every row in staging, in the reject table and in the target. Reconcile by batch, not by date: a late till upload for Monday that arrives on Wednesday belongs to Wednesday's batch.
2. Land everything in staging, then filter. A filter in the extract query (WHERE is_training = 0) leaves nothing to count. Bring the training tills in, flag them, and leave them out of the load, so the filtered rows can be counted.
3. Sum a measure the transformation leaves alone. If the load converts currency or splits tax, carry the original amount through to the target as its own column and balance on that.

#The tables, in SQL Server

Four tables: staging, rejects, the target, and the control table that keeps the balance for every run. The control table works out what is unexplained itself, in computed columns, so nobody has to do the arithmetic.

CREATE TABLE dbo.stg_till_sales (
  batch_id      int           NOT NULL,
  store         varchar(10)   NOT NULL,
  department    varchar(20)   NOT NULL,
  business_date date          NOT NULL,
  net_sales     decimal(12,2) NOT NULL,
  units         int           NOT NULL,
  tax_code      varchar(10)   NULL,
  is_training   bit           NOT NULL DEFAULT 0
);

CREATE TABLE dbo.etl_rejects (
  batch_id      int           NOT NULL,
  store         varchar(10)   NOT NULL,
  department    varchar(20)   NOT NULL,
  business_date date          NOT NULL,
  net_sales     decimal(12,2) NOT NULL,
  reason        varchar(100)  NOT NULL
);

CREATE TABLE dbo.fact_sales (
  batch_id      int           NOT NULL,
  store         varchar(10)   NOT NULL,
  department    varchar(20)   NOT NULL,
  business_date date          NOT NULL,
  net_sales     decimal(12,2) NOT NULL,
  units         int           NOT NULL
);

CREATE TABLE dbo.etl_batch_control (
  control_id     int IDENTITY PRIMARY KEY,
  batch_id       int           NOT NULL,
  checked_at     datetime2(0)  NOT NULL DEFAULT SYSDATETIME(),
  extracted_rows int           NOT NULL,
  extracted_net  decimal(14,2) NOT NULL,
  filtered_rows  int           NOT NULL,
  filtered_net   decimal(14,2) NOT NULL,
  rejected_rows  int           NOT NULL,
  rejected_net   decimal(14,2) NOT NULL,
  loaded_rows    int           NOT NULL,
  loaded_net     decimal(14,2) NOT NULL,
  unexplained_rows AS (extracted_rows - filtered_rows - rejected_rows - loaded_rows),
  unexplained_net  AS (extracted_net - filtered_net - rejected_net - loaded_net)
);

#The procedure that fails the job

Run this as the last step of the load. It writes one row to the control table and raises an error when the batch does not balance.

CREATE OR ALTER PROCEDURE dbo.reconcile_batch @batch_id int
AS
BEGIN
  SET NOCOUNT ON;

  INSERT INTO dbo.etl_batch_control
    (batch_id, extracted_rows, extracted_net, filtered_rows, filtered_net,
     rejected_rows, rejected_net, loaded_rows, loaded_net)
  SELECT @batch_id,
    (SELECT COUNT(*) FROM dbo.stg_till_sales WHERE batch_id = @batch_id),
    (SELECT COALESCE(SUM(net_sales), 0) FROM dbo.stg_till_sales WHERE batch_id = @batch_id),
    (SELECT COUNT(*) FROM dbo.stg_till_sales WHERE batch_id = @batch_id AND is_training = 1),
    (SELECT COALESCE(SUM(net_sales), 0) FROM dbo.stg_till_sales WHERE batch_id = @batch_id AND is_training = 1),
    (SELECT COUNT(*) FROM dbo.etl_rejects WHERE batch_id = @batch_id),
    (SELECT COALESCE(SUM(net_sales), 0) FROM dbo.etl_rejects WHERE batch_id = @batch_id),
    (SELECT COUNT(*) FROM dbo.fact_sales WHERE batch_id = @batch_id),
    (SELECT COALESCE(SUM(net_sales), 0) FROM dbo.fact_sales WHERE batch_id = @batch_id);

  DECLARE @rows int, @net decimal(14,2);

  SELECT @rows = unexplained_rows, @net = unexplained_net
  FROM dbo.etl_batch_control
  WHERE control_id = SCOPE_IDENTITY();

  IF @rows <> 0 OR @net <> 0
  BEGIN
    DECLARE @msg nvarchar(2048) = CONCAT(N'Batch ', @batch_id, N' does not balance: ',
      @rows, N' rows and ', @net, N' net sales unexplained.');
    THROW 50001, @msg, 1;
  END;
END;

Call it with EXEC dbo.reconcile_batch @batch_id = 20261110;.

Three details, from Microsoft's page on THROW:

• The error number must be 50,000 or more, and THROW always raises severity 16, so whatever called the procedure sees an error, not a success.
• The statement before THROW must end with a semicolon. Leave it off the DECLARE and the procedure will not compile.
• % in the message must be doubled. Not a problem here, but it is when someone adds "differs by 2%" to the text later.

Why COALESCE(SUM(…), 0). SUM over no rows is NULL, not 0, and NULL minus anything is NULL. Without it, a batch with no rejects would leave unexplained_net empty, @net <> 0 would be unknown rather than true, and a broken batch could pass.

Why there is no tolerance. Counts and sums of the same decimal column inside one pipeline must agree exactly. A tolerance belongs between two different systems, where rounding is real. Inside a pipeline, one cent unexplained is a row or an amount that went somewhere.

#Finding the rows that went missing

The balance says how many. Key matching says which. Look for staging rows that were neither filtered, nor rejected, nor loaded:

SELECT s.store, s.department, s.business_date, s.net_sales
FROM dbo.stg_till_sales AS s
WHERE s.batch_id = 20261110
  AND s.is_training = 0
  AND NOT EXISTS (SELECT 1 FROM dbo.etl_rejects AS r
                  WHERE r.batch_id = s.batch_id
                    AND r.store = s.store
                    AND r.department = s.department
                    AND r.business_date = s.business_date)
  AND NOT EXISTS (SELECT 1 FROM dbo.fact_sales AS f
                  WHERE f.batch_id = s.batch_id
                    AND f.store = s.store
                    AND f.department = s.department
                    AND f.business_date = s.business_date);

Run the reverse too: a target row with no staging row, or a key that appears twice in the target, means the load duplicated something. A duplicate and a lost row in the same batch cancel out in the count, which is why the sum and the hash are there as well.

SELECT store, department, business_date, COUNT(*) AS copies
FROM dbo.fact_sales
WHERE batch_id = 20261110
GROUP BY store, department, business_date
HAVING COUNT(*) > 1;

#A hash of the whole batch

Counts and sums agree when a value moves between rows: a department mapping that swaps Footwear and Accessories at one store keeps every count and every total. A hash of the batch does not.

SELECT HASHBYTES('SHA2_256',
         STRING_AGG(CONVERT(varchar(max),
           CONCAT(store, '|', department, '|', business_date, '|', net_sales, '|', units)), ';')
         WITHIN GROUP (ORDER BY store, department, business_date)) AS batch_hash
FROM dbo.fact_sales
WHERE batch_id = 20261110;

Run the same query on staging, adding AND is_training = 0 and leaving out the rejected keys, and compare the two values. Equal means every loaded row is identical, column for column.

What makes it work, from Microsoft's pages on STRING_AGG and HASHBYTES:

• Convert to varchar(max) first. STRING_AGG returns the type it is given, and plain varchar input comes back capped at 8,000 bytes. A batch of thousands of rows needs max.
• WITHIN GROUP (ORDER BY …) is required. Without it the order is not guaranteed, and the same rows in a different order give a different hash.
• SHA2_256 or SHA2_512 only. The other algorithms are deprecated from SQL Server 2016. STRING_AGG needs SQL Server 2017 or later.
• Both sides must hold the same types. 12400.00 and 12400 are different text. If staging keeps amounts as text and the target as decimal, convert staging first.

When the hashes differ, group the same query by store to find which store's hash moved, then compare that store's rows column by column, as in comparing two tables in SQL.

CHECKSUM_AGG looks like a shortcut, and it ignores row order. But Microsoft's own page warns there is a small possibility the checksum does not change when the data does. For a check that is meant to prove nothing changed, use the hash.

#The same in PostgreSQL

The tables are the same without dbo., with boolean for is_training, timestamp for checked_at, int GENERATED ALWAYS AS IDENTITY for control_id, and the two unexplained columns written as GENERATED ALWAYS AS (…) STORED. The procedure:

CREATE OR REPLACE PROCEDURE reconcile_batch(p_batch_id int)
LANGUAGE plpgsql
AS $$
DECLARE
  c etl_batch_control%ROWTYPE;
BEGIN
  INSERT INTO etl_batch_control
    (batch_id, extracted_rows, extracted_net, filtered_rows, filtered_net,
     rejected_rows, rejected_net, loaded_rows, loaded_net)
  SELECT p_batch_id,
    (SELECT count(*) FROM stg_till_sales WHERE batch_id = p_batch_id),
    (SELECT coalesce(sum(net_sales), 0) FROM stg_till_sales WHERE batch_id = p_batch_id),
    (SELECT count(*) FROM stg_till_sales WHERE batch_id = p_batch_id AND is_training),
    (SELECT coalesce(sum(net_sales), 0) FROM stg_till_sales WHERE batch_id = p_batch_id AND is_training),
    (SELECT count(*) FROM etl_rejects WHERE batch_id = p_batch_id),
    (SELECT coalesce(sum(net_sales), 0) FROM etl_rejects WHERE batch_id = p_batch_id),
    (SELECT count(*) FROM fact_sales WHERE batch_id = p_batch_id),
    (SELECT coalesce(sum(net_sales), 0) FROM fact_sales WHERE batch_id = p_batch_id)
  RETURNING * INTO c;

  IF c.unexplained_rows <> 0 OR c.unexplained_net <> 0 THEN
    RAISE EXCEPTION 'Batch % does not balance: % rows and % net sales unexplained',
      p_batch_id, c.unexplained_rows, c.unexplained_net;
  END IF;
END;
$$;

CALL reconcile_batch(20261110);

The batch hash, with the order inside the aggregate call, which is where PostgreSQL's documentation says it must go:

SELECT encode(sha256(convert_to(
         string_agg(concat_ws('|', store, department, business_date, net_sales, units), ';'
                    ORDER BY store, department, business_date),
         'UTF8')), 'hex') AS batch_hash
FROM fact_sales
WHERE batch_id = 20261110;

concat_ws skips NULLs without leaving a gap, so every column in this list is NOT NULL. For a column that can be empty, wrap it in coalesce(col::text, '~').

#The worked example: Tuesday night's load

An invented chain with 120 stores loads the tills' sales into its warehouse every night, one row per store, department and day. Batch 20261110 finished at 02:14 with no errors. The control table said:

Invented figures. The job was green, and two rows worth 6,410.75 were gone. The key query found them: ST-022's Footwear and Accessories, 4,196.45 and 2,214.30. Both had reached staging and neither was in the reject table. The load joined staging to the store dimension with an inner join, and ST-022's row in the dimension had been end-dated the day before by mistake, so the join returned nothing for it and the rows vanished without an error.

The rest of the batch explains itself. The four training-till rows are meant to stay out. ST-014's three rows sit in the reject table with a reason, which is where its 480.00 went. Without the control table, the morning's sales report would have been 6,410.75 light and nobody would have known why until a store manager asked.

#Where the balance inside a pipeline runs out

Every check above compares the pipeline with its own staging. If the extract itself was short, staging is short too, and the batch balances perfectly. Two ways that happens in retail:

• A store's tills had not uploaded when the extract ran at 01:00. ST-047's day arrives at 03:30, after the batch closed.
• The extract query filters at source. A date range in the wrong time zone or an outdated list of store codes means some rows were never asked for.

Neither shows up in extracted = filtered + rejected + loaded. To catch them, compare the warehouse with the till database directly, at a grain both keep, after the load: a different question from the balance, asked of two systems rather than one pipeline. The POS vs ERP sales post covers the same comparison between the tills and the ERP.

That is what DataMosaic does. A check reads both systems where they are, the till database and the warehouse (SQL Server, PostgreSQL, Oracle, Snowflake, Redshift and others), lines the rows up on shared keys, and shows which match, which differ and which are missing. Its rules decide when a run fails, and rows compared, at least 1 turns an extract that quietly returned nothing into a failure. The pipeline can run the check through Mosaic's HTTP API after the load and read back a pass or a fail. On a differing row, an investigation walks through why the systems can disagree. A check only reads; Mosaic changes a system only through a workflow you build, such as re-running the load for one store and checking again.

#Questions people ask

#What is data reconciliation in ETL?

Checking after each load that the target holds what the source sent: that every extracted row was loaded, rejected or deliberately filtered, that the totals agree, and that the values did not change on the way.

#How do you reconcile source and target counts in ETL?

Count rows per batch in staging, in the reject table and in the target, and check that extracted equals filtered plus rejected plus loaded. Store the result in a control table and fail the job when the difference is not zero.

#What is a control table in ETL?

A table with one row per load run that records how many rows and how much of a measure each step saw. It turns "the job succeeded" into a balance anyone can read later, and it is where the job looks to decide whether to fail.

#Are row counts enough to validate an ETL load?

No. A lost row and a duplicated row cancel out in the count, and a value moved between rows keeps the count and the sum. Add a control total on a measure and a hash of the batch.

#Should I use CHECKSUM or HASHBYTES to compare ETL data?

HASHBYTES with SHA2_256. Microsoft's documentation says CHECKSUM_AGG has a small chance of not changing when the data does, which is the one thing a reconciliation cannot accept.