topic Re: Datavault implemention on Databricks in Data Engineering

Datavault implemention on Databricks

ankit2604 — Fri, 16 Oct 2026 06:01:25 GMT

Hi everyone,

I'm building a Data Vault 2.0 architecture entirely natively in Databricks from scratch. We have specifically decided not to use dbt.

While Raw Vault ingestion is straightforward with Spark/Delta, I'm looking for architectural advice on complex Business Vault objects.

Would love to hear from anyone who has built a native framework from the ground up!

Thanks

Re: Datavault implemention on Databricks

balajij8 — Fri, 16 Oct 2026 07:00:18 GMT

You can leverage Declarative Pipelines SDP/LDP for building a native Data Vault on Databricks. For the Raw Vault layer, Streaming Tables are a good fit for Hubs and Links since they are strictly append only. The advantage comes with Satellites - use AUTO CDC with STORED AS SCD TYPE 2. It natively handles out-of-order events and maintains full history without forcing to write and maintain complex MERGE statements - which is challenge in Data Vault satellite loading. Make sure to enable Change Data Feed (CDF), deletion vectors and row tracking on all source tables, as these are prerequisites for downstream incremental refreshes.

When you move into the Business Vault - dealing with PIT tables, computed satellites, bridges and derived entities - you're inherently producing updates rather than pure appends. You can build these as Materialized Views within the same pipeline. Materialized Views support incremental refresh for joins, aggregations, window functions and CTE. It means a typical hour refresh cycle will only recompute changed results rather than running a massively expensive full scan across the vault.

At the storage and catalog level, avoid traditional partitioning if feasible. Hub keys generally have high cardinality and Business Vault query patterns evolve constantly, making Liquid Clustering (CLUSTER BY AUTO) a good choice. Ensure to declare your PK/FK constraints with the RELY keyword in Unity Catalog. It explicitly enables the query optimizer to perform join elimination along with helping in clean ERD documentation

In this approach, the entire vault - raw, business, and data marts stays in a single declarative pipeline per domain. The engine automatically evaluates the DAG and tracks all task dependencies, completely eliminating the need for dbt or any external orchestration tools. More details here

Re: Datavault implemention on Databricks

Satyasai — Fri, 16 Oct 2026 13:48:30 GMT

@ankit2604

Here is the combined, comprehensive architectural guide for building a Native Data Vault 2.0 Framework in Databricks (without `dbt`), incorporating modular Business Vault design, metadata-driven orchestration, incremental processing, dependency management, and data quality.

1. Core Architectural Strategy

A. Storage & Layout Optimizations

Delta Lake & Unity Catalog: Use Unity Catalog schemas (`rv` for Raw Vault, `bv` for Business Vault, `gold` for Information Marts).
Liquid Clustering (`CLUSTER BY`): Replaces legacy Z-Ordering and partitioning.
Hubs / Links: `ALTER TABLE rv.hub_customer CLUSTER BY (hk_customer_id);`
Satellites: `ALTER TABLE rv.sat_crm_customer CLUSTER BY (hk_customer_id, load_date);`
PIT Tables: `ALTER TABLE bv.pit_customer CLUSTER BY (snapshot_date, hk_customer_id);`
Bridge Tables: `ALTER TABLE bv.bridge_sales CLUSTER BY (snapshot_date, hk_order_id);`

Ghost Records: Insert a standard zero/ghost record (`-1` or `0x00000000000000000000000000000000`) into every Hub, Link, and Satellite during initialization to prevent `LEFT JOIN` record loss in downstream Marts.

2. Business Vault Implementation Patterns

A. Point-in-Time (PIT) Tables

PIT tables flatten non-equi joins across Satellites. Store only Hash Keys, Snapshot Dates, and Satellite `LOAD_DATE` values in PIT tables.

