Snowflake Semi-Structured Staging, Procedures, UDFs and Secure Functions

1.Staging tables and querying semi-structured data with FLATTEN

Many pipelines land raw data in a staging layer, transform it, and copy it into downstream tables. Snowflake's own description of a CDC consumer follows this pattern: a pipeline in which only the staging-table data that changed since the last extraction is transformed and copied onward. When the raw data is hierarchical, the staging table usually holds it in a VARIANT column. JSON, Avro, ORC and Parquet data is typically loaded this way. The documentation's example table, car_sales, has one VARIANT column, src, containing nested arrays and objects.

You read a VARIANT with path notation. Dot notation follows a path like column:level1.level2. Bracket notation uses column['level1']['level2'] with single-quoted element names. Column names are case-insensitive, but element names are case-sensitive, so src:salesperson.name and src:Salesperson.Name are different paths. Element names that break SQL identifier rules, such as names containing a space, must be double-quoted.

SELECT src:salesperson.name
    FROM car_sales
    ORDER BY 1;

Data types matter when you put views over a staging table. In the documented output, the path query returns "Frank Beasley" with its JSON quotes still attached, and the page notes that with bracket notation values are retrieved as strings. A view that only projects paths therefore exposes semi-structured values, not typed relational columns. The provided sources go no further on how views and data types interact, for example on casting rules inside views.

Path notation works for single values. Arrays such as customer or vehicle need FLATTEN. FLATTEN is a table function that explodes a VARIANT, OBJECT or ARRAY into multiple rows. It produces a lateral view that can correlate with tables earlier in the FROM clause, which turns semi-structured data into a relational shape. Its optional arguments are PATH (which element to flatten), OUTER, RECURSIVE and MODE. OUTER defaults to FALSE, and in that case input rows that can't be expanded, for example because they have zero entries, are dropped from the output. With OUTER => TRUE, each such row produces one output row with NULL in KEY, INDEX and VALUE. For heavy querying of semi-structured data, the documentation suggests the search optimization service to improve performance.

Sources123

2.Stored procedures vs UDFs for data processing

Once the logic outgrows a SELECT, you can extend Snowflake with your own code. There are two main tools, and their purposes differ. A stored procedure generally performs administrative operations by executing SQL statements, and returning a value is optional. A user-defined function (UDF) calculates and returns a value, which it always does explicitly.

A procedure can bundle many operations into one call. For example, it can run the same cleanup across several tables with a cut-off date passed in as a parameter. It can also run with owner's rights, which lets the owner delegate operations to users who couldn't otherwise perform them. You write the handler in Java, JavaScript, Python, Scala or Snowflake Scripting, create it with CREATE PROCEDURE, and invoke it with CALL. JavaScript and Snowflake Scripting handlers must be in-line, while Java, Python and Scala handlers can be in-line or staged. Procedures fit naturally into pipelines because tasks can run them. Dynamic tables can't call them.

CREATE OR REPLACE PROCEDURE myproc(from_table STRING, to_table STRING, count INT)
  RETURNS STRING
  LANGUAGE PYTHON
  RUNTIME_VERSION = '3.12'
  PACKAGES = ('snowflake-snowpark-python')
  HANDLER = 'run'
as
$$
def run(session, from_table, to_table, count):
  session.table(from_table).limit(count).write.save_as_table(to_table)
  return "SUCCESS"
$$;

Sources456

3.UDTFs, external functions and their performance impacts

A scalar UDF returns one value per row. A user-defined table function (UDTF) returns a table. Using Java as the example, the handler class processes the rows passed to the UDTF and returns a tabular result. The rows are partitioned, either implicitly by Snowflake or explicitly in the call. The handler can have an initializer for partition-scoped state, a process method that handles each row, and an endPartition method that finishes the partition and can return a value for it. Implicit partitioning is best when the UDTF looks at each row in isolation and keeps no state across rows. For performance, Snowflake usually runs multiple instances of the handler in parallel. Remember the dynamic table limit from the pipeline side: a UDTF can appear in a dynamic table only inside a lateral join.

An external function calls code that runs outside Snowflake. Remote services can be written with any HTTP server stack, including serverless compute such as AWS Lambda. Snowflake reaches them through a proxy, configured with an API integration (CREATE API INTEGRATION) that stores the security information needed. To a SQL user, an external function behaves like any other UDF. It returns a value, which can be a VARIANT containing JSON. It accepts parameters, can be overloaded, and can appear in any clause where a UDF can, including inside a view definition or a larger expression.

select upper(zipcode_to_city_external_function(zipcode)) from address_table;

These choices determine where a transformation can run. Since external functions and stored procedures are excluded from dynamic table definitions, logic that depends on them has to be orchestrated with tasks. The provided sources don't give detailed latency or batching figures for external functions.

Sources789

4.Secure functions: hiding definitions and sensitive data

Sometimes the logic itself is sensitive, such as proprietary scoring or computations that could reveal underlying data. Adding the SECURE keyword when you create a UDF or stored procedure limits who can see its definition and helps limit the sensitive data a UDF could expose. Only users granted a role that owns the function can see the definition details. Everyone else still sees enough to call the function correctly.

In some cases, error messages related to secure functions may also be redacted. Secure functions sit alongside the other protections already covered. A stored procedure's choice of caller's rights or owner's rights determines what it can access, and Snowflake lists secure UDFs and procedures as the way to keep sensitive information from users who shouldn't see it.

Sources105

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.FLATTEN returns a row for every input row, even when the array is empty.Why is that wrong?

   OUTER defaults to FALSE, which drops rows that can't be expanded. Only OUTER => TRUE produces one NULL-filled row for each zero-row expansion.

   Covered in Staging tables and querying semi-structured data with FLATTEN

2. 2.A UDF is the right container for transformation steps that update or delete rows.Why is that wrong?

   DDL and DML belong in stored procedures. A UDF's job is to calculate and return a value used inside a SQL statement.

   Covered in Stored procedures vs UDFs for data processing

3. 3.Making a function SECURE hides everything about it, including its signature.Why is that wrong?

   Only definition details such as the body, imports, handler name and packages are hidden. Parameter types, return type, language, null handling and volatility stay visible.

   Covered in Secure functions: hiding definitions and sensitive data