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 connection’s record source | No | Record source code |
dv_row_record_source_name | STRING(500) | From the connection’s record source | No | Record source name |
dv_row_startdate | TIMESTAMP | Set when the load runs | No | Effective-from timestamp |
dv_row_load_id | BIGINT | Identifies the load run | No | 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 | No | 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 | No | Current-record indicator |
dv_row_is_deleted | BOOLEAN | Historization indicator | No | 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 connection’s record source, so every loaded row records which source it
came from. That value is set on the connection itself.
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_startdate 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_startdate,
dv_row_end_date) cover when each version of a row is effective: dv_row_startdate 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.
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.
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.
Integration keys on import
Section titled “Integration 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 Integration keys on import card), each imported table gets one integration-key column at the Staging layer: a single business key column 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 connection’s record source (the same value that fills dv_row_record_source) to the key, so keys from different sources stay distinct. And when integration keys are on, the Staging change hash is computed from that one integration-key column instead of from the separate raw key columns. See Integration keys on import for the full behaviour, including applying integration 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.