Topic: Part 17. Payments and Risk Signals Mart
Difficulty level: Medium
Estimated study time: 2.5–3 hours
Prerequisites: Basic knowledge of SQL (SELECT, WHERE, JOIN, GROUP BY)
Understanding of the concept of data marts and warehouse layers
Familiarity with the Specification-Driven Development (SDD) methodology
Basic understanding of the banking and payments domain
Awareness of principles for working with PII and risk signals
Learning objectives: Define and fix the grain of the risk-signals mart at the transaction level and distinguish it from aggregation by customer or day.
Explain why the risk_flag = true filter without an explicit specification is considered a breaking change for the observation mart.
Compose a note about the risk mart following the template, specifying granularity, signal source, verification facts, and manual reviewer questions.
Separate the role of the signal source (stg_card_transactions) from the role of the decision, not turning the educational risk_flag into a scoring product.
Formulate manual facts for the reviewer: where the large-amount threshold is, which operations are included/excluded, and where PII appears.
Overview: Chapter 17 of the textbook is dedicated to designing the mart_payment_risk_signals mart — an observation layer, not a scoring engine. The mart aggregates payment operations marked with a risk flag from the source and records large amounts/channels that require attention. The key idea of the chapter: a technically simple aggregation in the payments domain can easily look like a risk decision, and therefore must be strictly separated from scoring. The student learns to fix the grain at the transaction level (one row per transaction_id), treat risk_flag as a source signal, not discard operations without a flag without an explicit specification, avoid direct PII, and explicitly record large-amount thresholds in the specification, verification note, and reviewer report. The chapter develops the skill to ask the question "what row does the mart represent?" and understand that transaction-level and customer-level marts are different data products with different requirements.
Key concepts: Observation mart vs. scoring engine: mart_payment_risk_signals is an observation mart. It records which operations are marked with a risk flag and which amounts/channels require attention. This is NOT a scoring model: scoring requires a separate methodology, explainability, quality control, and legal review. Here, risk_flag is a signal for the SDD process, not a product decision.
Mart granularity (grain): For a transaction-level mart, the grain is strictly fixed: one row per transaction_id. transaction_id not null and unique are mandatory invariants. If the mart aggregates by customer or day — that is a different data product with a different specification and different tests.
Risk flag as a source signal: In the educational project, risk_flag is treated as an educational signal from the stg_card_transactions source, not as the result of an analytical model. The student must not invent their own definition of risk (e.g., "amount above threshold") and apply it to SQL without a specification.
Mart completeness vs. the risk flag = true filter: The where risk_flag = true filter is only acceptable if the specification explicitly states: "the mart contains only risk events." If the consumer expects all operations with a risk attribute, such a filter breaks completeness. This is a typical trap: the agent "simplifies" the SQL without checking the contract.
Large-amount threshold as an educational rule: The large-amount threshold in mart_payment_risk_signals is an educational rule, not a banking truth. If the threshold is written only in SQL, the reviewer will not understand whether it is a temporary simplification, a product requirement, or the agent's guess. The threshold must appear in the specification, verification note, and reviewer report.
PII protection: Direct PII (name, passport, full card number) must not appear in the risk mart. Aggregates, masks, and identifiers are acceptable, but not raw personal data. This requirement must be verified in the verification facts.
Operations without a risk flag: Operations without risk_flag cannot be removed from the upstream layer without an explicit specification. If only a portion of transactions ends up in the mart, the reviewer must see the reason for the exclusion.
Mart note (minimum output): Based on the analysis, the agent composes a markdown note with six fields: Mart, Granularity, Risk-flag source, Included rows, Excluded rows, Verification facts, Manual reviewer questions. This disciplines separating facts from guesses.
Qwen query for SDD analysis: Standardized prompt: "Read stg_card_transactions, mart_payment_risk_signals, and specs. Determine the grain, which operations are included, which are excluded, and what verification facts are needed for the risk mart. Do not modify files." — fixes the agent's contract of work as an observer, not an editor.
Difference between transaction-level and customer-level marts: These are two different data products. Transaction-level mart: key is transaction_id, customer repeats, tests are uniqueness and not null on id. Customer-level mart: key is customer_id, aggregates by amounts/channels, tests are different completeness and consistency. One's SQL cannot be silently replaced by the other's.
Important dates: Educational context of the volume: The mart is considered in the context of SDD training, not real production. Therefore, all "rules" (amount threshold, interpretation of risk_flag) are educational constructs.
Moment of grain fixation: The grain decision is made BEFORE writing SQL, at the specification stage. Changing the grain post-factum is a breaking change for consumers.
Moment the threshold appears in artifacts: The large-amount threshold must be fixed simultaneously in specs, the verification note, and the reviewer report, so that a future author can replace the methodology without hidden drift.
Practice exercises: Name: Exercise 1. Mart note following the template
Problem: Given a specs file describing mart_payment_risk_signals and a mart SQL script that uses the filter WHERE risk_flag = true AND amount > 10000. Without modifying files, fill in the markdown template: Mart, Granularity, Risk-flag source, Included rows, Excluded rows, Verification facts, Manual reviewer questions. Explain what risks the current SQL carries.
Solution: Step 1. Mart: mart_payment_risk_signals. Step 2. Granularity: one row per transaction_id (if the specification confirms this). Step 3. Risk-flag source: stg_card_transactions.risk_flag, treated as a source signal. Step 4. Included rows: operations where risk_flag = true AND amount > 10000 (per current SQL). Step 5. Excluded rows: all operations with risk_flag = false or amount <= 10000 — potentially a breaking exclusion if the specification requires all operations with a risk attribute. Step 6. Verification facts: transaction_id is unique and not null; amount not null; threshold of 10000 fixed in specs; PII absent. Step 7. Manual reviewer questions: why was the threshold of 10000 chosen? Is this a product requirement or the agent's guess? Should all operations with a risk flag be in the mart? Where in specs is the amount filter fixed?
Complexity: intermediate
Name: Exercise 2. Comparing two grain variants
Problem: Describe two variants of the risk mart: transaction level and customer level. For each, specify the grain, consumer, key fields, and why one variant's SQL cannot be silently replaced by the other.
Solution: Variant A — transaction level: grain = one row per transaction_id. Key — transaction_id. Consumer: fraud-monitoring team, which needs to see every marked operation. Fields: transaction_id, customer_id (repeats), amount, channel, risk_flag, threshold_marker. Variant B — customer level: grain = one row per customer_id per period. Key — customer_id + period. Consumer: product analytics, which needs a slice of "how many risk operations a customer has and for what amount." Fields: customer_id, period, ops_count, total_risk_amount, channels_set. One variant's SQL cannot be silently replaced by the other: the consumer contract changes, different completeness checks are required (uniqueness of transaction_id vs. uniqueness of the customer_id+period pair), different JOINs to sources, and different protection against double counting.
Complexity: intermediate
Name: Exercise 3. Finding a typical error
Problem: Find a typical error in the following mart description: "mart_payment_risk_signals is a scoring model that, based on risk_flag and an amount above 50000, makes a decision to block the operation. The mart contains only operations that our model considered risky." Reformulate the description so that it matches the SDD contract of the observation mart.
Solution: Step 1. Error #1: the mart is called a "scoring model." The mart_payment_risk_signals mart is an observation layer, not scoring. Step 2. Error #2: "makes a decision to block." The decision to block is a product/risk process, not a function of the mart. Step 3. Error #3: "based on risk_flag AND amount above 50000" — a combined criterion invented by the agent and not fixed in specs. Step 4. Error #4: "contains only operations that our model considered risky" — the filter breaks completeness if the specification requires all operations with a risk attribute. Correct wording: "mart_payment_risk_signals is an observation mart with a grain of one row per transaction_id. It contains operations from stg_card_transactions with a risk-flag attribute; it separately marks operations with an amount above the educational threshold fixed in specs. It is not scoring and does not make risk decisions. PII is absent."
Complexity: intermediate
Name: Exercise 4. Formulating manual reviewer questions
Problem: In front of you is the mart SQL: SELECT transaction_id, customer_id_hash, amount, channel FROM stg_card_transactions WHERE risk_flag = true. Formulate at least five manual questions for the reviewer that the mart note should contain.
Solution: Question 1. Is the risk_flag = true filter a specification requirement or the SQL author's guess? If the specification requires all operations with a risk attribute, the filter breaks the contract. Question 2. How exactly is customer_id hashed, and is this acceptable from a PII policy perspective? Question 3. Why were exactly the fields transaction_id, customer_id_hash, amount, channel chosen? Is this sufficient for the mart consumer? Question 4. Are there operations in the source with risk_flag IS NULL, and where should they go? Question 5. Where is the educational large-amount threshold fixed — in specs, in code, or nowhere? Question 6 (optional). Why is the mart's grain a transaction and not a customer, and is this choice aligned with the consumer's contract?
Complexity: intermediate
Case studies: Name: Case 1. Silent replacement of transaction-level grain with customer-level grain
Scenario: In a fintech startup, the analytics team asked an AI agent to "simplify" the risk-signals mart SQL. The agent saw that the consumer needed a customer-level slice, and replaced the SELECT with a GROUP BY customer_id aggregation, removing the uniqueness of transaction_id. The SQL became 30 lines shorter, passed the linter review, and got into production under the same name mart_payment_risk_signals.
Challenge: The fraud-monitoring consumer discovered that the same operation started to "get lost" if a customer made several risky transactions in a day: the mart aggregated amounts, and the individual transactions stopped being distinguishable. Additionally, the uniqueness key broke: rows appeared that did not match the original grain contract. The incident investigation took 2 working days, and the quarterly reporting had to be recalculated.
Solution: The team introduced a strict rule: transaction-level and customer-level marts are different data products with different names and different specifications. The grain and the list of required fields were explicitly fixed in specs. A "Has the grain changed relative to the specification?" item was added to the note template as a mandatory manual reviewer question. A test contract was also introduced: for a transaction-level mart, CI checks that COUNT(*) equals the DISTINCT transaction_id from the source.
Result: After implementing the rule, there were no repeated grain mix-ups for a quarter. SQL review time for marts decreased thanks to the standardized note template. The team was able to honestly support both data products: mart_payment_risk_signals_ops (transaction level) and mart_customer_risk_profile (customer level).
Lessons learned: grain is a mart contract, not an optimization detail of SQL.
A shorter SQL does not mean a more correct SQL.
Key uniqueness test is a mandatory invariant for transaction-level marts.
The mart note template must explicitly fix grain as a separate field.
Related concepts: Mart granularity (grain)
Difference between transaction-level and customer-level marts
Mart note (minimum output)
Mart completeness vs. the risk_flag = true filter
Name: Case 2. Large-amount threshold written only in SQL
Scenario: A mart mart_payment_risk_signals appeared in a banking data lake with a hard-coded threshold amount > 50000 for marking a "large operation." The threshold was chosen by an analyst based on intuition and written only in SQL. The mart specification mentioned "mark large operations," but the specific value was absent.
Challenge: Six months later, the compliance team requested a report: which exact operations are considered large and on what basis? The reviewer could not reconstruct the logic of the threshold choice: was it a regulatory requirement, a product limitation, or the author's personal guess? Additionally, it turned out that in parallel, in another department, there was a mart with a threshold of 30000, which created reporting inconsistencies.
Solution: The team implemented a rule: any numeric rule (threshold, limit, coefficient) must be fixed in specs as a parameter with an indication of the source (product requirement, regulator, educational rule). The mart note became required to contain a section "Thresholds and their origin." A field large_amount_threshold = 50000 (educational rule, requires verification by the product team) appeared in specs.
Result: In one quarter, they managed to bring the thresholds to a single value and fix their origin. Future mart authors got the ability to replace the threshold without hidden drift, since the rule's source was now documented. The number of discrepancies between marts of different teams decreased.
Lessons learned: Any "magic number" in SQL is a potential source of hidden drift.
Thresholds must be accompanied by metadata: who set it, why, when to review.
A mart note is not a code report, but a document about the author's intentions.
In an educational project, thresholds are explicitly marked as educational rules, not banking truth.
Related concepts: Large-amount threshold as an educational rule
Mart note (minimum output)
Manual reviewer questions
Name: Case 3. The risk_flag = true filter without specification
Scenario: An analyst asked an AI agent to "make a mart of risk operations." The agent, without checking the specification, added the condition WHERE risk_flag = true to the SQL. The mart started to contain only operations marked as risky by the source. The linter test passed, and the mart was published.
Challenge: Two weeks later, it turned out that the auditor consumer expected in the mart ALL operations with a risk attribute, including those where risk_flag has a non-binary value (for example, intermediate statuses). Part of the audit checks gave a false conclusion "everything is clean," although in reality part of the operations was skipped by the filter. This was discovered only when investigating an incident with a customer complaint.
Solution: The team formalized the Qwen query as a mandatory first step: "Read stg_card_transactions, mart_payment_risk_signals, and specs. Determine the grain, which operations are included, which are excluded, and what verification facts are needed for the risk mart. Do not modify files." Without this query, the agent did not proceed to analysis. An explicit field included_rows_definition was also added to specs, listing the inclusion/exclusion logic.
Result: The number of cases where the risk_flag = true filter appeared without justification decreased. Separate lines "Included rows" and "Excluded rows" were added to the note template, which are filled in BEFORE any SQL changes. For a quarter, there was not a single incident related to silent mart filtering.
Lessons learned: "Simplifying" SQL without checking against the specification is a common cause of breaking changes.
Filtering by a binary field in the source requires an explicit indication in specs.
The standardized Qwen query fixes the agent's role as an observer.
The mart note disciplines the separation of "included/excluded."
Related concepts: Mart completeness vs. the risk_flag = true filter
risk_flag as a source signal
Qwen query for SDD analysis
Mart note (minimum output)
Study tips: Start with the question "what row does the mart represent?" and fix the answer BEFORE looking at SQL. This is the most common source of errors.
Always keep three artifacts nearby: specs, SQL, and the mart note. Any discrepancy between them is a reason for a manual reviewer question.
Make a checklist: transaction_id unique/not null? amount not null? risk_flag source specified? PII absent? threshold fixed in specs?
Treat risk_flag as a source signal, not as a decision. In the educational project, this is a fundamental boundary that does not allow "improvements."
Do not try to "shorten" a transaction-level mart SQL to a customer-level aggregate — these are different data products. If the consumer needs an aggregate, create a separate mart with a separate specification.
The WHERE risk_flag = true filter is a red flag if specs do not contain the explicit phrase "the mart contains only risk events." By default, consider it a breaking change.
Use the note template as a discipline: fill in the "Included rows" and "Excluded rows" fields BEFORE writing SQL — this forces you to think explicitly about the contract.
Mark any "magic numbers" in SQL (thresholds, limits, coefficients) and require their presence in specs. Otherwise, in six months, no one will remember where they came from.
Additional resources: Course textbook (part 17): Section "Part 17. Payments and Risk Signals Mart" — the main source, chapters Grain, What to Check, Bad Approach, Qwen Query, Minimum Output, Reader Walkthrough, Practice, Typical Error, Control Questions.
Mart specification: Specs file for mart_payment_risk_signals — mandatory context for filling in the mart note.
Mart sql: File mart_payment_risk_signals — the object of analysis, not edited by the agent in the educational scenario.
Source staging layer: stg_card_transactions — contains the original risk_flag as a source signal.
Sdd methodology: Specification-Driven Development — the general framework in which the risk mart is an observation artifact, not a decision.
Mart note template: Minimum output: Mart / Granularity / Risk-flag source / Included rows / Excluded rows / Verification facts / Manual reviewer questions.
Chapter control questions: Three questions at the end of the section: (1) why transaction-level and customer-level marts cannot be mixed; (2) when the risk_flag = true filter breaks the contract; (3) what manual fact the reviewer must check.
Summary: Part 17 introduces a key SDD discipline for the payments domain: the mart mart_payment_risk_signals is an observation layer, not a scoring engine and not a source of risk decisions. The mart's grain is strictly fixed as "one row per transaction_id," and any attempt to replace it with aggregation by customer or day is the creation of a new data product with a separate specification, not an "optimization" of the existing one. risk_flag is treated as a signal from the stg_card_transactions source, not as a product decision; the WHERE risk_flag = true filter is acceptable only with an explicit indication in specs. The large-amount threshold is an educational rule that must be documented in specs, the verification note, and the reviewer report, otherwise it becomes a hidden magic number. Direct PII does not get into the mart. The main skill of the chapter is to fill in the standardized mart note template (Mart / Granularity / Risk-flag source / Included rows / Excluded rows / Verification facts / Manual reviewer questions), not to confuse the role of the signal source with the role of the decision, and to separate observation from conclusion.