COF-C03 domain - 18% of the exam

Data Loading, Unloading, and Connectivity

Data Loading, Unloading, and Connectivity is 18% of the COF-C03 exam. These are the objectives it covers, each with practice questions, with every answer explained.

The domain in numbers

50
Practice questions
5
Objectives
8
Easy
31
Medium
11
Hard

Where people go wrong

  • Giving one stage the scope of another

    The wrong answers offer a user stage for one table, a table stage for several tables, or a table or user stage for files that must land in the organisation's own S3 bucket. The table stage (@%table_name) serves its one table. The user stage (@~) is private and feeds any table. A named internal stage is created and granted to roles for sharing. An external stage points at customer cloud storage.

    See it in a sample question
  • Giving a COPY INTO option another option's job

    The wrong answers pick ON_ERROR, FORCE, PURGE or SIZE_LIMIT for a job that belongs to a different option. VALIDATION_MODE checks the staged files and writes no rows. FORCE ignores load metadata so unchanged files reload. PURGE deletes staged source files after a successful load and does not reset that metadata. OVERWRITE replaces existing files at an unload target, and FORCE does not.

    See it in a sample question
  • Misreading SINGLE and MAX_FILE_SIZE on unload

    The wrong answers expect MAX_FILE_SIZE, OVERWRITE or DETAILED_OUTPUT to change how many files an unload writes, or expect SINGLE = TRUE to give parallel output. SINGLE = TRUE writes one file. The default of FALSE writes several files in parallel, with MAX_FILE_SIZE capping each one. Even with SINGLE = TRUE, output above the maximum supported single-file size is split into multiple files.

    See it in a sample question
  • Picking the wrong client or connector

    The wrong answers hand a workload to a connector built for another one: the Spark connector for Kafka topics, the Python connector for Spark DataFrames or an ODBC data source, the ODBC driver for Kafka ingestion. The Kafka connector loads topic records, the Spark connector moves DataFrames, the ODBC driver serves BI tools, and a Java service uses JDBC while a Python service uses the Python connector.

    See it in a sample question
  • Using a stream or task to ingest bucket files

    The wrong answers reach for a stream, a scheduled task or an always-on warehouse to load files that land in cloud storage. A stream tracks row changes on a Snowflake table and cannot see files arriving in a bucket. A scheduled task runs on a fixed cadence whether or not files arrived. Auto-ingest Snowpipe reacts to storage event notifications and runs on serverless compute, so no warehouse sits idle.

    See it in a sample question

Objectives in this domain

Sample questions from this domain

Free sampleData Loading, Unloading, and Connectivitymedium

A data engineer needs a personal staging area to hold ad hoc files that may later be loaded into several different tables, and does not want to create any stage object or share the files with teammates. Which Snowflake stage best meets this need?

  • AA table stage, addressed as @%orders, because the implicit per-table stage is private to the user and can stage files that load into several different tables at once
  • BThe user stage, addressed as @~, because each user automatically has a private staging area that holds files for loading into any tables the user can access Correct
  • CA named internal stage, addressed as @adhoc_stage, because it is private to the creating user by default and requires no CREATE STAGE statement before files can be uploaded
  • DAn external stage, addressed as @adhoc_ext, because it gives each user a private cloud area with no setup that can feed loads into many different tables freely
Recognise the user stage, referenced as @~, as each user's implicit private area for staging files bound for multiple tables. Snowflake allocates an implicit user stage to every user, addressed with @~, which requires no creation, is private to that user, and is not tied to any single table; this makes it the right place to stage ad hoc files that may later load into several different tables.

Why A is wrong: A table stage is private but is tied to its one table, so it cannot serve files destined for several different tables, which breaks the multi-table requirement.

Why B is correct: Each Snowflake user has an implicit private user stage referenced with @~ that needs no creation and can stage files bound for many tables, matching every part of the requirement.

Why C is wrong: A named internal stage is shareable via grants and must be created explicitly, so the private-by-default and no-creation claims here are both wrong.

