[Feature] Add TRUNCATE INSERT to materializations list

[akmalsoliev]

Is this your first time submitting a feature request?

• I have read the expectations for open source contributors
• I have searched the existing issues, and I could not find an existing issue for this feature
• I am requesting a straightforward extension of existing dbt-redshift functionality, rather than a Big Idea better suited to a discussion

Describe the feature

Instead of creating a new table, the approach involves using a TRUNCATE operation followed by an INSERT to update the existing materialized tables, which already have predefined data types.

Describe alternatives you've considered

No response

Who will this benefit?

This feature benefits database administrators, data engineers, developers, business analysts, and organizations by efficiently updating large datasets in existing materialized tables without the need for creating new tables, ensuring data integrity and speeding up data refreshes and decision-making processes.

Are you interested in contributing this feature?

Yes

Anything else?

No response

[amychen1776]

@akmalsoliev Does the delete+insert method for incremental materialization fit your use case here?https://docs.getdbt.com/docs/build/incremental-strategy#supported-incremental-strategies-by-adapter

[akmalsoliev]

@amychen1776 Hello and sorry for a late reply. Using DELETE + INSERT is not ideal because it occasionally changes the predefined data type. For example, a VARCHAR(100) might unexpectedly become VARCHAR(250). This is not the desired behaviour; we want the operation to forcefully fail in such cases. I understand that this should not be happening, however, in practice this action occurred.
Additionally, DELETE + INSERT strategy is quite slow in comparison to TRUNCATE + INSERT.

[amychen1776]

Thank you for the clarification! That is odd that it would change the data type.
Unfortuantely at this time we are unable to prioritize this on our roadmap. I'm going to leave this issue open to continue to collect information through and additional feedback

[akmalsoliev]

I don't mind making a PR, if you're open for a contribution

[amychen1776]

A contribution will always be welcomed :)

[akmalsoliev]

Hey @amychen1776 any tips and tricks on where to get started with this issue? I took a look around and it appears that most of the materialisation macros are located in dbt/include/redshift/macros/relations/materialized_view/, however, got stuck there with figuring on how to proceed.

[amychen1776]

@akmalsoliev as a side note - I'm curious if this would help resolve your use case? dbt-labs/dbt-core#10672