Skip to content

Database Objects

Object sub-categ Clone Replicate Share
Table Y
Table Ext N N Y
Table Event N
Table Hybrid N
Table Iceberg N N
Views Sec
UDF Sec
Pipe Pipe RG
Pipe Named Int Stg N RG
Alerts 2
Policies 3 Ent+
Stages RG
Stages Int Named N RG
Stream 1
Task 2
Tags 3 RG
Secrets N RG
Share N
  1. Streams can't be replicated if they are:
    • in the different database from the underlying table or the view,
    • on directory, external tables, or shared tables/views
  2. cloned objects will be suspended
  3. Individual governance objects can't be cloned, cloning schema will include policies
    • Ent+: Can be replicated if source and target accounts are Enterprise editions
    • RG: Supported only when part of group based replication

ALERT

  • components: condition, action, warehouse, optionally schedule (cron or frequency)
  • if schedule is omitted, the alert works runs on new data
    • has limitations on SQL statement: only one table, no CTE, no DML, no SP Calls
  • permissions needed:
  • execute alert, or execute managed alert for serverless, at account level,
  • create alert on schema; usage on database, schema and unless serverless, warehouse
  • suspended by default on create; must alter alert ... resume
  • can be run manually using execute alert
  • action is usually notification
  • functions in snowflake.alert schema:
  • scheduled_time() and last_successful_scheduled_time() if non-overlapping periods are needed
  • get_condition_query_uuid() to obtain condition query id in the action block
  • consists of IF(EXISTS(...)) THEN ... statement.
CREATE OR REPLACE ALERT my_alert
   SCHEDULE = '1 minute'
   IF( EXISTS(SELECT gauge_value FROM gauge WHERE gauge_value>200) )
   THEN INSERT INTO gauge_value_exceeded_history VALUES (current_timestamp());

POLICIES

PASSWORD

  • Min/Max Length
  • Min: Upper case, lower case, numeric, special
  • Max: age (days), retries
  • Lockout minutes

SESSION

STREAM

  • are markers that are useful in CDC processing by simulating repeatable read
  • to ensure all statements see the same stream data use BEGIN END
  • can be created on Tables (Standard, Event, External and Dynamic) and Views. Dynamic Views are not supported
  • View CDC requires enabling change tracking explicitly unless, the stream is created by the owner of the view and all underlying objects
  • only inner/cross joins, filter, projection and unions are supported
  • any non-deterministic functions (e.g CURRENT_USER(), CURRENT_TIMESTAMP()) are evaluated in the context of the query on the stream
  • for views with joins, delta on each table is joined with full other tables and all deltas are joined as well
  • a stream is advanced if, and only after it is used in a DML transaction and it commits
  • data retention on the base table is extended if stream hasn't been read by max_data_extension_time_in_days
  • alter table t1 set change_tracking = true; captures all changes with metadata that can be queried using CHANGES() sub-clause of FROM clause
  • types:
  • standard: captures inserts, updates and deletes. Uses join to the original table. Rows inserted and then deleted since the last offset are not returned
    • does not capture geo-spatial data, used append-only stream for geo-spatial
  • append-only: captures only inserts; doesn't remove inserted and then deleted entries; much more performant
  • insert-only: supported on external tables only. any removed files are not tracked, but new/overwritten/appended files show up as inserts
    • append-able-storage (such as Azure AppendBlobs) may not trigger automatic refresh
  • ensures exactly once semantics
  • can be created to use time-travel information using AT|BEFORE syntax
  • Snowflake extends time-travel, except on shared tables, to at least 14 days (or more MAX_DATA_EXTENSION_TIME_IN_DAYS) if stream has not been consumed

SEQUENCE

  • Supports NextVal but not CurrVal
  • each reference to NextVal generates a unique number
  • Use NextVal in a nested query if two references to the same value are needed
  • can be specified as DEFAULT for a column in a table definition
  • Alternate to using nested query, use GetNextVal(<seq>) table function
  • Implicit lateral join with other tables specified
  • table order matters as GetNextVal joins with each row of the tables specified prior to GetNextVal call
  • Allows referring to parent sequence in a child table when ingested using multi-insert syntax

Other Schema Objects

  • FILE FORMAT: creates a file format options that can be associated with named-stage.
  • MASKING POLICY consists of single data type, 1+ conditions and 1+ masking functions
  • SESSION POLICY: controls idle session timeouts

Classes

  • Snowflake provides 3 predefined Cortex ML-Based functions
  • create a trained model object with create <class-name>(...) <obj>
  • invoke methods as <obj>!<method>
  • Anomaly detection SNOWFLAKE.ML.ANOMALY_DETECTION
  • constructor args:
    • INPUT_DATA: reference to training data
  • methods:
  • Budget SNOWFLAKE.CORE.BUDGET
  • methods: add_resource, activate, get_config, set_spending_limit ...
  • Forecast SNOWFLAKE.ML.FORECAST
  • constructor args: INPUT_DATA,
  • methods:
    • FORECAST: run forecast