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:

As the chart shows, Monday is when 7% of users of health apps open their notifications.

Now, what about gaming apps? The best day to send notifications is Sunday:

But that’s only on iOS. Android users are most likely to open gaming related notifications on Wednesdays:

This data comes out of hundreds of companies that use Braze as their customer engagement platform, and you can use the data as a benchmark for your own applications. You can find the data in Braze’s public dashboard powered by Snowflake, or even better, this data is ready for you to query on the Snowflake Data Marketplace.

This is how I learned that people finally started reengaging with travel related apps in March — after a year with not much travel:

Or that in retail and e-commerce, the first purchase happens in 26 days, while the second one takes 43:

You don’t need to be a Braze customer to access all this data. It’s all available and updated for free in the Data Cloud. Just start writing your SQL queries, like this one to check the purchase propensity by industry:

// Pull percent of buyers and repeat buyers for each industry included in Benchmarks
SELECT
 industry,
 COALESCE(SUM(pct_first_time_buyers), 0) AS pct_first_time_buyers,
 COALESCE(SUM(pct_repeat_buyers), 0) AS pct_repeat_buyers
FROM BENCHMARKS_AVG_TIME_TO_PURCHASE
WHERE industry!= 'All Industries'
GROUP BY 1
ORDER BY 2 DESC
;

And if you are a Braze customer, then you can also get your own data from Braze shared automatically with your Snowflake account.

Are you ready to query? You will find the Braze benchmarks for 14 different industry segments:

If you are working to reengage customers, this is the data you want.

Learn more

• Check out our podcast with Braze to see why they trust Snowflake to handle their data from billions of monthly active users
• Read “How Braze Built Support for Snowflake Data Sharing”.

Want more?

• Try this out with a Snowflake free trial account — you only need an email address to get started.
• Find plenty more datasets in the Snowflake Data Marketplace.

I’m Felipe Hoffa, Data Cloud Advocate for Snowflake. Thanks for joining me on this adventure. You can follow me on Twitter and LinkedIn, and check reddit.com/r/snowflake for the most interesting Snowflake news.

Measuring Customer Engagement with Braze and Snowflake was originally published in Snowflake on Medium, where people are continuing the conversation by highlighting and responding to this story.

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.

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 each different measure that a station can produce.

So let’s transform this data here, to make it easier to incorporate in your data pipelines.

Pivot multiple rows into one

This was easy with Snowflake. The following query will take the multiple rows per day per station, and transform them into only one:

--create or replace table noaa_gsod
--cluster by (station_id, date)
--as (
    select *
    from (
        select "Stations", "Date", "Stations Name", "Country", "Indicator Name", "Value"
        from KNOEMA_ENVIRONMENT_DATA_ATLAS.ENVIRONMENT.NOAACD2019R
        where "Date">='2020-01-01'
        and "Measure"='M1'
        -- and "Country"='US'
    )
    pivot(max("Value") for "Indicator Name" in ('Mean visibility (miles)','Maximum temperature (Fahrenheit)','Mean dew point (Fahrenheit)','Maximum wind gust (Number)','Minimum temperature (Fahrenheit)','Maximum sustained wind speed (knots)','Mean wind speed (knots)','Mean station pressure (millibars)','Precipitation amount (inches)','Mean temperature (Fahrenheit)','Mean sea level pressure (millibars)','Snow depth (inches)'))
    as p(station_id, date, name, country_fips, visibility, max, dew, wind_max, min, wind_sustained_max, wind_mean, pressure, rain, temp, pressure_sea, snow_depth)
--)
;

Since Snowflake supports native pivots, we were able to merge multiple rows into only one, creating new columns and renaming them for easier usage:

Find cities

Now, how do we find the city closest to each station? Or the zip code?

Let’s start by finding a list of worldwide cities. In this case I used data from Wikidata (we can talk more about Wikidata on Snowflake in a future post).

Geolocate stations

With a list of cities and Snowflake’s GIS support we can find the city closest to each station:

create or replace table stations_city
as (
    select label city, station, st_distance(st_makepoint(a.lon, a.lat), st_makepoint(b.lon, b.lat)) distance, country_fips, b.country
        , station_id
    from weather_stations a
    join wikimedia.public.cities b
    on a.country=b.country_fips
    and st_distance(st_makepoint(a.lon, a.lat), st_makepoint(b.lon, b.lat)) < 50000
    qualify row_number() over(partition by station order by distance) = 1
    order by views desc
)
;

In that SQL query we limited the search of st_distance with a radius of 50km. This geo-join performed pleasantly fast: 3 seconds with a small warehouse.

Note that the qualify row_number() over(partition by station order by distance) = 1 brings us the city closest to each station. To get the station closest to each city we would need to change it to over(partition by city order by distance).

Pattern matching: Rainiest cities

Now let’s have some fun with pattern matching, thanks to our new MATCH_RECOGNIZE powers.

For example let’s find the cities in the US that had the most number of consecutive days with rain:

   select *
    from (select * from noaa_gsod where country_fips='US')
    match_recognize(
        partition by station_id, name
        order by date
        measures avg(temp) avg_temp, sum(rain) total_rain, min(rain) min_rain, count(*) rainy_days, min(date) date
        one row per match
        pattern(rain{5,})
        define
            rain as rain > 0.1
    )

I love how easy is to define a pattern between rows with MATCH_RECOGNIZE:

• First we define rain (for the pattern) as any row that has rain > 0.1 inches. If the rain that day didn’t hit that minimum, we won’t count it as a rainy day.
• Then we ask Snowflake to look for any series of rows that see that definition of rain for at least 5 consecutive days with pattern(rain{5,}).
• Check in the query how the combination of one row per match, partition by station_id, name order by date, and the measures min(date) date will help us get the desired results.

If we order the weather stations by most consecutive rainy days in a row, these are the results:

As you can see that Quillayute Airport saw 25 days of consecutive rain in January 2020, and then 16 days in December 2020. That’s a lot of non-stop rain.

But where is that airport? What’s the closest city? We can solve that with a join to our previous results:

select *
from (
    select *
    from (select * from noaa_gsod where country_fips='US')
    match_recognize(
        partition by station_id, name
        order by date
        measures avg(temp) avg_temp, sum(rain) total_rain, min(rain) min_rain, count(*) rainy_days, min(date) date
        one row per match
        pattern(rain{5,})
        define
            rain as rain > 0.1
    )
) a
join (
    select *
    from stations_city
) b
using (station_id)
order by rainy_days desc, total_rain desc
;

Turns out these are the rainiest cities in the US. Can you guess before you look?

Before looking, my guess was Seattle. And it was correct. Seattle followed by Tacoma, its twin city. They truly deserve the title “top rainy cities in the US, by number of consecutive rainy days”.

But where did Quillayute airport go? Turns out it’s not in the 50km radius to one of the major cities I had in my query above. If we grow that radius to 200km, these are the results:

And there it is, Seattle and Tacoma again. It sure rains a lot around Washington state.

Everything together in dbt

Check out my GitHub project with a full dbt project.

(Thanks to Noel Gomez and Mike Weinberg for their review on the dbt Slack)

Next steps

• Explore premium weather providers in the Snowflake Data Marketplace.
• Try this out with a Snowflake free trial account — you only need an email address to get started.
• Find plenty more datasets in the Snowflake Data Marketplace.

Want more?

I’m Felipe Hoffa, Data Cloud Advocate for Snowflake. Thanks for joining me on this adventure. You can follow me on Twitter and LinkedIn, and check reddit.com/r/snowflake for the most interesting Snowflake news.

NOAA weather data in Snowflake (free) was originally published in Towards Data Science on Medium, where people are continuing the conversation by highlighting and responding to this story.

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
handler='Test.add'
as
$$
    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')
handler='MyClass.detect'
as
$$
import com.github.pemistahl.lingua.api.*;
import static com.github.pemistahl.lingua.api.Language.*;

class MyClass {
     static LanguageDetector detector = LanguageDetectorBuilder.fromLanguages(ENGLISH, FRENCH, GERMAN, SPANISH).build();

    public static String detect(String x) {
          return detector.detectLanguageOf(x).toString();
        }
    }
$$;

If you have the right jar staged into your Snowflake account, that’s all it takes:

select $1, detect_lang($1)
from values('languages are awesome'),('hola mi amigo'),('hallo Freunde'),('je ne parle pas');

Notes

• I love the ability to write custom Java code while defining UDFs within SQL (Snowflake takes care of compiling it). This allows you to handle all your glue code in one place, within your SQL scripts and dbt projects.

Setup

Lingua is “The most accurate natural language detection library for Java and the JVM, suitable for long and short text alike” (according to them). It’s written in Kotlin, which is a language that runs on the JVM.

To package this library into a single fat jar for Snowflake, I followed these steps:

1. Clone the git project
2. Build a jar with dependencies with Gradle.

git clone https://github.com/pemistahl/lingua.git
./gradlew jarWithDependencies

Then you can PUT this jar into your Snowflake account with SnowSQL:

put 'file://build/libs/lingua-1.1.0-with-dependencies.jar' @~;

Read more

• “Welcome to Snowpark: New Data Programmability for the Data Cloud”
• “What JDK version does Snowflake support for Java UDFs?”
• How can I parse HTML with a Snowflake UDF?
• Let’s create fake data with few lines of SQL and Snowflake Java UDF
• Java UDF Snowflake docs
• pemistahl/lingua on GitHub

Acknowledgements

Java UDF support is in active development by a great team at Snowflake, including Elliott Brossard and Isaac Kunen. Stay tuned for more!

 — @Di_Ku

Thanks to Peter M. Stahl for the awesome Lingua library:

pemistahl/lingua

Want more?

• Try this out with a Snowflake free trial account — you only need an email address to get started.
• Find plenty of interesting datasets in the Snowflake Data Marketplace.

I’m Felipe Hoffa, Data Cloud Advocate for Snowflake. Thanks for joining me on this adventure. You can follow me on Twitter and LinkedIn, and check reddit.com/r/snowflake for the most interesting Snowflake news.

• Forecasts in Snowflake: Facebook Prophet on Cloud Run with SQL
• Dynamic Data Masks: Protect sensitive data with Snowflake (superhero edition)

New in Snowflake: Java UDFs (with a Kotlin NLP example) was originally published in Snowflake on Medium, where people are continuing the conversation by highlighting and responding to this story.

 — @felipehoffa

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. The results made sense, and gave me a way to communicate to others and myself who I was and what I could do.
5. Know what you want to do: If I had to summarize the 3 things that have motivated me the most throughout life it’s Travel, Storytelling, and Analyzing Data. DevRel was the perfect fit for me.
6. Choose a manager that values your strengths: You won’t always have a choice of who your next manager will be, but make sure to establish as early as possible what your strengths are and what you want to do.
7. There is life outside Google: After almost 10 years at Google with 13 different managers, I finally had enough. I used all I had learned to find my new home at Snowflake, and it has proven to be the right choice.
8. Making mistakes is ok: I’ve made people feel uncomfortable. I’ve missed deadlines. I should have replied to more emails and messages than I have. Hopefully my contributions have been bigger than my mistakes, and I’m grateful for everyone that has forgiven me.
9. Learn from your own mistakes and shortcomings. Have empathy for those that are still learning from their own.
10. Showing up and building relationships is more important than a plan: I never planned to move to the US, join Google, nor DevRel, nor Snowflake. All these happened thanks to friends that I made along the way. I’m grateful to them for opening doors and inviting me to join.
11. Pay it forward. Open doors. Share what you learned. Understand that everyone has different strengths. Forgive.

