A data team is designing a data pipeline that loads data from S3 into Snowflake. The raw data in S3 is compressed using gzip and stored in multiple files. They want to use a Snowflake virtual warehous

Question

Options

• ASpecify 'ON_ERROR = CONTINUE in the COPY INTO command to skip any files with errors.
• BUse the 'VALIDATION_MODE = RETURN ERRORS parameter to identify any issues within the
• CUtilize 'MATCH BY COLUMN NAME = CASE INSENSITIVE so that the command is robust to case
• DSpecify 'FILE FORMAT = (TYPE = CSV COMPRESSION = GZIP)' in the COPY INTO command to
• ESet the 'MAX FILE_SIZE parameter to a smaller value to force Snowflake to process files in

• B
  12% (3)
• C
  4% (1)
• D
  81% (21)
• E
  4% (1)

Explanation

Specifying FILE FORMAT = (TYPE = CSV COMPRESSION = GZIP) directly addresses the pipeline's actual requirements: the raw data is CSV-structured and gzip-compressed, so Snowflake must know both the format type and compression codec to correctly decompress and parse the files during ingestion. Without this declaration, Snowflake cannot properly interpret the binary content of the compressed files, making it the foundational optimization for this scenario.

Why the distractors are wrong:

• A (ON_ERROR = CONTINUE): This silently skips bad records rather than optimizing throughput - it hides data quality problems and has no bearing on handling compressed files.
• B (VALIDATION_MODE = RETURN_ERRORS): This runs a dry-run validation without actually loading data; it's a diagnostic tool, not a loading optimization.
• C (MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE): This resolves column name casing mismatches between source and target schema - useful in some scenarios, but irrelevant to compressed file loading.
• E (MAX_FILE_SIZE): This parameter controls file splitting during the staging (PUT) phase, not the COPY INTO loading phase, and artificially shrinking it wouldn't optimize loading of pre-existing S3 files.

Memory tip: Match your FILE FORMAT declaration to reality - if the file is gzip, say gzip. Think of it as Snowflake needing a "decoder ring": without the right format specification, it can't unlock the data at all.

No community discussion yet for this question.