sql - Creating a table that joins two databases

Question

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?

question from:https://stackoverflow.com/questions/65946039/creating-a-table-that-joins-two-databases

Answer 1

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

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)