Configure system columns
System columns are the row-level metadata DeltaVault adds to the tables it tracks: record source, load timestamps, change hashes, and historization flags. They let you trace where a row came from, detect when it changed, and keep history without touching your business attributes.
Where you configure them
Section titled “Where you configure them”System columns live in Settings, in the System columns grid. When you open it, the grid starts on the Global default scope, shown by the badge above the table. This is the catalog every organization and project inherits unless it overrides it.
To override the catalog, switch the scope to an organization or a project. An override starts as a copy of the global default, so you begin from the shipped columns and adjust from there. If you make edits and want to start over, Reset returns the grid to the global default.
Overrides are also available at the platform scope for administrators. A target platform that needs a different expression for a system column (for example, because of dialect differences in how a timestamp or change hash is produced) can keep its own version in the same System columns grid, switched to that platform’s scope.
The default catalog
Section titled “The default catalog”DeltaVault ships these system columns out of the box:
| Name | Data type | How the value is set | Nullable | Description |
|---|---|---|---|---|
dv_row_record_source | STRING(20) | From the project’s record source code | Yes | Record source code |
dv_row_record_source_name | STRING(500) | From the project’s record source name | Yes | Record source name |
dv_row_start_date | TIMESTAMP | Set when the load runs | Yes | Effective-from timestamp |
dv_row_load_id | BIGINT | Identifies the load run | Yes | Load-audit identifier |
dv_row_change_type | STRING(1) | Supplied by the source | Yes | Source change indicator (insert, update, or delete) |
dv_row_load_ts | TIMESTAMP | Set when the load runs | Yes | Load timestamp |
dv_row_end_date | TIMESTAMP | Supplied by the source | Yes | Effective-to timestamp |
dv_row_hash_diff | BINARY | Change hash over descriptive attributes | Yes | Descriptive-attribute change hash |
dv_row_hash_diff_scd2 | BINARY | Change hash over Type 2 attributes | Yes | Type 2 attribute change hash |
dv_row_is_current | BOOLEAN | Historization indicator | Yes | Current-record indicator |
dv_row_is_deleted | BOOLEAN | Set by persistent staging delete detection | Yes | Soft-delete indicator |
Where the values come from
Section titled “Where the values come from”Each system column is populated at a different point in the load, and knowing the source helps you reason about what each value means.
Record source columns (dv_row_record_source, dv_row_record_source_name) are
filled from the project’s Record source code and Record source name, so every
loaded row records which source it came from. Both are set in the project editor’s
Source options section.
Load columns (dv_row_load_ts, dv_row_load_id) are stamped when the load runs.
dv_row_load_id identifies that load run, so you can group everything a single load
wrote and trace a row back to the exact run that produced it. dv_row_start_date is also
set at load time, but it is the effective-from timestamp that pairs with dv_row_end_date
(covered with the effective-dating columns below).
Change and effective-dating columns (dv_row_change_type, dv_row_start_date,
dv_row_end_date) cover when each version of a row is effective: dv_row_start_date is
the effective-from timestamp set at load time, while the change feed supplies
dv_row_change_type and dv_row_end_date: the source tells DeltaVault whether a row
was inserted, updated, or deleted, and when each version stopped being effective.
Hash columns (dv_row_hash_diff, dv_row_hash_diff_scd2) are change hashes that let
DeltaVault tell whether a row changed without comparing every column one by one.
dv_row_hash_diff hashes the row’s descriptive attributes, while dv_row_hash_diff_scd2
hashes its Type 2 attributes.
Historization flags (dv_row_is_current, dv_row_is_deleted) mark which version of
a row is the live one and which rows have been logically removed at the source.
What sets dv_row_is_deleted in persistent staging depends on your platform (see
Per-layer system columns).
Per-layer system columns
Section titled “Per-layer system columns”Besides the shared catalog, each data layer has its own list of system columns, so a layer can carry exactly the metadata it needs rather than the full catalog.
Landing carries no system columns, at any scope, and by design: a Landing table is a verbatim replica of its source, so DeltaVault stamps nothing onto it. System columns begin at Staging, which is why the Staging section’s System columns card shows no layer picker.
Persistent Staging carries the delete flag, the column that marks a row the source
deleted. Every shipped Persistent Staging list includes dv_row_is_deleted, with the
role Delete flag. Only a full extract sets the flag. Where delete detection runs, it
marks deleted a key that a full extract into persistent staging no longer delivers, and
a delete the change feed reports in dv_row_change_type does not set it. A load is a full extract when each delivery
carries every row, not only the rows that changed. DeltaVault counts a load as one when
the landing table’s Ingest mode is Snapshot and no high water mark filter on the
source table or the landing table narrows it. Delete detection runs on a persistent
staging table only when the table is loaded as a full extract, has a business key, and
carries both the flag and a load timestamp column. What sets the flag depends on your
platform:
- Databricks: each load of a full extract looks for the keys the delivery no longer holds. For each one whose latest row is not already deleted, it appends a copy of that row with the flag set to true, and a key that comes back later loads as a normal row. An empty delivery flags nothing.
- Snowflake: a separate delete detection procedure for the persistent staging table sets the flag on the key’s one row, in place, and the load clears it when the key comes back. An empty delivery flags nothing.
- Fabric: there is no persistent staging load, so nothing sets the flag.
A persistent staging table created before the flag shipped has no dv_row_is_deleted,
so its pane in the catalog shows a banner saying the table is missing that column, which
the platform catalog defines. Reconcile this table adds the flag and brings the
table’s other system columns back in line with the platform catalog.
Reconcile whole connection does the same for every Staging and Persistent Staging
table under the table’s connection. A table already deployed on Databricks or Snowflake
gains the column the first time a load generated after the reconcile runs, while Fabric
has no persistent staging load to run.
To bring a column from the shared catalog into a layer, use Add from shared. The copied column is independent of the catalog, so editing it inside the layer does not change the shared definition, and changing the shared catalog later does not rewrite the copy you already pulled in.
Adopt columns from a parent scope
Section titled “Adopt columns from a parent scope”The shared catalog is configured per scope: a Global standard, one per target platform (Databricks, Snowflake, Fabric), and your organization’s Published settings. A fresh platform or Published catalog starts empty, so each scope can pull columns down from the scope above it instead of retyping them.
- On a platform’s Shared settings, use Add from global shared to copy columns from the Global catalog.
- On Published settings, use the platform shared button to copy columns from your organization’s target platform catalog. Until a target platform is set the button reads Add from platform shared and is disabled, with the hint “Set a target platform first”; once one is set it names the platform, for example Add from Databricks shared.
Each copied column is an independent snapshot, the same as Add from shared: editing it later does not change the scope you copied it from, and columns whose name already exists in the current grid are shown disabled so you never create a duplicate.
Add your own columns
Section titled “Add your own columns”The defaults cover most needs, but you can define more. Use Add row to create a system column beyond the defaults, then set its name, data type, how its value is produced, and whether it is nullable.
The data-type editor is the same one described in Choose column data types, so the searchable type picker and the length, precision, and scale fields behave exactly as they do elsewhere.
Common columns teams add:
| Example name | Data type | How you’d set it | Purpose |
|---|---|---|---|
dv_row_hash_diff_scd1 | BINARY | Change hash over Type 1 attributes | Detect changes to attributes you overwrite in place |
dv_row_is_inferred | BOOLEAN | Set when a placeholder member is created | Mark rows created as placeholders before the real record arrives |
dv_row_last_seen | TIMESTAMP | Set when the load runs | Track the most recent load that still saw the row |
dv_row_load_sequence | BIGINT | Set when the load runs | Order rows within a single load run |
How imports apply these columns
Section titled “How imports apply these columns”When you import metadata, DeltaVault generates Landing, Staging, and Persistent Staging tables from each source table and writes the configured per-layer system columns onto them. The columns it writes are the ones the System columns grid shows for that layer, resolved across the same scopes the grid uses: the Global default, then any target platform settings, then your organization, and finally the project, with the most specific scope winning. An import therefore emits the system columns you configured for the project, not a fixed built-in set.
Project-level overrides govern imports: when a project’s system-column grid is explicitly emptied for a layer, imports into that project add no system columns for that layer, even when organization or global defaults exist.
This applies to imports you run from now on. Tables imported earlier keep the columns they already have until you import them again.
Business keys on import
Section titled “Business keys on import”An import can also build a business key for each table and hash from it, separately from the system columns above. When Create concatenated business keys is on (Settings, Data Vault, the Business keys on import card), each imported table gets one business key column at the Staging layer, built from the table’s primary key or its flagged business-key columns. Two options interact with the system columns here. Prepend record source to the business key hash, in the Shared section rather than on that card, prepends the project’s record source code (the same value that fills dv_row_record_source) to the key, so keys from different sources stay distinct. And when concatenated business keys are on, the Staging change hash is computed from that one business key column instead of from the separate raw key columns. See Business keys on import for the full behavior, including applying business keys to foreign-key relationships.
System columns on the Lineage tab
Section titled “System columns on the Lineage tab”When the same system column is configured on a layer and on the layer above it, the downstream copy is linked to its upstream counterpart. After you commit the import, open the table’s Lineage tab and the system column that carries down the layers, such as a load timestamp or a record-source column, is drawn with an edge to the same-named system column on the upstream table, alongside the lineage for your business columns.
The chain starts at Staging and runs forward, to Persistent Staging and beyond. It never reaches back to Landing, because Landing declares no system columns for it to reach.
Per-layer hash columns are the exception. Each layer recomputes its change hashes from its own rows, so those columns are not linked across layers.