Snowflake query performance is unexpectedly slower for external Parquet tables vs. internal tables

Question

When I run queries on external Parquet tables in Snowflake, the queries are orders of magnitude slower than on the same tables copied into Snowflake or with any other cloud data warehouse I have tested on the same files.

Context:

I have tables belonging to the 10TB TPC-DS dataset in Parquet format on GCS and a Snowflake account in the same region (US Central). I have loaded those tables into Snowflake using create as select. I can run TPC-DS queries(here #28) on these internal tables with excellent performance. I was also able to query those files on GCS directly with data lake engines with excellent performance, as the files are "optimally" sized and internally sorted. However, when I query the same external tables on Snowflake, the query does not seem to finish in reasonable time (>4 minutes and counting, as opposed to 30 seconds, on the same virtual warehouse). Looking at the query profile, it seems that the number of records read in the table scans keeps growing indefinitely, resulting in a proportional amount of spilling to disk.

The table happens to be partitioned but it those not matter on the query of interest (which I tested with other engines).

What I would expect:

Assuming proper data "formatting", I would expect no major performance degradation compared to internal tables, as the setup is technically the same - data stored in columnar format in cloud object store - and as it is advertised as such by Snowflake. For example I saw no performance degradation with BigQuery on the exact same experiment.

Other than double checking my setup, I see don't see many things to try...

This is what the "in progress" part of the plan looks like 4 minutes into execution on the external table. All other operators are at 0% progress. You can see external bytes scanned=bytes spilled and 26G!! rows are produced. And this is what it looked like on a finished execution on the internal table executed in ~20 seconds. You can see that the left-most table scan should produce 1.4G rows but had produced 23G rows with the external table.

This is a sample of the DDL I used (I also tested without defining the partitioning column):

create or replace external table tpc_db.tpc_ds.store_sales (
    ss_sold_date_sk bigint as 
      cast(split_part(split_part(metadata$filename, '/', 4), '=', 2) as bigint)
    ,
    ss_sold_time_sk bigint as (value:ss_sold_time_sk::bigint),
    ss_item_sk bigint as (value:ss_item_sk::bigint),
    ss_customer_sk bigint as (value:ss_customer_sk::bigint),
    ss_cdemo_sk bigint as (value:ss_cdemo_sk::bigint),
    ss_hdemo_sk bigint as (value:ss_hdemo_sk::bigint),
    ss_addr_sk bigint as (value:ss_addr_sk::bigint),
    ss_store_sk bigint as (value:ss_store_sk::bigint),
    ss_promo_sk bigint as (value:ss_promo_sk::bigint),
    ss_ticket_number bigint as (value:ss_ticket_number::bigint),
    ss_quantity bigint as (value:ss_quantity::bigint),
    ss_wholesale_cost double as (value:ss_wholesale_cost::double),
    ss_list_price double as (value:ss_list_price::double),
    ss_sales_price double as (value:ss_sales_price::double),
    ss_ext_discount_amt double as (value:ss_ext_discount_amt::double),
    ss_ext_sales_price double as (value:ss_ext_sales_price::double),
    ss_ext_wholesale_cost double as (value:ss_ext_wholesale_cost::double),
    ss_ext_list_price double as (value:ss_ext_list_price::double),
    ss_ext_tax double as (value:ss_ext_tax::double),
    ss_coupon_amt double as (value:ss_coupon_amt::double),
    ss_net_paid double as (value:ss_net_paid::double),
    ss_net_paid_inc_tax double as (value:ss_net_paid_inc_tax::double),
    ss_net_profit double as (value:ss_net_profit::double)
)
    partition by (ss_sold_date_sk)
    with location = @tpc_ds/store_sales/
    file_format = (type = parquet)
    auto_refresh = false
    pattern = '.*sales.*[.]parquet';

Answer 1

Probably Snowflake plan assumes it must read every parquet file because it cannot tell beforehand if the files are sorted, number of unique values, nulls, minimum and maximum values for each column, etc.

This information is stored as an optional field in Parquet, but you'll need to read the parquet metadata first to find out.

When Snowflake uses internal tables, it has full control about storage, has information about indexes (if any), column stats, and how to optimize a query both from a logical and physical perspective.

Answer 2

I don't know how your query looks but there is also a small chance that you suffer from a known issue where Snowflake interprets a function in the partition filter as dynamic and thus runs over all data, see details in Joao Marques blog on Medium: Using Snowflake External Tables? You must read this!.

Example of how not to do it from the blog

SELECT COUNT(*) 
FROM EXTERNAL_TABLE 
WHERE PARTITION_KEY = dateadd(day, -1, current_date)

Example of how to do it from the blog

SET my_var = (select dateadd(day, -1, current_date));
SELECT COUNT(*) 
FROM EXTERNAL_TABLE 
WHERE PARTITION_KEY = $my_var

All credits to the blog author, I have merely stumbled across this issue myself and found his blog.