Multi-Tenant Environment

Designing and maintaining a Data Vault in a multi-tenant environment presents unique challenges. When a data warehouse must handle not just internal data, but also data from dozens of external clients with slightly different processes and systems, the complexity increases dramatically.

A recent question we received highlighted this exact situation:

“I’m struggling with link management and the evolution process in a multi-tenant warehouse, especially putting all data together in the Information Mart. Our Data Warehouse contains internal data as well as shared data from our clients, for which we perform job requisition processes using their internal systems. We plan to onboard 50–60 clients in the next 2–3 years. Right now, we’re still in the MVP phase, supporting just a few clients. How should I manage links with so many different systems, such a large number of source tables, and processes that are similar but not identical? The goal is to have one common Information Mart design for all clients to enable standardized reporting.”

This is a classic question in modern data architecture. Let’s explore how to approach Raw Vault, Business Vault, and Information Mart design in a multi-tenant context.

Multi-Tenancy in the Raw Data Vault

A cornerstone principle in Data Vault modeling is that each Satellite is sourced from a single source system. However, in a multi-tenant setup, this guideline needs some adaptation. Many tenants use the same source systems (e.g., Salesforce, SAP) with similar core structures. In such cases, you can load multiple tenants into the same Satellite as long as you introduce a Tenant ID as part of the key.

Why Add a Tenant ID?

• Ensures uniqueness of business keys across tenants (e.g., Customer 42 in Tenant A ≠ Customer 42 in Tenant B).
• Partitions data naturally, so Satellites contain subsets per tenant without overwriting each other’s records.
• Provides a straightforward way to filter or secure records by tenant.

By combining the local business key with the Tenant ID, you create a unique enterprise-wide business key. This guarantees data integrity while simplifying downstream querying and reporting.

Where to Add the Tenant ID

In multi-tenant designs, the Tenant ID should ideally appear:

• Hubs: As part of the business key or alternate key, ensuring uniqueness across tenants.
• Links: As part of the Hub references, ensuring uniqueness in combined relationships.
• Satellites: As a payload field for convenience, even if the hash key already includes the Tenant ID.

With this approach, every record in the Raw Vault can always be traced back to a specific tenant, which simplifies not only modeling but also governance and security.

Defining the Tenant ID

A natural question arises: what exactly is a “tenant”? The answer depends on your business context:

• It could be a client organization you serve.
• It could be a business unit, country, or factory in large enterprises.
• It might also be defined by data ownership—who is responsible for the dataset.

In some cases, you may also need a reserved Tenant ID for global or shared data that is not owned by any specific tenant. This ensures consistency and supports role-based access control.

Staging and Tenant Assignment

The Tenant ID is typically introduced already in the staging layer. How it’s assigned depends on the source system:

• Tenant-dedicated systems: Assign a constant Tenant ID for all data from that system.
• Multi-tenant systems (e.g., SAP, Salesforce): Extract and map the Tenant ID from existing fields (e.g., business unit, org ID).
• Global systems: Use a reserved Tenant ID (e.g., “GLOBAL”) when ownership is shared or unclear.

This is a hard rule (constant assignment), not a conditional transformation, which ensures repeatability and traceability.

Business Vault in Multi-Tenant Contexts

Once Tenant IDs are embedded in the Raw Vault, the Business Vault becomes much easier to design. Business rules can be applied consistently across tenants, while preserving tenant-specific contexts.

• Same-as Links: Crucial for resolving duplicate entities across tenants (e.g., the same customer appears in different client systems).
• Custom Satellites: Standardize where possible, but add additional Satellites for tenant-specific customizations.
• Wide PIT Tables: Be prepared for them—multiple tenants and diverse source systems naturally lead to broader structures.

At this stage, the goal is harmonization without oversimplification. A balance must be struck between common modeling and tenant-specific flexibility.

Designing the Information Mart

The Information Mart is where tenants—or the enterprise as a whole—derive insights. The challenge is to provide both:

• Enterprise-wide views: Merging data from all tenants for global reporting.
• Tenant-specific views: Allowing clients or business units to see only their data.

Common Mart Design