Felipe Hoffa on LinkedIn: It's exactly 10 years since my first day at Google. What I've learnt

5 Reasons Why I Joined Snowflake After Google | Snowflake Blog

 — @felipehoffa

 — @felipehoffa

 — @felipehoffa

 — @felipehoffa

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

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. So let me introduce QL2, a company whose mission is to provide competitive pricing data across many industries, including airlines, hotels, and a lot more — including car rental prices.

If you want to play with their data, you can find them in the Snowflake Data Marketplace, within the Travel category. You can get immediate access to their sample dataset, which has hotel pricing around Las Vegas. And you can request access to one of their premium datasets, like the car rental pricing dataset we are exploring here.

Then you can use SQL to analyze several years of car rental pricing data from all over the world. That’s how we can tell that people in Sydney, Australia have seen a similar pattern of high prices, while in Paris, France prices have been kind of stable:

Queries

Weekly summary

My first step was to reduce the billions of rows that QL2 offers to a weekly summary:

create table ql2_cars_weekly
cluster by (location, week)
as
select location, currency, rate_type, mileage, site
  , date_trunc(week, qts) week
  , exp(avg(ln(rate))) avg_price
  , count(*) requests
from ql2_cars.public.raw_carrental
where qts>'2019-01-01'
and rate>0
and location rlike '[A-Z][A-Z][A-Z]'
group by 1,2,3,4,5,6
having requests> 150;

Notes:

• To avoid the effect of outliers exp(avg(ln(rate))) gives a geometric mean instead of a simple average.
• The dataset includes multiple locations, so to extract only airports we look for 3 letters only with location rlike ‘[A-Z][A-Z][A-Z]’.
• We’ll keep groups of location, currency, rate_type, mileage, site only where there are more than 150 samples per week.

Pick currency and car rental type per location

Within the weekly summary we find that each location has multiple combinations of rate_type, mileage, and currency.

The following query filters to keep only daily rates, unlimited mileage, and guesses the preferred currency per airport:

create or replace table ql2_cars_weekly_viz
as
select location, currency, week, avg(avg_price) avg_rate, sum(requests) requests
from (
    select *
    from ql2_cars_weekly
    --where week = (select max(week) from ql2_cars_weekly)
    where rate_type like 'D%'
    and mileage like 'U%'
    qualify currency = first_value(currency) over(partition by location order by iff(site='XP_US', 0, requests) desc)
    order by sum(requests) over(partition by location) desc, requests desc
)
group by 1,2,3;

These are the results that we ended up visualizing with a simple filter:

select location, week, avg_rate, requests, currency
from ql2_cars_weekly_viz
where location IN 
-- ('SFO', 'MIA', 'LAX', 'LAS', 'ORD')
   ('CDG', 'SYD')
;

Next steps

Think about how your business could use these datasets. Competitive pricing data for travel means you’ll be able to understand trends as people start planning their vacations and future business trips. What are they planning, where do they want to go, where are prices increasing due to higher demand — or constraints — and all updated daily in the Data Cloud.

• Try this out with a Snowflake free trial account — you only need an email address to get started.
• Find plenty more datasets in the Snowflake Data Marketplace.

Want more?

I’m Felipe Hoffa, Data Cloud Advocate for Snowflake. Thanks for joining me on this adventure. You can follow me on Twitter and LinkedIn, and check reddit.com/r/snowflake for the most interesting Snowflake news.

Snowflake-Labs/awesome-snowflake

Car rental prices at record highs: Explore the trends with data from QL2 and Snowflake was originally published in Snowflake on Medium, where people are continuing the conversation by highlighting and responding to this story.

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 airport since 2018:

select date, temp
from noaa_gsod
where country_fips='US'
and station = 'NEWARK LIBERTY INTERNATIONAL AP';

-- TODO: Show how to get NOAA GSOD from Knoema in a future post.

Then we can call our function prophetize() (see below how to create it) by aggregating the previous time series into an array with dates and values:

select prophetize(array_construct(
        array_agg(temp::float) 
          within group(order by date)
        , array_agg(date::date) 
          within group(order by date))::string) str
from table(result_scan(last_query_id(-1)));

And that’s it. What we get back is an array with predictions. An easy way to visualize these results is to combine the values of the previous two queries:

select date, temp::float avg_temp
    , 0 forecast, 0 fore_min, 0 fore_max
from table(result_scan(last_query_id(-2)))
union all
select x.value[0]::date, 0, x.value[1]::int forecast, x.value[2]::int fore_min, x.value[3]::int fore_max
from table(result_scan(last_query_id(-1))) a, table(flatten(a.$1)) x;

Interesting notes on the above:

• Prophet was able to easily detect seasonal patterns and predict future values.
• A lot of how Prophet works is tunable, but what you get out of the box works too.
• On Snowflake I used last_query_id(-1) and last_query_id(-2) to combine the results of the previous two queries. It’s a nifty feature.

