How to validate client data before you start building anything

You're three hours into building the "revenue by customer segment" dashboard when the join returns nothing. The orders table has a customer_id like C-1001; the customers table has an id like 1001. They were never going to match, the segment breakdown the client actually asked for is impossible with the data as supplied, and now you have to email them to ask for a fixed export — three hours in, looking like you didn't check. Fifteen minutes of validation at the start would have caught it before you'd built a single visual. The point of validating first is simple: never build on data you haven't proven can answer the question.

Cleaning asks "is it tidy"; validation asks "does it fit"

Validation is a different question from cleaning, and it comes first. Cleaning makes the data neat. Validation asks whether the data — neat or not — can actually support what the client wants to see. A perfectly clean orders file is still useless for a segment dashboard if it has no way to join to segments. So before you invest build time, you check fitness for purpose: the right columns exist, the keys join, the grain is what you think, the dates cover the period, and the totals are in a believable range. It's the cheapest fifteen minutes in the whole engagement.

The example: the join that was never going to work

Here's the check that would have saved the three hours. Two tables, the key you plan to join on:

orders.customer_id     customers.id
C-1001                 1001
C-1002                 1002
C-1003                 1004

Before building anything, measure the match rate — what fraction of orders find a home in the customers table:

=SUMPRODUCT(--ISNUMBER(MATCH(orders_id, cust_id, 0))) / COUNTA(orders_id)
= 0%

Zero. The C- prefix means not one order matches, so the join is dead on arrival. Even after you strip the prefix, that third row (C-1003 against a customers table that jumps to 1004) is an orphan — a real order with no customer record, which will silently vanish from the segment totals. A match rate isn't a nice-to-have; it's the single number that tells you whether the central chart is buildable, and it takes one formula to get before you've committed to anything.

Five checks to run before you build

1. Fitness for the question. List what the client asked to see, then confirm a column exists for each dimension and measure. No segment column means no segment chart — find out now, not in hour three.
2. Join match rate. For every table you'll combine, measure the percentage of keys that match. Below 100%, find out why before you rely on the join.
3. Grain. Confirm what one row represents — one order, or one order-line, or one daily snapshot. Getting the grain wrong double-counts everything downstream.
4. Date coverage. Check the min and max date and that every period in the range is present. A "monthly trend" with March missing is a gap you must flag before, not after, you draw the line.
5. Range sanity. Eyeball the top and bottom of each key measure. A max order of £2m or a negative quantity is a data question that changes what you build.

Fifteen minutes, five checks, and you either start building with confidence or send the client one precise email — "the customer IDs in orders don't match the customers table; can you export with the raw ID?" — before you've wasted an afternoon.

Validation is what makes your estimate honest

There's a second payoff beyond saved hours. When you validate up front, your project estimate stops being a guess, because you've seen the actual state of the data before you quoted the build. You catch the unmatched keys, the missing month, and the wrong grain while they're cheap questions instead of expensive surprises, and you can tell the client "the build is two days once the join is fixed" with a straight face. Building on unvalidated data isn't moving fast — it's borrowing time from your future self at a punishing rate.