Write strategies

Prophecy provides a set of write strategies that determine how you will store your processed data and handle changes to the data over time. This page describes each strategy so you can choose the best one for your use case. You will configure the write strategy in the Write Options tab of a target table.

​

Write modes matrix

The following table describes the write modes that Prophecy supports by SQL warehouse and gem type.

​

Write modes

​

Wipe and Replace Table (default)

Replaces all existing data with new data on each run. This is the simplest approach and ensures your target table always reflects the latest state of your source data.

​

Example: Replace vehicle registry

A full vehicle registry is refreshed each day, replacing all prior records with the latest list.

​

Partition the target table (BigQuery only)

The following partitioning parameters are available for the Wipe and Replace Table write mode on BigQuery.

---

​

Append Row

Adds new rows to the existing table without modifying existing data. No deduplication is performed, so you may end up with duplicate records.

​

Example: Add daily trips

Daily trips are appended so that historical trips remain intact.

---

​

Merge - Upsert Row

If a row with the same key exists, it is updated. Otherwise, a new row is inserted. You can also limit updates to specific columns, so only selected values are changed in matching rows.

​

Example: Update vehicle type only

When an incoming VEHICLE_ID matches an existing one, vehicle type information is updated while registration dates remain unchanged. The merge column is explicitly set to TYPE.

---

​

Merge - 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.

​

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.

​

Define partition granularity (BigQuery only)

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

---

​

Merge - SCD2

Tracks historical changes by adding new rows instead of updating existing ones. This lets you preserve a complete history, since every change generates a new entry rather than erasing old information. To be specific:

• New rows are added for incoming records with unique keys that don’t exist in the target table.
• For records matching existing unique keys, a new row is added only when the incoming data differs from the existing record.
• New rows are assigned a start date and a null end date (indicating it’s currently valid).
• If the unique key of the new record matched an existing record, the existing row is assigned an end date to mark when it stopped being valid.
• This creates a complete timeline showing how data evolved over time.

​

Example: Route assignment history

Each time a vehicle changes routes, a new row is added to preserve history. In this case, Determine new records by checking timestamp column is enabled, and ASSIGNED_AT is the timestamp column.

---

​

Merge - Wipe and Replace Rows Per Predicate

Update records that match conditions defined in the Use Predicate parameter. Instead of updating individual rows, it replaces the entire partition of the table that matches the given predicate. The predicate defines which rows in the target table should be fully replaced with the incoming data.

​

Example: Update trips from January 5th onwards

When any record from January 5th onwards needs updating, ALL records from that date forward are dropped and replaced with the incoming data. Records from before January 5th remain unchanged. Predicate: DATE >= '2024-01-05'

---

​

Merge - Delete and Insert

For rows in the target table that have keys that match rows in the incoming dataset, deletes matching rows from the existing table and replaces them. For incoming rows with new keys, inserts these normally. This ensures that updated records are fully replaced instead of partially updated.

​

Example: Refresh vehicle registry entries

If a vehicle already exists in the fleet, its old record is deleted and replaced with the incoming row. New vehicles are simply inserted.

---

​

How write modes work

Prophecy simplifies data transformation by providing intuitive write mode options that abstract away the complexity of underlying SQL operations. Behind the scenes, Prophecy generates dbt models that implement these write strategies using SQL warehouse-specific commands. When you select a write mode in a Table gem, Prophecy automatically generates the appropriate dbt configuration and SQL logic. This means you can focus on your data transformation logic rather than learning dbt’s materialization strategies or writing complex SQL merge statements. To understand exactly what happens when Prophecy runs these write operations, switch to the Code view of your project and inspect the generated dbt model files. These files contain the SQL statements and dbt configuration (like materialized: 'incremental') that dbt uses to execute the write operation. To learn more about the specific configuration options available for each SQL warehouse, visit the dbt documentation links below.

• BigQuery configurations
• Databricks configurations
• Snowflake configurations

​

Partitioning

Depending on the SQL warehouse you use to write tables, partitioning can have different behavior. Let’s examine the difference between partitioning in Google BigQuery and Databricks.

​

Troubleshooting