sql
CREATE OR REPLACE MATERIALIZED VIEW bv.pit_customer AS
SELECT
h.hk_customer_id,
sd.snapshot_date,
MAX(sat_crm.load_date) AS hk_sat_crm_customer_load_date,
MAX(sat_erp.load_date) AS hk_sat_erp_customer_load_date
FROM rv.hub_customer h
CROSS JOIN bv.dim_snapshot_date sd
LEFT JOIN rv.sat_crm_customer sat_crm
ON h.hk_customer_id = sat_crm.hk_customer_id
AND sat_crm.load_date <= sd.snapshot_date
LEFT JOIN rv.sat_erp_customer sat_erp
ON h.hk_customer_id = sat_erp.hk_customer_id
AND sat_erp.load_date <= sd.snapshot_date
GROUP BY h.hk_customer_id, sd.snapshot_date;

B. Bridge Tables & Derived Satellites

Bridge Tables: Flatten multi-hop Link joins (Customer $\rightarrow$ Order $\rightarrow$ Store) at snapshot dates into physical tables to accelerate Star Schema queries.
Derived / Computed Satellites: Compute business calculations, text standardization, and metric derivation cleanly separated from Raw Vault Satellites.

3. Incremental Processing Strategy

To maintain scalability without `dbt`, leverage Delta Lake primitives and Spark Structured Streaming:

A. Windowed Merge for PIT Tables

Avoid full PIT rebuilds by executing rolling window merges (e.g., last 30 days) using Delta `MERGE`:

sql
MERGE INTO bv.pit_customer AS target
USING (
SELECT
h.hk_customer_id,
sd.snapshot_date,
MAX(sat_crm.load_date) AS hk_sat_crm_customer_load_date,
MAX(sat_erp.load_date) AS hk_sat_erp_customer_load_date
FROM rv.hub_customer h
CROSS JOIN bv.dim_snapshot_date sd
LEFT JOIN rv.sat_crm_customer sat_crm
ON h.hk_customer_id = sat_crm.hk_customer_id
AND sat_crm.load_date <= sd.snapshot_date
LEFT JOIN rv.sat_erp_customer sat_erp
ON h.hk_customer_id = sat_erp.hk_customer_id
AND sat_erp.load_date <= sd.snapshot_date
WHERE sd.snapshot_date >= CURRENT_DATE() - INTERVAL 30 DAYS
GROUP BY h.hk_customer_id, sd.snapshot_date
) AS source
ON target.hk_customer_id = source.hk_customer_id
AND target.snapshot_date = source.snapshot_date
WHEN MATCHED THEN UPDATE SET
WHEN NOT MATCHED THEN INSERT ;

B. Delta Change Data Feed (CDF)

Enable `delta.enableChangeDataFeed = true` on Raw Satellites. Micro-batch pipelines can consume `readStream` from CDF to selectively recalculate updated PIT keys without scanning static data.

4. Native Dependency Management & Metadata Orchestration

Option 1: Declarative Lakeflow Pipelines (Recommended)

Databricks Lakeflow Pipelines automatically build the execution DAG based on table/view references:

python
import dlt
from pyspark.sql import functions as F

1. Hub
@dlt.table(name="hub_customer")
def hub_customer():
return spark.readStream.table("staging_customers").select("hk_customer_id", "customer_id", "load_date", "record_source")

2. Derived Satellite
@dlt.table(name="sat_customer_cleansed")
def sat_customer_cleansed():
return (
dlt.read_stream("sat_crm_customer")
.withColumn("standard_email", F.lower(F.trim(F.col("email"))))
)

3. PIT Table
@dlt.materialized_view(name="pit_customer")
def pit_customer():
return spark.sql("""
SELECT h.hk_customer_id, sd.snapshot_date, MAX(s.load_date) AS hk_sat_crm_load_date
FROM live.hub_customer h
CROSS JOIN live.dim_snapshot_date sd
LEFT JOIN live.sat_customer_cleansed s
ON h.hk_customer_id = s.hk_customer_id AND s.load_date <= sd.snapshot_date
GROUP BY h.hk_customer_id, sd.snapshot_date
""")

