How can I load data into snowflake from S3 whilst specifying data types

Question

I'm aware that its possible to load data from files in S3 (e.g. csv, parquet or json) into snowflake by creating an external stage with file format type csv and then loading it into a table with 1 column of type VARIANT. But this needs some manual step to cast this data into the correct types to create a view which can be used for analysis.

Is there a way to automate this loading process from S3 so the table column data types is either inferred from the CSV file or specified elsewhere by some other means? (similar to how a table can be created in Google BigQuery from csv files in GCS with inferred table schema)

Answer 1

As of today, the single Variant column solution you are adopting is the closest you can get with Snowflake out-of-the-box tools to achieve your goal which, as I understand from your question, is to let the loading process infer the source file structure.

In fact, the COPY command needs to know the structure of the expected file that it is going to load data from, through FILE_FORMAT.

More details: https://docs.snowflake.com/en/user-guide/data-load-s3-copy.html#loading-your-data