This behavior change is in the 2022_06 bundle. The bundle was first introduced disabled by default in the 6.29 release.
For the most up-to-date status of the bundle, as well as other release-related details, see the Behavior Change Log.
The output of the INFER_SCHEMA function now includes a new ORDER_ID column which indicates the column order in the staged files.
Currently, when you create a table with the column definitions derived from a set of staged files (using CREATE TABLE … USING TEMPLATE), the column order in the table is randomized. While table column ordering does not matter for Snowflake, this may cause confusion when you compare the file column order to the table column order. The new ORDER_ID column in the INFER_SCHEMA output can help you ensure tables created with the detected schema have the same column order.
You can retrieve the schema of any file by using INFER_SCHEMA, as the following example demonstrates. The output includes a new column ORDER_ID and is sorted by ORDER_ID automatically for the single schema scenario.
SELECT *
FROM TABLE(
INFER_SCHEMA(
LOCATION=>'@***_****_STAGE/' , FILE_FORMAT=>'FFPARQUET'
)
);
Also, you can create a table using the detected schema from staged files and sort the columns by ORDER_ID, as the following example demonstrates:
CREATE OR REPLACE TABLE BIG_TABLE
USING TEMPLATE (
SELECT ARRAY_AGG(OBJECT_CONSTRUCT(*))
WITHIN GROUP (ORDER BY ORDER_ID) // NEW
FROM TABLE( INFER_SCHEMA(LOCATION=>'@***_****_STAGE/', FILE_FORMAT=>'FFPARQUET')
)
);
DESC TABLE BIG_TABLE;
Note that sorting the columns by ORDER_ID only applies if all staged files share a single schema. If the set of staged data files includes multiple schemas with shared column names, the order represented in the ORDER_ID column might not match any single file.
Ref: 751
