What if most manual data checks could validate themselves?

Overview

AI systems are only as trustworthy as the data underneath them. Large ETL pipelines built on PySpark, Kafka, Trino and Airflow move data between many sources, and every transformation is a chance for silent corruption.

I defined big data testing strategies for these pipelines and built an LLM-powered data validation framework that automated more than 70% of manual ETL checks. The platform and its data are confidential, so this page describes the testing strategy and the general design of an LLM-assisted validator rather than the internals of one product.

Problem statement

Manual ETL validation does not scale. Checks written and run by hand are slow, vary between people, and fall behind as pipelines grow.

In practice the gap shows up in familiar ways:

• Coverage lags change. New sources, columns and transformations arrive faster than someone can write checks for them.
• Inconsistency. Two engineers checking the same table look at different things, so results are hard to compare.
• Late discovery. Problems are found downstream, in a report or a model input, long after the load that caused them.
• Repetitive judgement. Much of the manual effort goes on checks that follow a pattern: row counts, nulls, ranges, referential integrity, reconciliation between source and target.

Engineering objectives

1. Define a testing strategy end to end, from ingestion through transformation to query, so every stage has an owner and a kind of check.
2. Turn checks into code that is versioned, reviewed and run automatically, rather than one-off scripts.
3. Use language models where they remove repetitive effort, while keeping the final validation deterministic and auditable.
4. Run fast enough to fit into pipeline changes, so validation happens before data is consumed, not after.
5. Keep performance in view, using standard benchmarks so changes to the query layer are measured, not assumed.

Solution architecture

The testing strategy follows data through the platform. Each stage gets the kind of check that suits it.

The validation layer has three parts:

• Rule engine: expectations captured with Great Expectations and PyDeequ, so rules are explicit, versioned and executable at scale.
• LLM assistance: language models built into the framework to take on checks that previously needed a person.
• Orchestration and reporting: validation runs as part of the pipeline schedule, with results reported where the team can act on them.

Alongside functional validation, performance baselines use the TPC-H and TPC-DS benchmarks, so the query layer is tested for speed as well as correctness.

Technical approach

Expectations as code

Rules expressed as code become reviewable assets. A rule such as “order totals are never negative” or “every order references an existing customer” lives in version control, runs on every load and produces a result that can be compared over time. Great Expectations suits declarative, documented checks on tables. PyDeequ suits metric-based checks computed on Spark at large volumes. Using each where it fits avoids forcing one tool to do both jobs.

Where a language model helps

The guiding design principle for an LLM-assisted validator is simple: let the model propose and explain, let deterministic code decide. Model output that directly marks data as valid or invalid is hard to audit and can vary between runs. Model output that becomes a reviewed rule, executed by a standard engine, is neither.

Typical places a language model reduces manual effort in this kind of framework:

• Drafting candidate rules from schema, column names, sample profiles and written business rules, for an engineer to review.
• Translating a plain-language check (“every shipment should have a delivery date after its ship date”) into an executable rule.
• Summarising validation failures into a readable explanation of what broke and where to look.

An illustrative example of a model-drafted rule after review, expressed as configuration:

# Illustrative only. Drafted with model assistance, approved by an engineer.
table: orders
rules:
  - column: o_totalprice
    check: min_value
    value: 0
  - column: o_custkey
    check: foreign_key
    references: customer.c_custkey
  - column: o_orderdate
    check: not_null

Reconciliation

Source-to-target reconciliation is where much of the manual time traditionally goes. A typical approach compares counts, aggregates and keyed samples between source and target in SQL on Trino:

-- Illustrative reconciliation check: totals per day must match.
SELECT s.load_date, s.total AS source_total, t.total AS target_total
FROM source_daily_totals s
JOIN target_daily_totals t ON s.load_date = t.load_date
WHERE abs(s.total - t.total) > 0.01;

Validation strategy

A validation framework needs its own tests. The practices that make a framework like this trustworthy are:

• Seeded defects. Known errors (nulls, duplicates, out-of-range values, broken keys) are injected into test data to confirm the rules catch them.
• Rule review. Model-drafted rules are reviewed before they run in a pipeline. The model widens coverage; a person owns correctness.
• Comparison with manual results. During adoption, automated results are compared with the manual checks they replace, so gaps are found before the manual step is retired.
• Benchmark repeatability. TPC-H and TPC-DS runs are repeated under the same conditions so performance changes can be distinguished from noise.

Metrics and measurement

The headline measure was the share of manual ETL checks now automated: more than 70%.

Metrics commonly tracked in this kind of framework also include:

• Rule coverage per table and per pipeline stage.
• Validation pass rate and the number of failures caught before data is consumed.
• Time from pipeline change to validated result.
• Proportion of model-drafted rules accepted, edited or rejected at review, which shows whether the model is helping.
• Query performance against TPC-H and TPC-DS baselines.

Challenges and trade-offs

• Determinism versus flexibility. Language models are flexible but variable. Restricting them to drafting and explanation trades some automation for auditability, which is usually the right trade for data validation.
• Rule sprawl. Easy rule generation can produce many low-value rules. Reviews should ask whether a rule protects something that matters.
• Cost at volume. Running checks on very large tables costs compute. A typical trade-off is full checks on critical tables and sampled or metric-based checks elsewhere.
• Sensitive data. Sending raw records to a model is often unacceptable. Schemas, profiles and synthetic samples are safer inputs than production rows.

Outcome and lessons learned

The framework automated more than 70% of manual ETL checks. Validation became repeatable and fast enough to run on every pipeline change, and the team’s time moved from writing checks to reviewing the ones that matter.

The lessons I take from it:

• Most manual data checking is pattern work, which is exactly where automation pays off.
• Language models are most useful as accelerators for people writing rules, not as judges of data.
• A testing strategy comes first. Tools only help once it is clear what each stage of the pipeline must guarantee.

Further reading

• Source-to-target reconciliation at lakehouse scale
• From expectations to a production data-quality system
• Validating CDC pipelines
• Great Expectations: a data quality framework, Part 3 (Medium)
• Best practices and frameworks for data quality, Part 2 (Medium)
• Great Expectations documentation
• PyDeequ on GitHub
• TPC-H benchmark