Medallion Architecture in Practice: Lessons from Building a Department Analytics Domain from Scratch

The Problem

When the department team asked for analytics on headcount, sick leave, and hours worked across their Store, Warehouse, and Finance divisions, the ask sounded deceptively straightforward. What arrived, though, was anything but clean. Profit center codes came in as raw strings that needed window-function deduplication because the same key could show up twice on the same day with different metadata from late-arriving corrections. Date fields came in at least three different formats depending on the source system. Some records had a valid CORPORATE identifier and others didn't. And the Horizon department feed — which supplies KPIs across three organisational scopes — was structured as a wide pivot table that had to be unpivoted before it could be joined against anything resembling a dimensional model.

On top of the data-quality problem sat a scale problem: the domain needed to cover four international markets and three distinct reporting scopes, and it needed to do so without collapsing under schema churn every time a source system changed a field. Four months and several architectural pivots later, that domain existed as a production pipeline covering 30+ KPIs. This article covers the decisions that got it there: what the architecture looks like layer by layer, the schema challenges that came from crossing country boundaries, and the mistakes I wish we had caught earlier.

What Is Medallion Architecture

Medallion architecture organises a data pipeline into progressive layers — commonly named raw (or bronze), stage (silver), bus (also silver/gold boundary), and serve (gold) — each with a narrow, well-defined responsibility. Raw data is landed and preserved verbatim, with no interpretation applied. Stage tables take on typing, validation, and normalisation, turning source strings into typed, deduplicated values. A conformed, dimensional bus layer is where cross-entity joins and business-rule enforcement happen once, against shared dimensions rather than per-report logic. Serve tables sit closest to consumption — thin, purpose-shaped aggregates that BI tools query directly.

The point of the pattern isn't just tidiness. Each layer boundary is a contract: a schema change or a data-quality issue at the source shows up first in raw and stage, and gets absorbed or normalised there instead of propagating directly into a report a business user is looking at. That containment is what makes the pattern worth the extra tables and the extra hops.

The Alternatives

Before committing to a full medallion build, three real options were on the table.

Flat ingestion straight into reporting tables. Land the raw feeds and transform them directly into the shapes BI needed, skipping intermediate layers entirely. This is the fastest path to a first dashboard, and for a single, stable source it can be a reasonable trade. But the department feeds were neither single-source nor stable — four countries, a pivoted Horizon export, and inconsistent date formats meant every upstream schema change would cascade directly into whatever query was serving the dashboard. Debugging a broken report would mean untangling raw parsing, business logic, and aggregation all in the same statement.

A single wide fact table with everything inline. Instead of a dimensional model, denormalise everything — KPI type, profit center attributes, scope — into one wide fact table per feed. This avoids join complexity entirely and would have been quicker to stand up for the first KPI. The cost shows up on the second and third KPI: every new metric duplicates dimension logic (KPI naming, profit center enrichment) inline, and there's no shared join key to compare metrics across scopes later. It optimises for week one at the expense of every week after.

Full medallion architecture with a conformed dimensional bus layer. Land raw untouched, type and normalise at stage, build shared dimensions and facts at bus, and expose thin, purpose-built aggregates at serve. Slower to deliver the first KPI — there's schema work with no dashboard to show for it — but every subsequent KPI reuses the same dimensions instead of accumulating one-off joins, and each layer boundary contains the blast radius of upstream changes.

The Decision

We chose the full medallion build. Department data carries sensitivity obligations, and having a clean raw layer that mirrors source fields verbatim before any transformation made it straightforward to audit what we received versus what we surfaced. Every enrichment decision was traceable to a specific stage or bus transformation, not buried inside a monolithic query.

The medallion pattern isn't just about data quality — it's about where you put the blast radius when something upstream changes. For department data crossing four country boundaries, that isolation was non-negotiable.

Raw Layer: Mirror, Don't Interpret

