These analytical notes explore a field I want to develop in. The examples are hypothetical and do not describe inspections I have carried out.
Define before querying
Identify the record unit: borrower, contract, drawdown or collateral. Joining tables without understanding their grain can multiply amounts. Reference dates and the treatment of closed or transferred contracts also matter.
An exception is not necessarily an error
Checks can flag missing identifiers, unusual approval dates, duplicates and unreconciled totals. Each result needs an explanation against a defined rule. A zero interest rate may be a valid product term; a missing rate is a different issue.
Hypothetical example
Exposure doubles after a collateral join because one contract has two collateral assets. Aggregate loans and collateral separately, and compare counts before and after the join. A reproducible query then makes the check easier to repeat.
Preparing reproducible analysis
First record input files and baseline reconciliation totals. Check field types, currencies and identifiers before writing exception queries. This helps distinguish a portfolio issue from an import or join error.
Give every test a plain-language definition. “Find contracts where drawdown predates recorded approval” is easier to discuss than code alone. Explain treatment of missing values, historical migrations and time zones wherever relevant.
From query output to a verified case
For duplicated exposure after joining collateral, compare row counts and unique contract counts at each step. This separates a valid one-to-many relationship from inflated totals. Reconcile the corrected output to the source overview rather than only to the original query.
Data tests identify candidates for investigation. Specialist conclusions still need case and policy context. Add review statuses such as explained, confirmed exception and awaiting evidence, so the team can distinguish a working list from verified findings.
Questions for further review
Which records support the conclusion? What alternative explanation remains possible? What additional evidence would change the assessment? These questions help keep the analysis open to challenge and its scope clear.
A real case: data quality as a supervisory issue
On 7 October 2020, the US OCC imposed a USD 400 million penalty on Citibank for wider weaknesses involving risk management, data governance and controls. I do not attribute that case to a single SQL error; it illustrates why data work belongs within institutional governance.
I would avoid interpreting such an announcement as evidence that a few extra validation queries will solve everything. An analytical test can identify an exception, but it needs an accountable rule owner, a correctly defined population and an agreed response. Without ownership, the same exception list can circulate repeatedly while nobody decides whether the findings represent errors, accepted limitations or a process that needs to change.
A hypothetical calculation: one contract, two collateral items
This example is invented. A loan table contains three contracts with exposures of CZK 10, 20 and 30 million, giving a total of CZK 60 million. The first contract has two collateral items and each other contract has one. A direct join creates four rows and a summed loan amount of CZK 70 million. The extra CZK 10 million reflects repeated data, not additional business.
I would not correct this by removing an arbitrary row, because that could also discard legitimate collateral information. I would separate two questions: the total loan exposure and the collateral associated with it. Loan amounts would be aggregated at contract level before the collateral join. Collateral would be processed separately according to its own rules, with the results combined only after those questions had been resolved.
A control table would record row count, distinct contract count and exposure total at every stage. Before the join it would show three contracts and CZK 60 million. After the join it would show four rows but still three distinct contracts. That change is not inherently a defect. What matters is whether the calculation respects the meaning of a row. A technically valid relationship does not automatically produce an economically valid aggregation.
How I would design the test set
I would start by expressing each question in ordinary language. One test checks key uniqueness, another required information, and another reconciliation to an independent total. Each needs an expected result, explicit treatment of missing values and known legitimate exceptions. A query that simply returns everything unusual may create a large workload without distinguishing the cases that matter most.
I would prioritise potential impact. A missing internal note and an incorrectly assigned currency are not equivalent defects. Before automation I would manually check a small set of known valid and invalid examples. These would include a zero-exposure contract, multiple drawdowns and a historical identifier change. This would show whether a test detects its intended error or merely flags an unfamiliar but legitimate data structure.
What I would hand to the specialist team
I would distinguish confirmed errors, explained exceptions and unresolved cases. Every item would identify the rule, reference date and underlying record. I would not infer the quality of an entire credit process from the exception count alone. Selection may be targeted, some records may reflect migration history, and a single underlying problem can produce hundreds of technically similar rows.
On a repeat run I would examine both new and disappearing exceptions. A lower count does not establish remediation if the process has started excluding problematic records. I would retain the population definition and compare rule changes across runs. Time to explanation and the number of cases returned for clarification would also be useful. They help identify friction between data analysis and process ownership.
When the rule itself is unclear
A test may reveal an ambiguous requirement rather than a bad record. An approval date might mean the initial decision, a contract amendment or a limit renewal. I would obtain the rule owner’s confirmation instead of choosing the interpretation that makes the query easiest to write. Until then, the output remains a clarification list, not a confirmed breach. Changes to that definition should be recorded so that later results remain comparable.
Summary and recommendations
I would favour fewer, well-explained tests over a larger collection of opaque queries. Each should have a purpose, verified example, accountable owner and subsequent decision. Often the most valuable analytical work is defining the record correctly and reconciling control totals, rather than writing complicated code. A reproducible result with a sound business explanation is what turns a query into useful evidence.
Sources and links
- OCC — Citibank enforcement announcement, 7 October 2020
- ECB — Guide on effective risk data aggregation and risk reporting, 2024
Foreign cases provide analytical lessons; their legal frameworks and measures are not automatically applicable to Czech banks. Hypothetical examples and proposed methods do not describe inspections I personally conducted.