Late Arriving Data

Late arriving or backdated data is a common challenge in data warehousing. In Data Vault, it is important to distinguish between the technical timeline used for loading data and the business timeline representing when events actually occurred in the real world.

1. Technical Timeline vs Business Timeline

When loading data into the Raw Vault, always use a Load Date Timestamp (LDTS):

• Set when the record first arrives in your target system (landing zone, data lake, or Raw Vault).
• Never backdate this timestamp—it should always move forward.
• Used for incremental loading, delta detection, and reproducibility of snapshots.

This timestamp does not reflect the real-world timing of the data. It is purely a technical artifact to track ingestion order.

2. Capturing the Business Timeline

To handle late arriving or backdated data, use descriptive business dates stored in your satellites, such as:

• Apply Date / Effective Date: When the data became valid in the source system or real world.
• Last Modified Date: When the record was last changed in the source system.

These business timestamps allow you to create snapshots or temporal views that reflect the true order of events.

3. Timeline Corrections Without an Extended Tracking Satellite

You can correct timelines without adding additional satellites by leveraging the business timestamps stored in your existing satellites:

1. Create temporal PIT tables or snapshots based on the business timeline, not the load date.
2. When late-arriving data is detected:
   • Option 1: Rebuild the affected snapshots to include the late data.
   • Option 2: Apply counter transactions to reverse previous measures and apply the updated values.
3. Always keep the load date unchanged—it only tracks ingestion, not validity.

This approach ensures that your historical reports reflect the correct business sequence without complicating the Raw Vault model.

4. Practical Guidelines

• Do not order or aggregate data using the load date when interpreting or reporting; always use business dates.
• Maintain separate timelines:
  • Load Date: Technical, for data ingestion and reproducibility.
  • Business Date: For interpretation, analysis, and handling late arrivals.
• Rebuild snapshots or use counter transactions as necessary when late data affects measures or aggregates.

Summary

Late arriving data can be handled in Data Vault without adding extra tracking satellites by clearly separating technical and business timelines. Load Date timestamps remain forward-only, while satellites store business dates to drive temporal snapshots and corrections. Using temporal PIT tables, counter transactions, or snapshot rebuilding ensures your analytics reflect the real-world timeline accurately.

Watch the Video

Capture CDC Data

Capturing Change Data Capture (CDC) data in Data Vault can be tricky, especially when the source mostly sends inserts but occasionally produces duplicates or deletions. Understanding how to handle these cases ensures historical accuracy and avoids data inconsistencies in your hubs and satellites.

The Scenario

Consider the following behavior of your source system:

• Most of the time, rows are insert-only.
• During initial load, the same row may arrive twice (once in the bulk load and once as an insert within the same batch).
• Deleted rows may occasionally appear.

These patterns can lead to duplicates if not handled correctly. At first glance, it might look like you need a Non-Historized Link, but duplicates must still be managed properly.

Why Standard Non-Historized Links May Fail

A standard non-historized Link assumes a single row per combination of hubs. When duplicates arrive, either due to CDC or multiple inserts during initial load, the Link cannot naturally distinguish them, leading to primary key conflicts or overwritten data.

A common—but sometimes problematic—solution is adding counter rows to differentiate duplicates. However, this often requires a GROUP BY in the Information Mart, which can cause performance issues, particularly on non-columnar databases.

Recommended Approach: Capture Technical History in Satellites

Instead of modifying the Link, the recommended approach is to handle duplicates in satellites, preserving the raw source events and their arrival order.

Step 1: Use a Satellite with a Load-Date Sequence

For each incoming batch:

• Assign the CDC load timestamp to the first row of a given parent.
• If multiple rows for the same parent exist in the batch, increment the timestamp by a small unit (microsecond, millisecond, or nanosecond) for each subsequent row.

This creates a unique ordering of changes while preserving the technical history, without touching the original raw data.

Step 2: Maintain Historical Order

By adding a microsecond increment to the load date for each row:

• The first row in the CDC batch gets the base timestamp.
• The second row gets base timestamp + 1 microsecond, the third row +2 microseconds, etc.

This ensures the latest row has the highest load timestamp, which can be used to drive Point-In-Time (PIT) tables and type-1 dimension replacements.

