DataSimplePractical GuideLog inOpen DataSimple
DataSimple
Chapter 17Transform and model

Data Vault: hubs, links and satellites from your Bronze tables

Back to the index · Previous: 16. Everything

What the Data Vault page does#

A Data Vault keeps every version of every business object that arrives in Bronze, and applies business rules and KPIs on top. A changed rule is recalculated, never reloaded, and new sources join without remodelling.

You describe the model on the Data Vault page (rail: Lakehouse & Storage → Data Vault). DataSimple generates everything else:

You decide DataSimple generates
Which Bronze table holds each business object, and its business key A hub per object, with SHA-256 hash keys
How the objects meet (a sale line joins store, product, customer and transaction) A link, or an event link for transactions
Which columns describe each object Satellites that keep the full history, and record-tracking satellites for deletes
The business rules (net amount, voided) Business Vault satellites with their own history
The KPIs ("net sales by region and month") PIT tables and a business view in Gold for each KPI

Everything goes into one notebook and one pipeline. The loads only add rows, so a run can be repeated safely. The last cell checks the vault and fails the run if a check fails.

Where each part lives#

Layer Schema Tables
Raw Vault Silver hub_<name>, lnk_<name> (link), nhl_<name> (event link), sat_<name>, rts_<hub> (record tracking)
Business Vault Silver bsat_<rule>
Query helpers and KPIs Gold pit_<hub>, the KPI views you name (gold.…)

Raw business data is never changed in the Raw Vault. Business logic lives in the Business Vault and the KPI views, so it can change without a reload.

Step by step#

The page has six steps along the top. Back and Next move between them; the draft is kept in your browser until you save.

1. Start#

Give the model a short name, then start from an example or an empty model:

Example Bronze tables What it shows
Retail retail_stores, retail_products, retail_customers, retail_sales_transactions, retail_transaction_lines an event link for sale lines, a business key fallback (RETAIL + transaction id), net sales by region and month
Finance fin_branches, fin_products, fin_customers, fin_accounts, fin_transactions a standard link (who holds which account where), an event link for postings, signed amounts and reversals, two KPIs

2. Hubs and business keys#

One hub per business object. For each hub choose its Bronze table, then press Find business keys. DataSimple profiles the current Bronze rows and scores every candidate column from 0 to 100, with its reasons:

Evidence Effect on the score
100% unique, never empty up
Never changed for the same record (Bronze keeps every version) up
Meaningful name (*_code, *_number, sku, email) up
The same column in other Bronze tables up
Counts upwards like an auto-number (store_id 1, 2, 3 …) down
Changed for some records, or often empty down

