Supertype and Subtype Schemas
Data models often need to handle entities that share common traits but also have highly specific details.
A common example is a retail bank. A bank offers dozens of account types, from savings accounts to mortgages and credit cards. All are accounts, but the data they track barely overlaps. A mortgage tracks the property value and remaining principal. A savings account tracks deposits and withdrawals. A credit card tracks the credit limit and minimum payment due.
The problem: the consolidated mega-fact table
The most obvious approach is creating one fact table containing every possible measure for a given reporting period, such as a daily snapshot_date.
| account_type | snapshot_date | balance | deposits_made | property_value | minimum_payment | credit_limit |
|---|---|---|---|---|---|---|
| Savings | Jan 1 | $5,000 | 3 | NULL | NULL | NULL |
| Mortgage | Jan 1 | -$300,000 | NULL | $400,000 | $1,500 | NULL |
| Credit Card | Jan 1 | -$2,000 | NULL | NULL | $50 | $20,000 |
Across dozens of products, this creates hundreds of columns. Most of these will be NULL for any given row. Users cannot tell which columns apply to which products. Also, every query needs an account_type filter so it does not combine unrelated measures.
The solution: core and custom schemas
The solution is the supertype and subtype design pattern. You split the model in two. A supertype (core) schema holds the data every entity shares. A subtype (custom) schema holds exactly what each specific entity needs.
Core schema
The core fact table only includes measures that exist for every single entity without exception.
Core Account Snapshot Fact:
| snapshot_date | account_key | account_type_key | balance |
|---|---|---|---|
| Jan 1 | 101 | Savings | $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 broad questions across the entire business, like total balances, customers holding multiple product types, and portfolio growth over time.
NOTE
A measure shared by only some products belongs in the custom schemas. For example, interest_rate applies to mortgages and credit cards, but not savings accounts, so it stays out of the core. Keeping the core narrow makes it usable. If the core starts collecting measures that are only somewhat common, it becomes the messy mega-fact table again.
Custom schemas
Each product gets its own fact table containing all the measures relevant to it, including the shared ones.
Savings Account Fact:
| snapshot_date | account_key | balance | deposits_made | withdrawals_made |
|---|---|---|---|---|
| Jan 1 | 101 | $5,000 | 3 | 1 |
Mortgage Fact:
| snapshot_date | account_key | loan_balance | property_value | minimum_payment | principal_paid | interest_rate |
|---|---|---|---|---|---|---|
| Jan 1 | 102 | -$300,000 | $400,000 | $1,500 | $100,000 | 7% |
Credit Card Fact:
| snapshot_date | account_key | balance | credit_limit | minimum_payment | cash_advance | interest_rate |
|---|---|---|---|---|---|---|
| Jan 1 | 103 | -$2,000 | $20,000 | $50 | $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 that number. Joining two fact tables together is slow and easy to mess up. The core duplicates a few common measures so you do not have to combine every single custom table to answer broad questions.
WARNING
balance is signed from the bank's perspective: deposit products positive, credit products negative. A savings balance of $5,000 is money the bank holds; a mortgage balance of -$300,000 is money owed to the bank. This is what makes SUM(balance) across products meaningful - it returns net customer position.
Custom schemas can also have their own dimensions. For example, a mortgage table might link to a property dimension that no other product uses.
Navigating between them
The same account_key appears in both schemas, so every custom fact table joins directly to the core account dimension. This means a mortgage report can group by customer name or filter on account status without needing to duplicate those details.
The account_type_key is the path going the other way. A user looking at a core-level total can see which product types make up that total. This tells them which custom schema they need to open for more details.