Step 3: Preserve Batch or CDC Metadata

If your CDC source provides a batch ID or subsequence number, include it in the satellite. This allows for:

• Tracking which records arrived together
• Reconstructing the technical timeline of changes

If no metadata exists, the microsecond sequencing on the load date is sufficient to order the rows.

Handling Non-Historized Links with Duplicates

In rare cases, a non-historized Link may receive multiple rows for the same key combination. To handle this safely:

• Extend the alternate key to include the load date (or other sequencing attribute) in the hash key calculation.
• This ensures each row has a unique primary key without modifying the raw data.

Key points:

• No need to use counter rows in the raw link.
• Duplicates are captured and preserved for historical accuracy.
• Aggregations in PIT or Bridge Tables can be used for reporting, ensuring performance optimization.

Optional: Bridge Tables for Performance

If your Information Mart requires grouping or deduplication and your database struggles with performance:

• Create a Bridge Table that pre-aggregates or resolves duplicates.
• The Bridge Table stores only the latest row (or the aggregated result) for reporting.
• You maintain the raw satellite history in case full lineage or historical reconstruction is needed.

Summary

• Capture all incoming CDC events in a satellite, including duplicates, without modifying the raw data.
• Use microsecond increments on the load date to order multiple rows per parent.
• Include CDC batch metadata if available to preserve groupings and arrival order.
• For non-historized Links receiving multiple rows, include the load date in the hash calculation.
• Bridge Tables or PIT tables handle reporting and aggregation efficiently, while maintaining full historical traceability.

This approach preserves auditability, ensures correct historical ordering, and avoids performance issues in the Information Mart.

Watch the Video

Multi-Type Business Keys

One of the more subtle challenges in Data Vault modeling appears when a single source delivers multiple types of business keys in one structure. Things get even more complex when those keys:

• Point to different business objects depending on a type indicator
• Have different formats than the “real” business keys used in existing hubs
• Require cleansing or transformation before they can be linked correctly

In this article, we’ll walk through how to model such a scenario in Data Vault, how to think about Links and key types, and—most importantly—where business logic and transformations should live.

The Problem Statement

Let’s restate the scenario in simple terms.

Your source table looks something like this:

| ItemBK | KeyType | KeyValue |
|--------|---------|----------|
| ITEM1  | A       | T123     |
| ITEM1  | B       | 999-45   |

From a business perspective:

• ItemBK identifies an item (one clear Hub candidate)
• KeyType determines which business object the key refers to
• KeyValue contains the actual identifier

The complication comes from the target data model:

• For KeyType A, the key belongs to Hub A, but the real business key is 123, not T123
• For KeyType B, the key belongs to Hub B and can be used as-is

So the value T123 must eventually link to Hub A using the cleaned value 123.

This raises three classic Data Vault questions:

• How should this be modeled from staging to the Raw Data Vault?
• Should the key type be stored in the Link?
• Where should key cleansing and transformation happen?

First Principle: Preserve the Source

Before jumping into modeling options, let’s clarify a core Data Vault principle:

The Raw Data Vault must always allow you to recreate the original source delivery.

This means:

• No irreversible transformations in staging
• No hidden business rules applied too early
• Full auditability back to the source

Every modeling decision below is guided by that principle.

Identifying the Core Business Objects

From the example, we can already identify several business concepts:

• Item → clearly a Hub (Hub Item)
• KeyValue → a business key, but its meaning depends on KeyType
• Relationships → Item is related to different business objects depending on KeyType

This is a common pattern: a single source column contains references to different hubs.

Modeling Option 1: Pivot the Source (When Key Types Are Fixed)

If—and this is a big if—you can guarantee that:

• The number of key types is small
• The set of key types is stable (e.g. only A and B)

Then a pivoted staging approach can work.

What This Looks Like

You pivot KeyType into separate columns:

| ItemBK | KeyValue_A | KeyValue_B |
|--------|------------|------------|
| ITEM1  | T123       | NULL       |
| ITEM1  | NULL       | 999-45     |

This allows you to:

• Load Hub Item from ItemBK
• Load Hub A from KeyValue_A
• Load Hub B from KeyValue_B
• Create Links between Item and each Hub

Limitations

This approach breaks down quickly when:

• A new key type appears
• Key types are dynamic or unknown
• The source evolves frequently

In those cases, pivoting creates a brittle staging layer and forces frequent refactoring.

Modeling Option 2: Generic Hub for Typed Keys

When key types are dynamic or extensible, a more robust pattern is required.

In this case, treat the combination of KeyType + KeyValue as a business key.

Create a Generic “Key” Hub

This hub represents “external identifiers” regardless of their semantic meaning.

• Hub Key
  • Business Key = KeyType + KeyValue

Why composite?

• Key values may overlap across types
• Only the combination uniquely identifies the business object

You then:

• Load Hub Item from ItemBK
• Load Hub Key from (KeyType, KeyValue)
• Create a Link between Item and Hub Key

This guarantees:

• Full preservation of the source
• No assumptions about future key types
• Clean separation of concerns

But What About the Existing Hubs (Hub A, Hub B)?

This is where many teams are tempted to inject business logic too early.

Yes, you already have Hub A and Hub B. But the mapping logic that says:

• “If KeyType = A, this belongs to Hub A”
• “If KeyType = B, this belongs to Hub B”

is business logic.

Business logic does not belong in the Raw Data Vault.

The Correct Place: Business Vault

In the Business Vault, you can:

• Create derived business hubs
• Filter by KeyType
• Union keys from different raw sources

For example:

• Business Hub A = existing Hub A UNION keys from Hub Key where KeyType = ‘A’

This approach ensures that:

• Raw data remains unchanged and auditable
• Business rules are explicit and versionable
• Changes in logic don’t force Raw Vault reloads

Should the Key Type Be Stored in the Link?

The answer depends on the modeling option.

Option 1 (Pivoted Source)

No.

Once pivoted, the key type is implicit in the structure. There is no longer a “KeyType” concept in the Link.

Option 2 (Generic Key Hub)

Again, no.

The key type becomes part of the Hub business key. The Link simply connects Item to the generic Key Hub.

If—and only if—key values are guaranteed to be unique across types, an alternative is:

• Store KeyType in a Satellite on the Hub

But in most real-world cases, using a composite business key is safer.

Where Should Key Cleansing and Transformation Happen?

This is the most important question.

In the example:

• T123 must become 123 to link to Hub A

This is a business rule.

What Not to Do

• Do not strip the “T” in staging
• Do not load cleaned values directly into the Raw Hub

Why?

• The rule may change
• You lose the original source value
• You break auditability

The Recommended Pattern: Same-As Link

Instead, load the raw value T123 as-is and create a Same-As Link.

This link connects:

• The “raw” business key (T123)
• The “master” business key (123)

Benefits:

• Full traceability
• Explicit business logic
• Flexible remapping if rules change

Same-As Links are purpose-built for this kind of semantic deduplication.

Putting It All Together

A robust end-to-end solution looks like this:

• Stage data exactly as delivered
• Load ItemBK into Hub Item
• Load (KeyType + KeyValue) into a generic Hub
• Link Item to the generic Hub
• Apply business logic in the Business Vault
• Use Same-As Links to handle key transformations

Key Takeaways

• Multi-type business keys are common—and tricky
• Preserve the source structure in the Raw Data Vault
• Avoid early cleansing and transformation
• Use composite business keys when uniqueness is not guaranteed
• Apply business rules in the Business Vault
• Use Same-As Links for key normalization

Handled correctly, this pattern gives you maximum flexibility, auditability, and long-term stability—exactly what Data Vault was designed for.

Watch the Video

Link Naming Convention

One of the most common questions we hear from Data Vault practitioners—especially once a model grows beyond a few hubs and links—is this: how do you define a clear link naming convention, and how do you avoid getting lost in all the different link types when querying current versus historical data? It’s a very practical concern, and if you don’t address it early, your Data Vault can quickly become hard to understand, even for experienced developers.

In this article, we’ll walk through a pragmatic approach to Data Vault link naming conventions. We’ll cover how to name links in a business-friendly way, how to encode technical meaning into the name without overcomplicating it, and how naming helps users understand which links to use for historical data and which are more event- or transaction-oriented.

The First Question: How Do You Name a Link?

