Choosing how to load a given data set into Snowflake

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.

1 · Learn the must-know

• 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.

2 · Check your understanding