A single common dimensional model for all tenants reduces development overhead and supports standardized reporting. By including the Tenant ID in dimensions and facts, you can apply row-level security to restrict access per tenant.

When Separate Marts Are Needed

In some cases, specific tenants may require custom Information Marts. This is typically justified when:

• Unique KPIs or processes cannot be expressed in the common model.
• Legal or contractual reasons require strict separation of data.

However, these should remain exceptions. A well-designed common mart, filtered by Tenant ID, is usually sufficient for most tenants.

Role of Same-as Links in Reporting

To unify data across systems and tenants, Same-as Links are critical. These resolve entity duplicates across different tenants and systems (e.g., a product appearing under different codes in SAP and Salesforce).

Same-as Links can be sourced from:

• Raw data: Mapping tables provided by business or source systems.
• Calculated logic: Fuzzy matching, soundex, or other deduplication algorithms.

This harmonization enables the creation of enterprise-wide dimensions that span multiple tenants.

Security and Governance in Multi-Tenant Data Vaults

By embedding Tenant IDs throughout the model, row-level security becomes straightforward. Each record can be tied to a tenant, and access can be granted or denied accordingly. This simplifies compliance with data privacy regulations and contractual obligations.

Governance practices should also establish clear rules for:

• Defining and maintaining Tenant IDs.
• Managing ownership of global vs. tenant-specific data.
• Regular audits of access controls and Same-as Links.

Best Practices for Multi-Tenant Data Vaults

1. Add Tenant IDs early: Introduce them in staging to ensure consistency across the pipeline.
2. Unify where possible: Standardize Satellites for common structures, customize only when necessary.
3. Reserve global IDs: Create special identifiers for shared or unclear ownership data.
4. Secure with Tenant IDs: Use row-level security tied directly to the Tenant ID field.
5. Leverage Same-as Links: Resolve duplicates to support enterprise-wide reporting.
6. Design one common mart: Rely on row-level filtering instead of duplicating models per tenant.
7. Scale incrementally: Start with MVP, refine the model as you onboard new tenants.

Conclusion

Multi-tenant Data Vault design requires careful thought about uniqueness, ownership, and harmonization. By embedding Tenant IDs consistently across Hubs, Links, and Satellites, you not only preserve data integrity but also simplify governance and security. The Business Vault and Information Mart can then be designed to support both tenant-specific and enterprise-wide perspectives.

As organizations grow and onboard more clients or business units, this approach ensures scalability without overwhelming complexity. With clear governance, Same-as Links, and standardized mart designs, you can build a robust multi-tenant data warehouse that serves diverse needs while staying maintainable and secure.

Watch the Video

Cost Factors in Data Vault 2.0

Implementing a modern data platform is never a one-size-fits-all endeavor. Every company has unique requirements, legacy systems, and business needs. When it comes to Data Vault 2.0 (or more precisely, Data Vault 2.1), understanding the main cost factors early on can help organizations budget realistically and avoid painful surprises later. In this article, we will explore the typical phases of a Data Vault project, break down the major cost drivers, and share best practices for cost optimization and governance.

How a Data Vault 2.1 Project Looks Like

While no two projects are exactly alike, a Data Vault journey often follows a recognizable structure:

• Training & Onboarding: Equip your team with the right skills through workshops and tool hands-on sessions.
• Requirements Analysis: Define the first use case and design an architecture that matches requirements.
• Architecture & Setup: Prepare the platform, establish automation, and agree on standards and conventions.
• First Tracer Bullet Sprint: Deliver an end-to-end flow for one use case, ensuring the first business value is realized.
• Next Sprints & Cost Optimization: Add data sources incrementally, monitor resource usage, and optimize for efficiency.

The key difference compared to traditional data warehouse projects? Instead of building layer by layer and waiting months for business value, Data Vault emphasizes sprints with early, visible results. This agile approach not only accelerates delivery but also makes cost management more transparent.

The Major Cost Factors

What drives costs in a Data Vault implementation? Broadly, there are three categories:

1. People

The largest expense in most data projects is people. Costs include developers, data modelers, business analysts, and ongoing maintainers. Skilled professionals are needed not only for implementation but also for optimization and support. Investing in training early can reduce errors and long-term inefficiencies, making this a cost that pays back quickly.