Now let’s check the details on how to connect a function in the Snowflake SQL world prophetize() to Facebook Prophet running on Cloud Run.

How to

Deploy Prophet on a container to Cloud Run

The requirements.txt to build this container are straightforward:

flask==1.1.4
requests==2.25.1
gunicorn==20.1.0
pandas==1.2.4
pystan==2.19.1.1  # <3.0
fbprophet==0.7.1

As the Dockerfile:

FROM python:3.8

# Allow statements and log messages to immediately appear in the Cloud Run logs
ENV PYTHONUNBUFFERED True

COPY requirements.txt .
RUN pip install -r requirements.txt

ENV APP_HOME /app
WORKDIR $APP_HOME
COPY main.py ./

CMD exec gunicorn --bind :$PORT --workers 1 --threads 1 --timeout 0 main:app

And this is main.py, a basic web server that parses incoming arrays into a dataframe that Prophet uses to forecast an arbitrary number of periods. Then it returns a serialized array with a forecast and uncertainty intervals that Snowflake will receive:

import json
import logging
import os

from fbprophet import Prophet
from flask import Flask, request
import pandas as pd

log = logging.getLogger()
app = Flask(__name__)

def forecast(df: pd.DataFrame, periods=365) -> pd.DataFrame:
    df["ds"] = pd.to_datetime(df["ds"])

    model = Prophet()
    model.fit(df)

    future_df = model.make_future_dataframe(
      periods=periods, include_history=False)

    return model.predict(future_df)[["ds", "yhat", "yhat_lower", "yhat_upper"]]

@app.route("/", methods=["POST"])
def index():
    payload = request.get_json()
    logging.info(payload)
    # https://docs.snowflake.com/en/sql-reference/external-functions-data-format.html

    rows = payload["data"]
    return_value = []
    for row in rows:
        row_number = row[0]
        function_input = json.loads(row[1])
        df = pd.DataFrame({'ds': function_input[1], 'y': function_input[0]})
        fc = forecast(df)
        fc['ds'] = fc['ds'].dt.strftime('%Y-%m-%d')
        row_to_return = [row_number, fc.to_numpy().tolist()]
        return_value.append(row_to_return)

    json_compatible_string_to_return = json.dumps({"data": return_value})
    return (json_compatible_string_to_return)

if __name__ == "__main__":
    app.run(debug=True, host="0.0.0.0", port=int(os.environ.get("PORT", 8080)))

If we want to build and run this container on Google Cloud run, we need to run:

gcloud builds submit --config cloudbuild.yaml;
gcloud run deploy --image gcr.io/fhoffa/prophetize --platform managed

Building the image on Cloud Build is slow the first time, as it takes time to compile Prophet — but this cloudbuild.yaml makes it fast with an image cache on further builds:

steps:
- name: 'gcr.io/cloud-builders/docker'
  entrypoint: 'bash'
  args:
  - '-c'
  - |
    docker pull gcr.io/fhoffa/prophetize:latest || exit 0

- name: 'gcr.io/cloud-builders/docker'
  args: [
          'build',
          '--cache-from', 'gcr.io/fhoffa/prophetize:latest',
          '-t', 'gcr.io/fhoffa/prophetize:latest',
          '.'
        ]

images: ['gcr.io/fhoffa/prophetize:latest']

Cloud Run behind Google API Gateway

One of the main goals I had behind this project was to celebrate that Snowflake now supports external functions for GCP. Hence my choice to deploy on Cloud Run.

Now, to run external functions through GCP, we need to set up a connection from Snowflake to Google API Gateway, and from API Gateway to Cloud Run.

First we need a gateway.yaml for API Gateway to know that it will act as a proxy to the service we deployed on Cloud Run:

swagger: '2.0'
info:
  title: API Gateway config for Snowflake external function.
  description: This configuration file connects the API Gateway resource to the remote service (Cloud Run).
  version: 1.0.0
schemes:
  - https
produces:
  - application/json
paths:
  /test:
    post:
      summary: Prophetize
      operationId: prophetize
      x-google-backend:
        address: https://prophetize-zqnzinxyeq-wl.a.run.app/ 
        protocol: h2
      responses:
        '200':
          description: <DESCRIPTION>
          schema:
            type: string

Then you can follow the GCP docs to create an API Gateway with this configuration. Oh, and make sure to replace the values above with your own service URLs.

Snowflake to Google API Gateway

This is how I connected the dots on Snowflake, to create an integration with API Gateway:

use role accountadmin;
use schema temp.public;

create or replace api integration prophet_test
    api_provider = google_api_gateway
    google_audience = 'test1-3s4aecfho43ih.apigateway.fhoffa.cloud.goog'
    api_allowed_prefixes = ('https://prophetize-4r3ddv95.wl.gateway.dev')
    enabled = true;
    
describe integration prophet_test;
create or replace external function prophetize(x string)
    returns variant
-- IMMUTABLE
    api_integration = prophet_test
    as 'https://prophetize-4r3ddv95.wl.gateway.dev/test' ;
grant usage on function prophetize(string) to role sysadmin;

And that’s all you need, now you can call the just minted prophetize() as in any other query in Snowflake:

select prophetize('[[41,43,62,43],["2019-12-30","2020-01-06","2020-01-13","2020-01-20"]]');

Which gives results like:

[
  [
    "2020-01-21",
    51.3641167654911,
    40.85673826625397,
    61.745184538148166
  ],
  [
    "2020-01-22",
    51.72223221323965,
    41.87259513681375,
    61.29144225035811
  ],
  [
    "2020-01-23",
    52.0803476609882,
    41.66374622035821,
    61.55883149200517
  ], [...]
]

