Advanced templates¶

When developing multiple use cases, you might want to reuse certain parts or steps of an analysis, such as data filters or similar table operations.

Read on to learn about advanced usage of the datafile system when using the Tinybird CLI. Before reading this page, familiarize yourself with query parameters.

Templates reuse¶

You can set up templates to reuse certain parts or steps of an analysis, such as data filters or similar table operations.

To follow along, clone the ecommerce_data_project_advanced repository:

git clone https://github.com/tinybirdco/ecommerce_data_project_advanced.git
cd ecommerce_data_project_advanced

The repository contains the following file structure:

ecommerce_data_project/
    datasources/
        events.datasource
        mv_top_per_day.datasource
        products.datasource
        fixtures/
            events.csv
            products.csv
    endpoints/
        sales.pipe
        top_products_between_dates.pipe
        top_products_last_week.pipe
    includes/
        only_buy_events.incl
        top_products.incl
    pipes/
        top_product_per_day.pipe

Take a look at the sales.pipe API Endpoint and the top_product_per_day.pipe Pipe that materializes to a mv_top_per_day Data Source. Both use the same node, only_buy_events, through the usage of include files:

NODE only_buy_events
SQL >
    SELECT
        toDate(timestamp) date,
        product,
        joinGet('products_join_by_id', 'color', product) as color,
        JSONExtractFloat(json, 'price') as price
    FROM events
    where action = 'buy'

Include variables¶

You can include variables in a node template. The following example shows two API Endpoints that display the 10 top products, each filtered by different date intervals:

NODE endpoint
DESCRIPTION >
    returns top 10 products for the last week
SQL >
    select
        date,
        topKMerge(10)(top_10) as top_10
    from top_product_per_day
    {% if '$DATE_FILTER' = 'last_week' %}
        where date > today() - interval 7 day
    {% else %}
        where date between {{Date(start)}} and {{Date(end)}}
    {% end %}
    group by date

In the previous examples, the DATE_FILTER variable is sent to the top_products include, where the variable content is retrieved using the $ prefix with the DATE_FILTER reference.

You can also assign an array of values to an include variable. To do this, parse the variable using function templates, as explained in Template functions.

Variables and parameters¶

Parameters are variables whose value you can change through the API Endpoint request parameters. Variables only live in the template and you can set them when declaring the INCLUDE or with the set template syntax. For example:

{% set my_var = 'default' %}

By default, variables are interpreted as parameters. To prevent variables or private parameters from appearing in the auto-generated API Endpoint documentation, they need to start with _. For example:

%
SELECT
  date
FROM my_table
WHERE a > 10
{% if defined(_private_param) %}
  and b = {{Int32(_private_param)}}
{% end %}

You also need to use _ as a prefix when using variables in template functions. See Template functions for more information.