---
title: Advanced templates
meta:
    description: Advanced usage of Tinybird datafiles, including how to work with query parameters.
headingMaxLevels: 3
---

# 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](/classic/cli/quick-start). Before reading this page, familiarize yourself with [query parameters](../work-with-data/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](https://github.com/tinybirdco/ecommerce_data_project_advanced) repository:

```shell {% title="Clone demo" %}
git clone https://github.com/tinybirdco/ecommerce_data_project_advanced.git
cd ecommerce_data_project_advanced
```

The repository contains the following file structure:

``` {% title="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](/classic/cli/datafiles/include-files) files:

{% tabs initial="only_buy_events" %}

{% tab label="only_buy_events"  %}

```tb {% title="includes/only_buy_events.incl" %}
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'
```
{% /tab %}

{% tab label="sales_pipes"  %}

```tb {% title="endpoints/sales.pipes" %}
INCLUDE "../includes/only_buy_events.incl"

NODE endpoint
DESCRIPTION >
    return sales for a product with color filter
SQL >
    %
    select date, sum(price) total_sales
    from only_buy_events
    where color in {{Array(colors, 'black')}}
    group by date
```
{% /tab %}

{% tab label="top_products"  %}

```tb {% title="pipes/top_product_per_day.pipe" %}
INCLUDE "../includes/only_buy_events.incl"

NODE top_per_day
SQL >
  SELECT date,
          topKState(10)(product) top_10,
          sumState(price) total_sales
  from only_buy_events
  group by date

TYPE materialized
DATASOURCE mv_top_per_day
ENGINE AggregatingMergeTree
ENGINE_SORTING_KEY date
```

{% /tab %}
{% /tabs %}

{% callout type="info" %}
When using include files to reuse logic in .datasource files, the extension of the file must be `.datasource.incl`.
{% /callout %}

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

{% tabs initial="top_products_1" %}

{% tab label="top_products_1"  %}

```tb {% title="includes/top_products.incl" %}
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
```
{% /tab %}

{% tab label="top_products_2"  %}   

```tb {% title="endpoints/top_products_last_week.pipe" %}
INCLUDE "../includes/top_products.incl" "DATE_FILTER=last_week"
```
{% /tab %}

{% tab label="top_products_3"  %}   

```tb {% title="endpoints/top_products_between_dates.pipe" %}
INCLUDE "../includes/top_products.incl" "DATE_FILTER=between_dates"
```
{% /tab %}
{% /tabs %}

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](/classic/cli/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:

```sql {% title="Using 'set' to declare a variable" %}
{\% 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:

```sql {% title="Define private variables" %}
%
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](/classic/cli/template-functions) for more information.