Json In Snowflake Masterclass Hands-On

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

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

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

What you will learnAll 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 enter manually enter JSON data into Snowflake tablesHow 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 XMLMy credentials for this courseThe only world-class expert from Canada selected for their Snowflake Data Superhero program in 2021.SnowPro Certification SME (Subject Matter Expert) - many SnowPro exam questions have been created by me.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!

Overview

Section 1: Introduction

Lecture 1 Welcome to this Course

Lecture 2 How to Benefit Most from this Course

Lecture 3 Create a Free Snowflake Trial Account

Lecture 4 Frequently Asked Questions

Section 2: JSON Fundamentals

Lecture 5 Introduction to JSON Fundamentals

Lecture 6 JSON Objects and Arrays

Lecture 7 JSON from Python Dictionaries and Lists

Lecture 8 JSON Viewers and Validators

Lecture 9 JSON Data Profiler and Modeler

Lecture 10 Hierarchical Data Formats

Lecture 11 JSON Graphical Representations

Lecture 12 JSONPath from XPath (Dot Notation)

Section 3: JSON Insertion in Snowflake

Lecture 13 Introduction to JSON Insertion in Snowflake

Lecture 14 JSON Document Databases

Lecture 15 Dynamic JSON with SELECT

Lecture 16 JSON Storage with INSERT

Lecture 17 OBJECT Creation Functions

Lecture 18 ARRAY Creation Functions

Lecture 19 JSON Data Visualization in Snowflake

Lecture 20 JSON Dot Notation in Snowflake

Lecture 21 JSON Extraction Functions

Section 4: JSON Uploading in Snowflake

Lecture 22 Introduction to JSON Uploading in Snowflake

Lecture 23 Manual JSON File Upload

Lecture 24 Data Transfer in Snowflake

Lecture 25 Bulk JSON File Upload

Lecture 26 Upload JSON with STRIP_OUTER_ARRAY

Lecture 27 Upload JSON with Inferred Schema

Lecture 28 Upload Validation and Errors

Lecture 29 Unload JSON Data

Section 5: JSON Transformations in Snowflake

Lecture 30 Introduction to JSON Transformations in Snowflake

Lecture 31 OBJECT Manipulation Functions

Lecture 32 ARRAY Manipulation Functions

Lecture 33 Higher-Order Functions for Array Elements

Lecture 34 LATERAL Keyword

Lecture 35 Flattening Arrays

Lecture 36 Flattening Dictionaries

Lecture 37 JSON from String Lists

Lecture 38 JSON from Row Aggregations

Lecture 39 JSON Transformations with UDFs

Lecture 40 JSON Transformations with Data Frames

Section 6: JSON Querying in Snowflake

Lecture 41 Introduction to JSON Querying in Snowflake

Lecture 42 JSONPath Equivalent in Snowflake

Lecture 43 More JSON Queries for Snowflake

Lecture 44 Even More JSON Queries for Snowflake

Lecture 45 Count Distinct Values with Arrays

Lecture 46 EXPLAIN Query in JSON Format

Lecture 47 Query Profile Info with JSON Data

Lecture 48 JSON Metadata for Data Lineage

Lecture 49 JSON Query Performance Optimizations

Section 7: JSON Modeling in Snowflake

Lecture 50 Introduction to JSON Modeling in Snowflake

Lecture 51 Hierarchical to Relational Model

Lecture 52 Relational to Hierarchical Model

Lecture 53 JSON to JSON Conversions

Lecture 54 JSON-Based Views

Lecture 55 Direct Access to Staged JSON Files

Lecture 56 External Tables

Lecture 57 Design Patterns for Data Pipelines

Lecture 58 Design Patterns for Data Modeling

Section 8: Wrapping Up

Lecture 59 Congratulations, You Made It!

Lecture 60 Bonus Lecture

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