Reconcile Warehouse Source Row Counts (2026)

TL;DR

Reconcile the same bounded population on both sides, store the two counts and their delta, and fail the batch when they differ. Then use grouped counts and key-level anti-joins to distinguish missing rows, duplicates, filters, and watermark defects.

Key Takeaways

• Compare the same business population by pinning one batch ID and a half-open source watermark window.
• Persist source count, warehouse count, delta, timestamps, and status so every reconciliation is auditable.
• Break totals into business-date and status slices before investigating individual keys.
• Check missing keys, unexpected keys, and duplicate keys because equal totals can still hide defects.
• Make the quality gate return a nonzero exit code so orchestration cannot publish a failed batch.
• Treat count equality as a completeness check, then add value aggregates and deterministic fingerprints for stronger coverage.

To reconcile warehouse source row counts reliably, count the same logical population on both sides, not two whole tables observed at unrelated times. Pin a batch ID, capture its source watermark interval, compare that interval with rows tagged by the same warehouse batch, and preserve the result in an audit table.

A raw total is only the first signal. This tutorial builds a repeatable PostgreSQL lab that finds count differences, narrows them by business slice, identifies missing and duplicate keys, and returns a failing process status that an ETL orchestrator can enforce. The population and audit design also applies to cloud warehouses, although connection and hashing syntax differ. If you need a SQL refresher first, work through the SQL for QA beginner tutorial.

TL;DR

Use one immutable reconciliation unit: batch_id + [watermark_start, watermark_end). Count source rows whose change timestamp is inside that half-open interval, count target rows labeled with that batch ID, and store warehouse_count - source_count as the delta. A zero delta passes the completeness gate, but it does not prove key or value equality.

What You Will Build

You will create a small source-to-warehouse test system and an enforceable data quality gate. By the end, you will have:

• A control.extract_batches record defining the exact source watermark interval.
• Source orders and a warehouse fact table with one intentional missing-row and duplicate-row pattern.
• An audit table containing observed counts, delta, status, and execution time.
• Slice, missing-key, unexpected-key, duplicate-key, amount, and fingerprint diagnostics.
• A shell command that exits with status 1 on a mismatch and 0 after correction.

The design complements the broader guide to writing SQL for ETL validation, but here the focus stays on one operational job: proving whether a batch moved every intended row exactly once.

Prerequisites

This tutorial pins PostgreSQL 18.4-alpine, Docker Engine 29.6.2, and Docker Compose 5.3.1. Newer compatible patch releases are fine, but keeping the database image fixed makes the exercise reproducible. You need a POSIX shell such as Bash 5.2 or Zsh 5.9; all database work runs through psql inside the container.

Confirm Docker and Compose before creating files:

docker --version
docker compose version

Expected output includes Docker version 29.6.2 and Docker Compose version v5.3.1. Use an isolated database because the final cleanup removes the tutorial container and volume.

Create a clean workspace:

mkdir warehouse-count-lab
cd warehouse-count-lab
mkdir -p sql scripts

Verification: run pwd and find . -maxdepth 1 -type d. The output should show warehouse-count-lab, sql, and scripts.

Step 1: Start the Reconciliation Database

Create compose.yaml. Its health check prevents later commands from racing startup.

services:
  db:
    image: postgres:18.4-alpine
    environment:
      POSTGRES_DB: reconciliation
      POSTGRES_USER: qa
      POSTGRES_PASSWORD: qa_local_only
    ports:
      - "54329:5432"
    volumes:
      - reconciliation_data:/var/lib/postgresql
      - ./sql:/work/sql:ro
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U qa -d reconciliation"]
      interval: 2s
      timeout: 3s
      retries: 15

volumes:
  reconciliation_data:

Start the service and wait for its health check:

docker compose up -d --wait
docker compose ps

Verification: the db service must show Up and healthy. Then verify the real server version instead of trusting the image label:

docker compose exec -T db psql -U qa -d reconciliation -Atc "SELECT current_setting('server_version');"

The result starts with 18.4. Port 54329 avoids collision with a host server on 5432; later commands use the container's client.

Step 2: Model a Bounded Batch to Reconcile Warehouse Source Row Counts

Create sql/01_schema.sql. The control row freezes the population. Its half-open interval assigns a boundary timestamp to exactly one adjacent batch.

BEGIN;

CREATE SCHEMA control;
CREATE SCHEMA source_app;
CREATE SCHEMA warehouse;

