dbt Snapshots, Workflow & More (YouTube Q&A)

This round of reader questions covers whether a dbt snapshot runs itself when source rows change (it doesn't, something has to run the snapshot command), what this.name does in Jinja, when a dbt project needs a new Git branch, and whether you can build type 1 and type 2 slowly changing dimensions with incremental models instead of snapshots. It also covers separating environments by schema versus database, automating an old SSIS package, whether the medallion architecture needs a separate gold layer, and how to safely change a live production data model. There is one request for a perfect end to end tutorial, which I pushed back on. The answers are below, one per question, in the order they came up.

Can you make an end to end project with Airbyte, Snowflake, dbt and Airflow?

This one came with three exclamation points, and it stands in for every request for the perfect step by step tutorial. In the real world you will not have a perfect set of instructions in front of you. You will probably never have that.

You already know the stack you want. The process of figuring out how those pieces fit together is what will serve you in your career.

If the goal is a portfolio project, copying what someone else did step by step isn't very helpful anyway. Put the pieces together yourself. It is the best way to learn.

Does a dbt snapshot run automatically when a new row is added to the source?

No. A dbt snapshot is a separate table that sits alongside a source and tracks how its rows change over time. Updating the source does nothing to the snapshot on its own.

Some dbt command has to run to trigger it. Two ways to do that:

• dbt build, which I believe includes snapshots in its run, so double check that in your version.
• dbt snapshot, the dedicated command, scheduled on a cadence through whatever job runner you use.

Either way, you have to schedule it. The snapshot only captures what the source looks like at the moment the command runs.

What does this.name do in dbt?

This is a confusing little nuance of Jinja. this is the representation of the current model: the one whose code is being compiled right now. this.name is its name, and this.database and this.schema give you the rest of the fully qualified name.

Why it is dynamic

Say a macro uses this.name and it is called from dim_customers. dbt knows the current context is dim_customers and fills that in. Call the same macro from a different model and the value changes to match.

You can think of it as the equivalent of running ref on the current model. dbt compiles it into the SQL the same way, so it is a neat way to self reference without creating a circular dependency.

Where it shows up

The dbt docs call out incremental models and hooks as the main uses. The classic incremental pattern reads from the table being built:

{{ config(materialized='incremental') }}

select *
from {{ ref('stg_orders') }}

{% if is_incremental() %}
where updated_at > (select max(updated_at) from {{ this }})
{% endif %}

When should you create a new branch in a dbt project?

dbt is a code based tool and the project lives in GitHub, so branching and versioning come with it. Here is how I typically recommend it:

• Main holds the production, live code. Nothing else.
• A new branch for every task. If the task is adding a staging model, create a branch named for that work, build the changes there, and test them in your dev environment.
• Pull request into main. Once it is merged and good, delete the branch, update main locally, and start over with a fresh branch for the next task.

Why not one shared dev branch

Some teams keep a single dev branch everyone works in. To me that moves away from the goal of version control, which is letting each developer isolate their own code for each task without overwriting each other.

The short answer: any time you have a new change, bundle it with related changes for the same task and give it its own branch. Iterating through that cycle often keeps you organized, tracks each change individually, and makes it easier to revert or see what happened over time.

Can you build SCD type 1 and type 2 with incremental models instead of snapshots?

Yes. First, the concept: a slowly changing dimension is a dimension whose values change over time, but slowly, and where you may want to track the change. The example I use is an employee based in Pennsylvania who moves to Illinois two years later.

Type 1 versus type 2

• Type 1 overwrites. The location becomes Illinois and Pennsylvania is gone.
• Type 2 keeps both. The Pennsylvania record stays, a new Illinois record is added, and extra columns track when each version was active, usually a from date, a to date and an active flag.

Why snapshots are a different use case

Everything above is possible without snapshots. To me a snapshot is about tracking changes at an individual source: one raw table, and you want its history. It isn't really intended to build a dimensional model with a bunch of underlying joins.

Type 1 and type 2 logic belongs in the dimension tables of your star schema. You can script that yourself in an incremental model, compare incoming rows to history, and manage the tracking columns on your own.

At least in the past, the dbt guidance was not to use snapshots for building new dimensional tables, and that is how I have always approached it. Think of a snapshot as an extension of a source, and the SCD logic as part of building the model.

Should environments be separated by schema or by database?

There's no hard and fast rule. I've been on teams that did it both ways. These days I gravitate toward schemas.

Why I lean toward schemas

In modern tools like Snowflake, schemas are easy to parse through and manage. In my memory, doing the same thing in SQL Server felt clunkier.

Databases and schemas are both just objects. If I can limit the number of objects I maintain, I will. Schemas let me separate environments without creating another database with its own permissions and responsibilities.

Security works the same way either way. When you set up roles and grants, you are just picking a different type of object to grant on.

When a separate database makes sense

If security is a major concern and you want environments physically separated, locked down differently and never commingled, a separate database may be preferred.

For the smaller teams I work with, that's rarely an issue. Proper role based access control locks a schema down just as well. The rest is preference and what you're used to.

How do you automate an SSIS package?

This came from a reader new to analytics, on an old video about building an ETL package in SSIS. Plenty of teams still run SSIS or Azure Data Factory.

If you are on SQL Server, the simplest route is a SQL Server Agent job on the same instance. Create a job, point it at the package, and schedule it. You could also kick it off from a Python script, but if everything already lives in that ecosystem, use the agent.

Are you AI generated?

A reader said, no offense, I look AI generated. None taken. That video was recorded before the current AI tools existed, so no, definitely not.

Does the medallion architecture need a separate gold layer?

A reader on my medallion architecture video argued that while you can name the layers anything, a separate gold layer makes data sharing and governance easier, with every earlier layer tightly locked down. I agree.

Call it gold, call it marts, it is the idea of a presentation layer at the end. It keeps things isolated so you can control access for stakeholders and reporting tools, and build custom datasets for them.

Everything before it, the heavier engineering, the modeling, the business relationships, stays separate. You're keeping those two concerns apart.

How do you change a live production data model in dbt?

The full question was how to make changes on a new branch, push them to Snowflake in a dev schema, model and test, and then properly override main. The answer is the standard workflow:

• Make your own branch.
• Test it in development.
• Open a pull request when it's ready and have it reviewed.
• Merge. GitHub, GitLab or whichever platform you use merges the code for you. You push a button.

Where to go deeper

I've covered this process in a few places: Modern Data Workflows, Explained walks through it, Data Automation (CI/CD) with a Real Life Example shows it running, and 3 Must-Know Trends for Engineers covers DataOps more conceptually.

Workflow is an area a lot of teams are missing. There is a learning curve, and it's worth pushing through.

Related reading

• Data Modeling, dbt Configs & More (Responding to Your Comments)
• Data Stacks, Hash Values & More (YouTube Q&A)
• How to Track History in a Data Warehouse
• The 3-Environment Design for Your Database

Final takeaway

The questions that come up most in these rounds are the same ones I get from small teams in the first few weeks of working together: where history should live, how branching should work, and how to change production without breaking it. The answers above are the ones I give in those conversations.