Option 2: Metadata-Driven Configuration Engine

If using standard Workflows, decouple transformation logic from orchestration using a metadata JSON/YAML schema driving PySpark generators:

json
{
"execution_tiers": [
{"tier": 1, "type": "Hubs_and_Links", "objects": ["hub_customer", "hub_order", "link_customer_order"]},
{"tier": 2, "type": "Raw_Satellites", "objects": ["sat_crm_customer", "sat_erp_order"]},
{"tier": 3, "type": "Business_Vault", "objects": ["pit_customer", "bridge_sales"]},
{"tier": 4, "type": "Information_Marts", "objects": ["dim_customer", "fact_orders"]}
]
}

5. Data Quality & Compliance Rules

Replaces external `dbt-expectations` with native Databricks enforcement features:

Lakeflow Expectations:
python
@dlt.table(name="sat_crm_customer")
@dlt.expect_or_fail("valid_hash_key", "hk_customer_id IS NOT NULL")
@dlt.expect_or_drop("valid_load_date", "load_date <= CURRENT_TIMESTAMP()")
@dlt.expect("valid_email_format", "email LIKE '%@%'")
def sat_crm_customer():
return spark.readStream.table("staging_crm_customers")

Unity Catalog Schema Constraints:
sql
ALTER TABLE rv.hub_customer ALTER COLUMN hk_customer_id SET NOT NULL;
ALTER TABLE rv.sat_crm_customer ADD CONSTRAINT valid_load_date CHECK (load_date > '2000-01-01');

Audit & Alerting: Query `system.access.audit` and Lakeflow event logs (`event_log(pipeline_id)`) to trigger SQL Alerts for orphan keys or non-conforming records.

Re: Datavault implemention on Databricks

Jen_Evangelista — Fri, 16 Oct 2026 16:43:01 GMT

Great points from @balajij8 on the SDP/LDP approach. I've worked on a few Data Vault implementations, and these are the things that tend to bite when the Business Vault grows:

1. Hash keys: standardize before hashing. Trim, upper-case and use a fixed delimiter for composite business keys, e.g. sha2(concat_ws('||', upper(trim(k1)), upper(trim(k2))), 256). Put this in one shared function, otherwise hubs and links built by different teams stop joining.

2. Satellites vs AUTO CDC: Data Vault satellites should only insert when the payload actually changes. With AUTO CDC ... STORED AS SCD TYPE 2, track history on the descriptive columns only, so technical columns (load timestamps, file names) don't create fake versions.

3. PIT tables: decide the snapshot grain early (daily, hourly). A PIT at the wrong grain is the most expensive object in the vault, even with incremental MV refresh.

4. Metadata-driven generation: keep hubs/links/sats defined in config (YAML or a control table) and generate the pipeline code from it. That's what replaces dbt's model management, and Declarative Pipelines handles the dependency DAG.

Happy to go deeper on any of these.

Re: Datavault implemention on Databricks

ankit2604 — Sun, 18 Oct 2026 04:37:27 GMT

Thanks @Jen_Evangelista for sharing your findings, could you please share some architectural overview of how you implemented it on databricks.

Re: Datavault implemention on Databricks

ankit2604 — Sun, 18 Oct 2026 06:59:45 GMT

Hi @balajij8 Thanks for the insights. I have a question - As per my understanding the Data vault should only support append only mechanics. If we go with the features that you mentioned like AUTO CDC, CDF, doesn't it go against the core concept of data vault? I am just wondering, will appreciate your feedback here to better understand what will be the perfect way to design the architecture.

Re: Datavault implemention on Databricks

Jen_Evangelista — Mon, 19 Oct 2026 21:51:50 GMT

Hi ankit2604, I would structure one domain like this:

Source events -> replayable Bronze Delta -> Raw Vault hubs/links/satellites -> Business Vault derived satellites, PIT and bridges -> information marts.

This is a reference design proposal, not a client implementation I can substantiate here.

