Snowflake Mind Map: Key Concepts, Use Cases and AI Capabilities

Optimized storage

One place for all data

What it is

Structured, semi-structured and unstructured data (PDFs, images) in one place, built on cloud blob storage. Snowflake handles micro-partitioning, compression and encryption, and works with on-prem data and open table formats such as Apache Iceberg.

When to use it

One storage layer for the lake and the warehouse, with no files to manage yourself.

Elastic compute

Multi-cluster, near infinite

What it is

"Near infinitely scalable" compute. Compute is also separated from compute: several clusters work on the same data without contention. Supports SQL, Python and Java.

When to use it

Teams and workloads scale independently: the dashboard cluster never waits for the ETL cluster.

Cloud services

The brain of the platform

What it is

Automatic upgrades with no downtime; manages files and metadata; enables ACID transactions, query result caching, Time Travel, zero-copy cloning and high concurrency.

When to use it

The DBA chores you stop doing: patching, vacuuming, index tuning.

Snowgrid

Across regions and clouds

What it is

Connects regions and clouds (AWS, Azure, GCP) so data, services and apps reach teams, partners and customers with no extra ETL or ELT. Supports replication, business continuity, failover and cloud migration.

When to use it

A European subsidiary reads the US dataset in its own region without a copy pipeline.

Storage ≠ compute

Scale and pay separately

What it is

Storage is where data lives; compute is where queries run, moving data from storage into the compute nodes. Each is sized and billed on its own.

When to use it

A small team with a huge table pays for the table once and for compute only while querying.

Snowsight & worksheets

The browser UI

What it is

Snowsight is "the primary browser-based way of interacting with Snowflake". SQL and Python worksheets write and run statements, filter results and chart them; Native Notebooks are the other option.

When to use it

Most of the course happens here: upload a worksheet from a .sql file, set the role and warehouse, run block by block.

In code

-- line comment   /* block comment */
Cmd/Ctrl + Enter   runs the statement under the cursor
Cmd + Shift + ?    lists the hot keys

Figures from the course

• The sample data: Tasty Bytes, a fictional company with 450 food trucks and 15 brands.Lesson: Worksheets

Virtual warehouse

Compute, not storage

What it is

"A cluster of compute resources": CPU, memory and temporary storage drawn from AWS, GCP or Azure. It runs most SQL queries and all DML. Despite the name it is for compute, not storage, and it bills while started.

When to use it

Pick a size per job: a 100-row menu table needs an XS; the 600-million-row orders table gets a bigger one, then you scale back down.

In code

CREATE WAREHOUSE warehouse_dash;        -- defaults to XS
USE WAREHOUSE warehouse_gilberto;
SHOW WAREHOUSES;
ALTER WAREHOUSE w SET WAREHOUSE_SIZE = 'LARGE';
DROP WAREHOUSE warehouse_dash;

Figures from the course

• 10 standard sizes from XS to 6XL; compute and credits per hour double at each step.Lesson: Virtual Warehouses
• XS = 1 credit per hour (2⁰); 6XL = 512 credits per hour (2⁹).Lesson: Virtual Warehouses
• The orders table in the sample data has more than 600 million rows.Lesson: Virtual Warehouses

1. Scale up and out

   Size vs clusters

   What it is

   Up means a bigger size for a heavier query. Out means a multi-cluster warehouse that adds clusters when many users query at once, so nobody queues.

   When to use it

   A huge warehouse for a short time can cost the same credits as a small one for a long time, and finish sooner.

   In code

   ALTER WAREHOUSE w SET MIN_CLUSTER_COUNT = 1 MAX_CLUSTER_COUNT = 3;

   Figures from the course

   • The demo multi-cluster warehouse runs between 1 and 3 clusters.Lesson: Virtual Warehouses Scaling

2. Auto-suspend & resume

   On by default

   What it is

   Both are on by default. A running warehouse keeps its cache, so suspending too early can mean redoing work; suspending late burns credits.

   When to use it

   Set a short suspend time on an ad-hoc warehouse and leave resume on so the next query just works.

   In code

   ALTER WAREHOUSE w SET AUTO_SUSPEND = 180 AUTO_RESUME = FALSE;
   ALTER WAREHOUSE w SUSPEND;

   Figures from the course

   • The demo sets auto-suspend to 180 seconds.Lesson: Virtual Warehouses Scaling

3. Snowpark-optimized

   Extra memory

   What it is

   A warehouse type with extra memory, offered next to Standard when you create a warehouse.

   When to use it

   Memory-hungry Python and machine-learning work in Snowpark.

   AI angle

   The course names it as the warehouse for ML training workloads.

Databases & schemas

db.schema.object

What it is

