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
In a nutshell
  • 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.

Custom logicreturns value in SQLUDFused in SELECTCustom logicruns steps and DDLStored procedureCALLDataread only accessShareconsumer accountTrained modelversionedModelModel RegistryApp packageinstalled asApplicationconsumer account

How it works

UDFs compared with stored procedures

User-defined function (UDF)Stored procedure
How it is usedInside a SQL expression, for example SELECT my_udf(col) FROM tCalled on its own with CALL my_proc()
PurposeCalculate and return a value (or a table)Perform a set of steps, including DDL and DML
ReturnsAlways returns a value; a table for a UDTFOptional single value (some languages can return a table)
LanguagesSQL, JavaScript, Python, Java, ScalaSQL (Snowflake Scripting), JavaScript, Python, Java, Scala
Typical exampleMask a string, convert a unit, score a rowNightly 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:

ModeRuns withTypical use
Owner's rights (EXECUTE AS OWNER, the default)The privileges of the role that owns the procedureLet users perform a controlled task on objects they cannot access directly
Caller's rights (EXECUTE AS CALLER)The privileges and session context of the callerUtility procedures that should act only on what the caller can already reach
Analyst roleEXECUTE on procedureCALLOwner's rights procedureowner privilegesSELECTRestricted tableno direct grant needed
Exam tip

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:

  1. The provider builds an application package containing a manifest, a setup script and the app's code and data.
  2. The provider publishes it through a listing, privately or on Snowflake Marketplace.
  3. The consumer installs it, which creates an application object in the consumer's account.
Providerapplication packagepublished viaListingMarketplace or privateinstalled asApplicationconsumer accountruns onConsumer datagranted to the app

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.
Watch out

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.