Skip to content

Modeling Refresh

A quick refresh of the staging -> intermediate -> marts layering pattern, using models from mediapulse_base as your walkthrough.

Exercise

Goal: trace one full chain of models end to end and be able to explain, in your own words, what each layer adds.

Step 1 - Check dim_shows

  • Step complete

Open the lineage in dim_shows.sql in mediapulse_base. For each model, write one sentence describing its job.

Hint: What to look for in dim_shows

Notice it's not just a pass-through join - it aggregates episode counts and dates per show. That aggregation is the kind of logic that belongs in a mart.

Step 2 - Check the materializations

  • Step complete

Look at mediapulse_base/dbt_project.yml. What is stg_podcasts__shows materialized as? What about dim_shows? Explain in one or two sentences why staging models are typically views while marts are tables.

Hint: Materializations

See the materializations docs for the trade-offs between view, table, incremental, and ephemeral.

Step 3 - Connect the intermediate model

  • Step complete

Look at the intermediate model int_shows_clean_string_columns, what is this model doing? Add a comment to the top of the file that explains the purpose of this model, and documents any known string patterns that are being cleaned.

Now go to the dim_shows model and make sure it references the intermediate model, so that these transformations are carried through.

Step 4 - Document the intermediate model

You may have already noticed, but the int_shows_clean_string_columns model is not currently documented outside of the comments in the file.

Add it to _int__models.yml yourself, matching the standard the other models in that file already follow:

  1. Follow the same structure, indentation, and style already used for other models in this YAML file (don't reformat or reorder existing entries).
  2. Write a description for the model that explains its purpose, based on the logic and comments already present in the model's SQL file - don't invent behavior that isn't in the code.
  3. Document each output column with a description, inferring its purpose from the SQL. Note when a column has been transformed/cleaned vs. passed through unchanged.
  4. Add column-level tests where they make sense given existing conventions in this file (e.g. not_null/unique on the primary key) - only use patterns already used elsewhere in the file, don't invent new testing conventions.
This is exactly the kind of thing dbt Wizard is good at

In the real world, this is boilerplate you'd hand to dbt Wizard with a precise, file-scoped prompt and then review the diff it proposes.

Deliverable: a short written explanation of the three-layer chain you traced, your answer to the completion-rate question, and the new mart file with its YAML documentation.

Extension

Step 1 - Refactor SQL logic for best practice

  • Step complete

dbt projects tend to accumulate logic in the wrong layer over time - a transformation that started in a staging model gets copy-pasted into three downstream models instead of being centralized, or business logic ends up living in a mart when it really belongs further upstream. This step is about spotting the drift and correcting for it.

Look at the podcasts source and everything built on top of it. Trace the logic across staging, intermediate, and mart models and ask:

  • Is the same transformation (a calculation, a filter, a case statement) repeated in more than one place instead of being defined once?
  • Is any model doing work that conceptually belongs in an earlier or later layer - e.g. cleaning logic sitting in a mart, or aggregation logic sitting in staging?

Review everything dependent of the podcasts source and refactor any code that belongs in a different model.

Hint: where to start

Not sure where to begin? Open every model downstream of the podcasts source yourself - staging, intermediate, and marts - and read them in DAG order. Look for logic that seems out of place for its layer, and for the same transformation defined more than once across different models. (In the real world, dbt Wizard can run this kind of review for you - for this training, do the read-through by hand.)

Hint: what to look out for

A few specific things worth checking as you review:

  • Does the staging model convert datetime columns from strings to actual timestamp types, or is that left for later (or never done at all)?
  • Are any sources being joined together at the staging level? Joins like this usually belong in an intermediate or mart model, not staging.
  • Is a seed being used anywhere in staging? This isn't necessarily a problem on its own, but it's worth discussing whether it's the right pattern.

Step 2 - Create an intermediate model for completion rates

  • Step complete

In the previous step you should have seen that the stg_podcasts__listens model joins two sources together. This is done so that the duration_seconds from the episode can be used to calculate the completion_rate can be calculated. You can see the SQL logic being applied in the fct_podcast_listens model.

Create an intermediate model that joins episodes and listens in the correct way (not in the staging layer) and then creates the completion_rate per listen.

Then create a mart that calculates the average duration listened and completion rate per episode. Then rank the avergage completion rate into four buckets:

  • no_listens
  • completed (episode is considered completed at over 90% completion)
  • partial
  • dropped_before_content (less than 10% is completed)
Hint: separating the join from the bucketing

Break this into two distinct pieces of logic rather than solving it all in one model.

The intermediate model's job is just to join episodes and listens and compute completion_rate per individual listen row (listen_duration_seconds / episode duration).

The bucketing into no_listens / completed / partial / dropped_before_content only makes sense once you've aggregated up to one row per episode - so that logic (a case when on the averaged completion_rate) belongs in the mart, applied after the avg(), not inside the intermediate model.

Done?

You've traced a real chain from staging to marts, refactored code to follow best practice and built your own new mart. That's the modeling instinct this whole workshop builds on.