DataJoinery’s Data Vault key concepts.
How DataJoinery turns your conceptual model into a Data Vault on Snowflake: the entity types, relationships, satellites and keys it generates, and the names it gives them.
Entities
Key concepts: the people, places and things your business runs on. The type you give each conceptual entity decides what DataJoinery generates.
- Hub
- A key concept with a set of defined attributes, such as a person, place or thing. Creates a _h object.
- Hub and link
- A key concept with attributes about itself and its key relationships: events and processes such as orders and invoices. Creates _h and _l objects.
- Dependent-child link
- An entity that can’t exist without its parent: an order item means nothing without its order. Creates a _dcl object.
Relationships
Relationships connect key entities and have a direction: one-to-one, one-to-many, zero-to-many or many-to-many.
- Link (default)
- A standard link between two or more related entities.
- Derived (exploratory) link
- A link derived from the data rather than from primary and foreign keys. Derived relationships show in green on the conceptual model, to flag that they contain logic.
- Hierarchical link
- A hierarchy between an entity and itself, or between two separate entities.
- Same-as link
- Marks two instances as the same thing, within one entity or across two, and is used to deduplicate data.
Satellites
Satellites hold the descriptive properties of an entity or relationship, loaded from your data feeds. Their attributes can be split three ways.
Rate of change
How often the attribute changes: high, medium or low. Splitting by rate can reduce storage.
Privacy
Personal or non-personal data. Keeping personal data apart helps with GDPR and POPIA compliance, secures PII, and makes it easy to forget a party.
Security
Levels such as top secret, public or internal employees only, so you can govern who has access to each type of data.
- Non-historized data
- For data that never changes. Records load without checking the HDIFF first, which avoids unnecessary processing and speeds up loads.
- Status tracking satellite
- Generated for every satellite on a hub or a link. It tracks whether each instance was inserted (I), updated (U) or deleted (D), so deleted values stay out of downstream calculations and reporting.
- Effectivity satellite
- Applies to links only. It tracks the period each relationship instance was active, so you can tell whether a relationship is current.
- Business satellite (BSAT)
- Describes an entity and can hold business logic that turns data into meaningful information. One is defined automatically for each entity in the conceptual model, with the fields you marked as important.
Point-in-time and bridge tables
Point-in-time (PIT) tables
Snapshots of the current version of satellite data at a point in time. They store the hash key (HK) and latest load datetime (LDTS) of each associated satellite, status tracking satellites included, and speed up performance significantly without replicating the raw data.
Bridge tables, or summaries
Store the current hubs, links and your own metrics at a point in time. A PIT table is per hub, while a bridge combines any number of hubs and links. Bridge tables form the basis of facts.
Keys
- Business key
- One or more natural keys that uniquely identify an instance of an entity: a bank account number, or an order ID.
- Hash key
- The business keys combined and run through a cryptographic hash function, such as MD5 or SHA1, to produce a unique key.
- Business key collision code (BKCC)
- Added to the hash key calculation so business keys from different systems never collide. It defaults to the string ‘default’, and can be overridden in the mapping wizards or when editing the logical entity.
- Driving key
- In a link, the hash key of the hub that stays constant. Because relationships change over time, the driving key decides which combination of hubs is currently effective.
- Dependent-child link hash keys
- A DCL has its own HK plus a DCL_HK, built from its business keys and those of its parent and the parent’s related entities, excluding many-to-many relationships. If an order is linked to a customer, an order item’s DCL_HK combines the order item ID, order ID and customer ID.
Driving key example
A link stores the relationship between a Customer hub and a Location hub. When a customer moves, the relationship with the old location is no longer valid, and the one with the new location becomes active. The customer stays constant, so the Customer’s hash key is set as the driving key.
Naming conventions
The object DataJoinery creates for each entity type and relationship type in the conceptual model.
| Entity or relationship | Object created | Example |
|---|---|---|
| Hub | _h | customer_h |
| Hub and link | _h and _l | order_h, order_l |
| Dependent-child link | _dcl | order_item_dcl |
| Default link between two hubs | entity_a_entity_b_l | customer_location_l |
| Hierarchical, or a default link to the same entity | entity_hl | employee_hl |
| Derived (exploratory) | _el | Named after the relationship |
| Same-as | _sal | Named from both entities |
The _l suffix can be changed in project settings, and link names can be overridden by giving the relationship a name.
See your own model generated.
Start with a small use case, and speak to a DataJoinery data specialist today.