How to make ​ for data warehouse zipper table in data warehouse enterprises

This article mainly shows you "how to make data warehouse zipper tables in data warehouse enterprises". The content is simple and clear. I hope it can help you solve your doubts. Let me lead you to study and learn the article "how to make data warehouse zipper tables in data warehouse enterprises".

Zipper list

The zipper table is defined for the way the table stores data in the data warehouse design. As the name implies, the so-called zipper is to record history. Record information about all the changes in a thing from its beginning to its current state.

Here is a zipper list that stores the most basic information about the user and the life cycle of each record. We can use this table to get the latest data for the day as well as previous historical data.

Description:

T_start_date represents the start time of the record's life cycle, and t_end_date represents the end time of the record's life cycle.

T_end_date = '9999-12-31' indicates that the record is currently in a valid state

If you query all currently valid records, then select * from user where t_end_date = '9999-12-31'

If you query a historical snapshot of 2017-01-01, then select * from user where t_start_date