Implement dbt snapshot

[findinpath]

See https://docs.getdbt.com/docs/building-a-dbt-project/snapshots

Possible reference to implement this feature:
https://github.com/dbt-labs/dbt/tree/develop/plugins/postgres

https://github.com/dbt-labs/dbt-spark/blob/master/dbt/include/spark/macros/materializations/snapshot.sql

[hovaesco]

MERGE statement support in Trino engine is required for the implementation of Snapshot, related PR: trinodb/trino#7933

[p11o]

@hovaesco it may be a while until that PR gets merged. I noticed that other features that use the merge statement have been re-written here (i.e. incremental materialization). Would it be acceptable to take the same approach here?

[hovaesco]

In order to have atomic merge incremental strategy, we do require MERGE statement support in Trino. My belief is that the only acceptable solution. Do you refer to other incremental strategies?

[p11o]

Do you refer to other incremental strategies?

Hey thanks for taking the time to respond. Just so I'm on the same page, what do you mean by incremental strategies?

On my end I was referring to how dbt-trino overrides this merge statement for incremental models.

[hovaesco]

https://docs.getdbt.com/docs/building-a-dbt-project/building-models/configuring-incremental-models#about-incremental_strategy

[mdesmet]

At this moment dbt_trino only supports append incremental models. See https://github.com/starburstdata/dbt-trino/blob/master/dbt/include/trino/macros/materializations/incremental.sql

We have an open PR for adding the delete+insert strategy. However to be able to test it, we need to expand the test setup with iceberg and delta.

I actually concur with @p11o that we could implement this feature using delete+insert but still would suggest getting above two PR's in first.

[retrry]

Now that both merge and delete+insert is merged to main there are no blockers for this ticket?

[findinpath]

@retrry exactly, snapshot will likely land very soon on dbt-trino.

[retrry]

Could you release dbt-trino 1.2.2 with snapshot support?

[hovaesco]

Sure, we'll release it tomorrow, sorry for the delay.

[hovaesco]

@retrry here you go https://github.com/starburstdata/dbt-trino/releases/tag/v1.2.2

[retrry]

@hovaesco thanks!