Retaining schema when unloading Snowflake table to s3 in parquet

Question

My table on Snowflake contains a field created as INT and defaults to NUMBER(38,0) as Snowflake data type.

When I unload this table to s3 in parquet format with COPY command, I expect to retain the whole schema including the precision of this field. However, the resulting parquet has INT32 Decimal(precision=9, scale=0).

In Snowflake documentation, it is mentioned that

/* To retain the table schema in the output file, use a simple SELECT statement (e.g. SELECT * FROM cities). */

However, my query below does not keep the precision intact.

COPY INTO @staging.dl_stage/prediction/vehicle/export_date=20200226/file 
FROM (
    SELECT * FROM  snd_staging.PREDICTION.vehicle
)
  FILE_FORMAT=(type='parquet' COMPRESSION = AUTO)
  HEADER = TRUE
  OVERWRITE = TRUE
  SINGLE = False
  MAX_FILE_SIZE=256000000;

Is it possible to force keeping Snowflake data type precision intact?

Sertac Sertac · Accepted Answer · 2020-02-27T15:50:27

I eventually solved this by running this:

alter session set ENABLE_UNLOAD_PHYSICAL_TYPE_OPTIMIZATION = false;

Retaining schema when unloading Snowflake table to s3 in parquet

1 Answers