Topic: Part 14. Silver layer: customers, accounts, cards, payments
Difficulty level: Medium
Estimated study time: 2.5–3 hours (theory — 1 hour, practice — 1 hour, cases and review — 1 hour)
Prerequisites: Understanding the Medallion architecture (Bronze/Silver/Gold) in Data Lakehouse
Basic dbt skills: models, ref(), sources, tests
SQL knowledge at the level of JOIN, GROUP BY, window functions
Understanding of the banking data domain: customers, accounts, cards, payments
Familiarity with the concepts of grain, PII, lineage
Learning objectives: Explain the role of the Silver layer as a reusable intermediate level between staging and mart
Identify and formulate the grain (granularity) of intermediate models int_customer_balances and int_customer_card_activity
Apply Silver layer rules: no PII leakage, clear grain, no product decisions, use of ref() for lineage
Conduct acceptance checks of Silver models against formalized quality criteria
Fill in the grain / inputs / usage / validation gap mapping table for intermediate models
Overview: The Silver layer in the dbt architecture of the banking domain is a layer of stable intermediate entities that sits between raw staging models and final mart data products. It removes the technical noise of sources (type casting, key normalization, simple aggregations) but does not make product decisions that affect the business promise. In the learning example, Silver is represented by two intermediate models: int_customer_balances (aggregation of accounts to the customer level) and int_customer_card_activity (aggregation of card transactions and risk signals). The main value of Silver is reusability: the same model can be read by the Customer 360 data product, the credit data product, and the risk data product without duplicating logic. However, this reusability requires discipline: a clear grain, documented windows for counters, a PII policy, and mandatory use of ref() to build lineage.
Key concepts: Silver layer (intermediate layer): A layer in the Medallion architecture that creates stable reusable entities from raw data. It connects staging (source cleaning and typing) and mart (business data products for consumers). It is not a final product, but it is no longer a raw source.
Medium model (int_): A type of dbt model located between staging and mart. Has the int_ prefix. Contains intermediate transformations and aggregations. In this part: int_customer_balances and int_customer_card_activity.
Grain (granularity): A key property of a table — one row per which entity (customer_id, account_id, transaction_id). It defines what is considered a record in the table. A model with an unclear grain becomes unsuitable for reuse.
Int customer balances: An intermediate model that aggregates data from stg_accounts to the customer level. Grain: one row per customer_id. Used in mart_customer_360. Contains aggregated balances of the customer across all their accounts.
Int customer card activity: An intermediate model that aggregates card transactions from stg_card_transactions and counts customer-level signals, including risk counters (for example, risk_event_count_7d). Grain: one row per customer_id.
PII (personally identifiable information): Personal data of the customer (full name, passport, phone, email). Silver must not pass direct PII further than needed without a handling policy.
Ref(): A dbt function used to reference other models in the project. Creates explicit lineage that the reviewer sees in the DAG. In Silver, all input models must be read only through ref().
Lineage (data lineage): A dependency graph between models that is automatically built by dbt based on ref(). It allows you to understand which models depend on a given one and which sources it uses.
Risk counter with a clear window: An aggregate (for example, risk_event_count_7d) where the time window (7 days) and the definition of the risk flag must be explicitly described in the model specification. Without this, the model is unverifiable.
Reusability of the Silver layer: A property whereby the same intermediate model can be used by multiple mart data products without duplicating logic. Reduces the risk of discrepancies between data products.
Validation gap: A situation in which a model lacks sufficient specification to validate data quality: the window is not described, a null value is not explained, the grain is not defined. An empty validation gap = the model is sufficiently described.
Product decision: A business rule that defines what is considered an "active customer," "overdue payment," "creditworthy." Silver must not make such decisions if it is reusable — that is the work of the mart layer.
Qwen review prompt: A specialized prompt for an LLM assistant that compares intermediate models with specifications and mart requirements, identifies grain, inputs, and validation gaps without modifying files.
Important dates: Stage 1 — Silver layer design: An architectural decision to isolate intermediate entities before creating mart data products. Not tied to a calendar date, but is a mandatory project milestone.
Stage 2 — Specification approval: The moment when each int_ model gets a documented grain, windows for counters, and a PII policy. Until that moment, the model is not considered ready for merge.
Stage 3 — Acceptance check: A formal run against the checklist: ref() in inputs, one row per customer_id for customer models, no unexplained nulls, clear windows for risk counters.
Practice exercises: Name: Building a mapping table for two intermediate models
Problem: Using the material from Part 14, fill in the grain / inputs / used in / validation gap table for the int_customer_balances and int_customer_card_activity models. For each model, indicate: (1) granularity — which entity corresponds to one row; (2) inputs — which staging models it reads via ref(); (3) where it is used — which mart models read it; (4) validation gap — what is not described in the specification (if everything is described, leave the cell empty).
Solution: Step 1. Determine the grain for int_customer_balances: the model aggregates accounts to the customer level, so one row = one customer_id. Step 2. Inputs per text: stg_accounts. Step 3. Used in: mart_customer_360. Step 4. Validation gap: in the material the grain and inputs are explicitly indicated, windows are not used (balance aggregation), PII is not mentioned as a problem area — the gap is empty. Step 5. Determine the grain for int_customer_card_activity: the model aggregates card transactions to the customer level, one row = one customer_id. Step 6. Inputs: stg_card_transactions. Step 7. Used in: mart_customer_360. Step 8. Validation gap: the model counts risk_event_count_7d, but the specification must explain what "7 days" means (window from which date, rolling or fixed) and where the risk_flag comes from (threshold, source). If this is not described — there is a gap. Final table: see the "Minimum Output" section in the material.
Complexity: intermediate
Name: Identifying product decisions in a Silver model
Problem: You were given the SQL code of int_customer_card_activity, which contains the line: WHERE balance > 0 AND last_transaction_date > CURRENT_DATE - INTERVAL '90 days'. A colleague claims that this is just an "activity" filter. Determine whether this violates Silver layer rules and what consequences this may have.
Solution: Step 1. Analyze the logic: balance > 0 is a business rule about the customer "having funds." last_transaction_date > CURRENT_DATE - INTERVAL '90 days' is a business definition of customer "activity." Step 2. Compare with Silver rules: Silver must not make product decisions if the model is reusable. Step 3. Conclusion: the condition turns the Silver model into a product decision (definition of "active customer"), which makes it unsuitable for other mart data products where the definition of activity may be different (for example, credit scoring may use 180 days). Step 4. Recommendation: remove the filter from the int_ model. Move aggregates (for example, days_since_last_transaction, has_positive_balance) as fields, and make the decision about "activity" in the mart layer.
Complexity: intermediate
Name: Auditing lineage through ref()
Problem: In the project, a model int_customer_card_activity_v2 was found that reads stg_card_transactions directly via source(), not via ref() to stg_card_transactions. How will this affect the review and what risks does it create?
Solution: Step 1. Check Silver requirements: "input models are read only through ref()." Step 2. What happens when source() is used instead of ref(): dbt will not include the dependency between int_customer_card_activity_v2 and stg_card_transactions in the project DAG as model-to-model. Step 3. Consequences for the reviewer: the reviewer will not see in the lineage that the int_ model depends on the stg_ model; will not be able to track which mart data products will be affected when the source changes; tests on stg_card_transactions will not automatically propagate to dependent models. Step 4. Recommendation: fix to ref('stg_card_transactions'), verify that the model appears in the DAG (command dbt ls --select int_customer_card_activity_v2 --output name).
Complexity: beginner
Name: Documenting a risk counter
Problem: The int_customer_card_activity model contains the field risk_event_count_7d with the value 0 or an integer. Write a specification for this field so that it passes the Silver layer acceptance check.
Solution: The specification must contain: (1) Definition of a risk event — for example, "a transaction marked with the is_risk flag in stg_card_transactions" or "a transaction with score > 0.8 according to the antifraud model." (2) Definition of the window — "the last 7 calendar days before the model execution date (execution_date - 7 days)" or "7 days before the transaction date." (3) Type of sliding window — fixed or rolling. (4) Source of the risk flag — stg_card_transactions.is_risk or a separate risk_scores table. (5) Null handling — if risk_score is empty, the event is not counted (or is counted, an explicit instruction is needed). (6) Grain of the final value — number of events per customer_id for the window. Without such a specification, the model looks like SQL but not like a stable data asset.
Complexity: intermediate
Name: Refactoring a wide int_ model
Problem: In the repository, a model int_customer_universe was found that contains: customer balances, card transaction aggregates, product flags, AML check status, and customer consent. Explain why this violates Silver layer rules and propose a refactoring plan.
Solution: Step 1. Check the rules: Silver must not mix unrelated domains and must have a clear grain and a single purpose. Step 2. Analyze the model: at least three domains are mixed inside — customer finances (balances), behavioral signals (card transactions), compliance (AML, consents). Each of them has its own update dynamics, its own owners, and its own mart consumers. Step 3. One grain (customer_id) formally exists, but a single purpose is missing: the model tries to be a universal bus for all mart data products at once. Step 4. Refactoring plan: split into three int_ models — int_customer_balances, int_customer_card_activity, int_customer_compliance (with AML and consents). If the Customer 360 mart data product needs all three, it does the JOIN itself via ref(). Step 5. Check against the criterion from the material: "does the layer help express a repeatable entity and prove its quality?" After splitting — yes; before splitting — no.
Complexity: advanced
Case studies: Name: Case: discrepancy of "active customers" between risk and CRM data products
Scenario: A bank from the top 20 (a conditional example) built a Data Lakehouse on dbt. The Silver layer contained the model int_customer_activity, which aggregated transactions and accounts at the customer_id level. Two mart data products depended on it: mart_risk_scoring (for credit scoring) and mart_crm_active_customers (for marketing campaigns). A filter was baked into int_customer_activity: customer_id IN (SELECT customer_id FROM stg_accounts WHERE balance > 0).
Challenge: Six months later, the risk scoring product team requested a recalculation with a different definition of activity (taking into account credit limits, without requiring a positive balance). In parallel, the CRM team asked to tighten the filter (only customers with transactions in the last 30 days). It became clear that the Silver model was not reusable — each mart data product was forced to create its own copy of the logic. Discrepancies arose: mart_risk_scoring_v1 showed 1.2 million "active" customers, mart_crm_active_customers_v1 showed 800 thousand, and mart_credit_eligibility_v2 (a new data product) showed 950 thousand. Analysts argued about which number was correct, auditors recorded a violation of the single source of truth principle.
Solution: The data team refactored the Silver layer according to the principles described in Part 14. Step 1: int_customer_activity was removed. Step 2: two separate int_ models were created — int_customer_balances (balance aggregates, without "activity" filters) and int_customer_card_activity (transaction aggregates with a documented risk_event_count_7d window). Step 3: for mart_risk_scoring, the has_positive_balance field was added as an attribute, and the activity filter was moved into the mart itself. Step 4: for mart_crm_active_customers, similarly — days_since_last_transaction was moved as an attribute, and the "30 days" business filter lives in the mart. Step 5: for mart_credit_eligibility — its own definition in the mart. Step 6: an acceptance run was carried out against the Silver checklist — ref() in inputs, one row per customer_id, no unexplained nulls, windows for counters documented.
Result: Two months after the refactoring: (1) three mart data products read the same Silver models, discrepancies in the definition of "activity" became explicit and discussable; (2) the time to change the activity logic decreased from 3 weeks (when three copies were fixed) to 3 days (when only the mart changes); (3) the audit passed without remarks on the single source of truth principle; (4) the lineage in dbt became transparent — any change in int_customer_card_activity is visible in all dependent marts.
Lessons learned: Silver must not contain product filters — otherwise it stops being reusable
Discrepancies between mart data products are a symptom of baked-in business rules in Silver
The principle of "one model — one purpose — one grain" is more important than architectural beauty
Documenting windows and flag sources is mandatory for passing the audit
Related concepts: Grain
Reusability of the Silver layer
Product decision in Silver
Lineage through ref()
Separation of responsibility between Silver and mart
Name: Case: PII leakage through a reusable Silver model
Scenario: A fintech company (an example based on real industry incidents) built int_customer_profile to aggregate customer data. The model read stg_customers, which contained full name, email, phone, passport data, date of birth, and aggregated all of this into one row per customer_id. The model was convenient — it was connected to five mart data products, including mart_marketing_segments (for external mailing).
Challenge: The information security service, during a scheduled audit, discovered that mart_marketing_segments was exported to the marketing platform with the full name and passport of the customer, although only the email hash and age group were needed for segmentation. Formally, there was no violation — the data was "consented" during onboarding. But the PII handling policy required minimization: each mart data product must receive only those PII that are justified by its purpose. Transferring the passport to marketing was recognized as excessive.
Solution: The data team applied the rule from Part 14: "do not expose direct PII further than needed." Step 1: int_customer_profile was split into int_customer_identity (full name, passport — only for compliance mart) and int_customer_attributes (age group, segment, email hash — for all others). Step 2: a policy is explicitly written in the model: "fields *_pii are available only for models with the compliance_use_case tag." Step 3: mart_marketing_segments now reads int_customer_attributes, which contains only the email hash. Step 4: an acceptance run was carried out — the item "PII does not leak into the reusable layer without a policy."
Result: The IS audit was closed without remarks. The marketing platform receives the minimum necessary set of data. The compliance mart retains access to the full PII through a separate protected branch. The time to onboard a new mart consumer has decreased — there is no longer a need to manually filter fields in int_customer_profile.
Lessons learned: PII in Silver is not a technical but a political question: each mart must have a justification
A wide model with PII is an antipattern of a reusable Silver layer
The separation of PII and attributes must happen before scaling the number of marts
dbt tags help automate control over PII policies
Related concepts: PII
Personal data handling policy
Reusability of the Silver layer
Grain
Tags in dbt
Study tips: Keep the Medallion architecture schema at hand: draw arrows Bronze → Silver → Gold in every example so as not to confuse the layers
Memorize the Silver model triad: grain + one purpose + documented windows. If at least one element is missing, the model is not ready
When reading someone else's int_ file in dbt, always check three things: (1) ref() in inputs, (2) presence of tests for grain key uniqueness, (3) presence of description in properties.yml
Do not measure Silver quality by the number of models. One careful int_ model is better than three layers with beautiful names — this is the key idea of Part 14
Use the Qwen prompt from the material as a template: "Compare intermediate models with specifications and mart requirements. Show grain, ref() inputs, reusable fields, and validation gaps"
When preparing for review, learn to explain each field of an int_ model in 30 seconds: what it is, where it comes from, why mart consumers need it
Make a grain/inputs/used in/validation gap table for each new int_ model before starting development — this disciplines
Avoid the temptation to "slightly improve" Silver — add a filter, rename a risk, calculate a convenient feature. Each such improvement must be recorded in the specification
Additional resources: Dbt documentation — intermediate models: https://docs.getdbt.com/best-practices/how-we-structure/1-guide-overview (section on staging → intermediate → marts)
Dbt documentation — ref() function: https://docs.getdbt.com/reference/dbt-jinja-functions/ref
Dbt documentation — graph operators and lineage: https://docs.getdbt.com/reference/dbt-jinja-functions/graph-operators
Data quality in dbt — tests and properties.yml: https://docs.getdbt.com/best-practices/warnings
The medallion architecture (databricks): https://www.databricks.com/glossary/medallion-architecture
Dbt tags for access policies: https://docs.getdbt.com/reference/resource-configs/tags
Qwen (tongyi qianwen) — llm for dbt code review: https://qwen.alibaba.com/
Book 'data mesh' (zhamak dehghani): The concept of data-as-a-product resonates with the principle of "one int_ model — one purpose"
Summary: The Silver layer in the architecture of banking data (customers, accounts, cards, payments) is a layer of stable reusable entities that removes the technical noise of sources but does not make product decisions. In the learning example, it is represented by two intermediate models: int_customer_balances (aggregation of accounts to the customer level) and int_customer_card_activity (aggregation of card transactions and risk counters). The main Silver rules: a clear grain (one row per customer_id for customer models), use of ref() to build lineage, prohibition on PII leakage without a policy, documented windows for counters (for example, what "7 days" means in risk_event_count_7d), no mixing of unrelated domains. The Silver quality criterion is simple: does the layer help express a repeatable entity and prove its quality? If not, the model was created for architectural symmetry, not for the reader or data consumer. For self-check, use the grain / inputs / used in / validation gap table and the control questions: why Silver should not be tied to one product, what PII cannot be transferred without a policy, how ref() helps the reviewer.