CREATE TABLE control.extract_batches (
  batch_id text PRIMARY KEY,
  watermark_start timestamptz NOT NULL,
  watermark_end timestamptz NOT NULL,
  CHECK (watermark_end > watermark_start)
);

CREATE TABLE source_app.orders (
  order_id bigint PRIMARY KEY,
  customer_id bigint NOT NULL,
  order_status text NOT NULL CHECK (order_status IN ('PAID', 'SHIPPED', 'CANCELLED')),
  order_total numeric(12,2) NOT NULL,
  updated_at timestamptz NOT NULL
);

CREATE TABLE warehouse.fact_orders (
  fact_order_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  source_order_id bigint NOT NULL,
  customer_id bigint NOT NULL,
  order_status text NOT NULL,
  order_total numeric(12,2) NOT NULL,
  source_updated_at timestamptz NOT NULL,
  loaded_batch_id text NOT NULL REFERENCES control.extract_batches(batch_id),
  loaded_at timestamptz NOT NULL DEFAULT clock_timestamp()
);

CREATE TABLE control.row_count_reconciliation (
  reconciliation_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  batch_id text NOT NULL REFERENCES control.extract_batches(batch_id),
  source_count bigint NOT NULL,
  warehouse_count bigint NOT NULL,
  row_count_delta bigint NOT NULL,
  status text NOT NULL CHECK (status IN ('PASS', 'FAIL')),
  checked_at timestamptz NOT NULL DEFAULT clock_timestamp()
);

COMMIT;

Apply it:

docker compose exec -T db psql -v ON_ERROR_STOP=1 -U qa -d reconciliation -f /work/sql/01_schema.sql

ON_ERROR_STOP=1 stops on the first schema error.

Verification: inspect the three expected tables:

docker compose exec -T db psql -U qa -d reconciliation -c "SELECT table_schema, table_name FROM information_schema.tables WHERE table_schema IN ('control','source_app','warehouse') ORDER BY 1,2;"

You should see four rows: two control tables, source_app.orders, and warehouse.fact_orders.

Step 3: Load a Known Defect Pattern

Create sql/02_seed.sql. Eight source orders fall inside the one-hour extraction window. The warehouse receives seven facts: orders 1006 and 1008 are missing, while order 1003 appears twice. The net delta is only -1, which deliberately demonstrates why totals do not reveal defect cardinality.

BEGIN;

INSERT INTO control.extract_batches (batch_id, watermark_start, watermark_end)
VALUES ('batch_20260806_01', '2026-08-06 00:00:00+00', '2026-08-06 01:00:00+00');

INSERT INTO source_app.orders
  (order_id, customer_id, order_status, order_total, updated_at)
VALUES
  (1001, 501, 'PAID',      25.00, '2026-08-06 00:05:00+00'),
  (1002, 502, 'SHIPPED',   80.00, '2026-08-06 00:12:00+00'),
  (1003, 503, 'PAID',      40.00, '2026-08-06 00:18:00+00'),
  (1004, 504, 'CANCELLED', 15.00, '2026-08-06 00:24:00+00'),
  (1005, 505, 'SHIPPED',  120.00, '2026-08-06 00:31:00+00'),
  (1006, 506, 'PAID',      55.00, '2026-08-06 00:39:00+00'),
  (1007, 507, 'SHIPPED',   70.00, '2026-08-06 00:46:00+00'),
  (1008, 508, 'PAID',      35.00, '2026-08-06 00:57:00+00'),
  (1009, 509, 'PAID',      99.00, '2026-08-06 01:04:00+00');

INSERT INTO warehouse.fact_orders
  (source_order_id, customer_id, order_status, order_total, source_updated_at, loaded_batch_id)
SELECT order_id, customer_id, order_status, order_total, updated_at, 'batch_20260806_01'
FROM source_app.orders
WHERE order_id IN (1001, 1002, 1003, 1004, 1005, 1007);

INSERT INTO warehouse.fact_orders
  (source_order_id, customer_id, order_status, order_total, source_updated_at, loaded_batch_id)
SELECT order_id, customer_id, order_status, order_total, updated_at, 'batch_20260806_01'
FROM source_app.orders
WHERE order_id = 1003;

COMMIT;

Run the seed once:

docker compose exec -T db psql -v ON_ERROR_STOP=1 -U qa -d reconciliation -f /work/sql/02_seed.sql

Verification: confirm that the lab has nine source records overall and seven warehouse facts. Record 1009 is outside the batch and must be excluded.

