Data Movement Study Guide — DEA-C02

Data Movement

This domain is the largest topic area on the SnowPro Advanced: Data Engineer (DEA-C02) exam under the current (carried-over, unverified) blueprint weighting. It tests your ability to design, build, and troubleshoot production-grade data pipelines entirely within Snowflake's ecosystem. Expect questions that require you to choose between competing ingestion patterns (Snowpipe vs. Snowpipe Streaming vs. COPY INTO), orchestrate transformations (Tasks vs. Dynamic Tables), handle semi-structured data, and configure external connectivity. Precision matters: the exam distinguishes exact defaults, limits, and behavioral differences between similar-sounding features.

COPY INTO

COPY INTO <table> is Snowflake's batch-load command. It reads files from a stage (internal or external) and loads them into a table. It is executed on a Virtual Warehouse (not serverless by default).

Key File Formats

Critical COPY Options

• PURGE = TRUE: deletes source files from stage after successful load.
• FORCE = TRUE: reloads files already in the load history (64-day default window). Use with care.
• ON_ERROR: CONTINUE (skip bad rows), SKIP_FILE (skip entire file), SKIP_FILE_<n> (skip file if error count ≥ n), ABORT_STATEMENT (default — rolls back entire load).
• VALIDATION_MODE: RETURN_n_ROWS, RETURN_ERRORS, RETURN_ALL_ERRORS — dry-run without writing data.
• MATCH_BY_COLUMN_NAME: for Parquet/ORC/Avro, maps file columns to table columns by name rather than position.
• FILE_FORMAT inline or named: named file formats are reusable objects; inline overrides per-statement.
• TRUNCATECOLUMNS = TRUE: silently truncates strings exceeding column length instead of erroring.
• COPY_OPTIONS ( PURGE = TRUE ) vs FILE_FORMAT ( ... ): these are distinct clauses, not interchangeable.

Load History & Deduplication

COPY INTO tracks loaded files per table for 64 days. Re-running COPY INTO on the same file within 64 days is a no-op unless FORCE = TRUE. This is not a deduplication guarantee at the row level — duplicate rows within files still load.

Common Traps

• ON_ERROR = ABORT_STATEMENT is the default — students assume CONTINUE is default.
• VALIDATION_MODE does not load data; using it with PURGE does not delete files.
• COPY INTO requires a running warehouse; it is not serverless.
• MATCH_BY_COLUMN_NAME is not available for CSV — CSV uses positional mapping.
• Files larger than 5 GB in S3 may require multipart upload; Snowflake recommends splitting files to 100–250 MB compressed for optimal parallelism.

Snowpipe

Snowpipe provides near-real-time, serverless, file-based ingestion. It wraps a COPY INTO statement inside a pipe object and triggers automatically or via REST API.

Auto-Ingest vs. REST API

Architecture

Snowpipe auto-ingest data flow from S3 to Snowflake table

Key Snowpipe Facts

• Pipe object contains a frozen COPY INTO statement — the file format and target table are fixed at pipe creation.
• Use ALTER PIPE ... REFRESH to backfill files already in the stage that predate the pipe or were missed.
• SYSTEM$PIPE_STATUS() returns pipe state: RUNNING, PAUSED, STOPPED_CLONED, etc.
• Billing: Snowpipe uses Snowflake-managed compute billed per-file at a credit rate approximately 0.06 credits per 1,000 files loaded (subject to change; exam tests the concept of serverless billing, not exact rates).
• Snowpipe does not guarantee exactly-once delivery — it provides at-least-once semantics; downstream deduplication is the developer's responsibility.
• ERROR_INTEGRATION on a pipe sends load error notifications to cloud messaging (SNS, Azure Event Grid, GCS Pub/Sub).

Common Traps

• Snowpipe uses a different load history table than COPY INTO: INFORMATION_SCHEMA.COPY_HISTORY (function) and SNOWFLAKE.ACCOUNT_USAGE.COPY_HISTORY (view with latency).
• Auto-ingest SQS queue is owned by Snowflake for S3 — you must grant S3 permission to publish to Snowflake's SQS ARN, not create your own queue.
• Changing a pipe's COPY statement requires recreating the pipe (CREATE OR REPLACE PIPE).
• Snowpipe does not support FORCE = TRUE.