SCD2 tables are the primary Business Vault output today. They materialize functional timelines from raw Data Vault satellite history.

On the .hbv canvas, link one parent hub to the SCD2 table (drag a Linked Hub, or set Parent hub on the table dialog). That line is the grain: it shows which hub the timeline belongs to and where the business key(s) live. Satellite derivatives remain the history feeds. Optional Include hub business keys copies those keys onto the SCD2 stream/table; the hub link is still useful when keys stay on the hub.

The hash-key column type and length come from that hub’s Data Vault configuration (BINARY + MD5 → BINARY(16) on SingleStore, not unbounded VARBINARY). A Linked Hub is enough to resolve the grain even when a satellite has no parent hub of its own.

Type 1 vs Type 2 and hybrid attributes

Business Vault SCD2 tables are pure SCD Type 2: each functional change produces a version row with valid_from / valid_to (or your configured validity columns). The plugin does not support a per-column Type 1 mode that rewrites every historical version when a “current” value changes.

That is intentional. BV SCD2 is organizational memory made consumable (harmonized names, multi-satellite merge, business-effective intervals). Hybrid Type 1 + Type 2 presentation rules usually belong further downstream.

Need Recommended approach

Historized attributes (as-of correct)

Map them on the SCD2 table (Type 2).

Always-current attributes (e.g. current sales rep on old facts)

Keep Type 2 history clean; resolve “current” at mart or query time by joining the open SCD2 row, a latest satellite, or a separate BV current table/view (see Business Vault SQL views and tables). Prefer hybrid SCD in the dimensional model when product owners want Type 1 and Type 2 columns on one dimension.

Mix Type 1 and Type 2 in the same BV SCD2 table

Not a field-mapping feature. Mixing would make some columns look historical while they are not, weaken audit-friendly intermediate history, and is expensive under incremental watermarks.

Type 1 / Type 2 modes on a PIT table

Wrong layer. PIT tables store hub grain × snapshot dates with satellite load-timestamp pointers only. Compose attributes (including always-current ones) when joining raw or BV tables. See Business Vault PIT.

In short:

  1. Prefer pure Type 2 BV SCD2 for mapped attributes.

  2. Prefer a separate current-state object (or open-row filter) for always-current values.

  3. Prefer hybrid Type 1 + Type 2 in the dimensional / information mart layer for reporting rules.

  4. Treat hybrid Type 1 inside a historized BV SCD2 table as a last resort outside this builder, not core plugin behaviour.

Dialog help (Hop GUI Help on the SCD2 table dialog): SCD2 table dialog.

What gets generated

For each SCD2 table the plugin builds a Hop pipeline that:

  1. Reads satellite history via TableInput SQL (grain + functional timestamp + attributes), ORDER BY hash key in Java String.compareTo order (Postgres COLLATE "C", SQL Server BIN2, MySQL BINARY) so SortedSchemaMerge sees a stream that is already sorted the way Hop compares strings. Locale collations such as Postgres en_US.utf8 sort 10- before 1- and would split the same key across satellite legs. The vault record-source / source-indicator column is not selected when a satellite has Store record source indicator off (VaultSpeed-style mono-source sats). Multi-satellite pipelines never read that physical column; merge identity is the synthetic _bv_source value.

  2. Adds source indicators when multiple satellites feed one BV table. When a satellite omits the physical column, a Constant supplies the BV record-source value from the satellite config (or the satellite name).

  3. Merges streams (multi-satellite) with SortedSchemaMerge

  4. Repeats sparse attribute columns across merged rows

  5. Computes validity bounds with Analytic Query (LAG/LEAD)

  6. Applies open-interval sentinels with If Null

  7. Collapses duplicate timestamps with Group By

  8. Loads the Business Vault target table with TableOutput (full rebuild) or runs the incremental write path below

Use Debug or Show build pipeline on an SCD2 table in the .hbv editor to inspect the generated pipeline before running a workflow.

Hash-key partitions (large full rebuilds)

