Data Schema Validation: Your First Gate on Any Export

Before any tool reads a single value, data schema validation asks whether the file matches the shape you agreed on: these columns, these types, no more than this many blanks. It is a comparison between a file and a written description of what the file should look like. That description is the schema, and how well you write it sets how much a pass is worth.

What a schema says

A schema is short. For the gate it is a JSON file with one entry per column, and each entry says four things:

• The column's name
• Its type: text, integer, decimal, date, email, or a fixed list of allowed words
• Whether the column is required
• The largest share of blanks you will accept

The sample schema that ships with the gate shows the range. order_id is an integer, required, with no blanks allowed. status is optional, may be up to 10% blank, and must be one of four words: new, paid, shipped or refunded. notes is free text and may be half empty.

Dates are the strict one. The date type means YYYY-MM-DD. A cell that reads 05/03/2026 counts as a mismatch, even though a person reads it as a date at a glance.

Three layers, three questions

Checks on a data file stack in a fixed order, and each layer asks something different.

• Schema validation asks about shape. Do the rows line up under the right headers, and does each column hold the kind of thing it should?
• Record-level validation asks about values. Is this total the sum of its parts? Is this code on the allowed list for this customer?
• Cross-system reconciliation asks about agreement. Does this file say the same thing as the other systems that describe the same orders?

Run them out of order and the later layers misreport. A record check on a shifted file will reject an email address that is really a phone number that slid over one column. The report says "bad email." The fault is a stray comma four fields to the left.

Types sit on the border

It is common to list type alongside headers and row width as "structure." The gate does not treat them the same way.

A ragged row is a structural failure. The file no longer lines up, so the verdict is BLOCK, even when every column scores 100. A wrong type is different. The file still lines up. One column holds a value it should not. So the gate grades it: the column reads DIRTY and the file reads FIX.

Whether you load a FIX file is your call, and the honest answer depends on the destination. If it rejects a bad row and tells you, loading and cleaning up afterward may be fine. If it stores an unreadable date as an empty field and says nothing, treat FIX as a stop.

The schema is a second thing to keep current

A schema is a promise about what the source system sends. Source systems change, and the gate reacts differently to each kind of change:

• A required column renamed at the source reads as missing. The file is BLOCK, though every value in it is fine. From the import's side that is correct: it cannot find the column either.
• An optional column that disappears is skipped. The report shows it as absent, and the file can still be READY. If its absence should stop the file, mark it required.
• A new column the schema has never heard of passes without comment. The gate checks the columns you listed. It does not complain about extras.

So a schema catches what goes missing and stays quiet about what gets added. Someone has to own the file and update it when the source changes on purpose. A schema nobody maintains ends one of two ways: it blocks good files until people stop trusting it, or it gets loosened until it passes everything.

There is no setting that removes this cost. A strict schema is more work and catches more. A loose one is less work and tells you less each time it passes.

Pairs well with

The record-level layer is the Document Field Validator ($79), which runs each record against a rule pack and returns PASS, REVIEW or REJECT; a critical-rule failure rejects the record however many soft rules pass. The agreement layer is the Multi-Source Data Reconciliation Engine ($89), which reconciles three or more sources on one match key and rolls them up to RECONCILED, FIX or NOT RECONCILED. If the export feeds a spreadsheet model, the Spreadsheet Formula & Model Integrity Auditor ($69) reads the .xlsx and grades the formulas, not the values.