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;