The raw layer holds exactly what arrived from the source systems — no column renames, no type coercion, no filtering. For the department domain this meant landing four source feeds: profit center metadata, store hours worked, sick leave hours, and the Horizon department pivot export. We deliberately kept the raw schema as close to the source as possible so that upstream changes were visible immediately, not masked behind a transformation.

One practical consequence of this strictness: all date columns at the raw layer are STRING. Source systems sent dates in at least three formats across different countries. Casting those at ingestion would have meant either silent data loss or a proliferation of country-specific parsing branches at the wrong abstraction level.

Stage Layer: Type, Validate, Normalise

The stage layer is where raw strings become typed values and where the domain starts to enforce its own semantics. For this domain, that meant four dedicated staging tables: sa_profit_center, sa_store_hours_worked, sa_store_sick_leave_hours, sa_store_soc, and — added later when the Horizon feed was onboarded — sa_horizon_store, sa_horizon_warehouse, sa_horizon_finance, and sa_cost_center.

We adopted a SQL-first approach for stage transformations: business logic written as spark.sql() queries against a notebook-scoped Spark session, with Python responsible only for configuration injection and orchestration. That choice wasn't just about readability — the KPI logic didn't start life in our hands. Business users had already written and validated the queries that defined each metric, and the fastest, lowest-risk path to production was to hand those queries to developers largely as-is rather than translate every one of them into the PySpark DataFrame API. A spark.sql query is also far easier to unit-test against a fixture table than a chain of DataFrame transformations — you assert on the query's output, not on the shape of an intermediate API call chain. Rewriting everything into DataFrame syntax would have added translation risk for no real performance gain, since Spark's SQL and DataFrame APIs compile to the same execution plan.

The date-parsing problem was solved with Unity Catalog functions rather than session-scoped UDFs. Registering str_to_date, str_to_date_int, and str_to_decimal as Unity Catalog functions meant they were defined once, governed centrally, and available by name inside any notebook-scoped Spark session that had the right catalog permissions — no per-notebook registration step, and no risk of one notebook's session drifting out of sync with another's UDF implementation:

-- str_to_date, str_to_date_int and str_to_decimal are Unity Catalog functions,
-- resolvable by name from any notebook-scoped Spark session with USE CATALOG access.
-- Usage inside stage transformation queries:

SELECT
    profit_center_id,
    str_to_date(raw_date_col)          AS event_dt,
    str_to_date_int(raw_date_col)      AS event_ymd,
    str_to_decimal(raw_amount_col)     AS hours_worked,
    corporate
FROM raw_dept.sa_store_hours_worked_source
WHERE str_to_date(raw_date_col) BETWEEN :start_dt AND :end_dt

Because the functions live in Unity Catalog rather than in each notebook's local session, adjusting the date-format handling was a single change in one place — every stage query across every notebook picked it up on its next run, without redeploying notebook code or re-registering anything.

Incremental loads used a delete-before-insert pattern on the partition key. For most department tables this partition was (ymd, corporate). Each pipeline run deleted the target partition and reloaded it from the freshly-transformed stage data. This gave us idempotency without the overhead of a full table rewrite and without the complexity of CDC-based merges on data that was already arriving in daily snapshots.

One subtle correctness issue we hit early: the sa_profit_center table required window-function deduplication because the raw feed could deliver the same profit center with different metadata on the same day (late-arriving corrections). We solved this with a ROW_NUMBER() OVER (PARTITION BY profit_center_id, ymd ORDER BY ingestion_ts DESC) filter at the stage level — keeping only the most recent record per key before the data reached the bus layer.

Bus Layer: Dimensional Model

The bus layer is where the dimensional model lives, and we built it before a single KPI existed. That ordering was deliberate: every metric that followed would reuse the same conformed dimensions — employee, cost centre, org hierarchy, date — instead of accumulating one-off joins per report. It felt slow in week one, when there was schema but no dashboard to show for it. By week eight, once the tenth KPI reused dim_profit_center and dim_kpi_type without a single new join, the upfront cost had clearly paid for itself.

