Snowflake Data Sharing Across Regions and Cloud Platforms — Part 2
This topic providing the instructions on using database replication to allow data providers to securely share data with data consumers across different regions and cloud platforms which helps to improve data availability and the accessibility.
This is a continuation of previous post on Snowflake data replication set up along with pre-requisites. Would highly recommend, please check the first blog before moving here on a technical demonstration.
Assumptions: Let’s assume the below things as a design consideration for the technical implementations/ demonstration, we discussed in part 1.
1 — There is a source db: metric_db used by XYZ company.
2 — Another primary db: metric_data_mart a subset of source db, populated with the required objects for ABC company usage.
3 — A replicated db: metric_data_mart, to create in ABC company Snowflake region in order to share the data.
4 — Data share to create in replicated db to consume in downstream apps.
5 — A final db: stg_metric_data_mart to create using data shares in ABC region for analytical usage.
Points to remember: Highlighting those as below.
1 — Snowflake hosted in AWS, Azure & GCP are supported for cross-region data sharing.
2 — Database replication is now a part of Account Replication.
3 — Currently, database roles are not included in the replication of a primary database.
4 — cross-region data sharing is not supported when objects are granted to a share via database roles.
5 — If the source region/ country differs from target database, then source org should confirm the legal/ regulatory restrictions.
6 — Planning is a must, as cross-region data sharing need data replication.
7 — Data providers only need to create one copy of the dataset per region.
8 — When sharing a view that references objects in multiple databases each of these other databases must be replicated.
9 — Before configuring data replication, must create an account in a region where you wish to share data and link it to your local account.
10 — Account replication enables the replication of objects from a source account to one or more target accounts in the same organization.
11 — Replicated objects in each target account are referred to as secondary objects and are replicas of the primary objects in the source account..
12 — A provider creates a primary replication group in a source account and enables replication to target accounts for consumers.
Demonstration/ Coding: Let’s recap. Built source db infrastructure. Sample tables has been created. Designed primary db to be shared with consumer. Track record changes made to source db and capture in primary db using stream object and to orchestrate. New account set up in target region. Finally enabling account database replication set up. Let’s move ahead.
Step 9: Set up primary database replication to a new database i.e. replicated database in the other region. Refer “design flow” diagram for more details. Let’s login using another account we have created in the target region.
Step 10: Schedule the refresh of replicated db using task object
Step 11: Create a share & add objects to the share in replicated db.
Finally one last step, let’s execute. :-) It’s simple. Isn’t it.
Step 12: Let’s create another database i.e. final db Please refer “design flow” diagram for more details. Create another trial account as mentioned in aws canada central region.
Step 13: Finally adding replicated db data share to final db account access. And should be executed in the replicated db account console.
Show shares command helps to describe the data shares information.
Let’s execute below code in final db.
Conclusion: This explains, how database replication copying/ transferring data from a database on one server to database on another server. Thanks for reading this post.
I hope this blog will help you to get understanding of data replication ways and how efficiently we can build this fashion to share data. Please feel free to chat with me in case of more questions you have, on my twitter handle or my LinkedIn or leave a comment below. Good luck!
