Study plan

Start hereStudy noteData Movement

Choosing how to load a given data set into Snowflake

Objective 1 of 7 in Data Movement, the first section in the guide. Not tried yet. See all 5 exam sections

What this note covers

Loading data into Snowflake typically means moving files from a stage (internal or external) into tables using bulk loading (COPY INTO) or continuous loading (Snowpipe), or ingesting data directly from applications via connectors and drivers. The exam objective focuses on choosing the right load method, stage type, file format, and handling errors/duplicates correctly for a given dataset scenario.

The facts the exam tests

• 1COPY INTO <table> loads staged files in bulk and uses a load metadata cache (~64 days) to prevent reloading the same file by default, unless FORCE=TRUE is specified.
• 2Snowpipe uses COPY INTO under the hood but is designed for continuous, near-real-time loading triggered by cloud provider event notifications (or REST API calls) rather than manual/batch execution.
• 3File formats (CSV, JSON, Parquet, Avro, ORC, XML) can be defined inline or as a named FILE FORMAT object, and must match the actual file structure or loads will fail or silently mis-parse.
• 4The `ON_ERROR` copy option (e.g., CONTINUE, `SKIP_FILE`, `ABORT_STATEMENT`) controls how COPY INTO handles malformed records, and `VALIDATION_MODE` lets you test a load without committing data.
• 5Semi-structured data (JSON, Avro, ORC, Parquet) can be loaded directly into a single VARIANT column without flattening, then queried/flattened later using dot notation or FLATTEN.
• 6Stages (user, table, named internal/external) and the PUT command are used to get local files into Snowflake-accessible storage before COPY INTO can load them, except when loading directly from an external stage/location.