Topic: Part 22. Practical Assessment: full SDD Data cycle from specification to reviewer report
Difficulty level: Medium
Estimated study time: 3–4 hours of independent work (including writing the specification, implementing the model, and preparing the reviewer report)
Prerequisites: Confident SQL skills (aggregations, window functions, JOIN)
Basic familiarity with dbt (models, tests, sources, schema.yml)
Understanding of the data contracts concept and SDD Data (Specification-Driven Development for Data)
Knowledge of principles for working with PII (Personally Identifiable Information)
Experience with git and the command line
Understanding of the difference between staging, intermediate, and mart layers
Learning objectives: Critically evaluate data specifications and find at least 10 weaknesses before starting SQL implementation
Rewrite weak requirements into full SDD Data specifications with grain, PII, contract, and acceptance facts sections
Design a dbt model for the mart layer with a guaranteed grain of "one row per key" and coverage of mandatory tests
Separate the author and reviewer roles, produce a reviewer report distinguishing facts, assumptions, and open confirmations
Conduct an SDD Data retrospective and determine which phase of the next feature should be narrowed or described in more detail
Overview: The final part of the course tests not memory of dbt syntax but the ability to keep a data product's promise from specification to reviewer report. The assessment intentionally starts with a weak specification like "make a useful mart" to test whether the student will stop a weak task before SQL. The assessment consists of four blocks: 12 quick written questions on understanding SDD Data terminology; a task to find problems in an intentionally weak specification; rewriting it in proper SDD Data format; and finally, implementing the mart_customer_risk_summary mart in dbt with a full evidence package — specification before SQL, tests, contract impact, reviewer report, and retrospective. The assessment can be taken solo or in pairs (author + reviewer) — the pair format shows that review is an active engineering role, not passive SQL reading. Work is graded on a 25-point rubric: 21+ points means the process is ready to be transferred to a real data product.
Key concepts: Grain (granularity): The source of truth for grain is the model specification or data product contract, not SQL. Grain fixes that each row of the output mart corresponds to exactly one business entity (for example, customer_id). Changing grain always requires human confirmation, even if SQL assembles "cleanly."
ODPS vs ODCS: ODPS (Open Data Product Specification) describes the product and its consumers: why the mart exists, who reads it, what questions it answers. ODCS (Open Data Contract Standard) is the technical contract: schema, SLA, format, owner, versioning. SDD Data uses both: ODPS for product meaning, ODCS for the technical promise.
Dbt tests vs data contract: dbt tests (not_null, unique, accepted_values, relationships) check only part of the form and partial data properties. They do not prove completeness of specification, correctness of metric calculation methodology, and do not replace the product contract. A green dbt build is hygiene, not a promise.
PII policy and prohibited fields: The list of prohibited direct PII (full name, email, phone, passport data) must be fixed in the model specification, in AGENTS.md, and in the reviewer checklist. Whenever possible, it is turned into a singular test or a macro that scans the resulting schema. This turns the policy from a slogan into a verifiable artifact.
Contract drift: A discrepancy between the contract/specification and the actually implemented model. Drift appears when the author of a "small improvement" silently changes the grain, adds a field, weakens a test, or renames an entity. Catching drift before release is the reviewer's main task.
Breaking changes: Changing grain, renaming or removing a contract field, changing metric semantics, changing PII policy, changing SLA. Adding a nullable column is usually not breaking, but can become breaking if downstream systems interpret it as a new business metric or if it changes the grain.
Separation of author and reviewer roles: The reviewer must check the author's assumptions, not defend them. Therefore author and reviewer are different roles (in the pair assessment — two different students). The reviewer does not edit the author's files, but prepares a report with comments on contract, SQL, tests, and process separately.
What dbt build proves: dbt build proves only the executability of the DAG and the passing of declared tests on current data. It does not prove completeness of specification, correctness of methodology, sufficiency of tests, or compliance with the consumer contract.
Schema manifest vs model specification: Schema Manifest (or schema.yml) describes the observed source: real columns, types, tests. The model specification describes the promised mart: grain, consumer, methodology, PII policy, acceptance facts. They are related, but not interchangeable.
Raw data and "fixing" for a green build: The raw layer preserves the input as is and is not "fixed" for a green dbt build. The semantics of empty values and nulls is resolved in staging or is explicitly spelled out in the specification. Otherwise you hide poor data quality behind a valid model.
Acceptance fact: This is an artifact that must contain four elements: the command (SQL/dbt/manual step), the expected result, the execution status, and who confirmed it. Without these four elements, a "check" is just a comment.
Human confirmation: Required when changing grain, PII policy, SLA, contract fields, or risk metric calculation methodology. This is a list of situations in which an AI assistant should not make the decision itself, but should stop and request confirmation.
Important dates: Stage 1 — specification before sql: Creating specs/models/mart_customer_risk_summary.md and acceptance facts before any code change
Stage 2 — contract impact note: Fixing whether the mart is a new data product or an extension of an existing one, before implementation
Stage 3 — implementation and verification: Writing the dbt model, updating models/schema.yml, running dbt build --profiles-dir .
Stage 4 — reviewer report: The paired reviewer runs data-contract-review and produces a report distinguishing facts, assumptions, and confirmations
Stage 5 — retrospective: Filling out the retrospective template with an assessment of what the specification described well and what had to be clarified after SQL
Practice exercises: Name: Block 1. Written answers to 12 quick SDD Data questions
Problem: Answer in writing, without AI assistant help, 12 questions: (1) What is the source of truth for grain? (2) How does ODPS differ from ODCS? (3) Why do dbt tests not replace the data contract? (4) Where should the list of prohibited PII be described? (5) What is contract drift? (6) When can adding a nullable column be a breaking change? (7) Why should author and reviewer be different roles? (8) What does dbt build prove, and what does it not prove? (9) How does Schema Manifest differ from a model specification? (10) Why shouldn't raw data be "fixed" for a green dbt build? (11) What should an acceptance fact contain? (12) When is human confirmation required?
Solution: Reference answers: (1) Model specification or data product contract, not SQL. (2) ODPS describes the product and consumers; ODCS is the technical contract. (3) dbt tests check part of the form/data, but not all of the product meaning. (4) In the specification, AGENTS.md, reviewer checklist, and, whenever possible, in a singular test. (5) A discrepancy between contract/specification and the actual model. (6) If it looks like a new business metric, changes the grain, or the API downstream. (7) The reviewer must check the author's assumptions, not defend them. (8) dbt build proves executability of DAG/tests, but not completeness of specification. (9) The manifest describes the observed source; the model specification describes the promised mart. (10) Raw preserves the input; the semantics of empty values and nulls is resolved in staging/specification. (11) Command, SQL or reviewer's manual step, expectation, and status. (12) When changing grain, PII policy, SLA, contract fields, or risk methodology.
Complexity: intermediate
Name: Block 2. Find 10+ problems in a weak specification
Problem: Given is an intentionally weak specification. Analyze it and find all weaknesses. Specification text: "# Customer Risk Summary. Make a useful customer risk mart. ## Data. Use customer_360 and payments. Add the necessary risk fields. ## Verification. Verify that the data is correct and contains no personal data."
Solution: Step-by-step breakdown: 1) Mart name not specified — impossible to reference the artifact in the contract. 2) Consumer not specified — impossible to verify usefulness. 3) Granularity (grain) not specified — impossible to prove row uniqueness. 4) "Useful" is an unverifiable word, not an acceptance criterion. 5) "Necessary fields" undefined — the consumer won't understand what they get. 6) No mandatory indicators — nothing to test. 7) PII policy not turned into a test by prohibited fields list — this is a slogan, not a check. 8) Contract impact not described — impossible to understand whether this is an extension or a new product. 9) No list of input models as sources — nothing to validate in the DAG. 10) No dbt commands — no reproducible check. 11) No manual facts for the reviewer — no evidence base. 12) Not stated whether this is a new product or an extension — impossible to assess change risk.
Complexity: intermediate
Name: Block 3. Rewrite the specification in SDD Data format
Problem: Rewrite the weak specification from Block 2 into a full SDD Data format with sections: Purpose, Consumer, Grain, Inputs, Output Fields, PII Policy, Contract Impact, Acceptance Facts, Open Confirmations. Constraints: grain — one row per customer_id; inputs — mart_customer_360 and mart_payment_risk_signals; fields — customer_id, risk_event_count, large_amount_event_count, last_risk_event_date; no direct PII; checks — customer_id not_null/unique, risk counters not_null; do not change the existing mart_customer_360 contract.
Solution: Sample rewritten specification:
# Model Specification: mart_customer_risk_summary
## Purpose
A mart of aggregated risk signals per customer for scoring and anti-fraud teams. Not used for marketing.
## Consumer
Risk modeling team, downstream BI dashboard; secondary consumer — anti-fraud alerts.
## Grain
One row per customer_id. Changing grain is prohibited without confirmation from the product owner.
## Inputs
- mart_customer_360 — customer reference, contract fixed, not changed.
- mart_payment_risk_signals — payment risk indicator events.
## Output Fields
- customer_id (PK, BIGINT, NOT NULL, UNIQUE)
- risk_event_count (INT, NOT NULL) — total number of risk events over 90 days.
- large_amount_event_count (INT, NOT NULL) — number of events with amount above threshold.
- last_risk_event_date (DATE, NULLABLE) — date of last event; NULL is allowed if there are no events.
## PII Policy
Direct PII is prohibited: full name, email, phone, passport, address. Only customer_id is used as a quasi-identifier. The prohibited fields list is checked by the no_pii_columns singular test against the final model schema.
## Contract Impact
New data product, not an extension of an existing one. The mart_customer_360 contract is not changed. Downstream dependencies: risk BI dashboard starts reading mart_customer_risk_summary instead of ad-hoc SQL.
## Acceptance Facts
1. dbt run on mart_customer_risk_summary model — success.
2. dbt test on not_null + unique customer_id — success.
3. dbt test on not_null risk counters — success.
4. no_pii_columns singular test — success.
5. Reviewer manual fact: visual check of 5 random customer_ids via BI — match.
## Open Confirmations
- large_amount threshold: requires confirmation from the risk modeling owner.
- 90-day window: requires confirmation from the BI dashboard consumer.Complexity: intermediate
Name: Block 4. Final project — implementation of mart_customer_risk_summary
Problem: Add a new mart_customer_risk_summary mart to the dbt project. Requirements: grain "one row per customer_id"; inputs — mart_customer_360 and mart_payment_risk_signals; fields — customer_id, risk_event_count, large_amount_event_count, last_risk_event_date; no direct PII; dbt tests; model specification and acceptance facts; contract impact note; reviewer report. Follow the 10-step process: from working tree to retrospective.
Solution: Step-by-step solution by process:
- Create a clean git branch: git checkout -b feat/mart-customer-risk-summary.
- Create the file specs/models/mart_customer_risk_summary.md with the specification from Block 3 — this happens before any SQL.
- Before SQL, describe the acceptance facts: a list of dbt commands, manual steps, and expectations. For example: "dbt test -m mart_customer_risk_summary expects 4 green tests".
- Describe the contract impact: "New data product, no mart_customer_360 extension, downstream BI dashboard switches to the new mart".
- Implement the model models/marts/mart_customer_risk_summary.sql. Example SQL:
{{ config(materialized='table') }}
with payments as (
select customer_id, event_date, amount, is_risk_event
from {{ ref('mart_payment_risk_signals') }}
),
agg as (
select
customer_id,
count(*) filter (where is_risk_event) as risk_event_count,
count(*) filter (where is_risk_event and amount > 100000) as large_amount_event_count,
max(event_date) filter (where is_risk_event) as last_risk_event_date
from payments
group by 1
)
select c.customer_id, a.risk_event_count, a.large_amount_event_count, a.last_risk_event_date
from {{ ref('mart_customer_360') }} c
left join agg a using (customer_id)- Update models/schema.yml: add the model, description, not_null and unique tests on customer_id, not_null on risk counters.
- Run dbt build --profiles-dir . — all tests should be green.
- Prepare the reviewer report (pair assessment partner): separate comments into facts (what was checked), assumptions (what was taken on faith), and open confirmations (what requires the product owner).
- Record in specs: whether the mart changes existing contracts (in this case — it does not, mart_customer_360 remains as it was).
- If the reviewer found drift (for example, last_risk_event_date became NOT NULL instead of NULLABLE) — fix the specification or SQL before merging.
Complexity: intermediate
Case studies: Name: Case 1: Customer risk mart — new data product from scratch
Scenario: The risk modeling team at a fintech company asks the data engineer to build a mart of aggregated risk signals per customer. Previously, the BI dashboard pulled data via ad-hoc SQL directly on top of mart_customer_360 and raw payments. The mart should replace ad-hoc queries and become the source for anti-fraud alerts.
Challenge: The initial specification from the analyst sounded like "make a useful customer risk mart". The following were not specified: mart name, consumer, grain, mandatory fields, PII policy, impact on the existing mart_customer_360 contract, acceptance criteria. Risk: the data engineer makes a "convenient" model with a join, which technically assembles, but does not answer the risk team's question and breaks the BI dashboard.
Solution: The engineer stopped work before SQL. Together with the product owner and analyst they fixed: grain = one row per customer_id; three mandatory fields (risk_event_count, large_amount_event_count, last_risk_event_date); prohibited PII list (full name, email, phone, passport, address) turned into the no_pii_columns singular test; contract impact — new data product, mart_customer_360 is not changed; acceptance facts include both dbt tests and a manual check of 5 random customer_ids in BI. After the specification was approved, the model was written, dbt build was run, the reviewer prepared a report separating facts and assumptions.
Result: The mart was deployed to production in one sprint. The BI dashboard switched to the new mart, ad-hoc SQL scripts were removed. Anti-fraud alerts became more stable as the variability of ad-hoc queries disappeared. Assessment per rubric: 23/25 points — loss of 2 points on the "acceptance facts" section (in the first iteration the reviewer's manual step was not described).
Lessons learned: A weak specification is a normal starting point; the engineer's task is to stop it before SQL and turn it into a full SDD Data artifact
The prohibited PII list should be turned into an executable singular test, not remain a slogan in chat
Manual acceptance facts (visual check in BI) are mandatory — dbt tests do not cover product meaning
The separation of "new product" vs "extension of existing" is a contractual decision, not a technical one
Related concepts: Grain as source of truth
ODPS vs ODCS
PII policy and singular test
Acceptance facts (command + expectation + status + confirmer)
Contract impact: new product vs extension
Name: Case 2: Contract drift with a "small improvement"
Scenario: In the existing mart_customer_360 mart, the author added a nullable date_of_birth column as part of a task to "enrich the customer profile for churn analytics". The author did not stop and did not coordinate the change with the product owner, because the column is nullable and "nothing breaks".
Challenge: A week later, the anti-fraud team built a dashboard that filtered customers by date_of_birth. This violated the PII policy (the company prohibits processing date of birth outside the KYC perimeter). Drift was not caught because: (a) the specification had no prohibited PII list with a check; (b) the reviewer checked only SQL, not the contract; (c) dbt tests passed — formally everything is green.
Solution: After the fact: the column was removed, the no_pii_columns singular test was added to all mart marts, AGENTS.md was updated with the prohibited fields list, the review process was revised so that the reviewer checks against the specification and PII policy before merge. In the next iteration, an analogous "small improvement" was caught by the reviewer at the specification stage.
Result: The incident led to a formal ban on adding fields to existing marts without confirmation from the product owner. The review process was expanded with mandatory reconciliation with the PII policy and ODCS contract. After the improvements, similar drift attempts were caught before release in 100% of cases over the next 3 months.
Lessons learned: A nullable column can be a breaking change if downstream starts using it as a business metric or violates the PII policy
Green dbt build ≠ contract intact
The prohibited PII list must be an executable artifact, not a document in Confluence
The reviewer checks the author's assumptions, not SQL — this is an active role
Related concepts: Contract drift
Breaking changes
What dbt build proves
Separation of author and reviewer roles
PII policy as an executable test
Name: Case 3: Pair assessment — experience separating author and reviewer roles
Scenario: Two junior engineers take the pair assessment on SDD Data. Student A is the author of the mart_marketing_attribution mart; Student B is the reviewer. Both have just completed the course and are applying the methodology in practice for the first time.
Challenge: The author tends to "tweak" SQL without going back to the specification. The reviewer tends to approve edits "on the small side" so as not to slow down the process. Both risk reducing the assessment to a formal dbt build run without actually checking the contract.
Solution: Before the start of the assessment, the pair fixed the rules: the reviewer does not edit the author's files; all comments are written in a report with sections "Contract", "SQL", "Tests", "Process"; after the first mart, the author swaps roles with the reviewer for the second. The reviewer uses the data-contract-review checklist: reconciles the actual schema with the specification, runs the PII singular test, verifies that acceptance facts are fulfilled. After the first iteration, the author saw three drifts in the reviewer's report: (1) a region column was added without approval; (2) the attribution_window calculation method was not described; (3) tests cover only not_null, no accepted_values for attribution channels.
Result: The first mart received 18/25 — not enough for "can transfer to prod", but provided material for the retrospective. After the role swap, the second mart received 23/25 — both students noted that the reviewer role gave much more than the author role, because it forces you to formalize your acceptance criteria.
Lessons learned: The pair format removes the illusion that review is passive SQL reading
Swapping author↔reviewer roles helps understand which acceptance criteria actually work
The reviewer's report must separate facts, assumptions, and open confirmations — otherwise it's just comments
The retrospective after the assessment is not a checkbox, but an input for the next phase: if "what had to be clarified after SQL" is more than 3 items, the phase must be narrowed
Related concepts: Separation of author and reviewer roles
Reviewer report (facts, assumptions, confirmations)
SDD Data retrospective
25-point rubric
Pair assessment format
Study tips: Don't try to "skip ahead" to SQL — write the specification and acceptance facts strictly before code. This is the most common cause of low rubric scores.
Make a checklist of the 12 quick questions from Block 1 on a card and run through it before each feature — this takes 5 minutes and saves hours of rework.
Set up a specification template in specs/models/ and don't edit it "on the small side" without going back to the product owner. Any change to the specification = a change to the contract.
Turn the PII policy into the no_pii_columns singular test right away, in the same iteration. The slogan "we have no PII" without a check = drift that will happen next week.
Agree with a colleague/partner on the pair format. If there is no partner, ask AI to play the reviewer role via the /clear + data-contract-review scenario, but still show the final report to a human.
Keep a journal of "open confirmations" in the specification — this reduces the chance that Qwen Code "guesses" the methodology and makes an irreversible assumption.
Run dbt build with --profiles-dir . and save the output as an artifact — this is your first acceptance fact. Keep the screenshot/log together with the specification.
Don't skip the retrospective after the assessment. If in the section "what had to be clarified after SQL" there are more than 3 items, reduce the next feature or describe it in more detail.
Use the recommended Qwen scenarios (creating specification → implementation → review) as a literal script: /clear + context + explicit prohibition to write code in the first step.
Remember: 21+ points on the rubric means "can transfer to a real data product". If you scored 16–20, that's not a failure, but verification and review need to be strengthened before transferring to prod.
Additional resources: Open data product specification (odps): A specification for describing a data product and its consumers — bitol.io/odps or the current version on the bitol.io site. Used as a template for the "Purpose" and "Consumer" sections.
Open data contract standard (odcs): Technical contract for a data product — bitol.io/odcs. Used as a template for the "Output Fields" and "Contract Impact" sections.
Dbt documentation — tests: Documentation on dbt tests (not_null, unique, accepted_values, relationships, singular). Source for the "dbt checks" section in the specification.
Dbt documentation — contracts: Documentation on dbt contracts — enforce_schema, enforce_not_null. Useful for the "Contract Impact" section in the model specification.
Agents.md in the project root: A file that records the PII policy, prohibited fields, stack, and constraints. Must be read by the AI assistant before any code suggestions.
Qwen code — data-contract-review scenario: Internal review scenario in Qwen Code. Runs after implementation and checks changes against the specification, PII policy, tests, and acceptance facts without editing the author's files.
SDD data retrospective template: Markdown template with sections "What the specification described well / What had to be clarified after SQL / Which checks caught the error / Where Qwen Code tried to guess the meaning / What to strengthen before transferring to the production environment". Must be filled out after each assessment and major feature.
Summary: Part 22 "Practical Assessment" is the final check not of knowledge of dbt syntax but of the ability to keep a data product's promise from specification to reviewer report. Key idea: the assessment starts with an intentionally weak specification ("make a useful mart"), and a good engineer should stop it before SQL. The assessment consists of four blocks: 12 written questions on SDD Data terminology (grain, ODPS vs ODCS, contract drift, PII, dbt build, acceptance facts, human confirmation); a task to find 10+ problems in a weak specification; rewriting it in full SDD Data format; implementing the mart_customer_risk_summary mart with a full evidence package — specification before SQL, dbt tests, contract impact, reviewer report, retrospective. The pair format shows that review is an active role. Grading on a 25-point rubric: 21+ points — the process can be transferred to a production data product; 16–20 — the result is useful for learning, but verification and review need to be strengthened; below 16 — the phase is too large or specifications don't hold the meaning. The main practical conclusion: if in the retrospective "what had to be clarified after SQL" is more than three items, the next feature should be reduced or described in more detail before implementation.