Part 22. Capstone Exam

The final task is to carry a change through the full SDD Data cycle. This is not a test of dbt-syntax knowledge. It is a check of whether you can keep the data product promise from specification to reviewer report.

Reader walkthrough

The capstone exam is not about testing memory. Its purpose is to force the student to go through the whole cycle without hints from the textbook author. If a person can write SQL but cannot explain grain, PII policy, contract impact and verification facts, they have not yet mastered SDD Data. In real work, these are exactly the gaps that turn tidy models into risky marts.

The exam deliberately starts with a bad specification. This is more important than jumping straight to an implementation task. A good data engineer must be able to stop a weak assignment before SQL. If a specification says "build a useful mart," you cannot start modeling. You need to ask about the consumer, grain, inputs, required fields, PII, impact on existing contracts, and acceptance criteria.

The paired format reveals another idea of the volume: review is an active engineering role. The reviewer is not just looking for typos in SQL. They verify that the author has not

replaced the promise with a convenient implementation, has not weakened the check after a failure, and has not hidden breaking changes inside a "small improvement." That is why a good capstone should include not only the model and tests, but also a reviewer report.

The retrospective at the end is about honesty. If after SQL you had to clarify many points, that is not a student failure but a signal that the next phase needs to be narrowed down or specified in more detail. SDD Data does not promise that all questions will be closed up front. It requires that questions do not disappear without a trace and that every clarification flows back into the project files.

Block 1. Quick questions

Answer in writing, without Qwen Code.

1. What is the source of truth for grain?
2. How does ODPS differ from ODCS?
3. Why do dbt tests not replace a data contract?
4. Where should the list of forbidden PII be described?
5. What is contract drift?
6. When can adding a nullable column be a breaking change?
7. Why must author and reviewer be different roles?
8. What does dbt build prove, and what does it not prove?
9. How does a Schema Manifest differ from a model specification?
10. Why shouldn't raw data be "fixed" for the sake of a green dbt build?
11. What should a verification fact contain?
12. When is human confirmation required?

Block 2. Find the problems in the data specification

Given a specification:

# Customer Risk Summary

Build a useful customer risk mart.

## Data

Use customer_360 and payments. Add the necessary risk fields.

## Verification

Verify that the data is correct and there is no personal data.

Find at least 10 problems. A good answer will notice:

• mart name is not specified;
• consumer is not specified;
• granularity (grain) is not specified;
• "useful" is not verifiable;
• "necessary fields" are undefined;
• no required metrics;
• PII policy is not converted into a test against a list of forbidden fields;
• impact on the contract is not described;
• no list of input models;
• no dbt commands;
• no manual facts for the reviewer;
• it is not stated whether this is a new product or an extension.

Block 3. Rewrite the specification

Rewrite the specification in SDD Data format:

# Model specification: mart_customer_risk_summary

## 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, 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 modify the existing mart_customer_360 contract.

Block 4. Final project

Add a new mart mart_customer_risk_summary.

It must:

• have grain "one row per customer_id";
• use mart_customer_360 and mart_payment_risk_signals;
• calculate risk_event_count, large_amount_event_count, last_risk_event_date;
• not expose direct PII;
• have dbt tests;
• have a model specification and verification facts;
• have a note about contract impact and a reviewer report.

Process requirements

1. Start with a clean working tree.
2. Create the specification at specs/models/mart_customer_risk_summary.md.
3. Write verification facts before SQL.
4. Describe the contract impact: new data product or extension.
5. Implement the dbt model.
6. Update models/schema.yml.
7. Run dbt build --profiles-dir . if dbt is installed.
8. Prepare a reviewer report.
9. Record whether the mart changes existing contracts.
10. If the reviewer finds drift, fix the specification or SQL before release.

Recommended Qwen scenario

Creating the specification:

/clear
Read AGENTS.md, specs, and current marts.
Help create the specification for mart_customer_risk_summary.
Don't write code yet. First ask questions about grain, PII policy, required fields, and
verification facts.

Implementation:

/clear
Implement only mart_customer_risk_summary according to the approved specification.
After the change, list the files and checks.
Do not change existing contracts without confirmation.

Review:

/clear
Use data-contract-review.
Check the change against the specification, PII policy, tests, verification facts, and
contract impact. Do not modify files.

Rubric for 25 points

21+ points — the process can be transferred to a real data product. 16–20 — the result is useful for learning, but checks and review need strengthening. Below 16 — the phase is too large or the specifications do not hold the meaning.

Answers to the quick questions

1. The model specification or the 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 the full product meaning.
4. In the specification, AGENTS.md, the reviewer checklist, and, where possible, a singular test.
5. A divergence between the contract/specification and the actual model.
6. If it looks like a new business metric, changes grain, or changes an API downstream.
7. The reviewer must check the author's assumptions rather than defend them.
8. dbt build proves the executability of the DAG/tests, but not the completeness of the specification.
9. The manifest describes an observable source; the model specification describes a promised mart.
10. Raw preserves the input; the semantics of empty values and null are resolved in staging or in the specification.
11. The command, SQL, or manual reviewer step, the expectation, and the status.
12. When grain, PII policy, SLA, contract fields, or risk methodology change.

Paired capstone format

One student is the author, the other is the reviewer.

Author:

• writes the specification;
• writes verification facts before SQL;
• implements the dbt model;
• runs checks;
• prepares an evidence package.

Reviewer:

• reads the specification before implementation;
• checks the change against the specification;
• runs or re-checks dbt commands;
• separates comments on the contract, SQL, tests, and process;
• does not edit the author's files.

After the first mart, roles switch. This dispels the illusion that review is passive SQL reading.

After the capstone

Write a short retrospective:

# SDD Data Retrospective

## What the specification described well
## What had to be clarified after SQL
## Which checks caught an error
## Where Qwen Code tried to guess the meaning
## What to strengthen before moving to a production environment

If the "what had to be clarified after SQL" section has more than three items, the next feature should be reduced or described in more detail before implementation.