Question

A data engineer is loading a CSV file from a named internal stage into a target table. The source file has columns in the order (id, raw_timestamp, amount), but the target table columns are (amount, event_id, event_time), and the team wants to cast raw_timestamp to a TIMESTAMP and multiply amount by 100 during the load. Which approach lets them accomplish this in a single COPY INTO statement?

Accepted Answer

Use a COPY INTO statement with a SELECT query referencing the staged file (e.g., $1, $2, $3) to reorder, cast, and transform the columns before loading.

Answer

COPY INTO does not support transformations; load the raw data first, then run an UPDATE to reorder and cast the columns.

Answer

Define the transformations and column reordering entirely within the FILE FORMAT object, then run a standard COPY INTO.

Answer

Use the MATCH_BY_COLUMN_NAME option, which automatically casts and multiplies numeric columns to match the target schema.