To create a Hub table in a Data Vault model using SQL, you must first define the field structure that contains a unique hash key, business ID, loading time, and source system, and ensure uniqueness and record loading time. For example: CREATE TABLE hub_customer (customer_hash_key CHAR(32) PRIMARY KEY, customer_id VARCHAR(50) NOT NULL, load_date DATE NOT NULL, record_source VARCHAR(255) NOT NULL); It is recommended to add an index for customer_id to improve query performance. The Link table is used to connect multiple Hubs. It must contain the hash keys, loading time and source system of the related Hubs, and combine multiple hash keys into primary keys, such as: CREATE TABLE link_order (order_hash_key CHAR(32), customer_hash_key CHAR(32), product_hash_key CHAR(32), load_date DATE NOT NULL, record_source VARCHAR(255) NOT NULL, PRIMARY KEY (order_hash_key, customer_hash_key, product_hash_key)); Each hash key should not be empty and the foreign key points to the corresponding Hub and does not store business attributes. The Satellite table is used to store descriptive attributes and change history of Hub or Link, such as: CREATE TABLE sat_customer_detail (customer_hash_key CHAR(32), load_date DATE NOT NULL, customer_name VARCHAR(100), address VARCHAR(255), phone VARCHAR(20), is_current BOOLEAN DEFAULT TRUE, PRIMARY KEY (customer_hash_key, load_date)); Track changes by inserting new records, and filtering the latest records using window functions. When data is loaded, you need to deduplicate. You can use NOT EXISTS or MERGE statements to unify the Hash function and optimize the deduplication efficiency.

If you are doing data warehouse modeling, or when you are exposed to data models that require flexible expansion and traceability, you may have heard of the Data Vault model. It is different from the traditional star/snowflake model, emphasizing flexibility, history tracking and scalability. SQL plays a very important role in it, not only for building core structures such as Hub, Link, and Satellite, but also for loading, maintaining and querying these objects.

Here are some common SQL usage scenarios and suggestions to help you better understand and apply Data Vault modeling.

How to create a Hub table in SQL

Hub is one of the cores of Data Vault, which stores unique identifiers of business entities. For example, customer ID, product ID, etc. The key point in creating a Hub table is to ensure uniqueness and record loading time.

A typical Hub table structure is as follows:

 CREATE TABLE hub_customer (
    customer_hash_key CHAR(32) PRIMARY KEY,
    customer_id VARCHAR(50) NOT NULL,
    load_date DATE NOT NULL,
    record_source VARCHAR(255) NOT NULL
);

Explain some points:

• customer_hash_key is usually a unique key generated by hashing based on customer_id
• load_date records the first load time, which facilitates subsequent tracking of changes
• record_source tags the source system from which this record comes from

Suggestion: In practice, in order to improve query performance, you can consider adding an index to customer_id (although not a primary key).

How to connect multiple Hubs in a Link table

The function of Link tables is to establish relationships between multiple Hubs. For example, if an order involves multiple entities such as customers, products, salesmen, etc., it needs to be linked with Link.

The key points of the design of Link table are:

• Hash keys containing all related Hubs
• No other attribute information is included
• The primary key is composed of multiple hash keys

Sample SQL:

 CREATE TABLE link_order (
    order_hash_key CHAR(32),
    customer_hash_key CHAR(32),
    product_hash_key CHAR(32),
    load_date DATE NOT NULL,
    record_source VARCHAR(255) NOT NULL,
    PRIMARY KEY (order_hash_key, customer_hash_key, product_hash_key)
);

Pay attention to a few details:

• Each hash key must be non-empty and the foreign key points to the corresponding Hub table
• The Link table itself does not have business attributes, and is only responsible for the connection relationship.

Common misunderstandings: Sometimes some factual attributes are placed in the Link table, which violates the principles of Data Vault and should be placed in Satellite.

How to handle property changes in Satellite tables

Satellite stores descriptive attributes of Hub or Link, such as customer name, address, phone number, etc., as well as the history of changes in these attributes.

A basic Satellite table structure is as follows:

 CREATE TABLE sat_customer_detail (
    customer_hash_key CHAR(32),
    load_date DATE NOT NULL,
    customer_name VARCHAR(100),
    address VARCHAR(255),
    phone VARCHAR(20),
    is_current BOOLEAN DEFAULT TRUE,
    PRIMARY KEY (customer_hash_key, load_date)
);

Key points:

• Every time the attribute changes, a new record is inserted
• is_current field marks the current latest record
• The latest records can be filtered through window functions

For example, if you want to obtain currently valid customer information:

 SELECT *
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY customer_hash_key ORDER BY load_date DESC) AS rn
    FROM sat_customer_detail
) sub
WHERE rn = 1 AND is_current = TRUE;

Recommendation: Some systems will automatically manage the is_current field at the ETL layer, but manual control is more flexible, especially during the debugging phase.

Data loading and deduplication strategies

One of the difficulties of the Data Vault model is the deduplication problem during data loading. Because every time you load, you have to determine whether there is a record, and avoid repeated insertion.

A common practice is to check whether there is a hash key before loading:

 INSERT INTO hub_customer (...)
SELECT ...
FROM source_table s
WHERE NOT EXISTS (
    SELECT 1
    FROM hub_customer h
    WHERE h.customer_hash_key = HASH(s.customer_id)
);

You can also use the MERGE (or UPSERT) statement, depending on your database support:

 MERGE INTO hub_customer AS target
USING (
    SELECT HASH(customer_id) AS hash_key, ...
    FROM source_table
) AS source
ON target.customer_hash_key = source.hash_key
WHEN NOT MATCHED THEN
    INSERT (...);

Notice:

• The selection of Hash functions should be unified, otherwise it is easy to cause data errors.
• The deduplication logic should be as efficient as possible, especially under large data volumes

Tips: You can aggregate the data once in the staging layer, reduce duplicates and then write them to the target table.

Basically that's it. The application of SQL in Data Vault is not just a simple table building and querying, but runs through the entire process of modeling, loading and maintenance. Understanding the responsibilities of each object and using SQL techniques reasonably can make your data model both stable and efficient.

The above is the detailed content of SQL for Data Vault Modeling. For more information, please follow other related articles on the PHP Chinese website!