Backfill in Datastream lets you load a snapshot of historical data from
your source objects into your destination alongside ongoing change data capture.
You can manually initiate a full or partial backfill if you didn't select
**Backfill historical data** when you [configured your
source](https://docs.cloud.google.com/datastream/docs/create-a-stream#configuresourcedbinfo). Initiating a
backfill also lets you resynchronize destination objects if data is accidentally
deleted.

While a stream runs, you can monitor object statuses and stop an active backfill
at any time. Stopping an operation prevents corrupted schema or data from
replicating and lets you run parallel tasks one at a time to reduce source load.

## Object statuses

The various statuses in the lifecycle of initiating and stopping backfill for an
object include:

- No status (represented in the UI as `-`): Reasons for an object receiving
  this status include:

  - The stream hasn't been started.
  - The **Backfill historical data** checkbox wasn't selected (so the backfill is defined as manual).
  - The object is excluded explicitly from being backfilled automatically.
  - The stream is configured to include future tables. If this happens, then when new tables are added to the source, there's no automatic backfill task created for them (because new tables typically don't have any historical data to backfill).

  > [!NOTE]
  > **Note:** For more information, see [Configure information about the source
  > database for the
  > stream](https://docs.cloud.google.com/datastream/docs/create-a-stream#configuresourcedbinfo).

- `Pending`: backfill hasn't yet started for the object.

- `Active`: backfill is in progress for the object.

- `Completed`: backfill is completed for the object.

- `Stopped`: backfill is stopped for the object. If backfill is initiated
  again for the object, then Datastream will stream all existing data
  associated with the object from the source into the destination.

- `Failed`: backfill failed for the object and the backfill must be initiated
  again.

## Initiate backfill

1. Go to the **Streams** page in the Google Cloud Console.

   [Go to the Streams page](https://console.cloud.google.com/datastream/streams)
2. Click the stream that contains objects for which you want to initiate
   backfill.

3. Click the **Objects** tab.

4. Select the checkbox for each object for which you want to initiate backfill.

5. Click **Initiate backfill**.

   <br />

   > [!NOTE]
   > If an object has a status of `Pending` or `Active`, then you can't initiate backfill for the object.

   <br />

6. If you selected only one object, then in the dialog, click **INITIATE OBJECT
   BACKFILL** . Otherwise, if you selected multiple objects, then click
   **INITIATE OBJECT BACKFILLS**.

   Datastream will start backfill for the objects that you selected,
   and the status of each object will change from `Pending` to `Active` to
   `Completed`. When an object has a status of `Completed`, this means that
   Datastream has read all the data for the object, but the data might
   still be loading to the destination.

   > [!NOTE]
   > If an object has a status of `Failed`, then backfill failed for the object, and you must initiate the backfill again.

## Initiate partial backfill

In addition to full backfills, you can perform a partial backfill to load a
specific subset of data from the source into the destination. To do this, you
provide a SQL `WHERE` clause as a custom filter.

Datastream supports partial backfill for the following sources:

- SQL Server
- Spanner
- Oracle
- PostgreSQL
- MySQL

> [!NOTE]
> Partial backfill for Oracle, PostgreSQL, and MySQL sources is made available gradually. In the Google Cloud console, partial backfill option only appears if your stream supports this feature. If you use Google Cloud CLI or Datastream API to configure partial backfill for a stream that doesn't support the feature yet, the request fails.

To initiate a partial backfill:

1. Go to the **Streams** page in the Google Cloud Console.

   [Go to the Streams page](https://console.cloud.google.com/datastream/streams)
2. Click the stream that contains objects for which you want to initiate
   backfill.

3. Click the **Objects** tab.

4. Select the object for which you want to initiate backfill.

5. Click **Initiate backfill**.

6. In the panel that appears, select the **Enable custom filter** checkbox.

7. Enter your SQL `WHERE` clause. For example, `product_id > 12 AND timestamp =
   '2025-01-06T22:00:00.00'`.

8. Click **Initiate backfill**.

Datastream validates the filter. If the validation fails, an error
message appears above the editor. If the validation succeeds,
Datastream starts the backfill job.

### Supported custom filter syntax

When you provide a custom filter, Datastream supports a fundamental
subset of SQL `WHERE` clause syntax. The supported elements include:

- **Logical operators** : `AND`, `OR`
- **Comparison operators** : `=`, `<`, `>`, `<=`, `>=`, `!=`
- **Conditions** : `IN`, `NOT IN`, `IS NULL`, `IS NOT NULL`
- **Grouping** : parentheses `()` for defining condition groups
- **Values**:

  - Strings: text enclosed in single quotes, for example, `'value'`,
  - Numbers: integers (`123`), decimals (`123.45`), negative numbers (`-10`), and numbers with exponents (`1.2e3`).
  - Boolean values: `TRUE` or `FALSE` case-sensitive keywords.

  > [!NOTE]
  > **Note:** `DATE`, `TIME`, and `BYTE` and other complex type literals aren't directly supported. To filter based on a boolean column, you must compare it with the boolean literals using an operator (for example, `is_active = TRUE`).

- **Identifiers**:

  - Qualification: column names can be fully qualified using dots to specify the schema or table, for example, `myColumn`, `myTable.myColumn`, or `mySchema.myTable.myColumn`.
  - Unquoted identifiers: they can contain letters, numbers, and underscores, and must start with a letter or underscore. Case sensitivity for column or table names depends on the target database.
  - Double-quoted identifiers: must be enclosed in double quotes (`"`).

### Partial backfill limitations

Certain statements, operators, and keywords are explicitly forbidden and using
them results in an error. These include:

- Comments, such as `--` or `/* */`.
- Functions, for example, `UPPER(column)` and `NOW()`.
- Arithmetic expressions, for example, `column + 1 > 10`.
- The `LIKE` operator. Wildcards such as `%` or `_` aren't supported with `LIKE`.
- The `BETWEEN` operator. Use `>=` and `<=` instead.
- Subqueries, for example, `(SELECT ...)` within the `WHERE` clause.
- `CASE` statements, for example, `CASE WHEN ... END`.
- Column comparisons, such as `column1 = column2`. Comparisons must be against literals.
- `DATE` and `TIME` specific types or functions.
- Array or JSON specific operators.
- Explicit casting: for example, `CAST(column AS INT)`.

## Stop backfill

1. Go to the **Streams** page in the Google Cloud Console.

   [Go to the Streams page](https://console.cloud.google.com/datastream/streams)
2. Click the stream that contains objects for which you want to stop backfill.

3. Click the **Objects** tab.

4. Select the checkbox for each object for which you want to stop backfill.

5. Click **Stop backfill**.

   <br />

   > [!NOTE]
   > If an object has a status of `Completed`, `Stopped`, or `Failed`, then you can't stop backfill for the object.

   <br />

6. If you selected only one object, then in the dialog, click
   **Stop object backfill** . Otherwise, if you selected multiple objects, then
   click **Stop object backfills**.

   Datastream will stop backfill for the objects that you selected,
   and the status of each object will change to `Stopped`.

   When an object has this status, backfill is stopped for the object. If
   backfill is initiated again for the object, then Datastream will
   stream all existing data associated with the object from the source into the
   destination.

> [!NOTE]
> You can also use the **OBJECTS** tab to view additional information about the objects of a stream. This information includes:
>
> - The status of the objects.
> - How many events Datastream processed and loaded into the destination for an object in the last 7 days.
> - The total size (in GB) of all events that Datastream processed and loaded into the destination for an object in the last 30 days.
> - Details about the table columns of the database schemas that are streamed from the source into the destination.

## What's next

- To learn more about streams, see [Stream
  lifecycle](https://docs.cloud.google.com/datastream/docs/stream-states-and-actions).
- To learn how to view information about your stream, see [View a
  stream](https://docs.cloud.google.com/datastream/docs/view-a-stream).
- To learn how to modify a stream, see [Modify a
  stream](https://docs.cloud.google.com/datastream/docs/modify-a-stream).
- To learn how to monitor a stream, see [Monitor a
  stream](https://docs.cloud.google.com/datastream/docs/monitor-a-stream).
- To learn how to recover a failed stream, see [Recover a
  stream](https://docs.cloud.google.com/datastream/docs/recover-a-stream).