Introduction Back in April of 2021, I was confronted with a customer issue. They were building a Security Data Lake with Snowflake and one of their data sources was a very large file in JSON format. While Snowflake can natively ingest semi-structured formats (JSON, XML, Parquet, etc.), the max size of…

Introduction Back in April of 2021, I wrote a blog post that outlined a technique for dealing with JSON files that exceed Snowflake’s internal limit of 16MB (compressed). It presented a way to use the open source jq utility to pre-process the JSON files, and split them into smaller chunks that…

Introduction Snowflake, The Data Cloud, is an ANSI-compliant SQL relational database. Support for strongly-typed row & column data (aka “structured” data), is well, kind of a given… Support for structured data is basic table stakes for anything wanting to call itself a relational database. …

"Closed, proprietary data format, vendor lock-in, limited support for semi-structured data, limited to BI, SQL applications and decision support" Snowflake suffers from NONE OF THESE issues. Not one. You must be describing some "other" DW platform.

Some background… One of the recent additions to the Data Engineering capabilities of Snowflake was the introduction of Scheduled Tasks. A scheduled task is pretty much exactly what it sounds like: a way to take a single SQL statement or stored procedure call and execute it on a recurring schedule. (Note: these…

The saga continues… Introduction In Part I of this series, we met our initial cast of characters. The VARIANT datatype, the COPY command, and how to use the strip_outer_array parameter when loading XML content into a Snowflake table. In Part II, the heroes of the story emerged: the SQL functions GET()…

How to navigate repeating groups in XML structures with the FLATTEN function and LATERAL join method. — Introduction This is the third in a series of posts that cover tips and techniques for working with XML-based data in Snowflake. Part 1 of the series was a general introduction to Snowflake’s support for semi-structured data formats, and how to use the COPY statement to load XML files into a…

Introduction In Part I of this series on working with XML data in Snowflake, I gave a little refresher on the basics of XML syntax, and covered the basics of semi-structured data in Snowflake. I showed how to create a table with a VARIANT column, and how to stage an XML…

Internal Stages — What are they and how to use them — #2: Top-Secret Internal Stages! OK, well maybe not so secret, since they’re well-documented. Everybody knows about internal stages. But there are two additional types of internal stages that have special properties, and every Snowflake developer should understand how — and when — to use them. Recap We all know what Stages are for, right? They…