Financial services
File: 06-financial-services.xdbml · Target: Snowflake
A retail banking schema. Demonstrates materialized and virtual views with source queries, complex transaction polymorphism, regulatory compliance tags (PCI, SOX, KYC, AML), and AI-readiness for natural-language reporting queries.
Source
xdbml
xdbml: 0.3
Project banking {
targets: Snowflake
Note: '''
A financial services schema for a retail banking platform. Demonstrates
the View construct (both materialized and virtual), complex polymorphism
for transaction types, comprehensive validation constraints for monetary
amounts and identifiers, and AI-readiness tags emphasizing regulatory
compliance (PCI-DSS for card data, SOX for financial controls, KYC for
identity verification, AML for transaction monitoring).
The schema is intentionally simplified -- real banking systems carry
many concerns out of xDBML scope: double-entry bookkeeping invariants,
immutable transaction logs, settlement networks, regulatory reporting
pipelines, and so on. This example focuses on the shape of the data
rather than the rules that govern its evolution.
'''
}
// ============================================================
// Reusable types
// ============================================================
Type Money {
Note: 'A monetary amount in a specific currency. Used throughout banking flows.'
amount decimal(19,4) [not null,
note: 'Value in major currency units; e.g. 100.50 not 10050']
currency varchar(3) [not null,
pattern: '^[A-Z]{3}$',
default: 'USD',
business_term: 'ISO 4217 Currency Code',
note: 'Three-letter currency code per ISO 4217']
}
Type AccountIdentifier {
Note: 'Structured account identifier including routing context.'
account_number varchar [not null,
pattern: '^[0-9]{8,17}$',
tags: ['pii', 'financial-identifier', 'pci-affected'],
business_term: 'Bank Account Number',
note: 'Domestic account number; format varies by country but always numeric and 8-17 digits in this schema']
routing_number varchar [pattern: '^[0-9]{9}$',
tags: ['financial-identifier'],
business_term: 'ABA Routing Number',
note: 'US ABA routing number; null for non-US accounts which use other identifiers']
iban varchar [pattern: '^[A-Z]{2}[0-9]{2}[A-Z0-9]{11,30}$',
maxLength: 34,
tags: ['financial-identifier'],
business_term: 'International Bank Account Number',
note: 'IBAN for European accounts; null for accounts that use ABA routing']
bic varchar [pattern: '^[A-Z]{6}[A-Z0-9]{2}([A-Z0-9]{3})?$',
minLength: 8,
maxLength: 11,
tags: ['financial-identifier'],
note: 'SWIFT/BIC code for international transfers']
}
// ============================================================
// Containers
// ============================================================
Container customers [type: schema] {
Note: 'Customer master data with KYC information required by banking regulations.'
Entity customers {
Note: '''
A retail banking customer. Customer records carry KYC (Know Your
Customer) data required by anti-money-laundering regulations.
Customer records are never deleted, only marked as closed; KYC
history is retained per regulatory requirements (typically 5-7
years after account closure depending on jurisdiction).
'''
id int [pk,
increment,
note: 'Internal surrogate identifier; not exposed to customers']
customer_number varchar [unique,
not null,
pattern: '^CUST-[0-9]{10}$',
tags: ['customer-identifier'],
business_term: 'Customer Number',
note: 'Customer-facing identifier shown on statements; format: CUST- and ten digits']
legal_name varchar [not null,
maxLength: 200,
tags: ['pii', 'kyc'],
business_term: 'Legal Name',
note: 'Name as it appears on government-issued identification']
date_of_birth date [not null,
tags: ['pii', 'kyc'],
business_term: 'Date of Birth',
note: 'Required for identity verification and age-restricted product eligibility']
tax_id varchar [not null,
unique,
maxLength: 20,
tags: ['pii', 'kyc', 'high-sensitivity'],
business_term: 'Taxpayer Identification Number',
note: 'SSN in the US, equivalent national identifier elsewhere; encrypted at rest in production']
email varchar [unique,
pattern: '^[^@]+@[^@]+\.[^@]+$',
maxLength: 255,
tags: ['pii', 'contact'],
note: 'Primary email contact; verified during onboarding']
phone varchar [pattern: '^\+[0-9]{1,3}[0-9]{4,14}$',
maxLength: 20,
tags: ['pii', 'contact'],
note: 'E.164 international format; +1 country code for US numbers']
kyc_status varchar [enum: ['pending', 'verified', 'review_required', 'rejected'],
default: 'pending',
tags: ['kyc', 'compliance'],
business_term: 'KYC Verification Status',
note: 'Current KYC verification state; accounts cannot transact while pending']
kyc_completed_at timestamp [granularity: second,
tags: ['kyc', 'audit'],
note: 'When KYC verification last completed successfully; null for unverified customers']
risk_rating varchar [enum: ['low', 'medium', 'high'],
default: 'medium',
tags: ['aml', 'risk-management'],
business_term: 'AML Risk Rating',
note: 'Anti-money-laundering risk classification; drives transaction monitoring thresholds']
onboarded_at timestamp [not null,
granularity: second,
note: 'Account opening timestamp; immutable']
status varchar [enum: ['active', 'frozen', 'closed', 'deceased'],
default: 'active',
note: 'Account lifecycle status; closed accounts retain history but cannot transact']
indexes {
customer_number [unique]
tax_id [unique]
email [unique]
(kyc_status, onboarded_at) [note: 'Used by compliance reporting dashboards']
risk_rating
}
}
}
Container accounts [type: schema] {
Note: 'Account master data and transaction history. Source of truth for current balances.'
Entity accounts {
Note: 'A deposit account belonging to a customer. Customers may have multiple accounts of different types.'
id int [pk, increment]
customer_id int [not null,
note: 'References customers.customers.id; the account holder']
account_identifier AccountIdentifier [
note: 'Structured account identifier including routing context'
]
account_type varchar [not null,
enum: ['checking', 'savings', 'money_market', 'cd', 'credit'],
business_term: 'Account Type',
note: 'Determines applicable interest rates, fees, and regulatory treatment']
balance Money [note: 'Current account balance; updated atomically with every posted transaction']
available_balance Money [note: 'Available for withdrawal; differs from balance when pending holds exist']
opened_at timestamp [not null,
granularity: second,
note: 'Account opening timestamp']
closed_at timestamp [granularity: second,
note: 'Null for active accounts; set when account is closed']
status varchar [enum: ['active', 'dormant', 'frozen', 'closed'],
default: 'active',
tags: ['lifecycle'],
note: 'Account status; dormant accounts have no activity in 12+ months']
indexes {
customer_id
(customer_id, account_type)
status
}
}
Entity transactions {
Note: '''
Posted financial transactions. Transactions are append-only --
a posted transaction is never modified. Corrections are issued
as compensating transactions referencing the original.
'''
id int [pk, increment]
transaction_number varchar [unique,
not null,
pattern: '^TXN-[0-9]{16}$',
business_term: 'Transaction Reference Number',
note: 'Customer-facing transaction identifier shown on statements']
account_id int [not null, note: 'References accounts.accounts.id; the account being debited or credited']
posted_at timestamp [not null,
granularity: millisecond,
business_term: 'Posting Date',
note: 'When the transaction was committed to the ledger; immutable']
effective_date date [not null,
business_term: 'Value Date',
note: 'Business date for interest calculation; may differ from posted_at for backdated entries']
amount Money [note: 'Transaction amount; sign convention varies by transaction type']
running_balance Money [note: 'Account balance after this transaction posted; denormalized for statement generation']
transaction_type oneOf {
deposit object {
Note: 'Funds added to the account'
source varchar [enum: ['ach', 'wire', 'cash', 'check', 'internal_transfer', 'card_refund'], note: 'Source channel of the deposit']
source_account_identifier AccountIdentifier [note: 'Identifying details of the sending account; null for cash and check deposits']
check_number varchar [pattern: '^[0-9]+$', maxLength: 20, note: 'Check number for check deposits; null otherwise']
}
withdrawal object {
Note: 'Funds removed from the account'
destination varchar [enum: ['ach', 'wire', 'cash', 'check', 'internal_transfer', 'card_purchase'], note: 'Destination channel']
destination_account_identifier AccountIdentifier [note: 'Identifying details of the receiving account; null for cash withdrawals and card purchases']
merchant_name varchar [maxLength: 200, tags: ['pii-adjacent'], note: 'For card purchases, the merchant identified by the card network']
merchant_category_code varchar [pattern: '^[0-9]{4}$', note: 'ISO 18245 MCC code identifying merchant category']
}
fee object {
Note: 'A fee charged by the bank'
fee_type varchar [enum: ['overdraft', 'monthly_maintenance', 'wire_fee', 'foreign_transaction', 'atm', 'other'], note: 'Fee category']
waived boolean [default: false, note: 'True if the fee was assessed then reversed; the original entry is preserved with this flag set']
description varchar [maxLength: 500, note: 'Human-readable explanation; required when fee_type is "other"']
}
interest object {
Note: 'Interest accrual or charge'
period_start date [not null, note: 'Start of the period this interest entry covers']
period_end date [not null, note: 'End of the period this interest entry covers; usually one month']
annual_rate_pct decimal(8,5) [minimum: 0, maximum: 100, note: 'Annual interest rate applied; varies by account type and prevailing rates']
}
adjustment object {
Note: 'A correcting entry made by bank staff, e.g. dispute resolution or error correction'
reason varchar [not null, maxLength: 500, tags: ['audit'], note: 'Required justification; appears in audit logs and customer statements']
related_transaction_number varchar [pattern: '^TXN-[0-9]{16}$', note: 'Transaction this adjustment corrects, when applicable']
authorized_by varchar [not null, maxLength: 100, tags: ['audit', 'sox-controlled'], note: 'Employee ID of the authorizing staff member; subject to SOX dual-control requirements']
}
} [discriminator: type_kind,
note: 'One of five transaction shapes; type_kind discriminator carries the kind']
indexes {
(account_id, posted_at) [note: 'Most common access pattern: account statement queries']
transaction_number [unique]
posted_at [note: 'Used by daily-batch reporting jobs']
}
}
}
Container reporting [type: schema] {
Note: '''
Views over operational data optimized for reporting, dashboards,
and regulatory submissions. Refreshed daily; not authoritative
for real-time balances or transaction status.
'''
// ============================================================
// Views
// ============================================================
View customer_balances [
materialized: true,
refresh_schedule: 'daily',
refresh_on: [accounts, transactions],
source_database: 'Snowflake'
] {
Note: '''
Per-customer aggregated balances across all accounts. Used by
customer service dashboards, marketing segmentation, and the
private-banking tier-eligibility process. Refreshed nightly;
real-time balance lookups go directly to accounts.accounts.
'''
source_query: '''
SELECT
c.id AS customer_id,
c.customer_number AS customer_number,
c.risk_rating AS risk_rating,
COUNT(a.id) AS account_count,
SUM(a.balance.amount) AS total_balance,
SUM(a.available_balance.amount) AS total_available_balance,
MAX(a.opened_at) AS most_recent_account_opened_at
FROM customers.customers c
LEFT JOIN accounts.accounts a ON a.customer_id = c.id
WHERE c.status = 'active' AND (a.status IS NULL OR a.status = 'active')
GROUP BY c.id, c.customer_number, c.risk_rating
'''
customer_id int [pk,
note: 'References customers.customers.id; one row per active customer']
customer_number varchar [unique,
not null,
business_term: 'Customer Number']
risk_rating varchar [tags: ['aml'],
note: 'Copied from customer record for segmentation queries']
account_count int [minimum: 0,
note: 'Number of currently-active accounts']
total_balance decimal(19,2) [synonyms: ['total assets', 'aggregate balance'],
business_term: 'Total Customer Balance',
note: 'Sum across all active accounts in USD-equivalent']
total_available_balance decimal(19,2) [note: 'Sum of available-for-withdrawal balances; usually equals total_balance unless holds exist']
most_recent_account_opened_at timestamp [granularity: second,
note: 'Useful for new-customer segmentation']
}
View suspicious_activity_candidates [
materialized: false,
source_database: 'Snowflake'
] {
Note: '''
Virtual view identifying transactions matching simple AML
heuristics. Compliance officers use this as an initial filter,
not as authoritative SAR candidates. Real SAR (Suspicious
Activity Report) decisions involve substantially more
investigation than this view supports.
'''
source_query: '''
SELECT
t.id AS transaction_id,
t.transaction_number AS transaction_number,
t.account_id AS account_id,
t.posted_at AS posted_at,
t.amount.amount AS amount,
a.customer_id AS customer_id,
c.risk_rating AS customer_risk_rating
FROM accounts.transactions t
JOIN accounts.accounts a ON a.id = t.account_id
JOIN customers.customers c ON c.id = a.customer_id
WHERE t.amount.amount >= 10000
OR c.risk_rating = 'high'
OR (
SELECT COUNT(*) FROM accounts.transactions t2
WHERE t2.account_id = t.account_id
AND t2.posted_at > t.posted_at - INTERVAL '24 hours'
AND t2.posted_at < t.posted_at
) >= 10
'''
transaction_id int [pk]
transaction_number varchar [not null]
account_id int [not null, note: 'References accounts.accounts.id']
posted_at timestamp [granularity: millisecond]
amount decimal(19,2)
customer_id int [not null,
note: 'References customers.customers.id; identifies the customer for investigation']
customer_risk_rating varchar [tags: ['aml'],
note: 'Risk rating at the time the view was queried; not historical']
}
}
// ============================================================
// Cross-container relationships
// ============================================================
Ref: accounts.accounts.customer_id > customers.customers.id [source: '1..*', target: '1..1', delete: restrict]
Ref: accounts.transactions.account_id > accounts.accounts.id [source: '0..*', target: '1..1', delete: restrict]