Securing the flows

You might have noticed many URLs in my configs above — now that you’ve seen them, you might want to start calling my functions from your accounts. That would be fine, but I’d rather protect them.

Snowflake makes this easy. Once you create the integration above, a service account for GCP will be automatically provisioned. You can get its value with describe integration prophet_test — and then use that service account to update the gateway.yaml so no one else can call it:

swagger: '2.0'
info:
  title: API Gateway config for Snowflake external function.
  description: This configuration file connects the API Gateway resource to the remote service (Cloud Function).
  version: 1.0.0
securityDefinitions:
  snowflakeAccess01:
    authorizationUrl: ""
    flow: "implicit"
    type: "oauth2"
    x-google-issuer: "ftyqmxcfyo@sfc-prod2-1-947.iam.gserviceaccount.com"
    x-google-jwks_uri: "https://www.googleapis.com/robot/v1/metadata/x509/ftyqmxcfyo@sfc-prod2-1-947.iam.gserviceaccount.com"
schemes:
  - https
produces:
  - application/json
paths:
  /test:
    post:
      summary: Prophetize.
      operationId: prophetize
      security:
        - snowflakeAccess01: []
      x-google-backend:
        address: https://prophetize-zqnzinxyeq-wl.a.run.app/ 
        protocol: h2
      responses:
        '200':
          description: <DESCRIPTION>
          schema:
            type: string

Then follow these Snowflake docs to update your GCP API Gateway with the above secured config.

Note that this GCP service account is provisioned by Snowflake regardless of what cloud you are using to host your Snowflake account. In this case I ran this whole demo on Snowflake on AWS, and it was able to call the GCP services effortlessly.

Meanwhile in Cloud Run, make sure to stop allowing unauthenticated invocations. With this only calls authorized through the API Gateway will be served:

Learn more

Facebook Prophet is a versatile tool with many levers and ways to tune:

Prophet is a procedure for forecasting time series data based on an additive model where non-linear trends are fit with yearly, weekly, and daily seasonality, plus holiday effects. It works best with time series that have strong seasonal effects and several seasons of historical data. Prophet is robust to missing data and shifts in the trend, and typically handles outliers well.

Read more from:

• “Seven Tips for Forecasting Cloud Costs (with FB’s Prophet)” from Gad Benram.
• “Building a serverless, containerized batch prediction model using Google Cloud Run, Pub/Sub, Cloud Storage and Terraform” by Sebastian Telsemeyer.
• Cloud Run costs and features, by Ahmet Alp Balkan

Next steps

• Try this out with a Snowflake free trial account — you only need an email address to get started.
• I shall set up a GitHub project with all these files (and update here when done)

Play with Prophet: Bitcoin predictions

Knoema already has the valuation of multiple crypto coins in the Snowflake Marketplace, thus building a time series forecast with Prophet is straightforward:

-- define the time series
select "Value" value, "Date" date
from KNOEMA_FINANCE_DATA_ATLAS.FINANCE.CMCCD2019 
where "Cryptocurrency Name" = 'Bitcoin (btc)' 
and "Measure" = 'PriceUSD' 
and date > '2017-01-01'
order by "Date";

-- prophetize
select prophetize(array_construct(
        array_agg(value::float) within group(order by date)
        , array_agg(date::date) within group(order by date))::string) str
from table(result_scan(last_query_id(-1)));

-- prepare the viz
select date, value, 0 forecast, 0 fore_min, 0 fore_max
from table(result_scan(last_query_id(-2)))
union all
select x.value[0]::date, 0, x.value[1]::int forecast, x.value[2]::int fore_min, x.value[3]::int fore_max
from table(result_scan(last_query_id(-1))) a, table(flatten(a.$1)) x;

The more you play with Prophet, the more you’ll see that the forecast will depend heavily on what values you feed it, and how you tune it. In this case, the forecasts varied widely depending on what starting date I used for the time series — starting in 2016 meant Prophet would observe a more complex pattern that feeding it data starting in 2018, and so forth.

Best part for me? I can pull all this off without leaving my comfy Snowflake SQL web UI:

Want more?

I’m Felipe Hoffa, Data Cloud Advocate for Snowflake. Thanks for joining me on this adventure. You can follow me on Twitter and LinkedIn, and check reddit.com/r/snowflake for the most interesting Snowflake news.

• Snowflake-Labs/awesome-snowflake
• Knoema Datasets and the Snowflake Data Marketplace Challenge

Forecasts in Snowflake: Facebook Prophet on Cloud Run with SQL was originally published in Towards Data Science on Medium, where people are continuing the conversation by highlighting and responding to this story.

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.

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:

For example, your lawyers need to see the real names of your heroes, but not their superhero identities. And customer service — they need to see the first name to talk to them, but not their full identity. Maybe they need to know how famous each of them is, but not necessarily the exact number. And so on.

So we need to solve this data problem, and we don’t want to make multiple copies of the data and we don’t want to create a complex system of views.

What we want here is dynamic masks. With dynamic masks, we can define policies in one place and Snowflake will make sure that people in your company can share tables and queries, but they will only see what they’re allowed to see, depending on their role.

So I wrote this policy in SQL that only allows people with the lawyer role to see the full name. Meanwhile people with the customer service role only see the first name — and no one else will be able to see the legal name of our customers:

Let’s try it out with a SELECT *: I get some data, and if I change my role, the same SELECT * gives me different results.

