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 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:
- 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. 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:
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 |
Connect and discover databases →
finance_demo→ Load tables for selected databases. DataBridge finds the five folders as five tables, 181K records, 23.3 MB.All five tables → Continue to Destination.
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).
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 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, fromupdated_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)
Start: Example: finance (bank branches, products, customers, accounts, transactions). The model name is
finance.Hubs & keys: five hubs on their business keys. The tenant already had a
silver.hub_customer,hub_productandhub_transactionwith other keys, so the bank's hubs are namedfin_*.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.
- account_holding, a standard link from
Satellites: one per hub. All are named
fin_*, because the Retail model already wrote asat_transaction_status.Satellite Columns fin_branch_detailsname, region, city, state, manager, open fin_product_termsname, type, interest rate, monthly fee, active fin_customer_profilesegment, risk rating, KYC status, city, state fin_account_statusstatus, credit limit, currency, opened, closed fin_transaction_statusstatus, type, channel Rules & KPIs:
Business rule posting_amountsExpression signed_amountCASE WHEN direction = 'C' THEN CAST(amount AS DECIMAL(14,2)) ELSE -CAST(amount AS DECIMAL(14,2)) ENDflowCASE WHEN direction = 'C' THEN 'inflow' ELSE 'outflow' ENDis_reversedCASE WHEN status = 'Reversed' THEN 'true' ELSE 'false' END(fromfin_transaction_status)KPI View Measure Filter Dimensions Net flow by region and month gold.dv_finance_net_flow_by_region_monthsum signed_amountasnet_flowis_reversed = 'false'month of posted_at;regionTransaction value by customer segment and month gold.dv_finance_value_by_segment_monthsum amountasgross_valueis_reversed = 'false'month of posted_at;segmentCheck & 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 tofin_*, 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.
Save notebook and pipeline: notebook #133
Data Vault · financeand 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_transactions1,580 rows;fin_customers65;fin_accounts40;fin_branches1;fin_products1.
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 |