When a satellite is too large to ORDER BY hash key and load date in one pass, set Hash-key partitions to 4, 8, or 16 on the SCD2 table. Full rebuild then:

  1. Truncates the BV target once with a workflow SQL action (TRUNCATE TABLE via the Hop database dialect).

  2. Generates partition numbers 0 .. N-1 and runs the SCD2 pipeline once per part, passing '${PARTITION_COUNT}' and '${PARTITION_NUMBER}'.

  3. Filters each satellite TableInput with a first-byte modulus of the parent hash key (BINARY: first octet; HEX: first two hex characters; STRING: first dash-separated token). Every version of a hub/link key stays in one partition. STRING/HEX hash keys still ORDER BY with a Java-matching binary collation inside each slice.

  4. Writes with TableOutput truncate off, so later partitions append.

Show build pipeline opens the parameterized SCD2 pipeline, the partition driver, and the wrapper workflow. Running the inner pipeline alone with defaults loads partition 0 of N.

Constraints:

  • Full rebuild only (Incremental already filters by watermark).

  • Target load mode is honored: Table Output and Native bulk append after the SQL truncate; Staging file writes one CSV set per partition (…-${PARTITION_NUMBER}-${Internal.Transform.CopyNr}.csv) then bulk-loads those files in the wrapper workflow.

  • Sequential partition execution (lower peak memory, not parallel wall-clock).

Integration test: integration-tests/tests/multi-satellite-bv/update-customer-360-partitioned.hwf uses customer-360-partitioned.hbv (4 parts) and the same golden current-state dataset as the unpartitioned full rebuild.

Incremental build mode

Set Build mode to Incremental on the SCD2 table when you want to append new functional versions instead of truncating and reloading the BV table on every run.

Incremental pipelines extend the shared analytic tail with:

  1. Watermark resolve — at pipeline generation time the plugin queries the Business Vault target with SELECT MAX(watermark) … (BV connection only; null → open-start sentinel in Java)

  2. Parameter Generate Rows — param_incremental_watermark (Generate Rows, limit 1) holds the Timestamp value; param_open_row_filter holds the open-end sentinel (and watermark when DV/BV share a connection). Generate Rows is used instead of Constant because Constant only enriches incoming rows and would emit nothing as a pipeline start.

  3. Satellite filter — each DV-connected TableInput uses JDBC positional parameters (source_ts > ?) with the watermark transform as its Insert data from stream. SQL stays dialect-neutral and never references BV tables

  4. Open target baseline — BV-connected TableInput with valid_to = ? (open-end bound from the param transform). When DV and BV share the same Hop connection name, hash keys are further restricted with IN (SELECT … FROM sat WHERE ts > ? UNION …) — one watermark ? per satellite leg (JDBC does not reuse bind values; the param transform repeats the same watermark once per leg). When connections differ, all open BV rows are read

  5. Watermark attach — a stream Constant (after the analytic chain) adds _incremental_watermark to every computed row

  6. Incremental row filter — FilterRows keeps computed rows where functional_timestamp > _incremental_watermark

  7. Append — TableOutput with truncate=false

  8. Close open version — Update sets valid_to = valid_from on the prior open row (match hash key + open valid_to) before new open versions are inserted

Watermark field resolution:

  1. Per-table Incremental watermark field override (optional)

  2. Per-table Functional timestamp field

  3. Model-level functional timestamp default

  4. Load-date fallback from Data Vault configuration

The watermark is always read from the Business Vault target table (MAX(watermark_field)), matching the PIT incremental pattern. DV satellite history may live on a different database connection than the BV target.

On the first incremental run against an empty BV table the sentinel (1900-01-01 00:00:00) applies, so behaviour matches a full rebuild. Later runs process only satellite deltas and close prior open rows in place.

Integration test: integration-tests/tests/multi-satellite-bv/update-customer-360-incremental.hwf loads four DV satellite waves, runs Business Vault Update once on customer-360-incremental.hbv, then validates current open rows against the same golden dataset as the full-rebuild fixture. Multi-run close-and-append behaviour is covered by unit tests in BvScd2PipelineSupportTest. Hash-key partitioned full rebuild: update-customer-360-partitioned.hwf / customer-360-partitioned.hbv (4 parts, same golden).

