Tips, SQL patterns, and notes from working with Snowflake — stored procedures, security, streams, geolocation, and more.
Posts
-
Quick Sample of Fuzzy Matching in Snowflake
Quick Sample of Fuzzy Matching in Snowflake Introduction This post walks through a quick, practical example of fuzzy name matching using Snowflake SQL. The goal is to identify approximate matches base…
-
Converting camelCase and SNAKE_CASE
In programming and string matching, use of camelCase and SNAKE_CASE are common. Here are two simple Snowflake UDFs to convert between the two. create or replace function camelToSnake("s" string) retur…
-
Running Dynamic SQL in Snowflake
Use Cases for Dynamic SQL Dynamic SQL allows you to create and manipulate a string, and then run the resulting string as a SQL statement. Snowflake supports dynamic SQL using the identifier keyword an…
-
Getting Snowflake's Current Timezone
Snowflake's built-in way to get the current timezone is using the SHOW statement, like this: show parameters like 'TIMEZONE'; If you're running the statement from a command line, this isn't a problem.…
-
Geolocation of IP Addresses in Snowflake — Part 3
Programming Note This is a continuation of the Part 1 and Part 2 of this series. Since considerable time has passed and changes made to the testing since posting those articles, this post will start f…
-
Least Privilege Access to Monitor Snowflake Usage
The SNOWFLAKE Database All Snowflake accounts should have a database named SNOWFLAKE. It's a shared database, using Snowflake's secure data sharing . If you set up your Snowflake account before the sp…
-
Snowflake Version
One of the great things about being a Snowflake customer is you'll never have to perform upgrades and patches. Snowflake performs upgrades and patches for you, transparently with no down time or degra…
-
Regex Non-Capturing Groups and Lookarounds in Snowflake
If you don't need the background or discussion of how they work and just want to download Snowflake UDFs that support regex non-capturing groups, lookaheads, and lookbehinds, you can download them her…
-
Getting Snowflake Primary Key Columns as a Table
One of my customers had an interesting requirement. In order to dynamically create merge statements, they needed a way to collect the primary key columns for any given table. After discussing some opt…
-
Oracle to Snowflake Table DDL Conversion
Jason Trewin at FreshGravity provided this Oracle to Snowflake Table DDL conversion script. FreshGravity is a great Snowflake partner, and Jason is working on his second Snowflake deployment for our s…
-
Overloading JavaScript UDFs in Snowflake
A Base Function with Two Overloads Snowflake supports overloading user defined functions . It's a great way to handle function calls with parameters of different data types or different numbers of par…
-
Multi-Table Inserts with Good and Bad Row Tables
Many customers have asked me how to separate good rows from bad rows during a data load. You can use the validate table function to return all the errors encountered during the load. This may not be e…
-
Helper Functions in Snowflake Stored Procedures
Snowflake supports JavaScript stored procedures . You may choose to start by copying and modifying a sample Snowflake stored procedure from the documentation, often this one . As you add more SQL stat…
-
Getting a Complete List of User Privileges in Snowflake
These queries don't need much explanation. I've had some customers request how to get a complete list of user privileges, often for auditing purposes. The two queries below will show the role hierarch…
-
Executing Multiple SQL Statements in a Stored Procedure - Part Deux
A customer requested the ability to execute multiple SQL statements that result from a query . Today I learned about a new use case that required some augmentation of the stored procedure. Specificall…
-
Connecting Microsoft Access to Snowflake
Microsoft Access A customer sent me an interesting request today asking how to connect Microsoft Access to Snowflake. I connected Excel to Snowflake, but had never tried with Access. I thought that si…
-
Snowflake UDF to Get Payment Card Type
This User Defined Function (UDF) doesn't require much explanation. Payment card number goes in; payment card type comes out. Since it is designed for speed, it does not validate the check digit. A sub…
-
Snowflake Relationships - Java Utilities
Administrators usually disable parent-child relational constraint enforcement, especially in OLAP databases. Snowflake allows definition of parent-child relationships, but currently does not enable en…
-
Snowflake Streams Made Simple
Snowflake streams demystified The term stream has a lot of usages and meanings in information technology. This is one of the reasons the Snowflake stream feature has excited interest, but also raised …
-
Field Comparisons Using Snowflake
Use cases for bulk field comparisons There are a lot of reasons why it may be necessary to compare the values of some but not all fields in two tables. In billing reconciliation, one table may contain…
-
Object Dependency Checking in Snowflake
Snowflake allows dropping of an object that has a view dependent on it. For example, create tables A and B, and view C that joins A and B together. Snowflake will allow you to drop table A or B, and w…
Stored Procedures · SnowSQL · Stored Procedures · Object Dependencies
-
Preserving Table Change History Indefinitely
Snowflake supports Time Travel and Stream retention up to 90 days. What if you need to preserve the history indefinitely? You can do that by making a Zero Copy Clone at intervals shorter than the Time…
-
Setting the Default Timezone for a Snowflake Account
By default, Snowflake accounts have their timezone set to US/Pacific. Here is how to change that permanently at the account level. You can still override it for specific users or sessions. Set the acc…
-
Executing Multiple SQL Statements in a Stored Procedure
A classic DBA technique to run a large number of SQL statements is to create them using a concatenated select statement. Suppose you need to delete all tables that end with "TEST". You can list them i…
-
Capturing Audit Trails in Snowflake
This article discusses two types of audit trails, 1) SQL statement execution logs, and 2) session logs for security. The Snowflake documentation discusses the QUERY_HISTORY functions, which "return qu…
-
CHAR Padding in Snowflake
For strings that need to be fixed-width, it's common to use the CHAR data type, for example CHAR(10). In this example, often databases will right pad a field has fewer than 10 characters with spaces. …
-
Conditional Column Masking Based on Role
Snowflake secure views offer a powerful way to control who gets to see what. One way to secure data is to mask a column's value based on who is accessing the data set. First a bit of background on Sno…
-
Creating a Hello World Stored Procedure in Snowflake
Snowflake makes creating stored procedures easy. Make sure that you have the database in context where you want to create the stored procedure. --Create a hello world Snowflake Stored Procedure. creat…
-
Geolocation of IP Addresses in Snowflake - Part 2
One of the thing needed to test Geolocating a weblog is, well, a weblog. As it turns out, this is not an easy thing to do. After some research, I located a partially obfuscated Apache web log from the…
-
Geolocation of IP Addresses in Snowflake
It's probably a safe assumption that business intelligence and data science teams can get valuable insights knowing the geolocation of website visitors. Suppose a product launch gets lots of web traff…
-
Grouping Numbers in Snowflake - Part 2
In a previous post, I showed a way to format numbers using local grouping characters. One of the advantages of the approach is it will work everywhere across Snowflake since it does not require creati…
-
Grouping Numbers in Snowflake
As I started working with Snowflake, one of the things I noticed is that large integers appear without grouping symbols. When counting rows in a table, it may display as 752941241 rows. Without counti…