Data Architecture w/ dbt (Putting It All Together)

The single biggest thing you can do for a dbt project's long term health is to make it mirror the database it deploys into. If your warehouse has a raw database for landed source data and an analytics database with staging, warehouse and marts schemas, your models/ directory should have folders with exactly those names, configured in dbt_project.yml to land in exactly those schemas. The other half is knowing the difference between a source and a model: a source is a pointer to a real raw table with no transformation, and everything built after that is a model. When teams drift from either rule, the project slowly stops matching the architecture and gets confusing to work in.

The high level design

Creating a data architecture involves a lot of moving parts, and that is true even for a one person team. Things drift out of sync, and I see it most often in dbt based projects.

Start from the end to end picture I call the simple stack. Read it left to right.

• Sources. The systems your data originates in.
• Landing zone. Everything lands in one centralized place, usually a database called raw with one schema per source system.
• Transformation. dbt reads from raw and builds a modeling pipeline in a second database.
• Modeling layers. A staging schema, a warehouse schema holding facts and dimensions, and marts for external facing reports.

If medallion architecture is your vocabulary, this is bronze, silver and gold. The label matters less than being consistent with whichever one you pick.

What it looks like in the database

Here is the same design as real objects. In my Snowflake instance there are two databases, prefixed because that server has a lot of other things in it.

The raw database

KDS_RAW is the landing zone. Inside it, one schema per source system, for example a schema for YouTube analytics data.

Nothing in here has been transformed. It is exactly what the ingestion tool dropped off.

The analytics database

KDS_ANALYTICS holds everything dbt builds. Its schemas are:

• staging for the first cleanup pass over raw data
• warehouse for facts and dimensions
• marts for reporting ready models
• dev as a developer workspace, with a CI schema alongside it in a full setup
• snapshots as a separate place for snapshot output

Two databases, a handful of schemas, and every one of them traces back to a box on the architecture diagram.

Aligning the dbt project

Now take it one level deeper into the project itself. Open the models/ directory.

The best thing you can do for long term success with dbt is make that directory match the database. Staging in the database, a staging directory under models. Same for warehouse. Same for marts.

This sounds obvious written down. Teams still miss it, because they start building the project one way and then deploy it another way.

Why the mismatch hurts

It gets confusing over time and it does not scale. Someone looking at a table in the warehouse cannot immediately guess where its code lives, and someone looking at the code cannot guess where it lands.

Keeping them in sync removes that whole class of question.

Configure each layer once

The payoff shows up in dbt_project.yml. Because directories and schemas line up, you configure a whole layer in one place.

models:
  my_project:
    staging:
      +schema: staging
      +materialized: view
    warehouse:
      +schema: warehouse
      +materialized: table
    marts:
      +schema: marts
      +materialized: table

Everything in the staging directory goes to the staging schema. Everything in warehouse goes to warehouse. Set it one time and stop thinking about it.

The dev schema works differently, since that workflow puts everything into a single schema by design.

The exception for development

That is worth saying plainly, because it is the one place the mirror does not hold. While developing, every model you build lands in your personal dev schema regardless of which directory it came from.

That is intentional. It keeps your in progress work in one isolated place, and the layer separation reappears the moment the code deploys to production.

Sources versus models

The next question is what happens to the raw data. That comes down to two dbt concepts that are easy to blur together.

What a source is

A source represents raw data in its purest form, straight from the source system. No transformations, no modeling, no customization.

It is a pointer. You declare it in a sources YAML file and reference it with source(), and dbt compiles that into the real table name.

sources:
  - name: youtube_analytics
    database: KDS_RAW
    schema: youtube_analytics
    tables:
      - name: video_stats
      - name: channel_daily

What a model is

A model is everything after that. Any object built from SQL you wrote, which dbt deploys into the analytics database.

Models use ref() to point at each other, and that is what gives dbt the lineage. It knows what has to build first and how to compile the same code into different environments.

The anti-pattern to watch for

Watch for sources popping up in the middle of the pipeline. A team declares real sources, builds a model, and then writes another source YAML pointing at that model so something downstream can read it.

That is a workaround, and it breaks the lineage dbt would otherwise give you for free. There are edge cases, but in my experience it is usually a sign something upstream is off.

The clean rule: sources are your true raw tables, and everything after that is a model.

Putting it all together

Three layers of the same idea, stacked:

• Architecture. Raw data on one side, transformed analytics data on the other.
• dbt concepts. Sources for the raw side, models for the analytics side.
• Project structure. Directories named staging, warehouse and marts, matching the schemas they deploy into.

One more organizing habit: inside staging, create subdirectories per source system, the same way raw has a schema per source. It keeps the project navigable once you have more than a couple of sources.

Translate all of that back to the database and everything connects. Raw holds the sources, analytics holds the models, and the schemas match the directories that produced them.

None of this is complicated. It is just easy to let slide once you get pulled onto other priorities.

Related reading

• A "Simple Stack" Data Architecture, Explained
• How to Create a 3 Layer Data Model Pipeline
• The 3-Environment Design for Your Database
• dbt Project Naming Conventions

Final takeaway

When I audit a dbt project that has become hard to work in, the cause is usually drift between the project structure and the database it deploys into, not anything clever gone wrong. Keep the directories, the schemas and the architecture diagram saying the same thing, and the project stays legible no matter who joins next.