Database › schema › tables, views and other objects, "like a file directory". Every database comes with PUBLIC (the default schema) and INFORMATION_SCHEMA (metadata views). The SNOWFLAKE database holds ACCOUNT_USAGE for observability.

When to use it

Layer schemas raw → harmonized → analytics, and address objects by their fully qualified name from any worksheet.

In code

CREATE DATABASE tasty_bytes_sample_data;
CREATE SCHEMA tasty_bytes_sample_data.raw_pos;
SELECT * FROM tasty_bytes_sample_data.INFORMATION_SCHEMA.TABLES;
UNDROP DATABASE tasty_bytes_sample_data;

Database Explorer

Catalog with lineage

What it is

Under Catalog: a hierarchical view of everything your role can access, sorted by object type. Tabs for columns, a 100-row data preview (uses a warehouse), view definitions and Lineage, a graph of upstream and downstream objects where each arrow shows the SQL behind it.

When to use it

Trace a gold table back to its sources before changing it; the course calls lineage "a genuinely big deal for data engineering".

Tables & data types

Micro-partitions underneath

What it is

Columns and rows, stored as micro-partitions. Six type categories: numeric, string and binary, logical, date and time, semi-structured, geospatial. Synonyms such as NUMBER = DECIMAL = NUMERIC and VARCHAR = STRING = TEXT make porting from other dialects easy.

When to use it

Declare VARCHAR without a length: only the space used is stored, and a smaller size helps only some BI tools.

In code

CREATE TABLE raw_pos.menu (menu_id NUMBER(19,0), menu_item_name VARCHAR(16777216), …);
INSERT INTO t VALUES (…);
SHOW TABLES IN tasty_bytes_sample_data.raw_pos;
UNDROP TABLE raw_pos.menu;

Figures from the course

• NUMBER defaults to precision 38, scale 0; VARCHAR to 16,777,216 bytes (16 MB).Lesson: Tables
• ORDER_DETAIL has more than 670 million rows and 9 columns.Lesson: Tables

Table types

Permanent, transient, temporary

What it is

Permanent: the default, retention up to 90 days on Enterprise plus 7 days of fail-safe. Transient: retention 0–1 day, no fail-safe, lives until dropped. Temporary: 0–1 day, no fail-safe, gone with the session.

When to use it

Scratch and staging tables as transient or temporary to stop paying retention storage for data nobody will restore.

In code

CREATE TRANSIENT TABLE raw_pos.truck_transient AS SELECT * FROM raw_pos.truck;
CREATE TEMPORARY TABLE raw_pos.truck_temp AS SELECT * FROM raw_pos.truck;
SHOW TABLES LIKE 'TRUCK%';

Views

Standard, materialized, secure

What it is

A standard view is "basically just a saved query", re-run each time. A materialized view stores its result and refreshes automatically, which costs money, and allows no joins. A secure view hides its definition from non-owners but bypasses the optimizer. Views cannot be undropped.

When to use it

Secure views for shared data; for new scheduled transformations the course points to dynamic tables instead of materialized views.

In code

CREATE VIEW analytics.menu_v AS SELECT …;
CREATE MATERIALIZED VIEW analytics.menu_mv AS SELECT … FROM raw_pos.menu;
CREATE SECURE VIEW analytics.menu_sv AS SELECT …;
SHOW MATERIALIZED VIEWS;

Dynamic tables

A view with a schedule

What it is

Defined by a query like a view, stored like a materialized view, refreshed on a TARGET_LAG you choose using a warehouse. Full support for joins and complex transformations: "the flexibility of a view and the performance of a table".

When to use it

Near-real-time results with Snowpipe or Snowpipe Streaming "without any orchestration code"; for scheduled transformations with joins they are "almost always the right choice".

In code

CREATE DYNAMIC TABLE analytics.orders_dt TARGET_LAG = '1 minute' WAREHOUSE = compute_wh AS SELECT …;
SHOW DYNAMIC TABLES;   -- refresh status and lag
ALTER DYNAMIC TABLE analytics.orders_dt REFRESH;

Figures from the course

• Refresh lag of 1 minute or more in the demo; Snowflake is working to lower it.Lesson: Dynamic Tables

Stages

Bridge from files to tables

What it is

An intermediate object that "serves as a bridge between your data source and your table". External stages point at S3, GCS or Azure storage you manage; internal stages are managed and billed by Snowflake: user (@~), table (@%) and named (@) stages, the named one being the most flexible.

When to use it

Load CSVs from a bucket: create the stage, LIST it, COPY INTO the table, then read COPY_HISTORY to see what landed.

In code

CREATE STAGE raw_pos.s3load URL = 's3://sfquickstarts/tasty-bytes/' FILE_FORMAT = csv_ff;
LIST @raw_pos.s3load;
COPY INTO raw_pos.menu FROM @raw_pos.s3load/raw_pos/menu/;
PUT file://local.csv @my_internal_stage;
SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.COPY_HISTORY;

Figures from the course

• A 3XL warehouse is 64 times as powerful as an XS and uses 64 times the credits.Lesson: Stages & Basic Ingestion

Semi-structured data

VARIANT, OBJECT, ARRAY

What it is

Hierarchical key-value data between tabular and unstructured, common in sensor and web data. Five formats: JSON, Avro, ORC, Parquet, XML. Three types: VARIANT (any value, tracks its type), OBJECT (key-value pairs), ARRAY (ordered VARIANTs).

When to use it

Query the nested ingredients and health flags of a menu row with colon paths, then FLATTEN them into one row per ingredient.

In code

SELECT menu_item_health_metrics_obj:menu_item_health_metrics[0]:ingredients FROM raw_pos.menu;
SELECT TYPEOF(col), col::VARIANT FROM t;
SELECT value:ingredients[0] FROM raw_pos.menu, LATERAL FLATTEN(input => menu_item_health_metrics_obj:menu_item_health_metrics);

Figures from the course

• The demo flags 95 nut-free, 81 dairy-free and 23 healthy menu items out of 100.Lesson: Semi-Structured Data

Time Travel

Query the past

What it is

Recover or query the exact past state of a table, schema or database, built on data retention; UNDROP is one form of it. Three ways to point back: AT a timestamp, AT an offset in seconds, or BEFORE a query ID.

When to use it

Undo a bad DELETE, or compare a table before and after a load. Long retention on a high-churn table costs storage.

In code

ALTER TABLE raw_pos.truck SET DATA_RETENTION_TIME_IN_DAYS = 90;
SET ts = CURRENT_TIMESTAMP;   SET qid = LAST_QUERY_ID();
SELECT * FROM raw_pos.truck AT(TIMESTAMP => $ts);
SELECT * FROM raw_pos.truck AT(OFFSET => -60);
SELECT * FROM raw_pos.truck BEFORE(STATEMENT => $qid);

Figures from the course

• Default retention is 1 day; up to 90 days on the Enterprise edition.Lesson: Time Travel

Fail-safe

The last resort

What it is

Seven extra days after the retention period in which Snowflake staff, not you, can recover data. Permanent tables only.

When to use it

Insurance for a disaster, not a working tool: plan retention so you never need it.

Figures from the course

• Fail-safe lasts 7 days.Lesson: Permanent, Transient, and Temporary Tables

Zero-copy cloning

Instant copies

What it is

A clone shares the micro-partitions of its source, so it is near-instant and storage grows only as the clone changes. Works for databases, schemas, tables and dynamic tables; clones of clones are fine, and a clone can be taken from a Time Travel point.

When to use it

Test a migration against a copy of production taken ten minutes ago, then drop it.

In code

CREATE TABLE raw_pos.truck_dev CLONE raw_pos.truck;
CREATE SCHEMA raw_pos_dev CLONE raw_pos;
CREATE TABLE raw_pos.truck_10m CLONE raw_pos.truck AT(OFFSET => -600);
SELECT clone_group_id, active_bytes FROM INFORMATION_SCHEMA.TABLE_STORAGE_METRICS;

Figures from the course

• A fresh clone reports ACTIVE_BYTES = 0; database clones take longer because of metadata.Lesson: Cloning

Resource monitors

Credit quotas

What it is

Credit limits for the whole account (only one account-level monitor) or for one warehouse, with a frequency (daily, weekly, monthly, yearly, never) and triggers at percentages that Notify, Notify & Suspend (running queries finish) or Notify & Suspend Immediately.

When to use it

Cap a trial or a team warehouse so a forgotten query cannot run all weekend.

In code

CREATE RESOURCE MONITOR daily_30 WITH CREDIT_QUOTA = 30 FREQUENCY = DAILY START_TIMESTAMP = IMMEDIATELY TRIGGERS ON 80 PERCENT DO NOTIFY ON 100 PERCENT DO SUSPEND ON 110 PERCENT DO SUSPEND_IMMEDIATE;
ALTER WAREHOUSE w SET RESOURCE_MONITOR = daily_30;
SHOW RESOURCE MONITORS;

Figures from the course

• Demo quota: 30 credits a day, which an XS warehouse running 24 hours stays under; triggers at 80, 100 and 110 %.Lesson: Resource Monitors

UDFs & UDTFs

Your own functions

What it is

A UDF returns one scalar value and lives in a schema (PUBLIC by default); languages are SQL, Python, JavaScript, Java and Scala. A Python UDF needs a LANGUAGE, a RUNTIME_VERSION and a HANDLER, can import numpy or pandas, and can be vectorized to process batches. A UDTF returns a table and is called with TABLE(); no Scala.

