Jinja and Macros Flashcards
(27 cards)
What is Jinja in the context of dbt?
Jinja is a templating language used within dbt to add logic and reusability to SQL. It allows for control structures, macros, and dynamic expressions.
How do Jinja expressions, statements, and comments differ?
Expressions {{ ... }} output strings or values, statements {% ... %} control flow without output, and comments {# ... #} are ignored in compiled output.
Where can Jinja be used in a dbt project?
In models, analyses, tests, hooks, and macros — essentially anywhere SQL is used.
How can you check what SQL Jinja compiles to in dbt?
Use the ‘Compile’ button in dbt Cloud, or run dbt compile in dbt Core and view the compiled SQL in the target/compiled/ directory.
What does the ref() function represent in dbt and why is it significant?
It’s a Jinja macro that resolves to the name of a model or seed, enabling dependency tracking and environment-agnostic SQL.
What is a macro in dbt?
A macro is a reusable piece of Jinja code (like a function) defined in .sql files, typically in the macros/ directory.
Give an example use-case for a macro in dbt.
A macro like cents_to_dollars(column_name) converts cents to dollars and can be reused across multiple models.
How do you define a macro in dbt?
Use {% macro macro_name(args) %} and {% endmacro %} inside a .sql file, usually located in the macros/ directory.
What does the following macro do?\n{% macro cents_to_dollars(column_name, scale=2) %} ({{ column_name }} / 100)::numeric(16, {{ scale }}) {% endmacro %}
It converts a value in cents to dollars, with a customizable numeric scale for precision.
What is whitespace control in Jinja and why is it useful?
Whitespace control (using - in delimiters like {%-) helps produce cleaner, more readable compiled SQL by removing unnecessary whitespace.
What is the benefit of using environment variables in Jinja within dbt?
They allow configuration changes (e.g., credentials or target environments) without modifying source code.
What does the following Jinja statement do in a model?\n{% for payment_method in payment_methods %}
It loops over each item in payment_methods to dynamically generate SQL code.
How can Jinja be used to dynamically generate pivoted columns in SQL?
By looping through a list of values and writing conditional aggregations with different column aliases.
Can Jinja be used to modify the way a dbt project builds based on the environment?
Yes, by referencing target.name or other context variables, Jinja can modify SQL or logic depending on the environment.
How do you use a macro from an installed dbt package?
Prefix the macro with the package name, e.g., {{ dbt_utils.dimensions(5) }}.
How can you qualify a macro within your own dbt project?
Prefix the macro with your project name if needed, mostly useful for package authors.
What are the benefits of using Jinja macros for repeated logic in models?
They reduce code duplication, improve consistency, and simplify maintenance by centralizing logic.
How do you use a macro from an external dbt package like dbt-utils?
You call the macro using the package name as a prefix, e.g., {{ dbt_utils.dimensions(5) }}.
What is the purpose of qualifying a macro with a package name?
It helps avoid naming collisions and clarifies which package the macro originates from.
When might you qualify a macro with your own project name?
Mostly useful for package authors or advanced use cases to disambiguate macro origins.
What is ‘dbtonic’ Jinja code?
It refers to well-structured and readable dbt code that uses Jinja appropriately — balancing abstraction with clarity.
What is a key principle of writing dbtonic code?
Favor readability over strict DRY-ness. Not all repeated lines need to be turned into macros.
What is the recommended way to set variables in Jinja for dbt models?
Set them at the top of the model using {% set ... %} for better readability and reusability.
Why should you avoid setting Jinja variables inline in loops or SQL?
It reduces readability and makes code harder to maintain, especially as complexity grows.
