Mapping
- Source-to-target mapping per table
- Transformation rules with examples
- Grain of each fact table
- Key and surrogate key rules
Data Warehouse Testing
Data warehouse, ETL and BI testing services
A dashboard nobody trusts is worse than no dashboard. Agnotic Technologies tests data warehouses and their reports so every figure can be traced back to source rows, and turns each check into a regression test that runs on every release.
Source-to-target testsReconciliationSCD history checksReport-to-source tracing
Trusted by global innovators
DW and BI testing
What is data warehouse testing?
Data warehouse testing checks that the numbers in a warehouse and its reports match the source systems: every row arrived, every transformation rule did what the mapping says, history was kept correctly and each dashboard figure can be traced back to source rows. Agnotic Technologies runs it as a QA engagement for ETL, ELT and BI releases.
We test warehouses other teams built as well as our own. Building pipelines is our data engineering work, and validation rules inside live healthcare feeds are in our data validation checklist; this page is the testing around a release.
The test basis is the source-to-target mapping. If it is missing or out of date, writing it down is the first deliverable, because a test without an expected result is only a query.
Checklist
Which tests does a warehouse release need?
The test types we plan for every release, what each one catches and an example of the check behind it.
| Test type | What it catches | Example check |
|---|---|---|
| Completeness | Rows dropped by filters, failed loads or late files | Source rows for the load batch equal target rows plus rows logged as rejected |
| Aggregate reconciliation | Amounts that drift in joins or in currency and unit conversion | Sum of order value by day and store matches between the source and the fact table |
| Transformation rules | Mapping logic coded differently from the specification | Each source status code lands as its mapped value, and no unmapped values exist |
| SCD Type 2 history | Overwritten history, overlapping or missing validity dates | Each business key has exactly one current row and no two versions overlap in time |
| SCD Type 1 | Old values kept where they should be replaced | After a correction load the dimension holds only the corrected value |
| Referential integrity | Facts that point to missing dimension rows | Every foreign key in the fact table finds a dimension key or the agreed unknown member |
| Report filter and security | Dashboards that hide, double count or expose rows | A region's dashboard total equals the warehouse query for that region, viewed as that region's role |
Each check is written as a query with an expected result, so it runs again in every release.
Report-to-source tracing
How do we trace a dashboard figure back to source rows?
The steps we follow when a number on a report is questioned. For key figures we automate the same steps.
Record the figure exactly
The report, the measure, the filters and the user role, exactly as the person saw them.
Read the measure definition
The formula in the semantic model, the tables it reads and any row-level security on that role.
Rebuild it in SQL
Query the fact and dimension tables directly with the same filters and compare the result.
Follow the rows through each layer
Compare the curated, staging and raw layers for the rows behind the figure, using the mapping document.
Compare with the source system
Pull the same records from the source and explain every difference: timing, rejects or a rule.
Save it as a regression test
Keep the queries and expected results so the figure is checked automatically on every release.
Big data testing
How do you test Spark and streaming pipelines at volume?
Big data testing for Spark and streaming pipelines adds checks that a small fixture cannot show: quality thresholds with a quarantine table, late, duplicate and out-of-order events, replay from a checkpoint, a stream reconciled against its source and a sustained run at production rate. Agnotic Technologies runs these alongside the job's own unit tests.
| Test | What it catches | When it runs |
|---|---|---|
| Data quality expectations with thresholds | Null rates, duplicate keys or values outside agreed ranges; failing rows go to a quarantine table for review instead of the target | Inside every pipeline run, with the run stopped when a threshold is crossed |
| Late events | Windows that close before late events arrive, or that drop events inside the agreed lateness | On a test stream with scripted event times, before each release |
| Duplicate events | Double counts when a producer retries or a source resends a batch | On the same test stream, with known duplicates injected |
| Out-of-order events | Results that change with arrival order, such as a status that ends on an older value | On the same test stream, with events shuffled |
| Replay from a checkpoint | A restarted job that skips or repeats data, or a query change the old checkpoint does not accept | After any change to state, windows, keys or sinks |
| Stream reconciliation | Counts and totals per window that drift from the source table over hours or days | Continuously in staging, and as a scheduled check in production |
| Sustained run at production rate | Consumer lag that grows, state that keeps expanding and checkpoints that slow down | Before major releases, on masked data |
Results are reported per row, because each one points to a different fix. Streaming architecture itself is covered on our real-time data warehouse page.
Test basis
What goes into the test basis?
The documents and access we collect before writing the first test.
History rules
- Type 1 and Type 2 dimensions
- Tracked attributes per dimension
- Late-arriving data rules
- Deleted record handling
Reports
- Key figures and their owners
- Measure definitions
- Roles and row-level security rules
- Known differences the business accepts
Access
- Read access to source extracts
- Read access to warehouse layers
- A test workspace in the BI tool
- Masked or synthetic test data
Failure points
Where do warehouse numbers usually go wrong?
Defects we find most often, and the test that catches each one.
Challenge
A late file loads after the nightly reconciliation has already passed.
Agnotic approach
Reconciliation runs per load batch with the batch ID recorded, not once per night.
Challenge
A Type 2 dimension adds a version on every load because a timestamp column changes.
Agnotic approach
Tests check that new versions appear only when tracked attributes change.
Challenge
A source schema change silently turns a column into nulls.
Agnotic approach
Not-null and accepted-value tests on critical columns fail the release before reports refresh.
Challenge
A many-to-many join doubles revenue in one report.
Agnotic approach
Grain tests check one row per key, and aggregate reconciliation against the source catches the doubling.
Challenge
Row-level security hides rows from the people testing the report.
Agnotic approach
Reports are tested as each role, using a role matrix agreed with the data owner.
Challenge
Test data is a copy of production with real customer details.
Agnotic approach
Masked or synthetic test sets that keep keys and edge cases but not identities.
Test data
How do we handle test data and masking?
Good warehouse tests need realistic data. These are the rules we follow.
- Masked copies keep primary and foreign keys consistent, so joins still work after masking
- Names, contact details and free text are replaced, not just hidden in the report
- Synthetic rows cover cases production rarely has: nulls, future dates, very large amounts
- Test data sets are versioned with the tests that use them
- Access to unmasked data, when a trace needs it, is logged and time limited
- For healthcare warehouses, Agnotic signs a business associate agreement before any PHI is shared
When to test
When teams bring in data warehouse testing
The moments when an independent look at the numbers pays off.
Before a warehouse migration goes live
Parallel runs of the old and new warehouse, reconciled by table and by key figure.
After a BI tool change
Report-by-report comparison when dashboards move to a new tool or semantic model.
When numbers are disputed
A trace of the disputed figures, then regression tests so the dispute does not come back.
For regulated or board reporting
Evidence that reported figures match source records, kept with each release.
As a suite for your data team
Tests your team runs in CI, written in the tools you already use, such as SQL or dbt tests.
DW and BI testing for startups
A short list of checks on the figures investors and the board see most.
Methods
Methods and tools we test with
We write tests in the tools your team already runs wherever we can.
Source-to-target mappingSCD Type 1 and Type 2dbt data testsRow-level securitySQLCI pipelines
Facts and sources
Facts behind data warehouse testing
Tool behavior our tests rely on, each linked to the vendor's own documentation.
dbt ships with four generic data tests: unique, not_null, accepted_values and relationships, the last of which checks referential integrity between tables.
Source: dbt documentation: Add data tests to your DAGdbt snapshots implement Type 2 slowly changing dimensions over mutable source tables, recording each version of a row with dbt_valid_from and dbt_valid_to columns.
Source: dbt documentation: Add snapshots to your DAGIn Power BI, a row-level security role filters rows with a DAX expression, and Microsoft advises using the View as role feature to check a filter returns the expected rows before publishing.
Source: Microsoft Learn: Row-level security with Power BIA Spark Structured Streaming query restarted after a failure or shutdown recovers from its checkpoint location, which saves the offsets processed and the running aggregates, and some query changes are not allowed between restarts from the same checkpoint.
Source: Apache Spark, Structured Streaming Programming GuideIn Spark Structured Streaming, a watermark is set from an event time column and a lateness threshold: late data within the threshold is still aggregated, and data later than the threshold starts to be dropped.
Source: Apache Spark, Structured Streaming Programming Guide
Scope and cost
What drives the cost of data warehouse testing?
We do not publish prices. These drive the size of an engagement.
Number of sources and tables
How many source systems feed the warehouse and how many tables are in scope.
State of the mapping
Whether a current source-to-target mapping exists or has to be written first.
History rules
How many Type 2 dimensions and late-arriving data cases need tests.
Reports and roles
How many key figures, dashboards and security roles we test.
Automation
One-off testing for a release, or a regression suite wired into your pipeline.
Test data
Whether masked or synthetic data sets exist, or need building.
Frequently Asked Questions
We do not publish prices. Cost depends on the number of sources and tables, whether a mapping exists, the history rules, the reports and roles in scope, and whether you want a one-off test or an automated suite.
Yes. Most of our warehouse testing is on systems we did not build. We need read access to sources and warehouse layers, the mapping or the people who know it, and a test workspace in the BI tool.
The ones your team already runs where possible: plain SQL, dbt tests or your orchestration tool's checks. For BI, we test measures and reports inside the BI tool itself, including views as each security role.
Yes. In ELT the raw data lands first and transformations run inside the warehouse, so we test the raw layer against the source and each transformation model against the mapping. The test types are the same; where they run changes.
We load known changes into a test copy and check the result: one current row per key, no overlapping validity dates, a new version only when a tracked attribute changes, and Type 1 columns overwritten in place.
Yes. We write each check as a query with an expected result and add it to your CI or orchestration runs, so a failed check stops the release before reports refresh.
AI tools may help draft test queries from the mapping document, but an engineer checks every query against the mapping and a known result before it joins the suite. Production data is never pasted into AI tools.