What you will be able to do
- Choose between table types based on where the data is stored and the workload
- Pick a user, table or named stage, and explain why named file formats and named stages are reusable schema objects
- Explain how owner's rights and caller's rights change what a stored procedure can do
- Describe how streams and tasks fit together to build change-driven pipelines inside a schema
1.Tables: the core schema object
Tables, views, functions and stages all live inside a schema in Snowflake. Tables are where data is stored. Standard Snowflake tables can hold structured and semi-structured data. The table type you pick has architectural consequences. Apache Iceberg tables keep their data and metadata in external cloud storage that you manage, outside Snowflake. They suit existing data lakes and lakehouses. Hybrid tables use index-based random reads and writes for low latency and high throughput. They support row locking and enforce unique and referential integrity constraints, which makes them suitable for transactional (Unistore) workloads.
| Table type | Where data lives | Typical fit |
|---|---|---|
| Snowflake table | Snowflake-managed storage | Structured and semi-structured analytical data |
| Apache Iceberg table | External cloud storage you manage | Existing data lakes and lakehouses |
| Hybrid table | Snowflake | Low-latency transactional workloads with row locking and constraints |
Checkpoint 1 of 6· Check yourself
An application needs enforced unique constraints and row-level locking for transactional writes, alongside analytical tables in the same platform. Which table type fits?
Hybrid tables are built for transactional workloads. They support row locking and enforce unique and referential integrity constraints.
“Hybrid tables are optimized for low latency and high throughput by using index-based random reads and writes.”Source: docs.snowflake.com
Sources1
2.Stages and file formats
A stage is the place where data files are stored ('staged') before they are loaded into a table. Snowflake gives every user and every table an internal stage automatically, and you can also create named internal stages. Which one to use depends on who needs the files and where they will be loaded. A user stage, referenced as @~, works when only one user accesses the files, even if they go into several tables. It is the wrong choice when several users need the files. User stages also can't be altered or dropped, and they don't hold file format options, so those options have to go into every COPY INTO command.
That limitation is why a named file format is useful. A file format is a schema-level object that describes a set of staged data so it can be accessed or loaded. Once it is defined, loads can refer to it by name instead of repeating parsing options each time.
CREATE [ OR REPLACE ] [ { TEMP | TEMPORARY | VOLATILE } ] FILE FORMAT [ IF NOT EXISTS ] <name>
[ TYPE = { CSV | JSON | AVRO | ORC | PARQUET | XML } [ formatTypeOptions ] ]Checkpoint 2 of 6· Check yourself
A team wants a shared landing area that several engineers upload to, with file format options attached to the stage itself. Which stage type fits?
User stages are meant for a single user, can't be altered or dropped, and don't support file format options. A named stage is the shared, configurable choice.
“User stages do not support setting file format options.”Source: docs.snowflake.com
3.Functions and stored procedures
Functions and procedures are the code objects that live in a schema. A user-defined function (UDF) extends the built-in functions. It always returns a value from an expression, and you call it the same way you call a built-in function. A scalar UDF returns one output row for each input row. A UDF looks a lot like a stored procedure, but the two differ in important ways. The difference that matters most for architecture is whose privileges a procedure runs with.
A stored procedure runs with either caller's rights or owner's rights, never both. The creator picks one when creating the procedure, and owner's rights is the default. The owner can switch it later with ALTER PROCEDURE. A caller's rights procedure uses the caller's privileges and can read the caller's session, such as session variables. An owner's rights procedure runs mostly with its owner's privileges. That lets the owner delegate one specific job, such as cleaning up old data, to another role without giving that role broad privileges like deleting all data from the table. So a calling role can do things through the procedure that it couldn't do directly.
Checkpoint 3 of 6· Check yourself
You want a support role to purge rows older than 90 days from one table without ever holding general DELETE on it. Which design matches the documented purpose?
Owner's rights procedures exist so an owner can delegate a narrow task without granting the broader privilege.
“the owner can delegate specific administrative tasks, such as cleaning up old data, to another role without granting that role more general privileges”Source: docs.snowflake.com
Checkpoint 4 of 6· Exam question
A data platform team is designing a schema named ANALYTICS.SALES that will host the full set of objects needed for an ELT pipeline: raw file landing, format parsing, incremental change capture, and scheduled transformation logic, in addition to the tables and views end users will query. Which of the following are valid schema-level object types the team can create directly inside this schema to support that pipeline? (Select all that apply)(Select 4)
Correct answers: A, B, C, D — A stage object that references the external or internal location where raw source files are landed before loading.; A file format object that defines how staged files such as CSV or Parquet should be parsed during a load.; A stream object that records the change data capture information for a table so downstream tasks can consume it.; A task object that runs a scheduled SQL statement or calls a stored procedure to transform newly captured changes.
- A. Correct - stages are schema-level objects and are the standard landing point for files before a COPY INTO load brings them into a table.
- B. Correct - file formats are schema-level objects that define parsing rules like delimiter or compression and are referenced by stages or COPY INTO statements.
- C. Correct - streams are schema-level objects that track row-level inserts, updates, and deletes on a source table for consumption by downstream logic.
- D. Correct - tasks are schema-level objects that execute on a schedule or trigger and commonly consume a stream's captured changes to drive transformation.
- E. Incorrect - virtual warehouses are account-level compute resources, not schema-level objects, and a warehouse is never scoped to or owned by a single schema.
- F. Incorrect - an organization is the top-most container above accounts and has no schema-level equivalent; schemas cannot group or contain organization-level constructs.
4.Streams and tasks
A stream is a schema object that records DML changes (inserts, including COPY INTO, plus updates and deletes) on a source object so the changes can be acted on. This is change data capture. A stream can be created on standard tables, including shared tables, and on views, including secure views. It can also track directory, dynamic, Iceberg, event and external tables. A stream holds no table data itself, only an offset into the source's version history. When the first stream on a table is created, Snowflake adds hidden change-tracking columns to that table. For a stream on a view, change tracking has to be enabled explicitly on the view and its underlying tables.
Tasks automate the work. A task can run on a schedule or be triggered by events, such as new data arriving in a stream. It can run SQL or a stored procedure, and several tasks can be chained into a task graph. For compute, a task is either serverless, where Snowflake predicts and assigns resources, or runs on a user-managed virtual warehouse. Snowflake suggests creating a dedicated task administrator role for this work.
Checkpoint 5 of 6· Put it in order
Put the documented task setup steps in order
- 1.Manually test it with EXECUTE TASK
- 2.Let it run continuously with ALTER TASK … RESUME
- 3.Define the task with CREATE TASK
- 4.Create a task administrator role
- 5.Refine it as needed with ALTER TASK
The role comes first so the later commands can run. Then you define the task, test it by hand, resume it, and adjust it.
“Create a task administrator role that can run the commands in the following steps.”Source: docs.snowflake.com
Checkpoint 6 of 6· Check yourself
What does a stream physically store?
A stream works like a bookmark. It stores an offset and returns change records from the source's version history plus hidden change-tracking columns.
“Note that a stream itself does not contain any table data.”Source: docs.snowflake.com
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.A stored procedure runs with the caller's privileges unless it is told otherwise.Why is that wrong?
The default is owner's rights. The procedure mostly uses its owner's privileges, so a caller can do things through it that their own role can't do directly.
Covered in Functions and stored procedures
2.A user stage can be configured with file format options and dropped when it's no longer needed, just like a named stage.Why is that wrong?
User stages can't be altered or dropped and don't support file format options. Format and copy options have to go into the COPY INTO command.
Covered in Stages and file formats
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“You can use Snowflake tables to store structured and semi-structured data.”
↩︎ Tables: the core schema object“Iceberg tables store their data and metadata files in an external cloud storage location”
↩︎ Tables: the core schema object“Hybrid tables are optimized for low latency and high throughput by using index-based random reads and writes.”
↩︎ Checkpoint - 2.
“By default, each user and table in Snowflake is automatically allocated an internal stage for staging data files to be loaded.”
↩︎ Stages and file formats“a convenient option if your files will only be accessed by a single user”
↩︎ Stages and file formats“Unlike named stages, user stages cannot be altered or dropped.”
↩︎ Exam trap 2“User stages do not support setting file format options.”
↩︎ Checkpoint - 3.
“Creates a named file format that describes a set of staged data to access or load into Snowflake tables.”
↩︎ Stages and file formats - 4.
“UDFs you create can be called in a way similar to built-in functions.”
↩︎ Functions and stored procedures“A UDF is like a stored procedure, but the two differ in important ways.”
↩︎ Functions and stored procedures - 5.https://docs.snowflake.com/en/developer-guide/stored-procedure/stored-procedures-rightsOfficial docs
“It cannot run with both at the same time.”
↩︎ Functions and stored procedures“by executing an ALTER PROCEDURE command”
↩︎ Functions and stored procedures“At the time that the stored procedure is created, the creator specifies whether the procedure runs with owner’s rights or caller’s rights.”
↩︎ Exam trap 1“The default is owner’s rights.”
↩︎ Prediction“the owner can delegate specific administrative tasks, such as cleaning up old data, to another role without granting that role more general privileges”
↩︎ Checkpoint - 6.
“A stream object records data manipulation language (DML) changes made to tables, including inserts (including COPY INTO), updates, and deletes”
↩︎ Streams and tasks“for streams on views, change tracking must be enabled explicitly for the view and underlying tables”
↩︎ Streams and tasks“Note that a stream itself does not contain any table data.”
↩︎ Checkpoint - 7.https://docs.snowflake.com/en/user-guide/tasks-introOfficial docs
“Tasks can run at scheduled times or can be triggered by events, such as when new data arrives in a stream.”
↩︎ Streams and tasks“Serverless tasks: Snowflake predicts resources that are needed and assigns them automatically.”
↩︎ Streams and tasks“For complex workflows, you can create sequences of tasks called task graphs.”
↩︎ Streams and tasks“Create a task administrator role that can run the commands in the following steps.”
↩︎ Checkpoint