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.
3. Links#
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:
- Add a bridge and name it.
- Pick its links in order.
- For each later link, choose the hubs it joins on. In the example, customer home branch joins on
fin_customeronly; 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:
- 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).
- 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.
- 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).
- Create or replace each KPI view.
- 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
opcolumn 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:
- A re-run of a File drop with a table prefix loaded nothing.
- A deleted customer's past transactions were loaded a second time. The fix is the "event loaded once" rule above, with its new check.
- A second save was refused because of the platform's
_tenantidcolumn. - 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 keyphone_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 onfin_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:
- The bridge SQL had two
ONclauses after one another when a link has effectivity. Each table now has its ownJOIN … ON. - A day-2 file with an empty column broke Bronze.
closed_datewas 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