2. Architecture

Whether you deploy on-premises or in the cloud, the technical backbone of your Data Vault incurs costs. Expect expenses for:

• Compute: Running queries, data transformations, and analytical workloads.
• Storage: Staging areas, raw vault, business vault, and marts require structured storage planning.
• ETL / ELT: Orchestration pipelines and integration layers that keep the system running smoothly.

3. Tooling

Tools for automation, governance, and project management also add to the bill. However, Data Vault’s standards lend themselves well to automation, reducing manual effort and long-term costs. Tools like dbt Core or Coalesce provide strong value, often at lower costs compared to legacy ETL suites.

Cost Optimization Strategies

Once the platform is running, cost optimization should not be an afterthought. Instead, it should be a guiding principle from the very beginning.

Define Responsibilities

Every instance, warehouse, or resource that incurs costs needs a clear owner. Without ownership, cloud resources often remain active long past their usefulness, silently increasing bills.

Set End Dates

Many dashboards and data pipelines are built for temporary projects. Without end dates, they keep consuming compute and storage. Assign a sunset date for every resource and re-evaluate its necessity over time.

Use Tags for Transparency

Cloud platforms allow tagging by project, department, or cost center. This makes it easier to allocate expenses and understand who is using what. Clear tagging also improves accountability and enables granular reporting.

Define Purpose

Every instance, pipeline, or report should have a clear business purpose. If you cannot state who benefits from it and why, it is a strong candidate for decommissioning.

9 Best Practices for Cost Monitoring

Effective cost management requires discipline. These nine practices provide a structured approach:

1. Involve Stakeholders: Ensure business and technical stakeholders understand cost implications.
2. Set Up Budget Alerts: Get notified when costs exceed defined thresholds.
3. Use Tags for Resources: Track usage by cost center, project, or department.
4. Create Cost Dashboards: Tools like Snowsight provide real-time insights.
5. Enable Usage Tracking: Know who uses which resources, and why.
6. Review Allocations: Regularly audit and rebalance resource usage.
7. Monitor Queries: Optimize inefficient SQL to cut unnecessary costs.
8. Optimize Warehouses: Use auto-suspend/resume and right-size compute.
9. Optimize Storage: Leverage zero copy cloning and transient tables to save space.

The Pareto Principle in Cost Saving

Not all cost optimizations are equal. According to the 80/20 rule, 20% of resources often account for 80% of costs. Identifying and addressing these high-impact areas—such as a handful of long-running queries—can unlock significant savings with minimal effort.

How Data Vault 2.0 Helps Reduce Costs

Beyond traditional cost-cutting measures, Data Vault 2.0 itself provides structural advantages that reduce expenses:

• Automation: Standardized entities make it possible to automate much of the raw vault, lowering developer workload.
• Agile Development: The tracer bullet approach allows incremental delivery of business value, avoiding expensive rework.
• Auditing & Compliance: Built-in historization and auditability support GDPR compliance, preventing costly legal issues.

Conclusion

Estimating the exact cost of a Data Vault 2.0 implementation is impossible—each project has unique factors. However, by recognizing the primary cost drivers (people, architecture, tooling), adopting disciplined cost management practices, and leveraging the automation and agility inherent in Data Vault 2.0, organizations can keep their projects efficient and cost-effective.

Cost optimization is not a one-time activity. It’s an ongoing process of review, accountability, and continuous improvement. With the right governance and monitoring in place, Data Vault 2.0 is not only a robust data architecture—it’s a cost-conscious one too.

Watch the Video

Complex Computed Satellites

When people first learn about computed satellites in Data Vault, they often encounter very simple examples: concatenating first and last names into a full name, or applying a basic calculation within a satellite. While these examples are valid, they don’t capture the full breadth of what computed satellites can do. In reality, computed satellites are a powerful mechanism for integrating, transforming, and enriching data across your vault — enabling business-driven insights while maintaining the Data Vault principles of auditability and traceability.

This article will walk through the broader concept of computed satellites, discuss how they are designed, and provide practical implementation patterns for handling more complex use cases.

What is a Computed Satellite?

