LLI Wiki — My Digital Knowledge Base

Data Vault, Snowflake and bronze, silver, gold layers: what I keep in mind

Hubs, links and satellites, what Snowflake brings, and how Data Vault fits into the bronze, silver and gold layered architecture.

A data warehouse isn't "a big database with reports on top". It's mostly one question: how do you keep the history of everything, from sources that keep changing, without breaking everything at each change? Data Vault answers on the modelling side, Snowflake on the engine side, and the bronze, silver and gold layers on the organisation side. Here's what I keep in mind.

Data Vault splits each business subject into three kinds of tables. It sounds like astronomy; it's modelling.

Table What it holds Example
Hub The business key, and nothing else: one row per customer, per order, per product hub_customer (customer number)
Link A relationship between two or more hubs link_order_customer (this order belongs to this customer)
Satellite The descriptive attributes and their history sat_customer_details (name, address, segment… with the date of each version)

The rules that hold it together:

A new source or a new attribute? Add a satellite next to the others. Nothing existing moves. That's the whole point: the model grows without a redesign.

-- Generic example (Snowflake syntax)
CREATE TABLE hub_customer (
    hk_customer    BINARY(16)    NOT NULL,   -- MD5 of the business key
    customer_no    VARCHAR       NOT NULL,   -- business key
    load_dts       TIMESTAMP_NTZ NOT NULL,
    record_source  VARCHAR       NOT NULL,
    CONSTRAINT pk_hub_customer PRIMARY KEY (hk_customer)
);

CREATE TABLE sat_customer_details (
    hk_customer    BINARY(16)    NOT NULL,
    load_dts       TIMESTAMP_NTZ NOT NULL,
    hashdiff       BINARY(16)    NOT NULL,   -- MD5 of the attributes below
    customer_name  VARCHAR,
    segment        VARCHAR,
    record_source  VARCHAR       NOT NULL,
    CONSTRAINT pk_sat_customer_details PRIMARY KEY (hk_customer, load_dts)
);

In Snowflake, PRIMARY KEY is informational: the constraint isn't enforced. The load must guarantee uniqueness.

The downside: nobody wants to write a report on hubs and satellites. On top you need star-schema marts (facts and dimensions), often helped by PIT (point in time) and bridge tables so queries stay fast. Data Vault is the well-organised wine cellar, not the glass you serve.

Snowflake: why it fits well

Bronze, silver, gold: what goes where?

The layered (so-called "medallion") architecture sorts data by quality level:

Layer Content Rule
Bronze Raw data, as the source sends it Touch nothing. It's the proof of what arrived
Silver Cleaned, deduplicated, typed, integrated data One clean version of each thing
Gold Data ready to serve: star schemas, aggregates Designed for the report or application that reads it

Data Vault and medallion don't compete, they fit together:

sources ──> bronze (raw, staging) ──> silver (Data Vault: hubs, links, satellites) ──> gold (star marts)

Like a good coffee: the raw bean, then roasted, then in the cup. Or a Pepsi Max: the can, the fridge, the glass.

What I keep in mind

Mastodon LLIMastodon SocialFosstodon