---
title: Backfill strategies for iterations
meta:
    description: When iterating Data Sources or Materialized Views, you will often need to backfill data from a Tinybird Data Source to another. This guide will help you understand the different strategies to do it in a safe way.
---

# Backfill strategies for iterations

Backfilling data is the process of filling in missing data that didn't exist before. Whether you're changing data types, changing the sorting key, or redefining whole views, at some point you may need to run a backfill from the previous version of your Data Source or Materialized View to the new one.

This page introduces the key challenges of backfilling real-time data, and covers the different strategies to run a backfill when you are iterating a Data Source or Materialized View.

{% callout type="caution" %}
Before you start iterating and making critical changes to your Data Sources, Materialized Views, and Pipes, it's crucial to read the [Deployment Strategies](/classic/work-with-data/strategies/deployment-strategies) docs.
{% /callout %}

## The challenge of backfilling real-time data

The iteration of Data Sources or Materialized Views often needs a careful approach to backfill data. This process becomes critical, especially when you create a new version of a Data Source or Materialized View, which results in creating a new, empty Data Source or Materialized View.

The main challenge lies in migrating historical data while continuously ingesting new real time data. See the detailed explanation [Best practices for Materialized Views](/classic/work-with-data/process-and-copy/materialized-views/best-practices).

## Use case

Imagine you have the following Data Source deployed in your main Workspace:

```shell {% title="analytics_events.datasource" %}
SCHEMA >
    `timestamp` DateTime `json:$.timestamp`,
    `session_id` String `json:$.session_id`,
    `action` LowCardinality(String) `json:$.action`,
    `version` LowCardinality(String) `json:$.version`,
    `payload` String `json:$.payload`

ENGINE "MergeTree"
ENGINE_PARTITION_KEY "toYYYYMM(timestamp)"
ENGINE_SORTING_KEY "timestamp"
```

You want to modify the sorting key from `timestamp` to `action, timestamp`. This change requires you to create a new Data Source, for example `events1.datasource`.

After merging the Pull Request, your main Workspace contains `analytics_events` and `analytics_events_1` (also in the Branch while in CI). The two Data Sources include the existing version and the newly created, empty version you want to deploy.

How can you sync data between the two Data Sources? Use a backfill.

## How to move data in Tinybird

Reminder: "Running a backfill" means copying all the data from one Data Source to another Data Source. There are different ways to move data in Tinybird:

### Using Copy Pipes

A Copy Pipe is a Pipe used to copy data from one Data Source to another Data Source. This method is useful for one-time moves of data or scheduled executions (for example, every day at 00:00), but it's not recommended if you want to keep the data in sync between two Data Sources.

For a backfill, use the following Pipe to copy data from one Data Source to another. A later section explains why you need the `timestamp BETWEEN {{DateTime(start_backfill_timestamp)}} AND {{DateTime(end_backfill_timestamp)}}` condition.

```shell {% title="backfill_data.pipe file" %}
NODE node
SQL >
   %
   SELECT *
   FROM analytics_events
   WHERE timestamp BETWEEN {{DateTime(start_backfill_timestamp)}} AND {{DateTime(end_backfill_timestamp)}}

TYPE COPY
TARGET_DATASOURCE analytics_events_1
```

Once deployed, you would need to run the following command to execute the copy:

```shell {% title="Command to run the Copy Pipe with the backfill_timestamp parameter" %}
tb pipe copy run backfill_data --param start_backfill_timestamp='1970-01-01 00:00:00' --param end_backfill_timestamp='2024-01-31 00:00:00' --wait --yes
```

Read more in the [Copy Pipes](/classic/work-with-data/process-and-copy/copy-pipes) documentation.

### Using Materialized Views

A Materialized View is a Pipe that materializes data from one Data Source to another Data Source.

This method is useful to keep the data in sync between two Data Sources.

```shell {% title="sync_data.pipe file" %}
NODE node
SQL >
   %
   SELECT *
   FROM analytics_events
   WHERE timestamp > '2024-01-31 00:00:00'

TYPE materialized
DATASOURCE analytics_events_1
```

{% callout type="caution" %}
By default, a Materialized View materializes only new incoming data. It doesn't process old data.

It can be forced by using `tb pipe populate` command using the CLI, but be careful as this can lead to duplicate or loss of data as explained in the previous section.
{% /callout %}

Combine both methods to synchronize the Data Sources and backfill data.