For your append-only concern, keep the Raw Vault loading contract explicit. A CDC feed describes source changes; consuming it does not require overwriting raw history. If you require physically insert-only satellites, insert immutable versions and derive end dates at read time or in a downstream object. AUTO CDC SCD2 maintains history, but it should not automatically be treated as equivalent to that insert-only satellite contract.

For the Business Vault, define the PIT grain first, for example customer plus daily snapshot. Track the affected keys and dates for late arrivals, with a backfill path; a fixed 30-day repair window is safe only if the lateness contract supports it. Verify materialized-view incremental-refresh eligibility for each query.

Keep object definitions and dependencies in versioned metadata, with small, inspectable transformation functions. Test replay, out-of-order events and late corrections against expected mart results before expanding the framework.

Also, my earlier concat_ws hash example needs refinement: preserve NULLs and component boundaries in the serialization before hashing, rather than relying on a delimiter alone.

References:
https://docs.databricks.com/aws/en/ldp/cdc
https://docs.databricks.com/aws/en/ldp/incremental-refresh

Re: Datavault implemention on Databricks

ankit2604 — Wed, 21 Oct 2026 16:48:11 GMT

how to handle duplicates(arriving at different time and not in the same batch) when data is ingested from stage/bronze to hubs? LDP/DLT does not support merge operations and what if i have multiple source table ingesting into a single hub. Any thoughts or suggestion on this? @Jen_Evangelista @balajij8 @Satyasai

Re: Datavault implemention on Databricks

balajij8 — Wed, 21 Oct 2026 17:10:37 GMT

You can check AUTO CDC. Hubs are streaming tables loaded with AUTO CDC INTO KEYS (hub_hash_key) SEQUENCE BY load_datetime STORED AS SCD TYPE 1 - it's an upsert by design, so duplicates arriving in different batches are deduplicated against the target's persistent state and late/out-of-order records are dropped automatically More details here

Use COLUMNS * EXCEPT (load_datetime, load_source) so hub metadata keeps its first seen values even when later duplicates arrive - More details here

For multiple sources into one hub - union all source key extractions (with identical hashing) into a single stage layer view and point one AUTO CDC flow at it. More details here

You can build hubs as materialized views as dedup is inherent and incrementally refreshable if you plan to run the pipelines every 4 hours or more. You can reserve AUTO CDC for satellites More details here

Re: Datavault implemention on Databricks

Jen_Evangelista — Wed, 21 Oct 2026 17:31:06 GMT

Hi @ankit2604! Great questions — this is one of the trickiest parts of hub ingestion. Let me break it down:

1. Duplicates across batches (late arrivals)

Hubs are insert-only, so you don’t need MERGE — you need an efficient “insert if not exists.” Use LEFT ANTI JOIN on Delta (much better than NOT IN at scale):

sql

INSERT INTO hub_customer
SELECT s.hash_key, s.business_key, s.load_date, s.record_source
FROM (
  SELECT 
    sha2(concat_ws('||', coalesce(business_key, '')), 256) AS hash_key,
    business_key, load_date, record_source,
    ROW_NUMBER() OVER (PARTITION BY business_key ORDER BY load_date) AS rn
  FROM stage_customer
) s
LEFT ANTI JOIN hub_customer h ON s.hash_key = h.hash_key
WHERE s.rn = 1

Make sure hash_key is Z-ordered on the hub table so Delta skips files efficiently.

2. DLT/Declarative Pipelines — APPLY CHANGES INTO is your friend

DLT does not expose MERGE directly, but APPLY CHANGES INTO handles deduplication + sequencing natively:

python

dlt.apply_changes(
  target="hub_customer",
  source="stage_customer_clean",
  keys=["hash_key"],
  sequence_by=col("load_date"),
  stored_as_scd_type=1
)

SCD Type 1 on a hub is effectively insert-only + idempotent (the business key never changes, so it just confirms the row). Alternatively, run hub inserts as a regular Delta job outside DLT — that gives you full MERGE control.