docker compose exec -T db psql -U qa -d reconciliation -c "SELECT (SELECT count(*) FROM source_app.orders) AS all_source, (SELECT count(*) FROM warehouse.fact_orders) AS batch_target;"

Expected result: all_source = 9 and batch_target = 7. This is not yet the valid comparison because the source total crosses the watermark.

Step 4: Define the Same Source and Target Population

A useful source-to-target row count validation begins with population equivalence. Source rows are selected by the batch's immutable watermark. Warehouse rows are selected by the persisted load lineage. Do not use created_at on one side and updated_at on the other unless the mapping specification explicitly says they represent the same business event.

Create sql/03_population.sql:

WITH batch AS (
  SELECT batch_id, watermark_start, watermark_end
  FROM control.extract_batches
  WHERE batch_id = 'batch_20260806_01'
),
source_population AS (
  SELECT s.order_id
  FROM source_app.orders AS s
  CROSS JOIN batch AS b
  WHERE s.updated_at >= b.watermark_start
    AND s.updated_at < b.watermark_end
),
warehouse_population AS (
  SELECT w.source_order_id
  FROM warehouse.fact_orders AS w
  JOIN batch AS b ON b.batch_id = w.loaded_batch_id
)
SELECT
  (SELECT count(*) FROM source_population) AS source_count,
  (SELECT count(*) FROM warehouse_population) AS warehouse_count;

Run the scoped query:

docker compose exec -T db psql -U qa -d reconciliation -f /work/sql/03_population.sql

Verification: expect source_count = 8 and warehouse_count = 7. If writes continue during extraction, count against the extractor's transaction snapshot or captured replica position. Add identical tenant filters, and document whether soft deletes are excluded, represented as tombstones, or marked inactive.

Step 5: Reconcile Warehouse Source Row Counts and Persist the Result

Now turn the comparison into evidence. Create sql/04_reconcile.sql. The insert stores the signed target-minus-source delta. Its sign is a clue, not a root cause.

WITH batch AS (
  SELECT batch_id, watermark_start, watermark_end
  FROM control.extract_batches
  WHERE batch_id = 'batch_20260806_01'
),
source_total AS (
  SELECT count(*)::bigint AS row_count
  FROM source_app.orders AS s
  CROSS JOIN batch AS b
  WHERE s.updated_at >= b.watermark_start
    AND s.updated_at < b.watermark_end
),
warehouse_total AS (
  SELECT count(*)::bigint AS row_count
  FROM warehouse.fact_orders AS w
  JOIN batch AS b ON b.batch_id = w.loaded_batch_id
)
INSERT INTO control.row_count_reconciliation
  (batch_id, source_count, warehouse_count, row_count_delta, status)
SELECT
  b.batch_id,
  s.row_count,
  w.row_count,
  w.row_count - s.row_count,
  CASE WHEN w.row_count = s.row_count THEN 'PASS' ELSE 'FAIL' END
FROM batch AS b
CROSS JOIN source_total AS s
CROSS JOIN warehouse_total AS w
RETURNING batch_id, source_count, warehouse_count, row_count_delta, status, checked_at;

Execute the reconciliation:

docker compose exec -T db psql -v ON_ERROR_STOP=1 -U qa -d reconciliation -f /work/sql/04_reconcile.sql

Verification: the returned audit row must read 8, 7, -1, and FAIL. Retain failed runs, and add source, target, query version, orchestration run ID, and approved threshold in production.

Do not apply a percentage tolerance to an exact-once load. At ten million rows, even 0.1 percent permits 10,000 unexplained records. Use tolerance only for a documented rule such as late arrivals, and store the exception count plus its authorization.

Step 6: Localize the Count Mismatch by Slice and Business Key

Group by status before scanning keys. The FULL OUTER JOIN keeps categories present on only one side visible.

WITH batch AS (
  SELECT * FROM control.extract_batches WHERE batch_id = 'batch_20260806_01'
),
source_slices AS (
  SELECT s.order_status, count(*)::bigint AS row_count
  FROM source_app.orders AS s CROSS JOIN batch AS b
  WHERE s.updated_at >= b.watermark_start AND s.updated_at < b.watermark_end
  GROUP BY s.order_status
),
warehouse_slices AS (
  SELECT w.order_status, count(*)::bigint AS row_count
  FROM warehouse.fact_orders AS w JOIN batch AS b ON b.batch_id = w.loaded_batch_id
  GROUP BY w.order_status
)
SELECT
  coalesce(s.order_status, w.order_status) AS order_status,
  coalesce(s.row_count, 0) AS source_count,
  coalesce(w.row_count, 0) AS warehouse_count,
  coalesce(w.row_count, 0) - coalesce(s.row_count, 0) AS delta
