-
Notifications
You must be signed in to change notification settings - Fork 0
14. DBT Best Practices
This document outlines the best practices to be followed when working with DBT (Data Build Tool) in our projects. These practices cover various aspects, including data source design, coding style, project architecture, documentation, testing, and collaboration.
The best practices describe expectations for delivery of all Analytics projects at Ona. Yet, it is understood that occasionally it will make sense to stray from the default set of rules (e.g. to accommodate specific client requirements for handover). These rules should provide strong defaults and anyone moving away from them should have good reason and be able to articulate that to the rest of the team.
- How we structure DBT projects:
- Advanced materialization:
- DBT fundamentals:
- Refactoring SQL for modularity:
- When initiating a DBT project, it's crucial to clearly define the data sources that will be utilized in a sources.yml file.
- This includes sources such as Google Sheets, Forms, and Directus Tables.
- Ensure that all the sources in your sources.yml file are currently in use i.e. no stale sources.
- Match the source name to the schema name
- By understanding the structure and nature of these sources, the subsequent data modeling process becomes more straightforward.
- Ensure that you have the staging, intermediate and marts folders within the models folder.
- All your models should have unique identifiers (primary key) for example; id.
- All model names should be in plural for example; monthly_beneficiaries.sql
- The default schema name for the DBT production instance should be the project name
-
Staging models act as an intermediary step between raw data sources and the transformed data models.
-
Each staging model should have a distinct purpose and a 1-1 relationship with the source tables that is same number of rows. Avoid unnesting and filtering at this stage, unless you have strong reasons as to why you need to.
-
Staging models should be described in a yml file. Column descriptions for the models should be included if the code is to be handed over to the client.
-
Use stg_model_name as the naming convention.
-
Ensure you have the generic tests added to the primary key i.e. unique and not null
-
Add subfolders for the different sources in your staging folder for example;
├── staging
│ │ ├── onadata (subfolder 1)
│ │ │ ├── stg_survey.sql
│ │ │ ├── ona_data_sources.yml
│ │ │ ├── ona_data_models.yml
│ │ ├── googlesheets (subfolder 2)
│ │ │ ├── stg_sheet.sql
│ │ │ ├── google_sheet_sources.yml
│ │ │ ├── google_sheet_models.yml
-
If you need to upload some datasets directly to the database, those should go in the manual_upload schema
- Intermediate models are useful for stacking layers of logic with clear and specific purposes to prepare our staging models to join into the entities we want
- Unnesting, joins and further extraction of the data are performed here
- Use int_model_name as the naming convention.
- You can also arrange the intermediate models in subfolders depending on the tasks that you are performing for example unnesting.
-
These are the final models that power your visuals/answer the business question(s).
-
Include the model description in the mart_models.yml file i.e. what it's trying to do and what each column represents.
-
They must be stored in the database as tables.
For client deployments, store these in a projectname_reporting schema.
- These are models that can be used as your reference models. For instance, when you need a days table in your database, you can use the date spine macro under dbt-utils to generate the dates. This model can be incorporated in the utilities folder.
- Models in this folder do not have a source
- This is for models that contain numerator/denominator metrics for example percentage of people who have covid19
- Materialization should be a table.
- Include disaggregation of precomputed aggregates such as number of children under 5 who have malaria in a specific village for a specific month.
- Follow SQLFluff standards for indentation and formatting.
- Utilize comments to provide context and explanations within the SQL queries.
- If there's a complicated calculation comment directly in the code on what the calculation does and add your initials and the date. I.e. - This model does … ATM 2023-12-07
- In case of a shortcut/workaround implemented, comment this on the line in the model with the team member’s name (initials) and date of writing code.
- Clearly define your sources in the sources yml.
- In case you will be handing over the project to the client, describe the purpose of each model, column and the logic behind the transformations.
- This documentation can be embedded within the relevant YAML file.
- Create a yml file for each subfolder, documenting all the models in that subfolder.
Testing allows us to make sure that the SQL transformations we write produce a model that meets our assertions.
- Ensure you add the unique and not null test to the primary key.
- If there is no unnesting in the intermediate models, add the equal row count test between the staging and marts models.
- Add custom tests to check for data integrity such as ensuring no there are no duplicates generated in the models. Store these in the tests folder.
- Use store_failure_as to store failures for relevant tests, especially if they may suggest bugs upstream in the application (e.g. exposing issues in data collection). Create a reporting dashboards with these test results for other teams (e.g. PM, QA, Clients) to access and review data quality issues.
- There are different materialization strategies that can be configured to the models. The strategies include: table, view, ephemeral, incremental.
View
- Majorly all intermediate and staging models will be materialized as views.
Table
- Majorly all marts will be materialized as tables.
Incremental
-
You can use the incremental strategy when:
- You are working with a large dataset that needs to be updated (new records are being submitted or updated) and the transformation involves joining with other tables
- When the data transformation includes json extractions.
-
When you notice your runs starting to slow down, it is probably time to implement this strategy
-
Incremental models process new or updated data since the last run.
-
Hence, for the materialization to be implemented, the model needs to have a cursor_field. It's highly recommended that the cursor field be a datetime field in our case you can use the _airbyte_emmitted_at field.
-
The first run builds the entire table. On subsequent runs, only appends new records
-
Specify a unique key such as id or a hash of distinctive columns
-
Set the materialized configuration of the model is set to ‘incremental’. i.e.:
**{{ config(materialized='incremental') }}**
Ephemeral
- This has not yet been adopted within the team. Use this only if the project becomes too large and you don’t want to create too many objects in the database (e.g. PII cleaning step).
- Follow well established table naming conventions.
- Database objects (tables/views) should be stored with names that are lowercase, plural, and descriptive of what each row of the table represents (e.g. encounters , not EncounterResource)
- Use underscores as separators in the naming (e.g. sessions_weekly).
- Standardize column names, (e.g.??)
- Include an 'id' column in each table.
- Avoid excessively long table names.
- The DBT model name should reflect the table/object name (i.e. describe what each row in the table represents.) One model per database object.
- Do not do left joins on JSON data extracted in a CTE or subquery. For left joins ensure the extraction is stored as a table in the database then left join this extracted table.
- All stg_models should not unnest any JSON arrays, only do extractions at the staging level. Unnesting is to be done in separate models in the unnested subfolder within the intermediate folder.
- Long term the plan is to move to databases that can better handle JSON data.
- Create a DBT project repository on Github.
- Pull the project from Github into the client server.
- DBT build to run the models and tests.
- [SRE]: Implement Ansible, CRON jobs, and Monit for on-premise DBT job scheduling and monitoring.
- [SRE]: Explore alerting and notification options using existing tools and short-term manual fixes.
- Set up the DBT project on Github.
- Create a repository for a DBT project only if the database for that project is not yet configured to another DBT project. Rule of thumb is a canopy subscription is tied to one DBT project.
- Add team director and the main analytics team project members as Admins and the “Data Analysts” group as Maintainers.
- Enforce a branching strategy (i.e. no code changes are made directly in the main branch) , conduct code reviews through pull requests, and communicate changes clearly.
- Protect the main/master branch
- Always commit changes on branches and request reviews. Ideally, PR have approvals, but this is not always required. Reviews are more important for junior members of the team/project.
- Configure DBT Cloud settings, and set up job scheduling and Slack notifications for failed jobs. DBT cloud should pull form the ‘main’ branch for the project.
- If the project is on DBT cloud, no ‘development credentials’ should be set for the shared user.
- Effective data source design lays the foundation for successful DBT projects.
- By clearly defining sources, introducing staging models, adhering to coding styles, and providing thorough documentation, teams can streamline the transformation process and build maintainable data models.
- These practices contribute to the overall efficiency and success of DBT projects, ensuring they align with best practices and industry standards.