Snowflake Geospatial, AI, UDF and ML Functions

1.Geospatial functions

Geospatial functions work on the GEOGRAPHY and GEOMETRY data types. They also convert those values to and from other formats, such as VARCHAR. The catalogue is grouped by job:

- Input/parsing: TO_GEOGRAPHY, TRY_TO_GEOGRAPHY, ST_GEOGRAPHYFROMWKT. - Output/formatting: ST_ASGEOJSON, ST_ASWKT, ST_GEOHASH. - Constructors: ST_MAKEPOINT, ST_MAKELINE, ST_MAKEPOLYGON. - Accessors: ST_X, ST_Y, ST_SRID. - Relationships and measurements: ST_DISTANCE, ST_CONTAINS, ST_INTERSECTS, ST_DWITHIN, HAVERSINE, ST_AREA. - Transformations: ST_BUFFER, ST_UNION_AGG, ST_TRANSFORM. - H3 grid indexing: H3_LATLNG_TO_CELL, H3_POLYGON_TO_CELLS.

Some functions only accept one of the two types. For example, ST_MAKEPOINT, ST_DWITHIN and all the H3 functions are GEOGRAPHY-only, while ST_BUFFER, ST_TRANSFORM and ST_SETSRID are GEOMETRY-only. Exam scenarios can turn on that difference.

The GEOGRAPHY/GEOMETRY split shows up as paired functions. To build a point, ST_MAKEPOINT (alias ST_POINT) gives a GEOGRAPHY, while ST_MAKEGEOMPOINT (alias ST_GEOMPOINT) gives a GEOMETRY. To convert from other representations, TO_GEOGRAPHY, ST_GEOGRAPHYFROMWKT and ST_GEOGRAPHYFROMWKB are GEOGRAPHY-only, while TO_GEOMETRY, ST_GEOMETRYFROMWKT and ST_GEOMETRYFROMWKB are GEOMETRY-only. So a scenario that builds points and then measures them, for example with ST_DISTANCE, needs a constructor that matches the type of the data. ST_ASWKT (alias ST_ASTEXT) turns either type back into text.

Sources1

2.Cortex AI functions

Cortex AI Functions run LLM-powered analysis on text and images as ordinary SQL functions. They are also available in Python. To call them, your role needs the USE AI FUNCTIONS account-level privilege plus either the CORTEX_USER or the AI_FUNCTIONS_USER database role.

Each task-specific function maps to one job. AI_COMPLETE handles general generative work. AI_CLASSIFY puts text or images into categories you define. AI_FILTER returns True or False, so you can use it in SELECT, WHERE or JOIN … ON clauses. AI_EXTRACT pulls information out of text, images and documents. The others are AI_SENTIMENT, AI_TRANSLATE, AI_REDACT (removes PII), AI_EMBED, AI_SIMILARITY, AI_TRANSCRIBE and AI_PARSE_DOCUMENT. AI_AGG and AI_SUMMARIZE_AGG are aggregates that work across many rows, and they are not subject to context window limits. There are also helper functions: AI_COUNT_TOKENS, TO_FILE and PROMPT.

These functions are built for throughput, so they suit batch processing over large tables. For interactive work where latency matters, Snowflake points you to the REST API.

Sources2

3.User-defined functions (UDFs)

When no built-in function does what you need, or your organisation wants a standard calculation in one place, write a UDF. You call it the same way as a built-in function. You choose a variation based on what goes in and what comes out, and you write the logic, called the handler, in one of the supported languages.

CREATE OR REPLACE FUNCTION addone(i INT)
  RETURNS INT
  LANGUAGE PYTHON
  RUNTIME_VERSION = '3.12'
  HANDLER = 'addone_py'
AS $$
def addone_py(i):
 return i+1
$$;

A few behaviours to remember. UDTFs can process staged files in parallel, but UDFs currently process them one at a time. A query that reads staged files through a UDF fails if the same statement also queries a view that calls any UDF or UDTF. If a handler calls CURRENT_DATABASE or CURRENT_SCHEMA, it gets the database or schema that contains the UDF, not the one the session is using.

Sources3

4.ML functions: Classification, Top Insights, Anomaly Detection

ML functions let you train a model on your own data without being a machine learning expert, because Snowflake chooses the type of model for each feature. They are SQL classes. You create an instance, which is a schema-level object, and then call that instance's methods with the ! syntax. ML functions incur compute and storage costs. The storage reflects the model instances created during training, so deleting unused models reduces it. Top Insights is the exception on storage: its instances store no data and have a negligible effect on storage costs, though it still uses compute.

Anomaly Detection works on time series. It flags metric values that differ from what is expected. Classification sorts rows into two or more classes. Models are immutable, so to retrain you replace the model (CREATE OR REPLACE), and the target column can have at most 255 distinct classes. Top Insights does key driver analysis. It needs a Boolean label column that separates the control group (FALSE) from the test group (TRUE). It returns the segments that explain the change in a metric, with CONTRIBUTION, RELATIVE_CONTRIBUTION and GROWTH_RATE columns. The instance holds no state, so one instance is enough.

Anomaly Detection in more detail. Training takes a reference to the training data (INPUT_DATA, a table, a view or a query, passed with TABLE(...), SYSTEM$REFERENCE or SYSTEM$QUERY_REFERENCE), a TIMESTAMP_COLNAME (TIMESTAMP_NTZ), a TARGET_COLNAME (the NUMERIC or FLOAT metric to analyse) and a LABEL_COLNAME. The label column holds Boolean values saying whether a row is a known anomaly. If you have no labelled data, pass an empty string, which gives unsupervised training. Supplying labels gives the supervised, labelled approach. SERIES_COLNAME is optional and identifies each series when you have several. You need at least 2 data points to train, at least 12 for non-naive results and at least 60 for non-linear results.

You then call <model_name>!DETECT_ANOMALIES with INPUT_DATA, TIMESTAMP_COLNAME and TARGET_COLNAME. SERIES_COLNAME and CONFIG_OBJECT are optional, and the columns must match those used in training. The timestamps in the data to analyse must chronologically follow the training timestamps, without too large a gap. The method returns a table that labels each row as anomalous or not, with the columns SERIES, TS, Y, FORECAST, LOWER_BOUND, UPPER_BOUND, IS_ANOMALY, PERCENTILE and DISTANCE. In CONFIG_OBJECT, prediction_interval defaults to 0.99. Because the method returns a table, you can also select columns from it by calling it inside TABLE(...) in a FROM clause. Models cannot be updated in place, so you delete and retrain.

CREATE OR REPLACE SNOWFLAKE.ML.ANOMALY_DETECTION sensor_model(
  INPUT_DATA => SYSTEM$REFERENCE('VIEW', 'sensor_data_view'),
  TIMESTAMP_COLNAME => 'timestamp',
  TARGET_COLNAME => 'temperature',
  LABEL_COLNAME => '');

CALL sensor_model!DETECT_ANOMALIES(
  INPUT_DATA => SYSTEM$REFERENCE('TABLE', 'sensor_data_device3'),
  TIMESTAMP_COLNAME => 'timestamp',
  TARGET_COLNAME => 'temperature'
);

CREATE OR REPLACE SNOWFLAKE.ML.CLASSIFICATION model_binary(
    INPUT_DATA => SYSTEM$REFERENCE('view', 'binary_classification_view'),
    TARGET_COLNAME => 'label'
);

For Classification, every column not named as the target is a feature. Feature columns must be STRING, NUMERIC or BOOLEAN. STRING and BOOLEAN features are treated as categorical and NUMERIC ones as continuous, so cast a number to STRING to make it categorical. PREDICT takes an INPUT_DATA object of feature names and values, and returns the predicted class (the label with the highest probability), a probability object with a probability for each class, and LOGS with any error or warning messages. To evaluate a model, call SHOW_EVALUATION_METRICS, SHOW_GLOBAL_EVALUATION_METRICS, SHOW_THRESHOLD_METRICS and SHOW_CONFUSION_MATRIX. SHOW_FEATURE_IMPORTANCE ranks the features, and SHOW_TRAINING_LOGS shows the training logs.

SELECT model_binary!PREDICT(INPUT_DATA => {*}) as prediction from prediction_purchase_data;

CALL my_insights!get_drivers (
  INPUT_DATA => TABLE(my_table),
  LABEL_COLNAME => 'label',
  METRIC_COLNAMe => 'sales');

Sources45678910

5.Review: NULLs in aggregates and table functions

These two practice questions are about general function behaviour, not about AI or ML functions.

NULLs in aggregates. Some aggregate functions ignore NULL values. AVG of 1, 5 and NULL is 3, because only the two non-NULL values count in both the numerator and the denominator. If every value passed to an aggregate is NULL, the aggregate returns NULL. When an aggregate such as COUNT is passed more than one column, it ignores a row if any of those columns is NULL.

Table functions. A table function returns a set of rows for each input row, and you use it in the FROM clause. FLATTEN is a built-in table function for semi-structured data. Snowflake requires the table function call to be wrapped in the TABLE() keyword, and the arguments can be columns from a table that comes earlier in the FROM clause.

Sources1112

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.Any UDF can be shared with consumers, whatever language its handler is written in.Why is that wrong?

   Only UDFs with SQL or JavaScript handlers are sharable. Java, Python and Scala UDFs are not.

   Covered in User-defined functions (UDFs)

2. 2.All Snowflake ML functions need time-series data.Why is that wrong?

   Anomaly Detection (and Forecasting) are time-series functions. Classification and Top Insights work without time-series data.

   Covered in ML functions: Classification, Top Insights, Anomaly Detection