Let’s start with the most basic aspect of the question: how do you name a link at all? Imagine a simple business scenario. A customer walks into a store and buys a product. From a Data Vault perspective, this creates a relationship between three business keys: customer, store, and product. This relationship is represented by a link.

Now you have several naming options. You could name the link something like customer_store_product_link, purely describing the hubs it connects. Or you could give it a business-oriented name such as retail_transaction_link or sales_transaction_link.

Our strong recommendation is to prefer business-driven names whenever possible. If the relationship clearly represents a real business concept, then the link should be named after that concept. Calling it a retail transaction link immediately tells business users and developers what this link represents, without forcing them to interpret a technical list of hubs.

That said, there are cases where a business name simply doesn’t exist. Sometimes a link truly represents nothing more than a structural relationship, such as a customer being associated with a store, without a clear transactional or event-based meaning. In those cases, it is perfectly fine to fall back to a composite name like customer_store_link.

The key point here is consistency and intent. If there is a meaningful business name, use it. If not, use a descriptive composite name that clearly shows which hubs are involved.

Ordering Hub Names in Composite Links

When you do use composite names, the next question is often about ordering. Should it be customer_store or store_customer? From a pure Data Vault perspective, links are end-to-end relationships with no inherent direction. Technically, the order does not matter.

However, from a modeling and readability perspective, a consistent ordering rule helps a lot. One practical guideline is to follow the natural business hierarchy if one exists. For example, if you model an industry body and the organizations that belong to it, the industry body is conceptually “above” the organization. In that case, naming the link industrybody_organization_link feels natural and intuitive.

This approach mirrors how people think about hierarchies: from root to leaf, from higher-level concept to lower-level concept. Again, the most important part is not which rule you choose, but that you standardize it across the entire Data Vault.

Standardization Beats Perfection

No naming convention will ever be perfect. What matters far more is that everyone follows the same rules. At Scalefree, for example, we deliberately leave some freedom to developers when choosing link names, with a clear preference for business-oriented naming. At the same time, we provide documented guidelines that explain how to make those decisions consistently.

This balance allows teams to model complex domains without being blocked by overly rigid naming rules, while still keeping the model understandable and navigable.

Dealing with the Many Types of Links

Once you move beyond basic modeling, the real challenge begins: Data Vault doesn’t just have one type of link. Over time, a model accumulates several varieties, each serving a different purpose.

Some of the most common link types include standard links in the Raw Data Vault, business or exploration links in the Business Vault, non-historized links (also known as transactional links in Data Vault 1.0), hierarchical links, same-as links, dependent child links, and even computed or aggregated links used to reuse logic across multiple bridges.

Without a clear convention, these different link types quickly become indistinguishable, making it hard to know which one to use for a specific query or use case.

Prefix or Suffix? Why Suffixes Usually Win

One common design decision is whether to encode link type information as a prefix or a suffix. While prefixes like link_retailtransaction are sometimes used, we generally recommend suffixes instead.

The reason is simple: suffixes group related objects together more naturally. If everything related to a customer starts with “customer”, then the hub, its satellites, and related links appear together when browsing schemas or metadata. This makes the model easier to explore and understand.

For example, a customer hub might be named customer_h, while a satellite could be customer_data_s. Using suffixes ensures that all customer-related entities are visually grouped.

A Compact Suffix-Based Pattern

To keep naming both expressive and machine-readable, we recommend a compact suffix pattern. In this approach, every link ends with an L, indicating that it is a link. Additional single-character markers are added before the L to indicate special link types.

For example, a standard link in the Raw Data Vault simply ends with _l. A non-historized link ends with _nl. A hierarchical link ends with _hl. A same-as link ends with _sl. A dependent child link ends with _dl.

When working with the Business Vault, an additional B is added. A standard Business Vault link ends with _bl. A dependent child link in the Business Vault becomes _bdl. In this scheme, the Raw Data Vault is the default, so there is no explicit “R” marker.

This compact notation might look cryptic at first, but it has significant advantages. It keeps names short, consistent, and easy to parse automatically.

Why Short Codes Are Powerful

Using single-character indicators is not just about aesthetics. It enables powerful automation and governance capabilities. With consistent suffixes, you can use regular expressions on metadata to identify entity types automatically.