Why D is wrong: An external stage points at customer cloud storage and must be configured first, so it is neither per-user private nor setup-free as this option asserts.

Free sampleData Loading, Unloading, and Connectivitymedium

An engineer reran a COPY INTO statement to reload a batch of files after fixing a downstream issue, but the command reported zero rows loaded and the files were untouched. The files are unchanged and were successfully loaded by an earlier run. The engineer wants this rerun to load the same files again into the same table. Which change to the COPY INTO statement achieves a deliberate reload?

  • ASet ON_ERROR to CONTINUE so the rerun ignores the earlier successful load and processes the previously loaded files again instead of treating them as already done
  • BSet SIZE_LIMIT to a value larger than the batch so the command is forced to process the full set of files again regardless of what the load metadata has recorded
  • CSet the FORCE option to TRUE so COPY INTO ignores the load metadata for these files and loads them again even though they were loaded successfully before Correct
  • DSet RETURN_FAILED_ONLY to TRUE so the rerun targets the files from the prior run and loads them once more into the same target table during this operation
Identify the FORCE option as the way to deliberately reload files that load metadata would otherwise cause COPY INTO to skip. By default COPY INTO consults the table's load metadata and skips files it has already loaded, which is why an unchanged rerun loads zero rows. Setting FORCE to TRUE makes the command ignore that metadata and load the matching files again, producing the intended reload. ON_ERROR, SIZE_LIMIT, and RETURN_FAILED_ONLY control error handling, byte limits, and output filtering, none of which override deduplication.

Why A is wrong: ON_ERROR governs handling of malformed rows, not whether already loaded files are skipped, so setting CONTINUE does nothing to make Snowflake reload files recorded in load metadata.

Why B is wrong: SIZE_LIMIT caps the total bytes loaded in one operation and never overrides load-metadata deduplication, so it cannot make Snowflake reload files it has already marked as loaded.

Why C is correct: FORCE tells COPY INTO to bypass the load metadata check and load matching files regardless of prior successful loads, which is exactly how to deliberately reload unchanged files.

Why D is wrong: RETURN_FAILED_ONLY only limits which files appear in the command output and does not change skip behaviour, so unchanged previously loaded files are still ignored on the rerun.

Free sampleData Loading, Unloading, and Connectivitymedium

A team unloads a very large table to an external stage with COPY INTO and wants the export to finish quickly by letting the warehouse write several files in parallel, with each file kept under a chosen size so a downstream loader can process them concurrently. Which approach to the COPY INTO location options meets this goal?

  • ASet SINGLE = TRUE and set MAX_FILE_SIZE to the chosen per-file limit so the unload still writes one file per execution thread under that size
  • BLeave SINGLE at its default of FALSE and set MAX_FILE_SIZE to the chosen per-file limit so the unload writes multiple files in parallel under that size Correct
  • CSet SINGLE = FALSE and set MAX_FILE_SIZE to zero so the unload removes any per-file cap and splits the table into the largest possible parallel files
  • DSet SINGLE = TRUE and rely on the warehouse size alone to split the export into multiple parallel files each under the chosen per-file limit
Identify that SINGLE = FALSE with MAX_FILE_SIZE produces multiple parallel output files each kept under a chosen size. COPY INTO location defaults to SINGLE = FALSE, which lets the execution threads write files in parallel for speed. MAX_FILE_SIZE then bounds the size of each file so a large table is split into many manageable objects that a downstream tool can load concurrently. Forcing SINGLE = TRUE would collapse the output into one file and remove that parallelism.

Why A is wrong: SINGLE = TRUE forces a single output file, so it cannot produce the many parallel files the team wants, which defeats concurrent downstream processing.

Why B is correct: With SINGLE = FALSE the unload runs in parallel and MAX_FILE_SIZE caps each file, producing many appropriately sized files that a downstream loader can read concurrently.

Why C is wrong: A MAX_FILE_SIZE of zero is not how the cap is removed, and the goal is to keep each file under a chosen size rather than make files as large as possible.

Why D is wrong: Warehouse size affects throughput but cannot override SINGLE = TRUE, so the unload still writes one file rather than the parallel set required.