FROM source_slices AS s
FULL OUTER JOIN warehouse_slices AS w USING (order_status)
ORDER BY order_status;

Verification: CANCELLED matches at 1, SHIPPED matches at 3, and PAID reports source 4, warehouse 3, delta -1. The defect is now isolated to the paid path. Production slices often include business date, tenant, region, event type, partition, and transformation branch. The SQL GROUP BY and HAVING guide for testers covers more aggregation patterns.

Next, distinguish missing, unexpected, and repeated keys. Run these statements as one block:

WITH batch AS (
  SELECT * FROM control.extract_batches WHERE batch_id = 'batch_20260806_01'
),
s AS (
  SELECT order_id
  FROM source_app.orders CROSS JOIN batch
  WHERE updated_at >= watermark_start AND updated_at < watermark_end
),
w AS (
  SELECT source_order_id
  FROM warehouse.fact_orders JOIN batch ON loaded_batch_id = batch_id
)
SELECT 'missing_in_warehouse' AS issue, s.order_id AS business_key
FROM s LEFT JOIN w ON w.source_order_id = s.order_id
WHERE w.source_order_id IS NULL
UNION ALL
SELECT 'unexpected_in_warehouse', w.source_order_id
FROM w LEFT JOIN s ON s.order_id = w.source_order_id
WHERE s.order_id IS NULL
ORDER BY issue, business_key;

SELECT source_order_id AS duplicated_key, count(*) AS copies
FROM warehouse.fact_orders
WHERE loaded_batch_id = 'batch_20260806_01'
GROUP BY source_order_id
HAVING count(*) > 1
ORDER BY source_order_id;

Verification: the anti-join returns missing keys 1006 and 1008; the duplicate query returns key 1003 with two copies. Three physical rows are wrong although the net delta is one. Check target uniqueness before value joins, which duplicates can multiply.

Step 7: Add Amount and Fingerprint Checks

Count equality measures completeness, not correctness. Add an amount aggregate because many warehouse facts represent financial or measured values. Use exact numeric values here, not floating-point sums that can differ due to calculation order.

WITH batch AS (
  SELECT * FROM control.extract_batches WHERE batch_id = 'batch_20260806_01'
),
s AS (
  SELECT count(*) AS rows, sum(order_total) AS total_amount
  FROM source_app.orders CROSS JOIN batch
  WHERE updated_at >= watermark_start AND updated_at < watermark_end
),
w AS (
  SELECT count(*) AS rows, sum(order_total) AS total_amount
  FROM warehouse.fact_orders JOIN batch ON loaded_batch_id = batch_id
)
SELECT s.rows AS source_rows, w.rows AS warehouse_rows,
       s.total_amount AS source_amount, w.total_amount AS warehouse_amount,
       w.total_amount - s.total_amount AS amount_delta
FROM s CROSS JOIN w;

Verification: expect source amount 440.00, warehouse amount 390.00, and delta -50.00. The duplicate 40.00 partially offsets the two missing amounts of 55.00 and 35.00. Offsetting value errors mean aggregate agreement is not row-level proof.

A deterministic fingerprint adds a compact diagnostic. Ordered string_agg removes scan-order variability; on large systems, fingerprint each partition.

WITH source_rows AS (
  SELECT order_id, customer_id, order_status, order_total
  FROM source_app.orders
  WHERE updated_at >= '2026-08-06 00:00:00+00'
    AND updated_at <  '2026-08-06 01:00:00+00'
),
warehouse_rows AS (
  SELECT DISTINCT source_order_id, customer_id, order_status, order_total
  FROM warehouse.fact_orders
  WHERE loaded_batch_id = 'batch_20260806_01'
)
SELECT 'source' AS dataset,
       md5(string_agg(concat_ws('|', order_id, customer_id, order_status, order_total), E'\n' ORDER BY order_id)) AS fingerprint
FROM source_rows
UNION ALL
SELECT 'warehouse',
       md5(string_agg(concat_ws('|', source_order_id, customer_id, order_status, order_total), E'\n' ORDER BY source_order_id))
FROM warehouse_rows;

