As a data person (engineer, analyst, scientist) we frequently run into that monolith of a data table in our databases. Attempting to figure out what columns we need can be challenging at times, especially if a lot of the columns have a similar naming convention. Snowflake has a way to help reduce some of this frustration in your SELECT statements with the ILIKE keyword. The documentation does a thorough job of explaining how ILIKE works; however, in a nutshell, ILIKE is a case-insensitive search method for columns that match a given pattern.
Overview
To illustrate how ILIKE works, I’ll run through a quick SQL statement querying some Global Weather data from Snowflake Marketplace. I won’t cover adding this dataset to your account in this article; however, Snowflake makes this extremely easy and only takes a few minutes. The weather history data contains many data points for MIN, MAX, and AVG values for weather readings. For this example, I only want to select columns with “AVG” in the column name.
Query Example
In other RDBMS systems without the ILIKE operator there’s a couple of options to get the column names needed for a query. You can run a SELECT * on the table and limit the rows or query the INFORMATION_SCHEMA or use another method to get the metadata on the table. ILIKE eliminates that step and gets you rolling quickly. Let’s look at the sample query I used on the Weather Data.
SELECT
POSTAL_CODE,
COUNTRY,
DATE_VALID_STD,
* ILIKE '%AVG%'
FROM
HISTORY_DAY AS F
WHERE
COUNTRY = 'US'
AND
POSTAL_CODE = '28204' /*CHARLOTTE, NC*/
AND
DATE_VALID_STD::DATE >= '2024-10-01'::DATE /*WEATHER DATA SINCE ONLY OCTOBER 1st*/
ORDER BY
DATE_VALID_STD DESC /*SORT IN DESCENDING ORDER*/
;
Notice that the query above looks like any regular select statement, except the ILIKE operator. The FROM, WHERE, and ORDER BY clauses all look “normal” for this statement – filtering data for the US in the Charlotte, NC area (where I live) and readings on or after October 1, 2024.
Wrapping Up
In the code sample above, I showed how to use the ILIKE operator to simplify querying wide tables in your Snowflake data warehouse to return only columns that match your search pattern. You’re not limited to a single ILIKE per query either – if you have multiple patterns to search for, you add additional ILIKE statements as needed.
Why does this matter? For cloud-based tools like Snowflake, running SELECT * on tables with many columns and rows can be a costly operation. Using ILIKE to reduce the number of columns returned ensuring your JOINS and WHERE clauses are in order will help reduce your query costs.
Follow me on LinkedIn and Medium for more content on Data Management and demos including Snowflake, Streamlit, and SQL Server.
Eric Heilman 