View

• views run as owning role
• only secure views can be shared
• TEMPORARY views are session scoped

MATERIALIZED VIEW

• limitations: can query only one table, no joins, only selected aggregated functions, no windowing functions, no UDFs
• automatically maintained (charged for the maintenance)
• no time-travel
• supports clustering
• no explicit permission needed if implicitly used by optimizer
• always returns up-to-date data: Snowflake may combine current state with pending deltas to derive current data on the fly
• MVs store aggregates per FDN instead of aggregating across table (Ref Darren Gardner office hour)
• can create MV on shared data
• BP: ideal when MV:
• contain small fraction of rows/columns
• expensive to compute (e.g. semi-structure data processing)
• external table
• underlying table doesn't change much

Semantic Views

• identify BI data model by classifying data as dimension or metric
• enables cortex analyst to work without having to create model YAML file
• securable and queryable; e.g.

select *
from semantic_view (
    impression_sem_vw
    dimensions
      impression.event_date,
      impression.image_type
    metrics
      impression.ctr
  );

• semantic views contain several sections
• tables: list of tables and view; have primary keys
• relationships: foreign key relationships between tables
• facts: columns that are reported as is
• dimensions: potentially rollup columns
• metrics: measures that can be aggregated
• example:

create semantic view sem_vw
  tables (
    customer primary key (c_cust_sk),
    date as date_dim primary key (d_date_sk),
    item primary key (i_item_sk),
    storesales as store_sales primary key (ss_sold_date_sk, ss_item_sk, ss_item_sk)
  )
  relationships (
    sales_to_cust as storesales(ss_cust_sk)      references customer(c_cust_sk),
    sales_to_date as storesales(ss_sold_date_sk) references date(d_date_sk),
    sales_to_item as storesales(ss_item_sk)      references item(id_item_sk),
  )
  facts (
    item.cost as i_wholesale_cost,
    item.price as i_current_price,
    store.tax_rate as s_tax_pct,
  )
  dimensions (
    customer.birthyear as c_birth_year,
    customer.country as c_birth_country
  )
  metrics (
    storesales.total_cost as sum(item.cost),
    storesales.total_qty as sum(ss_quantity) with synonyms = ('total sales quantity', 'total sales amount')
  )