JSON in Snowflake Masterclass Hands-On

Name: JSON in Snowflake Masterclass Hands-On
Rating: 4.9 (4 reviews)

Learn all you need about semi-structured JSON data in Snowflake

Created byCristian Scutaru

Last updated 5/2026

English

What you'll learn

How to load and unload data in JSON format in and from Snowflake
How to query JSON data from a VARIANT data type
How to use built-in Snowflake functions for JSON objects and arrays
How to FLATTEN semi-structured data into tabular data
How to dynamically extract schema-on-read from JSON data
How to convert JSON data to other hierarchical formats
How to call the Snowflake COPY INTO commands for JSON data
How to design databases using both tabular and hierarchical topologies
How to create all sorts of simple to complex queries using JSON in Snowflake

Course content

8 sections • 60 lectures • 12h 13m total length

Welcome to this Course5:29
How to Benefit Most from this Course3:57
Create a Free Snowflake Trial Account5:21
Create a free Snowflake trial account to explore the platform with 30 days and up to $400 US credit, no credit card required, and learn to activate and connect programmatically.
Frequently Asked Questions2:04

Introduction to JSON Fundamentals2:44
Explore the JSON format—objects, arrays, and scalar values—and map them to Python dictionaries and lists, plus use viewers and validators to probe schema-on-read.
JSON Objects and Arrays15:39
JSON from Python Dictionaries and Lists7:55
Explore handling JSON in Python by mapping JSON objects to Python dictionaries and arrays, parsing with json loads and dumps, formatting, and accessing nested properties in a store of books.
JSON Viewers and Validators6:51
JSON Data Profiler and Modeler7:18
Hierarchical Data Formats9:59
JSON Graphical Representations7:01
JSONPath from XPath (Dot Notation)8:24
Test Your Knowledge

Introduction to JSON Insertion in Snowflake3:10
JSON Document Databases12:39
Dynamic JSON with SELECT20:13
Explore dynamic json creation and parsing in Snowflake using select with object and array constants, parse_json, and variant data types for robust json handling.
JSON Storage with INSERT12:46
OBJECT Creation Functions21:06
ARRAY Creation Functions13:37
JSON Data Visualization in Snowflake14:09
JSON Dot Notation in Snowflake17:35
JSON Extraction Functions10:33
Test Your Knowledge

Introduction to JSON Uploading in Snowflake2:30
Learn to bulk upload JSON data into Snowflake using the web UI wizard and COPY INTO, including STRIP_OUTER_ARRAY, schema inference, validation, table creation, and unloading.
Manual JSON File Upload9:09
Data Transfer in Snowflake20:21
Bulk JSON File Upload17:00
Upload JSON with STRIP_OUTER_ARRAY12:30
Upload JSON with Inferred Schema11:10
Upload Validation and Errors17:59
Unload JSON Data17:39
Test Your Knowledge

Introduction to JSON Transformations in Snowflake4:01
Discover json transformations in snowflake to query and transform json data, from hierarchical to relational, using flatten, lateral, and array or object manipulation.
OBJECT Manipulation Functions19:59
Explore Snowflake's JSON manipulation functions, including object_ and array_ operations, object pick, delete, and insert, emphasizing immutability, reconstruction on updates, and parsing controls.
ARRAY Manipulation Functions17:47
Higher-Order Functions for Array Elements8:24
LATERAL Keyword16:03
Flattening Arrays24:39
Flattening Dictionaries15:12
JSON from String Lists18:40
JSON from Row Aggregations23:31
Explore JSON aggregations in Snowflake masterclass hands-on, learn object_agg and array_agg, flatten arrays and objects, and compare array aggregation, list aggregation, and window usage.
JSON Transformations with UDFs19:05
JSON Transformations with Data Frames7:41
Explore JSON transformations in Snowflake using SQL and data frames, including Python and Snowpark approaches, with UDFs, stored procedures, and server-side JSON handling.
Test Your Knowledge

