A data engineer is loading data into Snowflake from an external stage. They need to transform data during the load process. Which of the following is the MOST efficient and recommended approach for pe

Question

Options

• AUse Snowpipe with a transformation script running on a separate compute instance.
• BUse a Stored Procedure to read the data from the stage, transform it, and then insert it into the
• CUse a COPY INTO statement with a SELECT statement that performs the transformations.
• DDownload the data from the stage, transform it using a Python script, and then upload it into
• ECreate a View that transforms the data after loading it into a staging table.

• A
  13% (5)
• B
  3% (1)
• C
  50% (19)
• D
  29% (11)
• E
  5% (2)

Explanation

Using COPY INTO with an inline SELECT statement is the native Snowflake mechanism for transforming data during ingestion - it executes entirely within Snowflake's engine, requires no external compute, and avoids a separate staging step, making it both performant and operationally simple. Option A (Snowpipe + external script) adds unnecessary infrastructure and latency, since Snowpipe is designed for automated micro-batch ingestion, not for orchestrating external compute transformations. Option B (Stored Procedure) could work but is inefficient - it introduces procedural overhead and is not the recommended pattern when a declarative SQL approach exists. Options D and E both violate the "transform during load" requirement: D moves data outside Snowflake entirely (slow, costly egress), and E loads raw data first then transforms it, which wastes storage and adds a pipeline step. Memory tip: Think "COPY INTO = load + transform in one shot" - the SELECT inside the COPY INTO acts like a pipeline filter, so if you can write the transformation as SQL, you never need to touch the data outside Snowflake.

No community discussion yet for this question.