DataSimpleDeveloper HandbookLog inOpen DataSimple
DataSimple
Chapter 29Shape and govern data

Data Vault

Hubs, links and satellites from your Bronze tables. DataSimple scores the business keys and generates the Raw Vault, the Business Vault, PIT tables and KPI views with their checks, as one notebook and one pipeline. This chapter covers how the page works, two practical guides (Retail, and a bank from files in S3), and which scenarios are tested.

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 practical guide: Demo Finance below.

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. They are on the project's TODO list.

Practical guide: Demo Finance#

This guide builds a Data Vault 2.0 for a small retail bank:

  • the core banking tables arrive in S3 as nightly CSV files;
  • DataBridge loads them into Bronze;
  • the Data Vault page generates the Raw Vault, the Business Vault, PIT tables and two KPI views;
  • a day of changes then shows the history the vault keeps.

Every number below is from the real run.

Step 1: the files in the landing folder#

The bank's core system exports every table each night as a CSV file with a header row, one folder per table, into the landing folder of the lake:

Text
landing/finance_demo/branches/branches_2026-11-02.csv          20 rows
landing/finance_demo/products/products_2026-11-02.csv          12 rows
landing/finance_demo/customers/customers_2026-11-02.csv     8,000 rows
landing/finance_demo/accounts/accounts_2026-11-02.csv      12,000 rows
landing/finance_demo/transactions/transactions_2026-11-02.csv  160,897 rows

Every file carries created_at, updated_at and an op column (I insert, U update, D delete), so later files can say what changed. The files are made by the project's file generator (generate_files.py, fixed random seed, so the same rows every time).


Step 2: load the files into Bronze (DataBridge, File drop)#

Screen: DataBridge → + New connection → Source type File drop (S3 / ADLS / folder)

Field Value
Workspace connection DS-15 · Target_AWS (S3 storage): the bucket's keys stay on the server
Source Name FIN_CoreBanking
Drop folder inside Target_AWS landing/finance_demo (the folder inside the bucket; DS-15 already points at the lake root)
File format CSV
New files are merged by key (upsert)
Table name prefix fin_ (the folder accounts loads as fin_accounts)
Delete marker column op (rows with D are deletes)
Change time column updated_at
  1. Connect and discover databases → finance_demo → Load tables for selected databases. DataBridge finds the five folders as five tables, 181K records, 23.3 MB.

  2. All five tables → Continue to Destination.

  3. Destination: keep the defaults:

    • DS-15;
    • source layer Parquet;
    • Auto-apply to Bronze ticked;
    • no watermark (files need none: each run reads only files it has not read before).
  4. Check keys & watermark → Choose key & watermark… for each table. DataBridge proposes the source id from a sample; tick it and Use for this table:

    Table Key
    fin_branches branch_id
    fin_products product_id
    fin_customers customer_id
    fin_accounts account_id
    fin_transactions transaction_id
  5. Start processing.

Result (job 183):

  • 5 tables and 181K rows, read and written in 46 s; every row count matched.
  • The largest table, fin_transactions (160,897 rows), took 26 s.
  • Bronze was applied straight after: 5 table(s) applied, 181K rows.

Every Bronze row carries the bookkeeping columns:

  • _op_type (I, U, D) and _is_deleted;
  • _source_ts, from updated_at;
  • _load_ts, _batch_id, _row_hash;
  • _source_file, so you can see which file it came from.

Step 3: business keys#

Screen: Data Vault → Hubs & keys → Find business keys

DataSimple profiles the current Bronze rows and scores every column. On the bank's data:

Table Recommended Score Why Not recommended
fin_customers customer_number 75 100% unique, never empty, never changed for the same customer, meaningful name email 65 (contact details change over time); customer_id 45 (counts upwards like an auto-number)
fin_accounts account_number 90 100% unique, never empty, never changed, meaningful name, also found in gl_accounts account_id 45 (auto-number)

The source ids (customer_id, account_id) are what DataBridge merges by; the business keys (customer_number, account_number) are what the vault is built on. When the bank later adds a card system with its own ids, its customers meet the same hub through the customer number.


Step 4: generate the vault (Data Vault page)#

