[ADAP-691] [Feature] add incremental strategy append

[leo-schick]

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-bigquery functionality, rather than a Big Idea better suited to a discussion

Describe the feature

Other dbt adapters support the incremental stragey append but dbt-bigquery does not: Supported incremental strategies by adapter

I was wondering why this has not been implemented since it is quite useful when you want to create a log table holding snapshot data with a timestamp.
I found blog item Creating “Append” Materialization in dbt (data build tool) which discusses addinng append_only as a materialization option to dbt-bigquery on an older dbt version, so it must be possible to do that in big query.

Describe alternatives you've considered

I implemented this now with merge by using a column with current_timestamp() as key. That works not perfect:

• when running full_refresh, the data gets lost
• it is a total overhead creating unnecessary processing time

Who will this benefit?

People building historical data tables or people which use the incremental strategy append with another adapeter and now want to switch their dbt project over to big query.

Are you interested in contributing this feature?

No response

Anything else?

No response

[rloredo]

If you do merge without a unique key specification the behaviour should be append only.
This is in the documentation

Not specifying a unique_key will result in append-only behavior, which means dbt inserts all rows returned by the model's SQL into the preexisting target table without regard for whether the rows represent duplicates.

But I haven't tested it.

[leo-schick]

@rloredo OK, nice to know. I think this should be implemented by default so that this works equal to the other db provider with incremental strategy append.

[github-actions]

This issue has been marked as Stale because it has been open for 180 days with no activity. If you would like the issue to remain open, please comment on the issue or else it will be closed in 7 days.

[github-actions]

Although we are closing this issue as stale, it's not gone forever. Issues can be reopened if there is renewed community interest. Just add a comment to notify the maintainers.

[dbeatty10]

Thanks for raising this @leo-schick !

I think this would be covered by the work in dbt-labs/dbt-core#9290. Namely, these two:

• [CT-958] Refactor incremental materialization, using new incremental_strategy foundations #242
• [CT-2073] Full compatibility with incremental materialization refactor in v1.3 #523

So I'm going to leave this closed as a duplicate of those ones.