GLOBAL VARIABLES
datavault4dbt is highly customizable by using many global variables. Since they are applied on multiple levels, a high rate of standardization across your data vault 2.0 solution is guaranteed.
PREREQUISITES
The default values of those variables are set inside the packages dbt_project.yml under <your_dbt_project>/dbt_packages/datavault4dbt/dbt_project.yml and should be copied to your own dbt_project.yml. Copy all variables defined under the keyword vars, and paste them under the keyword vars in your file.
VARIABLES AND THEIR USAGES
All the following variables are prefixed with datavault4dbt.
GENERAL CONFIGURATION
| Name | Usage | Explanation |
|---|---|---|
| include_business_objects_before_appearance | Ref Table | If a Ref_Hub entry should appear in the ref_table (snapshot based), even if the snapshot date is before the first appearance of that business object. |
| enable_static_analysis_overwrite | All macros | Relevant for Fusion compatibility. For more info, see here. |
| multi_source_models__execution_aware_loading | Multi source entities | Whether multi source entities should respect the dbt command to reduce runtimes. |
| first_day_of_week | Snapshot Table | A mapping dictionary that defines the integer representation of the first day of the week (Sunday vs. Monday) for each specific database adapter. For the full configuration matrix, see Snapshot Control v0. |
| show_debug_logs | All macros | Whether verbose debug log() output should be written to the dbt .log file. Defaults to false to keep the log file clean. Set to true when troubleshooting macro behavior. |
COLUMN ALIASES
| Name | Usage | Explanation |
|---|---|---|
| ldts_alias | Stage, DV entities | The name of the load-date column in all DV entities. Is generated in the staging area. |
| rsrc_alias | Stage, DV entities | The name of the record-source column in all DV entities. Is generated in the staging area. |
| ledts_alias | Version 1 Satellites | The name of the load-end date column in version 1 Satellites and MA-Satellites. |
| sdts_alias | Snapshot Table, PITs | The name of the snapshot-date column in the snapshot table (+view) and all PITs. |
| snapshot_trigger_column | Snapshot Table, PITs, Post-Hook | The name of the column that shows the activation state of single snapshots. |
| stg_alias | Record Tracking Satellite | The name of the column that holds info about the staging model of each record. |
| is_current_col_alias | Version 1 Satellites | The name of the column that indicates the current row per hashkey. |
| is_active_alias | Effectivity Satellite v0 | The name of the column that marks activity. Generated by the macro. |
HASH CONFIGURATION
| Name | Usage | Explanation |
|---|---|---|
| hash | Stage | What hash algorithm should be used for generating hash values. MD5, SHA1 or SHA2. |
| hash_datatype | Stage | The datatype that hash columns should have. Needs to fit the output of the used hash algorithm. |
| hashkey_input_case_sensitive | Stage | Whether the input business keys for hashkey calculation should be case sensitive or not. |
| hashdiff_input_case_sensitive | Stage | Whether the input descriptive attributes for hashdiff calculation should be case sensitive or not. |
| hashdiff_use_trim | Stage | Define the global default if hashdiff input columns should be wrapped with TRIM() or not. Defaults to true. Available from v1.16.0 |
| concat_string | Stage | The delimiter placed between columns when concatenating them prior to hashing. Defaults to ||. |
| quote_character | Stage | The character used to wrap each individual column value during concatenation. Defaults to ". |
| null_placeholder_string | Stage | The string representing a NULL input column during concatenation. Defaults to ^^. |
| concat_string_replacement | Stage | Token substituted for any occurrence of concat_string found inside the input data, so real values can never collide with the structural delimiter. Defaults to dv4dbt-concat-replacement. |
| quote_character_replacement | Stage | Token substituted for any occurrence of quote_character found inside the input data. Defaults to dv4dbt-quote-replacement. |
| null_placeholder_string_replacement | Stage | Token substituted for any occurrence of null_placeholder_string found inside the input data. Defaults to dv4dbt-null-replacement. |
| hash_input_attribute_dtype | Stage | A mapping dictionary that defines, per database adapter, the datatype that a single input column is casted to inside attribute_standardise, before concatenation. Advanced. On T-SQL adapters a bounded value here also bounds the total payload, see the warning below. Leave at the default unless you have measured. |
| hash_input_concat_dtype | Stage | A mapping dictionary that defines, per database adapter, the datatype that the fully concatenated payload is casted to inside concattenated_standardise, before it is hashed. This is the variable to shorten if you want the performance benefit. |
Multi Active Satellites are excluded from hash_input_concat_dtype: multi_active_concattenated_standardise keeps its hardcoded datatype, so their aggregated payload can never overflow STRING_AGG. They are not excluded from hash_input_attribute_dtype, which is shared with all other entities and applies per column, so a Multi Active Satellite needs the same width check as a regular Satellite.
Shortening hash_input_attribute_dtype or hash_input_concat_dtype below the actual length of your hash input truncates that input silently on most adapters. Truncated input produces different hash values, and two rows that only differ behind the truncation point collapse into the same hashkey or hashdiff. Only lower these values if you are certain that your concatenated input stays below the chosen limit, and treat any later change as a full reload of the affected entities.
On sqlserver, synapse and fabric the columns are joined with CONCAT() / CONCAT_WS(), and those functions derive their own maximum length from their arguments: the result is capped at 8000 characters unless at least one argument is an unbounded type. A bounded hash_input_attribute_dtype therefore bounds your total hash input, not the single column it is named after, and the later cast of the concatenated payload cannot recover what was already dropped.
The safe per-column bound on those adapters is roughly 8000 / number_of_columns_in_the_hash, not 8000. Twenty columns of 500 characters already exceed the limit, even though every column is far inside its own. Leave hash_input_attribute_dtype at its default unless you have measured the concatenated length of your widest entity, and re-measure whenever you add a column.
STAGE CONFIGURATION
| Name | Usage | Explanation |
|---|---|---|
| copy_rsrc_ldts_input_columns | Stage | Whether the columns that are used for ldts and rsrc should also be inside the stage, or not. If true, the stage would hold the ldts- & rsrc-alias columns, and the original columns. If false, only the aliased columns are kept. |
SATELLITE CONFIGURATION
| Name | Usage | Explanation |
|---|---|---|
| is_active_datatype | Effectivity Satellite v0 | Controls the datatype which is used for the is_active-column. Defaults to Bit for Fabric & Synapse, Number on Oracle and Boolean on the remaining adapters. Available from v1.10.0 |
GHOST RECORD AND ZERO KEY CONFIGURATION
| Name | Usage | Explanation |
|---|---|---|
| beginning_of_all_times | Stage, Satellites, PIT | The timestamp that represents your earliest technical timestamp. |
| end_of_all_times | Stage, Version 1 Satellites, PIT | The timestamp that represents your latest technical timestamp. We recommend to not use the maximum possible timestamp of your database. |
| timestamp_format | Stage, Version 1 Satellites, PIT | The timestamp format of the two previous variables. |
| beginning_of_all_times_date | Stage | The date that represents your earliest technical date. Used for ghost-record creation of columns with the date-datatype. |
| end_of_all_times_date | Stage | The date that represents your latest technical date. Used for ghost-record creation of columns with the date-datatype. |
| date_format | Stage | The date format of the two previous variables. |
| default_unknown_rsrc | Stage | The default unknown value for the record source column you want to use. |
| default_error_rsrc | Stage | The default error value for the record source column you want to use. |
| rsrc_default_dtype | Stage | The default datatype that should be used for the two variables above. |
| stg_default_dtype | Record Tracking Satellite | The default datatype for the stg_alias column inside a record tracking satellite. |
| derived_columns_default_dtype | Stage | The default datatype for derived columns, if no other datatype can be detected automatically. |
DATATYPE SPECIFIC DEFAULT VALUES
For each datatype there is a default unknown and error value defined. Additionally, an alternative, usually much shorter value is defined. See the applied default values in the table below.
| Datatype | Error Value | Error Value alt. | Unknown Value | Unknown Value alt. |
|---|---|---|---|---|
| STRING | (error) | e | (unknown) | u |
| Numeric | -2 | -1 |
Those values are best changed by adding a global variable inside your dbt project, that is called following this pattern: datavault4dbt._value_<(alt)>__. If you want to change the default alternative error value for datatype STRING, you would need to set the global variable datavault4dbt.error_value_alt__STRING to your desired value.