TL;DR

ETL testing verifies that data extracted from source systems, transformed by business rules, and loaded into a target arrives complete, accurate, and on time.
The defect class is quiet: a pipeline runs green, the row count matches, and a currency field has been silently rounded for six months.
Five checks cover most risk: completeness, accuracy, transformation logic, referential integrity, and freshness.
Autosana verifies the far end of the pipeline, confirming that what reaches a user's screen matches what the warehouse holds.

A nightly pipeline reports success for eight months. Row counts match at every stage. Then finance notices that revenue in the dashboard trails the ledger by a fraction of a percent, consistently, and the cause is a transformation casting a decimal to a float somewhere in the middle.

Nothing errored. The job was green every night. ETL testing exists for this class of defect, where the pipeline works and the data is wrong.

This guide covers the five checks that find it, where mobile analytics pipelines differ, and how to verify a transformation without rebuilding it.

What ETL Testing Is

ETL stands for extract, transform, load. Data is pulled from source systems, reshaped by business rules, and written into a warehouse or downstream store. ELT reverses the last two steps, loading raw data first and transforming inside the warehouse, and the testing concerns are nearly identical.

ETL testing verifies that this movement preserves what matters. ISO/IEC/IEEE 29119-4 catalogues the test design techniques that apply.

What makes ETL testing distinct from application testing is the absence of a user interface and the presence of volume. You are comparing datasets rather than clicking screens, and the comparison has to work across millions of rows without reading them individually.

ETL testing contends with two dominant failure modes. Data arrives incomplete, which is usually loud and gets noticed. Data arrives subtly wrong, which is quiet and can persist for months.

What Are the Five ETL Testing Checks?

How Do You Check Completeness?

Did everything that left the source arrive at the target?

Row counts at each stage are the first pass and the weakest. They catch wholesale loss and miss selective loss where a filter silently drops a category. Stronger: reconcile counts grouped by a meaningful dimension such as date, region, or type, so a missing slice shows as a zero rather than disappearing into an aggregate.

Also check the boundaries. Records at the start and end of a batch window are where off-by-one errors in date filtering live.

How Do You Check Accuracy at Volume?

Does each field hold the value it should?

Full row-by-row comparison is impractical at volume, so the usual approach is checksums or hashes over selected columns, compared between source and target. Where they differ, narrow by partition until the offending rows surface.

Pay particular attention to numeric precision and type casts. The opening example is a type cast, and it is the silent corruption that recurs most across pipelines.

How Do You Test Transformation Logic?

Does the business rule produce the right output?

This needs its own cases rather than being inferred from the data. Take each rule, construct inputs covering valid values, boundaries, nulls, and malformed entries, and assert the output. Boundary values matter as much in ETL testing as in application testing: a rule applying above a threshold needs tests at the threshold and either side of it. Martin Fowler's test pyramid reasoning holds here too, with transformation rules belonging at the fast, cheap base.

Null handling deserves separate attention, since a rule that treats a missing value as zero produces different totals than one that excludes the row, and both are defensible until somebody writes down which is intended.

How Do You Check Referential Integrity?

Do the relationships survive?

Orphaned foreign keys, duplicated primary keys, and broken joins are the output of a pipeline that moved tables independently without checking they still agree. This check is cheap to automate and frequently skipped.

How Do You Check Freshness?

Did the data arrive when it was supposed to?

A pipeline delivering correct data twelve hours late has failed, because the dashboards reading it reported stale figures all morning. Assert on the latest timestamp present rather than on job success, since a job can succeed while processing an empty or stale input.

Where Mobile Analytics Pipelines Differ

ETL testing for products with mobile clients contends with pipelines that behave differently from server-sourced ones.

Events arrive late. A device offline for three days uploads its queued events on reconnection, which means yesterday's totals can change tomorrow. A pipeline assuming a closed window will undercount, and a dashboard built on it will disagree with itself over time.

Clocks disagree. Device timestamps come from a clock the user can change. Reconciling device time against server receipt time is a transformation rule in its own right, and it needs explicit test cases.

Duplicates are normal. Retry logic on an unreliable network produces the same event more than once. Deduplication is therefore a pipeline requirement rather than an edge case, and it needs testing with deliberately duplicated input.