For the initial department domain we built three core objects:

• dim_kpi_type — a static reference dimension seeded from a constants file, mapping each KPI type to a surrogate key and, after the Horizon extension, to a reporting_scope_nm that distinguished Store, Warehouse, and Finance scopes.
• dim_profit_center — a slowly-changing dimension enriched from an external SAP source. Seventeen columns wide, with surrogate keys generated by hashing a composite of profit_center_id, corporate, and ymd.
• fact_hr_kpi — the central fact table, 45 columns, partitioned by (ymd, corporate, kpi_type_sk).

The partition on kpi_type_sk deserves explanation. We had four initial KPI types (Store Hours Worked, Sick Leave Hours, Span of Control, Paid Overtime Hours) and knew the Horizon extension would add more. By including kpi_type_sk in the partition key, each pipeline run could reload a single KPI type's data for a given day without touching other partitions — making selective reruns cheap and safe.

When the Horizon department feed arrived, it introduced a structural challenge: the source data was a wide pivot table where each KPI was a separate column. We needed to unpivot three separate Horizon sources (Store, Warehouse, Finance), union the results, and then join against the dimension tables — but we wanted the expensive dimension enrichment to happen only once, not three times. The solution was to separate the unpivot logic from the enrichment join:

def get_query_unpivot_horizon_store(cfg: HRConfig) -> str:
    """Unpivot Horizon Store wide table into long KPI format."""
    return f"""
    SELECT
        cost_center_id,
        ymd,
        corporate,
        '{KPI_HORIZON_HEADCOUNT_STORE}'   AS kpi_type_nm,
        '{SCOPE_STORE}'                  AS reporting_scope_nm,
        headcount_val                    AS kpi_value
    FROM {cfg.stage_schema}.sa_horizon_store
    UNPIVOT (headcount_val FOR kpi_col IN (
        headcount_fte,
        headcount_part_time,
        headcount_full_time
    ))
    """

def get_query_enrich_horizon_kpis(unpivoted_df, dim_cost_center_df, dim_kpi_type_df):
    """
    Apply dimension enrichment once after union of all three Horizon sources.
    Avoids triple join cost when processing Store + Warehouse + Finance together.
    """
    return (
        unpivoted_df
        .join(dim_cost_center_df, on=["cost_center_id", "corporate"], how="left")
        .join(dim_kpi_type_df,    on=["kpi_type_nm", "reporting_scope_nm"], how="left")
        .select(
            "ymd", "corporate", "cost_center_sk", "kpi_type_sk",
            "kpi_value", "reporting_scope_nm"
        )
    )

# In the orchestration notebook:
store_df      = spark.sql(get_query_unpivot_horizon_store(cfg))
warehouse_df  = spark.sql(get_query_unpivot_horizon_warehouse(cfg))
finance_df    = spark.sql(get_query_unpivot_horizon_finance(cfg))

horizon_long_df = store_df.union(warehouse_df).union(finance_df)
enriched_df   = get_query_enrich_horizon_kpis(
    horizon_long_df, dim_cost_center_df, dim_kpi_type_df
)

This pattern — unpivot separately, enrich once — cut the bus-layer runtime for the Horizon segment by roughly 60% compared to our first prototype, which naively joined dimensions inside each of the three unpivot queries.

Serve Layer: Thin and Purpose-Built

Serve tables are materialised aggregations shaped for the BI consumers. We kept them intentionally thin: no business logic lives at this layer, only aggregation granularity decisions. For the initial department domain that meant two serve tables — fact_month_store_agg (monthly store-level aggregation of hours worked, sick leave, and overtime) and fact_month_region_agg (monthly region-level rollup of the 16 Span of Control headcount metrics used by leadership).

