Can Dbt do this? vs using a stored procedure...

The below is an except from a snowflake stored procedure I wrote, which works great. It backs up data, then deletes old pre-existing data and then inserts new data. If an error occurs during delete/insert, it restores the data from backup, thus enabling idempotency. Note: The delete + insert is not a merge. It might delete 100 rows and then insert 85. Or visa versa.

If this were dbt-ized, instead of using a stored procedure, would I be able to acheive the same level of data protection? If so, how do would this functionality working in dbt, with the backup and restore?

Thanks,

Dave

– backup data in the table. snowflake has unique commands for this…
drop table if exists FINANCE.CONFIGURATION_HIST_BAK;
create table FINANCE.CONFIGURATION_HIST_BAK clone FINANCE.CONFIGURATION_HIST;

– delete old and then inert new (think replace not merge)incomming data. Cannot use merge specifically
BEGIN
– delete -pre-existing data
DELETE FROM FINANCE.CONFIGURATION_HIST
WHERE CLOSING_MONTH = :VAR_CLOSING_MONTH;
–insert new data
INSERT INTO FINANCE.CONFIGURATION_HIST
(COUNTRY_CODE, SUBSIDIARY, COMBO, ENTRY_NO, CLOSING_MONTH)
SELECT COUNTRY_CODE, SUBSIDIARY, COMBO, ENTRY_NO, :VAR_CLOSING_MONTH
FROM FINANCE.CONFIGURATION_TEMP;
EXCEPTION
WHEN OTHER THEN
– restore table data on any error
ALTER TABLE FINANCE.CONFIGURATION_HIST SWAP WITH
FINANCE.CONFIGURATION_HIST_BAK;
VAR_BUS_ERROR := sqlerrm || ‘: Error Loading
ENTRY_NO_CONFIGURATION_HIST’;
raise BUS_ERROR_EXC;
END;

I received confrmation from dbt that dbt cannot do this. There are no exception handlng capabilities in dbt.

In this case what did you do?

That’s a classic example of fairly simple use case where we need this kind of ‘Algo’ wrapped under SF procedure…I am interested to know if still there are no alternatives available for this use case in dbt?