#040: 3 Ways to Deploy Data Projects

Deploying a data project is really a question of where development, testing and production live in your warehouse. Over the years I've seen three layouts cover almost every team: two databases with raw data separated from everything else, a single database with schema prefixes telling the layers apart, and three databases with raw, development and production fully isolated. The right one depends on your platform as much as preference, because Postgres can't easily query across databases, BigQuery bills by project, and Snowflake makes extra databases cheap. All three work with dbt and just as well with other transformation tools. Pick the one that matches your warehouse, name things consistently, and the deployment strategy mostly takes care of itself.

Why deployment layout is its own decision

On a data team it's one thing to build the code. It's another to plan how it gets deployed: where development happens, where testing happens, and what end users actually see.

I've worked on a handful of teams and the strategy was different on each one. Sometimes that came down to the personal preference of the team lead. Other times it was the technology, because the strategy that works on Postgres is probably not the one you'd use on Snowflake.

The three layouts below are the most common ones I've seen. The examples use a dbt project, dbt being the transformation tool I use most, but the concepts work regardless of tool selection.

Option 1: Two databases, source data separated

The first approach uses two databases. One holds raw data only: every source system loads into its own schema there, and nothing else touches it.

The second database holds everything dbt produces, both development and production.

raw
  salesforce
  stripe
  hubspot

analytics
  dbt_mkahan      development, one schema per developer
  dbt_jsmith
  staging         production, built by the scheduled job
  marts           production, what end users query

How the schemas split

• Development schemas: one per developer, named dbt_ plus the person's name. Each person builds and breaks things in their own schema without affecting anyone else.
• Production schemas: custom named schemas for the final models, like marts. These are the tables visible to end users and BI tools.

In dbt this split is just two targets in profiles.yml. Your dev target points at your personal schema. The production target, used by the scheduled job, points at the custom schemas.

The separation of raw from transformed is clean. The tradeoff is that development and production share a database, so naming carries the weight of keeping them apart.

Option 2: One database, all in one

The second approach keeps everything in a single database. This is the natural fit for BigQuery, and especially for Postgres.

Why one database

• Postgres: querying across databases isn't really supported, at least not easily, so one database is the practical choice.
• BigQuery: you can query across projects if you want, but teams often keep everything in one project for billing purposes.

Prefixes do the separating

Raw data loads as schemas in the same database, each prefixed with raw_. Development schemas are prefixed dbt_. Production models go under an analytics_ prefix: analytics_staging, analytics_marts, or whatever you call your layers.

analytics_marts
analytics_staging
dbt_jsmith
dbt_mkahan
raw_hubspot
raw_salesforce
raw_stripe

The naming does real work here. Most tools list schemas alphabetically, so raw data clusters at the bottom, development sits in the middle, and production is at the top. I call the production prefix analytics partly so that it sorts first.

You move up the ladder visually and know exactly where everything is. It's one way to do a single database, not the only way, but it looks clean and keeps the concepts separated.

Option 3: Three databases, isolated development

The third approach was the first one I ever worked on, back in the day. It's similar to the two database layout, except development gets pulled out into its own database.

raw
  salesforce
  stripe
  hubspot

analytics_dev
  dbt_mkahan
  dbt_jsmith
  qa

analytics
  staging
  marts

What goes where

• raw: source data, completely separate.
• analytics_dev: every development schema plus the QA work that sits between development and release.
• analytics: production models only.

The only thing anyone can see in the production database is the finished work. That's a clear separation that doesn't rely on people reading a prefix correctly.

Where it fits

This is easiest on a platform like Snowflake. Databases there behave like traditional databases, there's no billing problem with having several, and querying across them is normal.

On Postgres this layout fights the platform, which is exactly why option 2 exists.

Choosing between them

How you do this is up to you, the tooling you use, and how your team thinks about it. A rough guide from what I've seen:

• Postgres: option 2. One database, prefixes everywhere.
• BigQuery: option 2 inside one project is simplest. Options 1 and 3 work as separate projects if billing isn't a concern.
• Snowflake: option 3 for the cleanest isolation, or option 1 if you want fewer objects to manage.

The same project, three layouts

None of this changes your models. The layout lives in the database and schema values of each target, so moving between layouts is a config change, not a rewrite.

my_project:
  target: dev
  outputs:
    dev:
      type: snowflake
      database: analytics_dev
      schema: dbt_mkahan
    prod:
      type: snowflake
      database: analytics
      schema: marts

That example is the three database layout. For the single database version, set both targets to the same database and let the schema prefixes do the work.

Permissions follow the layout

Whichever option you choose, end users and BI tools get read access to production only. In the three database layout that's one grant on the analytics database. In the single database layout it's a grant per analytics_ schema, which is one more reason to keep the prefix consistent.

The pieces are the same whichever you pick. Raw data kept apart from transformed data, one schema per developer, and production models somewhere end users can find them without seeing the work in progress.

Each of these is a starting point. Tweak it to fit your team and the tools you've chosen.

Related reading

• The 3-Environment Design for Your Database (DEV vs CI vs PROD)
• dbt Environments vs Targets: What's the Difference?
• Data Automation (CI/CD) with a Real Life Example
• Why Data Teams Need Version Control

Final takeaway

This is the first thing I look at in a client's warehouse: where raw data lives, where development happens, and what end users can see. Pick one of these three layouts on purpose, and the rest of the deployment story gets a lot simpler.