Skip to content

Concatenated business keys

Not part of the free edition, and not switched on for every paying organization either. The Data Vault and Data Mart accelerators are enrolled surfaces. If the menu items described below are missing, that is the reason, and it is not a fault. See Pricing for what each edition includes.

Some business keys span more than one column. A customer might be unique only by customer number within a region, or an order line only by order number plus line number. DeltaVault lets you derive a single business key from those columns, builds one concatenated key column for them at the Staging layer, and keys the Data Vault hub on that single value instead of on the separate parts.

A concatenated key is the Derive mode of a table’s business key designation, so a table carries at most one: its designated business key is either a plain column or a derived one. This page covers the derived case. Business key column covers the designation itself, the accelerate gate, and how links reuse the definition.

Derive the key on the staging table

Section titled “Derive the key on the staging table”

Open the staging table and go to its Details tab. The Keys section shows the table’s Primary key and, below it, the Business key, which reads No business key designated until you set one.

  1. Choose Add beside Business key (the button reads Edit once a key exists).
  2. In the Add a business key dialog, switch the mode toggle to Derive.
  3. Under Columns, check the columns that make up the key (for example CustomerId and Region). Each one lands at the end of the Order list; drag rows by their handle, or use the up and down buttons, to set the sequence they join in.
  4. Accept or change the Separator. It seeds from the Shared business key separator and is stored on the designation, so the built column stays stable even if that default changes later.
  5. Accept or change the Column name. It seeds from the Shared business key column pattern, so a key named Customer becomes Customer_BK. Once the derived column exists, its name is fixed.
  6. Read the Preview, which shows the exact concatenation and the full hash expression the staging generator will build, then choose Save.

To key on a multi-column primary key, choose Use primary key instead: the dialog switches to Derive with those columns already in order.

What you get at Staging

Section titled “What you get at Staging”

When the table is built into the Staging layer, the designation becomes one new column:

  • The column name is the name you saved, seeded from the Shared Business key column pattern, which defaults to {{this}}_BK.
  • The column value is the constituent columns joined with the stored separator, which defaults to ||. For a key over CustomerId and Region, the Staging column is built as CONCAT_WS('||', CustomerId, Region), with each part cast to text and a consistent value substituted for nulls.
  • Hash input normalisation (described below) is applied to each part before it is joined, so the same logical key always produces the same value.

The concatenation SQL is generated for Databricks and Microsoft Fabric.

How it drives the hub

Section titled “How it drives the hub”

The hub keys on the designated column. When you accelerate the staging table into a Data Vault, the derived column (for example Customer_BK) is the hub’s business key, and the raw constituent columns (CustomerId, Region) carry through as descriptive attributes on the satellite rather than as separate hub keys.

A source with no Stage table in its ingest chain cannot be accelerated: the run stops with No staging table found. Create the staging table first, designate the key there, then run Data Vault again.

Settings that control it

Section titled “Settings that control it”

The three settings that shape concatenated keys live together under Settings, in the Shared section, so they apply across every project in your organization:

  • Business key separator is the text placed between the constituent values. It defaults to ||. The separator stored on a designation wins over this default.
  • Business key column pattern is the naming pattern for the generated column. It defaults to {{this}}_BK, where {{this}} stands for the key’s name.
  • Hash input normalisation cleans every value before it is hashed, and so every constituent before it is joined: trim surrounding spaces, force a consistent case, and collapse repeated internal spaces to one. Trimming and collapsing are on by default and forcing case is off. With them on, the same logical key reads and hashes the same way even when the source values differ only by spacing or case.
Section titled “Related guides”