Snowflake failover - replication mechanim

Question

I have been diving into the documentation about how to manage the failover/replication use cases within Snowflake. here Basically, the failover strategy is based on the replication feature of a database between two different accounts of the same organization sit in different regions.

From the point of view of replication I can configure these two DB's and setup my replication within a task that refreshes the primary DB every 10 minutes and in this way try to keep the secondary DB as much update as possible. Nevertheless, in case of an unexpected event my secondary database once will be promoted as primary will have the version of data based on the last completed update which means that I could have lost partially the whole new data/transformations carried out just before the outage. This makes me think on:

• There is any way to reduce as much as possible the lose data into a failover design within Snowflake than just schedule the replication task to 1 minute?
• In the scenario where the outage has been solve and I need to bring my primary DB back how to manage the portion of data that I could not replicate to my secondary DB but merge with the new data I could have generated whereas I was running in the secondary DB as production?

Thanks so much

Answer 1

a) To reduce data loss, one strategy is to schedule replication operations frequently as you pointed out. The other is to the have the ability replay the recent ETL jobs after failover. For this you need to ensure the source data is available and ETL processes can be recovered after a disaster. And that ETL can be replayed in an idempotent manner.

b) Merging changes / conflict resolution is not supported. Snowflake's Database Replication follows a single-master model. Refreshing the secondary database overwrites it with primary's current state. The recommendation is to recover lost data on the primary after failover by replaying ETL as suggested in (a).