The Snowflake Data Marketplace Challenge is open. To win you need to come up with a creative answer to a question, by using data from the Snowflake Data Marketplace. Find here a highlight of the Knoema datasets in Snowflake, then join us in the challenge.

There are four questions that you could answer, each focusing on a different industry: financial services, healthcare, media & entertainment, and retail/CPG. For example, one of the questions is

“As a financial planner, your client has received a stipend of $1 million and has asked you to help them invest in foreign currency. Which currency would you choose and why?”

To answer this question, you should go to your Snowflake account (sign up for a free trial), and explore these Knoema datasets.

Knoema has collected thousands of different public datasets — and you can access them for free in…


Image for post
Image for post

Easy to use dynamic pivots in Snowflake: How to use and create a JavaScript stored procedure within.

There’s something about SQL and PIVOTs: Everyone wants to PIVOT their results, but there never seems to be an easy way for it. Fortunately, Snowflake users have a native way to perform pivots in SQL, with the function PIVOT():

select *
from (
select * from table(result_scan(last_query_id(-1)))
)
pivot(max(pivot_value)
for pivot_column in ('Spider-Man', 'Wonder Woman', 'Iron Man'))


With Crunchbase in the Snowflake Data Marketplace you can use SQL to track the world of mergers, acquisitions, IPOs, and the key people behind these stories.

During 2020, more than 500 companies in the data and analytics space were acquired all over the world. Can you guess the top three cities where these companies are based?

Image for post
Image for post

Number three is London, number two is New York. And number one is San Francisco, where 33 companies in the data analytics space were acquired in 2020.


Image for post
Image for post

More than 100,000 of coders are working out the Advent of Code challenges in their favorite programming languages. What if I could use this opportunity to highlight the power of SQL and the latest Snowflake features?

2020 has been a year full of surprises — one of them being that I left Google and joined Snowflake. Last year I did my first run for #AdventOfCode with SQL, and this year the challenge is helping me get acquainted with a different SQL syntax than the one I was used to with BigQuery.

I’ll leave some notes here on my major discoveries and learnings — while developing my new Snowflake expertise.

Find the full code on Github for these solutions..

Day 1: Multiple joins

The first problem was pretty easy to solve with SQL.

Part 1 took only a cross join…


Sports and data are more fun when the data is readily available, and the tools smart. Let me show you this quick overview of ThoughtSpot and Snowflake ready to play Fantasy Football with NFL data.

Join us Dec 14 to go deeper:

Video transcription:

Let me show you how cool is to analyze live data with Snowflake and ThoughtSpot.

Last year, ThoughtSpot released a video with a cool demo, especially made for fantasy football players. They loaded NFL data into ThoughtSpot and they showed how easy is to get insights and visualizations through their search bar without having to write SQL. This year, they improved this demo by moving the data out of ThoughtSpot and into Snowflake. …


Image for post
Image for post
Image: tagged cow

UTM codes are a great way to track the success of your online activities, but you shouldn’t use them. Avoid link shorteners too. Especially if your goal is to engage in authentic human-to-human conversations.

Caveat: If you are in a marketing organization driving marketing campaigns, you should totally use UTM codes. For example, this post from Lee Hurst begs marketers to start using more UTM codes.

So why shouldn’t you use UTM codes?

Human filtering

Every time we dive into social media (email, reddit, Twitter, LinkedIn, Facebook, …) our brains drown in a sea of information overload. We are in this site to find interesting links, but our brains have had to develop quick strategies to separate real content from ads.

Which of these links would you rather click on?

  1. https://www.elisbwbar.com/
  2. https://www.elisbwbar.com/?utm_source=gmb&utm_medium=organic&utm_content=main&utm_campaign=listing
  3. https://bit.ly/3fQQMAW

The answer is…


Watch this to see how we set-up and run dbt and BigQuery on the cloud shell

Find here the quickest way to get started with dbt and BigQuery using only free offerings from Google Cloud.

I’m a big fan of dbt — an open source project that helps me build data pipelines around BigQuery using only SQL.

Get started with BigQuery and dbt

There’s a lot already writen about Bigquery and dbt. For example, there’s this official tutorial to set up dbt with BigQuery, with a lot more details than I do here (thanks Claire Carroll). The goal of this post is to share with you some GCP secrets to make the installation as easy as possible.

Step 1: Create a free Google Cloud account

Good news: You don’t need a credit card…


Image for post
Image for post

Can you predict the top Stack Overflow tags of 2023? BigQuery makes this easy, with its new support for training time-series analysis models with ARIMA.

Finding the top Stack Overflow tags of 2023

Let’s start by visualizing the top Stack Overflow tags on June 2020:


Image for post
Image for post
Source: https://commons.wikimedia.org/wiki/File:Basketball_game.jpg

Introducing the easiest way to get a pivot done in BigQuery. This has been one of the most recurrent BigQuery open questions. And now, thanks to dynamic SQL and stored procedures, we can finally make it easy for everyone.

Quick pivots

Before going any further, let me show you how to call fhoffa.x.pivot() to easily pivot any table, creating a new one with the results:

CALL fhoffa.x.pivot(
'bigquery-public-data.iowa_liquor_sales.sales' # source table
, 'fh-bigquery.temp.test' # destination table
, ['date'] # row_ids
, 'store_number' # pivot_col_name
, 'sale_dollars' # pivot_col_value
, 30 # max_columns
, 'SUM' # aggregation
, '' # optional_limit
);

That’s it. Let’s see what’s happening here. The original table has one row per sale (invoice), including the date of the transaction, the store number, and the sale amount:


Image for post
Image for post

Let’s super-power Apple Mobility reports’ data: Decision makers around the world are already using BigQuery and Data Studio to fight the current crisis. With the tools in this post we are going to help them get the best out of this dataset and our tools.

Update 2020–04–24: Step-by-step video

Check out my live chat with Yufeng G for the best practices for BigQuery with Data Studio:

Sample results

The official Apple dashboard for this dataset shows results like this:

Felipe Hoffa

Data Cloud Advocate at SnowflakeDB ❄️. 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