The Horizon extension added three more: fact_month_horizon_store, fact_month_horizon_warehouse, and fact_month_horizon_finance, one per reporting scope. Keeping scopes as separate tables rather than a single table with a scope discriminator was a deliberate choice — it let BI developers apply role-level security at the table boundary rather than relying on row filters, which are easier to misconfigure.

Cross-Country KPI Challenges

Extending the domain from a single market to four introduced problems that don't show up in single-country prototypes, and they became part of the decision record for how the layers should be built going forward.

The Corporate Column Problem

Early in the project, CORPORATE existed in the raw and stage layers but had been omitted from several bus and serve tables on the assumption that the domain would remain single-country. When multi-country requirements arrived, we had to retrofit the column through every layer — bus fact tables, bus aggregation tables, serve tables, Alembic migrations for each — and update every transformation step to propagate it. The column was added as nullable to keep the migration backward-compatible, but the lesson was clear: design for multi-country from day one, even if you're only running one country on day one. Adding a column through six tables and their migrations after the fact is expensive and error-prone.

Filtering Logic Belongs in the Right Layer

We initially applied warehouse-specific filters (isolating Distribution Centre facilities) inside the stage transformation. That worked fine until a downstream consumer needed access to non-DC warehouse records for a different KPI. The filter in the stage layer had thrown away data that another use case needed.

The fix was to move the filter to the bus layer, applied only to the specific transformation steps that needed it, using a boolean column condition:

-- Bus layer: outbound KPI transformation
-- FLAG_IS_DC is populated at stage, but the filter lives here
SELECT
    f.facility_id,
    f.tc_company_id,
    f.cluster_id,
    f.corporate,
    k.kpi_value
FROM stage_hr.sa_horizon_warehouse f
JOIN bus_hr.dim_cost_center d
    ON f.cost_center_id = d.cost_center_id
   AND f.corporate      = d.corporate
   AND f.FLAG_IS_DC IS TRUE   -- DC-only filter, applied at bus, not stage
JOIN bus_hr.dim_kpi_type k
    ON k.reporting_scope_nm = 'Warehouse'

The general principle: filter as late as the business rule allows. If a filter is a reporting rule ("only DC facilities appear in outbound KPIs"), it belongs in the bus layer alongside the other business rules. If it's a data quality rule ("records without a valid date are invalid"), it belongs in the stage layer.

Surrogate Keys Across Corporate Boundaries

Profit centers are reused across different corporate entities — the same numeric code means different things in different countries. Our first version of dim_profit_center generated surrogate keys by hashing only the profit center identifier, which produced collisions the moment we loaded a second country. The fix was to include corporate in the hash input: SHA2(CONCAT(profit_center_id, '|', corporate, '|', ymd), 256). We also extended the merge key in dim_profit_center to include TC_COMPANY_ID alongside the existing FACILITY_ID, CLUSTER_ID, and CORPORATE columns — adding that fourth column to the merge condition eliminated duplicate facility records that had been silently accumulating since the second country was loaded.

Dimensional Modelling Choices

Static vs. Dynamic Dimensions

dim_kpi_type is a static dimension — its rows are seeded from a constants file (hr/constants.py) at pipeline bootstrap and never updated by incremental runs. This made it trivially testable: a unit test can assert the full expected row set without touching any external system. It also means that adding a new KPI type is a two-line code change (add the constant string, add the seed row), not a data operation.

dim_profit_center, by contrast, is dynamic — enriched at runtime from a live SAP source via a left join at the stage-to-bus boundary. We debated whether to materialise the SAP enrichment into its own stage table or apply it inline at the bus join. We chose inline enrichment because the SAP data changes rarely and the join is cheap. If the SAP source started changing frequently or at high volume, materialising it as a stage table would be the right call.

Centralising Magic Strings

One of the most disruptive early bugs was a KPI type name mismatch. The string "Store Hours Worked" was used as a join key between the fact table and dim_kpi_type, but one transformation file had it as "StoreHoursWorked" (no spaces). The join silently produced NULLs instead of raising an error, and the discrepancy survived two code reviews before surfacing in QA.

The solution was a centralised hr/constants.py module holding every KPI type name string and every reporting scope name as typed Python constants. All transformation files import from this module — no string literals appear in business logic. A typo in a constant is a lint or test failure; a typo scattered across twelve files is a production incident.

# hr/constants.py
# KPI type name constants — single source of truth for all transformation modules
KPI_STORE_HOURS_WORKED    = "Store Hours Worked"
KPI_SICK_LEAVE_HOURS      = "Sick Leave Hours"
KPI_SPAN_OF_CONTROL       = "Span of Control"
KPI_PAID_OVERTIME_HOURS   = "Paid Overtime Hours"

KPI_HORIZON_HEADCOUNT_STORE     = "Horizon Headcount Store"
KPI_HORIZON_HEADCOUNT_WAREHOUSE = "Horizon Headcount Warehouse"
KPI_HORIZON_HEADCOUNT_FINANCE   = "Horizon Headcount Finance"

# Reporting scope names — used in dim_kpi_type.reporting_scope_nm
SCOPE_STORE     = "Store"
SCOPE_WAREHOUSE = "Warehouse"
SCOPE_FINANCE   = "Finance"

Schema Evolution Is Cheaper Upfront

Every column we omitted from early table designs because it "wasn't needed yet" came back as a migration cost later. CORPORATE was the most painful example, but reporting_scope_nm on dim_kpi_type was another — the column didn't exist when the dimension was first created, so adding it required an Alembic migration, a transformation update, and a full reload of the dimension. Had we anticipated the three-scope Horizon structure from the start, that would have been a one-time design decision, not a retrofit across four files.

Test Dimensional Seeds, Not Just Transformations

We invested heavily in unit tests for the transformation SQL but initially skipped tests for the static dimension seeds. The KPI type name mismatch described above exposed this gap. Static dimensions that serve as join keys are load-bearing — a silent discrepancy between what the seed populates and what the fact table produces is worse than a transformation bug because it produces NULLs rather than errors. Adding fixture-based tests that assert the full seed row set for dim_kpi_type caught two more similar issues before they reached staging.

Reporting Scope as a First-Class Concept

We initially modelled Store, Warehouse, and Finance KPIs as three entirely separate fact tables with no shared schema. When a cross-scope report was requested — comparing headcount trends between Store and Finance — we had no shared key to join them on. Introducing reporting_scope_nm as a column on dim_kpi_type, and ensuring it was present in all three serve-layer fact tables, gave us a natural join axis without collapsing the scope-level table isolation that the security model depended on.

The Outcome

The domain that shipped covers 30+ KPIs across four international markets and three reporting scopes — Store, Warehouse, and Finance — all running inside a single Databricks-based medallion pipeline. The layer boundaries did what they were built to do: when the Horizon feed arrived mid-project with a wide-pivot structure nobody had designed for on day one, it slotted in as new stage tables and a bus-layer unpivot-then-enrich step, without touching the existing Store or Warehouse transformations. The unpivot-once, enrich-once restructuring alone cut bus-layer runtime for the Horizon segment by roughly 60% compared to the first prototype, which had joined dimensions inside each of the three unpivot queries separately.

Not everything was right from the start. The schema discipline around multi-country support was not — CORPORATE had to be retrofitted through six tables and their migrations after the fact, and reporting_scope_nm was added to dim_kpi_type as an afterthought rather than a day-one design decision. Both retrofits worked, because the layer boundaries meant each fix touched a bounded set of tables rather than cascading through the whole pipeline, but both were more expensive than they needed to be. That gap is now the concrete argument for the next domain built this way: model for the full target scope — every country, every reporting axis expected eventually — before the first KPI ships, not alongside it. The medallion architecture absorbed every one of these mistakes without a production incident; it just made the cost of the mistake visible as a migration, instead of hidden as a query that quietly returned wrong numbers.