Snnowflake - COPY statement only supports simple SELECT from stage statements for import

Question

I'm trying to unload data from my table from snowflake to s3, like this:

COPY INTO "@DATABASE.SCHEMA.MY_STAGE/my_view/year=2023/month=04/day=21/my_view_data.parquet"
FROM (
    SELECT *
    FROM my_table
    WHERE to_date(event_time) = '2023-04-21'
)
HEADER = true

But I'm receiving this error: COPY statement only supports simple SELECT from stage statements for import

I don't understand why, I've created my stage with format file = parquet. I've tried before without the header = true and it worked. Has someone any thoughts? Thanks!!

Answer 1

The header = true option is only for when you're reading from a stage and copying to a table https://docs.snowflake.com/en/sql-reference/sql/copy-into-location.

In this case, you're reading from a table and copying to a stage. This has different options: https://docs.snowflake.com/en/sql-reference/sql/copy-into-table.

If your source file(s) have a header, then you would use a file format to handle that. In this case since it's parquet, you wouldn't need that option.