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.