Example: Replace vehicle registry
A full vehicle registry is refreshed each day, replacing all prior records with the latest list.Incoming table
Existing table
Updated table
Notice that all previous records (vehicles
| VEHICLE_ID | TYPE | REGISTERED_AT |
|---|---|---|
| 201 | Bus | 2024-01-15 |
| 202 | Train | 2024-01-16 |
| VEHICLE_ID | TYPE | REGISTERED_AT |
|---|---|---|
| 101 | Bus | 2023-12-01 |
| 102 | Tram | 2023-12-02 |
| VEHICLE_ID | TYPE | REGISTERED_AT |
|---|---|---|
| 201 | Bus | 2024-01-15 |
| 202 | Train | 2024-01-16 |
101 and 102) have been completely removed and replaced with the new incoming data.Partition the target table (BigQuery only)
The following partitioning parameters are available for the Wipe and Replace Table write mode on BigQuery.| Parameter | Description |
|---|---|
| Column Name | The name of the column used for partitioning the target table. |
| Data Type | The data type of the partition column. Supported types: timestamp, date, datetime, and int64. |
| Partition By granularity | Applicable only to timestamp, date, or datetime data type. Defines the time-based partition granularity: hour, day, month, or year. |
| Partition Range | Applicable only to int64 data type. Specify a numeric range for partitioning using a start, end, and interval value (e.g., start= 0, end=1000, interval=10).You must define an interval value so that Prophecy knows at what intervals to create the partitions. |
Only BigQuery tables can be partitioned at the table level. To learn more about partitioning, jump
to Partitioning.