When to use it

Encapsulate a currency conversion or a winsorize rule once and call it in any SELECT.

In code

CREATE FUNCTION to_cad(usd NUMBER) RETURNS NUMBER AS $$ usd * 1.35 $$;
CREATE FUNCTION winsorize(v FLOAT, hi FLOAT, lo FLOAT) RETURNS FLOAT LANGUAGE PYTHON RUNTIME_VERSION = '3.11' HANDLER = 'w' AS $$ … $$;
CREATE FUNCTION expensive_items(p NUMBER) RETURNS TABLE(name VARCHAR, price NUMBER) AS $$ SELECT … $$;
SELECT * FROM TABLE(expensive_items(15));
SHOW FUNCTIONS;   -- is_table_function column

Figures from the course

• winsorize(12, 11, 4) returns 11; the UDTF demo lists menu items above $15.Lesson: User-Defined Functions

Stored procedures

Steps with side effects

What it is

A stored, reusable series of steps that, unlike a UDF, can run DDL and DML and need not return a value. Written in Python, Java, JavaScript, Scala or SQL with Snowflake Scripting (DECLARE / BEGIN / EXCEPTION / END, := to assign, :var to reference). Each run needs its own CALL. SYSTEM$SEND_EMAIL is a built-in one.

When to use it

Housekeeping jobs: delete rows older than 180 days, then schedule the CALL with a task.

In code

CREATE OR REPLACE PROCEDURE delete_old() RETURNS BOOLEAN LANGUAGE SQL AS $$ BEGIN DELETE FROM raw_pos.order_header WHERE order_ts < DATEADD(day, -180, (SELECT MAX(order_ts) FROM raw_pos.order_header)); RETURN TRUE; END $$;
CALL delete_old();
DESCRIBE PROCEDURE delete_old();

Figures from the course

• The sample orders run from 1 Jan 2019 to 1 Nov 2022; SHOW PROCEDURES lists 4 built-in ones.Lesson: Stored Procedures

Role-based access control

Privileges → roles → users

What it is

Privileges on securable objects are granted to roles, roles to users, and roles to roles for inheritance; roles are "buckets of privileges". Six system roles: ORGADMIN, ACCOUNTADMIN, SECURITYADMIN, USERADMIN, SYSADMIN and PUBLIC. Governance also protects against "good but fallible actors".

When to use it

Give a data-engineering role exactly the warehouses and schemas it needs; the course uses ACCOUNTADMIN for convenience and says that "is not a best practice".

AI angle

The same roles govern who can call Cortex functions and open a Streamlit app.

In code

CREATE ROLE tasty_de;
GRANT CREATE WAREHOUSE ON ACCOUNT TO ROLE tasty_de;
GRANT ROLE tasty_de TO USER peter;
SHOW GRANTS TO ROLE tasty_de;
USE ROLE tasty_de;

Git integration

GitHub, GitLab, Bitbucket

What it is

A repository stage is a synced clone of a Git repo (branches, commits, files), set up with an API integration and, for private repos, a secret. Notebooks pull, edit, push and switch branches inside Snowsight; EXECUTE IMMEDIATE FROM runs SQL or Python files straight from the stage.

When to use it

Version your setup scripts in Git and deploy them from Snowflake; for full two-way Git on other objects use the CLI or a local environment.

In code

CREATE GIT REPOSITORY tasty_repo API_INTEGRATION = github_int ORIGIN = 'https://github.com/…';
EXECUTE IMMEDIATE FROM @tasty_repo/branches/main/setup.sql;

Data Clean Rooms

Analyse without sharing rows

What it is

Two parties analyse their combined data without either seeing the other's raw rows: the provider defines query templates, the consumer runs only approved ones, aggregation thresholds block inference and access can be revoked. A no-code web app for business users plus a developer API; consumers who are not Snowflake customers get a managed account.

When to use it

A retailer and a media company measure audience overlap, learning "40 % of your customers overlap" and nothing about individuals.

Snowpark DataFrames

Python next to the data

What it is

Snowpark is "Snowflake's set of non-SQL capabilities". A Snowpark DataFrame runs your Python logic inside Snowflake compute "right next to your data" instead of holding it in local memory like pandas, and evaluates lazily: nothing runs until an action such as show() or collect().

When to use it

Aggregate 600 million rows from a laptop with 16 GB of RAM, then to_pandas() only the small result for matplotlib or scikit-learn.

In code

from snowflake.snowpark.context import get_active_session
session = get_active_session()
df = session.table('raw_pos.order_header').filter(col('truck_id') == 15).select('order_id', 'order_total')
df.group_by('truck_id').agg(sum_('order_total')).sort('truck_id').show()
df.write.save_as_table('analytics.truck_totals', mode='overwrite')
pip install snowflake-snowpark-python   # local; credentials from environment variables, never in source

