Skip to content

Library · Data Practice OS

Medallion layers and modelling

Reference guide · Published · Source reviewed

The answer: three layers with clear jobs, one naming convention across both platforms, and dimensional models in gold. The layers are a refinement convention, not a rule that every table goes through three hops.

The layers

Layer Holds Rules Who reads it
Bronze Source data as landed: Change data capture streams, files, API payloads Append-only, source schema, load metadata (_loaded_at, _source, _batch_id). No business logic Platform and domain engineers only
Silver Cleaned, typed, deduplicated, conformed entities: a registration, a licence, a road segment One row per real-world thing (or version of it). Keys standardised. Personal information tagged Domain engineers, data scientists
Gold Business-ready Data products: facts, dimensions, aggregates, features Has a Data contract, tests, an owner and an SLA. Stable names Everyone: BI, the Semantic layer, AI agents, shares

Two common mistakes: putting business rules in bronze ("just a quick filter"), and letting reports read silver because gold isn't ready. Both create invisible logic that nobody owns.

Naming

Use the same three-part shape on both platforms. Snowflake: database.schema.object. Unity Catalog: catalog.schema.object.

<env>_<domain>.<layer>.<object>
prod_registrations.gold.fct_registration_event
prod_road_assets.silver.road_segment
dev_alice_registrations.gold.fct_registration_event      -- per-developer sandbox
Prefix Meaning
stg_ Staging: one-to-one with a bronze source, renamed and typed (dbt convention)
int_ Intermediate: reusable joins and logic, not published
fct_ / dim_ Gold facts and dimensions
agg_ Gold pre-aggregations for BI performance
v_ Published views: shares, contracts, versioned interfaces

Modelling gold

  • Dimensional by default. Facts at a declared grain (fct_registration_event: one row per registration transaction) and conformed dimensions (dim_vehicle, dim_customer, dim_location, dim_date). BI tools, the semantic layer and text-to-SQL all work best on star schemas with obvious joins.
  • SCD type 2 for things that change and matter historically. Vehicle ownership, licence class and road segment attributes need valid_from / valid_to / is_current. Use dbt snapshots, or build from CDC.
  • Wide, flat tables for ML features and agents. Data scientists and LLM tools prefer one row per entity with descriptive column names. Build these as gold too, derived from the star.
  • Data vault only where it pays. If you integrate many overlapping source systems with heavy auditability needs, a raw vault in silver can help. Most the organisation domains don't need it.

Platform mapping

Need Snowflake Databricks
Incremental bronze to silver Dynamic Tables, or dbt incremental Lakeflow streaming tables, or dbt incremental
Scheduled gold builds dbt + tasks, or dynamic tables with target lag dbt + Lakeflow Jobs, or materialised views
CDC apply (merge) MERGE, or dynamic tables over a CDC stream AUTO CDC in Lakeflow pipelines (replaces APPLY CHANGES, same syntax; AUTO CDC APIs)

Keep the logic in dbt where you can, so it stays portable (ADR-003: dbt as the transformation standard). Platform-native features are fine for ingestion and incremental plumbing.

Worked example: a silver entity with SCD2

-- models/silver/registrations/stg_registration_vehicle.sql (dbt)
select
    registration_id,
    upper(trim(plate_number))            as plate_number,
    vin,
    vehicle_class_code,
    garaging_postcode,
    cast(effective_ts as timestamp)      as effective_ts,
    _loaded_at
from {{ source('regis_bronze', 'registration_vehicle') }}
qualify row_number() over (
    partition by registration_id, effective_ts order by _loaded_at desc
) = 1

# snapshots/registration_vehicle_snapshot.yml
snapshots:
  - name: registration_vehicle_history
    relation: ref('stg_registration_vehicle')
    config:
      unique_key: registration_id
      strategy: timestamp
      updated_at: effective_ts

Pitfalls

  • Layer as a database per layer per domain per environment. That's an explosion of objects. Layers are schemas inside a domain database/catalog.
  • Gold that mirrors the source system. A gold table named after the legacy mainframe file isn't a product. Model the business concept.
  • No grain statement. Every fact table's description starts with "One row per…". Text-to-SQL depends on it (The semantic layer).

Lessons from platform options papers

Source: a platform team's draft options papers and platform docs (Oct-Nov 2026). All draft or proposed. None is an agreed decision; treat them as evidence, not standards.

  • Layer names. the platform team keeps RAW, SRC, STG, INT and DW (with optional SCD) and maps them to bronze, silver and gold. The mapping and model-name patterns are in Naming conventions.
  • Load raw into tables, do not query staged files. Raw tables give header checks, quarantine, incremental history, Time Travel and load metadata, so a file never loads twice. Snowflake states that querying staged files "is not intended to replace loading data into tables". Keep the S3 files as the landing copy and the raw tables as the Raw replay copy.
  • One raw load per account, not per environment. Shared sources load once into a protected raw database that every environment reads. Data that an environment makes gets a raw schema per environment.
  • Reference data on two timelines. Keep both effective dates and the deploy date, so a past decision can be replayed with the rules known then (Effective dating, Where business rules run after migration).