Skip to main content
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.

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

​
Partitioning

Depending on the SQL warehouse you use to write tables, partitioning can have different behavior. Let’s examine the differences between partitioning in Databricks, Google BigQuery, and Snowflake.
In Databricks, partitioning is a write strategy.Databricks organizes data in folders by column values. Partitioning only makes sense when you’re using the Wipe and Replace Partitions write mode because it allows you to overwrite specific directories (partitions) without rewriting the whole table.For the Wipe and Replace Table option, the table is dropped and completely recreated. Partitioning doesn’t add any runtime benefit here, so this is not an option for Databricks.

​
Troubleshooting

This happens when the incoming and existing schemas don’t align.To solve this, use the “On Schema Change” setting to set behavior or ensure schema compatibility.
This happens mainly when using the Append Rows write mode.To solve this, consider using one of the merge modes instead of append.