Data Vault Literacy & Model Validation

Multiple teams define customer business keys differently. SAP says customer_bk = SAP#. Salesforce says customer_bk = Salesforce Account ID. Shopify says customer_bk = Shopify customer ID. The Hub is loaded 3 different ways, and every query result is wrong because a single physical customer is represented as 3 separate Hub rows.

SAP customer 123456, Salesforce account SF-999, Shopify customer SH-777 represent the same Acme Corp. With no governance, you create 3 Hub rows. A query "sum sales for customer Acme" returns incomplete data (only SAP orders) or triple-counts (if you UNION all sources).

Establish a Business Key Governance Registry. Define: customer_bk = CONCATENATE(SAP#, '|', SF#, '|', Shopify#). Create a mapping table (golden record). All ETL pipelines use this governance table to compute customer_bk consistently. Update governance table when a new source is added.

SAT_CUSTOMER has 100+ columns: name, email, phone, fax, address, city, state, zip, country, industry, credit_limit, credit_utilization, segment, life_time_value, last_purchase_date, etc. When ANY column changes, a new row is inserted into a 100-column table. Storage bloats. Query performance suffers. Hashdiff becomes meaningless (detects change, but you can't tell which column changed without row-by-row inspection).

A customer's email changes. A new row is inserted into SAT_CUSTOMER with 100 columns, of which only email differs. The other 99 columns are duplicated. Over 1 year, with 50M customers and 5 email changes/customer, you insert 250M rows into SAT_CUSTOMER, wasting 25 GB on redundant data.

Split SAT_CUSTOMER into logical Satellites: SAT_CUSTOMER_CONTACT_INFO (email, phone, fax), SAT_CUSTOMER_ADDRESS (address, city, state, zip), SAT_CUSTOMER_PROFILE (segment, lifetime_value, industry). Each Satellite has fewer columns, smaller rows, faster joins, and more meaningful change detection.

A Satellite contains derived columns: SAT_CUSTOMER has a column CALCULATED_CREDIT_SCORE = ROUND(lifetime_value / avg_order_value, 2). This logic should not be in Raw Vault (audit-grade, source-aligned). When the credit score calculation changes, you can't easily re-compute historical rows. Raw Vault becomes coupled to business rules.

Credit score formula changes. You now need to recalculate 1M historical customer records in SAT_CUSTOMER. If you INSERT new rows, you have two versions of credit score (old formula, new formula) coexisting, causing confusion. If you UPDATE, you break immutability.

Keep Raw Vault purely source-aligned: SAT_CUSTOMER_CREDIT contains only raw score from SAP. Create a Business Vault table: SAT_CUSTOMER_CREDIT_GOLDEN, which applies the formula. When the formula changes, you re-run Business Vault; Raw Vault remains untouched. This preserves audit trail and allows formula re-computation.

A supplier relationship changes: Vendor A supplies Product X at $10 (2024 Q1), then at $15 (2024 Q2). You store this in LINK_PRODUCT_VENDOR with no Satellite. A query "what was the supplier of Product X on 2024-04-01?" is ambiguous: which price applies?

Historical costing is wrong. You can't replay 2024 Q1 costs because you don't know the Q1 supplier prices (they've been overwritten by Q2 prices in the Link).

Create SAT_LINK_PV_SOURCING (Product-Vendor sourcing satellite) with effective_date and expiry_date (or load_date + next_load_date). Capture price, lead_time, quality_metrics as time-variant attributes. Query WHERE effective_date <= @query_date AND (@query_date < expiry_date OR expiry_date IS NULL) to get historical values.

In the dimensional (star schema) layer, DIM_CUSTOMER has 150+ columns: all customer attributes stuffed into one dimension. This violates the principle of grain separation. DIM_CUSTOMER should be at the customer grain; if you mix in address, billing, and shipping attributes at different update frequencies, you create ambiguity.

An order was shipped to address A but billed to address B. DIM_CUSTOMER has only one address column. Do you store A or B? If you store both (ship_address, bill_address), you're violating atomicity. If you pick one, reporting is wrong for the other.

Break DIM_CUSTOMER into multiple conformed dimensions: DIM_CUSTOMER (core: name, segment, lifetime_value), DIM_CUSTOMER_BILLING_ADDRESS (address, city, state, zip, billing_date), DIM_CUSTOMER_SHIPPING_ADDRESS (ship_to_address, ..., ship_date). In the fact table, include both billing_address_sk and shipping_address_sk. This is cleaner and avoids ambiguity.

A query joins FACT_SALES (one row per order) to DIM_CUSTOMER (one row per customer) and DIM_PRODUCT_CATEGORY (one category per 10 products). If a customer has 5 orders and each order has 3 products in different categories, the join creates 5 × 3 = 15 rows, inflating the fact table. SUM(sales_amount) is now 15× the actual total.

Query: "Total sales by product category". You join FACT_SALES to DIM_PRODUCT (many products per category). The fact table explodes, and your sum is wrong.

Use a bridge dimension (or outrigger) if many-to-one is unavoidable. Or, re-grain the fact table: FACT_SALES_BY_CATEGORY (one row per order-category combination). Alternatively, ensure joins are 1:1 or M:1 only (never 1:M on the fact side).

A customer has many addresses (home, work, billing, shipping). A product has many categories (primary, secondary). If you join FACT_SALES to DIM_CUSTOMER to DIM_ADDRESS (M:N) to DIM_PRODUCT to DIM_CATEGORY (M:N), the row count explodes combinatorially. Aggregates are meaningless.

100 addresses × 10 categories per product = 1,000 rows per fact table join. SUM(sales) is multiplied 1,000×.

Use a bridge dimension that flattens the M:N relationship. Or, create separate conformed facts: FACT_SALES_BY_DELIVERY_ADDRESS, FACT_SALES_BY_CATEGORY. Or, normalize in Data Vault: ensure Links are the source of truth; dimensions are de-normalized views.

A dimension uses SCD Type 2 (effective_date, expiry_date) to track changes, but doesn't compute a hashdiff. When you load a new version of a dimension, you compare 50 columns one-by-one to detect change. If any differs, you insert a new row. This is CPU-intensive and error-prone.

DIM_CUSTOMER load takes 30 minutes for 5M rows because you're comparing columns individually. A hashdiff approach would take 5 minutes.

Add a change_hash column: MD5(CONCAT(col1, col2, ..., col50)). On load, compare change_hash from staging to the previous row's change_hash. If different, insert new version. If same, skip. This reduces CPU and makes change detection explicit.

Your dimensional layer is so normalized that a simple report requires 15 joins. DIM_CUSTOMER references DIM_REGION, which references DIM_COUNTRY, etc. Each join adds latency. Analysts complain that queries are slow.

"Total sales by region" requires joining FACT_SALES → DIM_STORE → DIM_ZONE → DIM_REGION → DIM_COUNTRY. 5 joins for one metric.

Denormalize the consumption layer for query speed. Embed region, zone, country as columns in DIM_STORE. Store is already filtered by report context (which stores?) so the denormalization doesn't inflate storage. Queries are now 1 join instead of 5. If storage is a concern, use a materialized view or aggregate table for common patterns.

A downstream team finds an error in a historical order (e.g., wrong product SKU). Instead of logging the error in the source system and reloading Raw Vault, they UPDATE the Raw Vault table directly. This breaks immutability and audit trail. Future audits can't explain why the row changed.

Order from 2024-01-15 had product SKU = "WRONGSKU". On 2024-06-01, someone UPDATEs it to "RIGHTSKU" in the Raw Vault. An audit query on 2024-01-20 now returns the wrong value. The timestamp of the UPDATE (2024-06-01) is lost.

Raw Vault is immutable. REVOKE UPDATE and DELETE permissions from all users. If an error is discovered, (1) log the error in the source system, (2) reload the source in Raw Vault with the corrected value and a NEW load_date, or (3) create a Business Vault correction table (SAT_CORRECTION) with reason, approved_by, approval_date. Business Vault references both Raw Vault and Corrections to derive the correct value.

Different source systems assign their own IDs without source qualification. SAP says customer 12345, Shopify says customer 12345 (different real customers). Both are loaded into HUB_CUSTOMER with the same business key. A query "get customer 12345" returns two unrelated customers, and reports are nonsensical.

ShaDey Inc. loads SAP customer 12345 (Acme Corp) and Shopify customer 12345 (Zephyr Ltd) into the same hub. Both have customer_bk = "12345". A customer segmentation report now shows Acme and Zephyr as a single entity with combined lifetime value, destroying data integrity.

Qualify business keys by source system: customer_bk = CONCAT('SAP_', SAP_customer_id) or customer_bk = CONCAT('SHOPIFY_', shopify_customer_id). Or maintain a source-aware golden record table that maps (source_system, source_key) to a canonical customer_bk. This ensures collisions are impossible and traceability is preserved.

A product's supplier relationship changes: Vendor A → Vendor B → Vendor A again. LINK_PRODUCT_VENDOR records only current relationships. Historical queries "Who was the supplier on 2024-Q2?" are unanswerable. You can't recreate historical cost-of-goods-sold because you don't know supplier validity periods.

Product SKU-999 was supplied by Vendor-A (Q1-Q2 2024) and Vendor-B (Q3-Q4 2024). The link is overwritten or lacks date fields. A profitability report for Q2 2024 can't determine the correct supplier cost because you don't know which vendor was active.

Create SAT_LINK_PRODUCT_VENDOR_SOURCING with effective_date, expiry_date (or dbt_load_date + dbt_next_load_date). Include supplier_cost, lead_time, quality_score. On query, filter: WHERE effective_date <= @history_date AND (@history_date < expiry_date OR expiry_date IS NULL). This preserves historical relationships and enables time-travel queries.

Hashdiff is computed differently in different ETL environments. Dev concatenates columns as (name, email, phone), Prod concatenates as (email, name, phone). The same customer record produces different hashdiff values in dev vs. prod. Change detection fails; spurious inserts create duplicate satellite rows. Hash mismatches between environments cause reconciliation nightmares.

SAT_CUSTOMER is loaded in dev with hashdiff_1 = MD5(concat(name, email, phone)). In prod, a new pipeline computes hashdiff_2 = MD5(concat(email, name, phone)). They differ for the same customer. Downstream depends-on logic breaks. Data quality checks fail.

Standardize hashdiff column order across all environments. Document it: "Hashdiff columns are alphabetically sorted." Use a dbt macro or shared SQL function that enforces this order: MD5(CONCAT(col_a, col_b, col_c, ...)) -- alphabetically. Add a test: verify hashdiff matches expected value for a known record. This prevents dev/prod misalignment.

Raw Vault tables contain derived, calculated, or filtered data. A satellite stores DISCOUNTED_PRICE = PRICE × (1 − discount_rate). A hub is filtered to exclude inactive customers. Raw Vault is supposed to capture source data as-is. Applying business logic before Raw Vault means (1) you can't audit source values, (2) you can't re-apply logic if business rules change, (3) you lose the single source of truth.

ShaDey Inc.'s Raw Vault includes a SAT_ORDER_PRICE with FINAL_PRICE (already discounted). Later, the discount calculation changes. You can't recalculate historical rows because you've lost the original PRICE and discount_rate values. You're forced to reload the entire source.

Raw Vault is "load as-is". Capture source fields unchanged: SAT_ORDER_PRICE stores (list_price, discount_rate, load_date) without calculation. Create Business Vault tables: SAT_ORDER_PRICE_GOLDEN computes FINAL_PRICE = list_price × (1 − discount_rate). If the formula changes, regenerate Business Vault; Raw Vault remains pristine. This preserves audit trail and enables re-computation.

A query joins multiple satellites to reconstruct a customer's state at a point in time (e.g., "What was the customer's address, credit limit, and loyalty status on 2024-06-15?"). Without a Point-in-Time (PIT) table, this requires a 4-way join with nested subqueries filtering by load_date. Each query is slow. Analysts bypass the raw vault and build their own snapshots, fragmenting logic.

A risk analyst needs to calculate customer credit exposure as of 2024-06-15. She must join SAT_CUSTOMER_CONTACT, SAT_CUSTOMER_ADDRESS, SAT_CUSTOMER_CREDIT_LIMIT (3 satellites) filtered by load_date <= 2024-06-15. The query takes 5 minutes. If she needs 100 such snapshots, that's 500 minutes.

Build PIT_CUSTOMER: one row per (customer_hk, effective_date) with foreign keys to each satellite's latest record as of that date. Pre-computed PIT tables collapse multi-way joins into a single lookup. SELECT * FROM PIT_CUSTOMER WHERE effective_date = 2024-06-15 AND customer_hk = 'xyz' returns all attributes instantly. Refresh PIT on each load. This trades storage for query speed; worth it for frequently queried entities.

A product belongs to multiple categories (primary, secondary). Instead of using a direct LINK_PRODUCT_CATEGORY, a team creates a bridge dimension (BRG_PRODUCT_CATEGORY) with weight factors. This adds complexity for a simple M:M relationship. The bridge table becomes a maintenance burden. For 1:M relationships (e.g., order has many line items), a bridge is unnecessary and adds overhead.

ShaDey Inc. has a 1:M (product : category). A well-meaning architect creates BRG_PRODUCT_CATEGORY with surrogate key and weights. Now every report must join through the bridge. A simple "count products per category" query becomes a 3-table join when a 2-table join suffices.

Use bridges only when true many-to-many relationships require weighting or allocation. For 1:M, use a direct link or fact table grain. For M:M that doesn't need weighting, a simple link suffices. LINK_PRODUCT_CATEGORY (product_hk, category_hk) handles the relationship cleanly without bridge overhead. Reserve bridges for complex cases (e.g., channel attribution with channel_weight).

An order references a customer that doesn't exist in HUB_CUSTOMER (e.g., source data has NULL customer_id or an orphaned key). The order row has no customer_hk, or customer_hk points to nothing. INNER JOINs silently drop these orders. Reports show wrong totals. Analysts don't know that 5% of orders are missing from customer dimension queries.

An e-commerce order from an unregistered guest has customer_id = NULL. Without a ghost record, FACT_SALES has customer_hk = NULL. A report "Total sales by customer" silently excludes guest orders. Revenue is understated by 8%.

Create ghost/unknown records in each Hub: HUB_CUSTOMER with customer_bk = 'UNKNOWN_CUSTOMER' (hk_customer = -1), HUB_PRODUCT with product_bk = 'UNKNOWN_PRODUCT' (hk_product = -2). When source data has NULL or orphaned keys, map them to the ghost record. OUTER JOINs now include all rows. Use a data quality rule to alert on excessive ghost record usage (indicates source data quality issues).

A same-as link (used for master data management) incorrectly merges two Hub records that should remain separate. Example: two customer hubs (customer_1 and customer_2) are linked as "same-as" because they share a phone number. Later, you discover they're different people (wife and husband). Undoing the same-as link is painful; downstream tables have already merged their data.

Two customer records (Acme Corp and Acme Industries) are linked as "same-as" based on a partial name match. Their orders, invoices, and contacts are merged in the Business Vault. Later, you discover they're competitors in different regions. Separating them requires re-processing months of data.

Same-as links should be manually curated or algorithmically approved with high confidence thresholds. Implement a business rule: same-as links require (1) identical customer name OR (2) matching tax ID AND address. Audit same-as link changes; log who approved them and why. If a link is later revoked, maintain a SAT_SAME_AS_AUDIT (effective_date, expiry_date, approved_by, reason) so downstream can re-run Business Vault excluding the revoked link. Treat same-as as a business decision, not a data quality automation.

A link connects 3+ hubs (LINK_PRODUCT_SUPPLIER_WAREHOUSE) but doesn't designate a driving key. When joined with a satellite (SAT_LINK_INVENTORY_LEVEL), the effectivity logic is ambiguous. A query "which warehouse supplied product X on 2024-06-15?" can't be answered because the satellite doesn't know which key drives the relationship (product? supplier? warehouse?).

LINK_PRODUCT_SUPPLIER_WAREHOUSE connects (product_hk, supplier_hk, warehouse_hk). SAT_LINK_INVENTORY_LEVEL tracks stock levels with load_date. A report "inventory levels by warehouse on 2024-06-15" requires knowing: is the satellite grain per warehouse? per product-warehouse? The ambiguity breaks temporal joins.

Designate a driving key: the entity for which the satellite is tracking changes. Document: "LINK_PRODUCT_SUPPLIER_WAREHOUSE has driving_key = warehouse_hk. SAT_LINK_INVENTORY_LEVEL is at warehouse grain (one row per warehouse per load). When joining, partition by warehouse_hk and take the latest row as of the query date." Alternatively, decompose into binary links: LINK_PRODUCT_WAREHOUSE (driving_key = warehouse) + SAT_LINK_SUPPLIER_WAREHOUSE (tracks supplier per warehouse). This clarity prevents temporal join errors.

Raw Vault is supposed to be immutable, but over time, various teams gain UPDATE/DELETE permissions for "troubleshooting" or "data corrections". One team fixes a typo, another applies a retroactive price adjustment, a third deletes duplicate rows. Raw Vault is now mutable in practice, and audit trail is destroyed. You can't explain historical data changes.

A customer service agent updates a customer phone number in Raw Vault directly (instead of correcting the source). A BI analyst deletes an erroneous sales order. An ETL developer resets a load_date. Six months later, an audit query shows contradictory results, and nobody can explain why.

Enforce immutability with database constraints: (1) REVOKE UPDATE, DELETE, TRUNCATE from all users except system administrators (required only for disaster recovery). (2) Create a correction workflow: errors are logged in a SAT_CORRECTION table with (error_description, source_record_id, correction_record_id, approved_by, correction_date). (3) Business Vault selects from Raw Vault LEFT OUTER JOIN SAT_CORRECTION to derive the corrected value. (4) Monitor database logs for any UPDATE/DELETE to Raw Vault; alert immediately. This preserves immutability and traceability.