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

Let’s say you have a health and fitness app, and you’re wondering: What’s the best day to send notifications to your users? It’s Monday:

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

To access the daily NOAA GSOD weather data, just go to the Marketplace and create a database with Knoema’s Environment Data Atlas in your account.

Knoema’s Environment Data Atlas in Snowflake

With a couple of exploratory queries you’ll notice that:

  • Having this data automatically refreshed in your account is cool!
  • Making this table useful is not that easy, because:
  • Stations have a lat, lon — but not a way to tell to which city or zip-code they belong.
  • The original NOAA rows for each day have been split into multiple rows for each day — with each row containing only one value for…

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

Snowflake has made it really easy to create Java UDFs. You just need to do something like this:

create function add(x integer, y integer)
returns integer
language java
class Test {
public static int add(int x, int y) {
return x + y;

Then you can use that function in your SQL statements:

select add(1, 3)// 4

That’s easy — and then the real power comes with the ability to load and use jar packages. For example, to detect written languages using Lingua:

create or replace function detect_lang(x string)
returns string
language java
imports = ('@~/lingua-1.1.0-with-dependencies.jar')

It’s exactly 10 years since my first day at Google. What I’ve learnt:

Same story, but as a Twitter thread
  1. In 2011 there was a career path for me, but I had to leave Chile to find it.
  2. There is a career path outside management: At Google I discovered that software engineers could keep moving forward without the need to become a manager.
  3. There are jobs that combine passions with skills: I wasn’t the best SWE at Google, but someone saw my passions and skills, and invited me to join Developer Relations.
  4. Know your strengths: Taking the Clifton StrengthsFinder test changed how I viewed myself. …

Car rental rates are surprisingly high — and I have the data to prove it. In this post learn where to find all this data and how to analyze it with SQL, thanks to QL2 and Snowflake.

Watch on Youtube

Have you tried renting a car lately? The rates are surprisingly high. And I have the data to prove it:

Average car rental rates in Las Vegas, LA, Miami, Chicago, and San Francisco — starting in 2019.

Each of these lines represents the average quoted price for car rentals in LA, Las Vegas, Miami, Chicago, and San Francisco — starting in 2019, and ending in record high prices right now.

The highlight of this story is having access to all this data, and wondering what else you could do with it. …

By creating a Snowflake external function we can get predictions out of Facebook Prophet for any time series. Learn how to easily integrate these forecasts into your SQL pipelines — with Snowflake connecting to Prophet running on a Docker container inside Google Cloud Run.

Photo: Pixabay

The goal for this post is to build a function you could use within Snowflake to forecast time series. A great open source tool for this is Facebook Prophet, and we just need a way to use it within our Snowflake environment. This is easy, with Snowflake’s ability to run external functions — hence we only need to host an instance of Prophet and add the necessary plumbing to end up with a prophetize(timeseries) function within Snowflake.

Let’s start with a demo, any time series in Snowflake will do: For example, the temperature around New York City in the Newark…

Source: Pixabay

Your customers trust you to protect their privacy and identities — and Snowflake makes this easy with Dynamic Data Masks. With this you can get rid of complex systems of secure views or copies of data: Just create policies that allow everyone to share the same tables and queries, while protecting sensitive data.

Watch on Youtube #SnowflakeBytes

Let’s say your customers are superheroes, and you have a table that contains data about them. You know a lot about your customers, and they trust you to protect their identities and privacy — not only from third parties, but also from people in your company that need this data to do their job, but not all of this:

There’s plenty of research establishing that boards should refresh their members regularly. Let’s see the numbers for the Free Software Foundation, Apache Software Foundation, Python Software Foundation and Open Source Initiative.

Rotation in open source boards: Average # of years served by members

The ASF, PSF, and OSI keep renewing their board members. On average each member serves less than 8 years.

Meanwhile the FSF average # of years served per board members keeps growing — currently >13.83 years. Or more, if the count started before 2001.

Why boards should rotate

The following research articles show why institutions need to keep boards fresh, regardless if they are non-profit or not:

Rotating new board directors into the boardroom and on committees prevents the board from becoming stale. The IRS favors term limits because they believe that static board membership leads to unhealthy attitudes, which can cause boards to…

Eva’s challenge: Produce a new daily visualization with Snowflake and Tableau for 30 days. Let’s check on her progress during her first week of this ongoing challenge.

Visualizing Snowflake’s Marketplace Data in Tableau

Eva Murray is a Tableau Zen master and a leader for #MakeOverMonday — and she just joined Snowflake! She loves data storytelling, and when she found thousands of datasets in the Snowflake Data Marketplace — she decided to take this challenge. We taped a call discussing her first 7 days of visualizations — and you can find it here:

Visualizing Snowflake’s Marketplace Data in Tableau

Let’s check some of her visualizations and my thoughts:

The visualizations

SQL is a great tool, but it can’t do everything. For example, figuring out a way to analyze funnels and sessions with SQL is truly difficult — until now. Let’s discover the power of MATCH_RECOGNIZE(), by analyzing a Google Analytics e-commerce funnel with Snowflake.

Image: Pixabay

Check TJ Murphy’s reaction when he discovered that Snowflake now has support for MATCH_RECOGNIZE:

Sample use case: Google Analytics e-commerce funnel with Snowflake

How would you answer this funnel question with SQL:

“We have an e-commerce shop. We need to identify sessions that land into the website from an arbitrary referrer (Instagram, Facebook, etc), and that end up buying something. How many steps did it take for these users to buy something, and how many times they tried to pay before success?”

That’s not an easy question to answer with plain SQL. But MATCH_RECOGNIZE finally gives us an elegant way to find answers to question like this one.

With MATCH_RECOGNIZE we can simply define the steps we…

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