Verification: the two 32-character MD5 strings must differ in the defective state. Here MD5 is a comparison checksum, not a security control. A mismatch still requires key and column diagnostics. For richer test datasets and boundary cases, use the QA test data strategy guide.

Step 8: Automate the Reconciliation Gate

A report that nobody reads does not protect downstream consumers. Create scripts/reconcile_counts.sh so a scheduler, CI job, or ETL orchestrator receives a nonzero exit code. It prints a pipe-delimited result and rejects inequality.

#!/usr/bin/env bash
set -euo pipefail

batch_id="${1:-batch_20260806_01}"

result=$(docker compose exec -T db psql -X -v ON_ERROR_STOP=1 -U qa -d reconciliation -At -F '|' -v batch_id="$batch_id" <<'SQL'
WITH batch AS (
  SELECT batch_id, watermark_start, watermark_end
  FROM control.extract_batches
  WHERE batch_id = :'batch_id'
),
s AS (
  SELECT count(*)::bigint AS n
  FROM source_app.orders CROSS JOIN batch
  WHERE updated_at >= watermark_start AND updated_at < watermark_end
),
w AS (
  SELECT count(*)::bigint AS n
  FROM warehouse.fact_orders JOIN batch ON loaded_batch_id = batch_id
)
SELECT s.n, w.n, w.n - s.n FROM s CROSS JOIN w;
SQL
)

IFS='|' read -r source_count warehouse_count delta <<<"$result"
printf 'batch=%s source=%s warehouse=%s delta=%s\n' \
  "$batch_id" "$source_count" "$warehouse_count" "$delta"

if [[ "$delta" != "0" ]]; then
  printf 'FAIL: row counts do not reconcile\n' >&2
  exit 1
fi

printf 'PASS: row counts reconcile\n'

Make it executable and run the known-bad batch:

chmod +x scripts/reconcile_counts.sh
./scripts/reconcile_counts.sh batch_20260806_01
printf 'exit_code=%s\n' "$?"

Verification: the script prints source=8 warehouse=7 delta=-1, writes the failure message to standard error, and exits 1. In a fail-fast CI shell, use ./scripts/reconcile_counts.sh ... || status=$? to capture the status.

Run the gate after load commit but before publication or model refresh. Preserve diagnostics with the run ID; an idempotent retry must create new reconciliation evidence.

Step 9: Repair the Batch and Prove the Gate Turns Green

Repair the laboratory data in the same way a sound pipeline retry would: remove the duplicate, insert the absent source keys, and preserve the batch lineage. The fact_order_id identity lets the query retain one copy of 1003 deterministically.

BEGIN;

DELETE FROM warehouse.fact_orders
WHERE fact_order_id IN (
  SELECT fact_order_id
  FROM (
    SELECT fact_order_id,
           row_number() OVER (PARTITION BY loaded_batch_id, source_order_id ORDER BY fact_order_id) AS copy_number
    FROM warehouse.fact_orders
    WHERE loaded_batch_id = 'batch_20260806_01'
  ) AS ranked
  WHERE copy_number > 1
);

INSERT INTO warehouse.fact_orders
  (source_order_id, customer_id, order_status, order_total, source_updated_at, loaded_batch_id)
SELECT order_id, customer_id, order_status, order_total, updated_at, 'batch_20260806_01'
FROM source_app.orders
WHERE order_id IN (1006, 1008);

COMMIT;

Save this as sql/05_repair.sql, apply it, and run the gate again:

docker compose exec -T db psql -v ON_ERROR_STOP=1 -U qa -d reconciliation -f /work/sql/05_repair.sql
./scripts/reconcile_counts.sh batch_20260806_01

Verification: the gate prints source=8 warehouse=8 delta=0, prints PASS, and exits 0. Rerun the missing-key and duplicate-key queries from Step 6; both must return zero rows. Rerun the fingerprint from Step 7; its source and warehouse values must now match. These checks prevent a repair from equalizing totals with the wrong keys.

Prevent recurrence with a unique constraint on (loaded_batch_id, source_order_id) when that pair is the fact grain. For multi-row orders, constrain the real composite grain. The SQL window functions guide for testers explains the repair query's ranking pattern.

Troubleshooting

Problem: the source count changes between runs -> Capture the extractor's database snapshot, change-data-capture offset, or high watermark. Never compare a historic batch with the source's current unrestricted state.

