Question 89 - ARA-C01 discussion

The architecture in the image shows a Snowflake data platform with two databases, DB1 and DB2, and two schemas, SH1 and SH2. DB1 contains a table TBL1 and a stage STAGE1. DB2 contains a table TBL2. The image also shows a snippet of code written in SQL language that copies data from STAGE1 to TBL2 using a file format FF PIPE 1.

To copy data from DB1 to TBL2, there are two possible options among the choices given:

Option B: Use a named external stage that references STAGE1. This option requires creating an external stage object in DB2.SH2 that points to the same location as STAGE1 in DB1.SH1.The external stage can be created using theCREATE STAGEcommand with theURLparameter specifying the location of STAGE11. For example:

SQLAI-generated code. Review and use carefully.More info on FAQ.

use database DB2;

use schema SH2;

create stage EXT_STAGE1

url = @DB1.SH1.STAGE1;

Then, the data can be copied from the external stage to TBL2 using theCOPY INTOcommand with theFROMparameter specifying the external stage name and theFILE FORMATparameter specifying the file format name2. For example:

SQLAI-generated code. Review and use carefully.More info on FAQ.

copy into TBL2

from @EXT_STAGE1

file format = (format name = DB1.SH1.FF PIPE 1);

Option E: Use a cross-database query to select data from TBL1 and insert into TBL2. This option requires using theINSERT INTOcommand with theSELECTclause to query data from TBL1 in DB1.SH1 and insert it into TBL2 in DB2.SH2.The query must use the fully-qualified names of the tables, including the database and schema names3. For example:

SQLAI-generated code. Review and use carefully.More info on FAQ.

use database DB2;

use schema SH2;

insert into TBL2

select * from DB1.SH1.TBL1;

The other options are not valid because:

Option A: It uses an invalid syntax for theCOPY INTOcommand.TheFROMparameter cannot specify a table name, only a stage name or a file location2.

Option C: It uses an invalid syntax for theCOPY INTOcommand.TheFILE FORMATparameter cannot specify a stage name, only a file format name or options2.

Option D: It uses an invalid syntax for theCREATE STAGEcommand.TheURLparameter cannot specify a table name, only a file location1.

1: CREATE STAGE | Snowflake Documentation

2: COPY INTO table | Snowflake Documentation

3: Cross-database Queries | Snowflake Documentation