Also known as non-historized or no-history Links, Transactional Links record the transaction or 'event' components of their referenced Hub tables. They allow us to model the more granular relationships between entities. Some prime examples are purchases, flights or emails; there is a record in the table for every event or transaction between the entities instead of just one record per relation.
Primary Key (src_pk)¶
A primary key (or surrogate key) which is usually a hashed representation of the natural key. For Transactional Links, we take the natural keys (prior to hashing) represented by the foreign key columns below and create a hash on a concatenation of them.
Foreign Keys (src_fk)¶
Foreign keys referencing the primary key for each Hub referenced in the Transactional Link (2 or more depending on the number of hubs referenced)
Payload (src_payload) - optional¶
A Transactional Link payload consists of concrete data for the transaction record. This could be a transaction number, an amount paid, transaction type or more. The payload will contain all the concrete data for a transaction. This field is optional because you may want to model your transactions as a Transactional Link, and multiple Satellites (off of the Transactional Link). This modelling approach can be useful if there are many fields, and these fields comprise multiple rates of change or types of data.
Effective From (src_eff)¶
An effectivity date. Usually called
EFFECTIVE_FROM, this column is the business effective date of a
transaction record. It records that a record is valid from a specific point in time. For a Transactional Link, this
is usually the date on which the transaction occurred.
Load date (src_ldts)¶
A load date or load date timestamp. this identifies when the record first gets loaded into the database.
Record Source (src_source)¶
The source for the record. This can be a code which is assigned to a source name in an external lookup table, or a string directly naming the source system.
Load date vs. Effective From Date¶
LOAD_DATE is the time the record is loaded into the database.
EFFECTIVE_FROM is different,
holding the business effectivity date of the record (i.e. When it actually happened in the real world) and will usually
hold a different value, especially if there is a batch processing delay between when a business event happens and the
record arriving in the database for load. Having both dates allows us to ask the questions 'what did we know when'
and 'what happened when' using the
EFFECTIVE_FROM date accordingly.
EFFECTIVE_FROM field is not part of the Data Vault 2.0 standard, and as such it is an optional field, however,
in our experience we have found it useful for processing and applying business rules in downstream Business Vault, for
use in presentation layers.
Creating Transactional Link models¶
Create a new dbt model as before. We'll call this one
1 2 3
To create a Transactional Link model, we simply copy and paste the above template into a model named after the Transactional Link we are creating. dbtvault will generate a Transactional Link using parameters provided in the next steps.
The recommended materialisation for Transactional Links is
incremental, as we load and add new records to the existing data set.
Adding the metadata¶
Let's look at the metadata we need to provide to the t_link macro.
We provide the column names which we would like to select from the staging area (
Using our knowledge of what columns we need in our
t_link_transaction table, we can identify columns in our
staging layer which map to them:
|src_payload||TRANSACTION_NUMBER, TRANSACTION_DATE, TYPE, AMOUNT|
When we provide the metadata above, our model should look like the following:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27
See our metadata reference for more detail on how to provide metadata to Transactional Links.
With our model complete and our YAML written, we can run dbt to create our
t_link_transaction Transactional Link.
dbt run -m +t_link_transaction
dbt run -s +t_link_transaction
The resulting Transactional Link table will look like this: