Skip to main content
Replaces entire partitions in the target table. Only partitions containing updated data will be overwritten; other partitions will not be modified.

​
Parameters

ParameterDescription
Partition byDefines the partitions of the target table.
  • Databricks: Each unique value of the partition column corresponds to a partition. You cannot change the granularity of the partitions.
  • BigQuery: You must manually define the granularity of your partitions. BigQuery does not automatically infer how to write the partitions.
(Advanced) On Schema ChangeSpecifies how schema changes should be handled during the merge process.
  • ignore: Newly added columns will not be written to the model. This is the default option.
  • fail: Triggers an error message when the source and target schemas diverge.
  • append_new_columns: Append new columns to the existing table.
  • sync_all_columns: Adds any new columns to the existing table, and removes any columns that are now missing. Includes data type changes. This option uses the output of the previous gem.

​
Example: Update trips for a specific day

In this case, the partition column is the DATE column, where each partition corresponds to a single day. Since the incoming data includes records from 2024-01-15, all of the records from that date are dropped from the existing table and replaced with the new data.
Incoming table
TRIP_IDVEHICLE_IDDATE
4012012024-01-15
4022022024-01-15
Existing table
TRIP_IDVEHICLE_IDDATE
1011012024-01-14
3012012024-01-15
3022022024-01-15
Updated table
TRIP_IDVEHICLE_IDDATE
1011012024-01-14
4012012024-01-15
4022022024-01-15
Notice that only the 2024-01-15 partition was replaced with new data, while the 2024-01-14 partition remained untouched.

​
Define partition granularity (BigQuery only)

The following partitioning parameters allow you to define the partition granularity for this operation.
ParameterDescription
Column NameThe name of the column used for partitioning the target table.
Data TypeThe data type of the partition column.
Supported types: timestamp, date, datetime, and int64.
Partition By granularityApplicable only to timestamp, date, or datetime data type.
Defines the time-based partition granularity: hour, day, month, or year.
Partition RangeApplicable 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.