For example, you can quickly validate that all dependent child links contain the required hub references, hash keys, and record source attributes. You can also apply automated tests and checks depending on the link type, without relying on manual inspection.

This becomes especially valuable in larger Data Vault environments where hundreds or thousands of entities exist.

Putting It All Together: Naming Examples

Let’s revisit the earlier example of a relationship between customer and store. In the Raw Data Vault, a simple link could be named customer_store_l. If this relationship is modeled in the Business Vault, the name could become customer_store_bl.

If the relationship represents a business transaction and is non-historized, a name like retailtransaction_nl or retailtransaction_bnl (for the Business Vault) clearly communicates both the business meaning and the technical behavior.

These names immediately tell an experienced user what kind of data to expect and how the link should be used.

Which Links to Use for Current vs. Historical Data?

The second part of the original question is just as important: how do you know which links to use when querying current versus historical data?

The key insight here is that almost all Data Vault entities are historical by nature. Links record which relationships have ever existed during the lifetime of the data warehouse. On their own, they usually do not tell you whether a relationship is currently active.

To answer questions about “current” relationships, you typically need additional structures such as effectivity satellites or deletion indicators. These tell you whether a relationship is active, inactive, or deleted at a given point in time.

For example, if customers can change their preferred store, the link captures all store assignments that ever existed. An effectivity satellite tells you which assignment is valid right now.

Standard Links vs. Non-Historized Links

Another important distinction is between long-lived relationships and point-in-time events. Standard links are often used for relationships that exist over a period of time, such as employment, ownership, or assignments.

Non-historized links, on the other hand, are typically used for events, messages, or transactions that occur at a specific moment. A customer purchasing a product, an invoice being issued, or a sensor sending a reading are all examples of point-in-time events.

Even though they are called “non-historized,” these links can still store large volumes of historical data. The name simply reflects that the relationship itself does not persist over time—it happens at a single point in time.

Dependent child links are a special case of this pattern, often used for structures like invoice line items, where detailed data depends on a parent transaction.

Teaching the Model to Users

Finally, no naming convention works unless users understand it. A clear, consistent naming scheme allows you to teach users how to recognize link types, understand their purpose, and choose the right entities for their queries.

Once users know that “_l” means Raw Vault link, “_bl” means Business Vault link, and “_nl” indicates an event-based relationship, they can navigate even large models with confidence.

Final Thoughts

A good Data Vault link naming convention is not about memorizing rules; it’s about reducing cognitive load. Business-driven names improve readability, suffix-based patterns improve structure, and compact codes enable automation and governance.

If you invest the time to define and standardize these conventions early, you will save countless hours later—both for developers and for business users trying to understand and trust your data warehouse.

Watch the Video

Link Effectivity

One of the most common — and most misunderstood — challenges in Data Vault modeling is how to correctly handle changing relationships. Especially when source systems insert, delete, and even reinsert the same records over time, many teams struggle to answer a simple but critical question:

“How do I reliably determine the latest valid state of a relationship?”

In this article, we will walk through exactly how to model this scenario using Links and Effectivity Satellites in Data Vault, why this pattern is essential, and how it allows you to extract both the current and historical state of relationships in a clean, auditable way.

The Scenario: A Source Table with Changing Relationships

Let’s start by restating the problem in simple terms.

You have a source table with the following structure:

• COL_1 + COL_2 → Business Key for Hub A
• COL_3 + COL_4 → Business Key for Hub B
• COL_5, COL_6 → Descriptive attributes
• ROW_OPERATION → Insert / Delete indicator

This tells us something important right away:

• Two business keys appear in the same row
• Therefore, there is a relationship between those business keys
• That relationship belongs in a Link

The complexity arises because the operational system:

• Inserts records
• Deletes records
• Sometimes reinserts the exact same records later

All while the actual business keys and attributes remain unchanged.

So how do we model this in a way that preserves history, supports auditing, and still lets us easily answer:

“Which relationships are valid right now?”

Why Links Alone Are Not Enough

In Data Vault, a Link represents the existence of a relationship — not its state.

Once a relationship between Hub A and Hub B exists, the Link record itself is immutable.
It simply tells us:

“At some point in time, this relationship existed.”

But the Link alone cannot tell us:

• When the relationship became active
• When it was removed
• When it was reintroduced

This is where many implementations go wrong. Teams try to encode relationship state directly into the Link or manage it downstream in reporting logic. Neither approach scales, and both break Data Vault principles.

The correct solution is to model the effectivity of the relationship separately.

Enter the Effectivity Satellite

An Effectivity Satellite attached to a Link tracks the physical existence of that relationship over time.

Think of it as a timeline that answers one simple question:

“Is this relationship active or inactive at a given point in time?”

Typical Structure of a Link Effectivity Satellite

• Link Hash Key
• Load Date Timestamp (LDTS)
• Record Source
• Deletion Timestamp

The deletion timestamp is the key attribute. It defines until when a relationship is considered active.

A common and proven pattern is:

• Active relationship → Deletion date = 8888-12-31
• Inactive relationship → Deletion date = actual load timestamp when deletion was detected

(Using 8888-12-31 instead of 9999-12-31 avoids date overflow issues and is widely adopted in practice.)

Walking Through the Lifecycle of a Relationship

Let’s walk through the example step by step.

Day 1: Relationship Is Inserted

The source system delivers a row linking business keys A and B.

• A Link record (A–B) is created if it does not yet exist
• An Effectivity Satellite record is inserted

Deletion_Timestamp = 8888-12-31

This means: the relationship is active.

Day 2: Relationship Is Deleted

The source system no longer contains the A–B row.

We do not delete the Link. Instead:

• A new Effectivity Satellite record is inserted
• The deletion timestamp is set to the current load timestamp

Now the timeline clearly shows when the relationship ended.

Day 3: Relationship Is Reinserted

The same A–B relationship appears again in the source.

Important point:

• The Link already exists → no new Link row
• The Effectivity Satellite needs a new delta

Deletion_Timestamp = 8888-12-31

This marks the relationship as active again — without losing the history of the previous deletion.

Handling Updates: Why Updates Are Two Events

Another area where teams often struggle is updates.

In Data Vault terms:

An update is a deletion of the old version and an insertion of a new version.

For example, if a relationship changes from A–B to A–C:

• A–C is inserted as a new Link relationship
• The Effectivity Satellite marks A–C as active
• The Effectivity Satellite marks A–B as deleted

This ensures that:

• No ambiguity exists about which relationship is active
• Historical reporting remains accurate
• Auditors can trace every change

How to Query the Latest Valid State

Once modeled correctly, querying the current state becomes straightforward.

Current Active Relationships

1. Join the Link to its Effectivity Satellite
2. Select the latest satellite record per Link hash key
3. Filter where Deletion_Timestamp = 8888-12-31

That’s it.

This pattern works consistently across:

• Current-state reporting
• Type 1 dimensions
• Snapshot-based reporting using PIT tables

What About ROW_OPERATION and Valid-From / Valid-To?

It’s important to clearly separate concepts:

ROW_OPERATION

Indicators like Insert/Delete from the source system are:

• Descriptive metadata
• Useful for loading logic
• Not the source of truth for effectivity timelines

They can be stored in a descriptive Satellite, but effectivity is driven by load detection.

Valid-From / Valid-To

Business validity dates are not deletion timelines.

• They describe business meaning (contracts, subscriptions, agreements)
• They belong in separate descriptive Satellites
• They are typically applied in the Business Vault or downstream

Mixing business timelines with technical effectivity is a common modeling mistake.

Why Effectivity Satellites Are Not Optional

A final, often overlooked argument: dirty data.

What happens if:

• A relationship is accidentally loaded
• Used in reports
• Then later corrected or removed

Without an Effectivity Satellite:

• You cannot explain historical results
• You cannot prove when data changed
• You cannot support audit requirements

That’s why, in real-world Data Vault implementations:

• Most standard Links have Effectivity Satellites
• Most Hubs have them as well
• Only a few special cases are exceptions

Key Takeaways

• Links capture existence, not state
• Effectivity Satellites capture when relationships are active
• Deletes and reinserts are modeled as satellite deltas
• Updates are modeled as delete + insert
• Current state is derived by filtering on open deletion timelines

Once you adopt this pattern consistently, handling complex relationship lifecycles becomes simple, scalable, and auditable.

Watch the Video