Quality Engineering
ETL & Data Warehouse TestingYour pipelines run green. That does not mean your data is right.
Data is extracted from the systems that run your business, transformed into the shapes a warehouse expects, and loaded somewhere it can be reported on. ETL Testing checks what comes out of that pipeline rather than whether the job finished. Source-to-target mismatches, truncated columns, duplicated loads and broken transformation logic all pass straight through a green run, then surface months later in a finance dashboard or a model feature.
What this covers
Source-to-target reconciliation, row counts, checksums and column-level comparison
Transformation logic tested against the mapping specification, not against itself
Incremental and CDC loads, late-arriving data, replays and idempotency
Slowly changing dimension handling, so history stays queryable
Nulls, duplicates, orphans and referential integrity across the warehouse
Schema drift detection between environments before it reaches production
What ETL Testing actually is
Data rarely lives where it is reported.
Data rarely lives where it is reported. It is written by the systems that run the business, then extracted, transformed into the shapes a warehouse expects, and loaded somewhere it can be queried — the three steps that give ETL its name. Every dashboard, board pack and machine-learning feature sits at the end of one of those pipelines.
ETL Testing checks the data that comes out the other side, rather than the job that moved it. That is a different question from the one a pipeline answers. A job reports success when its code finished without raising an error; whether the values it wrote still mean what they meant at the source is something nothing in that run has asked.
Why it is not the same as Database Testing
Database Testing asks whether a system of record stored what the application claimed — constraints, transactions, integrity in one place.
Database Testing asks whether a system of record stored what the application claimed — constraints, transactions, integrity in one place. ETL Testing asks whether a copy of that record, reshaped by transformation logic and landed somewhere else, still carries the same meaning.
The failure modes barely overlap. A transactional database fails loudly: a constraint rejects the write, a transaction rolls back, an error surfaces. A pipeline fails quietly, because truncating a column, rounding a decimal or dropping a late-arriving row are all legal operations that produce a green run and a wrong number. The tooling differs too, which is why we treat it as its own discipline rather than a corner of database work.
The migration succeeded. The data did not
A pipeline signals success when the job finishes, which is a statement about the job and not about the data.
A pipeline signals success when the job finishes, which is a statement about the job and not about the data. Source-to-target mismatches, silently truncated columns, duplicated loads and transformation logic that quietly rounds the wrong way do not fail a run. They pass through it.
The cost arrives later and somewhere else, in a finance dashboard nobody can reconcile, a model feature trained on a column that changed meaning, or an executive report two teams refuse to agree on. By then the numbers have been replicated, backed up and consumed downstream, and the expensive part is no longer the fix, it is that nobody trusts the warehouse any more.
What we validate
We work in SQL and Python, with dbt tests or Great Expectations where a team already runs them, against Snowflake, BigQuery, Redshift and Databricks.
We work in SQL and Python, with dbt tests or Great Expectations where a team already runs them, against Snowflake, BigQuery, Redshift and Databricks. The output is a suite that runs in CI, not a reconciliation someone performs by hand each quarter and eventually stops performing.
Source-to-target reconciliation: row counts, checksums, aggregate and balance checks, and row-by-row comparison on the columns that matter, so a load is proved rather than assumed.
Data profiling before anything else: distributions, cardinality, null rates and outliers on the source, because you cannot assert what a column should contain until you have looked at what it does.
Transformation logic: tested against the mapping specification, not against the pipeline's own output, which only ever confirms that the code does what the code does.
Incremental and CDC loads: late-arriving records, replays and idempotency, because the second run of a pipeline breaks more often than the first.
Slowly changing dimensions: that history remains queryable and a type-2 dimension has not quietly become a type-1 one.
Integrity across the warehouse: nulls, duplicates, orphans and referential relationships that no single job owns.
Schema drift: differences between environments caught before a deployment, not after a dashboard empties.
What makes this hard, honestly
Volume. Correctness has to be proved across millions of rows, which rules out reading them. The work is choosing checks that are cheap enough to run every load and strong enough to catch what matters.
Several owners, one symptom. Data arrives from systems owned by different teams, so a wrong figure in a report rarely tells you which upstream source produced it. Reconciliation has to point at a source, not just raise a flag.
Test data. Representative data across every source is the constraint nobody plans for. Tidy fixtures pass; production contains the row with the unexpected NULL, the customer with forty thousand orders and the legacy record written by a schema that no longer exists.
Rules that move. Source systems and governance rules change under you, so a suite written against last quarter's mapping quietly stops Testing anything. Assertions have to be versioned against the specification, not frozen against an output.
Estimating the work. Nobody knows how bad the data is until it is measured, which makes up-front sizing guesswork. This is why we scope a pilot on one pipeline first and price the rest once there is evidence.
Where this meets AI
A retrieval-augmented system is only as truthful as the data it retrieves, and an evaluation set is only as meaningful as the rows it was built from.
A retrieval-augmented system is only as truthful as the data it retrieves, and an evaluation set is only as meaningful as the rows it was built from. The same reconciliation and integrity work that keeps a warehouse honest is what keeps a RAG corpus and an eval dataset honest, which is why this practice sits next to our AI Quality Engineering work rather than apart from it.
Engagement path
How the engagement runs
Every ETL & Data Warehouse Testing engagement runs the same way: understand the risk, build the thing that reduces it, prove it works, hand it over.
Mapping and risk review
We read the mapping specification against the actual schemas and identify the transformations where a silent failure would be both plausible and expensive.
Reconciliation harness
Re-runnable checks for counts, checksums and column-level comparison, plus masked test data carrying the shapes that break pipelines rather than the tidy ones that do not.
Load-behaviour Testing
Incremental runs, replays, late-arriving records and dimension history, exercised deliberately instead of discovered during a month-end close.
CI integration
Validation wired into the pipeline so schema drift and reconciliation failures surface on a build, with a report a data owner can read.
Deliverables
What you get
Sign-off comes with evidence attached, not a green tick on a job history.
Handover pack
5 artefacts · yours to keep
A reusable validation suite living in your repository, not on our laptops
A reconciliation report your data owners and finance stakeholders can read
Masked, production-shaped test data safe for non-production environments
Transformation findings mapped back to the specification clause they break
Runbooks your own Engineers extend after we leave
Self-check
Signs your team needs this
If more than one of these is true, it is usually cheaper to fix now than after the next release.
Two dashboards disagree and no one can say which one is wrong
A warehouse migration is planned and reconciliation means comparing row counts
Pipeline runs are green while data owners keep filing correctness tickets
Incremental loads have never been tested with a replay or a late-arriving record
A RAG or ML feature is being built on tables nobody has validated
A Senior Engineer replies within one business day. NDA first.
Questions
Frequently Asked Questions
Straight answers, written the way we'd say them on a call.
Still curious? Talk to usKeep reading
Where to go next
ETL & Data Warehouse Testing rarely stands alone. These are the disciplines and resources teams pair it with most often.
Get a Free Review of Your Testing Setup
No pitch, just findings: what's working, what's costing you time, and where Automation pays off fastest.
- A Senior Engineer replies, not a sales layer
- Within one business day, every time
- NDA available before you share any details
16+
Years QA leadership
The founder's enterprise QA career across OTT, SaaS, e-commerce and regulated utilities. Not a team total.
17
Testing disciplines
Each one has its own page, scope and deliverables. Counted from that list, never typed by hand.
6
Markets served
Availability, not delivery history. Each market's page says plainly where we have clients and where we do not.
1
Business day to reply
A Senior Engineer answers, not an autoresponder or a scheduler.
Tell us where quality hurts
Prefer to talk? Book a 30-minute call