Topic: Part 11. Data Validation: validation.md

Difficulty level: Medium

Estimated study time: 2-3 hours

Prerequisites: Basic understanding of dbt (models, tests, schema.yml)

SQL knowledge at the level of writing queries and singular tests

Familiarity with ODCS and ODPS specifications

Understanding of the Specification-Driven Development (SDD) concept

Experience with the command line and running dbt commands

Learning objectives: Formulate a verification fact as a command, SQL query, or manual reviewer step, distinguishing it from a wish

Divide facts into automatic and manual, justifying the choice of type for each

Create a minimal validation.md for a mart, including facts about grain, PII, contract, lineage, and manual review

Apply the practice of writing validation.md before SQL code to limit the solution size

Link multiple validation layers (specification, ODCS, schema.yml, dbt tests) into a single validation artifact

Overview: Data validation in Specification-Driven Development is not an abstract assertion like "the data is correct," but a set of concrete facts that can be executed. Each fact must link the model specification, the SQL artifact, and the evidence. If a fact cannot be run as a command, executed as an SQL query, or confirmed by a manual reviewer step, it remains a wish. The validation.md document captures these facts in a versioned and readable form, turning a data release from a set of SQL patches into an engineering process. This guide covers the structure of validation.md, the minimum set of facts, the difference between automatic and manual checks, and the practices of writing validation before writing code.

Key concepts: Verification fact: An assertion about data that can be executed in one of three ways: a command (e.g., dbt test), an SQL/singular test, or a documented manual reviewer step. A fact links the specification, SQL, and evidence. Without this linkage, the assertion remains a wish.

Automatic fact: A verification fact that is run by a command and ends with an exit code or a set of rows. Example: dbt test --select mart_customer_360 expects exit code 0 and no errors. It is suitable for properties that can be formalized in the schema and data logic: uniqueness, not_null, ranges, formats.

Manual fact: A verification fact that requires reading a change or confirmation from the product owner/reviewer. It is needed where the decision depends on the contract context: for example, whether adding a new field to the mart grain is acceptable. A manual fact is only strong when it is explicitly stated what to read and what conclusion to draw.

Mart grain: The definition of what is considered a single row in a mart (e.g., one customer_id = one row). A fact about grain is usually automatic: uniqueness and not_null of the key are checked. A change of grain requires manual review.

PII protection: A check that no direct personal data (email, phone, passport) has ended up in the mart. It is implemented as a singular test that returns 0 rows if PII fields are present in the output columns of the model.

Mart contract: A list of mandatory fields, their types, and rules, recorded in schema.yml and ODCS. The contract verification fact consists of tests for mandatory fields and types, plus a manual fact: a change does not alter contract fields without an explicit assertion.

Lineage and input models: A list of sources from which the mart is assembled. The verification fact captures which models are fed into the input and whether sources have changed without updating the specification.

SDD (specification-driven development): A development approach in which the specification is written before the code. validation.md is one of the SDD artifacts that makes the specification verifiable and resilient to changes.

dbt singular test: An SQL query in a tests/assert_*.sql file that returns rows when a rule is violated. An empty result means the test passed. It is used for complex checks that cannot be expressed by generic schema.yml tests.

Weak fact vs strong fact: A weak fact is a phrase like "check data quality" or "looks normal." A strong fact contains a command, an expected result, and a status: "dbt test --select mart_customer_360 returns exit code 0, customer_id is unique and not_null, status: accepted."

Important dates: The concept of validation.md in SDD: Appears at the mart design stage in a data project, before writing dbt models. The file is stored in specs/validation/ and versioned in Git along with specifications and code.

Moment of fact revision: When any of the layers changes: the model specification, the ODCS contract, models/schema.yml, dbt models, or the composition of sources. validation.md must explicitly show which facts need to be revised.

Practice exercises: Name: Rewrite a weak item in three forms

Problem: The old release checklist has an item "check data quality in mart_customer_360." Rewrite it in three forms: 1) a dbt command with an expected result, 2) an SQL/singular test with a description of what it should return, 3) a manual reviewer fact stating what exactly to read and what conclusion to draw.

Solution: Form 1 (dbt command):

### F1 — key quality
- Command: dbt test --profiles-dir . --select mart_customer_360
- Expectation: exit code 0; customer_id unique and not_null tests pass.
- Status: accepted.