At its core, a satellite in Data Vault is a structure that describes a business object (a hub or link) by holding descriptive attributes over time. A computed satellite differs from a raw satellite because its data does not come directly from the source system but is derived through business logic.

Examples include:

• Concatenating FirstName and LastName into FullName.
• Deriving an age from a birthdate.
• Producing calculated scores, risk categories, or classifications.
• Integrating attributes from multiple satellites across different hubs via links.
• Creating artificial relationships, such as product recommendations based on purchase history.

Importantly, a computed satellite isn’t just about the calculation itself — it’s about what the result describes and where it logically belongs in your model.

Step 1: Defining the Parent Entity

Before you build a computed satellite, you must answer a critical question: What does the result describe?

Every satellite attaches to either a hub (a business key) or a link (a relationship between keys). If your calculation produces attributes describing a customer, then the computed satellite belongs on the Customer Hub. If it describes a relationship between customers and products, it belongs on the respective link.

For example:

• A Full Name attribute describes a Customer Hub.
• A product recommendation score describes a Customer–Product Link.
• A risk category for an account describes an Account Hub.

This step ensures that your computed satellite stays aligned with the business meaning of your Data Vault model.

Step 2: Designing the Structure

Once you know the parent, the next step is to decide the structure of your results. Computed satellites can contain:

• Simple attributes (e.g., strings, numbers, dates).
• Multiple descriptive fields derived from logic.
• Semi-structured data, such as JSON or XML.

For example, you might calculate a JSON object capturing a customer’s segmentation profile, or an XML document describing a product configuration.

The important point: the satellite reflects the structure of your results, not the mechanics of how you implemented them.

Step 3: Implementing the Business Logic

After modeling comes implementation. Computed satellites can be populated in several ways:

SQL Views

The most common approach is to implement a computed satellite as a SQL view. Here, the SQL query both expresses the logic (e.g., joins, transformations, calculations) and defines the result structure. If SQL is sufficient for your business rules, this is often the simplest and most maintainable approach.

External Scripts (Python, R, etc.)

For more advanced transformations, machine learning, or statistical processing, you may use external code. A Python script, for example, could pick up data from raw satellites, apply complex algorithms, and write results back into a computed satellite.

The golden rule: the implementation must remain under your control. Even if a data scientist creates an initial model using tools like Azure ML or RapidMiner, once it becomes part of your Business Vault, the deployment and maintenance are governed centrally. This ensures auditability and consistency.

Materialized Tables

Sometimes, business logic requires intermediate storage. In this case, you may materialize computed satellites as physical tables populated via INSERT statements or stored procedures. This is useful for performance optimization or managing dependency chains in cascading business rules.

Complex Use Cases for Computed Satellites

1. Filtering or Subsetting Business Keys

Imagine a Partner Hub with a single satellite. Business users may want to see only clients, employees, or vendors. Computed satellites can create filtered subsets that bring the model closer to business expectations. While not always the cleanest design, this is a practical option in some industries, such as insurance.

2. Artificial Links

A link doesn’t always need to come directly from a source system. You can create artificial links based on computed relationships. For example, by analyzing purchase history, you might generate product recommendations — effectively creating a Customer–Product Recommendation Link.

3. Cascading Business Rules

A powerful pattern is to break complex logic into smaller, reusable steps:

1. Create a simple computed satellite that performs data cleansing or a basic calculation.
2. Use that result in a second computed satellite to apply additional rules.
3. Join results with other business vault entities to build richer attributes.

This cascading approach makes rules easier to maintain, document, and reuse — and avoids giant, unmanageable SQL queries filled with dozens of CTEs.

Best Practices

• Start with the business meaning: Always clarify what the result describes before modeling.
• Keep business logic in the Business Vault, not in downstream marts.
• Favor cascading rules over monolithic transformations — it improves maintainability and reusability.
• Control the code: All scripts, views, and procedures must be owned by the data warehouse team, not end-users.
• Support multiple technologies: SQL for straightforward logic, external scripts for advanced logic, and materialized tables where necessary.

Dependencies and Execution

When you cascade rules or materialize results, you introduce dependencies. One entity must load before another. To manage this, many teams implement dependency tables that track loading order. This enables recursive or automated job scheduling, ensuring consistency across the Business Vault.