Pick the record identity (the source's own id) so DataSimple can tell versions of one record apart; without it, stability is not measured.

The recommendation is the best unique column that is not an auto-number. If no single column is unique, a unique pair of columns is proposed. If the source only has an auto-number, it is offered as a fallback together with a source code (for example RETAIL), so two systems' numbers can never collide. You confirm: press Use it, or click another column.

In the Retail example the scoring recommends store_code, sku and loyalty_number: real business keys, unique, always filled and never changed. It marks customer_id and store_id as auto-numbers. An email can be unique too, but it scores lower because contact details change over time. Transactions only have the source's number, so they use the fallback with source code RETAIL.

A link records that hubs meet. For transactions, use an event link: it keeps the amounts and the event time, and needs a dependent child key (the line number) so each event is unique.

When a business key is not in the link's own table, add a join: "where this column equals its column", and the columns it brings. A join can build on an earlier one. In the Retail example the sale line joins the transaction (for store, customer and time) and the product (for the SKU). It then joins the store through the transaction's store_id (for the store code), and the customer through the transaction's customer_id (for the loyalty number).

Every hub a link uses is also loaded from the link's table, so the link never points at a key its hub does not have.

A joined row gives its latest live version. If the parent was deleted later, its last version is used, so a deleted customer's past transactions keep their customer.

Effectivity (standard links). A relationship can change over time: an account moves to another branch, a customer changes home branch. Tick the driving key under Effectivity, for example fin_account on "account held at branch". This means the link has one active relationship per driving key. DataSimple adds an effectivity satellite eff_<link> with effective_from, effective_to and is_active:

  • when the source shows another relationship for the driving key, the old one ends and the new one starts;
  • when the source deletes the row, the relationship ends.

A check fails the run if a driving key ever has two active relationships.

An event is loaded once. It is identified by its dependent child key plus the hubs whose keys come from its own row. In Finance that is the transaction and its transaction_ref; in Retail, the transaction and its line_no. What it was linked to when it happened stays as it was. A parent that changes or is deleted afterwards never loads the event a second time. Examples are an account moving branch or a customer being removed.

4. Satellites#

Satellites keep the descriptive columns of a hub. A new row is added only when one of the values changes (compared with a hash of the columns). One for every hub without one fills in a satellite per hub from its table, which you can then trim.

Multi-active satellites. Some objects have several rows at the same time, for example a customer's mobile, home and work phone. Pick the column that tells them apart as the multi-active key (phone_type). The satellite then keeps the whole set as one version:

  • When any phone changes, is added or is removed: the customer's current set is written again under one load date.
  • When every phone is removed: a single marker row records the empty set.

A multi-active satellite cannot be a KPI dimension or a business rule input, because it would repeat each event. It is not part of the PIT tables.

Deletes in Bronze (_op_type = 'D' or _is_deleted) are recorded in the hub's record-tracking satellite as deleted, and as present again if the record comes back. Each event link has its own record-tracking satellite too (rts_nhl_<link>): an order line deleted in the source is marked deleted there, and the KPI views leave it out.

5. Rules and KPIs#

A business rule adds calculated columns on an event link, for example:

Column Expression
net_amount CAST(line_amount AS DECIMAL(14,2))
is_voided CASE WHEN status = 'Voided' THEN 'true' ELSE 'false' END

A rule can read the current values of satellites of the hubs the link joins (here transaction_status gives status). Its results are kept with history in silver.bsat_<rule>.

A KPI names a business view in Gold, its measure (aggregation and expression), the rules it uses, an optional filter, and its dimensions: a time grain of the event time, or a hub attribute from a satellite. The Retail KPI:

Part Value
View gold.dv_retail_sales_by_region_month
Measure sum of CAST(net_amount AS DECIMAL(14,2)) as net_sales
Filter is_voided = 'false'
Dimensions month of transaction_ts; region from store_details

Bridges. A bridge is a Gold table that follows standard links from hub to hub, so queries do not have to repeat the joins. For example: each customer's accounts with their branches, and the customer's home branch. To set one up:

  1. Add a bridge and name it.
  2. Pick its links in order.
  3. For each later link, choose the hubs it joins on. In the example, customer home branch joins on fin_customer only; joining on the branch as well would keep only accounts held at the home branch.

gold.brg_<name> has one row per relationship path per snapshot day (31 days by default), with the link and hub keys. Only relationships active that day are included: by effectivity when the link has it, otherwise loaded by then. A hub that a later link has again gets its own column, for example hk_fin_branch_customer_home_branch.

Hub attributes in a KPI are read through the hub's PIT table at its latest snapshot, so the view joins one row per key instead of searching satellite history. It shows every sale under the store's current region: a store that moved from West to South shows all its sales under South.

6. Check and generate#

  • Check the model refuses an incomplete model and says why. It refuses:

    • a hub without a business key or satellite;
    • a satellite without columns;
    • a link to a hub that does not exist;
    • a KPI dimension that no hub of its link gives;
    • a table or column that is not in the catalog;
    • an expression containing ; or a comment;
    • a Silver or Gold table the model would write that already exists with other columns.

    The same table with the same columns is accepted: a hub can be shared by several models, and saving again finds its own tables. Columns the platform adds to every table it writes (_tenantid) are not compared. Otherwise it lists every object it will create, by layer.

  • Show the SQL lists the generated cells; open any one to read it.

  • Save notebook and pipeline saves the notebook Data Vault · <name> and a pipeline that runs it, with an optional schedule. Nothing runs yet. Run the pipeline, or open the notebook and Run all; the first run creates the tables.

What the generated notebook does#

The cells run in order:

  1. Create every table if it does not exist, with a ghost row in each satellite (zero key, 1900-01-01) and the zero key in each hub (all-empty keys).
  2. Load hubs, then links, then satellites and record-tracking satellites (of hubs and of event links), then Business Vault rules. Each load adds only new keys, new events or changed rows. Business rules are recalculated for every event on every run, and a new version is added wherever the result differs, so a changed rule expression takes effect on the next run.
  3. Rebuild each PIT table: one row per hub key per day for the last 31 days, pointing at the satellite row valid that day (or the ghost row).
  4. Create or replace each KPI view.
  5. Check the vault. Each check counts problems; any problem fails the run with the names of the failed checks.
Check Problem it finds
Hub: no collisions, no duplicate hash keys One hash key for two business keys, or the same key twice
Link: no orphans A link pointing at a key its hub does not have
Event link: every event loaded once The same event in the event link twice (it would count twice in every KPI)
Event record tracking: every row has its parent, no repeated rows A deleted-event row for an event the link does not have
Satellite: every row has its parent, ghost record present, no repeated rows A satellite row without its hub key, a missing ghost row, two consecutive rows with the same values
Bronze to hub: every key reached the hub A Bronze key that was not loaded
PIT: one row per key per snapshot day A missing PIT row

Business keys are trimmed and upper-cased before hashing, empty parts become ^^, and parts are joined with ||, so " a1 " and "A1" are the same key.

Tested: the Retail model on sample data#

The generated notebook was run twice on a small Retail sample: two stores, two products, two customers, three sales and four sale lines. The sample also covered these cases:

  • one store moved region;
  • one store came in again with its code as st-002 (lower case, trailing space);
  • one customer was deleted;
  • one sale was voided.
What Result
Checks All 43 passed on both runs
Second run Added no rows anywhere: hubs, links, satellites and rules are unchanged; PIT rebuilt
Store that moved region sat_store_details holds its West row and its South row, plus the ghost row
st-002 vs ST-002 The same hub key: no extra hub row and no repeated satellite row
Deleted customer rts_customer records deleted
Voided sale bsat_sale_amounts marks it is_voided = true; the KPI leaves it out
KPI view October, South, net sales 16.50 (2.00 + 2.50 + 12.00)

Tested: the Retail demo data in DataSimple#

The Retail example was saved from the Data Vault page and its pipeline was run on the Retail demo tables (tenant 001). The first run took 13 minutes. All 43 checks passed. A second run took 6 minutes and added no rows: every table below had the same count afterwards.

Object Rows Cross-check against Bronze
hub_store 24 23 store codes plus the zero key
hub_product 1,201 1,200 SKUs plus the zero key
hub_retail_customer 20,077 20,076 loyalty numbers plus the zero key
hub_transaction 121,103 121,102 transactions plus the zero key
nhl_sale 444,734 444,734 distinct sale lines (444,746 Bronze rows; changed lines are not added twice)
sat_store_details 30 23 stores, 6 changes and the ghost row
bsat_sale_amounts 444,734 One rule row per sale line
gold.pit_store 713 23 stores × 31 days

The KPI view gold.dv_retail_sales_by_region_month gives 12 months × 5 regions. Its totals equal, to the cent, the same figure computed straight from Bronze. That direct query takes the current line and transaction rows, leaves out voided transactions and uses each store's current region.

Region Net sales (view) Net sales (Bronze)
Midwest 6,421,982.56 6,421,982.56
National 2,231,892.07 2,231,892.07
Northeast 4,205,512.35 4,205,512.35
South 7,938,748.89 7,938,748.89
West 7,385,806.26 7,385,806.26

An existing table with the same name is not touched. Tenant 001 already had a silver.hub_customer (Iceberg, with other columns). Check the model refused the model and named the table and its columns. The hub was renamed to retail_customer on the page; its link and satellites follow the new name automatically.

Tested: Demo Finance, from files in S3 to two KPIs#

A small bank's core banking tables were dropped in S3 as nightly CSV files:

  • branches, products, customers, accounts and 160,897 transactions;
  • an op column for deletes.

DataBridge (File drop, merged by key) loaded them into Bronze. The Finance example was generated on this page. The full walkthrough with every number is the Demo Finance practical guide.

Step Result
Load the files (DataBridge job 183) 5 tables, 181K rows in 46 s, every count matched, Bronze applied
Business keys customer_number (75) and account_number (90) recommended; customer_id and account_id marked as auto-numbers
First vault run (22 objects) 9 min 19 s, all 57 checks passed; both KPI views equal the same figures computed from Bronze, to the cent
Day 1 files (job 186, Re-run) Only the new files: 1,580 transactions (1,500 new, 80 reversed), 65 customers (60 changed, 5 deleted), 40 accounts, 1 branch, 1 product
History The savings rate 3.9 then 4.1, the branch manager before and after, 5 customers deleted in record tracking, 80 postings now reversed
Day-1 vault run All 58 checks passed; both KPI views equal Bronze to the cent again, with the reversals left out and the new transactions counted

This test found and fixed four problems:

  1. A re-run of a File drop with a table prefix loaded nothing.
  2. A deleted customer's past transactions were loaded a second time. The fix is the "event loaded once" rule above, with its new check.
  3. A second save was refused because of the platform's _tenantid column.
  4. A satellite name already used by the Retail vault was refused, as designed, and renamed fin_*.

Tested: a model built from Empty model (Manufacturing)#

The Manufacturing Bronze tables were modelled from Empty model, entirely on the page, to cover what the examples do not:

Part What was built
Hub mfg_order_line A two-column business key: order number (through a join to mfg_sales_orders) + line number
Hub mfg_supplier Fed by two tables: mfg_suppliers, and mfg_products through a join to the supplier (Another table with the same business key)
Hubs mfg_plant, mfg_product plant_code, sku
Event link order_sale Order lines across 4 hubs; joins build on earlier joins (order → plant, product → supplier)
Satellites One per hub; the order-line satellite joins the order header for the order number
Business rule line_values net_amount, is_cancelled (reads the order-line status)
5 KPI views count by week, avg by quarter, max by year, count distinct by day, min by month

Results (766K order-line rows in Bronze):

Scenario Result
First run All 47 checks passed. Hubs and PIT tables reconcile with Bronze; both supplier sources reached the hub. The run was repeated after a network outage stopped the first attempt; repeating it was safe
KPIs against Bronze Max and min matched; count, average and count distinct differed in a few rows. Found: 19 order lines deleted in the source were still counted. Fixed: record tracking for event links; KPI views leave deleted events out
Changing the rule on the page (is_cancelled now also for Open lines) and saving again Found: the rule was not recalculated for unchanged data. Fixed: rules are recalculated every run. The next run added exactly 8,222 rule versions, one per Open line, and no others
Schedule Saved with */5 * * * *; the pipeline started by itself at the next slot (triggered by the schedule), then the schedule was switched off
After the fixes All 49 checks passed; all 5 KPI views equal Bronze row for row (288, 64, 16, 1,994 and 176 rows; 0 differences either way)
Adding a column to an existing satellite Refused by Check the model, naming the new column; the table is not changed
A user without the Lakehouse tab Refused on every Data Vault call; with Lakehouse but without Pipelines, Check the model works and saving is refused (tested with the tab guard and test roles)

Tested: multi-active, effectivity and bridges (Demo Finance day 2)#

The Finance example was extended with:

  • a phone list per customer (fin_customer_phones, multi-active key phone_type);
  • effectivity on "account held at branch" (driving key fin_account);
  • a new standard link "customer home branch" with effectivity (driving key fin_customer);
  • the bridge fin_customer_accounts (account holding, then customer home branch joined on fin_customer).

A phones file (14,622 live phones for 8,000 customers) and a day-2 file set were dropped in S3 and loaded by DataBridge:

  • 25 accounts move to another branch;
  • 15 customers change home branch;
  • 60 customers' phones change: 30 mobile numbers changed, 20 work phones added, 10 home phones removed.
Run Result
First run with the new objects All 74 checks passed. 8,001 phone sets (8,000 customers and the ghost row); 12,000 active account holdings; 5 home-branch relationships ended, for the 5 customers deleted on day 1. The bridge has 31 snapshot days; today holds 11,994 paths (12,000 accounts less 6 held by the deleted customers)
Day-2 run All 74 checks passed
Phones 60 new sets (8,061). The latest set of every customer equals the live phones in Bronze: 14,632 rows, 0 differences either way. The removed home phones are not in the latest sets
Account held at branch 12,050 rows: 25 relationships ended, 25 new ones active. The active relationships equal the current account branch in Bronze: 12,000 rows, 0 differences
Customer home branch 30 more rows; 20 relationships ended (5 deletes from day 1, 15 changes)
Bridge Today's snapshot equals Bronze except for the 25 moved accounts. Their move is dated 4 November in the source, so today's snapshot correctly still shows the old branch

This test found and fixed two problems:

  1. The bridge SQL had two ON clauses after one another when a link has effectivity. Each table now has its own JOIN … ON.
  2. A day-2 file with an empty column broke Bronze. closed_date was blank in every row of the accounts file, so the column was read as text and Bronze was widened to text; Spark then refused the table. DataBridge now keeps the Bronze type for a column that is blank in every row (needtofix H118). The Bronze and source files were repaired, with a backup kept.

Good to know#

  • Who can use it. The page and its API follow the Lakehouse tab permission; saving also needs Notebooks and Pipelines. The profile and the loads run as you, under your grants.
  • Re-running is safe. Loads are insert-only and skip what is already there; the PIT tables and KPI views are rebuilt each run.
  • A new model version. Change the model and save again: a new notebook and pipeline are created, and existing tables keep their rows. Adding columns to an existing satellite is refused by Check the model, which names the new columns: put them in a new satellite of the same hub, or add them to the table first.
  • Still to come. Same-as and hierarchical links, saving approved business keys in the catalog and Semantics, and drafting the KPI as a Semantics metric. See TODO.md.

Previous: 16. Everything · Back to the index