Materialisation strategy determines how dbt creates and updates each model in the warehouse — whether it stores the result as a physical table, a SQL view, an ephemeral CTE, or an incrementally-updated table. Choosing the right strategy per model directly impacts pipeline runtime, warehouse compute cost, and query performance. A mart model materialised as a view runs the full transformation SQL on every dashboard load; the same model materialised as a table runs once during the dbt job and serves pre-computed results to all subsequent queries.
Incremental materialisation is the most strategically important materialisation type. Instead of rebuilding the entire model on every dbt run, an incremental model processes only the new or updated rows since the last run, merging or appending them to the existing table. This transforms a full-table rebuild that takes 30 minutes into an incremental update that takes 30 seconds for large tables. The trade-off is complexity: the model must define which rows are 'new' using the `is_incremental()` condition, and the logic must be correct to avoid missing updates or duplicating rows.
Analogy🏏Cricket
🏏 Think of it like cricket: Migrating from Airflow to Prefect is like the same bowling coach shifting from traditional Test cricket notation to a modern T20 analytics dashboard — the underlying ball-by-ball data (the business logic) is exactly the same. What changes is how the data is recorded, displayed, and acted upon. The yorker that Bumrah bowls in over 20 is identical whether it is recorded in the old scorebook (Airflow DAG file) or the new analytics platform (Prefect flow). The migration is a transcription exercise, not a strategy change — and a wise coach verifies that the runs, wickets, and economies match exactly between the old and new system before decommissioning the scorebook. That verification step is the whole heart of the migration: because the yorker is unchanged, the only honest test is to run the same over through both systems and confirm the recorded runs, wickets and economies match to the last digit before the old scorebook is thrown away. Rushing to burn the scorebook the moment the shiny dashboard lights up is how teams lose a season of records to a silent transcription slip. The coach keeps both systems running in parallel for a while, reconciles their outputs ball by ball, and only when every figure agrees does he trust the new dashboard alone — a transcription is only complete when you have proven nothing was lost in the copying.
🏏 Showing the Cricket analogy — a Cricket version isn’t available for this concept yet.