Contents

2. Templating & Macros

You can find the video summary for this section here.

2.1. Jinja Templates

In dbt, you can combine SQL with Jinja, a templating package in Python.

Using Jinja turns your dbt project into a programming environment for SQL, giving you the ability to do things that aren’t normally possible in SQL. For example, with Jinja you can:

Use control structures (e.g. if statements and for loops) in SQL Use environment variables in your dbt project for production deployments

Per dbt documentation, Jinja is useful for:

  • “Use control structures (e.g. if statements and for loops) in SQL

  • Use environment variables in your dbt project for production deployments

  • Change the way your project builds based on the current target.

  • Operate on the results of one query to generate another query, for example:

  • Abstract snippets of SQL into reusable macros — these are analogous to functions in most programming languages. “

Some useful functions in dbt:

  • References: {{ ref() }}

  • Source: {{ source('','') }}

And others such as:

  • Statements {% ... %}

  • Comments {# ... #}

2.2. Macros

dbt provides out of the box utility functions as a part of the package. The following is an example of a dbt.util:

SELECT
  field_1,
  field_2,
  field_3,
  COUNT(*)
FROM aggregated_table
{{ dbt_utils.group_by(3) }}

You can enable dbt utils by running the following command

dbt deps

Packages get installed in the dbt_modules directory

2.3. Templates

Personally, I call custom macros templates.

however, macros is a better defined term in the community

One can create templates/custom macros through creating such macros and putting them in their designated folder macros/.

2.3.1. Custom Macro Example

Let’s create a calculation for the Haversine Formula

Not that dbt already created this utility function, found here. This formula calculates the distance between two geographical points, latitude & longitude.

In macros/ folder, we create the geo_macros.sql .sql file

{% macro haversine_distance_calculation(column_long1, column_lat1, column_long2, column_lat2 ) %}
    DEGREES(
        ACOS(
            LEAST(1.0,
                 COS(RADIANS({{column_lat1}})) * COS(RADIANS({{column_lat2}})) *
                 COS(RADIANS({{column_long1}}) - RADIANS({{column_long2}})) +
                 SIN(RADIANS({{column_lat1}})) * SIN(RADIANS({{column_lat2}}))
            )
        )
    )
{% endmacro %}

I would like to add more, but I leave it up to the reader to learn more about tests when they are curious about taking those next steps!

1. Testing, Testing… 1. Next Steps!