Announcing the LocalStack for Snowflake 1.7 Release
Introduction
LocalStack for Snowflake 1.7 delivers focused improvements to Snowflake Scripting, information_schema coverage, and SQL function support. This release extends Snowflake Scripting with anonymous code blocks, IF-ELSE conditional logic, and exception handling, enabling more comprehensive testing of procedural workflows locally.
Beyond scripting enhancements, version 1.7 expands information_schema coverage with support for columns, schemata, and databases views, adds several aggregate and bitwise functions commonly used in analytics, and includes targeted parity improvements for dynamic tables, session variable handling, window functions with DISTINCT, and LIKE/ILIKE operators.
Get your free LocalStack trial to access the latest features in LocalStack for Snowflake 1.7!
How to upgrade?
To upgrade to LocalStack for Snowflake 1.7 using the LocalStack CLI, run:
docker pull localstack/snowflake:1.7.0localstack start --stack snowflakeIf you’re using LocalStack for Snowflake with Docker CLI or Docker Compose, you can pin the image version in your docker run command or Docker Compose file to 1.7.0.
What’s new in LocalStack for Snowflake 1.7?
- Expanded Snowflake Scripting Features
- Enhanced information_schema views
- Support for several new SQL functions
- Improved parity with Snowflake
Expanded Snowflake Scripting Features
The Snowflake emulator now provides extended support for Snowflake Scripting with several key additions that enable more sophisticated procedural logic testing locally.
This update brings the emulator closer to full Snowflake Scripting parity with the following key additions:
- Support for anonymous blocks: Enables execution of procedural SQL logic outside of stored procedures and UDFs, useful for ad-hoc procedural code without creating persistent objects.
- Support for
IF-ELSEIF-ELSEconditional clauses: Enables branching logic in stored procedures and anonymous blocks, supporting nested expressions and multi-statement conditional paths. - Support for exception handling: Enables
EXCEPTION WHEN OTHER THENblocks with access toSQLERRMandSQLSTATEvariables for error logging and graceful error recovery.
Enhanced information_schema views
The Snowflake emulator now provides support for additional information_schema views, improving compatibility with tools and frameworks that query schema metadata.
The following information_schema views are now supported:
information_schema.columns: Enables introspection of column-level metadata, including column names, data types, nullability, and comments.information_schema.schemata: Returns metadata about schemas within a database, including default and user-created schemas.information_schema.databases: Provides metadata about databases in the account.
Support for several new SQL functions
We have added support for several new SQL functions in LocalStack for Snowflake. The new functions include:
| Function | Notes |
|---|---|
| APPROX_COUNT_DISTINCT | Returns an approximate count of distinct values using HyperLogLog. |
| APPROX_PERCENTILE | Returns an approximate percentile value for a specified percentile. |
| BITAND_AGG | Returns the bitwise AND of all non-null input values. |
| BITOR_AGG | Returns the bitwise OR of all non-null input values. |
| BITXOR_AGG | Returns the bitwise XOR of all non-null input values. |
| BITMAP_BIT_POSITION | Returns the bit position of a value within a bitmap. |
| BOOLAND | Computes the boolean AND of two expressions. |
| BOOLAND_AGG | Returns the boolean AND of all non-null input values. |
| BOOLNOT | Returns the boolean NOT of an expression. |
| BOOLOR | Computes the boolean OR of two expressions. |
| BOOLOR_AGG | Returns the boolean OR of all non-null input values. |
| BOOLXOR | Computes the boolean XOR of two expressions. |
| BOOLXOR_AGG | Returns the boolean XOR of all non-null input values. |
| OBJECT_AGG | Aggregates key-value pairs into an OBJECT. |
| QUERY_HISTORY_BY_USER | Returns query history filtered by user name (extends existing QUERY_HISTORY support). |
| REGR_COUNT | Returns the number of non-null pairs used in a linear regression. |
Improved parity with Snowflake
We have made several enhancements to LocalStack for Snowflake to improve compatibility with the Snowflake service. These include:
- Added support for date arithmetic operations (
date +/- numberandnumber + date), enabling date calculations commonly used in SQL queries. - Enhanced
LIKEandILIKEoperator support, including negated variants (NOT LIKE,NOT ILIKE), improving compatibility with pattern-matching queries on semi-structured data. - Added support for
ALTER DYNAMIC TABLE ... RENAME TOandALTER DYNAMIC TABLE ... SET(forTARGET_LAGandWAREHOUSEproperties), along with enhanced metadata tracking inSHOW DYNAMIC TABLES. - Extended
IDENTIFIER()function support to handleDROP SCHEMA IF EXISTSstatements with fully-qualified names. - Added support for multiple session variable assignment using
SET (a, b, c) = (1, 2, 3)syntax, along withIDENTIFIER()function support for session variables. - Added support for
DISTINCTclause within window functions (e.g.,COUNT(DISTINCT ...) OVER ...), transforming these expressions to produce compatible results. - Enhanced
SHOW TERSEcommand support forSTAGES,PIPES,DYNAMIC TABLES,FUNCTIONS, andPROCEDURES, improving compatibility with the Snowflake extension for VS Code.
Conclusion
LocalStack for Snowflake 1.7 expands procedural SQL capabilities with Snowflake Scripting enhancements and improves schema introspection with additional information_schema views. The new aggregate and bitwise functions, combined with parity improvements for dynamic tables, window functions, and VARIANT operations, continue to refine local Snowflake development workflows.
Sign up for a free trial or upgrade to LocalStack for Snowflake 1.7 to use these improvements in your local development environment.
