Architecting a High-Throughput Enterprise Data Warehouse: Medallion Architecture, SCD-2 & Scalable Pipelines

Modern enterprise data architectures demand both the high-speed streaming ingestion of a data lake and the rigorous transactional governance, ACID consistency, and sub-second analytical performance of a dimensional data warehouse.

In this deep dive, we explore how to construct a battle-tested enterprise Data Warehouse and Lakehouse engine capable of ingesting gigabytes of continuous telemetry and transactional mutations while serving mission-critical BI dashboards and machine learning pipelines.

1. High-Level Medallion Architecture

The bedrock of modern data warehouse design is the Medallion Pattern, which separates data refinement into deterministic, auditable layers:

Layer Breakdown:

1. Bronze (Raw Ingestion Layer):

   • Ingests raw source logs, CDC stream payloads, and files exactly as-is.
   • Preserves append-only immutable histories with metadata tracking columns (_ingested_at, _source_system, _batch_id).
   • Powered by Informatica CDI, Apache Spark Auto-Loader, and Kafka topic consumers.

2. Silver (Cleaned & Conformed Layer):

   • Applies strict schema validation, type casting, deduplication, and anomaly quarantine.
   • Implements Slowly Changing Dimensions (SCD Type 2) to preserve complete entity state changes over time.
   • Enforces data quality rules (CDQ/CDGC) and maintains relational integrity.

3. Gold (Dimensional & Curated Marts):

   • Aggregated, denormalized Kimball Star Schemas (Fact and Conformed Dimension tables).
   • Optimized with columnar formats (Parquet, Delta Lake, Snowflake Micro-partitions), clustering keys, and pre-computed materializations for BI tools (PowerBI, Tableau, Looker).

2. Dimensional Star Schema & SCD Type 2 Implementation

A core challenge in enterprise data warehousing is tracking history without mutating historical analytics. If a customer changes their membership tier or home state, past sales must remain attributed to their historic state, while new sales reflect their current state.

SCD Type 2 Table Schema (SQL DDL)

Here is a production-grade DDL for an SCD Type 2 Customer Dimension:

CREATE TABLE DIM_CUSTOMER_SCD2 (
    customer_sk       BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, -- Surrogate Key
    customer_nk       VARCHAR(64) NOT NULL,                           -- Natural Business Key
    customer_name     VARCHAR(255) NOT NULL,
    email             VARCHAR(255),
    tier_level        VARCHAR(50),
    state_code        VARCHAR(10),
    
    -- Audit & Temporal Validity Fields
    valid_from        TIMESTAMP NOT NULL,
    valid_to          TIMESTAMP NOT NULL DEFAULT '9999-12-31 23:59:59',
    is_current        BOOLEAN NOT NULL DEFAULT TRUE,
    dw_created_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    dw_updated_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT uq_customer_temporal UNIQUE (customer_nk, valid_from)
);

-- Indexing for Lightning-Fast Surrogate Lookups
CREATE INDEX idx_dim_customer_lookup ON DIM_CUSTOMER_SCD2 (customer_nk, is_current);
CREATE INDEX idx_dim_customer_temporal ON DIM_CUSTOMER_SCD2 (customer_nk, valid_from, valid_to);

3. High-Performance ETL Ingestion Pattern (Merge / Upsert)

When orchestrating ETL with Informatica CDI or PySpark Structured Streaming, we use deterministic idempotent upserts with MERGE INTO operations:

-- Step 1: Expire changed existing records
MERGE INTO DIM_CUSTOMER_SCD2 AS target
USING (
    SELECT 
        src.customer_nk,
        src.customer_name,
        src.tier_level,
        src.state_code,
        CURRENT_TIMESTAMP AS effective_ts
    FROM STG_CUSTOMER_STREAM src
) AS source
ON target.customer_nk = source.customer_nk 
   AND target.is_current = TRUE
   AND (
       target.tier_level <> source.tier_level 
       OR target.state_code <> source.state_code
   )
WHEN MATCHED THEN
  UPDATE SET 
    target.valid_to = source.effective_ts,
    target.is_current = FALSE,
    target.dw_updated_at = CURRENT_TIMESTAMP;

-- Step 2: Insert new records & brand new dimension versions
INSERT INTO DIM_CUSTOMER_SCD2 (
    customer_nk, customer_name, tier_level, state_code, valid_from, is_current
)
SELECT 
    src.customer_nk,
    src.customer_name,
    src.tier_level,
    src.state_code,
    CURRENT_TIMESTAMP,
    TRUE
FROM STG_CUSTOMER_STREAM src
LEFT JOIN DIM_CUSTOMER_SCD2 cur 
    ON src.customer_nk = cur.customer_nk AND cur.is_current = TRUE
WHERE cur.customer_sk IS NULL;

4. Fact Table Grain & Fact Ingestion Best Practices

To ensure consistent performance across billions of rows:

1. Surrogate Key Joins: Fact tables join dimensions purely on integer surrogate keys (customer_sk, product_sk), avoiding slow string-based natural key comparisons.
2. Partition Pruning & Liquid Clustering: Partition fact data by date_key (e.g., 20260821 or year/month) to enable aggressive partition elimination during query planning.
3. Null-Safe Unknown Records: Never allow NULL foreign keys in facts. Point missing dimensional lookups to a surrogate key -1 (“Unknown / Unassigned”) to maintain inner-join integrity.

Conclusion & Architecture Takeaways

A resilient enterprise data warehouse isn’t built on ad-hoc scripts—it relies on deterministic layering (Bronze/Silver/Gold), auditable temporal versioning (SCD Type 2), and optimized star schema dimensions.

By orchestrating these transformations with robust tooling like Informatica CDI/CAI, Databricks Delta Lake, and dbt, data engineering teams can deliver trusted, scalable, sub-second analytics to the entire enterprise.