SnowPro Core · 10% of the exam

Data loading and unloading: free practice questions

5 sample questions from our 25-question bank for this domain — answers and explanations included. These are the same scenario-based style as the real Snowflake exam.

1. A Snowflake table stage (@%MY_TABLE) holds several CSV files. A developer issues the following command: REMOVE @%MY_TABLE; What is the result of this command?

  • A. The MY_TABLE table is dropped along with all its staged files.
  • B. All files currently in the MY_TABLE table stage are deleted, but the table itself is unaffected.✓ Correct
  • C. The command fails because REMOVE is not supported on table stages; only named stages support REMOVE.
  • D. All files in the table stage are deleted and the table's load metadata history is also purged.
Explanation

Option B is correct: REMOVE (or RM) deletes files from a stage but has no effect on the underlying table or its metadata. Option A is wrong because REMOVE only deletes staged files, never the table itself. Option C is wrong because REMOVE is fully supported on table stages, user stages, and named stages. Option D is wrong because REMOVE only removes the physical files; it does not affect load history metadata, which is maintained separately by Snowflake for up to 64 days.

2. A Snowflake architect is reviewing Snowpipe usage for a high-volume pipeline. They observe that files are frequently being re-sent to the Snowpipe REST API endpoint. What behavior should the architect expect regarding duplicate file submissions?

  • A. Snowpipe uses a 14-day deduplication window based on the file path and stage; submitting the same file path within this window will not result in duplicate loads.✓ Correct
  • B. Snowpipe has no deduplication mechanism; each API call results in an independent load regardless of whether the file was previously loaded.
  • C. Snowpipe deduplicates based on file content hash, so files with identical content are never loaded more than once, regardless of file name.
  • D. Snowpipe deduplicates indefinitely; once a file path is loaded, it can never be reloaded without dropping and recreating the pipe.
Explanation

Option A is correct: Snowpipe provides a best-effort deduplication window of approximately 7 minutes based on file path, with load history maintained for 14 days. Re-submitting the same file path within the 14-day history window will typically be skipped to prevent duplicate loads. Option B is wrong because Snowpipe does include deduplication logic based on the file path and stage combination. Option C is wrong because Snowpipe deduplicates on file path/stage metadata, not on content hashing. Option D is wrong because the deduplication window is time-bound (14 days), not permanent; a file can be reloaded after that window, and the pipe does not need to be dropped.

3. A data engineer needs to load a large set of CSV files stored in an Amazon S3 bucket into a Snowflake table. The files are already accessible via an existing external stage called `MY_S3_STAGE`. Which command correctly loads all CSV files from that stage into a table called `SALES_DATA`?

  • A. COPY INTO SALES_DATA FROM @MY_S3_STAGE FILE_FORMAT = (TYPE = 'CSV');✓ Correct
  • B. INSERT INTO SALES_DATA SELECT * FROM @MY_S3_STAGE FILE_FORMAT = (TYPE = 'CSV');
  • C. LOAD DATA INTO SALES_DATA FROM STAGE MY_S3_STAGE FORMAT CSV;
  • D. GET @MY_S3_STAGE/SALES_DATA INTO TABLE SALES_DATA;
Explanation

COPY INTO <table> FROM @<stage> with a FILE_FORMAT specification is the correct Snowflake syntax for bulk loading files from a stage into a table. Option B is invalid because Snowflake does not support INSERT INTO ... SELECT * FROM a stage directly. Option C uses non-existent SQL syntax ('LOAD DATA INTO') that belongs to other database systems like MySQL. Option D uses the GET command, which is used to download files from an internal stage to a local filesystem, not to load data into a table.

4. A data engineer runs the following COPY INTO command: `COPY INTO TRANSACTIONS FROM @MY_STAGE ON_ERROR = 'CONTINUE' PURGE = TRUE;` After the command completes, the engineer queries `SYSTEM$GET_LOAD_HISTORY()` and notices that 3 out of 20 files show a status of 'LOAD_FAILED'. What happened to those 3 files on the stage?

  • A. The 3 failed files were automatically moved to a Snowflake-managed quarantine stage for later inspection.
  • B. The 3 failed files were deleted from the stage because PURGE = TRUE removes all files regardless of load success or failure.
  • C. The 3 failed files remain on the stage because PURGE = TRUE only removes successfully loaded files.✓ Correct
  • D. The COPY command rolled back entirely because 3 files failed, and no files were purged since the transaction was not committed.
Explanation

When PURGE = TRUE is set, Snowflake automatically removes files from the stage after they are successfully loaded. Files that fail to load (status = LOAD_FAILED) are NOT purged and remain on the stage, allowing the engineer to investigate and retry. Option A is incorrect; Snowflake does not have a built-in quarantine stage concept — failed files simply remain in place. Option B is a very common misconception about PURGE = TRUE; it does not remove all files indiscriminately, only successfully loaded ones. Option D is incorrect because ON_ERROR = 'CONTINUE' tells Snowflake to skip files with errors and continue loading the rest — there is no rollback. Successfully loaded files from the 17 successful files would be committed and purged.

5. An analytics team wants to query data files stored in an external Azure Blob Storage container directly from Snowflake without loading the data into a standard table. They also need the query results to reflect newly arrived files automatically after running a single refresh command. Which Snowflake feature BEST satisfies this requirement?

  • A. A named internal stage with a directory table enabled and periodic ALTER STAGE … REFRESH calls.
  • B. An external table with its metadata refreshed using ALTER EXTERNAL TABLE … REFRESH.✓ Correct
  • C. A transient table populated nightly via a COPY INTO task.
  • D. A materialized view over a named external stage.
Explanation

Option B is correct: External tables allow Snowflake to query data files stored in external cloud storage without loading them. Running ALTER EXTERNAL TABLE … REFRESH updates the external table's metadata to reflect newly arrived or removed files, satisfying the auto-discovery requirement. Option A is wrong because a directory table on a named internal stage still requires files to be PUT into an internal (Snowflake-managed) stage, not external storage. Option C is wrong because loading data into a transient table defeats the requirement of querying data in place without loading. Option D is wrong because Snowflake does not support materialized views directly over external stages; materialized views are built on top of tables or regular views.

20 more questions in this domain

Practice the full bank with instant grading, flashcards, and a timed mock exam.

Start practicing free