During a database cloning, if a SNOWPIPE is cloned with a fully qualified table in the COPY statement in the pipe definition (in the form of db_name.schema_name.table_name or schema_name.table_name), then what will happen?

During a database cloning, if a SNOWPIPE is cloned with a fully qualified table in the COPY statement in the pipe definition (in the form of db_name.schema_name.table_name or schema_name.table_name), then what will happen?
A . Snowpipe loads duplicate data into the source table
B. Data loading will fail when the snowpipe is triggered
C. All the files will be loaded into stage again

View Answer

Answer: A

Explanation:

Cloning a database or schema clones all objects, including pipes that reference external stages, in the source database or schema. When a data file is created in a stage location (e.g. S3 bucket), a copy of the notification is sent to to every pipe that matches the stage location. This results in the following behavior:

If a table is fully qualified in the COPY statement in the pipe definition (in the form of

db_name.schema_name.table_name or schema_name.table_name), then Snowpipe loads duplicate data into the source table (i.e. the database.schema.table in the COPY statement) for each pipe.

If a table is not fully qualified in the pipe definition, then Snowpipe loads the data into the same table (e.g. mytable) in the source and cloned databases/schemas. https://docs.snowflake.com/en/user-guide/data-load-snowpipe-auto-s3.html#step-2-create-a-pipe-with-au to-ingest-enabled