Supertype and Subtype Schemas
Financial services and similar businesses offer many products across disparate lines of business. A retail bank may hold dozens of account types, from checking accounts to mortgages to credit cards.
All are accounts, but their measures barely overlap. A mortgage tracks interest rate, property value and principal remaining. A checking account tracks overdrafts and ATM withdrawals. A credit card tracks credit limit and minimum payment due.
The problem: the consolidated mega-fact table
The obvious response is one fact table holding the union of every product's measures.
| account_type | date_key | balance | overdraft | property_value | interest_rate | credit_limit |
|---|---|---|---|---|---|---|
| Checking | Jan 1 | $5,000 | $0 | NULL | NULL | NULL |
| Mortgage | Jan 1 | $300,000 | NULL | $400,000 | 7% | NULL |
| Credit Card | Jan 1 | $2,000 | NULL | NULL | 18% | $20,000 |
Across dozens of products this produces hundreds of columns, almost all NULL for any given row. Users can't tell which columns apply to which products, and every query needs an account_type filter to avoid combining measures that have nothing to do with each other.
The solution: core and custom schemas
Split the model in two. A supertype (core) schema holds what every product shares. A subtype (custom) schema per product holds everything that product needs.
Core schema
The core fact table carries only the strict intersection - measures that exist for every account type, without exception.
Core Account Snapshot Fact:
| date_key | account_key | account_type_key | balance |
|---|---|---|---|
| Jan 1 | 101 | Checking | $5,000 |
| Jan 1 | 102 | Mortgage | $300,000 |
| Jan 1 | 103 | Credit Card | $2,000 |
Core Account Dimension:
| account_key | customer_name | open_date | status |
|---|---|---|---|
| 101 | John Doe | Jan 2025 | Active |
| 102 | Mary Smith | Feb 2025 | Active |
| 103 | John Doe | Mar 2025 | Active |
This answers the cross-product questions: total balances by line of business, customers holding more than one product type, portfolio growth over time.
NOTE
interest_rate applies to both mortgages and credit cards but not to checking accounts, so it stays out of the core. A measure shared by some subtypes belongs in those subtypes. Keeping the core narrow is what keeps it usable - a core that accumulates near-common measures drifts back toward the mega-fact table.
Custom schemas
Each product gets its own fact table containing all measures relevant to it, including the common ones.
Mortgage Fact:
| date_key | account_key | loan_balance | interest_rate | property_value | principal_paid |
|---|---|---|---|---|---|
| Jan 1 | 102 | $300,000 | 7% | $400,000 | $100,000 |
Checking Account Fact:
| date_key | account_key | balance | overdraft_count | atm_withdrawals |
|---|---|---|---|---|
| Jan 1 | 101 | $5,000 | 0 | 3 |
Repeating balance in the custom tables is deliberate. It means a mortgage report never has to join back to the core fact table to get it - a fact-to-fact join, which is both slow and easy to get wrong. The core duplicates a small subset of measures so that cross-product questions don't require unioning every custom table.
Custom schemas can also have their own dimensions. A mortgage fact might reference a property dimension that no other product needs.
Navigating between them
The same account_key appears in both schemas, so every custom fact table joins directly to the core account dimension. A mortgage-specific report can still group by customer name or filter on account status without duplicating those attributes.
account_type_key is the path in the other direction. A user looking at a core-level total can see which product types make it up, and that tells them which custom schema to open for detail.