CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
bayesian.asian
bayesian.asian

Reputation: 299

Creating a table that joins two databases

I am interested in creating a unified table that is the result of a join between two tables that are located in separate databases in my Snowflake environment. I found this resource which was quite helpful: https://resources.snowflake.com/youtube-all-videos/query-multiple-databases-within-snowflake

In the example in the video, the instructor says that the following will create a unified table in the CUSTOMER_SEGMENT database:

CREATE OR REPLACE TABLE UNIFIED_DATA AS (
   SELECT SEGMENT.CUST_JEY, MKTSEGMENT, COMMENT, ADDRESS, NAME
   FROM CUSTOMER_SEGMENT.PUBLIC.SEGMENT
   JOIN CUSTOMERS_DATA.PUBLIC.DATA
   ON DATA.CUST_KEY = SEGMENT.CUST_KEY)

I was confused by how Snowflake knows to store the table in the CUSTOMER_SEGMENT database. Is it because the FROM statement selects from that database?

Upvotes: 3

Views: 190

Answers (1)

Elad Kalif
Elad Kalif

Reputation: 16109

Notice that in the UI shown at the video the CUSTOMER_SEGMENT was selected as the database and PUBLIC as the schema.

enter image description here

This is the equivalent of running:

USE DATABASE CUSTOMER_SEGMENT;
USE SCHEMA PUBLIC;

When you run it Snowflake will auto add CUSTOMER_SEGMENT.PUBLIC to any table reference unless specified otherwise.

So :

CREATE OR REPLACE TABLE UNIFIED_DATA AS (
   SELECT SEGMENT.CUST_JEY, MKTSEGMENT, COMMENT, ADDRESS, NAME
   FROM CUSTOMER_SEGMENT.PUBLIC.SEGMENT
   JOIN CUSTOMERS_DATA.PUBLIC.DATA
   ON DATA.CUST_KEY = SEGMENT.CUST_KEY)

Is equivalent to:

CREATE OR REPLACE TABLE CUSTOMER_SEGMENT.PUBLIC.UNIFIED_DATA AS (
   SELECT SEGMENT.CUST_JEY, MKTSEGMENT, COMMENT, ADDRESS, NAME
   FROM CUSTOMER_SEGMENT.PUBLIC.SEGMENT
   JOIN CUSTOMERS_DATA.PUBLIC.DATA
   ON DATA.CUST_KEY = SEGMENT.CUST_KEY)

Upvotes: 3

Related Questions