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
4. Ent+: Can be replicated if source and target accounts are Enterprise editions
5. 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

• Allows setting idle timeout period, for general and web-ui
• applied at account and/or user level

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:
    • DETECT_ANOMALIES run detection
• 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