SnowPro Associate: Platform Question 73
Single answer○ SQLA data engineer loads a CSV file into a Snowflake table named SALES_RAW with the following columns in order: ORDER_ID NUMBER, ORDER_TS TIMESTAMP_NTZ, AMOUNT NUMBER(10,2). After loading, business users report that some rows have ORDER_ID values populated, but ORDER_TS and AMOUNT are NULL even though the source file contains values in those fields. The engineer suspects the issue is caused by the INSERT statement used during a transformation step:
INSERT INTO SALES_RAW (ORDER_ID, ORDER_TS, AMOUNT) SELECT $1, $3, $2 FROM @mystage/sales.csv (FILE_FORMAT => my_csv_format);
Which statement best explains the problem and the correct fix?
- A
The SELECT list maps staged file columns by position, so ORDER_TS and AMOUNT are swapped. The fix is to select $1, $2, $3 in the correct order for the target columns.
- B
Snowflake requires explicit CAST functions for all staged file columns, so the load fails silently for ORDER_TS and AMOUNT. The fix is to CAST every column in the SELECT list.
- C
The issue occurs because Snowflake does not support selecting directly from staged CSV files in an INSERT...SELECT statement. The fix is to load the file into a temporary table first.
- D
The NULL values are caused by the target column list in the INSERT statement. Removing the target column list allows Snowflake to auto-match source fields to target columns by name.
Show answer and explanation
Correct answer: A
Explanation
This question tests practical SQL skills in Snowflake related to loading and transforming staged file data. When selecting from a staged CSV file, Snowflake exposes fields as positional columns such as $1, $2, $3. In an INSERT INTO ... (target columns) SELECT ... statement, the SELECT output is mapped positionally to the target column list, not by semantic meaning or file header names. In this scenario, ORDER_TS and AMOUNT are reversed because the engineer used SELECT $1, $3, $2 instead of $1, $2, $3. Snowflake documentation for querying staged files and loading data shows the use of positional references and emphasizes correct column mapping during transformations. Best practice is to specify the target column list and ensure the SELECT expressions are in the intended order, adding explicit CASTs only when needed for format handling or data quality.
- A. Correct.
Correct. In Snowflake, when querying staged files, positional references such as $1, $2, and $3 refer to the first, second, and third fields in each file row. In the INSERT statement, the target columns are listed as (ORDER_ID, ORDER_TS, AMOUNT), but the SELECT returns $1, $3, $2. That means the second and third source fields are reversed during insertion. If the swapped values cannot be converted to the target data types, Snowflake can produce NULLs depending on the transformation logic and file contents. The proper fix is to align the source field order with the target column order: $1, $2, $3.
- B. Incorrect.
Incorrect. Snowflake does not require explicit CAST for every field when loading or selecting from staged files if values can be implicitly converted to the target column types. CAST may be useful for data cleansing or format control, but it is not the root cause here. The more direct issue is that the engineer selected the second and third file columns in the wrong order.
- C. Incorrect.
Incorrect. Snowflake does support querying staged files directly with SELECT statements using positional column references and a file format, and this pattern is commonly used in INSERT...SELECT transformations. Loading into a temporary table first is optional in some workflows, not required.
- D. Incorrect.
Incorrect. The target column list is a best practice because it makes the insert mapping explicit and protects against schema-order assumptions. Snowflake does not auto-match SELECT output fields to target columns by name in this context; mapping is positional between the SELECT output and the listed target columns. Removing the target column list would not fix the swapped source positions and could make the statement more error-prone.