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_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_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.