JSON in Snowflake Masterclass Hands-On
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
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!
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
Instructor
- 4.6 Instructor Rating
- 689 Reviews
- 16,855 Students
- 21 Courses
[English] In only half a year on Udemy, most of my video courses became best-sellers and highest rated.
World-class expert in Snowflake AI Data Cloud. Former Snowflake "Data Superhero" and SnowPro Certification SME (Subject Matter Expert). With eight SnowPro exams, all passed from the first attempt.
Over 40 proctored certification exams passed in the last 3-4 years alone, all from the first attempt. Dozens on certifications in AWS/Azure/GCP, in Data Science and Machine Learning.
Over three decades in the software industry, as a hands-on data and solutions architect, technical manager and team lead, software and data engineer. Successful entrepreneur and independent consultant. Former Microsoft employee (there is still code of mine in Microsoft SQL Server and Microsoft Windows).
---
[Français] En seulement six mois sur Udemy, la plupart de mes cours vidéo sont devenus des best-sellers et les mieux notés.
Expert de classe mondiale en Snowflake AI Data Cloud. Ancien "Super-Héro des Données" en Snowflake et SME (Subject Matter Expert) de la certification SnowPro. Avec huit examens SnowPro, tous réussis dès la première tentative.
Plus de 40 examens de certification passés au cours des 3-4 dernières années seulement, tous réussis dès la première tentative. Des dizaines de certifications en AWS/Azure/GCP, en science des données.
Plus de trois décennies dans l'industrie du logiciel, en tant qu'architecte de données et de solutions, directeur technique et chef d'équipe, ingénieur en logiciel et en données. Entrepreneur à succès et consultant indépendant. Ancien employé de Microsoft (j'ai encore du code dans Microsoft SQL Server et Microsoft Windows).
---
[Româna] În doar o jumătate de an pe Udemy, majoritatea cursurilor mele video au devenit best-seller-uri și cele mai bine cotate.
Expert de talie mondială în Snowflake AI Data Cloud. Fost Snowflake „Data Superhero” și expert recunoscut in certificările SnowPro.
Peste 40 de examene de certificare trecute doar în ultimii 3-4 ani, toate din prima încercare. Zeci pe certificări în AWS/Azure/GCP, în Data Science și Machine Learning.
Peste trei decenii în industria software, ca arhitect de date și soluții, manager tehnic și lider de echipă, inginer software și de date. Antreprenor de succes și consultant independent. Fost angajat Microsoft (există cod de-al meu încă în Microsoft SQL Server și Microsoft Windows).