Snowflake Data and AI Engineering
Architecture, modelling, pipelines, governance, performance and Cortex AI on one governed platform
About this course
A six-module course that takes you from how Snowflake is built to running AI over governed data inside it. You learn the architecture and cost model first, then model data, build continuous ELT pipelines with Snowpipe, streams, tasks and dynamic tables, and lock it down with role-based access, masking, row policies and secure sharing.
The last six modules cover Snowpark and performance tuning with BI integration, and then Cortex AI: language model functions in SQL, retrieval over documents, natural-language analytics and agents. A capstone on a fictional grocery chain ties it together.
What you’ll be able to do
- Explain Snowflake’s architecture and control its cost with warehouses and resource monitors
- Model data with appropriate table types, views, semi-structured handling and functions
- Build continuous, idempotent ELT with Snowpipe, streams, tasks and dynamic tables
- Govern data with role hierarchies, masking and row access policies, auditing and secure sharing
- Tune performance from query profiles and build transformations with Snowpark
- Apply Cortex AI functions, retrieval and agents to governed data with measured quality and cost
Before you start
What this course assumes you can already do.
- Working SQL, including joins and aggregation, and basic Python.
- Familiarity with cloud object storage helps but is not required.
The curriculum
Open any module to see its objective, every teaching topic, the practice brief and the checkpoint.
Recaps and reference material are included in the LMS.
PART 1Platform and Modelling
01Snowflake Fundamentals and Architecture
Explain how Snowflake separates storage, compute and services, and set up an account that is safe to learn in and cheap to run.
- Snowflake’s three layers: storage, compute and cloud servicesConcept
- Accounts, regions and the Snowsight interfaceHands-on
- Virtual warehouses: sizing, auto-suspend and multi-cluster scalingHands-on
- Micro-partitions, pruning and cachingConcept
- Time Travel, Fail-safe and zero-copy cloningHands-on
- Credits, storage costs and resource monitorsConcept
Stand up a cost-safe sandbox
Create a database, two schemas and two warehouses, one sized for loading and one for analysis, each with auto-suspend. Add a resource monitor that alerts at seventy percent and suspends at one hundred percent of a small quota. Load a sample table, clone the database, drop a table in the clone and recover it with Time Travel.
Knowledge checkpoint with explained answers
02SQL, Data Modelling and Database Objects
Model analytical data in Snowflake with the right table types, views, semi-structured handling and reusable functions.
- Databases, schemas and table typesHands-on
- Views, secure views and materialised viewsHands-on
- Semi-structured data with VARIANT and FLATTENHands-on
- Analytical SQL: window functions, QUALIFY and CTEsHands-on
- Dimensional modelling in SnowflakeHands-on
- User-defined functions and stored proceduresHands-on
Model a grocery sales mart
Design and build a star schema for grocery sales with a type 2 product dimension. Load a JSON feed of promotions into a VARIANT column and flatten it into a promotion dimension. Write a deduplication query with QUALIFY, a secure view for store managers and a Python UDF that normalises unit sizes, and test each object.
Knowledge checkpoint with explained answers
PART 2Pipelines and Governance
03Data Ingestion, Snowpipe and ELT Pipelines
Load data in batch and continuously, and build incremental ELT pipelines that process each change exactly once.
- Stages, file formats and COPY INTOHands-on
- Storage integrations and external stagesHands-on
- Continuous loading with SnowpipeHands-on
- Streams and tasks for incremental ELTHands-on
- Dynamic tables for declarative pipelinesHands-on
- Connectors, third-party ingestion and change data captureConcept
An incremental pipeline that runs itself
Build a pipeline that auto-ingests daily sales files from cloud storage with Snowpipe, captures new rows with a stream and merges them into a curated table with a task. Replace one downstream step with a dynamic table and compare the two approaches. Drop a malformed file into the stage and show how the pipeline reports it without stopping.
Knowledge checkpoint with explained answers
04Governance, Security and Data Sharing
Secure and govern data with role hierarchies, policies and auditing, and share it live without copying.
- Role-based access control and role hierarchyHands-on
- Masking policies, row access policies and tagsHands-on
- Network policies, authentication and encryptionConcept
- Auditing with account usage and access historyHands-on
- Secure data sharing and reader accountsHands-on
- Marketplace listings, clean rooms and data classificationConcept
Share safely
Create a role hierarchy for engineers, analysts and a store-manager group. Mask customer email and phone for everyone except a privacy role, and add a row access policy so store managers see only their stores. Share a secure view of weekly sales with a second account, then prove from access history who queried it.
Knowledge checkpoint with explained answers
PART 3Performance, Serving and AI
05Snowpark, Performance Optimisation and BI Integration
Run Python transformations inside Snowflake, tune queries from evidence, and serve BI tools efficiently.
- Snowpark DataFrames in PythonHands-on
- Snowpark UDFs, stored procedures and packagesHands-on
- Reading query profilesHands-on
- Clustering, search optimisation and query accelerationHands-on
- Warehouse strategy and cost optimisationConcept
- Connecting BI tools and a shared semantic layerHands-on
Faster and cheaper, with evidence
Take three slow reporting queries on a large sales table. For each, read the query profile, apply one change such as a clustering key, a rewritten join or a different warehouse size, and measure runtime and credits before and after. Rewrite one pandas transformation in Snowpark and connect a dashboard to the tuned tables.
Knowledge checkpoint with explained answers
06Cortex AI, Generative AI, AI Agents and Capstone
Apply language model functions, retrieval, natural-language analytics and agents to governed data, measuring quality and cost, and plan the capstone.
- Cortex AI functions: summarise, classify, translate and completeConcept
- Cortex Search for retrieval-augmented generationHands-on
- Natural-language analytics with semantic modelsHands-on
- Building AI agents over structured and unstructured dataHands-on
- Governing AI in Snowflake: access, cost and evaluationConcept
- Capstone planning and reviewHands-on
Grounded answers over policies and sales
Load a set of synthetic store policy documents and index them for search. Build a small assistant that answers ten known-answer questions with citations and measure how many it gets right. Then use an AI function to classify a column of customer comments by theme and spot-check twenty results. Report the credit cost of each step.
Knowledge checkpoint with explained answers
Pellmont Grocers data and AI platform on Snowflake
Pellmont Grocers is a fictional regional grocery chain with sixty stores, a loyalty programme and a supplier network. You receive synthetic sales files, stock snapshots, a promotions feed in JSON, customer comments and a library of store policy documents. Build a governed Snowflake platform that reports sales and promotion uplift, shares stock data with suppliers safely, and answers staff policy questions with cited, evaluated AI. All data and the organisation are synthetic.
Platform, cost controls and access — Set up databases, warehouses, resource monitors and a role hierarchy before any data lands.
Continuous ingestion — Load sales, stock and promotions continuously and incrementally.
Modelled and governed data — Model the sales mart and protect sensitive customer data.
Performance, BI and sharing — Serve reports efficiently and share stock data with suppliers safely.
Governed AI assistant — Answer staff policy questions with citations and classify customer comments, with measured quality and cost.
Next course
Tell us about yourself or your team and we’ll let you know how and when you can take this course.