When translating a Logical Data Model (LDM) into a Physical Data Model (PDM), the most common approach is the 1:1 mapping:
- Every entity in the LDM becomes a table in the PDM.
- Every subtype of a supertype (
PRODUCT_A,PRODUCT_B,AGREEMENT_XYZ) gets its own table.
It’s simple and automatic — but also wasteful.
- Too many tables clutter the PDM.
- Subtypes that belong together end up scattered.
- Historization becomes messy and imprecise.
There’s a better way.
One Table per Hierarchy
Instead of exploding the hierarchy, we consolidate it:
- Supertype → one physical table.
- Subtype → a discriminator column.
- Subtype access → views (so logical consumers still see the familiar entities).
Example:
CREATE TABLE party_core (
party_sk BIGINT PRIMARY KEY,
party_bk STRING NOT NULL,
party_type STRING NOT NULL, -- discriminator: PERSON, ORG
-- common attributes
name_full STRING,
country_code STRING,
-- subtype: person
person_birthdate DATE,
person_gender_code STRING,
-- subtype: organization
org_legal_name STRING,
org_chamber_id STRING,
-- historization
valid_from_ts TIMESTAMP,
valid_to_ts TIMESTAMP,
is_current BOOLEAN
);
Subtype view (only relevant columns exposed):
CREATE VIEW v_person_core AS
SELECT
party_sk,
party_bk,
name_full,
country_code,
person_birthdate,
person_gender_code,
valid_from_ts,
valid_to_ts,
is_current
FROM party_core
WHERE party_type = 'PERSON';
This keeps the PDM lean, while preserving subtype semantics.
Why This Works Today
- Cloud platforms compress nulls well — wide, sparse rows are no longer expensive.
- Volatile attributes (like gender, marital status, product prices) belong in Characteristic entities, so the core table stays stable.
- Views keep queries readable, without duplicating storage.
IDS Decomposition: From Wide to Right-Sized
Most Integrated Data Sources (IDS) don’t align neatly with our logical model.
- One IDS table may combine many concepts.
- Our PDM expects separation into core, characteristic, and reference tables.
Solution: split wide IDS tables into multiple right-sized PDM tables.
The benefit?
- Historization works at the right level.
- A small change (say, marital status) only creates a new row in its Characteristic table, not in the entire Party record.
- Version explosion is avoided.
Historization at the Right Granularity
In a naïve wide historized table, every attribute change forces a full new row.
With decomposition:
- Each characteristic entity versions independently.
- Common identifiers stay stable in the core.
This gives you:
- Less storage churn
- Clear lineage
- Accurate timelines
Wrapping Up
By consolidating supertypes and subtypes into one table per hierarchy, and decomposing IDS sources into core + characteristics, you get:
- A simpler PDM with fewer tables
- Cleaner historization at the right level of change
- Closer alignment to the logical business model
This approach keeps both the data team and the business happy:
- The data team avoids sprawl and redundancy.
- The business still sees clear concepts through views.
In Part 2, we’ll look at how to implement this pattern with metadata-driven loading: how to map multiple IDS subtype feeds into one supertype table, using model metadata (e.g. from Erwin or PowerDesigner).