Inheritance the Business Way
Most business models contain supertypes and subtypes.
Think of PARTY as the supertype, with PERSON and ORGANIZATION as subtypes.
Traditional physical design gives you two choices:
- One table per entity (supertype + subtype tables, 1:1 PK–FK).
- One table per hierarchy (a single table with discriminator + nullable subtype columns).
We chose one table per hierarchy:
- A single PARTY_CORE table with all subtype columns.
- A discriminator column (
PERSON,ORG, …). - Generated views (
v_person_core,v_organization_core) that filter on the discriminator and expose only relevant columns.
Example:
CREATE TABLE party_core (
party_sk BIGINT PRIMARY KEY,
party_bk STRING NOT NULL,
party_type STRING NOT NULL, -- discriminator
-- common
name_full STRING,
country_code STRING,
-- person
person_birthdate DATE,
person_gender_code STRING,
-- organization
org_legal_name STRING,
org_chamber_id STRING,
-- SCD2
valid_from_ts TIMESTAMP,
valid_to_ts TIMESTAMP,
is_current BOOLEAN
);
CREATE VIEW v_person_core AS
SELECT * FROM party_core WHERE party_type = 'PERSON';
Why This Works Well Today
- Cloud platforms compress nulls efficiently → wide sparse rows are no longer expensive.
- Many volatile attributes (like gender or marital status) already live in Characteristic entities → the core table doesn’t get bloated.
- Subtype views keep queries clean without extra storage.
Source Subtypes: Translation Blocks
Now let’s add multiple sources. PARTY may come from Salesforce, SAP, or a legacy CRM.
We don’t want to reload raw data over and over. So we use source subtypes:
party_salesforceparty_sap
These are translation views/tables shaped exactly like party_core. Each contains only records from that source.
The supertype party_core can then be:
- A union view over source subtypes (dedup + BK→SK resolution).
- Or a persisted table if reproducibility or SLA demands it.
The Minimal Duplication Principle
- 1:1 mapped attributes → flow via views.
- Derived attributes → persisted once, historized.
- Raw data → stored once in the source layer, not copied endlessly into integration.
This keeps the integration layer lean, aligned to the business model, and reproducible.
What’s Next
In the final part, we’ll cover the glue that holds this together:
- Access Views → contracts between steps.
- Dual SCD2 historization → reproducibility guaranteed.