3. Multiple sources → single hub

Union all sources before inserting, then pick the earliest per business key:

sql

WITH all_sources AS (
  SELECT sha2(concat_ws('||', coalesce(customer_id::string, '')), 256) AS hash_key,
         customer_id::string AS business_key, load_date, 'CRM' AS record_source
  FROM stage_crm
  UNION ALL
  SELECT sha2(concat_ws('||', coalesce(customer_id::string, '')), 256),
         customer_id::string, load_date, 'ERP' AS record_source
  FROM stage_erp
),
deduped AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY hash_key ORDER BY load_date) AS rn
  FROM all_sources
)
INSERT INTO hub_customer
SELECT hash_key, business_key, load_date, record_source
FROM deduped d
LEFT ANTI JOIN hub_customer h ON d.hash_key = h.hash_key
WHERE d.rn = 1

If sources have priority rules, add that to the ORDER BY inside ROW_NUMBER(). Hope this helps!

Re: Datavault implemention on Databricks

Satyasai — Thu, 22 Oct 2026 06:00:09 GMT

Hi @ankit2604

Your statement is touching on a fundamental design principle of Data Vault 2.0: a hash key enforces uniqueness, but when business keys collide across independent systems, you need a business key collision code to avoid catastrophic collisions

To truly understand the situation and know when you actually "need" a BKCC, let's explore the difference between a true duplicate and a namespace collision.

1. The Confusion: "Do we want duplicates in a Hub?"
In a true Data Vault 2.0 design, you should never want to have duplicate records of the same entity in a Hub.
Say John Doe registers on your CRM system twice, creating two rows of data with the same ID CUST-100. You want these two rows to collapse into a single hash key, so John Doe only exists once in your Customer Hub. This is a situation where you would NOT use a BKCC.

2. When You Need BKCC (Namespace Collisions)
You need a BKCC not when you want duplicates of the same entity, but when entities from different systems happen to have the exact same ID string
For example, let's say you have:
Regional Invoices
Region North Database: Issues an invoice numbered INV-2026-001 for a software purchase.
Region South Database: Also independently issues an invoice numbered INV-2026-001 for a hardware purchase.
If you naively hash the raw business_key (INV-2026-001), both records produce the exact same hash key
When loaded into a single Hub_Invoice, the data warehouse will see the second record as a duplicate and either reject it or overwrite it, corrupting Region North's invoice data with Region South's invoice data.

3. How BKCC Solves This
To allow both invoices to co-exist as valid, separate entities in your Hub without any collisions, you prepend a Business Key Collision Code (the source system or region in this example) to the hash formula:
HashKey=SHA256(BKCC∥Delimiter∥BusinessKey)

North Invoice:
SHA256("NORTH"∥"#"∥"INV-2026-001")
→ Hash: 0xABC123...

South Invoice:
SHA256("SOUTH"∥"#"∥"INV-2026-001")
→ Hash: 0xXYZ789...
Since the hash keys are now entirely different, both records can peacefully exist as separate rows in your Hub, preserving the integrity of your enterprise warehouse.

Re: Datavault implemention on Databricks

ankit2604 — Thu, 22 Oct 2026 06:09:08 GMT

Thanks @Satyasai for the answer. Consider the below situation:
Lets say the source is pushing the same set of data in the future, what then? How do I perform a dedupe logic using LDP since it does not support the SQL Merge Operation. One option here is to use Materialized views to group and select the first record always, but i am not really inclined towards MVs for Hubs.

Re: Datavault implemention on Databricks

Satyasai — Thu, 22 Oct 2026 07:20:44 GMT

HI @ankit2604,
I am not able to get what you are saying , when you the same business key is coming from different batch, it will not load into hub as the logic for the hub is such a way that same only one business key per record should exists in hub and flow is as below, if you check the distinct business keys are already exists or not in target, it will not allow dauplicates into hub.

if this does not resolve/help your issue, connect me on linkedin, https://www.linkedin.com/in/satyasai-gurazada

