Write modes matrix
The following table describes the write modes that Prophecy supports by SQL warehouse and gem type.| Write mode | Databricks table | Databricks model | BigQuery table | BigQuery model | Snowflake model |
|---|---|---|---|---|---|
| Wipe and Replace Table (Default) | ✔ | ✔ | ✔ | ✔ | ✔ |
| Append Row | ✔ | ✔ | ✔ | ✔ | |
| Merge - Upsert Row | ✔ | ✔ | ✔ | ✔ | ✔ |
| Merge - Wipe and Replace Partitions | ✔ | ✔ | ✔ | ✔ | |
| Merge - SCD2 | ✔ | ✔ | ✔ | ✔ | ✔ |
| Merge - Wipe and Replace Rows Per Predicate | ✔ | ✔ | |||
| Merge - Delete and Insert | ✔ |
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 (likematerialized: '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 difference between partitioning in Google BigQuery and Databricks.- BigQuery
- Databricks
In Google BigQuery, partitioning is a table property.Partitioning is defined at the table schema level (time, integer range, or column value). Because it is a part of the table architecture, the physical storage in BigQuery is optimized by partitioning automatically.Once a table is partitioned, every write to that table, full or incremental, respects the partitioning. That means even when you drop and create an entirely new table, Google BigQuery creates the table with partitions in an optimized way.
Troubleshooting
You see schema mismatch errors
You see schema mismatch errors
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.
You see unexpected duplicate data in the target table
You see unexpected duplicate data in the target table
This happens mainly when using the Append Rows write mode.To solve this, consider using one of the merge modes instead of append.