It’s the same table. It’s the same query. My role determines what I see. Even better, these masks are applied at query time. They even work with external tables that might live in your Data Lake.

And it’s that simple because Snowflake cares about your data and the privacy of your customers. And this is just a start. We will go deeper in the near future but in the meantime, check below the full SQL script to reproduce these results.

Script

For dynamic masks to work, you need to use Snowflake’s Enterprise edition or higher:

use role sysadmin;

// Let's assume this database and a warehouse exists, otherwise create them.
use masks_db;

// Quick creation of base data
create or replace table customers
as
select $1[0]::string hero_name, $1[1]::string birth_name, replace($1[2], ',')::int fame, $1[3]::string birth_place
from (
  select split(x.value, '|')
  from table(split_to_table('Batman|Bruce Wayne|104,302|Gotham City
  Iron Man|Tony Stark|64,098|Manhattan
  Wolverine|James Howlett|43,482|Alberta
  Black Widow|Natasha Romanoff|42,453|Volgograd
  Catwoman|Selina Kyle|40,763|Gotham City
  Magneto|Max Eisenhardt|35,691|Dusseldorf
  Doctor Manhattan|Jonathan Osterman|30,225|Heidelberg
  Kitty Pryde|Katherine Anne Pryde|28,226|Deerfield
  Spawn|Albert Francis Simmons|28,080|Detroit
  Daredevil|Matthew Michael Murdock|27,574|New York City
  Martian Manhunter|J\’onn J\‘onzz|27,547|Mars
  Luke Cage|Carl Lucas|24,799|New York City
  Storm|Ororo Munroe|24,221|New York City
  Wolfsbane|Rahne Sinclair|23,413|Ullapool
  Colossus|Пётр Николаевич Распутин|22,823|Siberia
  Legion|David Charles Haller|22,769|Haifa
  Tim Drake|Timothy Jackson Drake|21,957|Gotham City
  Sunspot|Roberto da Costa|21,872|Rio de Janeiro
  Elasti-Girl|Rita Farr|20,650|
  Polaris|Lorna Dane|20,532|San Francisco
  Zatanna|Zatanna Zatara|19,782|Gotham City
  Emma Frost|Emma Grace Frost|19,427|Boston
  Cable|Nathan Christopher Charles Summers|18,894|Westchester County
  Nightcrawler|Kurt Wagner|18,886|Bavaria
  Spider-Woman|Gwendolyne Maxine Stacy|18,375|Forest Hills
  Cyclops|Scott Summers|18,344|Anchorage
  Psylocke|Elizabeth Braddock|18,168|Maldon', '\n')) x
)
;

// securityadmin can create roles
use role securityadmin;

create role marketing;
create role cust_service;
create role lawyer;

// our user 'felipe' will be able to use these roles
grant role marketing to user felipe;
grant role cust_service to user felipe;
grant role lawyer to user felipe;

// create a role that we'll use for common permissions
create role masks_demo_role;
grant role masks_demo_role to role marketing;
grant role masks_demo_role to role cust_service;
grant role masks_demo_role to role lawyer;

// back to sysadmin, to grant permissions to tables and create policies
use role sysadmin;
grant usage on database masks_db to masks_demo_role;
grant usage on schema masks_db.public to masks_demo_role;
grant select on table masks_db.public.customers to masks_demo_role;

// creating and applying "empty" policies, making it easier to modify later
create or replace masking policy mask_hero as (val string) returns string -> val;
create or replace masking policy mask_name as (val string) returns string -> val;
create or replace masking policy mask_fame as (val int) returns int -> val;
alter table customers modify column hero_name set masking policy mask_hero; 
alter table customers modify column birth_name set masking policy mask_name; 
alter table customers modify column fame set masking policy mask_fame;

// these are the real policy implementations, we can play here with their code
alter masking policy mask_hero set body ->
case 
when current_role() in ('MARKETING', 'SYSADMIN') then val
else ''
end;

alter masking policy mask_name set body ->
case 
when current_role() in ('LAWYER', 'SYSADMIN') then val
when current_role() in ('CUST_SERVICE') then regexp_substr(val, '[^ ]*')
else ''
end;

alter masking policy mask_fame set body ->
case 
when current_role() in ('MARKETING', 'SYSADMIN') then val
when current_role() in ('CUST_SERVICE') then pow(10, round(log(10, val)))::int
else -1
end;

// try the different roles and see what results they get
// https://i.imgur.com/q1jGMZe.gif
use role marketing;
use role cust_service;
use role lawyer;

select *
from customers;

// introspection: what policies were created and applied
use role sysadmin;
describe table customers;
show masking policies;

Next steps

• Check the docs.
• Discover further features, like dynamic masks on semi-structured data.
• Can we go beyond roles? In future posts we can review masking data based on the person running the query individual characteristics.
• New in Snowflake: Row level policies.
• Check how JetBlue Secures and Protects Data Using dbt and Snowflake.

Want more?

I’m Felipe Hoffa, Data Cloud Advocate for Snowflake. Thanks for joining me on this adventure. You can follow me on Twitter and LinkedIn, and check reddit.com/r/snowflake for the most interesting Snowflake news.

Snowflake-Labs/awesome-snowflake

Dynamic Data Masks: Protect sensitive data with Snowflake (superhero edition) was originally published in Snowflake on Medium, where people are continuing the conversation by highlighting and responding to this story.

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:

Best Practices for Nonprofit Board Term Limits | BoardEffect

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 govern out of self-interest rather than community interest. Boards that have a majority of longstanding members may intimidate newer members, causing them to hold back with new thoughts and ideas.

Board Refreshment Trends at S&P 1500 Firms

Tenure Trends Reversing… And May Reverse Again: Investors’ concern — warranted or not — over rising director/board tenure is based in reality. Average boardroom tenure steadily rose from 8.4 years in 2008 to a peak of nine years in 2013 before slowly reversing course from 2014 to 2016 (YTD). As a result, average director tenure at S&P 1500 firms now stands at a level — 8.7 years — last recorded in 2010. Moving in a similar pattern, median board tenure across all S&P 1500 directorships rose from six years to seven years in 2009, but has remained steady from 2010 to 2016. Absent intervention by boards, however, structural issues — especially rising mandatory retirement ages — could cause average and median tenures to climb again in a few years.

Source data

Brian Fitzpatrick researched and shared this data in a Google sheet:

Thread: This tweet got me to thinking: is there a way to show just how broken the FSF is as an organization? Is there a way to show how their board isn’t really a functioning board, but rather an enabling body for RMS? Turns out, there is! And I will show you in *four* tweets.

 — @therealfitz

 — @therealfitz

Sheet to SQL

I found it difficult to manipulate these numbers within the format that Brian chose for these sheets, so I brought everything to SQL.

I copy pasted the whole sheets into Snowflake, parsed with SQL, and added window functions to count the number of years that each member served:

// see https://github.com/fhoffa/snowflake_snippets/blob/main/open_source_boards/board_tenure.sql

unpivot as (
    select f
        , split(y.value, '\t')[0]::string who, z.index+2000-5 year
        , row_number() over(partition by f, who order by year) years_served
    from sheet4
        , table(split_to_table(x, '\n')) y
        , table(split_to_table(y.value, '\t')) z 
    where z.index>5
    and z.value='1'
)

-- select *
-- from unpivot;

select f, year, avg(years_served)
from unpivot
group by 1,2
order by 1,2

Above average terms

With counts starting in 2001:

FSF: Gerald Sussman (21), Geoffrey Knauth (21), Richard Stallman (20), Henry Poole (19), Benjamin Mako Hill (13), Hal Abelson (10)

ASF: Jim Jagielski (17), Greg Stein (16), Sam Ruby (14), Bertrand Delacretaz (11), Shane Curcuru (10), Roy T. Fielding (9), Brett Porter (9)

PSF: Tim Peters (13), Martin von Löwis (11), Van Lindberg (9), Steve Holden (9)

OSI: Simon Phipps (9)

Shared sheet

Find the unpivoted data on my own shared sheet:

Open source boards and tenure terms

Learn more

I’m looking forwards to Matt Asay take on this data!

 — @mjasay

Open source boards and tenure terms: The FSF has stalled was originally published in The Startup on Medium, where people are continuing the conversation by highlighting and responding to this story.

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.

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:

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

The visualizations

• Employment in Sports
• Sports Footwear Exports
• Stanley Cup Winners
• Employment in Sports 2019 (Gender Split)
• Twenty20 Cricket Rankings in 2020
• Starbucks visits during the start of the pandemic
• Tourism Brand Performance 2019

My Notes

First I’m in awe of her courage to attempt this challenge. As you can see above, every day she picks up a new domain — so she needs to quickly understand it enough to be work within it. Then she digs into the data to find an interesting story and a suitable visualization. And then she performs the most dangerous step: Posting the visualizations and findings on the Internet. This means she opens up to criticism and complaints, from anyone on the Internet and with the desire to reply. Fortunately her experience with the #MakeOverMonday project has prepared her exactly for this — leveraging the power of community to learn and improve.

My least favorite visualization: “Employment in Sports 2019 (Gender Split)”

As I looked at this visualization I started wondering — “why are these slopes and not bars?”, “what is the pattern?”, “is there an interesting story here?”.

As you look at this data you’ll find that the differences between countries and genders are pretty small — all around 0.3% and 2.3%. To me this looks almost like a random distribution, where any gender could have the lead by arbitrary reasons.

But then that becomes a highlight of Eva’s chosen visualization: It makes it easy for me to figure that out. Thanks to this visualization choice and annotations I could quickly figure out that all numbers are constrained to a small range — and the slope shows that when there’s a difference in many cases it’s almost non noticeable. And then, where there’s a significant difference, the slope quickly reveals it.

All these thoughts and ideas that first made this one my “least favorite” ended up making it one of the most interesting choices, and full of lessons for me.

My favorite visualization: “Sports Footwear Exports”

This one I found fascinating: By playing with the data, Eva was able to uncover a major unnoticed trend — in less than 10 years someone in Belgium was able to create an industry that makes almost 3 billion euros a year by importing and exporting shoes.

How did this happen and what’s the story behind? This chart has made me really curious to learn more. I foresee many business schools teaching this story for years, once they reach this same discovery.

How-to

Tableau and Snowflake

Connecting Tableau and Snowflake is really easy — and also navigating all the metadata to find interesting datasets. Check the video where we show how Eva managed to discover these datasets within Tableau.

Datasets

Most of the data for these visualizations came out of the Knoema listings in the Snowflake Data Marketplace. To learn more about Knoema — check my previous posts, and my conversation with Knoema’s CEO:

Knoema Datasets and the Snowflake Data Marketplace Challenge

Want more?

Follow Eva on Twitter and LinkedIn.

I’m Felipe Hoffa, Data Cloud Advocate for Snowflake. Thanks for joining me on this adventure. You can follow me on Twitter, LinkedIn, and check reddit.com/r/snowflake for the most interesting Snowflake news.

Snowflake-Labs/awesome-snowflake

Eva’s first days of Snowflake+Tableau visualizations was originally published in Snowflake on Medium, where people are continuing the conversation by highlighting and responding to this story.

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

 — @teej_m

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 want to find within a series of events, and then we can define any pattern for these steps — and then MATCH_RECOGNIZE does the rest.

For this example we’re going to use a Google Analytics 360 dataset from an e-commerce shop. Nothing better than real data to prove the power of our tools.

Step 1: Load Google Analytics data into Snowflake

Taking data out of BigQuery and into Snowflake is easy. You might need tools to build a reliable pipeline, but the basic steps are easy:

• Export from BigQuery to GCS: You can use your own GA data, or a sample dataset with data from the Google Merchandise store.
• Load GA data into Snowflake: This is straightforward. Snowflake will happily ingest files from GCS, even if your Snowflake account lives in AWS. Loading JSON works like magic too. You might want to set-up authentication and permissions for maximum security, while for sample data I just used a public bucket.

create or replace table ga_demo2(src variant);

copy into ga_demo2
from 'gcs://fhoffa/ga360/ga_sessions000000000000'
file_format=(type='JSON');

• Query in Snowflake: Notice above that we loaded the JSON rows into a variant type. Snowflake makes working with semi-structured data a joy; you can easily navigate the Google Analytics schema. We can also create a view out of the basic query, to make all the following steps easier to follow:

create or replace view ga360_parsed
as
select src:visitStartTime::timestamp ts
    , hit.value:hitNumber::int hit
    , src:visitId::string visit
    , hit.value:eCommerceAction.action_type::int action_t
    , hit.value:eCommerceAction.step::int action_s
    , hit.value:eCommerceAction.option::string action_o
    , hit.value:page.pagePath::string path
    , hit.value:referer::string referer
    , src:fullVisitorId::string visitor
    , src:totals.transactions::int total_transactions
from ga_demo2, lateral flatten(input => src:hits) hit

Step 2: Querying the funnel is fun

This “straightforward” query answers the question we were looking to answer:

select count(*) visits
  , avg(steps) avg_steps
  , avg(payment_attempts) avg_pay_attempts
  , max(payment_attempts) max_pay_attempts
from ga360_parsed
match_recognize(
    partition by visit
    order by hit
    measures count(*) steps, count(payment.*) payment_attempts
    one row per match
    pattern(from_plex (payment | anything)*  completed)
    define
        from_plex as referer = 'https://mall.googleplex.com/'
        , completed as path like '/ordercompleted.html'
        , payment as action_o = 'Payment'
);

Turns out 21 visits landed from that specific referrer into our site, and ended up purchasing something. On average it took them 35.6 steps to do so, and 1.7 attempts to pay — with at least one session having nine pay attempts before success.

Understanding MATCH_RECOGNIZE

You can follow the official docs to discover all the powers of MATCH_RECOGNIZE — here we’ll look at the basics of the query above.

define

Let’s start with the define section, that is used to define steps in the user’s funnel:

define
        from_plex as referer = 'https://mall.googleplex.com/'
        , completed as path like '/ordercompleted.html'
        , payment as action_o = 'Payment

You can see that we gave an arbitrary name to each step, and that the conditions can be whatever you need them to be. One step for us looks at the referer, another one looks at the users landing on a specific path, and the payment steps are identified by the action variable that Google Analytics stores.

pattern

The pattern section is basically regular expressions for funnel steps:

pattern(from_plex (payment | anything)*  completed)

This says “look for sessions that start with the from_plex step, that are then followed by any number of payment or anything steps, and that end up in the completed state.”

match_recognize

With define andpattern established, we can do a basic match_recognize query:

select classified_as, hit, visit, action_o, referer ref, path
from ga360_parsed
match_recognize(
    partition by visit
    order by hit
    measures classifier() as classified_as
    all rows per match
    pattern(from_plex (payment | anything)*  completed)
    define
        from_plex as referer = 'https://mall.googleplex.com/'
        , completed as path like '/ordercompleted.html'
        , payment as action_o = 'Payment'
);

We added 3 constructs to that query. First, how to split and order our sessions: partition by visit order by hit. Then an added column that will show how each row is classified in our funnel: measures classifier(). And then our desire to see all rows of each funnel, instead of just a one-row summary: all rows per match. That’s what allows us to see the following results, with the steps classified as “payment” highlighted:

Answering funnel questions

With each funnel step identified, we can answer the opening question. Instead of selecting all rows, we can ask for one per funnel with a summary: one row per match. We add the measures we are interested in: measures count(*) steps, count(payment.*) payment_attempts. MATCH_RECOGNIZE does the rest of the job, and we can go back to our usual SQL ways to get counts, averages, and other metrics.

Next steps

Funnel analytics is just one example. Think of all the possibilities with MATCH_RECOGNIZE in your toolbox: Threat detection, finding trends within weather and stock prices trend lines, and much more.

Let’s explore these topics in future posts, and share your best ideas and results.

Read more

• Official Snowflake MATCH_RECOGNIZE() docs.

Want more?

I’m Felipe Hoffa, Data Cloud Advocate for Snowflake. Thanks for joining me on this adventure. You can follow me on Twitter, and check reddit.com/r/snowflake for the most interesting Snowflake news.

Snowflake-Labs/awesome-snowflake

Funnel analytics with SQL: MATCH_RECOGNIZE() on Snowflake was originally published in Towards Data Science on Medium, where people are continuing the conversation by highlighting and responding to this story.