Topic: Part 16. Customer 360 Mart
Difficulty level: Medium
Estimated study time: 4-6 hours
Prerequisites: Basic understanding of SQL and data models
Familiarity with dbt (data build tool) and the concept of ref()
Understanding of the principles of building data marts
Knowledge of the SDD (Specification-Driven Development) concept
Basic understanding of PII (Personally Identifiable Information) and principles of working with personal data
Experience working with YAML files and markdown documentation
Learning objectives: Explain the purpose and architecture of the mart_customer_360 mart as the first final mart in the project
Map SQL implementation, schema, specification, and acceptance facts to identify coverage gaps
Apply the acceptance matrix to verify the fulfillment of each acceptance fact with a specific type of evidence
Identify and prevent leakage of PII fields from staging/raw layers to the final mart
Construct dbt tests (unique, not_null, singular) to prove acceptance facts
Overview: Part 16 of the course is dedicated to the mart_customer_360 mart — the first final mart in the project, which combines customer attributes, account balances, and card activity. This section is a central learning example demonstrating how product specification, ODCS (Open Data Contract Standard), model specification, dbt tests, and verification facts should work together in a coordinated way. Special attention is paid to product discipline: the mart is intentionally limited — one row per customer, no direct PII, the balance is calculated from accounts, and risk events are taken from a synthetic activity window. This limitation makes the example "honest": every field can be linked to a specification and a check. The student learns to understand why a field name (for example, risk_event_count_7d) becomes part of the contract, and why the PII policy cannot be left as a general phrase. The Customer 360 mart is the starting point for understanding the principles of SDD (Specification-Driven Development), where readiness evidence is built on the acceptance matrix rather than on a visual SQL review.
Key concepts: Mart customer 360: The first final mart in the project, which combines customer attributes, account balances, and card activity. It is the main learning example of the integration of specification, implementation, and testing. Limited to one row per customer_id.
Acceptance facts: Concrete verifiable statements about the mart that must be covered by evidence. They include: one row per customer_id, customer_id not null and unique, no direct PII, total_balance_rub not null, risk_event_count_7d not null, mandatory contract fields are present, lineage is read through ref().
Acceptance matrix: A table mapping each acceptance fact to a specific piece of evidence (dbt test unique, not_null, singular schema test, singular contract columns test). If the evidence row is empty, it is not readiness, but a gap.
Odcs (open data contract standard): A data contract standard used to formally describe the product specification. In the context of the mart, it is the file specs/customer_360_contract.odcs.yaml, which describes commitments regarding data structure and quality.
Sdd (specification-driven development): A development approach in which the specification is written before the SQL code. SDD forces the choice of a single version of the product promise and records it in advance, which reduces the risk of contract drift.
Dbt tests: Automated checks in dbt (data build tool) that serve as evidence for acceptance facts. They include generic tests (unique, not_null) and singular tests (arbitrary SQL for checking the schema, contract columns, and absence of PII).
Lineage through ref(): The ability to read data lineage through ref() functions in dbt. One of the acceptance facts: lineage must be transparent and recoverable.
Pii (personally identifiable information): Personal data that allows identification of a subject. The PII policy in SDD cannot be a general phrase: pii_email is allowed in raw and staging for educational purposes, but it must not be in the mart. This is a product boundary, not a matter of schema aesthetics.
Risk event count 7d: A field for the count of risk events over a seven-day window. The name itself promises a seven-day window, so if SQL simply counts all risk events, the field looks plausible but deceives the consumer. The window must be described and verified with a separate test. The field name is part of the contract.
Contract drift: The discrepancy between the declared specification and the actual implementation. It must have dedicated checks, not be detected only by visual SQL review.
Acceptance evidence: A concrete artifact (dbt test, schema review, explicit verification fact, reviewer report) confirming the fulfillment of an acceptance fact. Without evidence, readiness is not counted.
Reviewer note: The minimal output of a review artifact, containing: granularity, PII status, contract fields, list of checks, and open questions. Example: "Granularity: one row per customer_id. PII: no direct PII. Contract fields: customer_id, total_balance_rub, risk_event_count_7d. Checks: dbt build, mart tests, list of forbidden PII, contract columns. Open questions: the definition of risk_event_count_7d depends on the input risk flag".
Qwen review query: A typical prompt for an LLM assistant (Qwen) to compare SQL, schema, ODCS, and validation files to identify covered, manual, and missing acceptance facts. The files themselves are not modified.
Practice exercises: Name: Acceptance matrix audit
Problem: You have been given the mart mart_customer_360. An intern student claims that everything is ready because dbt build ran successfully. Conduct an audit: go through each acceptance fact and map it to evidence from the acceptance matrix. Identify which facts are covered automatically, which require manual review, and which have no evidence at all. Files: models/marts/mart_customer_360.sql, models/schema.yml, specs/customer_360_contract.odcs.yaml, specs/validation/customer_360.md.
Solution: Step 1. List all acceptance facts from the specification: (1) one row per customer_id, (2) customer_id not null, (3) customer_id unique, (4) no direct PII, (5) total_balance_rub not null, (6) risk_event_count_7d not null, (7) mandatory contract fields are present, (8) lineage is read through ref(). Step 2. Open models/schema.yml. Find the unique and not_null tests for customer_id, total_balance_rub, risk_event_count_7d. Record: facts (2), (3), (5), (6) are covered by generic tests. Step 3. Check for the presence of a singular test listing forbidden PII fields (pii_email, pii_phone, etc.) and comparing them with the list of mart columns. If the test exists — fact (4) is covered automatically; if not — mark it as requiring manual review. Step 4. Find a singular test for contract columns (a check that the mart contains exactly customer_id, total_balance_rub, risk_event_count_7d and other mandatory fields from ODCS). Step 5. Verify that only ref() is used in SQL for upstream models — otherwise fact (8) is not covered. Step 6. Build a coverage table. If at least one row is empty, it is a readiness gap, not readiness.
Complexity: intermediate
Name: Writing a singular test for the absence of PII
Problem: The staging layer contains the pii_email field (for educational purposes and to demonstrate the risk). Write a singular test for dbt that verifies that this field is absent from the final mart mart_customer_360, and that any other columns containing the substrings 'email', 'phone', 'passport', 'inn' are also absent.
Solution: Create the file tests/mart_customer_360_no_pii.sql with the following contents:
{{ config(severity='error') }}
with mart_columns as (
select column_name
from information_schema.columns
where table_schema = 'marts'
and table_name = 'mart_customer_360'
),
forbidden_substrings as (
select unnest(array['email', 'phone', 'passport', 'inn', 'snils']) as forbidden
)
select m.column_name, f.forbidden
from mart_columns m
cross join forbidden_substrings f
where lower(m.column_name) like '%' || f.forbidden || '%'Place the file in the tests/ folder (or in models/marts/ with the config {{ config(materialized='test') }}). The test should return rows — if the result is empty, there are no PII fields. Add a test description in schema.yml: description: "Verifies the absence of direct PII fields in mart_customer_360". This is a singular schema test that serves as evidence for the acceptance fact "no direct PII".
Complexity: intermediate
Name: Protecting the risk_event_count_7d window
Problem: The risk_event_count_7d field promises a seven-day window of risk events. The current implementation in mart_customer_360.sql counts all events from stg_risk_events without filtering by date. Write a verification test that (a) fixes the window in SQL, (b) adds a dbt test proving that the counter is indeed limited to the last 7 days relative to the load date.
Solution: Step 1. Fix the SQL model mart_customer_360.sql so that the count is taken only for the window: add the condition event_date >= current_date - interval '7 days' or use the dbt_date macro. Example fragment: count(case when r.event_date >= current_date - interval '7 days' then 1 end) as risk_event_count_7d. Step 2. Create a singular test tests/mart_customer_360_risk_window.sql that compares the count in the mart with the reference count for the window from stg_risk_events:
{{ config(severity='warn') }}
with mart as (
select customer_id, risk_event_count_7d
from {{ ref('mart_customer_360') }}
),
stg_window as (
select customer_id,
count(*) as expected_7d
from {{ ref('stg_risk_events') }}
where event_date >= current_date - interval '7 days'
group by 1
)
select m.customer_id
from mart m
join stg_window s on m.customer_id = s.customer_id
where m.risk_event_count_7d <> s.expected_7dStep 3. Record in the specification specs/validation/customer_360.md the definition of the window: "risk_event_count_7d — the number of risk events of the client over the last 7 calendar days relative to the mart calculation date". Step 4. In schema.yml, add a description for the risk_event_count_7d field indicating the window. Now the field name, SQL, test, and specification are aligned.
Complexity: intermediate
Name: Verifying lineage through ref()
Problem: A reviewer suspects that in mart_customer_360.sql one of the upstream models is connected not through ref(), but through a direct source reference (source()). Find this violation, explain the risk, and propose a fix.
Solution: Step 1. Open models/marts/mart_customer_360.sql. Check all calls in with-blocks and from-clauses: only {{ ref('stg_customers') }}, {{ ref('stg_accounts') }}, {{ ref('stg_cards') }}, {{ ref('stg_risk_events') }} should be present. Step 2. Find calls to {{ source('...', '...') }} — this means accessing raw directly, bypassing staging. Step 3. Explain the risk: source() bypasses the transformation layer, breaks data lineage, makes quality checks before the mart impossible, and creates contract drift. Step 4. Replace source() with ref() to the corresponding staging model. Step 5. Run dbt compile and make sure the lineage is built correctly (dbt docs generate → open the visualization). Step 6. Add a singular test that scans SQL for the presence of source() (optional, advanced level).
Complexity: intermediate
Name: Writing a reviewer note
Problem: Prepare the minimal output of a reviewer of the mart mart_customer_360 following the template from the course materials. Use the following inputs: the mart passed dbt build, schema.yml contains unique and not_null tests for customer_id, total_balance_rub, risk_event_count_7d. There is no singular test for PII, but visually pii_email is not selected in the code. A singular test for contract columns is present, but it checks only 3 out of 5 mandatory ODCS fields. SQL uses ref() correctly. The definition of risk_event_count_7d in specs/validation/customer_360.md mentions a 7-day window, but does not specify the anchor date.
Solution: Ready reviewer note:
Granularity: one row per customer_id (covered by dbt unique test).
PII: visually absent, but there is no automatic evidence — status "manual review, singular schema test required".
Contract fields: customer_id, total_balance_rub, risk_event_count_7d (covered partially — 3 out of 5 fields per ODCS, gap in 2 fields).
Checks: dbt build — ok, mart tests unique/not_null — ok, singular test for contract columns — incomplete, list of forbidden PII — missing.
Open questions:
1. The definition of risk_event_count_7d does not fix the anchor date (current_date vs event_date vs mart calculation date) — risk of drift.
2. Which other 2 fields from ODCS should be present in the mart? Reconciliation with customer_360_contract.odcs.yaml is required.
3. The PII check exists only as a visual review — a singular test must be added.Conclusion: the mart is not ready for production. The 3 open questions must be closed before merge.
Complexity: intermediate
Case studies: Name: pii_email leak into a bank's marketing mart
Scenario: A large retail bank is building a Customer 360 mart for the CRM department. The marketing team requests "everything available on the customer" in order to do segmentation and personalization. A dbt model developer in the marts layer assembles a table of 47 columns, including pii_email and pii_phone, citing that these fields "are already in stg_customers and are needed for mailings". The mart passes a code-only review: the reviewer looks at the SQL, sees a clean join, but does not reconcile the list of columns with the PII policy and the ODCS contract.
Challenge: After 4 months, the security service conducts an audit and discovers that the marketing mart contains direct PII, which according to internal regulations must be masked or hashed at the mart level. The mart was already used in 12 dashboards and 3 churn-prediction ML models. No data leak occurred (access was restricted), but the regulatory risk materialized: a violation of the personal data processing policy was identified, and the bank faces a directive from the Central Bank.
Solution: The team adopts the SDD approach: (1) rewrites the ODCS contract customer_360_contract.odcs.yaml, explicitly listing allowed and forbidden fields; (2) removes pii_email and pii_phone from the mart, replacing them with hash_email for safe segmentation; (3) adds a singular test mart_customer_360_no_pii.sql, which scans information_schema and fails if forbidden substrings appear in column names; (4) introduces a mandatory acceptance matrix step in the PR template — the reviewer is required to fill in the fact coverage table; (5) connects a Qwen assistant that, in every PR, compares SQL, schema.yml, and ODCS and highlights discrepancies.
Result: Within 6 months of SDD adoption, the number of PII incidents in the mart layer dropped to zero. Average mart review time increased by 15 minutes, but the number of rollbacks and rework decreased. The regulatory risk is closed: the bank passed a re-audit without remarks. The team scaled the practice to 14 other marts.
Lessons learned: Code-only review is insufficient — automatic singular tests are needed to prove compliance with the PII policy.
The ODCS contract must be the single source of truth about the composition of mart fields, not the visual impression of the reviewer.
Names like "Customer 360" are deceptively obvious — different stakeholders have different expectations; SDD fixes a single version of the promise.
The acceptance matrix turns the abstract "done" into a discrete list of facts with evidence.
Related concepts: mart_customer_360
PII (Personally Identifiable Information)
ODCS (Open Data Contract Standard)
Acceptance matrix
Singular schema test
SDD (Specification-Driven Development)
Name: Contract drift of risk_event_count_30d at an insurance company
Scenario: An insurance company uses the Customer 360 mart for fraud scoring. The risk_event_count_30d field promises a 30-day window. A data analyst makes a change to stg_risk_events: adds a filter where event_status = 'confirmed' to remove "raw" alerts. Technically the field remains "risk events over 30 days", but its meaning shifts from "all alerts" to "confirmed alerts". The SQL in mart_customer_360 does not change, dbt build passes, no one notices the change.
Challenge: After 2 months, the scoring model starts producing false-positive policy blocks: customers who had only unconfirmed alerts are now not blocked, but should be. Fraud losses grow by 8% per quarter. The investigation reveals that the mart continues to call the field risk_event_count_30d, but its semantics have changed. The contract and the implementation diverged unnoticed.
Solution: The team implements multi-level protection: (1) in specs/validation/customer_360.md a formal definition is added: "risk_event_count_30d — the number of records in stg_risk_events with status event_status in ('confirmed', 'investigating') over the last 30 days relative to the mart calculation date"; (2) in mart_customer_360.sql the filter event_status in ('confirmed', 'investigating') is hard-coded with a comment; (3) a singular test is created that compares the field value in the mart with a reference count using the fixed formula — any discrepancy breaks the build; (4) in schema.yml, the field gets a description with a link to the specification section.
Result: The scoring model returns to the expected semantics, and fraud losses return to the target level. The introduction of a formal contract test reduces such incidents from 2-3 per year to zero. The team formalizes a rule: "if a field name contains a window, status, currency, or aggregation level — it is part of the contract and requires a separate test".
Lessons learned: A field name is part of the contract. Unspoken changes in the upstream layer silently drift the semantics of downstream marts.
Passing dbt build ≠ readiness. Tests are needed that check the meaning itself, not just the structure.
A filter in the staging layer changes the semantics of a mart field, even if the SQL in the mart has not changed. An end-to-end test is needed.
The window definition must contain an anchor date and conditions for including/excluding events.
Related concepts: risk_event_count_7d
Contract drift
Validation specification
Singular test
Field name as part of the contract
Name: Migration of 14 marts to SDD in one quarter
Scenario: A fintech startup has 14 marts in the marts layer, written over 2 years by different developers. The approach to contracts is inconsistent: some marts have ODCS, others have only a wiki page, and still others have no documents at all. Mart review is completely manual and takes 2-3 days per mart. Data quality is unpredictable: consumers complain about "drifting" metrics, and the Customer 360 mart is rebuilt with different results on different branches.
Challenge: The product director requires that within a quarter the team stabilize quality and reduce time-to-market for new marts. A team of 5 dbt developers must simultaneously migrate existing marts to SDD without stopping data delivery. High risk: during migration, it is possible to break things that have worked for years.
Solution: The team applies an incremental approach: (1) selects mart_customer_360 as a pilot — it is the "first final mart" with understandable business logic and a limited number of fields; (2) for the pilot, writes an ODCS contract, a model specification, a validation specification, and an acceptance matrix, as described in Part 16; (3) introduces 3 types of dbt tests: unique/not_null for mandatory fields, a singular test for the absence of PII, a singular test for compliance with contract columns; (4) automates the check through a Qwen assistant: a PR triggers a review that compares SQL, schema.yml, and ODCS and generates a fact coverage table; (5) after a successful pilot, templates the artifacts and scales to the remaining 13 marts at a rate of 2 marts per week.
Result: In 12 weeks, all 14 marts were migrated to SDD. Code review time decreased from 2-3 days to 4-6 hours thanks to automatic coverage verification. The number of data drift incidents decreased from 8-10 per quarter to 1 per half-year. Consumers note that the marts have become "predictable" — documentation and implementation no longer diverge. Bonus: new developers onboard in 1 week instead of 1 month thanks to standardized templates.
Lessons learned: Customer 360 is an ideal pilot for SDD: the name is familiar, so everyone thinks they understand it, but different people have different expectations. A written specification removes ambiguities.
The acceptance matrix turns the abstract "done" into a concrete checklist with evidence. An empty evidence cell = a readiness gap.
An LLM assistant (Qwen) speeds up routine fact coverage review, but does not replace human decisions on open questions.
Templating artifacts (ODCS, spec, validation) speeds up scaling and reduces variability between marts.
Related concepts: SDD (Specification-Driven Development)
ODCS (Open Data Contract Standard)
Acceptance matrix
Qwen review query
Reviewer note
Contract drift
Study tips: Start with the acceptance matrix, not with SQL. Go through each fact and find evidence before reading the code — this will flip the perception from "what the model does" to "what the model promises".
Build a "fact → evidence → test type" table for each mart. An empty cell = a readiness gap. This is your main working artifact.
For each field with a "promise" in its name (7d, 30d, confirmed, rub, count), ask: "where in the code is this promise fixed?" If only in the name — it is technical debt.
Use the Qwen query from the course as a template for your own PRs: compare SQL, schema.yml, ODCS, and the validation file, and output a coverage table. Do not modify the files — only analyze.
Do not limit yourself to reading SQL. Open information_schema and verify that there are no PII columns physically in the mart, even if they are not visible in SELECT.
Write singular tests right away, not "someday later". A test for the absence of PII is 10 lines that save weeks of investigation.
For each PR, fill in a reviewer note following the template from the course. This disciplines: facts, not impressions.
Run dbt build --select mart_customer_360 and analyze the output line by line — which line corresponds to which fact in the matrix.
Do not confuse "visually no PII" with "proven absence of PII". The first is review, the second is a test. SDD requires evidence.
Use date anchoring in window fields: always fix relative to which date the window is counted (current_date, load date, event_date).
Additional resources: Dbt documentation on tests: https://docs.getdbt.com/docs/build/data-tests — official guide on generic tests (unique, not_null, accepted_values, relationships) and singular tests.
Dbt documentation on ref(): https://docs.getdbt.com/reference/dbt-jinja-functions/ref — description of the ref() function and data lineage building.
Odcs (open data contract standard): https://bitol-io.github.io/open-data-contract-standard/ — specification of the data contract standard used in SDD.
Dbt-utils package: https://github.com/dbt-labs/dbt-utils — a collection of macros, including tests for PII and contract columns (expression_is_true, relationships, etc.).
Sqlfluff linter for dbt: https://sqlfluff.com/ — static analysis of dbt model SQL code, helps maintain style and catch basic errors.
Dbt-expectations package: https://github.com/calogica/dbt-expectations — advanced tests in the style of Great Expectations for dbt.
The book "Data Contracts" (Andrew Jones): A conceptual introduction to data contracts as a product artifact. Useful for understanding why SDD works.
Great Expectations: https://greatexpectations.io/ — a data validation framework whose concepts echo dbt singular tests.
Summary: Part 16 "Customer 360 Mart" is the central learning example of applying SDD (Specification-Driven Development) to the final data layer. The mart mart_customer_360 is intentionally limited: one row per customer_id, no direct PII, balance from accounts, risk events from a synthetic window. Every field can be linked to a specification and a check. The key idea: the acceptance matrix turns the abstract "done" into a discrete list of facts with concrete evidence (dbt unique/not_null tests, singular schema and contract columns tests, lineage verification through ref()). If the evidence row is empty, it is a readiness gap, not readiness. Field names are part of the contract: if a name contains a window (7d), a status, or an aggregation level, this promise must be fixed in code and confirmed by a test. The PII policy cannot be a general phrase — it is verified by an automatic singular test that scans mart columns. The minimal artifact output is a reviewer note with granularity, PII status, list of contract fields, list of checks, and open questions. A Qwen assistant can speed up routine fact coverage verification, but does not replace human decisions. The main lesson: in SDD, writing correct SQL is not enough — you need to prove every promise with a test or an explicit fact.