Introduction to JSON Querying in Snowflake4:09
Master JSON querying in Snowflake with dot notation and JSONPath equivalents, flatten arrays and objects, and explore explain plans, query profiles, and account-usage JSON metadata for data lineage.
JSONPath Equivalent in Snowflake13:19
More JSON Queries for Snowflake17:58
Even More JSON Queries for Snowflake11:17
Count Distinct Values with Arrays10:13
EXPLAIN Query in JSON Format15:06
Learn how to generate a json query execution plan with explain plan json and explain json, then convert it to tabular or text formats using a query ID in Snowflake.
Query Profile Info with JSON Data11:05
Explore how to retrieve and interpret query profiles in Snowflake using the get query operator stats function, including JSON data, execution plans, and execution details.
JSON Metadata for Data Lineage8:12
JSON Query Performance Optimizations5:45
Test Your Knowledge

Requirements

Basic SQL query programming skills
Basic Python programming skills
Basic beginner-level knowledge of Snowflake, as a data warehouse
Optional JavaScript programming skills
Optional knowledge of Streamlit
No prior knowledge of JSON or other semi-structured data format

Description

There is a growing demand on the market today to build and query hybrid relational databases, enhanced with JSON extensions. We used to have separate NoSQL document stores, like MongoDB or Couchbase. But most relational database systems today - including Snowflake - added powerful data types and built-in functions to store and query JSON along tabular data, from SQL. Learn how to properly upload, query and design relational-hierarchical databases, as you will need them more and more in your professional career or your job!

What you will learn

All you need to know about JSON used as a data storage format.
How to design hybrid databases in Snowflake, using tabular data extended with JSON hierarchies.
How to create and manipulate the two complex structures in JSON: OBJECT dictionaries and ARRAY lists.
How to use LATERAL FLATTEN, to explode array elements and key-value pairs into tabular rows.
How to aggregate key-values or row elements into single OBJECT or ARRAY values.
How to create all sorts of simple to complex queries for JSON data in Snowflake.
How to manually enter JSON data into Snowflake tables.
How to upload JSON files into tables, using the STRIP_OUTER_ARRAY option.
How to unload Snowflake table data into a JSON format.
How to switch between tabular and hierarchical topologies, to extract data.
How to use the JSON dot notation, to locate specific elements in the hierarchy.
How to interpret JSON data returned by system functions or as ACCOUNT_USAGE metadata.
How to create custom JSON data profilers and schema on-read extractors.
How to represent in a graphical manner JSON hierarchies.
How to perform JSON transformations with higher-order functions, UDFs or with Snowpark DataFrame.
How to find JSONPath equivalent queries in Snowflake.
How to transpose data between relational and hierarchical representations.
How to convert JSON to JSON, or other hierarchical formats, like YAML and XML.

My credentials for this course

The only world-class expert from Canada selected for their Snowflake Data Superhero program in 2021.
SnowPro Certification SME (Subject Matter Expert) - I helped create many SnowPro exam questions.
Passed five SnowPro certification exams to date, all on the first attempt.
Certified Professional Couchbase Developer - in querying their JSON document stores using SQL.
Expert in many NoSQL databases, including MongoDB and Couchbase JSON document stores.
Professional workplace experience with MongoDB NoSQL JSON document stores since 2012.

Enroll today, and learn how to master everything JSON in Snowflake!

[Disclaimer: We are not affiliated with or endorsed by Snowflake, Inc.]

Who this course is for:

Data Engineers collecting semi-structured data in their data pipelines
Database Administrators learning new design patterns for hybrid tabular-hierarchical data
Data Analysis who need querying hybrid data stores
Data Architects willing to learn how to flatten key-value JSON dictionaries and JSON arrays
SnowPro certification exam candidates, learning about LATERAL FLATTEN in detail
OLAP and OLTP practitioners, in need to understand how JSON fits into a relational model

JSON in Snowflake Masterclass Hands-On

What you'll learn

Explore related topics

Course content

Introduction4 lectures • 17min

JSON Fundamentals8 lectures • 1hr 6min

JSON Insertion in Snowflake9 lectures • 2hr 6min

JSON Uploading in Snowflake8 lectures • 1hr 48min

JSON Transformations in Snowflake11 lectures • 2hr 55min

JSON Querying in Snowflake9 lectures • 1hr 37min

JSON Modeling in Snowflake9 lectures • 2hr 22min

Wrapping Up2 lectures • 3min

Requirements

Description

Who this course is for: