Two hundred checks, one scan

Most data quality frameworks start with one check per query. That’s fine until you have a couple hundred checks running against tables with hundreds of millions of rows.

Two hundred checks means two hundred scans. The checks are nothing — count nulls, count distincts, compare this period’s distribution to last period’s. The scan is the entire cost, and you’re paying it over and over to answer questions you could have answered together.

That’s not a figure of speech. On a managed platform you rent the cluster by the second, so the scan count is the invoice. Two hundred reads of the same table is two hundred times the compute for the same answers.

So batch them. Most checks against a table are aggregates over the same rows, so they fit in one pass: one scan, a pile of SUM(CASE WHEN ...) expressions, one result row with all the answers. Two hundred scans become one.

Then you find out what batching costs you.

Everything shares a fate now

Separate queries fail separately. Batched ones don’t, and that turns out to matter more than it sounds like it should.

Vectorized engines like Photon process data in columnar batches — that’s where the speed comes from. Some expressions can’t be vectorized. Python UDFs are the obvious one, since they force row-at-a-time serialization between the JVM and a Python worker. On a table with a few hundred million rows I’ve watched that turn a two-minute scan into an hour.

Put that UDF inside a batched aggregate and it doesn’t just slow down its own check. It de-vectorizes the whole query. Forty other checks were riding along in that scan and now they’re all on the slow path too.

The bill follows. A job that fell back doesn’t just finish late — it holds the cluster an order of magnitude longer to produce identical output. Same result, many times the money. Picture Bugs Bunny shoveling cash into a furnace, except the furnace is a compute cluster and the cash is your quarterly budget.

Two fixes

Compile the rules to SQL instead of calling out to code. Most of what people reach for a UDF to do — format validation, bucketing, some business rule — is expressible as CASE WHEN. The generated SQL is horrible to look at. It also stays in the vectorized engine, and that’s the difference between minutes and hours.

For the ones that genuinely need a UDF — decryption, mostly — pull them out of the batch and run them standalone. They’ll be slow. They were always going to be slow. But they’re slow by themselves instead of taking forty other checks down with them.

So the batch is a fast path with an entry requirement, and anything that can’t meet it gets its own query.

When it breaks

Worth knowing in advance, because the errors lie to you.

If something in a batched aggregate fails — bad cast, malformed expression — every check in the batch fails with the identical error. Forty-two failures across a dozen check types isn’t forty-two problems. It’s one broken expression and forty-one bystanders.

The check names are useless here. Go straight to the SQL fragment in the query trace; it usually names the expression and the value that broke it. Debugging by check name will take you somewhere unrelated.

The part that generalizes

The expensive thing usually isn’t the work. It’s the setup around the work — the scan, the round trip, whatever you had to load before you could start. Making the work faster gets you a few percent. Doing the setup once gets you the real number.

Which is also where the cost savings live, and why the big ones look implausible when you write them down. Nobody finds a 90% reduction by tuning a query. You find it by noticing the same table is being read a hundred times, or that a job has been quietly running unvectorized for months. The wins aren’t clever — they’re structural, and they were sitting there the whole time.

Sharing creates coupling, though. Once forty things depend on one scan, the question isn’t how fast that scan is anymore. It’s what can poison it.

Two hundred checks in one scan is easy. Keeping the two hundred and first from wrecking it is the actual job.