Virtualized approaches (SQL views) are often easier, since query optimizers can resolve dependencies dynamically. Materialized approaches, however, provide better performance and control at scale.

Why Computed Satellites Matter

Computed satellites are more than “extra calculated fields.” They enable organizations to:

• Bridge the gap between raw data and business expectations.
• Implement business rules in a controlled, auditable environment.
• Support advanced analytics and machine learning workflows inside the Data Vault framework.
• Enable modular, reusable logic that scales across domains and use cases.

By treating computed satellites as first-class citizens in your Business Vault, you ensure that business logic is not scattered in marts, reports, or ad hoc scripts — but is instead centralized, governed, and reusable.

Conclusion

Computed satellites in Data Vault can be as simple as a concatenated name, or as complex as multi-step cascading business rules that derive artificial relationships. The key is to start by identifying what your result describes, attach the satellite to the correct parent, design the structure of your attributes, and then implement the logic in a controlled, maintainable way.

Whether implemented via SQL, Python scripts, or materialized processes, computed satellites should remain under the stewardship of your data warehouse team. By following best practices, you’ll unlock the full potential of the Business Vault — keeping it business-aligned, auditable, and ready for advanced analytics.

Watch the Video

Hashing on Databricks

Hashing is a core element of Data Vault modeling. Hash keys are used to uniquely identify Hubs, Links, and Satellites, and they need to be consistent, reproducible, and efficient. A common debate is whether to use widely adopted hashing algorithms like MD5 or SHA-1, or to opt for faster and smaller hash functions such as xxhash64, which Databricks natively supports.

The question is simple: What if we stored hash keys as 64-bit integers (int64) using xxhash64 instead of 128-bit MD5 values? On the surface, this looks attractive — faster generation, better join performance, and reduced storage. But as we’ll explore in this article, the trade-offs around collisions, scalability, and platform independence make this a risky choice in Data Vault 2.0 architectures.

Why Smaller Hash Keys Look Tempting

There are clear benefits to using smaller hash values such as int64:

• Speed of generation: Algorithms like xxhash64 or CRC-64 are significantly faster than MD5 or SHA-1. OpenSSL and Linux benchmarks consistently show xxhash64 outperforming older cryptographic functions.
• Join performance: Joining 64-bit integers is naturally faster than joining 128-bit binary or 32-character string columns.
• Storage efficiency: An int64 hash key requires only 8 bytes, compared to 16 bytes for binary MD5 or 32 bytes for an MD5 stored as a string.

From a performance perspective, the appeal is undeniable. But in Data Vault, performance is not the only concern. The fundamental question is: How safe are these smaller hashes when used as surrogate keys at scale?

The Risk of Collisions

A hash collision occurs when two different inputs produce the same hash value. In Data Vault, this means two different business keys could be treated as the same Hub, Link, or Satellite record — corrupting your data integrity.

With 64-bit hashes, the number of possible unique values is 2^64. While that sounds huge, probability tells a different story when you start loading millions or billions of rows. The so-called birthday paradox makes collisions far more likely than intuition suggests.

For example:

• With just 10,000 records, a 32-bit hash already has a 1 in 100 chance of collision.
• A 64-bit hash greatly reduces the risk, but at large scales (hundreds of millions of rows), the probability becomes uncomfortably high.
• A 128-bit hash (MD5) pushes collision risk into the realm of trillions of rows before it becomes statistically relevant.

That’s why MD5 and SHA-1 — despite being slower — are still standard in Data Vault: they provide mathematically safe keyspace sizes for large enterprise datasets.

Performance vs. Integrity

The argument for int64 hashing often emphasizes query performance:

• Joins on numeric columns are faster than joins on strings or binary.
• xxhash64 is faster to compute than MD5.

These points are true. But in practice, the cost of a collision far outweighs the performance gains. A single collision can undermine the entire lineage of your data warehouse. Once data integrity is compromised, every downstream analytic and report is suspect.

As a result, most Data Vault practitioners will gladly accept the slightly higher CPU cost of MD5 or SHA-1 in exchange for peace of mind.

Platform Portability Matters

Another issue with xxhash64 is platform availability. While Databricks supports it, you also need to consider:

• Can you compute xxhash64 in Snowflake, BigQuery, SQL Server, or Oracle?
• Can you reproduce xxhash64 consistently in Python, Java, or ETL tools?
• Will the algorithm be supported 5–10 years from now?

One of the strengths of MD5 and SHA-1 is their ubiquity. They are implemented in nearly every database, programming language, and ETL platform. This cross-platform reproducibility is essential when building a Data Vault that may span multiple systems. By contrast, xxhash64 locks you into Databricks (or requires custom implementations elsewhere).

Why 128-Bit is the Safe Minimum

Let’s compare hash sizes:

• CRC-32: Very fast, but collisions appear after ~10,000 records. Unusable for Data Vault.
• xxhash64 / CRC-64: Better, but collisions become likely as datasets grow into the hundreds of millions.
• MD5 (128-bit): Standard choice. Safe up to trillions of rows.
• SHA-1 (160-bit): Provides even more headroom for extremely large datasets.

For most enterprises, MD5 hits the sweet spot: fast enough, widely supported, and statistically collision-free at realistic data volumes.

Alternative Approaches

If performance is a serious concern, consider these approaches instead of shrinking hash size:

• Binary storage: Store MD5 as a 16-byte binary instead of a 32-character string. This cuts storage in half and improves join performance.
• Partitioning strategies: Optimize joins by partitioning your Data Vault tables, reducing the need for full-table joins.
• Hardware acceleration: Modern CPUs have optimized instructions for MD5 and SHA-1, making them faster than you might expect.
• Consider SHA-256 only if required: While SHA-2 offers stronger guarantees, it’s rarely necessary in Data Vault and adds performance overhead.

Summary: Should You Use xxhash64 in Data Vault?

While xxhash64 looks attractive in Databricks because of its speed and smaller footprint, it’s not a good fit for Data Vault 2.0:

• Collision risk is too high for large-scale data warehouses.
• Portability is limited — you risk vendor lock-in.
• Long-term maintainability suffers if your algorithm isn’t standardized across platforms.

In Data Vault, data integrity always comes first. That’s why MD5 (128-bit) or SHA-1 (160-bit) remain the recommended standards for hash keys. They provide the balance of performance, portability, and collision safety needed for enterprise-scale solutions.

Conclusion

If you’re working in Databricks, it might be tempting to adopt xxhash64 for hash keys. But resist that temptation. The risks of collisions and platform lock-in far outweigh the benefits. Stick with MD5 or SHA-1 for your Data Vault hash keys, store them efficiently as binary values, and optimize performance through storage and join strategies.

Remember: a faster broken key is still a broken key. In Data Vault, correctness and consistency are always the highest priority.

Watch the Video

Joining SCD2 Tables

When working with Data Vault 2.0, one of the most common challenges is how to handle Slowly Changing Dimension type 2 (SCD2) tables when loading Link Satellites. Imagine a scenario where you need to join three SCD2 tables, each with valid_from and valid_to dates, and bring them together into a single Satellite hanging from a Link. At first, this might sound straightforward, but the details matter — and depending on your approach, you could make your warehouse harder to maintain, less performant, or less flexible.

In this article, we’ll walk through the best practices for handling this situation, based on Data Vault principles. We’ll discuss why you shouldn’t rush into joining multiple SCD2 tables into one Satellite, how to handle business timelines, the role of PIT and T-PIT tables, and strategies for keeping your design scalable and future-proof.

Understanding SCD2 in the Context of Data Vault

Slowly Changing Dimensions type 2 are a way to track historical changes in dimensional data. Each record typically has valid_from and valid_to dates that describe its period of effect. In a Data Vault model, however, the focus isn’t on interpreting those business dates upfront. Instead, the Raw Data Vault stage is all about capturing what the source gives you, as-is.

That means when we first bring data into the Raw Vault, the valid_from and valid_to fields should be stored as descriptive attributes — not as part of the Satellite’s primary key. Trying to interpret and align them too early will only create unnecessary complexity.

Why Not Load One Satellite From Three SCD2 Tables?

At first glance, it may seem attractive to combine all three SCD2 tables directly into one Satellite. But this goes against Data Vault best practices:

• Each Satellite should usually source from a single table or source system.
• Denormalizing multiple sources into one Satellite complicates your Raw Vault and makes it harder to maintain.
• You risk having to undo the denormalization later when new requirements come in.

The recommended approach is to create at least three Satellites — one per SCD2 table. You may even need more Satellites if you have to split them based on privacy, rate of change, or security rules. This simplifies your Raw Vault and sets you up for flexibility later.

Handling Validity Dates in Satellites

Once your Satellites are created, the question is how to handle valid_from and valid_to. Here are two key approaches:

1. Multi-Active Satellites

If your SCD2 records represent multiple simultaneously valid states (for example, different price lists where both current and future prices are valid), you can use a multi-active Satellite. In this design:

• The primary key is composed of hash_key + load_date + subsequence.
• valid_from and valid_to are stored as descriptive attributes, not as key parts.
• A staging-generated subsequence ensures uniqueness within a load.

2. Standard (CDC) Satellites with Subsequence

If only one record is active at a time (the classic SCD2 case), then you don’t need a multi-active design. Instead:

• Stick with the standard Satellite primary key: hash_key + load_date.
• Handle multiple intraday changes by adding micro- or nanosecond subsequences to load_date.
• This ensures only one active row per parent key at any given time.

Choosing between multi-active and CDC-style Satellites depends entirely on your data. Do you need multiple simultaneously valid records? Or does one replace the other in sequence? Your answer determines the right design.

Aligning Data with PIT Tables

Once the three Satellites are loaded, you’ll need to bring them together for reporting. This is where PIT (Point-in-Time) tables come in. PIT tables align deltas across Satellites to a common snapshot date, making it possible to present a unified view of related changes.

There are two main flavors:

• Standard PIT – aligns data based on load_date (technical historization).
• Temporal PIT (T-PIT) – aligns data based on business timelines like valid_from and valid_to.

A T-PIT lets you activate rows based on both the technical and business timelines. However, it comes with maintenance challenges: whenever valid_from or valid_to dates change, you may need to rebuild PIT partitions — which can be expensive if large date ranges are altered.

Where Should You Apply Business Timelines?

There are three strategies for applying business timelines like valid_from and valid_to:

1. Downstream in reporting or dashboards – simplest to maintain, and business users can define how timelines should be applied.
2. In dimension views – apply filters and conditions directly in the SQL layer that feeds reports.
3. Upstream in PIT/T-PIT – most performant but requires heavier maintenance whenever business timelines change.

A practical approach is to start downstream and only move timeline application upstream if performance issues demand it.

Building a Business Satellite

Once your PIT table aligns the Satellites, you may still need a Business Satellite (or Computed Satellite). This Satellite:

• Uses the PIT table as its foundation.
• Combines attributes from the three original Satellites using COALESCE or other business rules.
• Optionally applies T-PIT logic if strict business timeline alignment is required.

By separating Raw Satellites (which store raw source data) from Business Satellites (which apply interpretation and business logic), you preserve Data Vault’s flexibility while still meeting analytical requirements.

Best Practices Recap

• Create one Satellite per SCD2 source table — don’t denormalize too early.
• Treat valid_from and valid_to as descriptive attributes in the Raw Vault.
• Use multi-active Satellites only when multiple records are simultaneously valid; otherwise, stick with standard CDC Satellites.
• Align Satellites with PIT tables; consider T-PITs only if business timelines must be applied upstream.
• Whenever possible, push business timeline interpretation downstream to dimensions or reports for easier maintenance.
• Use Business Satellites when you need to merge multiple sources into a single unified view.

Conclusion

Joining multiple SCD2 tables into a single Satellite is rarely the right first step in a Data Vault 2.0 implementation. Instead, build your Raw Vault Satellites closely aligned with their sources, use PIT tables to align changes, and apply business timelines carefully — starting as far downstream as possible. This approach keeps your architecture maintainable, flexible, and scalable while still supporting complex historical analysis.

By following these principles, you’ll not only simplify your data model but also give your business users the power to interpret validity ranges in ways that make sense for them — without locking your warehouse into rigid rules that are hard to maintain.

Watch the Video