Schema drifts per app version. Users run builds from several releases back, so the pipeline ingests multiple event schemas simultaneously. A transformation written against the current schema needs cases covering the older ones still in the field.

Mobile app testing verifies the event gets emitted correctly. ETL testing verifies it survives the journey. Both are needed, and ETL testing is the half more commonly assumed than performed.

Which ETL Testing Checks Should Be Automated?

Most ETL testing automates well, because the assertions are deterministic and the inputs are controllable.

Seed a known dataset into a source in a dedicated environment, run the pipeline, and assert against expected output. Deterministic seed data is what makes the result repeatable, and our variables handling keeps connection details out of the test definitions.

Run completeness and integrity checks on every pipeline execution rather than on a schedule, since they are cheap and the failure they catch is expensive.

Run transformation logic cases on every change to the transformation, treating them the way unit tests are treated for application code.

Keep the comparison queries in version control beside the pipeline. A reconciliation query that lives in somebody's client is lost the moment they change role.

Route failures to issues so a data defect enters the same workflow as any other, rather than living in a monitoring channel somebody skims. API testing covers the ingestion endpoints feeding the pipeline, and regression testing of the transformation rules keeps them stable across releases.

Why Verify the Figure on Screen as Well as in the Warehouse?

ETL testing that stops at the warehouse is halfway. The figure a user sees has usually passed through an API and a rendering layer after leaving the warehouse, and each of those can introduce its own discrepancy.

That final comparison is worth making explicitly: query the warehouse directly, open the screen that displays the same figure, and assert they agree. Rounding applied at the presentation layer, a cached response serving a stale value, and timezone handling differing between warehouse and client all surface here and stay hidden everywhere upstream.

End-to-end suites that include this check catch a class of defect that pipeline testing and UI testing each individually miss, and smoke tests on a reporting screen are a cheap daily version of it.

How Autosana Fits

We verify the far end of the pipeline, which is where data becomes something a person acts on.

Our flows open the screen displaying a figure and assert its value, on real devices, so the number a user sees gets checked rather than inferred from the warehouse.

Environments configuration points a run at a staging warehouse with seeded data, and variables keeps credentials out of the flow definition.

Suites group reporting screens so a daily run confirms every dashboard still agrees with its source, and failures link to issues with the screen capture attached.

We cover the presentation boundary. Pipeline-internal reconciliation belongs in your data tooling, and the two together close the path from source system to rendered figure.

Conclusion

Take one number from a dashboard your business trusts and trace it back to the source system by hand.

Count the transformations it passed through and check how many have an ETL testing case asserting their output against a known input. The usual answer is fewer than expected, because transformations get verified by eyeballing the result once at build time and then left alone.

Then check the type casts specifically. A decimal becoming a float is silent, survives every row count, and is the defect in the opening example.

FAQ

What is ETL testing?

ETL testing verifies that data extracted from source systems, transformed by business rules, and loaded into a target arrives complete, accurate, and on time. It compares datasets rather than exercising a user interface.

What are the main ETL testing checks?

Completeness, accuracy, transformation logic, referential integrity, and freshness. Completeness and integrity are cheap enough to run on every execution; transformation logic runs on every rule change.

Why are row counts insufficient for completeness?

They catch wholesale loss and miss selective loss. A filter silently dropping one category leaves the total looking plausible. Reconciling counts grouped by a meaningful dimension exposes the missing slice.

What causes silent data corruption?

Type casts, most often. A decimal cast to a float introduces a small consistent error that survives every row count and error check, and can persist for months before anyone reconciles against a ledger.

How is ELT testing different from ETL testing?

ELT loads raw data first and transforms inside the warehouse, reversing the last two steps. The testing concerns are nearly identical, since the same five checks apply wherever the transformation happens.

What is different about mobile analytics pipelines?

Events arrive late from devices that were offline, device clocks disagree with server time, retry logic produces duplicates as normal traffic, and multiple event schemas arrive simultaneously from different app versions.

Should freshness be asserted on job success?

Assert on data rather than on job status. A job can succeed while processing empty or stale input. Assert on the latest timestamp present in the target, since correct data delivered twelve hours late has still failed its purpose.

Why verify the figure on screen as well as in the warehouse?

Rounding at the presentation layer, cached responses serving stale values, and timezone differences between warehouse and client all appear after the data leaves storage and stay invisible to pipeline testing.