This section explains the conventions used when building the Snowflake database and functionality for implementing various features such as history tracking and support for ETL.
Each table includes ROW_LOAD_DATETIME and ROW_IS_DELETED columns. Data that is deleted is marked as true in this column and the removed from the source table.
History is tracked in the USER_MANAGER table using Slowly Changing Dimension (SCD) type 2.0 with the VALID_FROM_DATETIME and VALID_TO_DATETIME columns. The VALID_TO_DATETIME is NULL for data that is currently valid. Historical data is available from August 2022. Contact us If you need earlier data.
This a list of conventions used when designing the database:
-
The schema is in 3rd normal form. Some data may be denormalized for query ergonomics.
-
Table names are plural and snake_case.
-
Column names are snake_case.
-
Id columns are suffixed with _id and are of varchar type and should be treated as such regardless of their content.
-
Date and time column names are suffixed with _datetime and are of type TIMESTAMP_TZ which includes UTC offset.