ETL and Data Pipeline Testing

Why it matters

Pipeline defects are quiet. A job that drops a few rows, loads a batch twice or rounds amounts down still ends with status "success", and no user sees an error. The damage surfaces later as wrong numbers in reports and invoices, after decisions were made on them.

There is often no user interface: the output is thousands of rows, and the expected result comes from a mapping document and arithmetic. Proving that two data sets agree, automatically, is a skill you will reuse for integrations, migrations and analytics.

Extract, transform, load: what to check at each stage

Check each stage at its boundary. The test basis is usually a source-to-target mapping document: for each target column it names the source columns, the transformation rule and what happens to invalid values. If none exists, writing one with the developer is often the most useful test activity.

• Extract: all rows of the agreed window arrive (for example yesterday from 00:00 Riyadh time up to, but not including, 00:00 today; a filter that ends at 23:59:59 misses a row stamped 23:59:59.5), the count matches what the source reports, and Arabic or Hindi text keeps its encoding. In CSV files, RFC 4180 says a field containing a comma, a double quote or a line break should be enclosed in double quotes, with each quote inside it doubled; a reader that just splits on commas shifts the columns after it.
• Transform: every mapping rule gives the expected value, also for NULLs, unknown codes and boundary values; rows that break a rule go to a reject table with a reason, and rows a rule filters out are counted.
• Load: rows land in the right table, business keys (the natural key from the source, such as order_id) stay unique, and loading the same batch twice leaves the target unchanged: the load is idempotent.

Try it: CSV Dataset Generator

Reconciliation: account for every row

Reconciliation compares source and target and explains every difference. A pipeline may load fewer rows than it read, because rules filter some and validation rejects others, but each row must end up in exactly one place: source rows = loaded + rejected + filtered. If that does not balance, rows were lost or duplicated.

Check at three levels. Counts per run, day and status show that totals agree. Control totals, such as the sum of amounts, show that values were not truncated or rounded away. Key-level comparison, the LEFT JOIN … IS NULL query from SQL for testers run in both directions, names the exact missing or extra ids, which is what a developer needs. Across two systems, export the keys from both sides and compare them in one place.

-- Balance one run (src_orders = the extracted rows, as staged)
SELECT
  (SELECT COUNT(*) FROM src_orders
    WHERE order_date = '2026-09-30')    AS source_rows,
  (SELECT COUNT(*) FROM fact_sales
    WHERE order_date = '2026-09-30')    AS loaded,
  (SELECT COUNT(*) FROM reject_orders
    WHERE order_date = '2026-09-30')    AS rejected,
  (SELECT COUNT(*) FROM src_orders
    WHERE order_date = '2026-09-30'
      AND store_type = 'TEST')          AS filtered;

-- Source orders that reached neither the target nor the reject table
SELECT s.order_id
FROM src_orders s
LEFT JOIN fact_sales f    ON f.order_id = s.order_id
LEFT JOIN reject_orders r ON r.order_id = s.order_id
WHERE s.order_date = '2026-09-30'
  AND COALESCE(s.store_type, '') <> 'TEST'   -- NULL store_type must not hide a row
  AND f.order_id IS NULL
  AND r.order_id IS NULL;

-- The other direction: target rows with no source order
SELECT f.order_id
FROM fact_sales f
LEFT JOIN src_orders s ON s.order_id = f.order_id
WHERE f.order_date = '2026-09-30'
  AND s.order_id IS NULL;

Try it: CSV to SQL Try it: SQL Query Validator Try it: Text Diff

Testing transformation rules

Each row of the mapping is a test condition. Design input rows with partitions, boundary values and decision tables, put them in a small source data set where you know every expected value, run the job and compare the target row by row. A large, production-like run adds volume and surprising values; you need both.

• Lookups: status P → Paid; also test an unknown code and a lowercase p.
• Conversions: amount × the exchange rate of the order date, rounded to 2 decimals. Test a result exactly halfway, such as 10.125: half-up rounding gives 10.13, half-to-even gives 10.12. Test a missing rate too.
• Time zones: converting UTC to Riyadh time (UTC+3) moves orders from 21:00 UTC onwards to the next day; test 20:59:59 and 21:00:00 UTC.
• Duplicates and NULLs: when the source sends an order twice, the rule decides which copy wins, for example the latest updated_at; a NULL discount may become 0.

Try it: Decimal Dataset Generator Try it: Date Test Data Generator

Data quality checks and backend validation

Reconciliation proves that nothing was lost; data quality checks prove that what arrived is usable. Teams name the dimensions differently, but most check those below, each as a query whose empty result is a pass.

Backend data validation means the pipeline runs these checks itself, on every run: a schema check on arrival, row rules that send bad rows to a reject table with a reason code, thresholds agreed with the data owner that stop the run before it publishes, and an audit table with the counts of every run. Test that machinery too: feed a row that breaks each rule, and push the reject rate over the threshold to confirm that the run stops and someone is alerted.

• Completeness: every sale has a store and an amount.
• Uniqueness: one row per business key, such as order_id.
• Validity: known status codes, amount > 0, no future dates.
• Consistency: every sale points to an existing store, and line totals add up to the order total.
• Timeliness: the load finished on time, and the newest row is from the expected day.
• Accuracy: a sample of target rows matches the source or a trusted report, field by field.

Worked example: a nightly load that ran twice

The run for 30 September reads 10,000 orders. The mapping filters 120 test-store orders and validation rejects 15 orders without a store id, so the target should gain 9,865 rows. The audit table shows a network timeout at 02:10, an automatic restart, and status "success" at 02:40.

The balance query counts 10,365 loaded rows, more than the source sent: 10,000 ≠ 10,365 + 15 + 120. The missing-order query returns nothing. The duplicate query returns 500 order ids, each loaded twice, which explains the whole surplus of 500 rows, and the SAR control total is too high by exactly their amounts. The job loads chunks of 500 rows and records each finished chunk in a progress table. One chunk was committed but its progress record was not, because the two writes were not in one transaction, so the restart inserted it again.

• Bug report: run date, balance numbers, the 500 order ids, the restart in the audit log, and the expected behaviour: rerunning a batch leaves the target unchanged.
• New checks: run every batch twice in testing, kill the job between a write and its progress record, and let the order_id uniqueness check stop publication.

Frequently asked questions

References

• ISTQB Certified Tester Foundation Level (CTFL) v4.0ISTQB · 2023The foundation syllabus of software testing: test levels and types, test design techniques, test management and tools.
• ISTQB GlossaryISTQBThe standard definitions of testing terms — test case, regression testing, severity, priority, boundary value analysis and the rest.
• RFC 4180: Common Format and MIME Type for Comma-Separated Values (CSV) FilesIETF · 2005The commonly implemented CSV rules: comma separators, CRLF line ends, quoting of fields that contain commas, quotes or line breaks.
• Personal Data Protection Law (English translation)Saudi Data & AI Authority (SDAIA) · 2023The Saudi law governing personal data — why test environments should use synthetic data rather than copies of real customer records.