Problem: both totals match but missing-key checks still return rows -> Missing rows and duplicate or unexpected rows are offsetting one another. Treat total equality as one assertion, then require uniqueness and bidirectional key coverage for one-to-one loads.

Problem: the target count grows after a join -> Check each input with GROUP BY key HAVING count(*) > 1. Multiple matches multiply rows, so confirm the modeled grain with its owner.

Problem: midnight rows appear in two batches or neither batch -> The watermark predicates use inconsistent inclusivity or time zones. Normalize timestamps to UTC and apply [start, end) on every batch: timestamp >= start AND timestamp < end.

Problem: the shell gate returns malformed output -> Confirm the batch ID, retain psql -X and ON_ERROR_STOP=1, and keep informational messages out of the tuples-only result.

Problem: rejected records explain the difference -> Prove extracted = loaded + quarantined + documented filters for the batch. Fail if any row remains unaccounted for.

Interview Questions and Answers

Q: Why is comparing count(*) on two complete tables usually invalid?

The tables can have different history, retention, and grains. I select one batch, apply equivalent predicates, and confirm whether one source row may legitimately expand into several facts.

Q: What does a zero row-count delta prove?

It proves only equal physical totals for the scoped populations. I still test uniqueness, bidirectional key coverage, aggregates, and important mapped values.

Q: How do you investigate a warehouse count that is lower than the source?

I confirm snapshot and watermark alignment, group by partition and transformation branch, then anti-join keys in the failing slice. Reject and quarantine records distinguish loss from intended filters.

Q: Why use a half-open watermark interval?

>= start AND < end puts a boundary timestamp in the next batch without overlap. The extractor and validator must share timestamp precision and time zone.

Q: When is a row-count tolerance acceptable?

Only a written requirement, such as a defined late-event window, should permit it. Record the threshold, age limit, owner, and escalation; financial and migration loads usually require exact reconciliation.

Q: How would you scale this method to billions of rows?

I gate first on ingestion counters, then reconcile partitions in parallel with counts, key ranges, aggregates, and fingerprints. Periodic exact audits check metadata against physical data.

The structured interviewQnA section below contains concise versions you can use for practice. For more database-focused scenarios, review senior database testing interview questions.

Best Practices

• Persist batch boundaries before extraction starts, and reference the same control record from loading and validation.
• Define the target grain explicitly. A valid count ratio may be one-to-one, one-to-many, or many-to-one depending on the model.
• Record counts before and after every intentional filter so the pipeline satisfies a conservation equation.
• Compare cheap totals first, then slices, keys, and values. This produces fast feedback without sacrificing diagnostic depth.
• Keep failed audit attempts immutable. A later pass should be a new record linked to the retry.
• Make reconciliation ownership explicit. A failure needs a responder, a publication rule, and a documented override process.
• Test boundary timestamps, empty batches, duplicates, late arrivals, updates, deletes, rejects, and retry behavior before production launch.
• Prevent known defects with constraints or idempotent merge logic rather than relying only on detection after loading.

Remove the local lab when you finish:

docker compose down -v

This deletes the tutorial container, network, and named database volume. It does not remove the files in warehouse-count-lab.

Where To Go Next

You now have a portable procedure to reconcile warehouse source row counts, explain a mismatch, and block publication. Adapt the population CTEs to your real source and target, retain the audit schema, and replace the laboratory batch ID with your orchestrator's run identifier.

Strengthen the workflow with these next steps:

• Use SQL joins for testers to expand key and value comparisons safely.
• Apply SQL test data setup and teardown patterns to create repeatable reconciliation fixtures.
• Add API-side evidence with the API test data management guide when the source is a service rather than a database.
• Practice the complete lab, then explain its audit trail and failure triage during a data QA interview.

Conclusion

Reliable ETL row count testing is a controlled comparison, not two ad hoc count(*) statements. Freeze a batch boundary, select equivalent populations, persist the delta, and use slices plus key diagnostics to show exactly what failed.

Run the gate before downstream publication and require a new, auditable pass after repair. Once count, uniqueness, key coverage, and selected values all agree, stakeholders have defensible evidence that the warehouse batch is complete rather than a dashboard that merely looks plausible.

Interview Questions and Answers

Frequently Asked Questions

Related Guides

• Test Kafka to Warehouse Data Pipeline (2026)
• A/B test validation: A Complete Guide for QA (2026)
• Accessibility testing checklist (2026)
• Accessibility testing with Cypress (2026)
• Accessibility testing with Playwright (2026)
• Agentic testing with tool calling (2026)