A Snowflake table 'product catalog' exists with columns 'product id', 'product_name', and 'price'. You have a pipe-delimited file in an external stage named 's3 stage' containing product data, but…

Question

Options

• AModify the file format options to automatically replace null values with 0.
• BUse a transformation within the `COPY INTO' statement to replace null values with 0.
• CLoad the data into a staging table, then use a SQL 'UPDATE statement to replace null prices with
• DUse the 'ON_ERROR = SKIP_FILE' option to skip any file with missing prices.
• EThe 'COPY INTO' command cannot handle missing values; pre-processing is always required.

• A
  7% (2)
• B
  66% (19)
• C
  3% (1)
• D
  21% (6)
• E
  3% (1)

Explanation

Option B is correct because Snowflake's COPY INTO supports inline column transformations using COALESCE or IFF, letting you replace null values on-the-fly without any extra steps - for example: COPY INTO product_catalog FROM (SELECT $1, $2, COALESCE($3::NUMBER, 0) FROM @s3_stage).

Why the distractors are wrong:

• A - Snowflake file format options (like NULL_IF) control how strings are interpreted as nulls, but they don't replace nulls with a specific default value like 0.
• C - Loading into a staging table then running UPDATE works, but it's a two-step process requiring extra storage and compute; the question asks for the most appropriate (i.e., most efficient) approach.
• D - ON_ERROR = SKIP_FILE skips entire files on errors; it doesn't handle missing values gracefully and would cause data loss, not substitution.
• E - Factually false; COPY INTO with a SELECT transformation is specifically designed for this kind of in-flight data manipulation.

Memory tip: Think of COPY INTO with a SELECT subquery as a "transform while loading" pattern - whenever you see "replace/convert during load," reach for COPY INTO (SELECT COALESCE(...) FROM @stage). If the fix requires logic inside the load, B-style transformations are your tool; staging tables are a last resort.

No community discussion yet for this question.