demo1.sql

alter session set USE_CACHED_RESULT = FALSE;
alter session set query_tag = 'resultcache';
use warehouse benchmark;
alter warehouse benchmark set warehouse_size = XSMALL;

select c.*
from cost.altimate.store_sales_price sp
join cost.altimate.customer c
on sp.ss_customer_sk = c.c_customer_sk
where c_birth_country like '%REP%'
limit 5000000;

-- 12 seconds

alter session set USE_CACHED_RESULT = TRUE;

select c.*
from cost.altimate.store_sales_price sp
join cost.altimate.customer c
on sp.ss_customer_sk = c.c_customer_sk
where c_birth_country like '%REP%'
limit 5000000;

--  99 ms

-- adding non deterministic
select c.*
, current_timestamp() 
from cost.altimate.store_sales_price sp
join cost.altimate.customer c
on sp.ss_customer_sk = c.c_customer_sk
where c_birth_country like '%REP%'
limit 5000000;

-- 7 seconds