Snowflake Notebooks

SQL, Python, Markdown cells

What it is

The recommended environment for Snowpark work, replacing Python worksheets: run cells one by one, mix SQL and Python, Snowpark pre-installed, Git-connected from the Files tab, governed by RBAC.

When to use it

Explore, train and document in one place; `snow notebook run` executes a notebook on demand from a pipeline.

AI angle

Where the course runs its Cortex and Snowpark ML demos.

VS Code extension

Maintained by Snowflake

What it is

Verified and maintained by Snowflake: autocomplete, hover links to the docs, query history, an object explorer, execution context and role switching. The account identifier uses "-" where the URL has ".".

When to use it

Write SQL and Python against Snowflake in the editor you already use, with the same role model.

Snowflake CLI

`snow` in the terminal

What it is

Installed with pip, the tool is called `snow`; connections live in ~/.snowflake/config.toml (dev, staging, prod) and are picked with --connection. Command groups: object, sql, notebook, cortex and git. The older "Snowflake CLI labs" package is deprecated.

When to use it

Scripts and CI: list objects, run SQL files, trigger notebooks, sync a Git project.

AI angle

`snow cortex` calls the Cortex AI functions from the terminal and pipes the plain-text answer onwards.

In code

pip install snowflake-cli
snow connection add   &&   snow connection test
snow sql -q "SELECT COUNT(*) FROM raw_pos.menu"
snow object list warehouse
snow notebook run my_notebook
snow git fetch tasty_repo

Drivers & connectors

Python, JDBC, Node.js, Go, PHP

What it is

The Snowflake Connector for Python, the JDBC, Node.js, Go and PHP PDO drivers, a Django connector built on the Python one, and SQLAlchemy. The usual pattern: connect, then run SQL based on what the user does in the UI.

When to use it

Integrating Snowflake into an existing application stack: the course's advice is to learn the drivers, connectors and Python API first.

Snowflake Python API

Manage objects from Python

What it is

Create, alter and drop tables, tasks, warehouses, databases and schemas from Python, and manage Snowpark Container Services and tasks.

When to use it

Infrastructure for a data app written in the same language as the app.

Ingestion

Batch to streaming

What it is

A spectrum from batch to low latency: COPY INTO for batch, Snowpipe for continuous files, Snowpipe Streaming and the Kafka connector for rows in seconds, plus native connectors and the Python, ODBC and SQLAlchemy interfaces.

When to use it

Match the loader to the latency the business needs instead of streaming everything.

1. COPY INTO

   Batch loads

   What it is

   Loads files from a stage into a table using a file format. COPY_HISTORY in ACCOUNT_USAGE shows what was loaded, with a latency of up to 1–2 hours.

   When to use it

   The nightly load of a partner's CSV drop.

   In code

   COPY INTO raw_pos.menu FROM @raw_pos.s3load/raw_pos/menu/ FILE_FORMAT = (FORMAT_NAME = csv_ff);
   SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.COPY_HISTORY WHERE table_name = 'MENU';

2. Snowpipe

   Files arrive, rows appear

   What it is

   Continuous loading in under a minute: with AUTO_INGEST = TRUE a cloud storage event triggers the pipe's COPY INTO with no manual orchestration. It needs a storage integration, "an interface between Snowflake and third-party services": an IAM policy and role on AWS, the role ARN in Snowflake, then the trust policy edited with Snowflake's IAM user ARN and external ID.

   When to use it

   Landing zone in S3 → table in Snowflake within a minute of each upload. The course warns the hardest part is the permissions between AWS and Snowflake.

   In code

   CREATE STORAGE INTEGRATION s3_int TYPE = EXTERNAL_STAGE STORAGE_PROVIDER = 'S3' ENABLED = TRUE STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::…:role/snowflake' STORAGE_ALLOWED_LOCATIONS = ('s3://bucket/path/');
   DESCRIBE INTEGRATION s3_int;   -- IAM user ARN and external ID for the trust policy
   CREATE STAGE raw_pos.s3_stage STORAGE_INTEGRATION = s3_int URL = 's3://bucket/path/';
   CREATE PIPE raw_pos.menu_pipe AUTO_INGEST = TRUE AS COPY INTO raw_pos.menu FROM @raw_pos.s3_stage;
   ALTER PIPE raw_pos.menu_pipe SET PIPE_EXECUTION_PAUSED = TRUE;

   Figures from the course

   • A pipe's role needs USAGE on the database, schema and stage, INSERT on the table and OWNERSHIP of the pipe.Lesson: Snowpipe

3. Snowpipe Streaming & Kafka

   Rows in seconds

   What it is

   Row-level streaming through an API and the Java SDK, loading in seconds rather than Snowpipe's under-a-minute; the Kafka connector uses it.

   When to use it

   Clickstream or IoT events that a dynamic table turns into near-real-time metrics.

4. Native connectors

   ServiceNow, Google Analytics…

   What it is

   Snowflake-built connectors for sources such as ServiceNow and Google Analytics, next to the Python, ODBC and SQLAlchemy interfaces for everything else.

   When to use it

   SaaS data without writing an extractor.

Data sharing & Marketplace

Zero-copy, read-only

What it is

Shares are zero-copy and read-only, offered through the Snowflake Marketplace or directly; the consumer creates a database from the share and queries it live, with no ingestion.

When to use it

Join a weather dataset to your orders without a pipeline, or publish a dataset to partners.

In code

CREATE DATABASE weather FROM SHARE provider_account.weather_share;

Transformation

SQL, Snowpark, dynamic tables

What it is

SQL, Snowpark DataFrames, UDFs and UDTFs, stored procedures, and dynamic tables, which the course describes as "like a table with inbuilt orchestration".

When to use it

Raw → harmonized → analytics layers rebuilt on a lag you choose, no scheduler to run.

Orchestration

Streams + tasks

What it is

A stream tracks the inserts, updates and deletes on a table; a task runs SQL or a procedure on a schedule, an interval or after another task, forming a DAG; serverless tasks need no warehouse. Together they give "efficient incremental data processing".

When to use it

Process only the rows that changed since the last run, every ten minutes.

In code

CREATE STREAM raw_pos.menu_stream ON TABLE raw_pos.menu;
CREATE TASK analytics.refresh_menu WAREHOUSE = compute_wh SCHEDULE = '10 MINUTE' AS INSERT INTO analytics.menu_changes SELECT * FROM raw_pos.menu_stream;
ALTER TASK analytics.refresh_menu RESUME;

1. Airflow, Prefect, dbt

   Open-source orchestration

   What it is

   The open-source orchestrators the course names as alternatives to streams and tasks: Apache Airflow, Prefect and dbt.

   When to use it

   Teams that already run Airflow or dbt keep their scheduler and point it at Snowflake.

Observability

Alerts, logs, traces, DAGs

What it is

Alerts and notifications (SYSTEM$SEND_EMAIL), logging and trace events stored in event tables, the task graph shown as a DAG in Snowsight, and views such as COPY_HISTORY.

When to use it

An e-mail when a load fails or a credit threshold is crossed, and a table of traces to debug a procedure.

In code

CREATE EVENT TABLE analytics.events;
CREATE ALERT low_sales WAREHOUSE = compute_wh SCHEDULE = '60 MINUTE' IF (EXISTS (SELECT … )) THEN CALL SYSTEM$SEND_EMAIL(…);

Governance built in

Data never leaves

What it is

The same RBAC and governance policies that protect tables apply to the AI functions automatically; the model runs where the data is.

When to use it

Run an LLM over customer text without exporting it to a third-party API.

AI angle

Governance is the argument for AI inside the warehouse.

Cortex LLM functions

AI inside SELECT

What it is

Task-specific functions called inside a SELECT with no model to manage: AI_SENTIMENT (positive, negative, neutral), AI_TRANSLATE, AI_EXTRACT (questions over text), AI_CLASSIFY, AI_PARSE_DOCUMENT and AI_COMPLETE (any prompt to any supported model). SUMMARIZE is called deprecated; use COMPLETE with a summarising prompt.

When to use it

Score the sentiment of every review, translate a column, classify tickets, all as columns in a query.

AI angle

The whole family is LLM-powered and runs row by row over your tables.

In code

SELECT AI_SENTIMENT(review_text) FROM reviews;
SELECT AI_TRANSLATE(review_text, 'en', 'es') FROM reviews;
SELECT AI_EXTRACT(review_text, 'Which dish is mentioned?') FROM reviews;
SELECT AI_CLASSIFY(review_text, ['service', 'food', 'price']) FROM reviews;
SELECT AI_PARSE_DOCUMENT(@docs, relative_path) FROM DIRECTORY(@docs);

COMPLETE in practice

Prompts over rows

What it is

SNOWFLAKE.CORTEX.COMPLETE(model, prompt) shows up as a UDF under database.schema.function. The demo uses mistral-7b; a larger model costs more per token and a max-tokens option caps the answer. Prompts can be built per row with CONCAT, and a conversation is an array of {role, content} objects with system, user and assistant roles.

When to use it

A short marketing line per menu item, generated for five rows at a time while you tune the prompt.

AI angle

Generative text from SQL, model chosen per call.

In code

