Wipe and Replace Partitions

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

Parameters

Parameter	Description
Partition by	Defines 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 Change	Specifies 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: Replace one day’s sales

A sales table is partitioned by the SALE_DATE column. A corrected dataset arrives for 2024-01-15, so only that partition is replaced. All other partitions remain unchanged.

Existing target table

ORDER_ID	PRODUCT	SALE_DATE
101	Laptop	2024-01-14
201	Mouse	2024-01-15
202	Monitor	2024-01-15

Incoming dataset

ORDER_ID	PRODUCT	SALE_DATE
301	Mouse	2024-01-15
302	Keyboard	2024-01-15

Resulting target table

ORDER_ID	PRODUCT	SALE_DATE
101	Laptop	2024-01-14
301	Mouse	2024-01-15
302	Keyboard	2024-01-15

Because this write mode replaces entire partitions, Prophecy removes all existing rows from any partition represented in the incoming dataset before writing the new rows. Partitions that are not represented in the incoming dataset are left unchanged. In this example, only the 2024-01-15 partition is replaced, while the 2024-01-14 partition remains unchanged.

Define partition granularity (BigQuery only)

The following partitioning parameters allow you to define the partition granularity for this operation.

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.

Overview

Sources and targets

Data processing

Reporting

Advanced

Parameters

Example: Replace one day’s sales

Define partition granularity (BigQuery only)

​Parameters

​Example: Replace one day’s sales

​Define partition granularity (BigQuery only)

Parameters

Example: Replace one day’s sales

Define partition granularity (BigQuery only)