Skip to main content

Build MATE Models

Jinja is used extensively in MATE for model documentation, macros, and packages. However, Jinja is not exclusive to MATE; it is used throughout the DataOps platform as a template-rendering tool, including other orchestrators than MATE Transform Orchestrator. For an overview on DataOps Template Rendering, see Template Rendering in the DataOps Concepts section.

Now that we have an overall understanding of template rendering in DataOps, including some of the Jinja basics, let's dive into how Jinja is used in MATE.

Models form the foundation of MATE. The data cannot be modeled and transformed without these building blocks. Therefore, we recommend you understand how to build MATE models with and without Jinja.

Using SQL to Build MATE Models

As described in the Models section in the MATE Core Concepts doc, SQL SELECT statements are the foundation of a MATE model. The best way to describe this is by considering the following example:

Let's assume you need to build two staging tables as a foundation for a downstream dimension table.

As an aside, the accepted definition of a staging table is an intermediate table that sits in the modeling and transformation DAG between a source table and a final data product. It includes light-touch transformations shaping the data into the way you want it to be, including changes such as renaming columns and reformatting the values in a particular column. These light-touch transformations clean and standardize the data before it is transformed into the final product downstream.

In this SQL code sample, we create a model called stg_orders:

stg_orders.sql
stg_orders as (
select
id as order_id,
prod_type_code as product_type
items as items_ordered,
date as order_date
from
raw.stock_orders
)

And in this SQL code snippet, we create a model called stg_product_types:

stg_product_types.sql
stg_product_types as (
select
id as product_type_id,
code as product_type_code,
prod_type_desc as product_type_description
from
raw.product_types
)

These basic SQL models (stg_orders and stg_product_types) can then be used to build more complex downstream models.

For example:

The following code references the stg_orders and stg_product_types models described above to build a model (called dim_total_orders_product_type) that returns a list of orders per product type.

dim_total_orders_product_type.sql
with product_types as (
select * from {{ ref('stg_product_types') }}
),

with orders as (
select * from {{ ref('stg_orders') }}
),

final as (
select product_types.product_type_code,
product_types.product_type_description,
coalesce(sum(orders.items_ordered), 0), as total_orders

from product_types
left join orders using (orders.product_type)
)

select * from final
note

Jinja is used to reference the stg_orders and stg_product_typesmodels in this MATE Model.

Building MATE Models with Jinja

Jinja templating is extremely useful to simplify complex models as the following code example shows:

The original SQL is as follows:

with product_types as (
select * from {{ ref('stg_product_type') }}
),

final_stock_levels as (
select
stock_id,

sum(case when product_type = 'toy_trains' then stock_count else 0 end) as toy_trains_stock_count,
sum(case when product_type = 'toy_cars' then stock_count else 0 end) as toy_cars_stock_count,
sum(case when product_type = 'toy_dolls' then stock_count else 0 end) as toy_dolls_stock_count,
sum(case when product_type = 'toy_airplanes' then stock_count else 0 end) as toy_airplanes_stock_count

from stock_levels

group by 1
)

select * from final_stock_levels

And, the refactored Jinja and SQL model code is as follows, simplifying the code by increasing its readability and maintainability:

{%- set product_type_names = ['toy_trains','toy_cars','toy_dolls','toy_airplanes'] -%}

with product_types_tbl as (
select * from {{ ref('stg_product_type') }}
),

final_stock_levels as (
select
stock_id,

{% for product_type in product_types -%}

sum(case when product_type = '{{ product_type }}' then stock_count else 0 end)
as {{ product_type}}_stock_count

{%- if not loop.last -%}
,
{% endif -%}

{%- endfor %}

from stock_levels
group by 1
)

select * from final_stock_levels