Data Loading, Unloading and Connectivity — Snowflake SnowPro Core practice questions

A team creates an external stage over cloud object storage. Compared with an internal stage, the external stage MOST notably:

1. Encrypts data automatically using a Snowflake-managed key only
2. Requires files to be uploaded through the PUT command first
3. Leaves files under the customer's own storage control
4. Restricts loads to a single file format defined at creation time

Answer: C — Leaves files under the customer's own storage control

C) Correct — an external stage points at buckets or containers the customer owns and pays for, so file lifecycle and access remain theirs. A) Internal stages are the ones always encrypted with Snowflake-managed keys; external storage encryption is configured by the customer. B) PUT targets internal stages only; external files are written by the customer's own tooling. D) A default file format can be set but is overridable per COPY statement in either stage type.

A load of a few hundred thousand rows onto an X-Large warehouse runs no faster than it did on a Medium. The FIRST thing to check is:

1. Whether the target table has a clustering key that is being maintained
2. Whether result caching is interfering with repeated load executions
3. Whether the data is arriving as a small number of large files
4. Whether the file format specifies a compression codec explicitly

Answer: C — Whether the data is arriving as a small number of large files

C) Correct — load parallelism is bounded by file count, so a handful of files leaves added compute idle regardless of warehouse size. A) Clustering maintenance is background work and is not what caps load parallelism here. B) Result cache does not apply to loads at all. D) Compression affects transfer time modestly but does not explain zero benefit from doubling compute.

A team must reload a set of files that COPY INTO now skips, without renaming or re-staging them. The MOST direct option is:

1. Set ON_ERROR = CONTINUE so skipped files are processed anyway
2. Set PURGE = TRUE so the metadata entries are cleared on the next run
3. Set FORCE = TRUE so files load again regardless of load metadata
4. Set SIZE_LIMIT high enough that the metadata check is bypassed

Answer: C — Set FORCE = TRUE so files load again regardless of load metadata

C) Correct — FORCE = TRUE tells COPY INTO to ignore load metadata and load every matching file, at the cost of possible duplicates. A) ON_ERROR governs how row-level errors are handled, not whether already-loaded files are revisited. B) PURGE deletes staged files after loading; it does not reset the metadata for a reload. D) SIZE_LIMIT caps the bytes loaded in one statement and has no bearing on the metadata check.

A nightly load of 400 files must not abort when a handful of malformed records appear, but any file with bad data should be quarantined whole for manual review. The BEST ON_ERROR setting is:

1. CONTINUE, so good rows load and bad rows are simply reported
2. ABORT_STATEMENT, so the entire batch rolls back for investigation
3. SKIP_FILE, so a file with any error is left entirely unloaded
4. SKIP_FILE_10%, so only heavily corrupted files are excluded

Answer: C — SKIP_FILE, so a file with any error is left entirely unloaded

C) Correct — SKIP_FILE excludes any file containing an error while letting the remaining files load, which is exactly file-level quarantine. A) CONTINUE loads the clean rows from a bad file, mixing partial data into the table. B) ABORT_STATEMENT is the default for bulk loads and would stop the whole run, which the requirement forbids. D) The percentage variant tolerates some errors inside a file, so partially bad files would still load.

A user runs COPY INTO from a user stage without specifying anything else. The stage reference that identifies a user stage is:

1. @~ , which is scoped to one user and not shareable
2. @% followed by the table name, scoped to one table's loads
3. @ followed by a stage name created with CREATE STAGE
4. @$ , which resolves to the current session's default stage

Answer: A — @~ , which is scoped to one user and not shareable

A) Correct — @~ denotes the user stage, allocated per user and accessible only to that user. B) @%table is the table stage, tied to a single table. C) A bare @name refers to a named internal or external stage, which is a different object. D) There is no @$ stage prefix in Snowflake.

A file format object is created with TYPE = CSV, and a later COPY INTO references it but also specifies a different FIELD_DELIMITER inline. The result is that:

1. The COPY statement fails because the two definitions conflict openly
2. The inline option overrides the named format for that run
3. The named format wins, since stored objects take precedence at runtime
4. Both delimiters are accepted, and the parser tries each in turn per row

Answer: B — The inline option overrides the named format for that run

B) Correct — options given directly in the COPY statement override the corresponding values from the referenced file format for that execution only. A) Mixing a named format with inline overrides is supported, not an error. C) This reverses the precedence rule; stored defaults are the fallback, not the winner. D) The parser uses one resolved delimiter; it does not attempt alternatives.

For continuous low-latency ingestion of files from S3 to Snowflake, use:

1. COPY INTO statements run manually as each new file arrives
2. Snowpipe ingestion triggered by cloud event notifications
3. Database replication into a secondary Snowflake account
4. AWS Database Migration Service writing into tables

Answer: B — Snowpipe ingestion triggered by cloud event notifications

B) Correct — Snowpipe auto-ingests new files when the storage event notification fires, giving serverless near-real-time loading. A) Manual runs are not continuous. C) Replication copies databases between accounts. D) DMS is an AWS database migration service, not a Snowflake loader.

An engineer wants to load only the files under a stage path whose names match a date pattern, without listing them individually. The BEST COPY INTO option is:

1. FILES, listing each qualifying file name explicitly in the statement
2. PATTERN, supplying a regular expression matched against file paths
3. SUBPATH, which filters by directory depth beneath the stage root
4. MATCH_BY_COLUMN_NAME, which maps files to targets by naming rules

Answer: B — PATTERN, supplying a regular expression matched against file paths

B) Correct — PATTERN takes a regular expression evaluated against staged file paths, which is exactly a name-based filter. A) FILES requires enumerating names, which the requirement rules out. C) There is no SUBPATH option; path scoping is done in the stage reference itself. D) That option maps source fields to table columns and has nothing to do with file selection.

A materialized view is created over an external table PRIMARILY to:

1. Allow updates to be pushed back into the external source files on write
2. Enable Time Travel retention on data that lives outside Snowflake
3. Improve query performance by materializing results inside Snowflake
4. Convert the external table into a native Iceberg-managed table

Answer: C — Improve query performance by materializing results inside Snowflake

C) Correct — this is a recognized pattern: the materialized view caches results in Snowflake storage so repeated queries avoid scanning remote files. A) External data is never written back through a view. B) Materializing does not retroactively give the external table Time Travel semantics. D) Materialized views do not convert table types or catalogs.

Which is TRUE about Snowflake's pipe object?

1. A pipe executes Snowpark Python handlers when files arrive
2. A pipe encrypts staged files before Snowflake loads them
3. A pipe replicates a loaded database to a secondary account
4. A pipe is a named object wrapping a COPY INTO statement

Answer: D — A pipe is a named object wrapping a COPY INTO statement

D) Correct — CREATE PIPE binds a COPY INTO statement to a stage so Snowpipe can ingest arriving files. A) Pipes run a COPY, not Snowpark handlers. B) Stage encryption is handled independently of the pipe. C) Replication is a separate feature set.

Bulk load methods?

COPY INTO <table> from internal/external stage. Snowpipe for continuous. Snowflake Streaming for low-latency.

What does COPY INTO <location> do, and what does the SINGLE option change?

It unloads query or table results from Snowflake to an internal or external stage, by default writing multiple compressed files in parallel with a filename prefix. SINGLE = TRUE forces one output file, which removes parallelism and is limited by the maximum file size.

Connectors?

SnowSQL CLI, JDBC, ODBC, Python, Spark, Kafka, .NET, Node.js. Plus partner ETL/BI tools.

What is the general guidance for file sizing when bulk loading into Snowflake, and why?

Aim for compressed files of roughly 100-250 MB and split very large files, because each file is processed by a thread and many appropriately sized files let a warehouse parallelize the load. A single huge file cannot be parallelized, and thousands of tiny files add per-file overhead.