How to set up a dev to prod workflow on snowflake (and dbt)?

user_13123614 0
Viewed 695

We are currently implementing snowflake and dbt and want to split snowflake databases between dev and prod, so that we have a database to test on before releasing new data models. We are planning to use dbt to create all of our data models going forward. I have a couple questions on the logistics of the workflow:

  1. How do we keep dev and prod in sync? (Or should they be?) I know in snowflake theres a clone feature you can recreate metadata without copying data over. Should we clone our prod database to dev? On a daily basis? What about users that have materialized resources in dev -- they would lose that data.

  2. Should we make it so that deployment to prod was part of the CICD process, and only a fully merged pull request (tested on snowflake dev) can be deployed to the snowflake prod? Would that present too much of a bottle neck?

Curious to understand how people have architected their workflows maintaining both a dev and prod snowflake environment.

dbt snowflake-cloud-data-platform workflow
2 Answers

A common implementation is to have user-specific dev schemas (e.g., dbt_lfolsom) that are written to and overwritten whenever a user executes any kind of dbt run; and then a single prod schema (e.g., analytics) that's written to and overwritten when jobs are executed "in production."

Running dbt "in development" means a dbt command is executed by an individual user (using dbt Cloud or dbt CLI) so that data is written to a dedicated schema that is used specifically for development.

Running dbt "in production" means running dbt commands that are configured as jobs in dbt Cloud (or using another orchestration tool) write data to a specific "prod" target schema.

Check out this article on running dbt in production.

With dbt Cloud, you can also write to PR-specific schemas that are created automatically when you create or update a GitHub PR, which may be what you'd like to use for CI/CD. Check out this article on options.

You can (should) definitely configure prod jobs to run only on fully merged code.

If for some reason you really need prod and dev to be separate databases instead of separate schemas, I think you would create separate dbt projects that use code from the same git remote repo but are configured (in dbt Cloud or using your profiles.yml) to write to different Snowflake databases. But I think that's an unconventional approach that would require more work.

user_50376350

To change the database where you run your code go to top right corner -> your profile -> in credentials on the left choose the project, fill in the dev section (here you select only schema) and above it you can overwrite the default for this project.

This default will be changed only for you and will be indicated by a strikethrough over the default one and your selection next to it

user_78569440
Related