All You Need To Know About Snowflake Database Replication & Fail-Over/Failback — Part 4

This blog is a continuation of my below previous part:

All You Need To Know About Snowflake Database Replication & Fail-Over/Failback — Part 3

All You Need To Know About Snowflake Database Replication & Fail-Over/Failback — Part 2

All You Need To Know About Snowflake Database Replication & Fail-Over/Failback — Part 1

If you missed seeing the previous blog than I would recommend reading that first to understand this blog better.

Please read through the below admin series of blog which might also interest you.

Snowflake Account Replication

How about a quick demo on Sharing the Replicated database?

This demo will cover how we can share the replicated database with our end consumers. The below diagram will give you the overview of high level architecture for this demo.

High Level Architecture

How about a quick demo on Database Replication and Cloning?

In this demo we will see how clone database behave differently in primary vs secondary database.

Use role accountadmin;
use warehouse COMPUTE_WH;

SHow databases like 'DATABASE_FOR_REPLICATION';

create database DATABASE_FOR_REPLICATION;
create schema if not exists SOURCESCHEMA;

use database DATABASE_FOR_REPLICATION;
Use schema SOURCESCHEMA;

show parameters like 'DATA_RETENTION_TIME_IN_DAYS' in account;

--Step 1 create base table
CREATE  OR REPLACE TABLE EMPLOYEE (MEMBER_ID NUMBER, LASTNAME VARCHAR(50));
INSERT INTO EMPLOYEE VALUES 
(1,'Choules' ),
(2,'Aaronsohn'),
(3,'Cotherill'),
(4,'Grewer' ),
(5,'Mammatt');

--Step 2 : Create employee clone
Create table EMPLOYEE_CLONE1 clone EMPLOYEE;
Create table EMPLOYEE_CLONE2 clone EMPLOYEE_CLONE1;

Count Check For All table

Now that we had already created a base database with all the required tables and clone table. Let's check the storage of all the tables in DATABASE_FOR_REPLICATION.

Now it’s time to do a storage check in Secondary account.

SELECT TABLE_NAME,
ID,
CLONE_GROUP_ID,
ACTIVE_BYTES,
TIME_TRAVEL_BYTES,
FAILSAFE_BYTES,
RETAINED_FOR_CLONE_BYTES,
TABLE_DROPPED,
TABLE_ENTERED_FAILSAFE
FROM INFORMATION_SCHEMA.TABLE_STORAGE_METRICS 
WHERE TABLE_CATALOG='DATABASE_FOR_REPLICATION' 
AND CATALOG_DROPPED IS NULL  
ORDER BY TABLE_NAME;

Output of above query

Observation:

1. All tables have identical 5 rows out of which 2 tables are cloned from Employee table and have no active bytes since it has reference to Employee table partition at the moment.
2. CLONE_GROUP_ID column has reference to Employee table ID for both cloned tables EMPLOYEE_CLONE1 & EMPLOYEE_CLONE2 regardless of it being cloned from cloned table.
3. At the moment, employee tables have only 1024 bytes in active byte's storage.
4. Both cloned tables, i.e. EMPLOYEE_CLONE1 & EMPLOYEE_CLONE2 have no storage of its own and referencing to base table micro-partition.

Enable the replication for source and target database and create a replica database in secondary account.

Refresh the replica database in secondary account using the below code:

Alter database DATABASE_FOR_REPLICATION refresh;

Again, do the count check in Secondary Account.

Output of above query

Now it's time to do a storage check in Secondary account.

SELECT TABLE_NAME,
ID,
CLONE_GROUP_ID,
ACTIVE_BYTES,
TIME_TRAVEL_BYTES,
FAILSAFE_BYTES,
RETAINED_FOR_CLONE_BYTES,
TABLE_DROPPED,
TABLE_ENTERED_FAILSAFE
FROM INFORMATION_SCHEMA.TABLE_STORAGE_METRICS 
WHERE TABLE_CATALOG='DATABASE_FOR_REPLICATION' 
AND CATALOG_DROPPED IS NULL  
ORDER BY TABLE_NAME;

Output of above query

Observation:

1. All tables have identical 5 rows out of which 2 tables are cloned from Employee table and have no active bytes since it has reference to Employee table partition at the moment.
2. For both cloned tables EMPLOYEE_CLONE1 & EMPLOYEE_CLONE2 the clone reference(CLONE_GROUP_ID) is different from the base table i.e. EMPLOYEE.
3. All 3 table has tables has now 1024 bytes in active byte’s storage. Now cloned tables are also contributing to total storage, which is opposite to what we have seen in Primary database.
4. So cloning doesn't contribute to total storage unless you perform any DML operation in the Primary database. While it contribute to storage for all replicated cloned tables regardless of whether you perform any DML operation or not.

Database Replication and Historical Usage Data

Historical data from both Snowflake Information Schema or Account_Usage schema of Snowflake database from primary account is not being replicated to the secondary database. Each account has its own query history, login history, copy history etc.

Account Usage metadata data is tightly coupled to the respective account and to safeguard this, Snowflake doesn't replicate this information to a secondary account.

What are things which can break the database replication?

Database replication may break if you have any of the below:

1. An external table in the primary database will not allow you to replicate the database.
2. You can’t replicate the shared database to a secondary database.
3. If you have any kind of Dangling reference between multiple databases out of which you are trying to replicate only one of them to a secondary database. For all Dangling Reference issue , see the documentation.
4. If you are trying to replicate an object which is referencing to dropped objects.
5. If you are trying to replicate higher edition features to lower edition secondary account.
6. An object contained in the primary database has a dangling reference to a tag in a different database.

I will conclude my series of Database Replication here.

You can read Snowflake documentation about more consideration in regard to Database Replication here.

Hope this blog helps you to get insight into the database replication and failover/fail back feature in Snowflake. Feel free to ask a question in the comment section if you have any doubts regarding this. Give a clap if you like the blog. Stay connected to see many more such cool stuff. Thanks for your support.

You Can Find Me:

Subscribe to my YouTube Channel: https://www.youtube.com/c/RajivGuptaEverydayLearning

Follow me on Medium: https://rajivgupta780184.medium.com/

Follow me on Twitter: https://twitter.com/RAJIVGUPTA780

Connect with me on LinkedIn: https://www.linkedin.com/in/rajiv-gupta-618b0228/

#Keep learning #Keep Sharing #RajivGuptaEverydayLearning #SnowflakeDataSuperhero #RajivGupta