Snowflake + dbt 1.1.0 performance trick. This has no business working so well, but it does.
For large, incremental models with a surrogate unique_key - add one of your surrogate columns to the unique_key config. That’s it.
This change cut my MERGE times nearly in half:
(View Tweet)
Why does this work?
For Snowflake to MERGE, it must join your new data with the existing data in the table.
This trick allows the optimizer to prune the existing table before it joins. This eliminates a full table scan as a part of your MERGE operation.
https://t.co/mEMhSoZJYC (View Tweet)
(View Tweet)