JSON in Snowflake Masterclass Hands-On

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.

Learn the basics of JSON objects and arrays, including key value pairs, nesting, and arrays of objects; understand strings, numbers, booleans, and null values in a hierarchical format.

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.

Access JSON viewers, validators, and formatters to inspect and format data. Build quick local apps with Streamlit to render JSON with collapsible nodes.

Explore a Streamlit JSON data profiler and modeler for Snowflake that detects read-time schema, profiles documents, infers data types and optional properties, and maps JSON to relational structures.

Explore converting CSV data to JSON, then transform JSON into XML and YAML using Python, Pandas, and json.dumps, building hierarchical, recursive data structures like employee trees.

Explore how to visualize JSON hierarchies using Graphviz, Plotly charts, and D3, generating treemaps, icicles, sunbursts, Sankey diagrams, and interactive network graphs.

Explore how JSON path adapts XPath concepts to JSON in Snowflake, using dot notation, array indices, and filters to query JSON data and compare with XPath.

Master json insertion in Snowflake by populating relational tables with sql, using parse_json, object_construct, and dot notation, exploring insertion, ctas, and file-based data entry.

Explore json document databases like mongodb and couchbase, their json storage formats, and how snowflake handles json with object and array types, json lines, and related semi-structured data formats.

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.

Discover saving JSON in Snowflake by storing in a variant column, parsing strings into objects or arrays, and using select or ctas for automatic JSON parsing.

Explore Snowflake's built-in object and array constructors. Learn how to build objects from key-value pairs or separate arrays, handle nulls, and understand JSON vs SQL nulls, quotes, and key ordering.

Explore array creation functions in Snowflake, including array construct, reconstruct, and array generate range; learn null handling, string to array conversions, and type casting with :: for precise data types.

Explore how to visualize and validate JSON data stored in Snowflake using the web UI, SQL queries, Python connectors, Snowpark, and Streamlit applications.

Explore Snowflake's JSON dot notation to navigate JSON data, access objects and arrays by key or index, and cast values to strings or timestamps in variant data.

Explore json extraction functions in snowflake, comparing dot notation with built-in get, get path, and get pass for locating json elements in arrays and objects, parse text and parse json.

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.

Learn to upload json data to snowflake via the from file wizard, infer schema, and copy into a new table with a variant column and temporary json format using stage.

Learn to transfer json data in Snowflake using parse_json and variant, loading it into tables via stages, with put, get, and copy into table.

Bulk upload json data to snowflake by staging local files with json formats and loading via copy into a table, including ndjson and gzipped files.

Explore uploading JSON to Snowflake with strip outer array, comparing top-level array, single object, and newline-delimited formats, and learn how validation mode and error handling affect ingestion.

Apply Snowflake's infer schema to JSON files to automatically determine metadata and create a table template. Load multiple JSON files with array aggregation and object construct, then inspect inferred columns.

Learn to validate JSON data during ingestion, diagnose copy into errors, and handle relaxed formats like JSON5, using validation mode and NDJSON to ensure successful Snowflake loading.

Learn to unload json data from Snowflake by constructing json objects with object_construct and aggregating them into a json array with array_agg. Understand stage export and file formats.

Discover json transformations in snowflake to query and transform json data, from hierarchical to relational, using flatten, lateral, and array or object manipulation.

Explore Snowflake's JSON manipulation functions, including object_ and array_ operations, object pick, delete, and insert, emphasizing immutability, reconstruction on updates, and parsing controls.

Explore Snowflake array manipulation functions, including construction, indexing, concatenation, intersection, overlap, flattening, slicing, and conversion to strings, with handling for nulls and nested arrays.

Explore snowflake's higher order functions for arrays, including filter and transform, using inline lambda expressions to filter and transform elements without flattening or user defined functions.

Explore how the lateral keyword enables flattening arrays and dictionaries in snowflake, turning each element into a separate row in an inline view.

Master the flatten function in Snowflake to explode arrays and objects, using lateral table flatten, recursive options, and JSON data techniques for nested structures.

Learn to flatten JSON in Snowflake by exploding arrays and object key-value pairs with the flatten table function, using input, pass, outer, recursive, and mode options.

Explore how Snowflake converts string lists to arrays and tables, using split and string token, and filter with the qualify clause alongside window functions.

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.

Explore JSON transformations with user defined functions in Snowflake, using Python and JavaScript to solve array and object problems alongside SQL solutions, including merging arrays, removing duplicates, and unpivoting data.

Explore JSON transformations in Snowflake using SQL and data frames, including Python and Snowpark approaches, with UDFs, stored procedures, and server-side JSON handling.

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.

Explore how to use JSONPath and XPath concepts in Snowflake, translate JSONPath expressions to Snowflake queries, and work with nested objects, arrays, and flattening to extract data.

Explore practical JSON querying in Snowflake by solving real-world problems from Stack Overflow, using generator and range, flatten, parse JSON, array and object aggregation, list aggregation, and joins.

Explore advanced json queries in Snowflake by flattening arrays to apply sql aggregates, parsing json into variants, and pivoting json data to map values 1-to-1.

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.

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.

Learn how to optimize JSON queries in Snowflake by storing JSON as variant, using columnar storage and flattening judiciously, and applying transform and filter functions for better performance.

Learn to load and query json in Snowflake, parse dynamic json, upload json files to tables, and model data as tabular or hierarchical using views and external tables.

Explore converting hierarchical JSON into relational tables by denormalizing JSON keys into tabular columns, using dot notation and flattening nested objects and arrays, including recursive CTE patterns for hierarchies.

Convert relational data to JSON by denormalizing to nested objects and arrays with object_construct and array_aggregate, including managers, subordinates, and projects.

Learn to transform JSON to JSON within Snowflake using plain SQL, handle hierarchical structures with flatten, object_construct, and array aggregation, and build manager subordinate graphs for visualization.

Learn how to expose json data as tabular through json-based views in Snowflake, flattening json keys into columns and using array and object aggregations.

Learn direct access to staged JSON files in Snowflake, using at stage syntax, flattening arrays, and querying JSON data with views rather than loading into tables.

Explore creating external tables in Snowflake with external stages on S3, inferring schema from JSON and querying data via variant columns.

Explore design patterns for data pipelines in Snowflake, focusing on JSON ingestion and data modeling. Compare ETL and ELT, stage versus final tables, and JSON validation techniques.

Learn patterns for modeling json data in Snowflake, balancing relational and json storage, using variant types, respecting the 16 mb per variant cell limit, and exposing json via views.

Celebrate mastering JSON in Snowflake, including loading, uploading, querying, and transforming JSON data, and moving between relational and JSON structures.