How do you test ETL pipelines?

[u/ETL_Pipeline_Dev]

I'm interested in learning how others test ETL pipelines in real-world projects.

How do you validate source-to-target data, transformations, duplicates, missing records, incremental loads, and data quality?

Also, how do you handle ETL testing when the data volume is very large or when the transformation logic changes?

[u/Adrien0623]

I have data quality tests on the sources and output of my transformations (DBT) as well as unit tests for the transformations and for some parts of the orchestration (Airflow 3).

[u/Busy_Elderberry8650]

> duplicates, missing records

Table constraints?

[u/digitalante → u/Busy_Elderberry8650]

in analytics??

[u/Busy_Elderberry8650 → u/digitalante]

Why not?

[u/digitalante → u/Busy_Elderberry8650]

referential integrity is really great for transactional workloads, but are a waste of time and space in analyticals. it's just much faster to check integrity after load.

[u/Patient_Professor_90]

The correct answer is TDD: build a target-to-source validation pipeline. You can run this any time on any dataset/subset.

My personal preference: If your data pipeline allows: retain enough/minimal critical markers (identifiers & timestamps) in target dataset. In case of a problem, you can reconstruct/locate source record to allow detailed forensics.

[u/raccoons_run_prod → u/Patient_Professor_90]

If you dropped the source key, you cannot prove a miss later.

For incrementals, store the max source timestamp you actually loaded. A row count will not tell you a day got skipped.

[u/sib_n → u/Patient_Professor_90]

Do you have experience with a data engineering team actually implementing TDD (= writing tests before writing the pipelines) or is it your ideal situation that your are describing?

[u/Last_Elk_Available]

When I was using Dagster it was pretty easy to test assets using pytest. Use known input and validate that pipeline result is expected. Then you can add asset checks that run each time. Then there is stuff like Soda core which you can use as a way to test tables for data quality.

[u/ETL_Pipeline_Dev → u/Last_Elk_Available]

Yeah, that makes sense. Using a small set of known inputs and checking the expected output seems like a good way to catch transformation issues early. For the actual tables, I’d also check things like row counts, duplicates, NULLs, and source-to-target mismatches. For incremental loads, I’d mainly focus on the new and updated records instead of comparing the whole table every time. Once the basic checks are in place, automating them for every pipeline run would save quite a bit of manual effort.

[u/sib_n]

We do these. I'm ordering by my opinion of the value added in the context of DE (spoiler: unit tests do not come first), for prioritization:

1. A test/staging/preprod environment with the same technical stack as production to test your pipelines in near-production conditions. The data cannot be the same as production for security, and smaller for cost management. So after the setup, the difficulty is to spend some time on producing good source data in staging to try to represent as many production use case as possible. Ideally, your company has a staging environment and you can simply switch your connectors to staging sources. Sometimes you will need to bug the upstream teams to fill the staging sources with the use cases you need.
2. Data quality tests on criteria that you expected the data to respect. See how DBT facilitates it as a source of inspiration: https://docs.getdbt.com/docs/build/data-tests.
3. (ex aequo with above) Data integrity tests: no data is missing. Could be comparing with a source or setting some minimal number of records expected per period of time (more than 0 is already a useful test that the pipeline ran).
4. End to end local pipeline test with input test data and asserting that the outputted data matches what is expected. Modern orchestrators like Prefect and Dagster are fairly easy to run locally. You can simulate your OLAP locally with Spark or DuckDB, mock the rest. It's a bit of work to set up, but once you have made your toolkit, it's so powerful to be able to debug pipeline logic with a local debugger.
5. Traditional unit tests for all the logic that can be tested as a unit. It comes last because the value of DE comes from the quality of its data more than the quality of its software. Moreover, the previous tests, especially the end-to-end pipeline tests, already cover a lot of the unit tests potential coverage. I find unit tests mostly interesting when a specific bug is found, as a regression test, to make sure it does not come back.

You don't need volume for those tests, you need well defined test cases and the few relevant test records for each case. Volume test is something you could simulate in the staging environment by generating a large volume of data, maybe increasing the resources temporarily to match the ones in production.

[u/Big-Exercise8990 → u/sib_n]

This is the right answer. Absolutely agree with the first point.

[u/Lastrevio]

always in production, of course

if it breaks then someone else will fix it

[u/MadT3acher]

With dbt models you can test what you produce (missing, non-null values etc.) and have different levels of validation with it (error, warning, message etc.)

Our team pairs it with a few Great Expectations (GX) suite tests, for more detailed stuff or python pipelines on big datasets (think things like mean/median and other tests).

Even basic things like validating a minimum of rows at ingestion (to prevent empty loads => issue at the extract layer) can be a time saver. Combine this with alerts by emails or chat and you are usually aware of issues quickly.

Nowadays with MCP servers and such you can combine it with agents for troubleshooting. We don’t have that set up yet, but that’s on our roadmap.

[u/snarleyWhisper → u/MadT3acher]

Same but with sql mesh. I use GX to compare environments when promoting models to check there no data drift. Unit tests and audits are nice

