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')
      )