Snowflake Architecture · Topic 14 of 18
Object Hierarchy - Stages, File Formats, Functions, Procedures, Streams and Tasks
Place stages, file formats, UDFs, stored procedures, streams and tasks: stage types, format precedence, owner's rights and stream and task behavior.
- 5 min
- Stage types: user
@~, table@%, named internal, named external; named ones are schema objects. - File format precedence:
COPYoptions, then stage, then table. - UDFs return values and cannot run DDL; procedures run logic and DML; default is owner's rights.
What it is
Besides tables and views, a schema holds the objects that move and transform data: stages, file formats, functions, procedures, streams and tasks. They are all schema level objects, so each has a database and schema in its name, can be granted to roles, and is copied when the schema is cloned.
The exam tests where these objects live, what privileges they need, and the subtle behaviors: stage types, which file format wins, owner's versus caller's rights, and how a stream and task work together.
How it works
Stages
A stage is a named location for data files.
| Stage type | Reference | Notes |
|---|---|---|
| User stage | @~ | One per user; for that user's files; cannot be altered or dropped |
| Table stage | @%table_name | One per table; for files loaded into that table; cannot be altered or dropped |
| Named internal stage | @my_stage | A schema object; supports file format and encryption options; access is granted to roles |
| Named external stage | @my_ext_stage | A schema object pointing to cloud storage through a URL and a storage integration |
Named stages are schema objects, so they need USAGE or READ and WRITE privileges. For an external stage, use a storage integration rather than embedding credentials. A directory table can be enabled on a stage to list its files.
When a database or schema is cloned, external named stages are cloned and internal stages are cloned only with the INCLUDE INTERNAL STAGES option. Table stages in a clone start empty.
File formats
A file format is a named schema object that describes how to read or write staged files (type, delimiter, compression, null handling). One definition can be reused by many stages and COPY statements.
Precedence when several are set: options in the COPY statement override the stage definition, which overrides the table definition. Specify the format on the stage when many loads share it, and override inside COPY only for exceptions.
Functions
User defined functions (UDFs) are schema objects written in SQL, JavaScript, Python, Java or Scala. A UDF returns a value per row; a user defined table function (UDTF) returns rows. External functions call services outside Snowflake through an API integration.
Key facts:
- A UDF is called in a query by its qualified name. The caller needs
USAGEon the function and on its schema and database. - Use a secure UDF when the code must not be visible and when the function is shared.
- Code that needs packages or libraries can import them from a stage or an approved package set.
Procedures
Stored procedures run logic and can issue DDL and DML, which a UDF cannot. They are called with CALL, written in SQL (Snowflake Scripting), JavaScript, Python, Java or Scala.
| Rights model | Runs with | Notes |
|---|---|---|
| Owner's rights (default) | Privileges of the procedure owner | Callers need only USAGE on the procedure; good for controlled, narrow actions |
Caller's rights (EXECUTE AS CALLER) | Privileges of the calling role | The procedure can use the caller's session context and objects |
Choose owner's rights to give users a safe way to do a privileged task without granting the privilege itself. Choose caller's rights when the procedure should act only on what the caller can already access.
"Let analysts run a controlled operation on a table they cannot modify directly" points to an owner's rights procedure.
Streams and tasks
A stream records the change data (inserts, updates and deletes) of a table, view or directory table. It adds metadata columns such as METADATA$ACTION and METADATA$ISUPDATE and has an offset that moves forward only when the stream is consumed in a DML statement that commits.
Stream types: standard (inserts, updates, deletes), append only (inserts only) and insert only (for external tables and Iceberg tables that Snowflake does not manage). A stream becomes stale when its offset falls behind the table's change retention. Snowflake can extend retention for a table that has a stream, up to the MAX_DATA_EXTENSION_TIME_IN_DAYS limit, so consume streams regularly.
A task runs SQL, a stored procedure call or a Snowflake Scripting block on a schedule, or after another task in a task graph.
- A task uses either a user managed warehouse or serverless compute.
- The task owner role needs
EXECUTE TASKon the account, and serverless tasks needEXECUTE MANAGED TASK. - A
WHENclause withSYSTEM$STREAM_HAS_DATA('stream')lets a task skip runs when there is nothing new, which saves credits. - A new or cloned task is suspended; run
ALTER TASK ... RESUMEto start it.
When a schema containing streams and tasks is cloned, tasks arrive suspended and a cloned stream starts its history at the clone point, so unconsumed records of the source stream are not available in the clone.
Common mistakes
Dropping or altering a table stage. User and table stages cannot be altered or dropped; use a named stage when you need options or role-based access.
Assuming a clone has working tasks. Cloned tasks are suspended and must be resumed.
Letting a stream go stale. A stream that is not consumed within the retention window loses its change history and must be recreated.
Choosing a UDF to run DDL. UDFs cannot perform DDL or DML on tables; use a stored procedure.
Putting credentials in an external stage. Use a storage integration so secrets stay out of the stage definition.
What to remember for the exam
- Stage types: user
@~, table@%, named internal, named external; named ones are schema objects. - File format precedence:
COPYoptions, then stage, then table. - UDFs return values and cannot run DDL; procedures run logic and DML; default is owner's rights.
EXECUTE AS CALLERmakes a procedure run with the caller's privileges.- A stream tracks changes by offset; consuming it in committed DML advances the offset; unused streams go stale.
- Tasks need
EXECUTE TASK(serverless:EXECUTE MANAGED TASK) and start suspended after creation or cloning.
Check yourself
Which file format setting wins when COPY and the stage both define one?
The option in the
COPYstatement, which overrides the stage, which overrides the table.How do you let a task run only when there is new data in a stream?
Add a
WHEN SYSTEM$STREAM_HAS_DATA('stream_name')condition to the task.A role must perform a privileged update but must not hold the privilege. What object fits?
A stored procedure with owner's rights, with
USAGEgranted to that role.
Topic 14 of 18 in Snowflake Architecture
Test yourself on Snowflake Architecture
The full Snowflake ARA-C01 question bank has 687 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.