How to export GA4 from BigQuery into Snowflake, and translate the cookbook sample queries.

Picture generated by AI (VQGAN+CLIP)

Building a decay function in SQL is not trivial, but fun. The best answer so far uses window functions, but can we do better with a JS UDTF in Snowflake? Find the results here

A UDTF solving the decaying scores SQL puzzle (image by author)

The most popular language on Reddit (other than English) will surprise you. To build this chart I analyzed almost a million Reddit comments with Snowflake and a Java UDTF (in less than 2 minutes).

Top languages on Reddit — full image + updated Tableau interactive (by author)

Predicting the future starts with understanding the past and this is no different when working with weather data. Just think how much weather affects agriculture, construction, transportation, and even consumer behavior. Let’s focus on Weather Source, one of the premium weather data providers in the Data Marketplace.

This example explores Snowflake’s geospatial capabilities, and how to enhance them using CARTO shared functions in the Snowflake Data Marketplace.

Watch on YouTube

Random points around a country

I used to load reddit comments onto BigQuery, now it’s time to upgrade my pipelines to Snowflake — and to share some of the nice surprises I found. Let’s get started with 261GB of them.

Querying for the subreddits with the most comments
  • Working with semi-structured data in Snowflake is fast, easy, and fun.
  • Snowflake understands JSON objects on load…

The Braze Engagement Benchmarks give Snowflake users access to industry-by-industry data on message engagement, app retention, user acquisition, and purchasing behavior, updated daily. All data in Benchmarks are anonymized and aggregated. The data are pulled from their customer base of over 1,000 global brands across 14 major industries and encompass the past year from the current date. Find here how to query them.

Photo by Anne Nygård on Unsplash

NOAA GSOD’s daily worldwide weather data is updated daily in Snowflake, and in this post we’ll make it even more useful. Check inside for pivots, geo-joins, finding the closest station to each city, and pattern matching with MATCH_RECOGNIZE().

Video on Youtube

The source

Knoema’s Environment Data Atlas in Snowflake
  • Having this data automatically refreshed in your account is cool!
  • Making this table…

Snowflake now let’s you easily create Java UDFs, which is an incredibly powerful and versatile feature. Let’s check it out with by running a library written in Kotlin — to detect written languages. Out of GitHub and into your SQL code, in 3 easy steps.

Detect written languages with a Java UDF

Quick example: Detect written languages

create function add(x integer, y integer)
returns integer
language java
handler='Test.add'
as
$$
class Test {
public static int add(int x, int y) {
return x + y;
}
}
$$;

Felipe Hoffa

Data Cloud Advocate at Snowflake ❄️. Originally from Chile, now in San Francisco and around the world. Previously at Google. Let’s talk data.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store