Calculations (SQL expressions)

After collapse, each SCD2 table can compute extra target columns with deterministic SQL scalars. This is the replacement for per-entity CASE / COALESCE / CAST in dbt-core SCD2 models.

  • Expressions run after Group By collapse and before Table Output / incremental write.

  • They do not create new versions. Mapped satellite attributes remain the grouping keys.

  • Target names must be new columns (they cannot overwrite mapped attributes, hash keys, hub business keys, or validity fields). Raw mapped columns stay on the table so incremental runs can recompute expressions, unless Load is No on the mapping (calculation-only; full rebuild only).

  • Optional Include hub business keys (General tab) left-outer Merge Joins parent-hub BK columns onto the collapsed stream immediately before SQL calculations. They are identity fields: available for calculations, but they do not open new versions. Load hub business keys (default on) writes them to the BV table; uncheck to keep them on the stream only. The SCD2 merge / repeat / incremental chain is unchanged. The Lineage tab lists every loaded column, including hub business keys when those are loaded.

  • Syntax is SQL: CASE WHEN …, COALESCE, CAST(x AS VARCHAR(n)). x :> VARCHAR(n) and x::TYPE are accepted. MySQL / SingleStore-style CONCAT, HEX, UNHEX, MD5, DATE_FORMAT, and TO_DATE are available for migration.

  • Not allowed: NOW(), CURRENT_TIMESTAMP, RANDOM, subqueries, aggregates, Jinja.

Configure them on the SCD2 table Calculations tab. Add / Edit opens the SQL expression editor (fields + CASE / COALESCE / CAST patterns, large SQL widget, type and description). Generated production pipelines insert an SQL Expression transform (calculate_<table>) after collapse_* with a copy of the expressions.

SCD2 table dialog — Calculations tab on customer_360_bv
SQL Expression transform dialog — Keep input fields

The SQL Expression transform can also link a Business Vault model file and SCD2 table. When both are set, it loads calculations from that table at runtime (inline expressions are ignored). Production generate still copies expressions so load pipelines do not depend on opening the .hbv at run time. Help for the transform: SQL Expression.