Free sampleData Loading, Unloading, and Connectivityeasy

An analytics team uses a third-party BI desktop tool that connects to databases through the ODBC standard and wants it to query Snowflake directly. Which Snowflake component should they install so the tool can connect?

  • AThe Snowflake Kafka connector, because the BI tool relies on it to register a standard data source name so the desktop application can open an ODBC connection and query Snowflake
  • BThe Snowflake Connector for Python, because the BI tool loads it as the standard data source so the desktop application can open an ODBC connection and run queries against Snowflake
  • CThe Snowflake ODBC driver, because the BI tool uses it as the standard data source so the desktop application can open an ODBC connection and run queries against Snowflake Correct
  • DA secure view, because the BI tool references it as the standard data source so the desktop application can open an ODBC connection and run its queries against Snowflake
Recognise the Snowflake ODBC driver as the component that lets ODBC-based BI and desktop tools connect to Snowflake. The Snowflake ODBC driver implements the ODBC connectivity standard, allowing third-party BI and desktop applications to register a Snowflake data source and submit queries through their standard ODBC interface.

Why A is wrong: The Kafka connector ingests streaming records from topics into Snowflake and offers no ODBC data source, so a BI tool cannot use it to connect over the ODBC standard.

Why B is wrong: The Python connector is a library for Python programs, not an ODBC data source a generic desktop BI tool can register, so it does not satisfy an ODBC connection requirement.

Why C is correct: The ODBC driver implements the ODBC standard so that BI and desktop tools can register a Snowflake data source and run queries, which is exactly what an ODBC-based tool requires.

Why D is wrong: A secure view restricts which data a query can see and is not a driver, so it cannot provide the ODBC connectivity the desktop tool needs to reach Snowflake.

Free sampleData Loading, Unloading, and Connectivityhard

A team must load small files that arrive in a cloud storage bucket at irregular intervals throughout the day, and they want each file ingested within roughly a minute of arrival without provisioning or paying for an idle virtual warehouse between loads. Which approach best meets this requirement?

  • ASchedule a serverless task to run COPY INTO against the stage every minute so the table is refreshed continuously from whatever files have landed since the previous run
  • BCreate a Snowpipe with auto-ingest enabled so cloud storage event notifications trigger the pipe to load each new file using Snowflake-managed serverless compute Correct
  • CRun a dedicated extra-small virtual warehouse continuously and execute COPY INTO in a tight loop so newly arrived files are picked up the instant they appear in the bucket
  • DBuild a stream on the target table and a task that copies fresh rows whenever the stream reports change data, so loading follows the arrival of files into storage
Recognise that auto-ingest Snowpipe loads new files event-driven on serverless compute, avoiding idle warehouse cost for irregular arrivals. Snowpipe with auto-ingest subscribes to cloud storage event notifications, so each new file triggers a load on Snowflake-managed serverless compute billed per second of actual work. This delivers near-real-time ingestion of irregularly arriving files without keeping a virtual warehouse running and idle between loads, which scheduled COPY and a looping warehouse cannot do as efficiently.

Why A is wrong: A minute-by-minute scheduled task does load files, but it runs even when nothing has arrived and reacts on a fixed cadence rather than to each file, so it wastes compute and is less timely than event-driven ingestion.

Why B is correct: Auto-ingest Snowpipe loads each file in response to a storage event notification on Snowflake-managed serverless compute, so files load within about a minute of arrival and there is no idle warehouse to pay for.

Why C is wrong: A warehouse left running all day bills credits for every idle minute between irregular arrivals, which directly violates the requirement to avoid paying for idle compute.

Why D is wrong: A stream tracks row changes on a Snowflake table, not files landing in a bucket, so it cannot detect external arrivals and is the wrong mechanism for ingesting files from cloud storage.

Other domains in this exam

See also the COF-C03 cert hub, the study guide, and the cheat sheet.

Examworthy is not affiliated with or endorsed by Snowflake. Original, blueprint-aligned practice material only.