Why modeling patterns still matter for performance and clarity.
Summary
In Part 1, we saw why consolidating supertypes and subtypes in the PDM makes sense: fewer tables, clearer lineage, and historization at the right granularity. In this part, we’ll get practical and see how to load IDS subtypes into one supertype table — using metadata-driven patterns.
Why Metadata-Driven Loading?
Hard-coding pipelines for every subtype doesn’t scale. Business models evolve, new systems get integrated, and suddenly you’re maintaining dozens of nearly identical loaders.
The better way:
- Export metadata from your modeling tool (Erwin, PowerDesigner, …).
- Use it to drive the loading logic automatically.
- Discriminator values (PERSON, ORG, …) can come either from the IDS table itself or from metadata.
This keeps pipelines lean, consistent, and adaptable.
Case 1 — Discriminator Comes from IDS
Sometimes the source table already tells you what subtype a record belongs to.
INSERT INTO party_core (
party_sk, party_bk, party_type, name_full, country_code,
person_birthdate, org_legal_name, valid_from_ts, valid_to_ts, is_current
)
SELECT
NEXTVAL('seq_party_sk') AS party_sk,
s.party_bk,
s.party_type, -- comes directly from IDS column
s.full_name AS name_full,
s.country AS country_code,
s.birth_dt AS person_birthdate,
NULL AS org_legal_name,
s.valid_from,
s.valid_to,
s.is_current
FROM ids_party_person s
WHERE s.party_type = 'PERSON';
Case 2 — Discriminator from Metadata
Other times, the IDS table doesn’t carry a subtype marker. In that case, your interface model injects the value.
INSERT INTO party_core (
party_sk, party_bk, party_type, name_full, country_code,
person_birthdate, org_legal_name, valid_from_ts, valid_to_ts, is_current
)
SELECT
NEXTVAL('seq_party_sk'),
s.party_bk,
'PERSON' AS party_type, -- injected by metadata
s.full_name AS name_full,
s.country AS country_code,
s.birth_dt AS person_birthdate,
NULL AS org_legal_name,
s.valid_from,
s.valid_to,
s.is_current
FROM ids_party_person s;
For the ORGANIZATION feed, you’d repeat with 'ORG' and its specific attributes (like org_legal_name).
UNION ALL Loader
In some platforms (Snowflake, dbt, Spark SQL), you may prefer a single INSERT ... SELECT that combines all subtype feeds with UNION ALL:
INSERT INTO party_core (party_sk, party_bk, party_type, name_full, country_code,
person_birthdate, org_legal_name, valid_from_ts, valid_to_ts, is_current)
SELECT
NEXTVAL('seq_party_sk'), party_bk, 'PERSON', name_full, country_code,
person_birthdate, NULL, valid_from_ts, valid_to_ts, is_current
FROM ids_party_person
UNION ALL
SELECT
NEXTVAL('seq_party_sk'), party_bk, 'ORG', name_full, country_code,
NULL, org_legal_name, valid_from_ts, valid_to_ts, is_current
FROM ids_party_org;
Metadata-Driven Code Generation
The real power comes from letting metadata generate the loaders for you.
Here’s a minimal Python example that builds INSERT ... SELECT statements from a JSON export of your model:
subtype_mappings = [
{
"source_table": "ids_party_person",
"discriminator_value": "PERSON",
"column_map": {
"name_full": "full_name",
"country_code": "country",
"person_birthdate": "birth_dt",
"valid_from_ts": "valid_from",
"valid_to_ts": "valid_to",
"is_current": "is_current"
}
},
{
"source_table": "ids_party_org",
"discriminator_value": "ORG",
"column_map": {
"name_full": "display_name",
"country_code": "country",
"org_legal_name": "legal_name",
"valid_from_ts": "valid_from",
"valid_to_ts": "valid_to",
"is_current": "is_current"
}
}
]
TARGET_COLS = [
"party_sk", "party_bk", "party_type", "name_full", "country_code",
"person_birthdate", "org_legal_name", "valid_from_ts", "valid_to_ts", "is_current"
]
def build_insert(mapping):
selects = [
"NEXTVAL('seq_party_sk') AS party_sk",
"party_bk",
f"'{mapping['discriminator_value']}' AS party_type"
]
for col in TARGET_COLS[3:]:
src = mapping["column_map"].get(col)
selects.append(f"{src} AS {col}" if src else f"NULL AS {col}")
select_sql = ",\n ".join(selects)
return f"""INSERT INTO party_core ({", ".join(TARGET_COLS)})
SELECT
{select_sql}
FROM {mapping['source_table']};"""
for m in subtype_mappings:
print(build_insert(m))
This script generates the exact SQL inserts needed for each subtype feed. Add a new subtype? Just extend the metadata — no pipeline code changes.
Historization
Once subtype rows land in party_core, apply your standard SCD2 historization (via MERGE or a dedicated historization layer). The important part is:
- Each record already carries its discriminator.
- Timelines stay consistent across subtypes.
Wrapping Up
With this approach you:
- Unify subtypes into one clean supertype table.
- Load automatically using metadata from your model.
- Keep pipelines future-proof — new subtypes are just metadata entries, not new pipeline code.
Combined with the concepts from Part 1, this gives you both the why and the how of building a business-shaped integration layer.