Integration fixture: integration-tests/tests/scd2-calculations/update-scd2-calculations.hwf — deleted-flag CASE plus CAST('Default value' AS VARCHAR(720)) on a single-satellite SCD2 table, golden current-state after initial and update loads. The same workflow also builds sat_customer_calc_hub_bv (issue #153): hub customer_id on the stream/table, deleted_flag mapped with Load=No.

Tests

When the table is ready, use Generate calculation unit test on the SCD2 table (canvas context menu or the Tests tab).

SCD2 table dialog — Tests tab for customer_360_bv

That creates:

SCD2 table context menu — Generate calculation unit test on customer_360_bv

  • A slim pipeline Dummy collapse_sample → SQL Expression (linked to this table) → Dummy calculated_out

  • Input data set bv-scd2-<table>-collapse and golden data set bv-scd2-<table>-calculated

  • A Hop pipeline unit test that injects at collapse_sample and compares golden output at calculated_out

  • A capture pipeline: the SCD2 build up to Group By, then Reservoir Sampling (100 rows), then the linked SQL Expression. Optional: run it once against the warehouse to fill the data sets

Sample warehouse rows confirmation — 100 reservoir-sampled collapsed rows
Generated calculation unit-test pipeline — collapse_sample to SQL Expression (linked to the SCD2 table) to calculated_out

Production SCD2 loads are not reservoir-sampled. After capture, re-run the unit test from the pipeline Testing toolbar (no database).

Calculation unit test completion report with pipeline

Check model still compiles expressions. Legacy column-level / CSV tests stored on older .hbv files still run if present.

Migrating from dbt SCD2 SQL

Keep using Import dbt models for arbitrary SQL business tables. For SCD2-like models:

  1. Model the raw satellites on .hdv (Hop-managed or External read-only).

  2. Create a BV SCD2 table with field mappings, including CASE inputs such as deleted flags.

  3. Copy SELECT-list expressions onto Calculations.

  4. Generate a Hop pipeline unit test and sample collapsed rows once.

  5. Optional: a BV SQL view that projects the calculated columns without the raw flags.

Source queries (other tables, views, or SQL)

When a satellite-shaped input is not on the Data Vault target — for example a corrective view on another database — add a Source query on the BV canvas and hop it to the SCD2 table. The Data Vault model still has one target database; the source query is the BV-side exception.

Linked satellites and source queries are listed on the SCD2 Derivatives tab. Customer 360 links sat_customer_demo, sat_customer_contact, sat_customer_address, and sat_customer_prefs.

SCD2 table dialog — Derivatives tab with the four customer satellites

Each generated TableInput uses that source’s Hop connection. Table/view names are read directly. Authored SQL has -- line comments and /* */ block comments stripped, then is wrapped as a subquery so the plugin can add partition WHERE and hash-key/date ORDER BY outside the inner statement.

On the source query, set Hash key (source column) to the physical name (burger_hkey) and Hash key (hub field) to the hub grain name when they differ (hub_burger_hkey). The pipeline maps that with Select Values after Table Input; it does not rename columns in SQL.

SCD2 merges those streams with satellite history on the same hub grain. Set Parent hub on the SCD2 table. Origin does not matter.

Mixed SCD2 (DV satellites plus source queries) is supported; two or more inputs still require field mappings. The mapping Source column is the satellite name or the source-query name. Suggest mappings lets you pick one or more of those linked satellites and source queries, then fills unmapped fields from the selection.

Identity, cutover, and survivorship pipeline

Show the infographic
HOPPER-EDW ISSUE #183 Business Vault SCD2 Architecture

Identity, Cutover & Survivorship

"Three contracts, three homes, wired only by name." Zero regression; decoupled grains, time windows, and attribute ranks.

Decoupled
3 Objects
Fallback
SortedMerge
1. Identity Resolution Physical Table

BvIdentityMap

Clusters raw hub hashes (hk_raw) into a single durable surrogate key (hk_durable).

Canvas Grain: Shared by Concept
SCD2 Pointer: identityMapName
Golden Rule: Frozen on First Assignment
2. Source Cutover Metadata Only

BvSourceCalendar

Declares temporal eligibility windows for source systems. Compiled directly into SQL WHERE bounds.

Canvas Grain: Shared Dates / Systems
SCD2 Pointer: sourceCalendarName
Interval Type: [effectiveFrom, effectiveTo)
3. Survivorship Pipeline Merge

SurvivorshipMerge

Field-level rank arbitration (Rank 1 > Rank 2) across multi-satellite timeline histories.

Canvas Grain: Per SCD2 Target Column
SCD2 Pointer: field_mapping.rank
Null Policy: inherit vs. apply + flags

End-to-End Pipeline Data Flow

How raw Data Vault assets pass through the three stages to materialize a consolidated BV SCD2 table

Click any stage box below to inspect its mechanics
Raw Storage
DV Satellites & Links
sat_person_oracle
sat_person_kafka
lnk_person_same_as
➔
Gate 1: SQL Filter
Source Calendar
WHERE ts >= from AND ts < to
Half-Open Bounds
➔
Gate 2: Lookup
Identity Lookup
hk_raw ➔ hk_durable
As-Of Temporal Match
➔
Gate 3: Arbitration
SurvivorshipMerge
Rank 1 > Rank 2
inherit / apply nulls
➔
Functional Result
BV SCD2 Timeline
valid_from / valid_to
Durable Key Grain
Decoupled Entity Clustering

Identity Map: Freezing the Durable Hash

An identity map resolves duplicate raw keys (hk_raw) across disparate source systems into a unified durable surrogate key (hk_durable).

Object: BvIdentityMap
Table: Physical table (upsert, not truncate)
Hop Transform: IdentityLookup
1

Freeze on First Assignment

When a cluster forms, hk_durable is created from MDM ID or the initial master raw hash. Once created, it is frozen forever.

2

Master Flip Resiliency

If CRM flips the preferred master from O100 to K100, hk_master and rule_version update, but hk_durable never changes. Downstream marts never break!

3

Preferred BK is Payload

The human-readable preferred_bk is carried as payload. It can be corrected in place without driving a spurious new SCD2 version row.

Durable Key Stability Mechanism

Load 1 (Initial Assignment) rule_version = 1
Same-as Link: O100 (Master) ➔ K100 (Dup)
hk_durable(O100) = HASH("O100")
hk_durable(K100) = HASH("O100") (Inherited)

Durable key is minted and snapshotted into map_person table.

Load 2 (Master Flips to K100) rule_version = 2
Same-as Link: K100 (Master) ➔ O100 (Dup)
hk_durable(O100) = HASH("O100") [FROZEN!]
hk_durable(K100) = HASH("O100") [FROZEN!]

Master pointer updates to K100, but the durable key remains immutable!

Unmapped Key Policies (unmappedPolicy)

Policy Action When No Map Interval Found Timeline Impact
self (default) Substitutes hk_durable = hk_raw. Entity passes through with its own raw identity until mapped.
quarantine Suppresses the row from main SCD2 stream and routes to quarantine. Only explicitly resolved business keys enter the Business Vault.
drop Silently discards the unmapped raw satellite row. Strict whitelist: unmapped history is ignored.

Three contracts, three homes, wired only by name. A blank pointer keeps today’s SortedSchemaMerge pipeline:

Business Vault SCD2 — Cutover

PlantUML source: bv-identity-cutover-survivorship.puml.

Identity maps

An identity map on the canvas resolves many raw hub hashes to one durable hash. Add it with Add identity map. An SCD2 table opts in with Identity map. Leave that blank and the timeline stays on the raw hub hash.

Identity map — Freezing hk_durable on first assignment

PlantUML source: bv-identity-map-freeze.puml.

The generated map pipeline reads the same-as link and the map rows already stored, then upserts. It does not truncate. A raw key that already has a durable key keeps it. A new duplicate in that cluster inherits it. A new cluster hashes the MDM id when one is present, otherwise the current master raw hash, and that value sticks. The preferred business key is updated in place and does not open an SCD2 version.

On each SCD2 leg the generator looks up the map as of the row timestamp, writes the durable hash into the grain column, and keeps the raw hash as hk_raw. The preferred business key is carried as preferred_bk and is not a version driver. The map rule_version is stored as map_rule_version, also not a version driver. Rows before the mapping starts follow the unmapped policy: self uses the raw hash, quarantine and drop leave the row out of the timeline. Partitions, when set, use the durable hash and stay a full rebuild. Hub business keys, when loaded, join on hk_raw so the durable grain still finds the raw hub.

Incremental build is allowed with a map. Rows after the watermark load as usual. A raw key whose rule_version no longer matches the open SCD2 row is read again from the earlier of that map row’s valid_from and the watermark, and the open row for its durable key is closed. That extra read is generated only when the satellite uses the Business Vault database connection. Otherwise run a full rebuild after the map changes. Survivorship still forces a full rebuild.

Naming an effectivity satellite requires both the from and to columns. Empty effectivity means the same-as match applies for the whole timeline.

Survivorship

Field mappings can name more than one source for the same target column. Give every one of those rows a Rank. 1 wins over 2. Leave rank blank when the target has a single source. If any rank is set, that SCD2 table uses SurvivorshipMerge instead of SortedSchemaMerge and Repeat. A table with no ranks is unchanged.

Survivorship — Field ranks

PlantUML source: bv-survivorship-rules.puml.

The merge reads the legs already sorted by hash key and functional timestamp. At each timestamp it keeps the best-ranked value. Null policy inherit skips a null and tries the next rank. apply writes the null. A blank null policy uses the leg default, then the calendar: a delta-only source inherits, a full or seed source applies. seed fills a gap and does not replace a value already held by an upsert source. delete withdraws that source. A row is emitted only when the surviving values change. The build is a full rebuild even if the dialog says Incremental.

Present flag is an optional source column on a mapping. When the flag is false, a null was left out of the message and does not change the stored value. When the flag is true, a null clears the value even if the null policy is inherit. Blank means the column arrived, so the null policy decides.

Operation field is an optional column on a satellite or source query.A row value of upsert, delete, or seed overrides the leg operation.A key that does not appear on a delta feed is not deleted.A source query lists either a functional timestamp or a load date, not both.

Calendar priority and the leg priority override are still stored.They are not the field rank.

Source calendars

A source calendar says when each source system may contribute rows. Add it with Add source calendar. It is shared metadata, not a warehouse table. An SCD2 table opts in with Source calendar. Leave that blank and every satellite row is kept.

Source calendar — Half-open cutover windows

PlantUML source: bv-source-cutover-calendar.puml.

Calendar system is a name you choose, such as ORACLE or KAFKA. It is not a table name. On the SCD2 table, open Satellite settings and pick that name from the dropdown. Source queries are picked on the Derivatives tab. A row is kept when the leg’s functional timestamp falls in a window for that name.

Oracle through February 2024, Kafka from 1 March 2024 onward:

Calendar system Effective from Effective to

ORACLE

1900-01-01

2024-03-01

KAFKA

2024-03-01

2024-02-29 23:59:59 is Oracle. 2024-03-01 00:00:00 is Kafka. A date with no time means midnight at the start of that day. An empty effective to means the source is still in use. Dates are yyyy-MM-dd or yyyy-MM-dd HH:mm:ss.

The worked example and the column tooltips are in the source calendar help. How an SCD2 satellite picks ORACLE or KAFKA is in Satellite settings. Priority and mode do not change this filter. Two SCD2 tables can share the calendar and still pick different calendar systems.

Single-satellite SCD2

The simplest case: one DV satellite derivative → one BV table.

  • Set the satellite as a derivative on the SCD2 table

  • Configure functional timestamp (per table, or model default, or load-date fallback)

  • Attribute columns pass through with DV names unless you add field mappings

Example: integration-tests/tests/basic/vault1.hbv builds sat_customer_hb from sat_customer.

Multi-satellite SCD2

When an SCD2 table references two or more satellites (e.g. Customer 360 from demo, contact, address, and preference satellites), you must provide:

  • Field mappings — map each (satellite, source field) to a BV target column name

  • Satellite configs — optional per-satellite functional timestamp override and source indicator value

The generated Table Output / native bulk loader field list comes from the SCD2 target layout, which includes every mapping with Load enabled (satellite attribute type when known, otherwise a string fallback). You should not need Get fields on the bulk loader to pick up mapped columns.

Target column names must fit the Business Vault database identifier limit (PostgreSQL 63 characters, MySQL/SingleStore 64, SQL Server 128). Suggest mappings truncates collision prefixes to that limit; Check model errors if a name is still too long.

Validation enforces:

  • All referenced satellites share the same hub (or link) parent, or the SCD2 table has a Parent hub / Linked Hub

  • Every mapped source field exists on the satellite (or source-query column list)

  • No duplicate BV target column from different sources

  • Explicit mappings when multiple satellites are present

  • Column names do not exceed the target identifier limit

Example: integration-tests/tests/multi-satellite-bv/customer-360.hbv → table customer_360_bv with 11 field mappings across 4 satellites.

Customer 360 Business Vault model on the canvas

Functional timestamp resolution

Priority order:

  1. Per-satellite override in Satellite configs

  2. Per-table functional timestamp field on the SCD2 table

  3. Model-level functional timestamp in Business Vault configuration

  4. Load date fallback (typically x_load_ts from Data Vault configuration)

The functional timestamp drives SCD2 interval boundaries — not the technical load date unless you choose it explicitly.

Valid from / valid to

Defaults come from Business Vault configuration (x_from_ts, x_to_ts in the sample project). Override per table if needed.

Open intervals use sentinels from configuration (default 1900-01-01 00:00:00 and 9999-12-31 23:59:59).

SCD2 table dialog (key fields)

Field Description

Name

Logical name of the SCD2 table in the .hbv model.

Physical table name

Target table in the Business Vault database.

Derivatives

DV satellite references and/or source queries. At least one satellite or source query is required.

Include hash key

When enabled, the parent hash key column is included in the BV table.

Include hub business keys

When enabled, parent-hub business key columns are Merge Joined after collapse, before calculations. Identity only (not version drivers). Requires Include hash key and hub-parent satellites. Not supported for link satellites.

Load hub business keys

Enabled with Include hub business keys. Checked (default) writes hub BK columns to the BV table. Unchecked keeps them on the stream for calculations only.

Suggest mappings

Asks which linked satellites and source queries to fill, reloads the linked .hdv, and adds unmapped satellite attributes and source-query columns for that selection (hash key / timestamps on the source query are skipped). Reports a clear reason when sources are listed but no fields appear (stale or missing Data Vault model, unresolved satellite or source query, empty satellite Attributes tab, or source query without Get fields).

Functional timestamp

Column on the satellite stream used as the timeline (see resolution order above).

Build mode

FULL_REBUILD (default) truncates and reloads the BV table; INCREMENTAL filters satellite history and appends/closes versions.

Hash-key partitions

None (default), or 4 / 8 / 16 parts for a large full rebuild. See Hash-key partitions (large full rebuilds).

SQL Expression copies

Number of parallel copies of the generated SQL Expression transform after collapse (default 1). A literal (4) or a Hop variable such as '${SQL_EXPRESSION_COPIES}' so DTAP environments can differ. Rows are round-robin distributed.

Incremental watermark field

Optional BV column used for MAX(…​) watermark reads and incremental write filtering. Defaults to the resolved functional timestamp field.

Valid from / Valid to

Output column names for SCD2 interval bounds.

Field mappings

Required for multi-satellite tables. Maps satellite attribute → BV column.

Satellite configs

Per-satellite functional timestamp and source indicator for multi-satellite merges. The source indicator is the merge tag (_bv_source) and the BV record-source value when the satellite does not store a physical source-indicator column. Honor Store record source indicator on each DV satellite — SCD2 Table Input SQL never requests a column the satellite table does not have. When a source calendar is selected, Calendar system on this tab picks which name on that calendar the satellite uses. It is not a field on the Data Vault satellite.

Source calendar

Optional name of a BvSourceCalendar on this canvas. Empty means every leg is eligible. See Source calendars.

The Customer 360 sample (customer_360_bv on integration-tests/tests/multi-satellite-bv/customer-360.hbv) uses these tabs:

SCD2 table dialog — General tab
SCD2 table dialog — Derivatives tab
SCD2 table dialog — Field mappings
SCD2 table dialog — Satellite settings
SCD2 table dialog — Calculations tab
SCD2 table dialog — Tests tab
SCD2 table dialog — Lineage tab

Target databases

SCD2 pipeline generation requires:

  • Data Vault target database — on the linked .hdv configuration (read satellite history)

  • Business Vault target database — on the .hbv configuration (write BV table)

Both connection names must resolve in Hop project metadata. Check model on the .hbv file validates this before pipeline generation.

If you change the linked .hdv file on disk, use Reload DV model on the Business Vault toolbar before generating pipelines.

External satellites

Satellites marked External read-only in the .hdv model are skipped by Data Vault Update but remain fully visible to Business Vault generation. Ensure:

  • Physical table names match the warehouse

  • Attribute lists describe actual columns

  • The vault database connection can read those tables

See integration-tests/tests/multi-satellite-bv/customer-360-external.hdv and .hbv for a working example.

External read-only satellites on the Customer 360 Data Vault canvas

Running loads

  • Debug — generate and open build pipeline(s) in Hop GUI (no data write unless you run the pipeline)

  • Business Vault Update action — model check, optional DDL, generate, stage, and execute build pipelines

See Business Vault Update Action.