My Two-Part Strategy for Testing Data Marts

Data mart testing is often discussed as if the main question is how to sample a few rows and see whether they look right.

That can be useful at the beginning. But once a data mart becomes important, sampling alone stops being enough.

My own testing strategy for data marts has two major parts:

1. repeat previous test runs to detect drift
2. write diagnostic queries that validate constraints across the whole set

I have used this approach on both the Loss Sensitive Invoicing project and the Reinsurance Cessions project. In both cases, the point was not to create a small number of heroic test cases. The point was to create a system where the computer could do the exhausting comparison work and the humans could focus on whatever changed.

On one project, we reran a previously validated test suite after a change to the transformation logic. As expected, the before-and-after comparison reported differences, but the important question was whether those differences represented defects or intentional business changes.

Rather than inspecting records one at a time, we used diagnostic queries to compare key constraints across the entire data set. The diagnostics showed that record counts, referential integrity, and uniqueness constraints were still satisfied, while the differences were concentrated in one category that had been intentionally affected by the revised business rule.

Because the comparisons isolated the change and the diagnostics confirmed that the broader data set still met its expected constraints, the team was able to focus its review on the intended impact instead of revalidating the entire data mart. The exercise increased confidence in both the implementation and the testing approach. The comparisons told us where the data changed. The diagnostics helped us determine whether those changes made sense.

That is the real leverage.

Part One: Repeat Previous Test Runs to Find Drift

One of the strongest test strategies I know is also one of the most straightforward.

If I can repeat a previous test run against the same logic, or against a revised version of the logic, then I can compare the outputs and ask a very practical question:

What changed?

That question becomes especially powerful when the earlier run included the complete dataset, or at least most of it.

At that point, unexplained differences are not just interesting. They are test failures until proven otherwise.

This flips the testing burden in an important way. Instead of asking a human to manually inspect huge volumes of output, we let the computer compare the old and new runs and surface only the differences.

Now the human task becomes:

• review the differences
• explain them
• classify them as expected or unexpected
• investigate anything that should not have changed

That is a much more scalable model.

Why This Matters

This approach allows humans to manage millions of test cases because the computer is doing the broad comparison and surfacing just the issues.

That means:

• a rerun of the whole process becomes a regression test
• the comparison output becomes the test report
• unexplained deltas become a practical work queue for investigation

In other words, if the dataset is large, that is not necessarily a reason to reduce the test strategy. It is a reason to lean harder into machine comparison.

What Counts as Drift

In this context, drift does not automatically mean “bad.”

Sometimes the data changed because:

• source data changed
• business rules changed
• known bugs were fixed
• timing windows changed
• a correction was intentionally introduced

That is fine.

But if the output changed and nobody can explain why, then that is exactly the kind of thing the test process should force into view.

The discipline here is simple:

• expected differences should be explainable
• unexplained differences should be investigated

That is a strong regression posture.

Part Two: Use Diagnostic Queries to Validate Constraints Across the Whole Set

My second major testing strategy is to rely on diagnostic test queries.

Instead of sampling a few rows and hoping that the sample is representative, I usually prefer to query the whole set for violations of important rules.

This changes the shape of the test.

Instead of writing a query that says:

• show me a handful of records so I can inspect them

I write queries that say:

• show me every record that violates this rule

If the query returns 0 rows, then that rule passed.

That is a very different testing mindset.

It treats the database not just as the thing being tested, but also as the thing doing much of the testing.

Examples of the Kinds of Constraints That Matter

The exact rules depend on the data mart, but the pattern is broadly reusable.

Diagnostic queries can test for things like:

• nulls in fields that should always be populated
• duplicate business keys where uniqueness should hold
• records outside an allowed date range
• amounts that do not reconcile to expected totals
• orphaned rows that should always join to a parent
• statuses that violate valid state combinations
• records that break allocation or percentage rules
• dimensional combinations that should be impossible

Each query is a focused test of a business rule, technical rule, or structural expectation.

When the query returns rows, it surfaces issues. When the query returns none, it gives confidence.

Why This Scales So Well

This strategy scales because it does not depend on a person remembering where the risky rows might be.

The query looks everywhere.

And the collection of queries grows over time.

That is one of the best properties of this method: it behaves like a tree we can keep extending.

As:

• new edge cases are found
• source systems evolve
• business rules change
• old defects teach us new lessons

we add more diagnostic queries.

So the test suite becomes smarter through real experience.

That makes it useful for:

• project testing
• regression testing
• operational monitoring
• production data health dashboards

The same queries that catch problems during development can often remain useful in production, where they become ongoing indicators of data quality.

Why These Two Strategies Fit Together

These are not competing approaches. They reinforce each other.

The repeated-run comparison strategy answers:

• what changed between one run and another?

The diagnostic query strategy answers:

• what rules are being violated in the current result set?

Together, they create a practical testing system for data marts:

• reruns and comparisons detect drift
• diagnostic queries validate constraints
• humans investigate only the surfaced issues

That is the model I trust most.

It is realistic about scale, and it respects the strengths of both machines and people.

Machines are excellent at:

• comparing large outputs
• scanning full datasets
• surfacing anomalies consistently

Humans are excellent at:

• deciding whether a difference is meaningful
• interpreting business context
• understanding whether a surfaced issue is a defect, an expected change, or a deeper modeling problem

Good testing strategy should reflect that division of labor.

Why I Prefer This Over Pure Sampling

Sampling still has a place. It is often useful for:

• early exploration
• spot checks
• explaining results to business partners
• manually reviewing unusual cases

But sampling is weak as the backbone of a serious test strategy for a data mart.

Why?

Because a data mart usually exists to represent a large operational reality. If our test strategy looks at only a tiny slice, we may miss the exact class of problems that matter most:

• rare edge cases
• low-frequency but high-impact anomalies
• drift that only appears in a subset of the data
• broken assumptions that show up only at scale

That is why I prefer tests that examine the whole set wherever possible.

If we can query the whole set, we should. If we can compare whole outputs, we should.

The cost of doing that is often far lower than the cost of missing a defect that affects downstream trust.

Regression Testing Becomes Much Stronger

One of the most valuable side effects of this strategy is how naturally it supports regression testing.

Once you have:

• prior run outputs
• comparison logic
• a library of diagnostic queries

then a future change can be tested against both:

• prior behavior
• explicit constraints

That is much stronger than relying on memory, scattered spot checks, or a few manually curated examples.

It also makes change review more disciplined.

When someone says, “the change was small,” the test suite can respond:

• here is everything that changed from the prior run
• here are the rule checks that still pass
• here are the rule checks that now fail

That is the kind of evidence that makes regression conversations more concrete.

This Also Improves Production Monitoring

Another thing I like about diagnostic queries is that they do not have to stop being useful after go-live.

Many of them can be promoted into production data health checks.

That means the testing investment continues to pay off by helping answer questions like:

• is today’s data still within expected constraints?
• did a source system change create new invalid combinations?
• are duplicate keys appearing again?
• did a reconciliation that used to pass now start failing?

So the testing framework becomes more than a release gate. It becomes an operational sensing system for the data mart.

The Underlying Philosophy

The underlying idea is simple:

Do not ask humans to manually manage what computers are good at comparing.

Do not rely on sampling when the database can evaluate the whole set.

Instead:

• rerun and compare
• write queries that surface violations
• investigate the exceptions
• keep expanding the rule tree as reality teaches you more

That is the strategy.

It has served me well because it turns data mart testing into something that is both more rigorous and more humane. The machine does the broad scanning. The humans do the judgment.

And for large, important data marts, that is exactly the balance I want.