Skip to content

How to Filter and Aggregate Efficiently

The 15m views are views, not tables, and they materialise a dense 96-rows × N-days × N-combos grid at query time. Performance depends entirely on giving Snowflake a tight date filter so the underlying sat_period_15m scan and dim_combos cross-join are bounded.

The rule

Always filter by delivery_date_utc (or begin_utc) with a literal range.

Without it, every customer-facing 15m view scans the full dim_period range (2020-01-01 onward) cross-joined against every combo the data has ever seen — many minutes of compute, multi-GB scans.

Good patterns

Bounded by date

where delivery_date_utc between '2026-04-01' and '2026-04-30'

Bounded by timestamp

where begin_utc >= '2026-04-01' and begin_utc < '2026-05-01'

Both push predicates into the underlying sat_period_15m scan.

Rolling window from current date

where delivery_date_utc between dateadd('day', -7, current_date) and current_date

Snowflake evaluates current_date once per query, so this still prunes correctly.

Bad patterns

No date filter

-- BAD: scans every day from 2020-01-01
select * from fct_volume_pwr_15m

Filter on a derived column

-- BAD: predicate doesn't push down
where year(begin_utc) = 2026

Use delivery_date_utc between '2026-01-01' and '2026-12-31' instead.

CET as the only filter

-- RISKY: CET-bounded filters can be off-by-one across DST
where begin_cet between '2026-04-01 00:00 +0200' and '2026-04-30 23:59 +0200'

Filter on delivery_date_utc (or begin_utc), display in CET. See Time zones and DST.

Reducing dense-grid size

The dense grid the view computes is periods × combos. Two ways to keep it small:

  1. Tighten the date range — the primary lever.
  2. Filter combos in a CTE before the dense projection — works best on the deal-grain views.
-- Filter deals first, then ask for the dense grid for just those
with deals_in_scope as (
    select dim_deal_attribute_id
    from dim_deal_attribute
    where portfolio = 'PWR_ST_PF_Netting'
)
select f.*
from fct_volume_pwr_15m_deal f
join deals_in_scope d using (dim_deal_attribute_id)
where f.delivery_date_utc between '2026-04-01' and '2026-04-30'

Monthly / quarterly bucketing

Roll up after the date filter, not before:

select
    date_trunc('month', delivery_date_utc) as delivery_month,
    business_case,
    grid,
    sum(volume_buy_mw)  as buy_mw,
    sum(volume_sell_mw) as sell_mw
from fct_volume_pwr_15m
where delivery_date_utc between '2026-01-01' and '2026-12-31'
group by 1, 2, 3

For quarterly windows, see Migration how-to: Pattern D.

Warehouse sizing

For typical date ranges (≤ 1 month, single business case or portfolio filter), the 15m views run on NUMERA_PROD_REPORTING (Snowflake X-Small) in a few seconds. For full-month, full-business-case-set queries against fct_volume_pwr_15m_deal, step up to Small or Medium.

If a query is taking longer than expected:

  1. Confirm there's a literal date range in the WHERE clause.
  2. Check the query profile for a full dim_period scan — that's the symptom of a missing filter.
  3. Reduce the date range and iterate.

Why this matters (in one paragraph)

The customer view computes dim_period × dim_combos and left-joins the agg. dim_period has ~150k rows for the full range; dim_combos for the unfiltered short-term desk variant has thousands of distinct combos. Without a date predicate, that's a billions-row intermediate before the left join even runs. With a 30-day date predicate, it's ~2,880 periods × O(1000) combos = ~3 M rows — comfortable for Snowflake.

The intermediate core_*__real is materialised and small (one row per (combo, date) with arrays), so the predicate flows through cleanly when present.