SELECT SNOWFLAKE.CORTEX.COMPLETE('mistral-7b', 'Why is mango sticky rice tasty?');
SELECT SNOWFLAKE.CORTEX.COMPLETE('mistral-7b', CONCAT('Tell me why this food is tasty: ', menu_item_name)) FROM raw_pos.menu LIMIT 5;
SELECT SNOWFLAKE.CORTEX.COMPLETE('mistral-7b', [{'role': 'system', 'content': 'You are a chef.'}, {'role': 'user', 'content': 'Suggest a dessert.'}], {'max_tokens': 100});

Figures from the course

• The row-wise demo is limited to 5 rows; the CLI demo switches to a Llama model.Lesson: Snowflake Cortex LLM Functions

Cortex Search

Retrieval for RAG

What it is

A semantic search service over a text column: the retrieval part of retrieval-augmented generation, paired with COMPLETE for the answer. Setup takes the column, a warehouse, a refresh lag and a query; it is used through a REST API or from Streamlit. Embeddings need a paid account, so it errors on a trial.

When to use it

Chat over your own documents with answers grounded in the passages it finds.

AI angle

The "R" of RAG, managed, indexed from a table column.

In code

CREATE CORTEX SEARCH SERVICE review_search ON review_text WAREHOUSE = compute_wh TARGET_LAG = '1 hour' AS SELECT review_text, truck_id FROM reviews;

Cortex Analyst

Questions, not SQL

What it is

Answers natural-language questions about your data without writing SQL, exposed as a REST API. It needs a semantic model that defines the business terms, and the course calls it reliable enough for production apps.

When to use it

Self-serve analytics in an app: "revenue by truck brand last quarter" typed by a manager.

AI angle

Text-to-SQL grounded in your semantic model rather than raw table names.

Cortex Fine-Tuning

Tune a model in place

What it is

Tune a base model on pairs of input and ideal output stored in Snowflake, with no GPUs to provision, then call the result like any other Cortex function.

When to use it

Your own classification labels or house style, learned from examples you already have in a table.

AI angle

Custom models without leaving the governance boundary.

Document AI

PDFs into tables

What it is

A Snowflake-managed model that extracts structured data from PDFs, forms and contracts into table columns.

When to use it

Invoice totals and dates out of a folder of scanned documents.

AI angle

No training or model hosting on your side.

AI/ML Studio

Point and click

What it is

A Snowsight interface for forecasts and classification without writing SQL.

When to use it

An analyst builds a demand forecast in minutes before anyone writes code.

AI angle

The "seconds" end of the spectrum: no-code AI.

Universal Search

Find anything in words

What it is

Natural-language search across your objects, Marketplace listings, the documentation and community resources.

When to use it

"Which table has truck revenue?" instead of browsing schemas.

AI angle

LLM-powered discovery inside Snowsight.

Snowflake Copilot

Plain English to SQL

What it is

An LLM assistant in Snowsight that turns a plain-English request into SQL and refines it with you. The transcript renders the name as "Snowflake Coco".

When to use it

Draft the GROUP BY you half remember, then edit it in the worksheet.

AI angle

Text-to-SQL for the person writing the query.

Cortex CLI

`snow cortex`

What it is

The CLI exposes complete, extract-answer, sentiment, translate and search. Output is plain text to pipe or redirect, and a file can be piped in for document tasks.

When to use it

Summarise a log file from a shell script with a model that runs in your account.

AI angle

The same Cortex functions, from the terminal.

In code

snow cortex --help
snow cortex complete "Why is mango sticky rice tasty?" --model llama3-8b
cat notes.txt | snow cortex summarize

Snowpark Container Services

The escape hatch

What it is

Bring your own model, or a partner LLM from the Marketplace, in containers on Snowflake-managed infrastructure including GPUs; any language (C/C++, Node.js, Python, R, React).

When to use it

A model Cortex does not offer, or a React front end that must live inside the account.

AI angle

The "hours" end of the spectrum: fully custom AI next to the data.

Cortex ML functions

FORECAST, ANOMALY, CLASSIFY

What it is

SQL-callable ML models (not LLMs) that usually need training data: FORECAST for time series, ANOMALY_DETECTION for outliers, TOP_INSIGHTS for the drivers of a metric shift (not causal) and CLASSIFICATION, binary or multiclass. FORECAST, ANOMALY_DETECTION and CLASSIFICATION use a gradient-boosting machine; FORECAST and ANOMALY_DETECTION are created then CALLed, the others run inside SELECT.

When to use it

Next month's sales per truck or a flag on an odd daily total, with no Python at all.

AI angle

Pre-built models you train with a statement.

In code

CREATE SNOWFLAKE.ML.FORECAST sales_fc (INPUT_DATA => TABLE(daily_sales), TIMESTAMP_COLNAME => 'day', TARGET_COLNAME => 'revenue');
CALL sales_fc!FORECAST(FORECASTING_PERIODS => 30);
SELECT SNOWFLAKE.ML.TOP_INSIGHTS(…);
SELECT model_binary!PREDICT(…);

