# dbt Model Building Principles - Follow [the SQL style guide](docs/guides/sql_style_guide.md), and utilise SQLFluff as you code. - Optimise code for readbility, maintainability, and robustness rather than fewer lines of code - Extra Lines are free, time is expensive! - Keep Row line length as short as possible (whilst not sacrificing verbosity) - Be as verbose as possible with names - names should be as descriptive as possible and explain what each object does as fully as possible - Our code should be as dry (Don't Repeat Yourself) as possible - Utlilize intermediate views if you find yourself referencing the same query, CTE, or block of code throughout multiple models - Utlise dbt macros as often as possible. - Use CTEs _early & often_ - Add as much cleaning and new column logic to base views as possible, e.g. casting columns to their correct datatype, or adding `case when` statements that should propogate throughout the model - Overall, _consistency_ is key rather than following individual guidelines to the letter. - This is a living document - if we don't like something, we can change it - submit a Github Issue & we will discuss it during a guidance drop-in session. # Model Building Guidance ## General Guidance - Where possible, models names should be pluralized e.g. `summary_streams`, `ritmogestion_uploads` - Each model should have a [primary key]('https://docs.getdbt.com/terms/primary-key#:~:text=A%20primary%20key%20is%20a,uniqueness%20constraint%20for%20each%20row..'). If your table does not have a primary key supplied from data ingestion, you can create a [surrogate key](https://docs.getdbt.com/blog/sql-surrogate-keys). ``` jinja /* you can use the dbt surrogate key macro */ {{ dbt_utils.generate_surrogate_key(['field_a', 'field_b'[,...]]) }} ``` ``` sql with surrogate_key_example as ( select col1, col2, {{ dbt_utils.generate_surrogate_key(['col1', 'col2']) }} as id from some_table ), select col1, col2, id from surrogate_key_example ``` - Do not use abbreviations e.g. `str` for `streams` - be as verbose as possible - Do not use [reserved words](https://www.google.com/search?q=reserved+words+snowflake&rlz=1C5GCEM_enGB1049GB1049&oq=reserved+words+snowflake&aqs=chrome..69i57j0i22i30j0i390i650l3j69i60.3975j0j1&sourceid=chrome&ie=UTF-8) for column names e.g. `create` or `sample` - Booleans should be prefixed with `is_` or `has_` e.g. `is_editorial_playlist` rather than `editorial_playlist` - Timestamp columns should have an `_at` suffix e.g. - Date columns shoul have a `_date` suffix - Where possible, date columns should be in the past tense e.g. `created` or `streamed` - Price/revenue fields should be in decimal currency (19.99 for $19.99. If a non-decimal currency is used, indicate this with a suffix (price_in_cents). - Schema, table, & column names with multiple worlds should be in `snake_case` e.g. `device_type` not `DeviceType` or `deviceType` ## Model Structure - dbt Labs suggests a consistence ordering of data where you group and label columns by type. This will help with join errors, and with model readibility. - Comments are added for readibility - Common groups could be: - [`primary key`](https://www.secoda.co/glossary/primary-key) - `surrogate key` - `timestamps` - `dates` - `statuses & properties` - for things like `is_editorial` or `dsp_type` - `booleans` - `metrics` - for numerics - `aggregates` ### Example ```sql with monthly_spotify_streams as ( select * from {{ ref('agg_spotify_model') }} ), final as ( select /* primary key */ a_primary_key, /* surrogate key */ a_surrogate_key, /* ids */ isrc, upc, label_id, track_uri, spotify_track_id, source_uri /* dates */ activity_date, /* status & properties */ user_country_code, user_account_type, /* metrics */ user_age /* aggregates */ sum(streams) as sum_streams from monthly_spotify_streams ) select * from final ``` # Project Structure - At a conceptual level we aim to build a stack that looks like this ![Stack Drawing](docs/../../images/stack.png) - New models added to the project should be structured as follows. ``` . ├── README.md ├── analysis ├── dbt_project.yml ├── macros ├── model │ ├── marts │ │ ├── analytics_store_streaming_models │ │ | ├── amazon_streaming_models │ │ │ ├── ├── daily_agg_amazon_streams.sql │ │ │ ├── ├── weekly_agg_amazon_streams.sql │ │ │ ├── ├── monthly_agg_amazon_streams.sql │ │ │ ├── └── amazon_store_streams.yml | | | ├── └── intermediate │ │ | ├── apple_streaming_models │ │ │ ├── ├── daily_agg_apple_streams.sql │ │ │ ├── ├── weekly_agg_apple_streams.sql │ │ │ ├── ├── monthly_agg_apple_streams.sql │ │ │ └── ├── apple_store_models_streams.yml | | | ├── intermediate │ │ │ ├── int_agg_apple_streams_staging_1.sql │ │ │ ├── int_agg_apple_streams_staging_2.sql │ │ │ └── int_apple_store_models.yml │ │ ├── moments │ │ │ ├── weekly_agg_trending_genres_spotify.sql │ │ │ ├── weekly_agg_trending_genres_tiktok_hits.sql │ │ │ ├── weekly_trending_tracks_combines_tiktok.sql │ │ │ ├── moments.yml │ │ │ └── intermediate │ │ │ ├── int_weekly_amazon_trending_tracks.sql │ │ │ ├── int_weekly_apple_trending_tracks.sql │ │ │ ├── int_weekly_spotify_trending_tracks.sql │ │ │ └── int_moments.yml │ │ └── etc. │ ├── base │ │ ├── prod │ │ │ ├── sources_prod.yml │ │ │ ├── dim_track.sql │ │ │ └── dim_release.sql │ │ ├── orchard_app_reporting_art_relations │ │ │ ├── sources_orchard_app_reporting_art_relations.yml │ │ ├── release_exclusive.sql │ │ ├── artist_info.sql │ │ └── contact.sql │ └── etc. │ ├── packages.yml ├── seeds └── etc. ``` # Model Documentation - Documentation for all source and mart level models (or logical groups of models) should be placed in the source or mart directory inside a schema `.yml` file - This is for the following reasons: - Makes it easier to find the documentation and tests for a model. - Clearly shows which models have documentation/tests and which don't. - Helps avoid version control merge conflicts. - A model is not complete without tests and documentation. - A MODEL IS NOT COMPLETE WITHOUT TESTS AND DOCUMENTATION - Intermediate models do not need to be documented # Base Models - The Base directory contains a directory for each set of source-centric - Base folders should correspond to an individual dbt `source` e.g. the `prod` source should have a corresponding `prod` folder in the `base` directory - Each base folder should contain a `source_name.yml` folder, defining the sources that that exist withing in source-specific directory ## Base Views - Base models are the first layer of modelling on top of the source directory - Base models should be prefixed with `base_` - Unless there are specific use cases, base views should be materialized as views - Base views must not contain any `join`, `where`, or `having` clauses that might limit the scope of the data - Base views must select from a dbt source using the dbt souce macro e.g. `{{ source('prod','spotify') }}` and not directly from snowflake tables - Base views are the place to do the following: - Renaming & Aliasing in line with our naming conventions - Performing data type corrections and and extractions of nested data e.g. json objects - Fixing timezones to our agreed standard (what is this?) dbt ## Intermediate Folders - For some use cases it may be neccesary to use intermediate tables. Intermediate table are useful for the following reasons: - Writing dry code: where the same table might be consumed by multiple models, it makes sense to do this one in an intermediate view. For example - adding large `case when` statements, adding in join logic, unioning multiple realted tables together, or some tables need to be joined together so as to add in logic that must propogate through the rest of the project. - Make large models easier to read - When models are getting very long and unwieldy - Intermediate models, as a rule, are not exposed in the marts folder # Production Tables (Core & Marts) - We have two types of production layers, core models tables & mart models. - Core: these are our dimension and fact tables that provide business information and act as sources for aggreated and transformed models - Marts: Self contained, denormalized models are specific granularities, rady to be used in reporting, analytics, machine learning, and elsewhere. - It is common and desireable that the same entities and metrics will appear in multiple marts - Core Models will often be base views or tables. - The marts directory should contain a folder for each business-centric production model (mart) - Marts model processes - For example, a store model mart models streams from all of each dsp - Aggregate models should be prefixed with the grain of the aggregation of the aggregation e.g. `monthly_`, `weekly_`, `monthly_` - Other developers should be able to infer what a model is doing from the model name - Utilise `dbt_project.yml` to create schemas for subfolders where this makes logical sense ```yaml models: analytics_model: streaming_models: +schema: "streaming_models" metadata_models +schema: "metadata_models; ``` These will appear in the `intelligence` database prefixed with `dbt_` e.g. `dbt_prod_streaming_models` or `dbt_jbloggs_streaming_models` # Miscellaneous - When using Jinja delimiters, put spaces on the inside of the delimiters ```jinja /* Good */ {{ ref('customers') }} /* Bad */ {{ref('customers')}} ```