Testing Millions of Rows Without Slowing Down Your Database

Validating data at scale is hard. Testing millions of records in a production database without slowing anything down is even harder.

In this article, I’ll walk through a real-world architecture I built to run tens of millions of data validation tests with minimal production impact, fast execution, and reporting that’s actually useful for debugging. I’ll stay focused on the system design (technical deep dives will come in follow-up posts).

If you’ve ever wondered how to:

• Test data integrity safely in production
• Validate millions of rows without locks or performance hits
• Turn noisy failures into actionable insight,

Then this approach is for you.

The Core Problem

We manage a relational database with millions of rows, where each record depends on data from multiple tables.

To validate data integrity, we needed to:

• Join multiple large tables
• Collect derived data per record
• Execute validation rules at scale

At the time, this meant running over 40 million validation tests, a number that continues to grow every day as new data and rules are added.

All of this had to run against production data, with minimal performance impact.

At the same time, the system needed to be Fast, Easy to maintain, Easy to debug and Able to produce clear, actionable reports

Key Challenges

1. Minimize production database load Heavy joins and rule execution could not run directly on production.
2. Reduce execution time as volume grows The solution needed to scale linearly and remain predictable.
3. Optimize for maintainability and debugging When tests fail, engineers should understand why in minutes, not hours.
4. Make reporting meaningful Looking at 100,000 raw failures is useless without grouping and context.

️ The Architecture

Data flows from the production database into the data lake, then through the validation engine, into aggregated datasets, and finally into the reporting UI.

The core idea is simple: do all the heavy lifting outside of production.

Step 1: Data Ingestion (ETL)

To minimize load on production, I introduced a partitioned ingestion strategy.

What I did:

• Partitioned data by date
• Extracted raw data daily
• Stored everything in a cloud data lake

Why this works:

• Limits the amount of data processed at once
• Avoids long-running queries on production
• Makes reprocessing deterministic and reproducible

Step 2: Run Validation Tests on the Data Lake

Once data was safely stored in the data lake, all validation work happened outside production.

Process:

• Read data partition-by-partition (daily)
• Run validation rules on each partition
• Store validation results separately

At this point, production systems were completely decoupled from validation workloads.

Step 3: Aggregate Results for Performance

Raw validation results were too granular for real analysis.

So I created derived datasets:

• Daily aggregates
• Monthly aggregates
• Yearly aggregates

These pre-aggregations drastically improved performance for reporting and analysis.

Step 4: Analyze & Categorize Failures

On the first full run, the system produced ~100,000 failures. Manually inspecting them was impossible.

The solution:

• Group failures by type, rule, and metadata
• Categorize them into meaningful buckets
• Enable filtering by severity and domain

At this point, validation stopped being a simple pass/fail signal and became a diagnostic tool.

Because raw validation results were stored separately and enriched with expectation metadata, engineers could immediately see:

• which rule failed,
• how many records were affected,
• and whether the issue was a regression or a known problem.

Debugging shifted from manual inspection to simple filtering, dramatically reducing time to root cause.

Step 5: Defining and Scaling Data Validation Rules

I used Great Expectations to define and manage data validation rules. It’s an open-source data quality framework that expresses validation logic as clear, human-readable expectations.

This made validation easier to scale and debug by:

• keeping rules declarative and easy to review,
• separating rule definitions from execution,
• producing rich, structured results for aggregation and reporting,
• and integrating seamlessly with DuckDB and Parquet for fast execution.

Step 6: Build a Reporting UI

To make results usable for engineers and stakeholders, I built a lightweight reporting UI.

Key features:

• Interactive filters,
• Dashboards, Tables and Charts
• Failure breakdowns,
• Trend analysis over time,
• Drill-down into specific categories.

I used Streamlit for its simplicity and fast setup, but any BI tool (e.g. Metabase) would work just as well.

Tools & Technologies

Dagster

An asset-based data orchestrator that made pipelines observable, testable, and production-ready.

DuckDB

A fast, columnar analytical database. Perfect for large joins and validation logic on Parquet data.

Data Lake (S3 + Parquet)

Cheap, scalable, and ideal for partitioned processing.

Streamlit

The fastest way to turn validation results into an interactive UI.

Final Thoughts

This system reinforced a few key lessons:

• never run heavy validation directly on production,
• always rely on partitioning at scale,
• and treat debuggability as just as important as correctness.

By aggregating results and enriching them with metadata, we turned noisy failures into clear insights. In practice, this approach allowed us to validate millions of records without impacting production, give engineers actionable feedback, and scale confidently as the data continued to grow.

If you have any questions, feel free to reach out to me on LinkedIn.

I’ll be diving deeper into more technical topics in upcoming stories.