Screen: Data Vault (Lakehouse & Storage)

  1. Start: Example: finance (bank branches, products, customers, accounts, transactions). The model name is finance.

  2. Hubs & keys: five hubs on their business keys. The tenant already had a silver.hub_customer, hub_product and hub_transaction with other keys, so the bank's hubs are named fin_*.

  3. Links:

    • account_holding, a standard link from fin_accounts: which customer holds which account, at which branch, on which product. It joins customers, branches and products to reach their business keys.
    • posting, an event link from fin_transactions, one row per transaction:
      • it keeps amount, direction, posted and value dates;
      • its dependent child is transaction_ref;
      • customer and branch are reached through the transaction's account.
  4. Satellites: one per hub. All are named fin_*, because the Retail model already wrote a sat_transaction_status.

    Satellite Columns
    fin_branch_details name, region, city, state, manager, open
    fin_product_terms name, type, interest rate, monthly fee, active
    fin_customer_profile segment, risk rating, KYC status, city, state
    fin_account_status status, credit limit, currency, opened, closed
    fin_transaction_status status, type, channel
  5. Rules & KPIs:

    Business rule posting_amounts Expression
    signed_amount CASE WHEN direction = 'C' THEN CAST(amount AS DECIMAL(14,2)) ELSE -CAST(amount AS DECIMAL(14,2)) END
    flow CASE WHEN direction = 'C' THEN 'inflow' ELSE 'outflow' END
    is_reversed CASE WHEN status = 'Reversed' THEN 'true' ELSE 'false' END (from fin_transaction_status)
    KPI View Measure Filter Dimensions
    Net flow by region and month gold.dv_finance_net_flow_by_region_month sum signed_amount as net_flow is_reversed = 'false' month of posted_at; region
    Transaction value by customer segment and month gold.dv_finance_value_by_segment_month sum amount as gross_value is_reversed = 'false' month of posted_at; segment
  6. Check & generate → Check the model. The first check refused the model: silver.sat_transaction_status already exists with other columns (hk_transaction, …). The Retail vault had written it. After renaming the satellites to fin_*, it lists 22 objects:

    • Raw Vault: 5 hubs, 2 links, 5 satellites, 5 record-tracking satellites;
    • Business Vault: 1 business satellite;
    • Gold: 2 PIT tables and 2 KPI views.
  7. Save notebook and pipeline: notebook #133 Data Vault · finance and pipeline #35. Nothing runs yet.


Step 5: run the pipeline and check#

Screen: Pipelines → Data Vault · finance → Run

Result (run 130): 41 cells in order, 9 min 19 s, all 57 Data Vault checks passed.

Object Rows Against Bronze
hub_fin_branch / product / customer / account / transaction 21 / 13 / 8,001 / 12,001 / 160,898 the source rows plus the zero key
lnk_account_holding 12,000 one per account
nhl_posting 160,897 one per transaction
satellites rows + 1 one row per key, plus the ghost row
bsat_posting_amounts 160,897 one rule row per posting
gold.pit_fin_branch 620 20 branches × 31 days
gold.pit_fin_customer 248,000 8,000 customers × 31 days

Step 6: the KPI views against Bronze#

Each KPI total was computed a second way, straight from Bronze. That query takes the current rows, leaves out reversed transactions, and uses each account's current branch and each customer's current segment. Both match to the cent:

Region Net flow (view) Net flow (Bronze)
Midwest 21,125,859.06 21,125,859.06
Northeast 22,693,760.47 22,693,760.47
South 26,375,116.00 26,375,116.00
West 16,742,238.33 16,742,238.33
Segment Transaction value (view) Transaction value (Bronze)
Business 17,791,922.46 17,791,922.46
Premier 22,572,253.82 22,572,253.82
Retail 123,302,665.96 123,302,665.96

Each region has 23 months, January 2025 to November 2026.


Step 7: day 1: changes, reversals and deletes#

The next night's files (*_2026-11-03.csv) arrive in the same folders, carrying only what changed on 3 November:

Change Rows
Branch BR007 gets a new manager 1
High-Yield Savings rate raised from 3.90 % to 4.10 % 1
Customers: 30 risk reviews, 30 segment changes 60
Customers removed from the system (op = D) 5
Accounts frozen or closed 40
Transactions reversed 80
New transactions 1,500

DataBridge → Load History → job 183 → Re-run.

Result (job 186): only the new files were read, an incremental load:

  • fin_transactions 1,580 rows;
  • fin_customers 65;
  • fin_accounts 40;
  • fin_branches 1;
  • fin_products 1.

Bronze keeps every version:

Table _op_type I U D
fin_transactions 162,397 80
fin_customers 8,000 60 5 (_is_deleted true)
fin_accounts 12,000 40
fin_branches / fin_products 20 / 12 1 / 1

Then Pipelines → Data Vault · finance → Run again.


Step 8: history in the vault#

After the day-1 run:

What happened In the vault
Savings rate raised sat_fin_product_terms for SAV-HY: 3.9 (first load) and 4.1 (day 1), both kept
New branch manager sat_fin_branch_details for BR007: Lucia Miller, then Grace Okafor
5 customers removed rts_fin_customer: 5 deleted rows; the hub keeps their keys and their past transactions keep them
80 reversals bsat_posting_amounts: a new rule row with is_reversed = true for each; 2,037 postings are now reversed in total
1,500 new transactions 1,500 new hub keys and event-link rows
Accounts frozen or closed sat_fin_account_status: 40 new rows

After the fixes below, the final day-1 run (run 133, 7 min 47 s) passed all 58 checks, including the new every event loaded once. Both KPI views again equal Bronze to the cent, now with the day's changes. The 80 reversals are left out, the 1,500 new transactions are counted, and the 60 segment changes are reflected:

Region Net flow after day 1 (view = Bronze)
Midwest 21,108,173.87
Northeast 22,688,335.17
South 26,342,398.74
West 16,730,398.14
Segment Transaction value after day 1 (view = Bronze)
Business 17,843,399.05
Premier 23,175,040.44
Retail 123,135,496.63

The vault holds 162,397 postings, one per transaction (160,897 + 1,500); the Business Vault holds 162,477 rule rows (the postings plus the 80 reversal versions).


Step 9: day 2: phones, branch moves and a bridge#

The model was extended with a multi-active satellite (each customer's phones), effectivity on two standard links (account held at branch, customer home branch) and the bridge fin_customer_accounts. The files: customer_phones_2026-11-03.csv (14,622 phones), then the day-2 set of 4 November (25 accounts moved branch, 15 customers changed home branch, 60 customers' phones changed). The generator writes them with --phones and --day2.

Check after the day-2 run (all 74 checks passed) Vault Bronze
Phones in each customer's latest set 14,632 14,632 live phones, 0 differences
Phone sets 8,061 8,001 first load + 60 changed customers
Active account-to-branch relationships 12,000 12,000 current accounts, 0 differences
Ended account-to-branch relationships 25 25 accounts moved
Ended home-branch relationships 20 5 deleted customers + 15 moves

The bridge holds one row per path per day for 31 days. Today's snapshot shows the 25 moved accounts at their old branch: their move is dated 4 November, so it starts in tomorrow's snapshot.


What this test found and fixed#

Finding Fix
Re-running a File drop with a table prefix loaded nothing ("no new files since the last run"): the re-run lost table_prefix, so it looked for the folder fin_accounts instead of accounts The prefix is kept on re-runs and schedules (tracked as H112)
A deleted customer's past transactions were loaded a second time, pointing at the zero key, and counted twice in the KPIs. Joins looked up only live parent rows. A parent that changed later, such as an account moving branch, would have caused the same. Joins use a parent's latest live version, else its delete row. An event link loads each event once, by its own identity (hk_fin_transaction, transaction_ref). A new check, every event loaded once, fails the run if it ever happens. The 98 duplicated events and 6 holdings from the first day-1 run were removed. One new transaction that the first day-1 run had linked to the zero key was removed and reloaded with its customer.
A second save of the model was refused: the platform adds _tenantid to the PIT tables it writes, and the check compared it The comparison ignores the platform's own _ columns
sat_transaction_status already existed (from the Retail vault) Check the model refused it; satellites renamed fin_*
The bridge SQL failed (two ON clauses in a row) when a link has effectivity Each table has its own JOIN … ON
The day-2 accounts file broke Bronze: closed_date was blank in every row, read as text, and Bronze was widened to text DataBridge keeps the Bronze type for a column blank in every row (tracked as H118); the Bronze and source files were repaired, with a backup