Re: Datavault implemention on Databricks

data_pulse — Thu, 22 Oct 2026 10:46:46 GMT

@ankit2604

I tested a few approaches on how duplicate business keys can be handled across ingestion batches while maintaining an insert-only Data Vault Raw Hub.

I compared three approaches using five incremental batches covering duplicate keys, late arrivals, replays, and CRM/ERP business-key identities.

Observations from my test:

Lakeflow AUTO CDC (SCD Type 1): Successfully prevented duplicate Hub keys, but when an existing business key appeared again in a later ingestion with updated source information, it overwrote the original Hub metadata, including the load timestamp and record source. The tests confirmed three such updates. This makes SCD Type 1 useful for maintaining the latest record, but not ideal for a strictly insert-only Raw Hub where original metadata must remain unchanged.
Insert-only Delta MERGE: Using only WHEN NOT MATCHED THEN INSERT preserved existing Hub records and inserted new business keys. It passed all five batches without modifying previously loaded records.
Lakeflow append flow with dropDuplicates(["hub_hash_key"]): Also passed all five batches, maintaining unique Hub keys and preserving original metadata. Deduplication continued working across normal incremental pipeline updates without explicit MERGE logic.
Watermark-based deduplication: Initially worked but failed a separate watermark-expiry test. Once state was evicted, an existing key was inserted again, while a genuinely new late-arriving key was dropped.

Based on these results, I'd suggest two options for your Raw Hub:

Insert-only MERGE if you want explicit control over new-key insertion and deterministic handling of duplicate source records.
Lakeflow append flow with unwatermarked dropDuplicates if you prefer a declarative streaming approach without custom MERGE logic.

I would avoid watermark-based deduplication when business keys must remain unique indefinitely.

Also, define your business-key normalization and BKCC rules upfront, especially when multiple source systems can produce the same business key.

These findings specifically address Raw Hub loading and duplicate prevention. For the broader Business Vault architecture, the earlier suggestions from our community members around PIT tables, bridges, derived Satellites and metadata-driven Lakeflow pipelines are very much relevant.

Re: Datavault implemention on Databricks

Louis_Frolio — Thu, 22 Oct 2026 19:18:04 GMT

Hello @ankit2604, I took a look at both internal and external documentation and here is what I found.

Thanks @balajij8, @Satyasai, @Jen_Evangelista and @data_pulse for doing most of the work here. The shape they've described is right: streaming tables for the Raw Vault, AUTO CDC for satellites, materialized views for PIT and bridge tables, Liquid Clustering underneath. What I'll add is one correction, a few caveats I checked against the docs, and one untested idea. The theme tying it together: keep the source's CDC mechanism separate from the physical contract of each vault object. Lakeflow gives you the dependency graph and declarative CDC. It doesn't define your table contracts, dedup scope, key namespaces or replay tests for you.

Your append-only concern

Your instinct is right, and Jen's framing holds. Hubs and links are insert-only, full stop. Satellites are insert-only in the sense that every change is a new row, and Data Vault 2.0 treats end dates as derivable, which is why PIT tables exist. AUTO CDC with STORED AS SCD TYPE 2 physically updates __END_AT on the previous version, so it's a managed SCD target, not a pure insert-only satellite. You're trading a purity rule for correct late-data handling and a lot less code. That's a fine trade as long as you make it on purpose and write it down.

Hub dedup

A small correction first: COLUMNS * EXCEPT (...) on AUTO CDC chooses which columns exist in the target. It doesn't freeze first-seen values; exclude load_datetime and record_source and the hub simply won't have them. data_pulse's test showed what SCD Type 1 really does with a repeated key: latest arrival wins and overwrites load date and record source. That's documented behavior, so SCD1 isn't an insert-only hub.

Three honest options for a hub that stays unique across batches and keeps its original metadata:

data_pulse's append flow with unwatermarked dropDuplicates(["hub_hash_key"]). It works precisely because nothing is ever evicted from state. The cost is a state store holding every hub key forever (a full refresh rebuilds it), and a watermark isn't a fix, since an expired key gets admitted again. Fine for most hubs, but size it.
An insert-only MERGE outside the pipeline. You're right that a declarative pipeline can't run MERGE, but nothing stops you from loading hubs as regular Delta tables in a SQL task of the same Lakeflow Job (reduce each batch to one row per key, then WHEN NOT MATCHED THEN INSERT with no WHEN MATCHED), and running the pipeline as the next task. DML against pipeline-managed streaming tables is meant for things like GDPR deletes, not loading, so keep the hub outside the pipeline if you go this way.
AUTO CDC SCD Type 1 with the sequence inverted. I haven't tested this, so treat it as a test-harness candidate only. AUTO CDC is idempotent on keys plus sequence and drops anything with a lower sequence than what's stored. Compute -unix_micros(load_datetime) AS seq_first_wins in staging and SEQUENCE BY STRUCT(seq_first_wins, record_source), and the first arrival holds the highest sequence, so later replays are dropped and first-seen metadata survives. Clever, which means it needs a comment block and data_pulse's five-batch test before you trust it.

For multiple sources, balajij8 has it right: normalize and hash identically, UNION ALL into one staging view, pick one deterministic winner per key with an explicit priority and tie-breaker, and point one flow at it. Two more things, one from the docs and one from Satyasai. An AUTO CDC target can only be fed by other AUTO CDC flows, so you can't mix an append flow onto the same hub. And decide up front whether CRM's CUST-100 and ERP's CUST-100 are the same entity (that's the BKCC call), serialize nulls and component boundaries deliberately as Jen described, because the hash makes all of it permanent.

Satellites

AUTO CDC SCD Type 2 fits when the source gives you a reliable change sequence. Sequence on that, not arrival time, add a deterministic tie-breaker where timestamps collide, and use TRACK HISTORY ON for payload columns only so load timestamps and file names don't mint fake versions. If your sources carry an effective date distinct from the load date, look at the new bitemporal mode (SYSTEM SEQUENCE BY, currently Beta) before hand-rolling applied dates.

Business Vault

Materialized views are the right tool for PIT, bridge and derived satellites, with conditions. Incremental refresh needs serverless and a supported query shape, and it falls back to a full recompute quietly, which on a large PIT is the most expensive thing in the vault. Run EXPLAIN CREATE MATERIALIZED VIEW on the real query, watch the Incrementalization column after each update, and put REFRESH POLICY INCREMENTAL STRICT on the expensive ones so a surprise recompute fails loudly instead of running up a bill. The classic PIT (hub cross-joined to a date spine, non-equi join on load_date <= snapshot_date, then MAX) is exactly the plan I'd check. The docs list inner and outer joins, GROUP BY, window functions and QUALIFY as incrementalizable and say nothing about non-equi conditions, so don't assume. A rolling window in the WHERE clause (current_date() is allowed there) or a QUALIFY-based latest-version pattern may incrementalize where the cross join doesn't.

Two precision points on earlier advice. Row tracking is required for several incrementalizable operators, and deletion vectors and CDF are recommended on MV sources, but none of the three is a prerequisite for hubs or for AUTO CDC itself. And Unity Catalog PK/FK constraints are informational; RELY tells the optimizer it may eliminate joins on the assumption the constraint holds, so only add it once your hub load actually guarantees uniqueness. Define the PIT grain first, and treat a fixed repair window (30 days or otherwise) as safe only if it matches your sources' real lateness.

The Databricks Data Vault blog balajij8 linked is from 2023 and uses DLT-era syntax. Read it for the modeling pattern, not the code.

If I were building this today: keep hubs and links deliberately boring (first or second option above), let AUTO CDC run the satellites with the purity trade made consciously, and treat every PIT and bridge MV as unproven until EXPLAIN and the event log say it refreshes incrementally.

Regards, Louis.