Skip to content

Jinja/Macros & Custom Schema Logic

A deeper look at one specific, real macro in this project: mediapulse_base's custom generate_schema_name override, and what it means that this override doesn't automatically apply to mediapulse_analytics too.

  • Exercise 1: Jinja and macros
  • Exercise 2: Custom schema
  • Extension: a reusable regex-cleaning macro

Exercise 1

Goal: write a macro that handles conditional logic.

Step 1 - Find the pattern

fct_podcasts_listens computes completion_rate with a case expression that guards against dividing by zero when total_length_seconds is 0. This is a "safe division" pattern - useful anywhere you divide two columns and can't guarantee the denominator is non-zero.

Step 2 - Write safe_divide

Write a macro, safe_divide(numerator, denominator, precision=4), that returns 0 when the denominator is zero and otherwise returns the rounded division. this matches what the model already does inline, but can be reused across models. Give precision a default value so most callers don't need to pass it.

Hint: default argument values in Jinja macros

See Jinja and macros for how macro arguments and default values are declared.

Step 3 - Apply it

Edit fct_podcasts_listens to call your new macro instead of the inline case expression, and confirm the model still builds and the column's values are unchanged.

Exercise 2

Goal: understand exactly why the generate_schema_name override exists, and check whether the newer analytics-side seeds actually need one of their own.

Step 1 - Give two domains their own schema

In dbt_project.yml, add a +schema config so that the streaming and podcasts model folders land in distinct custom schemas. Run the affected models and check where they actually landed - is it the schema name you gave, or something else?

Step 2 - Read the macro that's actually deciding this

mediapulse_base/macros/generate_schema_name.sql already exists in this project - it's the reason your Step 1 schema names came out the way they did. Open it and identify its two branches: one for is the custom_schema_name DOESN'T exists, and one where it DOES.

Hint: dbt's default behaviour

This macro is taken directly from the custom schemas documents, where dbt's default generate_schema_name lives. It has been added as a file in this project, meaning you can override this behaviour.

Step 3 - Add a dev/prod branch

Your team wants every model to land in one flat target schema during development (e.g. dbt_yourname), but to get the full target-schema_custom-schema naming (e.g. prod_streaming) in production. Extend the existing macro with a condition on the current target's name to implement this, rather than writing a new macro from scratch.

Hint: which Jinja variable tells you the target

The context available inside generate_schema_name includes the current target - see Custom schemas for what's accessible and how to branch on it.

Extension: looping over article survey scores

Goal: replace repeated per-column normalization and scoring logic with a single macro that loops over a list of columns.

Context: The columns in the news articles data get scored by visitors of the article responding to questions. All score columns begin with score_* and the number of responders is prefixed with num_responses_*. The final mart calculates the overall average score of the article by applied a product sum across the score columns and their corresponding number of responders column.

Step 1 - Find the pattern

stg_news__articles currently normalizes five survey score columns and five response-count columns with near-identical case statements, one per column:

case when trim(score_relevance) in ('', 'N/A', '-1') then NULL else cast(score_relevance as float) end as score_relevance,
case when trim(score_clarity) in ('', 'N/A', '-1') then NULL else cast(score_clarity as float) end as score_clarity,
...

case when trim(num_responses_relevance) in ('', 'N/A', '-1') then NULL else cast(num_responses_relevance as float) end as num_responses_relevance,
case when trim(num_responses_clarity) in ('', 'N/A', '-1') then NULL else cast(num_responses_clarity as float) end as num_responses_clarity,
...

Then, in the mart layer, each normalized score is rescaled onto a common 0-10 basis and weighted by its response count:

-- relevance: already 0-10, no rescale needed
coalesce(score_relevance, 0) * coalesce(num_responses_relevance, 0)
    as weighted_relevance,

-- clarity: already 0-10, no rescale needed
coalesce(score_clarity, 0) * coalesce(num_responses_clarity, 0)
    as weighted_clarity,

...

Ten normalization blocks and five weighting blocks, all doing the same shape of work with only the column name and (for weighting) a rescale factor changing. That repetition is what this extension asks you to collapse.

Step 2 - Write normalize_score_columns

Write a macro that takes a list of column name pairs and loops over them to generate the normalization case statements above - one pair per score/response combination, e.g. ('score_relevance', 'num_responses_relevance').

Hint: looping over pairs

You can loop over a list of 2-item tuples the same way clean_text loops over (pattern, replacement) pairs:

      {%- for score_col, response_col in score_pairs %}
          ...
      {%- endfor %}
Each iteration needs to emit two case statements (one for the score column, one for its response column) since both go through the identical sentinel-cleaning logic.

Step 3 - Write weighted_score

Write a second macro that takes a list of (score_col, response_col, rescale_factor) triples and loops over them to generate the weighted-score expressions from Step 1. The rescale_factor should be applied as a multiplier - so 1 for columns already on a 0-10 scale, 2 for score_bias (1-5 scale), and 0.1 for score_trust (0-100 scale), matching the rescaling already shown above.

Hint: keeping the metadata in one place

Define the list once, near the top of the model or in a var(), rather than passing it inline at the call site - that way the "which column needs which rescale factor" knowledge lives in exactly one place:

      {%- set score_config = [
          ('score_relevance', 'num_responses_relevance', 1),
          ('score_clarity', 'num_responses_clarity', 1),
          ('score_bias', 'num_responses_bias', 2),
          ('score_trust', 'num_responses_trust', 0.1),
          ('score_engagement', 'num_responses_engagement', 1),
      ] -%}

Step 4 - Apply both macros

Update stg_news__articles to call normalize_score_columns instead of the ten longhand case statements, and update the mart to call weighted_score instead of the five longhand weighting blocks. Confirm the compiled SQL matches the original longhand output, and that total_weighted_score per article is unchanged.

Hint: checking your work

Run dbt compile on both models before and after the change and diff the two outputs - if your macros are correct, the compiled SQL (and therefore the resulting data) should be identical, just generated differently.

Deliverable: normalize_score_columns and weighted_score macros, plus stg_news__articles and the scoring mart updated to call them instead of the longhand blocks above.