Snowflake AI Data Cloud Features & Architecture · Topic 5 of 17
UDFs, Stored Procedures, Shares, ML Models and Applications
Tell UDFs from stored procedures and owner's rights from caller's rights, and see how shares, Model Registry models and Native Apps fit for COF-C03.
- 6 min
- UDF: returns a value, used in SQL expressions; UDTF returns a table in the FROM clause.
- Stored procedure: called with CALL, can run DDL and DML and procedural logic.
- Both support SQL, JavaScript, Python, Java and Scala; procedures in SQL use Snowflake Scripting.
What it is
These objects add code, collaboration and apps to Snowflake. UDFs and stored procedures hold custom logic. A share gives other accounts read access to data. An ML model object stores a trained model with versions. An application is an installed Snowflake Native App.
The exam mainly tests the differences: when to use a function instead of a procedure, whose privileges a procedure runs with, and which level each object lives at.
How it works
UDFs compared with stored procedures
| User-defined function (UDF) | Stored procedure | |
|---|---|---|
| How it is used | Inside a SQL expression, for example SELECT my_udf(col) FROM t | Called on its own with CALL my_proc() |
| Purpose | Calculate and return a value (or a table) | Perform a set of steps, including DDL and DML |
| Returns | Always returns a value; a table for a UDTF | Optional single value (some languages can return a table) |
| Languages | SQL, JavaScript, Python, Java, Scala | SQL (Snowflake Scripting), JavaScript, Python, Java, Scala |
| Typical example | Mask a string, convert a unit, score a row | Nightly cleanup, dynamic SQL, admin automation |
A scalar UDF returns one value per input row. A user-defined table function (UDTF) returns rows and is called in the FROM clause with TABLE(...).
A secure UDF hides its definition from users who do not own it, and is used when the logic itself is sensitive or the function is shared. External functions call code outside Snowflake through an API integration.
Owner's rights and caller's rights
Stored procedures run with the privileges of either the owner or the caller:
| Mode | Runs with | Typical use |
|---|---|---|
Owner's rights (EXECUTE AS OWNER, the default) | The privileges of the role that owns the procedure | Let users perform a controlled task on objects they cannot access directly |
Caller's rights (EXECUTE AS CALLER) | The privileges and session context of the caller | Utility procedures that should act only on what the caller can already reach |
If an analyst has no SELECT on a table but the procedure still succeeds, the procedure is running with owner's rights, which is the default.
Shares
A share is the object behind Secure Data Sharing. The provider creates it, grants privileges on a database and its objects to it, and adds consumer accounts:
CREATE SHARE sales_share;
GRANT USAGE ON DATABASE sales TO SHARE sales_share;
GRANT USAGE ON SCHEMA sales.public TO SHARE sales_share;
GRANT SELECT ON TABLE sales.public.orders TO SHARE sales_share;
ALTER SHARE sales_share ADD ACCOUNTS = partner_org.partner_acct;
The consumer creates a read-only database from the share. No data is copied; the consumer queries the provider's storage directly and pays for its own compute (reader accounts, where the provider pays, are the exception). Sharing in detail is covered in the Data Collaboration chapter.
ML models
A model is a schema-level object in the Snowflake Model Registry. It stores a trained machine learning model together with its versions, and one version is the default.
Models are usually logged from Python with the snowflake-ml-python registry API (log_model). Inference can then run in Python or in SQL by calling a model method, for example MODEL(mydb.ml.churn_model)!predict(...). Without a version name, the default version is used.
Because a model is a governed schema object, access is controlled with normal privileges, like any table or view.
Applications
An application is a Snowflake Native App installed in an account. Native Apps are built with the Snowflake Native App Framework:
- The provider builds an application package containing a manifest, a setup script and the app's code and data.
- The provider publishes it through a listing, privately or on Snowflake Marketplace.
- The consumer installs it, which creates an application object in the consumer's account.
The app runs inside the consumer's account, so the consumer's data does not leave it. The provider's code can be protected from viewing, and the consumer grants the app only the privileges it needs.
How the exam asks about this
- "A function must be used in a SELECT list to convert currencies per row" -> scalar UDF.
- "A routine must truncate staging tables and run several MERGE statements" -> stored procedure.
- "A partner needs live read access to tables without copying data" -> a share.
Common mistakes
Calling a stored procedure inside SELECT. Procedures are run with CALL. Only functions can be used inside a SQL expression.
Assuming procedures run as the caller by default. The default is owner's rights. Caller's rights must be declared with EXECUTE AS CALLER.
Thinking a consumer gets a copy of shared data. The consumer reads the provider's data in place. Changes by the provider are visible immediately.
Treating a model as a file in a stage. In the Model Registry a model is a schema object with versions and privileges, not a loose file.
What to remember for the exam
- UDF: returns a value, used in SQL expressions; UDTF returns a table in the FROM clause.
- Stored procedure: called with CALL, can run DDL and DML and procedural logic.
- Both support SQL, JavaScript, Python, Java and Scala; procedures in SQL use Snowflake Scripting.
- Procedures default to owner's rights; EXECUTE AS CALLER switches to caller's rights.
- A share grants read-only access with no data copy; the consumer pays for its compute.
- Models live in the Model Registry as versioned schema objects with a default version.
- An application is an installed Snowflake Native App, created from an application package or listing.
Shares and applications are account-level objects even though the outline lists them with database objects. Models, UDFs and procedures are schema-level.
Check yourself
Which object should you use to return a calculated value for every row in a SELECT?
A scalar UDF, because functions can be used inside SQL expressions and stored procedures cannot.
An analyst without SELECT on a table can still run a procedure that reads it. Why?
The procedure runs with owner's rights, the default, so it uses the owning role's privileges.
What does a consumer account receive when a share is added to it?
Read-only access to the shared objects in place, with no data copied; the consumer uses its own warehouse.
Topic 5 of 17 in Snowflake AI Data Cloud Features & Architecture
Test yourself on Snowflake AI Data Cloud Features & Architecture
The full Snowflake SnowPro Core COF-C03 question bank has 845 questions, with timed practice exams and an explanation for every answer.
This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.