Redundant join elimination is a new feature that helps some SQL queries run way faster. To get these benefits, you will need to give some hints to Snowflake. Let’s figure out these constraints here. — A quick review of redundant join elimination Sometimes people (and tools) write joins in SQL that end up being redundant. If eliminated these queries could run significantly faster, saving time and complexity. Snowflake is ready to do this automatically, but to do this safely Snowflake might need some extra information from you.

Let’s find out what the new `memoizable’ SQL UDFs are, their effects, and limitations. Then let’s put them to work with an example that shows how to improve the performance of complex security policies. — The effects of `memoizable` Memoizable UDFs behave like smart session variables that remember the results of the UDF even beyond a session. These UDFs will only recompute the results if the underlying data changes, leading to improved performance as results are not computed repeatedly.

Snowflake users can finally dig deep into query stats with this new function. This data was only available in the query profile visualization, and now you can get it in your SQL world with GET_QUERY_OPERATOR_STATS(). Let’s review the new function, and how to use it to get deeper insights while benchmarking. — The old ways Previously you could find deep stats only on the query profile visualization UI:

In this monthly update, we share handpicked resources and news about Snowflake developer programs, user groups, discussion channels, advocacy opportunities, and open source projects. — January Editor’s note: Felipe Hoffa We take turns on compiling the monthly community updates — and since this is my month, let me add some notes before our usual update: We’ve seen many layoffs in the tech space — so I shared my thoughts for friends considering joining Snowflake.

With a Java UDF in Snowflake that geolocates IP addresses using MaxMind’s GeoLite2 Free Geolocation Data — including support for IPv4 and IPv6. No pre-processing needed ❄️🐻‍❄️📍 — Some backstory At my previous job I published “Geolocation with BigQuery: De-identify 76 million IP addresses in 20 second”. There I described how I transformed MaxMind’s GeoLite2 Free Geolocation database into a SQL table, ready to produce fast results thanks to a couple of “clever” SQL tricks.

Snowflake can send emails now —so let’s build a stored procedure that sends you the results of a query every morning — including if there’s a Holiday somewhere in the world that’s affecting your numbers. — Snowflake can send emails now To send an email notification with Snowflake, you now need only one SQL call: call system$send_email( 'my_email_int', 'felipe@example.com, dan@example.com, rajiv@example.com', 'This is the subject', 'This is the body' );

After an incredible 13 year career, Dan Delorey left Google to become the VP of Data at SoFi. It’s a huge switch, and — fortunately for us — Dan shared his story with the Data Engineering Podcast. Here I want to share my notes, starting with Dan as a Google Software Engineer who became an Engineering Director for BigQuery, and then moved into a VP role at a regulated financial company. — This is a fascinating conversation, as it moves from a deep focus on query execution and data storage — and then moves into business problems with data at SoFi (like “finding data, making sure data is reliable, monitoring SLAs about data delivery, answering business user questions”).

Now Facebook Prophet runs inside Snowflake — no need for external functions or dependencies. Let’s see how. — Previously I showed how to run Facebook Prophet as an external function, and then I wrote about running pmdarima inside Snowflake as a Python UDF — thanks to Snowpark and Anaconda. Today I’m happy to announce that Facebook Prophet runs inside Snowflake — without any additional hassles or compilation steps…

Shruti Sekaran is one of the amazing interns I met at Snowflake during the summer of 2022. I was immediately impressed by her goal: Improving the performance of some specific SQL patterns in Snowflake by “eliminating redundant semi-joins and anti-joins from queries”. Check this post for some SQL optimization tricks, and why you might not need them. — SQL quiz: Optimize this Before we go deeper, check the following query. It scans 6 billion rows, while performing two semi-joins with sub-queries that go over 1.4 billion rows each. How would you optimize it?