Question

A data engineer must unload a large fact table to an external S3 stage. The downstream analytics team requires that the exported files be organized into subfolders by region and load date (for example, region=US/dt=2024-01-15/) so their partition-aware query engine can prune efficiently. Which approach in a single COPY INTO <location> statement produces this folder structure?

Accepted Answer

Use the PARTITION BY clause with an expression that builds the folder path from the region and date columns

Answer

Set SINGLE = TRUE so Snowflake writes one file per partition automatically

Answer

Specify MAX_FILE_SIZE with a small value so each region's rows land in a separate file

Answer

Add the OVERWRITE = TRUE option to force Snowflake to create date-based directories