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
ETL_MODIFIED_DATETIME column which records the date and time the entity was last updated.
History is tracked in the
USERS 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 is 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.