I am having a use case in my engagement where I need to archive data from reporting table in Snowflake to another table at a monthly cadence. The purpose of this archive is the end user wants to do analytics at the current state of the data in the reporting tables and also perform some trends check with the historical/archive data.
There is a dbt snapshot available but not all tables have a timestamp in there and sometimes these have lot of dimensions (60+). Also how do I delete the data from the reporting tables once its archived.
We are using dbt-core and using bitbucket pipelines to schedule as of now , so how can we achieve this functionality of setting up monthly sync of archiving.