Snowpark ML Modeling

scikit-learn style, in Snowflake

What it is

One Python library (snowflake.ml) with scikit-learn, XGBoost and LightGBM-style APIs for preprocessing, feature engineering and training that run inside Snowflake; some steps and hyperparameter tuning run distributed. Before it, ML in Snowflake meant hand-made stored procedures or UDFs.

When to use it

Keep the sklearn habits, drop the data export.

AI angle

Training moves to the data instead of the data moving to the trainer.

In code

from snowflake.ml.modeling.preprocessing import LabelEncoder
from snowflake.ml.modeling.xgboost import XGBClassifier
enc = LabelEncoder(input_cols=['city'], output_cols=['city_code']).fit(df)
model = XGBClassifier(input_cols=features, label_cols=['label']).fit(train_df)
model.score(test_df)

Model Registry

Versioned models of any origin

What it is

Versioned model artifacts and metadata; manages and runs models of any origin, including SageMaker, Azure ML, Vertex AI and Hugging Face LLMs.

When to use it

One place to log, version and serve the model whichever platform trained it.

AI angle

Hugging Face models can be registered and run next to the data.

In code

registry.log_model(model, model_name='truck_classifier', version_name='v1')

Feature Store

Features as a single source of truth

What it is

Create, store, manage and serve features as "a single source of truth" with continuous updates.

When to use it

Training and inference read the same feature definitions, so the model sees in production what it saw in training.

AI angle

The data layer under both ML and LLM pipelines.

The XGBoost demo

Snowpark ML end to end

What it is

Session from credentials, Snowpark DataFrame exploration (count, describe, group_by), a LabelEncoder, a random split, tables saved back, an XGBClassifier fitted and scored, all in Snowflake. The same task could use the Cortex ML CLASSIFICATION function.

When to use it

The template for a first in-warehouse model: preprocess, split, fit, score, log.

In code

train_df, test_df = df.random_split(weights=[0.9, 0.1], seed=0)
train_df.write.mode('overwrite').save_as_table('train')

Figures from the course

• 8 neighbourhoods and 20 years of daily data: 7,300 rows; a 10 % test set of 730 rows, 730 of 730 predictions correct.Lesson: Snowpark ML Modeling

Streamlit in Snowflake

Data apps in Python

What it is

Streamlit is an open-source Python framework for data apps "with just a few lines of Python". Built into Snowsight: no data movement, one-click secure deployment hosted by Snowflake, sharing by URL under existing RBAC, multipage apps and custom branding, low latency because the code sits next to the data.

When to use it

A revenue explorer with a date slider and a brand multiselect for the food-truck managers, or a model turned into an app with no front-end work.

In code

import streamlit as st
session = get_active_session()
@st.cache_data
def load(q): return session.sql(q).to_pandas()
city = st.select_slider('City', cities)   brands = st.multiselect('Brand', brands)
tab1, tab2 = st.tabs(['Revenue', 'Orders'])

Figures from the course

• The demo app is under 100 lines of code and queries orders_v, a view of more than 690 million rows.Lesson: Streamlit in Snowflake

Hybrid tables (Unistore)

Transactions + analytics

What it is

Tables that serve transactional and analytical workloads from one database, so an app does not need separate OLTP and OLAP systems; the Unistore workload.

When to use it

A Django or Streamlit app writes its records to a hybrid table and the same table feeds the analytics.

Native App Framework

Package, list, install

What it is

Package app logic as an application package, list it on the Snowflake Marketplace, and let consumers try, buy and install it to run on their own compute.

When to use it

Sell a data product to other Snowflake accounts without hosting infrastructure.

Architecture choices

Outside or all inside

What it is

Front end outside Snowflake with Snowflake as the back end: React with the Node.js driver, or Django or open-source Streamlit with the Python connector writing to hybrid tables, the Python API managing containers and tasks. Or everything inside: a React front end in Snowpark Container Services, for lower latency, security or regulatory needs, faster time to market, scalability, high availability and less operational work.

When to use it

Choose by where the data must stay and who runs the servers.

The Data Cloud

Platform + content

What it is

Two elements: the platform, and the content on it: datasets, ML models and applications. Inside an organisation no silos; anyone with the right permissions reaches content without moving it. Between organisations the Marketplace builds bridges: zero-copy access across clouds and regions, "no ingestion necessary", with "a single governance model that cuts across everything".

When to use it

Join a weather dataset or install a geospatial app from the Marketplace and query it alongside your own tables.

AI angle

The course's closing example: Snowpipe → clean → feature store → train a model → serve it in Streamlit, one platform end to end.