Chapter 5 of 5 · Snowflake DEA-C02

Data Transformation

UDFs, external functions, stored procedures, transactions, semi-structured and unstructured data.

  • 14topics
  • 1 h 2 minfast track
  • 2diagrams and exhibits
  • 25%of the exam

Topics in this chapter

Take them in order, or jump to the one you need.

  1. 1User-Defined Functions - SQL, JavaScript, Snowpark and Secure UDFsPick the right scalar UDF language in Snowflake: SQL, JavaScript, Python, Java and Scala, Snowpark registration, vectorized…4 min
  2. 2User-Defined Table Functions and Aggregate FunctionsLearn when to use a UDTF or a UDAF in Snowflake: handler methods, TABLE() calls, PARTITION BY, lateral joins and the accumulate…4 min
  3. 3External Functions - Create, Secure and UseHow Snowflake external functions call remote services through an API integration and proxy: setup, batching, JSON format…Diagram5 min
  4. 4Stored Procedures - Design, SQL Scripting and JavaScriptDesign Snowflake stored procedures: procedures vs UDFs, owner and caller rights, Snowflake Scripting blocks, cursors, exceptions…5 min
  5. 5Snowpark Stored Procedures and Transaction ManagementWrite Snowpark stored procedures in Python, Java or Scala and manage transactions: autocommit, BEGIN, COMMIT, ROLLBACK, DDL…5 min
  6. 6Semi-Structured Data - Traverse, Flatten and RebuildTransform JSON and other semi-structured data in Snowflake: VARIANT paths, casting, LATERAL FLATTEN, OBJECT_CONSTRUCT, ARRAY_AGG…5 min
  7. 7Unstructured Data - URLs, Directory Tables and the REST APIWork with files in Snowflake stages: scoped, file and pre-signed URLs, directory tables and refresh, processing files in UDFs…5 min
  8. 8Cortex AI Functions in Data PipelinesUse Snowflake Cortex AI functions in SQL pipelines to classify records, extract data from documents and audio, run text analytics…5 min
  9. 9Semantic Views, Semantic Analysis and Cortex Cost ControlDefine business metrics in semantic views, run semantic analysis with embeddings and Cortex Analyst, and control Cortex LLM cost…4 min
  10. 10Development Workflows, Workspaces and NotebooksChoose the right Snowflake development tools: Snowsight Workspaces, Snowflake Notebooks, Snowflake CLI and Git, and manage code…4 min
  11. 11Git Integration, dbt Projects and Deployment PipelinesConnect Git to Snowflake, run dbt projects natively, and build deployment pipelines with EXECUTE IMMEDIATE FROM, Snowflake CLI…4 min
  12. 12Testing, Validation and Environment ManagementTest and validate Snowflake pipelines with dbt tests, data metric functions and Snowpark local testing, and manage dev, test and…4 min
  13. 13Snowpark Architecture, Querying and FilteringLearn how Snowpark pushes DataFrame code down to Snowflake as SQL, how lazy evaluation works, and how to query and filter data…Diagram4 min
  14. 14Snowpark Transformations and DataFrame ManipulationTransform data with Snowpark DataFrames: aggregations, joins, window functions, new columns, null handling, pandas conversion and…4 min

About this chapter

What this chapter covers

This chapter of the SnowPro Advanced: Data Engineer DEA-C02 exam tests how you reshape data inside Snowflake: which kind of function or procedure to write, in which language, and how to handle nested and file based data. Questions are scenarios that begin "A Data Engineer needs to ...", and they usually come down to choosing one feature or one parameter.

The chapter also covers semantic views, Snowflake Cortex features, development workflows and Snowpark DataFrames. Those are later pages of this chapter, written separately, and they follow the pages listed here.

How the topics fit together

Custom logic comes in a small set of shapes, and the first skill is matching the requirement to the shape.

Calculation per rowuse aUDFscalarMany rows from one rowuse aUDTFTABLE in FROMOne value from many rowsuse aUDAFGROUP BYLogic outside Snowflakeuse anExternal functionAPI integrationDDL, DML and control flowuse aStored procedureCALL
  • UDFs compute a value inside a query and cannot change objects.
  • UDTFs and UDAFs change the number of rows in or out.
  • External functions send batches of rows to a remote service.
  • Stored procedures orchestrate statements, and transactions make them all or nothing.
  • Semi-structured data is flattened with FLATTEN and rebuilt with OBJECT_CONSTRUCT and ARRAY_AGG.
  • Unstructured data stays in stages and is reached by URLs, directory tables and code.
Exam tip

Ask three questions of every scenario. Does it return a value, rows or an action? Where does the code run? Who is allowed to see the definition or the data? Most answers follow from those.

How to use these pages

Each topic page has the same shape, so you can skim for what you need.

  • What it is: the idea in plain words.
  • How it works: the mechanism, with diagrams, SQL and comparison tables.
  • Common mistakes: the classic traps.
  • What to remember for the exam: the facts to memorize.
  • Check yourself: three questions to test your understanding.

Read the pages in order the first time. The procedure pages assume you know the function pages, and the transaction rules are easier after the procedure basics.

Ready for more practice?

The full Snowflake DEA-C02 question bank has 709 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.