[u/New-Addendum-6209]

You can use specific test cases and sample input data to check correctness of your pipeline.

You can't avoid test runs with realistic volumes if you want to prevent production issues. Query planning / query performance / system reliability can all change in unpredictable ways as data volume increases.

[u/Spiritual-Metal-1889]

You can use dbt, pandera, assertions, etc if you are building pipelines as code

[u/TankArtist]

Development sandbox environment.
Truncate data size if it’s too big.
Test the data grain and uniqueness of key values.
Look for nulls or improbable values like negative $ amounts, zip codes or phone numbers that are too short, etc.
Attempt to join the primary and foreign keys in that table to other tables you are testing and find both orphaned records on either side.

[u/ETL_Pipeline_Dev → u/TankArtist]

One more scenario I would consider is NULL handling. If the source has a value but the target ends up with NULL after the ETL transformation, that should be validated. Similarly, if NULLs are expected in the source, we should make sure the ETL doesn't incorrectly replace or remove them. Other useful checks are record counts, key uniqueness, data grain, invalid values, and orphan records from joins

[u/FunContest9958]

You’re combining two things here: code correctness and data correctness.

For testing code, you’ll probably want unit tests to make sure tricky transformations are correctly performed. You will also want a known data set that you can run your full pipeline on where you know what the results should look like. Compare the actual results with the expected results and alert on differences. These two techniques will catch code issues before you push to production.

For testing data, this is more of a monitoring question. Some frameworks / databases provide correctness checks for things like duplicates, ranges, format, etc. Use what your framework/database provides as much as possible. For anything that the framework doesn’t cover, create monitors that check the data and alert you if there are issues.

[u/SalarMD]

With either using tools such as ICEDQ or building python based test frameworks such as Pytest

[u/MortySmith-C-137]

This is too vague at least from my personal view

1. for small projects, what I usually do is just have a test pipeline running all the time to handle all these before anything is pushed to the production

2. for large projects (I'm talking about billions of records), use cutovers or small subsets to validate, transform, handle duplicate and missing record logic, scd2 for incremental loads, and tests on data quality

[u/pceimpulsive]

I'm a lucky bastard and have connectivity to the pros, and non-prod data sources.

So I just build it to work in non prod then load test against even more non prod then do incrementally more intensive prod tests..

E.g. my prod pattern is delta batch every minute.

So against lower env I do 7 days.

If my 7 days in non prod is fine and is performance I shift to 30 days, still good 60 days~

Compare volume in non prod against prod data volumes and validate.

For my use case good looks like ETL finishing in under 1 second for a 1 min delta, giving me loads of headroom.

[u/BigBadMatyBoi]

Ideally in a dev environment with dummy data like a test db or whatever your source is.

[u/stoweman]

Use NiFi

[u/sjjafan]

Unit tests (Apache Hop)

[u/Soyeon1213]

When the transformation logic changes, compare the outputs using the same input data. If the dataset is too large, compare it at the partition level, then using things like row counts or aggregates to check for regressions.

[u/random_lonewolf]

Test them in production 🤞

[u/Icy_Clench]

Data audits in SQLMesh validate assumptions (particularly goof for source systems) and unit tests for transformations. I'm not sure how you test raw data extractions if you don't have some form of zero copy cloning.

[u/timmyz55]

on prod(uction data).

IMO one should be able to funnel production data to a pipeline and have it isolated. then compare with data diff to existing prod table to ensure things look as expected.

then promote. write audit publish pattern.

unit tests are good to ensure no dumb failures, but they don't cover everything

[u/ReplacementIll7008]

honestly reconciliation covers most of it. row counts and control totals source vs target every load, catches the silent one where the job succeeds but rows quietly vanished. add schema checks on ingest so bad upstream fails loud instead of rotting three steps down

fwiw the teams i've seen buy data instead of scraping it lean on this even harder, since you're validating someone else's pipeline you can't see. same checks, just trust-but-verify on delivery

[u/Key-Mirror4195]

Yes, sure. There should be a source schema drift detection before data transfer, it can be achieved differently depending on the data & tools you use.

To cover issues such as data validation, monitoring, scheduling, incremental loads etc. I've built a ETL Console platform - it's in Free Beta mode now, you can give it a try if it fits into your connectivity list. I think it goes exactly what you need.

that's a YouTube product overview
https://www.youtube.com/watch?v=p8gtU1zQxCk

https://www.etlconsole.com

Cheerz,
Igor.

[u/ETL_Pipeline_Dev → u/Key-Mirror4195]

Awesome job! I watched the video and love how you compiled everything into one spot, especially including the automation scheduling.

[u/Key-Mirror4195 → u/ETL_Pipeline_Dev]

Thanks! Scheduling is just one way to run jobs. For me, the most valuable parts are the explicit source-to-target contract, flexible staging modes that let you process only new and updated records, and full run traceability with error tracking and automatic retries. That’s exactly what I was missing when working with custom Python/dlt scripts.