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 in three words: hub, link, satellite
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:
- Insert only. Never an
UPDATE, never aDELETE: a change in the source becomes a new row in the satellite. The history is complete by design. - Every row says where it came from and when it arrived: a load date (
load_dts) and a source (record_source). - Keys are hashes of the business key (Data Vault 2.0). Hubs, links and satellites can therefore load in parallel, without waiting for a sequence.
- A satellite compares a hash of its attributes (
hashdiff) to know whether anything changed. No change, no new row.
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
- Storage and compute are separate. "Virtual warehouses" are compute clusters you start, resize or stop on demand. The evening load and the morning reports don't step on each other.
- Auto-suspend. An idle warehouse stops by itself; you only pay while it computes. Tune it carefully, or the bill will start data mining you.
- Time Travel. You can read a table as it was a few hours or days ago (depending on the configured retention). Handy after a failed load.
- Zero-copy cloning. A clone of a database or table is instant and doesn't duplicate the data at first. Perfect for testing a new load on a copy of production.
- Data Vault's insert-only writes match Snowflake's storage, which prefers appends to row-by-row updates.
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
- Keep the raw data. As long as the bronze layer exists, everything can be recomputed. The day you delete it "to save space", you regret it.
- Never modify history. An error is fixed with a new row, not a quiet
UPDATE. - Code, not clicks. Loads are versioned scripts (SQL, Python, Spark). A Data Vault model is very repetitive: it's far better generated from metadata than written by hand.
- The model serves the business. Data Vault isn't an end in itself: if nobody reads the gold layer, the finest cellar in the world is useless.