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: duplicating schema logic across two projects, or sharing it?

Exercise 1

Goal: write a macro that handles conditional logic, not just a one-line substitution.

Step 1 - Find the pattern

fct_ad_impressions.sql computes click_through_rate with a case expression that guards against dividing by zero when impressions_count 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 fct_ad_impressions.sql 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_ad_impressions.sql to call your new macro instead of the inline case expression, and confirm the model still builds and the column's values are unchanged.

Deliverable: the safe_divide macro and the updated fct_ad_impressions.sql.

Exercise 2

Goal: see what this project's generate_schema_name override currently does (nothing dbt wouldn't do on its own), then extend it so dev and prod schemas are named differently.

Step 1 - Give two domains their own schema

In dbt_project.yml, add a +schema config so that the streaming and ads 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. It has a single branch: when a model sets a custom schema the name becomes {{ target.schema }}_{{ custom_schema_name }}, otherwise it's just target.schema.

Now compare it to dbt's default generate_schema_name, documented in Custom schemas. They are identical - as it stands this override does nothing dbt wouldn't do on its own. Step 3 is where you give it a reason to exist.

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.