# 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,