Topic: Part 18. Credit Portfolio Mart
Difficulty level: Medium
Estimated study time: 3-4 hours
Prerequisites: Basic SQL knowledge (SELECT, GROUP BY, aggregate functions, NULL handling)
Understanding the concept of data marts and layered architecture (staging → mart)
Familiarity with the principles of data quality, reproducibility, and data validation
General understanding of credit reporting and the terms stage/principal/days_past_due
Learning objectives: Explain the purpose of the educational mart mart_credit_portfolio_quality and its minimal contract
Distinguish source facts (stage, principal, days_past_due) from methodological interpretations not embedded in the specification
Apply verification invariants for an aggregated credit mart (NOT NULL, non-negative amounts, row count correspondence)
Compose correct specifications and review notes that prevent the introduction of false banking methodology
Formulate Qwen queries for analyzing the mart without modifying source files
Overview: Part 18 is dedicated to the credit portfolio mart mart_credit_portfolio_quality — an educational artifact demonstrating SDD Data work with aggregated reporting. Here the central danger shifts from PII leaks to the risk of false methodology: the words stage, principal, delinquency, and portfolio quality sound like elements of a serious banking model (IFRS 9, PD, LGD, ECL), and an agent may try to "improve" the educational example by injecting external knowledge into it. The chapter teaches how to separate source facts from interpretations, fix the boundaries of the mart through specification and review note, and assemble verification invariants ensuring reproducibility and honesty of the report.
Key concepts: Mart credit portfolio quality: An educational data mart that aggregates synthetic loans by the stage attribute. Minimal contract: stage NOT NULL, principal aggregated reproducibly, delinquency not lost during aggregation, the source of stage described explicitly, business interpretation not invented by the agent.
Stage as a synthetic attribute: In a real bank, stage is associated with IFRS 9 and risk methodology, but in the educational example stage is simply a source field. It must be explicitly treated as synthetic, otherwise the agent will begin to "build out" methodology where none exists.
Principle of separation of facts and interpretations: Every mart field must be classified as (a) a source fact, (b) an educational simplification, or (c) requiring a separate banking specification. Without such classification, the mart looks convincing but may mean different things.
Minimal mart contract: A set of obligations: stage not null, principal amounts not negative (if dictated by source rules), row count equals the number of unique stage values, reviewer confirmed that stage is not redefined in SQL, a note about the impact on the contract is recorded.
Specification vs. implementation: A bad specification: "Calculate credit portfolio quality." A good specification: "Group synthetic loans by source stage; show loan_count and principal_rub; do not derive IFRS staging rules; treat stage as a source field." Implementation must follow the specification exactly.
Qwen query for mart review: A standardized prompt of the form: "Compare stg_loans and mart_credit_portfolio_quality. Show what business assumptions the SQL makes. Separate source facts from derived rules. Do not modify files." — allows audit without modifying sources.
Review note: A fixed structure of six blocks: source stage, aggregation, indicators, assumptions, verification, confirmation required. This is a modest role: the reviewer does not become a credit committee, but merely checks the boundaries of the mart.
Typical mistake: false methodology: An agent may know IFRS, PD, LGD, ECL terms and try to inject them into the educational project. Outside of a product specification this is a mistake: the mart begins to assert more than it can prove.
Days past due as a source fact: The number of days of delinquency is system data, not a methodological decision about portfolio quality. The decision of how exactly days_past_due affects the stage is already a separate methodology requiring specification.
Principal rub and its duality: It can mean outstanding debt or the original loan amount. If this is not clarified in the specification, the aggregate looks correct but is interpreted ambiguously. The specification should either close the question or explicitly leave it open.
Important dates: January 1, 2018: Effective date of IFRS 9 — the standard that in real banks links stage with the calculation of expected credit losses (ECL). In the educational mart this is external context, mentioned only to understand the nature of the stage field.
Date not specified — educational context: Within the course, stage is treated as a synthetic attribute without binding to a real methodological date. Any "ECL calculation date" in the educational example is invented by the agent and must be removed.
Practice exercises: Name: Auditing the minimal mart contract
Problem: You are given a SQL query that builds mart_credit_portfolio_quality:
SELECT stage, COUNT(*) AS loan_count, SUM(principal_rub) AS total_principal FROM stg_loans GROUP BY stage;
Check it for compliance with the minimal contract of the educational mart. Which invariants are verified at the data level and which at the specification level?
Solution: Step 1. NOT NULL check: make sure stage contains no NULL (e.g., SELECT COUNT(*) FROM stg_loans WHERE stage IS NULL). Step 2. Non-negativity check: SUM(principal_rub) >= 0 for each group, or explicitly record that negative values are allowed by source rules. Step 3. Row count check: the number of rows in the mart equals the number of unique stage values. Step 4. Confirmation that stage is not redefined in SQL (no expressions like CASE WHEN days_past_due > 90 THEN 3 ... — that would be methodology). Step 5. The note about the impact on the contract records that the methodology has not changed.
Complexity: intermediate
Name: Writing a review note
Problem: Fill in the review note for the credit mart using the template from the chapter. Assume stg_loans contains 1200 synthetic loans with three stage values (1, 2, 3), principal_rub is in the range of 50,000–5,000,000, days_past_due ranges from 0 to 180.
Solution: Source stage: stg_loans.stage field, a synthetic attribute without binding to IFRS 9. Aggregation: GROUP BY stage, aggregates loan_count (COUNT) and principal_rub_total (SUM). Indicators: loan_count, principal_rub_total, average principal_rub (only as a derived source metric). Assumptions: stage is treated as a source field; principal_rub is the sum of source values, without accounting for outstanding balance; days_past_due is not interpreted in the mart. Verification: stage NOT NULL — OK; sums >= 0 — OK; row count = 3 — OK; stage not redefined in SQL — OK. Confirmation required: methodological interpretations (e.g., which stage to consider "problematic") are not within the scope of the educational mart.
Complexity: intermediate
Name: Detecting false methodology in SQL
Problem: A colleague wrote a mart with the following fragment:
SELECT CASE WHEN days_past_due <= 30 THEN 1 WHEN days_past_due <= 90 THEN 2 ELSE 3 END AS stage, COUNT(*) AS loan_count, SUM(principal_rub) AS total_principal FROM stg_loans GROUP BY 1;
What is the violation of the chapter's principles here? Rewrite the query in correct form.
Solution: Violation: stage is redefined in SQL based on days_past_due. This is the injection of methodology (essentially a simplified IFRS 9 staging) that is not in the specification. The agent (or colleague) substituted the source field with their own logic. Correct form:
SELECT stage, COUNT(*) AS loan_count, SUM(principal_rub) AS total_principal FROM stg_loans GROUP BY stage;
Stage is used as a source field without redefinition. If a breakdown by days_past_due is needed, that is a separate mart with its own specification.
Complexity: intermediate
Name: Classifying a mart field
Problem: The mart mart_credit_portfolio_quality has acquired fields: stage, loan_count, principal_rub_total, avg_days_past_due, ecl_provision. Classify each field into three categories: (a) source fact, (b) educational simplification, (c) requires a separate banking specification.
Solution: stage — source fact (stg_loans.stage field). loan_count — source fact (derived from row count, reproducible). principal_rub_total — source fact (if interpreted as the sum of source values), but requires clarification: outstanding balance or original amount. avg_days_past_due — educational simplification (aggregation of a source fact without methodological interpretation). ecl_provision — requires a separate banking specification, since IFRS 9 ECL is a full-fledged methodology for calculating expected losses; it cannot be added to the educational mart without a separate requirements document.
Complexity: intermediate
Name: Formulating a Qwen query for review
Problem: Formulate a Qwen query to compare stg_loans and mart_credit_portfolio_quality. The query must: (1) not modify files, (2) highlight the business assumptions of SQL, (3) separate source facts from derived rules.
Solution: Reference query from the chapter: "Compare stg_loans and mart_credit_portfolio_quality. Show what business assumptions the SQL makes. Separate source facts from derived rules. Do not modify files." Alternative formulations are acceptable while preserving three invariants: read-only mode, explicit requirement to highlight assumptions, explicit requirement to separate source from interpretation.
Complexity: beginner
Case studies: Name: Case 1: The agent "improved" the educational mart with banking methodology
Scenario: A student worked with the educational mart mart_credit_portfolio_quality. Having received the task "calculate credit portfolio quality," the LLM agent generated SQL that calculated stage according to IFRS 9 rules (12-month ECL for stage 1, lifetime ECL for stage 2 and stage 3 based on days_past_due and PD/LGD indicators). The mart looked professional and contained ten metrics, including ecl_provision_rub.
Challenge: The mart asserted methodological things that were not in the specification. The student could not verify the correctness of the PD/LGD logic, since the source data was synthetic. At the same time, the report looked "real" and could be mistakenly perceived as banking analytics.
Solution: The student returned to the specification, rewrote it in explicit form: "Group synthetic loans by source stage; show loan_count and principal_rub; do not derive IFRS staging rules; treat stage as a source field." SQL was rewritten under this specification. The agent was re-invoked with the clarified specification — all ECL fields were removed.
Result: The mart shrank to three fields (stage, loan_count, principal_rub_total), but became honest and reproducible. The review note recorded that methodology is not in scope. The student learned the key lesson: implementation follows specification, not the agent's "common sense."
Lessons learned: An LLM agent may know IFRS 9 / PD / LGD / ECL terms and inject them without specification — this is a typical mistake of educational projects
Specification must be explicit and close methodological decisions, otherwise the agent will adopt them for the student
Reproducibility is more important than imaginary completeness: a small mart that does not lie is more valuable than a big one that asserts too much
Related concepts: Stage as a synthetic attribute
Principle of separation of facts and interpretations
Typical mistake: false methodology
Specification vs. implementation
Name: Case 2: Discrepancy in principal_rub — outstanding balance or original amount
Scenario: An analyst built the mart mart_credit_portfolio_quality and took the principal_rub field from stg_loans as is. The mart showed SUM(principal_rub) = 4.2 billion rubles for 1200 loans. When shown to management, the question arose: "Is this the current outstanding balance or the amount of disbursements?" The analyst could not answer immediately.
Challenge: Without clarification in the specification, the same number could mean fundamentally different things. If it is the outstanding balance — the portfolio is healthy; if it is the disbursed amount without accounting for repayments — the picture is different. The mart looked credible, but the interpretation was ambiguous.
Solution: The analyst turned to the source, established that principal_rub is the original loan amount at the time of disbursement. The specification was supplemented with an explicit indication: "principal_rub is treated as the original loan amount (gross principal at origination), without accounting for repayments." If the field meant the outstanding balance, the wording would be accordingly.
Result: The review note received a clarification in the "Assumptions" block. Now any interpretation of the mart is unambiguous. The analyst formulated a rule: "In credit data, it is especially useful to separate the source fact from interpretation."
Lessons learned: principal_rub is an ambiguous field: outstanding balance or original amount. Specification must close this
A convincingly looking aggregate may mean different things — this is a data quality risk
Specification should either close the question or explicitly leave it open — but not remain silent
Related concepts: principal_rub and its duality
Principle of separation of facts and interpretations
Minimal mart contract
Name: Case 3: The educational mart accidentally ended up in a regulatory report
Scenario: An internal educational project on SDD Data showed the mart mart_credit_portfolio_quality with realistic numbers. Demo data was accidentally connected to a working BI dashboard used for a weekly portfolio review. One of the credit analysts copied a number from the mart into an internal memo.
Challenge: The educational mart had no relation to the real portfolio, but its appearance (stage, principal_rub, days_past_due) was indistinguishable from production reporting. There was a risk of using synthetic data for a real management decision.
Solution: The team added an explicit educational technical marker to the mart: a comment at the beginning of the DDL file, the watermark "EDUCATIONAL MART — NOT FOR REGULATORY REPORTING" in the name, a separate database schema. The review note was supplemented with a block on application boundaries.
Result: Accidental use ceased. The team recorded a rule: educational marts must have explicit educational markers in names, schemas, and metadata so that they cannot be confused with production reporting.
Lessons learned: The external appearance of the mart (stage, principal, delinquency) should not mask its educational status
Technical markers (schema, prefix, watermark) are mandatory for educational artifacts
A good reviewer's report checks not only SQL, but also the context of mart application
Related concepts: Stage as a synthetic attribute
Review note
Boundary between educational mart and real methodology
Study tips: Before writing SQL, always return to the specification and check that you are not injecting methodology that is not there
When working with an LLM agent, use explicit prohibitions: "do not derive IFRS staging rules", "treat stage as a source field", "do not modify files"
Get into the habit of filling out the review note using the six-block template — this disciplines and prevents "confident text around unproven methodology"
Separate in your mind three field categories: source fact, educational simplification, requires a separate banking specification — and apply this classification to each mart field
Do not confuse reproducibility with completeness: a small mart that does not lie is always better than a big one that asserts too much
When analyzing days_past_due, remember: the number is a fact, and the decision of how it affects portfolio quality is already methodology
Use the Qwen query from the chapter as a standard prompt for mart review — it separates facts from interpretations well
Remember the modest role of the reviewer: they are not a credit committee, but a checker of boundaries. This is the prevention of the main educational defect
Additional resources: IFRS 9 — financial instruments (overview): https://www.ifrs.org/issued-standards/list-of-standards/ifrs-9-financial-instruments/ — to understand how stage is associated with ECL in a real bank. Use only as context, not for injection into educational SQL
Data marts documentation (Kimball): https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/ — classical principles of building data marts
DBT documentation — mart layer: https://docs.getdbt.com/best-practices/how-we-structure/1-guide-overview — example of organizing the mart layer in modern ELT projects
DBT assertions and not null tests: https://docs.getdbt.com/docs/build/tests — for automating the stage NOT NULL invariant check
Great Expectations — data validation: https://docs.greatexpectations.io/docs/ — a tool for checking the non-negativity of amounts and row count correspondence
Russian-language course on SDD Data: Parts 1–17 of the textbook — to understand the previous context on the staging layer and general principles of working with PII
Summary: Part 18 teaches how to work with aggregated credit reporting, where the main risk is not a PII leak, but false methodology. The mart mart_credit_portfolio_quality aggregates synthetic loans by the stage field, which is treated exclusively as a source field, without redefinition in SQL. The minimal contract requires: stage NOT NULL, reproducible principal aggregation, explicit description of the stage source, absence of invented business interpretation. Verification invariants include NULL control, non-negativity of amounts, correspondence of row count to the number of unique stage values, and reviewer confirmation that methodology has not changed. The chapter consolidates three key skills: separation of source facts from interpretations, composition of a review note according to a fixed template, and formulation of Qwen queries for audit without modifying files. The main lesson: the educational mart must clearly show where the example ends and real banking methodology begins — otherwise the agent will inject external knowledge (IFRS, PD, LGD, ECL) without specification, and the report will begin to assert more than it can prove.