## Scenarios for backfill strategies

Depending on your use case and ingestion pattern, there are different recommended strategies to backfilling data in Tinybird. The complexity of this migration depends on several factors, notably the presence of streaming ingestion. The most common scenarios are:

{% callout %}
Tinybird is actively improving this workflow. Reach out to [Tinybird support](/classic/support) if you have any questions.
{% /callout %}

- **Scenario 1: Not in production**.
- **Scenario 2: Full replacement every few hours**.
- **Scenario 3: Streaming ingestion WITH incremental timestamp column**.
- **Scenario 4: Streaming ingestion WITHOUT incremental timestamp column**.

### Scenario 1: Not in production

If you aren't in production or the data from that Data Source isn't being used and **you can accept losing data**, you can opt-in by create a new Data Source and start using it right away. Alternatively you can remove and re-create the original Data Source using a custom deployment.

Once you start appending data to the Data Source, data starts appearing in the new Data Source.

### Scenario 2: Full replacement every few hours

If you are running a full replacement every few hours, you can create a Materialized View the two Data Sources.

To synchronize data between the two Data Sources, use a Materialized Pipe (MV) that materializes data from the old Data Source to the new one:

```shell {% title="Materialize data from old to new Data Source" %}
NODE migration_node
SQL >
    SELECT *
    FROM analytics_events

TYPE materialized
DATASOURCE analytics_events_1
```

Deploy this new Pipe with the modified Data Source. After you deploy the Pull Request, the Materialized Pipe and new Data Source are available, and the Pipe materializes data from the old Data Source.

Wait until the full replacement finishes and the new Data Source contains all the data. Then, create a Pull Request to connect the new Data Source to the rest of your Pipe Endpoints.

### Scenario 3: Streaming ingestion WITH incremental timestamp column

