A posting lands in the data warehouse with an account_id that does not exist in the accounts table. A card authorisation points to a merchant that was never loaded. A payment names a counterparty ID that the risk system has not seen. Nothing crashes. The rows simply fall out of every inner join, and the totals downstream are quietly wrong. Referential integrity in banking data means that every reference (a posting's account, a payment's counterparty, a loan's customer) points to a record that actually exists in the master table it names.

Core banking systems usually enforce their own keys. The trouble starts once data leaves them: the warehouse, the risk engine, the AML platform and the reporting layer each get their own copy, on their own schedule, often with a different key format. That is where orphans appear, and where banking data quality is actually decided.

Below: the references that matter in a bank, what breaks when they don't hold, why orphans keep appearing, and how to check each one with digna without copying data out of your systems.

Key takeaways

• An orphan in banking data is not an error message. It is a posting, payment or exposure that silently drops out of a join.

• Check the joins reports depend on: postings, authorisations, payments, loans, FX rates and AML alerts against their masters.

• Most orphans come from timing and translation: late master data, migrations, product launches and differing key formats.

• In digna Data Validation each relationship is one Referential Integrity rule, with a zero threshold for regulatory data and relative thresholds only for noisy feeds.

• Since Release 2026.01 a rule can compare a table in the core system with one in the warehouse, and the failing records can be exported as evidence.

Table of Contents

• Key takeaways

• Which references matter most in banking data?

  • Composite keys

  • Cross-system references

• What happens when a banking reference breaks?

• Why do orphan records appear in banking systems?

• How do you find orphan postings with SQL?

• How do you set up a referential integrity check in digna?

• Which threshold should regulatory data use?

• How do you check core banking against the data warehouse?

• How do failing records become audit evidence?

• Start with the keys your reports depend on

Which references matter most in banking data?

The references that matter most in banking data are the ones every report and risk figure joins through: postings to accounts, card authorisations to cards and merchants, payments to counterparties, loans to customers and collateral, FX rates to currency codes, and AML alerts to accounts and customers. If any of these fails to join, the record disappears from the result.

For the general definition, see the pillar post Can Your Data Still Find Its Parents? Understanding Referential Integrity. In a bank, the list is fairly stable:

• Postings → accounts. Balances, interest and fees are all computed through this join.

• Card authorisations → cards and merchants. The card leads to the account and customer; the merchant carries the category code.

• Payments → counterparties. The counterparty record is used for screening, statistics and exposure.

• Loans → customers and collateral. Borrower and collateral both feed risk and provisioning.

• FX rates → currency codes. Rates and foreign-currency amounts reference a currency table, typically keyed by ISO 4217 codes.

• AML alerts → accounts and customers. Without them, nobody can work the alert.

Composite keys

Many banking keys are not a single column. A multi-currency account is often identified by account + currency; a syndicated or drawn-down loan by contract + tranche. Checking only account_id would pass a EUR posting against an account that only exists in USD. The check has to be on the combination, in the same column order on both sides.

Cross-system references

The most fragile references cross system boundaries. The customer master lives in the core banking system, transactions in the core banking data warehouse, exposures in a risk system with its own counterparty table. Each copy is consistent with itself. The question is whether they agree.

What happens when a banking reference breaks?

When a banking reference breaks, the child record does not fail loudly. It drops out of joins, so balances, exposures, report populations and alert queues are computed on fewer records than exist. The consequence depends on which relationship broke, and the table below states it plainly for the common ones.

None of these produce an error in the ETL log. The job succeeded; the join was just smaller.

Why do orphan records appear in banking systems?

Orphan records appear in banking systems mainly because master data and transactions arrive on different schedules and through different translations. A transaction can reach the warehouse before its account, a migration can rewrite one side of a key, and two systems can format the same identifier differently. Each cause is ordinary; together they make orphans routine.

• Late-arriving master data. Transactions load intraday, the account master once a night. A customer onboarded at 10:00 and transacting at 10:05 is an orphan until the next master load.

• Mergers and migrations. When a portfolio moves from an acquired bank or old core, customer and contract numbers are remapped. Rows that miss the mapping keep old keys.

• Product launches. A new card product, account type or currency goes live in the core before the warehouse reference tables know about it.

• Key format differences. One system pads account numbers with leading zeros, another stores them as integers; one uses IBAN, another an internal ID; one stores counterparty IDs in upper case. The values mean the same thing and still do not join.

• Constraints that are not enforced. The core database may enforce its foreign keys, but warehouse platforms often treat declared keys as informational. Snowflake, for example, documents foreign keys on standard tables as not enforced. Nothing stops an orphan from loading.

Supervisors expect banks to aggregate risk data completely and accurately, as set out in the Basel Committee's BCBS 239 principles, and orphaned exposures work directly against that. For the wider organisational side of this, see data management in banks.

How do you find orphan postings with SQL?

You find orphan postings with an anti-join: select postings whose account_id is not null and has no matching row in accounts. Comparing against the distinct set of account IDs keeps the result correct even if the accounts table has duplicates, and excluding nulls separates missing references from wrong ones.

-- Postings whose account does not exist in the account master
SELECT p.*
FROM postings p
LEFT JOIN (SELECT DISTINCT account_id FROM accounts) a
  ON p.account_id = a.account_id
WHERE p.account_id IS NOT NULL
  AND a.account_id IS NULL

-- Postings whose account does not exist in the account master
SELECT p.*
FROM postings p
LEFT JOIN (SELECT DISTINCT account_id FROM accounts) a
  ON p.account_id = a.account_id
WHERE p.account_id IS NOT NULL
  AND a.account_id IS NULL

-- Postings whose account does not exist in the account master
SELECT p.*
FROM postings p
LEFT JOIN (SELECT DISTINCT account_id FROM accounts) a
  ON p.account_id = a.account_id
WHERE p.account_id IS NOT NULL
  AND a.account_id IS NULL

For a composite key such as account + currency, the join simply carries both columns:

SELECT p.*
FROM postings p
LEFT JOIN (SELECT DISTINCT account_id, currency FROM accounts) a
  ON p.account_id = a.account_id
 AND p.currency   = a.currency
WHERE p.account_id IS NOT NULL
  AND p.currency   IS NOT NULL
  AND a.account_id IS NULL

SELECT p.*
FROM postings p
LEFT JOIN (SELECT DISTINCT account_id, currency FROM accounts) a
  ON p.account_id = a.account_id
 AND p.currency   = a.currency
WHERE p.account_id IS NOT NULL
  AND p.currency   IS NOT NULL
  AND a.account_id IS NULL

SELECT p.*
FROM postings p
LEFT JOIN (SELECT DISTINCT account_id, currency FROM accounts) a
  ON p.account_id = a.account_id
 AND p.currency   = a.currency
WHERE p.account_id IS NOT NULL
  AND p.currency   IS NOT NULL
  AND a.account_id IS NULL

That works for one relationship in one database. A bank has dozens of relationships across several databases and needs the result every day. Maintaining these queries by hand is the part that tends to lapse.

How do you set up a referential integrity check in digna?

In digna you set up a referential integrity check by adding a rule of type Referential Integrity to the child data source, picking its key columns, and naming the data source and columns they must exist in. There is no SQL to write. digna generates the check, runs it inside your database with every inspection, and reports passing and failing counts.

For postings.account_id → accounts.account_id, the steps in digna Data Validation are:

1. Go to Configuration, select the postings data source, open the Data Validation tab and click Add Rule. The Add Data Validation Rule dialog opens.

2. Enter a Name and Description, for example posting_account_exists, "Every posting belongs to an account in the account master".

3. Set Type to Referential Integrity.

4. Under Attributes, pick account_id (add currency as well for an account + currency key).

5. Under must exist in, pick the Data Source accounts and its Attributes account_id, in the same order as on the left.

6. Choose the Threshold Mode and set the Info threshold and Warn threshold.

7. Save. The rule runs with every scheduled or on-demand inspection of postings.

The screenshot below comes from digna's hospital demo rather than a bank, but the dialog is identical for a banking table: replace product_code and hospital_medications with account_id and accounts.

The Referential Integrity rule dialog in digna. Demo data from Danubia Kliniken, a fictional Austrian hospital group.

digna keeps the distinct values from the parent data source and fails any child row that does not join. The two column lists must be the same length; a mismatch is rejected rather than silently checking a weaker condition. Null foreign keys are skipped, so a payment with no counterparty ID does not fail this rule. If the counterparty is mandatory, add a separate Rule with counterparty_id IS NOT NULL. The full walkthrough is in how to set up a referential integrity check; the digna documentation explains how validation is evaluated.

Which threshold should regulatory data use?

Regulatory data should use zero tolerance: Absolute threshold mode, set so that a single orphan fails the check. An exposure missing from a regulatory report is a defect however many rows passed. Relative thresholds belong only on noisy feeds where a small, known share of late references is expected.

digna evaluates two levels. Above the Info threshold the status is Uncertain; above the Warn threshold it is Failed; otherwise Passed. Both thresholds default to zero, so a new rule fails on the first bad record until you decide otherwise. The demo rule above uses Absolute, Info 0, Warn 1, so a single orphan already raises an Uncertain status and two or more fail the check; for regulatory data, leave Warn at 0 so that one orphan fails.

If a relative threshold absorbs late master data, re-check the same data later, so that "late" does not quietly become permanent.

How do you check core banking against the data warehouse?

You check core banking against the data warehouse with a referential integrity rule whose two data sources sit on different database connections in the same digna project. Since Release 2026.01 that is supported directly, so the account master in the core system and postings in the warehouse are validated without replicating either table.

This catches the cross-system problems described above: key format differences, remapped customer numbers, accounts that never reached the warehouse. In digna, database connections are global and reusable across projects, and a data source can be a table, a view or a custom SQL statement. A view or SQL data source is a practical place to normalise a key format (say, stripping leading zeros) before comparing. The checks run inside your databases; your data never leaves your infrastructure. The details, including schemas and views, are in referential integrity across databases and connections.

How do failing records become audit evidence?

Failing records become audit evidence because digna returns the rows themselves, not only a count. The Invalid Records view lists every record that failed a check on a given inspection, filtered by Passed, Uncertain or Failed, and the list can be exported for an auditor, a data owner or an incident ticket.

Technically it is the same query with the pass condition negated, run inside the source database. For a postings rule you see each orphan posting with its account ID, amount, booking date and other columns, usually enough for the owning team to trace the cause. digna can also notify the team that owns the data.

Invalid Records for a failed referential integrity check, from the same hospital demo; a banking table shows its own columns in the same view.

Because results are kept per inspection date, you can show when a relationship broke and when it was fixed. That record of checks, results and failing rows is useful material when you explain your controls, though it does not by itself make a process compliant.

Start with the keys your reports depend on

You don't need every relationship on day one. Take the five or six joins your regulatory and risk reports depend on, add one Referential Integrity rule per relationship, set zero tolerance where a missing row is a missing exposure, and let the inspections run. You will soon see which feeds produce orphans, and when. If you want to see this on your own core and warehouse tables, book a demo with the digna team.

Frequently asked questions

What is referential integrity in banking data?

It means every reference in a bank's data points to a record that exists: each posting to a known account, each payment to a known counterparty, each loan to a known customer. When a reference breaks, the record silently drops out of joins, so balances, exposures and reports are computed on fewer rows than exist.

Why do orphan records appear in a core banking data warehouse?

Mostly because transactions and master data arrive on different schedules. Postings load intraday while the account master loads nightly, migrations remap customer numbers, new products go live before reference tables know them, and systems format the same key differently, for example with or without leading zeros.

How do I find postings without a matching account in SQL?

Use an anti-join: LEFT JOIN postings to the distinct account IDs from the accounts table and keep rows where the account side is NULL, excluding postings whose account_id is itself NULL. For an account plus currency key, join on both columns in the same order.

Should regulatory reporting data allow any orphan records?

No. For data that feeds regulatory or risk reports, use an Absolute threshold so a single orphan fails the check, because every missing row is a missing exposure or booking. Relative thresholds are reasonable only for noisy feeds, such as intraday card authorisations waiting for merchant master data.

Can digna check references between the core banking system and the warehouse?

Yes. Since Release 2026.01, a digna Referential Integrity rule can compare data sources on different database connections in the same project, such as the account master in the core system and postings in the warehouse. The checks run inside your databases, and no table is replicated.