Managing a Dynamic dbt Project
Guardrails, Guidelines, Gadgets
At Whatnot, our dbt project has grown rapidly in the last year. We have 4x-ed the number of models in the project to just over 1,200 and currently support 70 developers—30 of whom contribute to the project at least once a week. This growth is fantastic news, however, it does present some unique challenges when it comes to maintaining the project and empowering our developers to build the data products they need. To combat these challenges, Whatnot’s data team has implemented solutions across the dbt project which can be broken into 3 “G”s:
- Guardrails—to maintain stability in the project
- Guidelines—to encourage best practices
- Gadgets—designed to automate processes and empower our developers
In this post, we will share the patterns we have in place for each “G”. More details including example code and a talk on the subject can be found at the bottom of the post.
Project Structure, Orchestration & Challenges
Our primary dbt project is divided into five schemas: applications, core, products, projects, and visualizations. The design decisions behind our schemas were discussed in a previous blog post.
The primary dbt project is supported by auto-generated staging models over internal source data. In the case of our event logs, dbt models exist in a second dbt project which is referenced as a source in the primary project. Models over our change data capture logs are auto-generated by the CDC pipeline logic directly into a sub-folder of the primary repository. Orchestrating a project of this size is an ongoing challenge. To escape the single project build, the job was initially split by schema. However, it soon became clear that adding builds at different cadences was critical to support our users. In addition, errors in non-critical models could disrupt the build and lead to alert fatigue. To this end, our orchestration plan is now split by priority and cadence. Critical (P0) models can be built at minutely, hourly, or daily cadences and have a bug response SLA of hours. Important (P1) models can be built hourly or daily and have a bug response SLA of days. Any other model is deemed P2 and may be run manually at any time by our developers.
With the rapid growth of the primary dbt project, we ran into a myriad of challenges. The exponential growth of our datasets in combination with sub-optimal SQL led to queries that ran into the tens of hours. The order in which our models were constructed with many data products pre-dating the “Core” Kimball-inspired data mart led to some non-linear upstream dependencies which in turn led to cascading model failures. While the Whatnot engineering team excels at releasing new features at high velocity, this led to changes in the underlying schema of our source data and definitions of our key metrics. Finally, with 70 developers and an abundance of data to analyze, we inevitably generated some project bloat with out-of-date and/or unused models floating around in the repo.
Guardrails
Continuous Integration (CI)
When it comes to guardrails, perhaps the most powerful tool in our dbt project is Continuous Integration (CI).
Our CI tests can be broadly placed into three categories:
- Will the project run after the change?
- Does the change meet project requirements?
- And is the new code legible?
dbt’s Slim CI functionality is fantastic for verifying project integrity. More details can be found in the original Slim CI dbt discourse post and the dbt team continues to make improvements to the process with the introduction of both CI run concurrency and stale build cancellation in dbt Cloud. The only additions we make to this process are designed to prevent long-running queries. To this end, we use the --fail-fast flag, time out all CI runs after 1 hour and encourage developers to limit the amount of data processed in development using a macro, minimize_dev_table_size(). When it comes to project requirements, we have found less is more. In our case, we rely upon dbt-checkpoint to check that models meet naming conventions, do not contain hard-coded references, and models tagged P0 have a primary key that is tested. We have also built some custom tests as macros that verify all models are tagged with a team maintainer and verify model lineage is compatible with our build structure (P0 models do not rely upon P1 models etc.). Finally, we use a linter, SQLFluff, to ensure new code is legible. Opinions on SQL formatting are varied and frequently shared, but ultimately, if the SQL runs, the code is fine. Thus, we keep linting to a minimum simply ensuring lines do not get too long, aliasing is performed when needed, and SQL meets compatibility requirements for both Snowflake and dbt-checkpoint.
With each iteration of our CI process, we are reminded that the most critical part of CI from a developer’s perspective is its speed. Getting feedback to the developer in seconds or minutes rather than hours is the only way to support moving fast and prevent test fatigue. Similarly, CI should provide feedback only based on the changes made in the specific PR and where possible not require developers to make changes to code that they did not write.
Model Run Time
While running a model in CI gives a good indication of how long it will take in production, in some cases due to dataset scaling or limits placed on development queries, production queries can take longer than expected to run. In the context of a daily build, we want to ensure models do not block dependencies downstream. To prevent runaway queries, we implemented query cancellation policies across production dbt Snowflake warehouses. We set the time limit to be the length of the longest known running model in the project plus one hour. If a query is canceled, a metric is sent to datadog and the query details are inserted into a query cancellation table which is monitored in a Hex Dashboard. While in some cases this has resulted in job failure, typically large models are caught before this timeout and we have found it to be invaluable in alerting the data team to exploding joins or models that must be incrementalized.
Similarly, given the size of our project and orchestration patterns, we aim to prevent users from accidentally running the entire project. dbt user brunoszdl came up with a good solution to this problem, developing a macro that prevents a command from being run without a --select statement. We use a modified version of this macro to prevent users from executing all models or all tests.
Model History
From time to time, we receive questions from business stakeholders like “What did x table look like on x date?” Such information is particularly critical for our accounting teams who are often required to provide point-in-time analyses. While the repo is version-controlled and our raw data is not modified, due to the volume of pull requests in our project recreating the historic state of models, particularly those further downstream in the DAG, can be tricky. For this reason, we implemented 90-day time travel on a number of our critical tables. This works well for our incremental models, but for those materialized as tables, the CREATE OR REPLACE statement associated with a daily build is incompatible with maintaining a time travel history. To this end, we found the snowflake_timetravel_table dbt package by edx and implemented the associated macro in our repo.
As storage is cheap, dbt project bloat can often be ignored, however, persisting models that are no longer in use or out-of-date can lead to confusion for data users. We have written about removing models that no longer exist in the dbt project, however, this process does not identify models that exist in the project but are not being queried in the database. Inspired by select.dev’s blog post, we now use the Snowflake access history to identify these models. Unused models and their maintainers are sent in a monthly digest to the data Slack channel to be reviewed for deprecation.
Guidelines
Documentation
Project guidelines are often synonymous with documentation and indeed documentation is critical to keeping our developers up to date with project processes and best practices. We keep as much information as possible inside the dbt project repo itself. Developer documentation is located in the repository README and user documentation is in yaml files, which are then shared via the dbt docs. Keeping documentation in a single location as close to the code as possible ensures updating the documentation is top of mind and allows us to enforce requirements as part of the code review process.
Macros
In addition to documentation, we also provide reusable frameworks that implement best practices. dbt provides the macro functionality to support this. Our macros can be bucketed into three categories: SQL filters, SQL functions, and helper functions. SQL filters are most often where clauses that are used extensively throughout specific marts. A good example of this is our incremental_partition_filter() macro. This extends the basic where event_time > (select max(event_time) from {{ this }}) logic to support backfilling incremental models and the addition of a “buffer” interval to account for late delivery. SQL functions are reusable code snippets that operate on a specific field. For example, our try_decode_ids() macro handles columns in which some values are base64 encoded and some are not. Finally, helper functions are designed to perform an operation either in the database itself or using metadata from dbt. Examples of these macros will be discussed in the gadgets section below.
Gadgets
Code Generation
Whatnot’s dbt gadgets are designed to improve the developer and data team experience either by eliminating repetitive manual tasks from a workflow or empowering the developer to perform advanced operations in the warehouse. Due to the awesome rate at which our engineering team releases new features, our data schema changes frequently. Historically, these schema changes required manual updates to the dbt code to add new staging models, pull new columns into views, or update data types. This repetitive work was a great candidate for automation so during a migration of our internal data pipelines Whatnot’s data team took the opportunity to implement dbt code generation in both our event and CDC logging processes. When a new event or table is added to a pipeline either by a software engineer or a member of the data team, the associated dbt staging model is generated or updated.
Table Schema Changes
While schema changes can be addressed by code generation in the staging layer, it is not always quite so simple downstream. New columns need to be added, missing data requires backfilling, data types need to be updated, and fields may even need to be redefined. dbt provides the --full-refresh flag to address these needs for incremental models, however, as datasets expand, full-refreshes can become prohibitively slow and for tables with time travel enabled a create/ replace statement eliminates the table history.
Typically, the most efficient way to do this is by using DML/ DDL commands directly in the warehouse. However, there is little visibility into what is run here, it’s hard to ensure repeatable patterns are followed, and operations run outside of typically orchestration pathways are not shown in a job history and do not generate associated metadata. For these reasons, we wrote macros that can be run using the dbt run-operation command in any environment by any developer to perform the most commonly required operations. These include delete_from_table(), create_new_table_schema() and clone_new_table_schema(). delete_from_table() allows developers to run a DELETE FROM statement with a user-defined where clause. create_new_table_schema() creates a new table in a sandbox schema that is a clone of the target table with any new columns added. If a specific column needs modification, for example, the data type needs changing, custom SQL may also be passed to the macro to create a new table based on production data. This table can then be reviewed and validated by the developer within the sandbox schema.
Once the developer is satisfied with the new schema, the table can be cloned into the staging or production environment using clone_new_table_schema(). First, the macro checks the DATA_RETENTION_TIME_IN_DAYS for the object. If it is <= 1 day (the default), the sandbox table is cloned directly into the chosen environment. If it is > 1 day, the production table is truncated, the table schemas are compared, columns are dropped and added as needed, then the records from the sandbox table are inserted.
While the team was initially wary of providing these levers in a production environment, there really is limited downside to using these operations when needed. As all our raw data sits outside the dbt schema, operations cannot modify the underlying data. Tables or schemas can be excluded from the process, as needed, using logic embedded in the macro. Finally, if an operation goes wrong, the—full-refresh flag is still there as a fallback option.
Unloading Data
A good proportion of our data models are built to support use cases outside of Snowflake. For example, our Machine Learning team builds daily feature sets that are used to train models in Sagemaker. Typically, these downstream processes require data to be unloaded from the warehouse into another data storage system such as S3. Unloading data had become a frequent request to the data platform so the team decided to implement an unload materialization in dbt. This runs a COPY INTO command in Snowflake allowing the user to specify file format, compression, and file size. Developers can set up their own unloads, simply by making a pull request to the dbt repo. This process must be carefully monitored. Unloading data can be slow and computationally expensive. As such, we have monitors in place to alert when new models are added to the workflow, ensure the file size is within limits, and the total number of unloads is reasonable. This macro was so popular that version 2 is in the works allowing users further control over how and where their data is unloaded.
What’s next?
As our developers can tell you, the Whatnot dbt project is far from perfect and we continue to work on improvements across the repository. As Whatnot’s datasets continue to grow and demand for fresher data increases, simplifying the path to incrementalization for more complex models is becoming critical. We continue to develop documentation and processes around this topic. We are also exploring the use of Snowflake’s dynamic table functionality across the repo.
Another high priority is implementing versioning on critical models to better monitor and communicate model changes in the project. We also hope to further improve data governance by implementing dynamic masking using dbt with Snowflake. In the longer term, in good alignment with the roadmap of dbt itself, our biggest challenge remains overcoming complexity. The concepts of dbt mesh and the semantic layer will be top of mind for Whatnot’s data team as we continue to scale our dbt project. Throughout this work remaining an in-demand, self-service project continues to be our North Star. For that reason, our highest priority is and will continue to be improving the experience of our developers and downstream data users.
This blog post is based around a talk given at dbt Coalesce 2023: “The More, the Merrier: Managing a dynamic, expanding, self-service dbt project”.
Example code associated with the processes described below is found here.
If you’re interested in data engineering projects like this, check out our Careers Page to learn more about joining our team.