Data loading, unloading, and transformation Study Guide — COF-C03

Data Loading and Unloading

This domain tests your ability to select the correct Snowflake mechanism for moving data in and out of Snowflake, configure stages and file formats, tune COPY INTO commands, set up automated ingestion with Snowpipe, and apply unloading techniques. It accounts for one of the larger shares of the SnowPro Core COF-C03 blueprint under the current estimated domain weighting — tied with two other domains. Questions mix concept recall (What is the default file format for COPY INTO?), configuration detail (Which COPY INTO option skips rows that fail parsing?), and scenario-based reasoning (Which stage type should you use for this architecture?).

Stages: Internal vs External

A stage is a named location that holds data files before loading or after unloading. Every COPY INTO command references a stage explicitly or implicitly.

Internal Stages

Snowflake manages the storage. Files are encrypted automatically. Three sub-types:

Key distinctions the exam tests:

• User and table stages cannot be altered or dropped — only named stages support ALTER STAGE and DROP STAGE.
• User stage is private; other users cannot list or read it.
• Table stage is tied to a specific table; it cannot be used to load another table.
• Only named stages support file format objects, directory tables, and scheduled refreshes.
• Files in internal stages are automatically server-side encrypted with AES-256.

External Stages

An external stage points to a cloud storage location (Amazon S3, Google Cloud Storage, Azure Blob Storage / ADLS Gen2). Snowflake reads from or writes to the cloud bucket; you supply credentials or use storage integrations.

Storage integration (CREATE STORAGE INTEGRATION) is the recommended authentication method — it uses an IAM role (AWS) or service principal (Azure/GCS), keeping credentials out of stage definitions.

Common Traps

• Assuming you can DROP or ALTER a user or table stage — you cannot.
• Confusing @~ (user stage) with @% (table stage).
• Believing external stage credentials are stored in the stage — with a storage integration the credentials are not embedded.
• Thinking files are auto-deleted from external stages after COPY — they are not unless you use PURGE = TRUE.

Worked Scenario

Requirement: A team shares a set of CSV files that multiple pipelines load into different tables. They need fine-grained access control and want to reuse a single file format definition.

Reasoning: Use a named internal stage with a file format object. Table stages are per-table; user stages are per-user; only named stages are shareable and support attached file format objects.

COPY INTO Options, File Formats, and Validation Modes

COPY INTO <table> is the primary bulk-load command. COPY INTO <location> is the unload command (covered in the Unloading section).

Bold diagram caption: COPY INTO load process flow — from stage to table

Supported File Formats

Default file format for COPY INTO is CSV when no FORMAT or FILE FORMAT is specified.

Key COPY INTO Options

Validation Mode Detail

VALIDATION_MODE performs a dry run — no data is written to the table. Use it to check files before committing a load.

• RETURN_<n>_ROWS — Return the first n rows as if they were parsed.
• RETURN_ERRORS — Return all errors from files Snowflake would process.
• RETURN_ALL_ERRORS — Return errors even from files that would be skipped due to ON_ERROR = SKIP_FILE.

Important: VALIDATION_MODE is incompatible with FORCE = TRUE and cannot be used with transformation queries in COPY INTO.

Load History and Deduplication

Snowflake tracks every file loaded via COPY INTO in load metadata for 64 days. Re-submitting the same file within 64 days is silently ignored unless FORCE = TRUE. After 64 days the metadata expires and the file can be reloaded.

Check load history via: INFORMATION_SCHEMA.LOAD_HISTORY or SNOWFLAKE.ACCOUNT_USAGE.LOAD_HISTORY.

Common Traps

• Thinking ON_ERROR = CONTINUE re-tries failed rows — it skips them permanently.
• Assuming PURGE = TRUE deletes files from external stages by default — you must explicitly set it.
• Using VALIDATION_MODE expecting data to land in the table — no rows are inserted.
• Confusing RETURN_ERRORS with RETURN_ALL_ERRORS — RETURN_ALL_ERRORS includes errors from files that SKIP_FILE would have skipped entirely.
• FORCE = TRUE bypasses deduplication but does not reset load history.