Data Modeling, dbt Configs & More (Responding to Your Comments)

Every so often the most useful thing I can do is answer the questions people actually leave on my videos rather than pick a new topic. This round covers whether an ingestion tool like Airbyte should be doing data cleaning and version control, how a YAML file gets run, whether ephemeral models in dbt only make sense when a CTE is reused, and what invocation_id is. It also covers how closely my star schema tutorial follows the four step Kimball method and the bus matrix, why surrogate keys are worth the overhead, and whether a years old getting started with dbt video is still relevant. There are two career questions in the mix, on working as an employee at a consulting company and on learning streaming architectures. The answers are below, one per question, in the order they came up.

Do you use Airbyte for data cleaning, and do you version control the scripts inside it?

No on both counts, at least for how I work. These all fall into the same bucket for me:

• Airbyte
• Fivetran
• Stitch
• dlt
• A plain Python script

They're data ingestion tools whose job is to get data from a source into the warehouse. I use them for loading and that's it.

Cleaning happens in the transformation layer. For me that's dbt, though stored procedures can play the same role. That's also where version control lives, because the dbt project is a git repository and every change to a transformation is tracked there.

You can do light transformations inside an ingestion tool, and some teams do. I'd rather keep the two jobs separate so there's one place to look when logic changes.

How do you run or execute a YAML file?

Most of the time, you don't. YAML is a format for storing values, similar to JSON, and in data engineering it's used almost entirely for configuration. You write the file, and some other tool reads it and does something with what's inside.

Where it shows up in dbt

A dbt project is a good example. The sources.yml file declares which raw tables exist and can attach tests to them, but I never execute that file myself.

dbt reads it when it compiles the project, passes the values into its own scripts, and runs the tests it finds.

version: 2

sources:
  - name: raw_sales
    schema: raw
    tables:
      - name: orders
        columns:
          - name: order_id
            tests:
              - unique
              - not_null

Same idea in GitHub Actions

The other place I see YAML constantly with clients is GitHub Actions, where a workflow file defines the steps of an automated job and GitHub reads it to run the job.

Same idea. You're almost always handing a configuration file to a tool that knows what to do with it.

Are ephemeral models only worth it when a CTE needs to be reused in more than one model?

This question mixes two concepts that get talked about together so often that they blur:

• Ephemeral is a dbt materialization. Set materialized='ephemeral' and dbt doesn't deploy the model as a view or table; it compiles the code and wraps it in a CTE inside whatever model references it.
• Intermediate is a modeling concept. You take one long query with a lot of custom logic, break a piece of that logic out into its own file, and pull it back in with ref.

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

with orders as (

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

),

pivoted as (

    select
        order_id,
        sum(case when line_type = 'product' then amount else 0 end) as product_amount,
        sum(case when line_type = 'shipping' then amount else 0 end) as shipping_amount
    from orders
    group by 1

)

select * from pivoted

Intermediates are usually deployed as ephemeral because you don't need the extra database objects, which is why the two ideas travel together.

Reuse is the wrong test

The part I'd push back on is the reuse. In the pure sense, an intermediate model feeds one downstream model. You're isolating a complex operation, such as repivoting to a different grain, so the final model stays readable.

If you want to reuse that logic across several models, it's probably a regular model or a macro, not an intermediate.

Can you put the CTE directly in the non-ephemeral model instead? Sure. Nothing forces you to break it out. The dbt documentation on intermediate models explains the distinction well, and I'd recommend reading it.

Can you be an employee at a consulting company?

Absolutely, and I was one. I worked on the internal data team at a consulting firm while other engineers at the same company were consultants who went out to clients but were still employees.

It's a good way to get started and a good way to learn how consulting works, because it's a different world from working on an internal team at a typical company.

Where does invocation_id come from in dbt?

invocation_id is a dbt variable: a unique identifier generated every time a dbt command runs. It's useful for outputting metadata and for tracking individual runs.

I use it a lot for auditing, because once it's stamped on a row you can trace that record back to the exact run that created it.

select
    order_id,
    order_date,
    amount,
    '{{ invocation_id }}' as dbt_invocation_id,
    current_timestamp()   as dbt_loaded_at
from {{ ref('stg_orders') }}

Does the star schema tutorial follow the four step Kimball method?

A reader pointed out that my star schema tutorial doesn't follow the textbook order and doesn't mention the bus matrix. Fair comment.

What I share is what has worked for me and for clients, which isn't always perfectly textbook, but going step by step we mostly agree.

• Business process. Yes. Every engagement starts with what the business is actually trying to get insight on and what it will do with that information. Don't model for its own sake.
• Declare the grain. Also yes. Wrong granularity is one of the biggest problems I see teams run into. Instead of modeling invoices, model transactions, where a transaction might be an invoice, a payment, a refund or a charge, because the lower grain lets you do more later.
• Dimensions, then facts. For loading, definitely. In a daily refresh you load dimensions first so the keys exist, then load the facts that carry those keys. For building, on a small team the dimensions and facts usually take shape at the same time. On a large migration you may need to follow the order strictly.
• Bus matrix. A real part of the method the tutorial didn't cover. It's the planning grid of business processes against shared dimensions, with something like sales as the fact and employees, locations and products as the dimensions.

Is the getting started with dbt video still relevant?

The concepts are. These still work the same way:

• Initializing a project
• The default folder structure
• Setting up profiles.yml locally

dbt has changed a number of commands and the interface since then, so if you're trying to follow it click for click some steps won't match.

A video can't be edited once it's posted, so the plan is to record a newer version that lines up with the current steps.

Why are surrogate keys worth the overhead?

A reader on my surrogate key video said the consistency argument was one he hadn't heard elsewhere, and that it might make the other trade-offs moot. That's exactly the point I was trying to make.

One rule for everyone. On a team of engineers and analysts, being able to say "always join on the surrogate key" is a single rule everyone follows.

No more remembering that these tables join on an ID and those tables join on a composite of three columns. The time you'd spend figuring that out case by case isn't worth it.

What it does for the marts layer

With surrogate keys in the warehouse, a mart just joins keys. Each key is unique per record, and with a slowly changing dimension each version of a record gets its own key, so the fact table is stamped with the correct version at load time.

The date between logic that resolves which version applies happens once, in the warehouse layer, before the mart exists. By the time you get to marts you never think about it again.

Rebuilds and backfills

The keys should stay the same. They're hashed from the natural key and other inputs, so the same input produces the same output and a rebuild regenerates identical keys.

There may be an edge case I'm not thinking of, but in general it hasn't been a problem.

Will you make a hands-on Lambda architecture tutorial?

Lambda architecture runs a batch pipeline and a streaming pipeline side by side. I'm not going to claim to be an expert on streaming.

The clients I work with are smaller teams that rarely need it, so my focus is batch loading and building the warehouse on top of it.

Build the thing you already planned

The reader already had a clear plan: download sample data, build a pipeline, set up a warehouse and run it on a free tier.

My honest advice: go do it. Don't wait for a tutorial.

Getting stuck, looking things up and figuring it out teaches far more than following along step by step, and it's how I learned most of what I know.

Related reading

• Data Stacks, Hash Values & More (YouTube Q&A)
• dbt Snapshots, Workflow & More (YouTube Q&A)
• What Are Intermediate Models in dbt?
• The Key To Building A Reliable Data Model

Final takeaway

Most of these questions are versions of ones I get from clients in the first few weeks of an engagement: where cleaning should live, what a config file is actually doing, and which parts of the Kimball method matter in practice on a small team. The answers above are the ones I give in those conversations.