You can unload (copy) data from a Snowflake database table to an external stage using the copy into command. The unload operation enables various use cases to transform data for output to a data lake. The data can also be partitioned during the unload operation.

The following example unloads data from a Snowflake Sample Data table to ECS and partitions the data on one of the table data columns. It copies table data from the call_center table in the Snowflake_Sample_Data database to an external stage. This example also partitions the data on the external table by the first column in the table.

Note: This example assumes that an external stage named ext_unload has been created.

copy into @ext_unload/call_center/

from SNOWFLAKE_SAMPLE_DATA.TPCDS_SF10TCL.CALL_CENTER

partition by to_varchar(CC_CALL_CENTER_SK)

file_format = (TYPE = 'parquet')

max_file_size = 3200

header=true;

Once the data has been unloaded, the external table can be created, as follows.

Note: This example includes the column partition to be created during the unload operation.

create or replace external table call_center(

    CC_CALL_CENTER_SK number(38,0) as to_number(split_part(metadata$filename, '/', 4)),

    CC_CALL_CENTER_ID varchar(16) as (value:CC_CALL_CENTER_ID::varchar(16))

)

 partition by (CC_CALL_CENTER_SK)

 location=@ext_unload/call_center/

 auto_refresh = false

 file_format = (type = parquet);

Note: The external table in this example includes a subset of the columns for illustration purposes.

Now, when we query the external table using a filter, Snowflake only has to scan 1 of the 54 partitions on the external stage.

select * from call_center where CC_CALL_CENTER_SK = 53;

{

  "CC_CALL_CENTER_ID": "AAAAAAAAEDAAAAAA",

  "CC_CALL_CENTER_SK": 53,

  "CC_CITY": "Wildwood",

  "CC_CLASS": "large",

  "CC_COMPANY": 5,

  "CC_COMPANY_NAME": "anti",

  "CC_COUNTRY": "United States",

  "CC_COUNTY": "Dauphin County",

  "CC_DIVISION": 2,

  "CC_DIVISION_NAME": "able",

  "CC_EMPLOYEES": 384374949,

  "CC_GMT_OFFSET": -5,

  "CC_HOURS": "8AM-4PM",

  "CC_MANAGER": "Javier Moten",

  "CC_MARKET_MANAGER": "Antonio Rockwell",

  "CC_MKT_CLASS": "Immense plants impose practices. Demands m",

  "CC_MKT_DESC": "Important, high chairs sleep jointly users. Old, dif",

  "CC_MKT_ID": 3,

  "CC_NAME": "North Midwest_4",

  "CC_OPEN_DATE_SK": 2451059,

  "CC_REC_END_DATE": "2001-12-31",

  "CC_REC_START_DATE": "2000-01-02",

  "CC_SQ_FT": -1905098178,

  "CC_STATE": "PA",

  "CC_STREET_NAME": "Lincoln ",

  "CC_STREET_NUMBER": "361",

  "CC_STREET_TYPE": "Ct.",

  "CC_SUITE_NUMBER": "Suite X",

  "CC_TAX_PERCENTAGE": 0,

  "CC_ZIP": "16871"

}