Form 2 (singular test):

-- tests/assert_customer_360_no_orphan_orders.sql
select c.customer_id, c.last_order_id
from {{ ref('mart_customer_360') }} c
left join {{ ref('dim_orders') }} o on o.order_id = c.last_order_id
where c.last_order_id is not null and o.order_id is null

- Command: dbt test --profiles-dir . --select assert_customer_360_no_orphan_orders
- Expectation: the test returns 0 rows.
- Status: accepted.

Form 3 (manual fact):

### F3 — grain review
The reviewer confirmed that adding product_code to mart_customer_360 would change the grain (one row per customer × product instead of customer) and is not part of the current phase. The decision is recorded in ticket DATA-1234.

Complexity: beginner

Name: Assemble a minimal validation.md for a mart

Problem: For the mart mart_orders_summary (one row = one order_id), create a minimal validation.md that includes: a fact about grain, a fact about PII, a fact about mandatory contract fields, a fact about lineage/input models, and one manual fact. Model specification: models/schema.yml contains order_id (unique, not_null), customer_id (not_null), total_amount (not_null), order_date (not_null). Sources: stg_orders, stg_customers. PII: email, phone must not end up in the mart.

Solution: ```markdown

validation.md — mart_orders_summary

F1 — mart grain

• Command: dbt test --profiles-dir . --select mart_orders_summary
• Expectation: order_id is unique and not_null, exit code 0.
• Status: accepted.

F2 — PII did not end up in the mart

• Command: dbt test --profiles-dir . --select assert_orders_summary_no_pii
• Expectation: the test returns 0 rows (no email/phone fields in the output columns).
• Status: accepted.

F3 — mandatory contract fields

• Command: dbt test --profiles-dir . --select mart_orders_summary
• Expectation: customer_id, total_amount, order_date are all not_null.
• Status: accepted.

F4 — lineage and input models

• Command: dbt list --select +mart_orders_summary --output name
• Expectation: the list contains stg_orders and stg_customers as direct predecessors.
• Status: accepted.

F5 — review of grain change

The reviewer confirmed that adding order_line_id to mart_orders_summary would change the grain and is not part of the MVP phase. The decision is recorded in ticket DATA-7788.

Complexity: intermediate

Name: Find and fix a typical mistake

Problem: In the project, a colleague wrote the following fact in validation.md: "The data in the mart is correct and ready for release." Explain why this is a weak fact and rewrite it as a strong one. Hint: the mart is mart_payments, the key is payment_id, the source is stg_payments.

Solution: Analysis: the phrase "the data is correct" is a wish, not a fact. It contains no command, no expected result, and cannot be run. An agent may interpret correctness as the absence of execution errors, an analyst as the absence of empty values, a product owner as preservation of grain. A fact removes this ambiguity only if it contains a command, an expectation, and a status.

Strong fact:

F1 — payment_id is unique and not null

• Command: dbt test --profiles-dir . --select mart_payments
• Expectation: payment_id unique and not_null tests pass, exit code 0.
• Status: accepted.

You can additionally add facts about amounts and dates: total_amount ≥ 0, payment_date within the last 90 days.

Complexity: beginner

Name: Decide which fact to automate and which to leave manual

Problem: For the mart mart_customer_360, a list of assertions is given. For each one, indicate: automatic or manual fact, and justify the choice. Assertions: 1) customer_id is unique; 2) there are no PII fields in the mart; 3) adding product_code will not break the grain; 4) total_orders is calculated using the actual cancellations; 5) created_at is filled in for all rows.

Solution: 1) Automatic — a dbt test for unique and not_null, formalizable.
2) Automatic — a singular test that checks the set of model columns for the presence of PII names or values.
3) Manual — the decision on the admissibility of a new field in the grain depends on the contract and phase, and requires reading the specification and confirmation from the owner.
4) Automatic (partially) — a singular test that compares total_orders with the aggregation from the source and verifies that cancellations are accounted for. Additionally, a manual fact: the reviewer confirmed the cancellation handling logic in the model code.
5) Automatic — a generic not_null test on the created_at column.

Complexity: intermediate

Name: Link validation layers in one fact

Problem: Describe how a single verification fact links the model specification (specs/models/mart_customer_360.md), ODCS, models/schema.yml, a singular test, and a reviewer report. Show what happens to the fact if any of the layers changes.

Solution: Example of the fact "customer_id is unique and not null":
- The specification captures the grain and the list of mandatory fields.
- ODCS describes customer_id as a mandatory field of type string.
- models/schema.yml contains unique and not_null tests on customer_id.
- The singular test additionally checks that customer_id is not NULL after all joins (protection against a LEFT JOIN with NULL on the right).
- The reviewer report records that the tests passed in CI and the fact status is "accepted."

If the specification changes (for example, the grain becomes customer × product), all four layers need to be revised: ODCS changes, in schema.yml uniqueness is moved to a composite key, the singular test is updated, and the reviewer re-checks the fact. validation.md must explicitly indicate which facts have been revised.

Complexity: advanced

Case studies:
Name: Customer 360 mart release: how validation.md saved from a PII leak

Scenario: The analytics team was preparing the release of the mart mart_customer_360 for marketing. The mart was assembled from stg_customers (containing email and phone) and stg_orders. A condition did not make it into code review, and the email field accidentally remained in the output set. The model specification explicitly prohibited direct PII in the mart.

Challenge: Without a formal check, the fact "PII did not end up in the mart" existed only in the analyst's head. The agent considered the release successful based on the dbt run exit code, the analyst based on the absence of empty values, the product owner based on grain preservation. No one checked the composition of the columns.

Solution: The team adopted validation.md as a mandatory release artifact. For mart_customer_360, fact F2 was written with the singular test assert_customer_360_no_direct_pii, which compared the list of model columns against the list of prohibited PII fields. The test was run with the command dbt test --select assert_customer_360_no_direct_pii and had to return 0 rows. The fact was included in the release checklist and blocked the deploy on failure.

Result: On the next attempt to bring email into the mart, the test failed already in CI, the reviewer saw a violation of fact F2 and rejected the merge. PII did not leak into the marketing mart. The release process became reproducible: the fact can be re-run at any time and produce the same answer.

Lessons learned:
The fact "PII did not end up in the mart" without a command and expected result is a wish, not a check.

A singular test protects against regressions that are not visible in generic schema.yml tests.

validation.md must be a versioned artifact, not a message in chat.

Related concepts:
PII protection

dbt singular test

Verification fact

Manual and automatic fact

Name: Grain change without validation: how we lost a month of reporting

Scenario: An analyst added the product_sku field to the mart mart_sales so that the dashboard could show sales by product. The change passed code review, the not_null and uniqueness tests on order_id remained green. Two weeks later, the finance department discovered that revenue had doubled: the mart grain had effectively become order_id × product_sku, even though the name and tests said the grain was order_id.

Challenge: There was no validation.md in the project. The tests checked only column properties, but did not capture the fact "the mart grain is one order_id = one row." No one explicitly recorded that adding product_sku changes the grain. The reviewer received no hint about what to pay attention to.

Solution: After the incident, the team introduced a mandatory validation.md for each mart. For mart_sales, fact F1 appeared (grain: order_id is unique and not_null) and manual fact F5: "The reviewer confirmed that the change does not alter contract fields and the grain without an explicit assertion from the owner." In the ticket for adding product_sku, the manual fact would require an explicit decision: either change the grain and the mart name, or move product_sku to a separate layer.

Result: Three months later, the team proposed adding the channel field to the mart. The reviewer read validation.md, saw fact F5, and stopped the merge, requesting a separate decision about the grain. Release time decreased because the review followed a rubric rather than intuition.

Lessons learned:
Key uniqueness is not the same as a recorded grain. Grain is a contract; it must be checked manually as well.

validation.md is written before SQL: it limits the size of the solution and suggests which tests are needed.

If validation.md is written after SQL, it turns into a justification for what has already been done, rather than a release safeguard.

Related concepts:
Mart grain

Manual fact

Mart contract

SDD

Name: Lineage as a verification fact: catching a silent source change

Scenario: The platform team updated the schema in stg_orders by renaming the column order_total to order_total_amount. The dbt models of mart_sales continued to assemble, tests passed, because the code used ref() and adaptation to the new name happened automatically through a variable rename. But the mart mart_sales_weekly broke: one of the downstream dashboards showed zeros, because it used a hard-coded SELECT referencing the old name.

Challenge: Generic tests did not fail, because mart_sales_weekly was assembled from an intermediate model in which the column had already been renamed. There was no fact capturing the composition of input models and their schema. The reviewer received no signal that the source had changed.

Solution: The team added a fact F4 about lineage to validation.md for each mart: the command dbt list --select +<mart> --output name captures the list of predecessors. Additionally, singular tests were introduced that check for the presence of mandatory columns in the source (information_schema). When renaming in stg_orders, such a test would have failed at the mart build stage, and the reviewer would have seen a violation of fact F4.

Result: A quarter later, the team noticed an attempt to delete the customer_segment field in stg_customers. The test for the presence of the column in the source failed, fact F4 in validation.md was marked as needing revision, and the change did not get into main until an explicit decision was made.

Lessons learned:
Lineage is part of the contract. A change in the composition or schema of sources must be visible in validation.md.

Automatic tests on information_schema protect against silent renames that do not break generic tests.

validation.md links several layers: the specification, ODCS, schema.yml, singular tests, and review.

Related concepts:
Lineage and input models

dbt singular test

Verification fact

Mart contract

Study tips:
Write validation.md before SQL. First formulate the facts (grain, PII, contract, lineage, manual review), then the tests and the model. This reduces the size of the solution and makes the review structured.

Keep validation.md in Git alongside the specifications and code. A file in chat or in a Wiki cannot serve as evidence of a release.

Turn every weak checklist item into a strong fact: add a command, an expected result, and a status. The phrase "looks normal" is not a fact.

Do not try to automate everything. Decisions that depend on the contract (for example, what counts as an active customer) should remain manual facts with an indication of what exactly to read and what conclusion to draw.

Use singular tests for checks that cannot be expressed by generic tests: column composition, date ranges, absence of PII, referential integrity after joins.

When any layer changes (specification, ODCS, schema.yml, model, source), go through validation.md and mark the facts that need to be revised.

Use Qwen (or another LLM) for the validation.md draft, passing it links to specs/models/, ODCS, ODPS, and models/schema.yml. Do not ask the LLM to change dbt models — only to generate validation facts.

Add validation.md to the pull request checklist: the reviewer must read the file before the change and mark which facts have been revised.

Distinguish grain (contract) from key uniqueness (test). Uniqueness can be checked automatically, a grain change only manually with confirmation from the owner.

The minimum set of facts for a mart: one about grain, one about PII, one about mandatory contract fields, one about lineage, and one manual fact about the contract remaining unchanged without an assertion.

Additional resources:
dbt documentation — tests: https://docs.getdbt.com/docs/build/tests — official documentation on generic and singular dbt tests.

dbt documentation — singular tests: https://docs.getdbt.com/docs/build/singular-tests — guide on writing singular SQL tests.

ODCS (Open Data Contract Standard): https://bitol-io.github.io/open-data-contract-spec/ — a data contract standard that links with validation.md.

ODPS (Open Data Product Standard): https://opendataproducts.org/ — a data product standard used in SDD as one of the specification layers.

Specification-Driven Development for data projects: the SDD concept, in which validation.md is a mandatory release artifact, not an optional document.

dbt-utils package: https://github.com/dbt-labs/dbt-utils — a package with ready-made tests (expression_is_true, accepted_range, relationships, etc.) that can be used in validation.md.

Summary: validation.md is the place where the SDD textbook becomes strict. A verification fact differs from a wish in that it can be executed: by a command (dbt test), an SQL/singular test, or a documented manual reviewer step. A minimal validation.md for a mart contains five facts: grain, PII protection, mandatory contract fields, lineage/input models, and a manual fact about the contract remaining unchanged without an assertion. Not all facts need to be automated — decisions that depend on the contract (for example, the admissibility of a new field in the grain) remain manual. validation.md links several layers: the model specification, ODCS, models/schema.yml, singular tests, and the reviewer report. When any layer changes, the file must explicitly show which facts need to be revised. The main practice: write validation.md before SQL — this limits the size of the solution, suggests the necessary tests, and gives the reviewer a ready-made rubric. If validation.md is written after SQL, it turns into a justification for what has already been done, rather than a release safeguard.