If you have streaming ingestion using the [events API](/classic/get-data-in/ingest-apis/events-api) with a huge ingest rate, you can use the following strategy to not be impacted by [the backfilling challenge with real-time data](#the-challenge-of-backfilling-real-time-data).

{% callout type="caution" %}
To use this strategy successfully, your Data Source must have an incremental timestamp column with the same time zone. This example uses the `timestamp` column.
{% /callout %}

First, create a new Pipe that materializes data from the old Data Source to the new one, **but filters by a future timestamp**. For example, if you deploy the Pull Request at `2024-02-02 13:00:00`, use `timestamp > '2024-02-02 13:30:00'`.

```shell {% title="sync_data.pipe file" %}
NODE node
SQL >
   SELECT *
   FROM analytics_events
   WHERE timestamp > '2024-02-02 13:30:00'

TYPE materialized
DATASOURCE analytics_events_1
```

{% callout %}
Tinybird is using the `timestamp > '2024-02-02 13:30:00'` condition to only materialize data that is newer than the `2024-02-02 13:30:00` timestamp.
{% /callout %}

Then, create a Copy Pipe with the same SQL statement, but use two parameters to filter by a timestamp range instead of a specific future timestamp.

Use these parameters for better control of the backfilling process. For example, when moving large amounts of data, split the backfill into batches to avoid overloading the system.

```shell {% title="backfill_data.pipe file" %}
NODE node
SQL >
   %
   SELECT *
   FROM analytics_events
   WHERE timestamp BETWEEN {{DateTime(start_backfill_timestamp)}} AND {{DateTime(end_backfill_timestamp)}}

TYPE COPY
TARGET_DATASOURCE analytics_events_1
```

After making these code changes, create a Pull Request. The CI Workflow generates a new Branch.

#### CI workflow

After the CI Workflow finishes successfully, it creates a new Branch.

For the following steps, use the CLI. If you don't have it installed, you can follow the [CLI installation docs](/classic/cli/install).

First, you should be able to authenticate in the Branch by copying the Token from the Branch or using these commands:

```shell {% title="Authenticate in the Branch" %}
# You can use `tb auth -i` to authenticate in the branch
tb auth -i

# Or you can switch to the branch if you are already authenticated
tb branch ls

# By default, the CI Workflow will create a branch following the pattern `tmp_ci-<PULL_REQUEST_ID>`.
tb branch use <NAME_BRANCH>
```

It's best to run the Copy Pipe outside of the CI Workflow.

As you don't have continuous ingestion in the Branch, don't wait for the future filter timestamp. Instead, run directly the Copy Pipe to backfill the data by running the following command:

```shell {% title="Run the Copy Pipe" %}
tb pipe copy run backfill_data --param start_backfill_timestamp='1970-01-01 00:00:00' --param end_backfill_timestamp='2024-02-02 13:30:00' --wait --yes
```

After the Copy Pipe finishes, the new Data Source contains all the data. Compare the number of rows in both Data Sources with the following commands:

```shell {% title="Compare the number of rows" %}
tb sql "SELECT count() FROM analytics_events"
tb sql "SELECT count() FROM analytics_events_1"
```

#### CD workflow

After testing the backfill in the Branch, merge the Pull Request. The CD Workflow performs the same operation as in the Branch: first deploy the resources, and then run the data operations manually, as recommended, or automate them with a custom deployment.

{% callout type="caution" %}
**Verify that you deployed the new Data Source before the timestamp used in the Materialized Pipe. Otherwise, the new Data Source is missing data**.

For example, if you have used `timestamp > '2024-02-02 13:30:00'` in the Materialized Pipe, you should verify that you have deployed before `2024-02-02 13:30:00`.

If you deployed after `2024-02-02 13:30:00`, remove the Data Source and restart the process with a different timestamp.
{% /callout %}

At `2024-02-02 13:30:00`, data starts appearing in the new Data Source. Then, execute the same process used in the CI Workflow to backfill the data.

First, authenticate in the Workspace by running the following command:

```shell {% title="Authenticate in the Workspace" %}
tb auth -i
```

Then, run the Copy Pipe to backfill the data:

```shell {% title="Run the Copy Pipe" %}
tb pipe copy run backfill_data --param start_backfill_timestamp='1970-01-01 00:00:00' --param end_backfill_timestamp='2024-02-02 13:30:00' --wait --yes
```

{% callout %}
If the Copy Pipe fails, rerun the same command without duplicating data. **The Copy Pipe copies data only if the process succeeds.**

If you get any error like `MEMORY LIMIT`, you can also run the Copy Pipe in batches. For example, you could run the Copy Pipe with a timestamp range of 1 hour, 1 day, 1 week, depending on the amount of data you are moving.
{% /callout %}

After the Copy Pipe finishes, the new Data Source contains all the data. Compare the number of rows by running the following command:

```shell {% title="Compare the number of rows" %}
tb sql "SELECT count() FROM analytics_events"
tb sql "SELECT count() FROM analytics_events"
```

When both places contain the same number of rows, connect the new Data Source to the rest of the Dataflow.

Finally, you have the Data Source with the new schema and all the data migrated from the previous one. The Data Source is receiving real-time data directly and now the next step is to remove the Materialized Pipe and Copy Pipe you have used to backfill the data.

To do that, create a Pull Request and remove (`git rm`) the Materialized Pipe and Copy Pipe used to backfill the data. After merging the Pull Request, the workflow automatically removes the resources. Verify this operation in CI.

### Scenario 4: Streaming ingestion WITHOUT incremental timestamp column

If you have streaming ingestion, but you don't have an incremental timestamp column, you can use one of the following strategies to backfill data in Tinybird.

{% callout %}
Reach out to [Tinybird support](/classic/support) if you have any questions or you aren't sure how to proceed.
{% /callout %}

- **Strategy 1**: Run a populate, but consider [the previously mentioned challenges of backfilling real-time data](#the-challenge-of-backfilling-real-time-data).
- **Strategy 2**: Move ingestion to the new Data Source until you finish backfilling data. **The data in your old Data Source remains outdated until the new Data Source is fully synchronized**.

#### Strategy 1: Run a populate

{% callout type="caution" %}
Before following this strategy, you should be aware of the [backfilling challenge with real-time data](#the-challenge-of-backfilling-real-time-data).
{% /callout %}

Consider that the use case is the same as the previous one, but you don't have an incremental timestamp column. You can't rely on the `timestamp` column to filter the data as it's not incremental.

First, create a Materialized Pipe that materializes data from the old Data Source to the new one.

```shell {% title="backfill_data.pipe file" %}
NODE migrating_node
SQL >
   SELECT *
   FROM analytics_events

TYPE materialized
DATASOURCE analytics_events_1
```

To run the backfill, use the `tb pipe populate` command. This command materializes data from the old Data Source to the new one. Because you don't need to wait until a future timestamp, you can run it inside the CI/CD Workflow.

You can create a custom deployment using `VERSION=1.0.0` in the `.tinyenv` file and placing the custom deployment script in the `deploy/1.0.0` folder:

```shell {% title="Scripts generated inside the deploy folder" %}
deploy/1.0.0
├── deploy.sh ## This is the script that will be executed during the deployment
```

Modify the `deploy.sh` script to run the `tb pipe populate` command:

```shell {% title="deploy.sh script" %}
#!/bin/bash

# This script will be executed after the deployment
# You can use it to run any command after the deployment

# Run the populate Pipe
tb pipe populate backfill_data --node migrating_node --wait
```

After making these code changes, create a Pull Request. The CI Workflow generates a Branch with the new Data Source. Verify that everything works as expected, as described in the previous section.

```shell
# You can use `tb auth -i` to authenticate in the branch
tb auth -i

# Or you can switch to the branch if you are already authenticated
tb branch ls

# By default, the CI Workflow will create a branch following the pattern `tmp_ci-<PULL_REQUEST_ID>`.
tb branch use <NAME_BRANCH>

# Compare the number of rows by running the following command:
tb sql "SELECT count() FROM analytics_events"
tb sql "SELECT count() FROM analytics_events_1"
```

After verifying that everything works as expected, merge the Pull Request. The CD Workflow generates a new Data Source in the Main Branch. After the CD Workflow finishes successfully, verify it the same way as in the Branch.

#### Strategy 2: Move ingestion to the new Data Source

Consider that the use case is the same as the previous one. You don't have an incremental timestamp column. You can't rely on the `timestamp` column to filter the data as it's not incremental and you don't want to run a populate as you might be impacted by [the backfilling challenge with real-time data](#the-challenge-of-backfilling-real-time-data).

In this case, you can move the ingestion to the new Data Source until you finish backfilling data.

First, create a Copy Pipe that copies data from the old Data Source to the new one.

```shell {% title="backfill_data.pipe file" %}
NODE migrate_data
SQL >
   SELECT *
   FROM analytics_events

TYPE COPY
TARGET_DATASOURCE analytics_events_1
```

{% callout %}
You can also parameterize the Copy Pipe to filter by a parameter and gain better control of the backfilling process.
{% /callout %}

After making these code changes, create a Pull Request. The CI Workflow generates a new Branch with the new Data Source and the Copy Pipe.

Now, run the Copy Pipe to backfill the data. To do that, authenticate in the branch by either copying the Token from the branch.

```shell {% title="Authenticate in the branch" %}
# You can use `tb auth -i` to authenticate in the branch
tb auth -i

# Or you can switch to the branch if you are already authenticated
tb branch ls

# By default, the CI Workflow will create a branch following the pattern `tmp_ci-<PULL_REQUEST_ID>`.
tb branch use <NAME_BRANCH>

# Once you have authenticated in the branch, you can run the Copy Pipe by running the following command:
tb pipe copy run backfill_data --node migrate_data --wait --yes
```

After the Copy Pipe finishes, the new Data Source contains all the data. Because you likely aren't ingesting data into your Branch, both numbers should match.

```shell {% title="Compare the number of rows" %}
tb sql "SELECT count() FROM analytics_events"
tb sql "SELECT count() FROM analytics_events_1"
```

Now, merge the Pull Request. The CD Workflow generates the new resources in the main Branch.

Modify ingestion to send data to the new Data Source. **While you ingest data into the new Data Source, ingestion into the old Data Source stops**.

Once all the ingestion is pointing to the new Data Source, you should verify that new data is being ingested into the new Data Source and nothing is being ingested into the old one. To do that you could query the Data Source directly or the Service Data Source [tinybird.datasources_ops_log](/classic/monitoring/service-datasources).

Start backfilling data by running the Copy Pipe with the following command:

```shell {% title="Run the Copy Pipe" %}
tb pipe copy run backfill_data --node migrate_data --wait --yes
```

{% callout %}
There are sometimes when the Data Source you are modifying has downstream dependencies, in that case when creating the new version of the Data Source you need to make sure that you create new version of all downstream dependencies to avoid connecting two different Data Sources receiving data to the same part of the Dataflow hence duplicating data.
{% /callout %}

## Next steps

If you're familiar with backfilling strategies, check out the [Deployment Strategies](/classic/work-with-data/strategies/deployment-strategies) docs.