Why isn't snowflake using my materialized view

user_3016699 0
Viewed 71

Why is that when I query my base table with the following aggregate query snowflake doesn't reference my MV?


create or replace table customer_sample as (
SELECT * FROM 
"SNOWFLAKE_SAMPLE_DATA"."TPCDS_SF100TCL"."CUSTOMER");

create or replace materialized view customer_sample_mv
as 
select c_customer_sk,
sum(c_current_hdemo_sk) total_sum 
from customer_sample 
group by 1;

select c_customer_sk,
sum(c_current_hdemo_sk) total_sum 
from customer_sample 
group by 1;

Query Profile

snowflake-cloud-data-platform
2 Answers

There are lots of possible reasons e.g.

  1. The MV was still being built when you executed the query
  2. Snowflake determined it was quicker to execute the query without using the MV
  3. The user running the query didn’t have the required privileges on the MV
  4. etc.
user_136583990

In this example Snowflake is doing the right thing by skipping the materialized view.

First surprise: Scanning the materialized view is slower than just re-running the query:

select *
from customer_sample_mv
order by total_sum desc nulls last
limit 100;
-- 4.4s

vs

select *
from (
    select c_customer_sk,
    sum(c_current_hdemo_sk) total_sum 
    from customer_sample 
    group by 1
)
order by total_sum desc nulls last
limit 100;
-- 3.6s

enter image description here

So Snowflake is saving time by not choosing the materialized view.

How is this possible?

Well, turns out there are no repeated customer ids. So pre-grouping them does nothing.

select c_customer_sk, count(*) c
from customer_sample 
group by 1
having c>1
order by 2 desc
limit 10;
-- null

From the docs:

Even if a materialized view can replace the base table in a particular query, the optimizer might not use the materialized view. For example, if the base table is clustered by a field, the optimizer might choose to scan the base table (rather than the materialized view) because the optimizer can effectively prune out partitions and provide equivalent performance using the base table.

https://docs.snowflake.com/en/user-guide/views-materialized.html#how-the-query-optimizer-uses-materialized-views

user_1324380
Related