Still open: Bronze rows from a File drop over a storage data source carry _datasource = Target_AWS rather than the source name FIN_CoreBanking (tracked as H113).

Which scenarios are tested#

Tested live means run in DataSimple on the Retail demo tables, the Demo Finance files or the Manufacturing tables, with the results checked against Bronze. Unit tested means the generated SQL and the model check are covered by automated tests, but the scenario has not been run on real data.

Scenario Status Where
A model built from Empty model on the page, step by step Tested live Manufacturing
Hubs on a real business key (store_code, sku, loyalty_number, customer_number, account_number, plant_code) Tested live Retail, Finance, Manufacturing
Business key fallback: source number + source code (RETAIL + transaction id) Tested live Retail
Business key scoring: unique, filled, stable, auto-number, contact details, shared names Tested live Retail, Finance
A hub on a two-column business key, one part through a join Tested live Manufacturing (order number + line number)
Composite business key proposed by the scoring when no single column is unique Unit tested
A hub fed by its own table and by the links that use it Tested live Retail, Finance, Manufacturing
A hub fed by two of its own source tables (one through a join) Tested live Manufacturing (suppliers)
Event link (non-historized) with joins that build on earlier joins Tested live Retail, Finance, Manufacturing (4 hubs)
Standard link (a relationship of four hubs) Tested live Finance (account holding)
A satellite with a join Tested live Manufacturing (order-line status)
Satellites keep history: a new row only when a value changes; the same key spelled differently is one key Tested live Finance; sample data
Deletes of hub records: record-tracking satellite marks deleted, past events keep their parent Tested live Finance (5 customers)
Deleted events (order lines deleted in the source) left out of the KPIs Tested live Manufacturing (17 events)
A parent deleted the same day one of its events arrives Tested live Finance
A parent that changes after its events (for example an account moving branch) Unit tested Same rule as deletes: each event is loaded once
Business rules with history, reading the current value of a satellite Tested live Retail, Finance, Manufacturing
Changing a business rule's expression on the page and running again Tested live Manufacturing (8,222 new rule versions, only the changed rows)
KPI aggregations: sum, count, avg, max, min, count distinct Tested live, equal to Bronze Retail, Finance, Manufacturing
KPI time grains: day, week, month, quarter, year Tested live, equal to Bronze Manufacturing, Retail, Finance
PIT: one row per key per day, ghost row when a satellite has no row yet Tested live Retail, Finance, Manufacturing
Re-running the pipeline adds no rows; repeating a run stopped half way Tested live Retail, Finance, Manufacturing
A day of changes loaded incrementally and applied to the vault Tested live Finance
The automatic checks (up to 74 per model) Tested live all three
A name already used by another table with other columns is refused, and nothing is written Tested live Retail, Finance
Saving the same model again Tested live Finance, Manufacturing
Renaming a hub on the page renames it in its links and satellites Tested live Retail
Adding a column to an existing satellite Refused with advice (new satellite, or change the table first) Manufacturing
A schedule on the pipeline Tested live Manufacturing (started by the schedule, then switched off)
A user without the Lakehouse tab, or without Pipelines Tested with the access guard and test roles Not with a second real user
Multi-active satellite: the whole set written again when a row changes, is added or is removed Tested live, equal to Bronze Finance (60 customers' phones)
Effectivity satellite: a relationship ends when the driving key moves or the row is deleted Tested live, equal to Bronze Finance (25 accounts moved branch, 15 customers moved home branch, 5 deletes)
An empty set in a multi-active satellite (every row removed) Unit tested
Bridge across two standard links, the second joined on one hub, with effectivity per snapshot day Tested live, equal to Bronze Finance (31 days)
Same-as and hierarchical links Not built Still to come