What's new in LocalStack for Snowflake?

Introduction

LocalStack aims to empower development teams by providing a seamless and powerful local cloud development experience. While AWS is a big part of cloud development, it is not the only part and we are working on expanding our emulators to address other popular cloud services.

To that end, a few months ago we launched the public preview of our LocalStack for Snowflake emulator. Since then, we’ve continuously improved this emulator to address the evolving needs of our users. In this blog, we’ll discuss the areas we’ve invested in recent months towards providing our users with the best Snowflake developer experience. These updates include increasing parity with Snowflake, introducing a new web user interface, tighter integration with our SaaS offerings, and enhancements to developer tools and integrations.

How to upgrade?

To upgrade your LocalStack for Snowflake installation to the latest version, run the following commands:

$ docker pull localstack/snowflake:latest
$ IMAGE_NAME=localstack/snowflake:latest localstack start

Note that installing the Snowflake emulator through the LocalStack Extension mechanism is now deprecated and may not be compatible with all versions of LocalStack. It is recommended to use the custom LocalStack Snowflake Docker image for the best experience.

New features

• Support for new functions
• New Web User Interface (Preview)
• Support for LocalStack Ephemeral Instances (Preview)
• Improved parity with Snowflake
• Support for Snowpipe
• Support for Iceberg tables
• Support for Hybrid tables
• Support for Dynamic Tables
• Support for new integrations

Support for new functions

We have added support for several new SQL functions in LocalStack for Snowflake. The new functions include:

Function	Notes
DATEDIFF	Returns the difference between two dates or timestamps.
TIMEDIFF	Returns the difference between two time values.
LEAD	Accesses the value of a subsequent row in a result set without the use of a self-join.
LAG	Accesses the value of a previous row in a result set without the use of a self-join.
INITCAP	Converts the first letter of each word in a string to uppercase and the rest to lowercase.
LAST_QUERY_ID	Returns the query ID of the last executed query in the session.
DESCRIBE FUNCTION	Describes a user-defined function.
SPLIT	Splits a string into an array based on a specified delimiter.
DATE_FROM_PARTS	Constructs a date from individual year, month, and day parts.
FLOOR	Rounds down a numeric value to the nearest integer.
MIN	Returns the smallest value in a set of values.
MIN_BY	Returns the minimum value of a specified column, grouped by another column.
MAX	Returns the largest value in a set of values.
MAX_BY	Returns the maximum value of a specified column, grouped by another column.
COALESCE	Returns the first non-null value in a list of arguments.
DIV0	Returns zero if the divisor is zero; otherwise, it performs division.
LEAST	Returns the smallest value from a list of expressions.
GREATEST	Returns the largest value from a list of expressions.
NVL2	Returns one value if a specified expression is not null, otherwise returns another value.
IS_NULL_VALUE	Checks if a value is null and returns a boolean result.
PARSE_IP	Parses an IP address and returns its components.
OBJECT_KEYS	Returns the keys of an object as an array.
OBJECT_CONSTRUCT_KEEP_NULL	Constructs an object and keeps null values.
DATE_TRUNC	Truncates a date or timestamp to a specified precision.
TIMESTAMP_LTZ	Returns the current timestamp in local time zone.
AS_DOUBLE	Converts a value to double precision.
AS_INTEGER	Converts a value to an integer.
AS_NUMBER	Converts a value to a numeric type.
AS_CHAR	Converts a value to a character string.
DATE_TRUNC	Truncates a date, time, or timestamp to the specified part.
NVL2	Returns different values depending on whether the first argument is NULL.
LEAST	Returns the smallest value from a list of expressions.
GREATEST	Returns the largest value from a list of expressions.
COALESCE	Returns the first non-NULL expression among its arguments.
SPLIT	Splits a string into an array based on a delimiter.
FLOOR	Returns the largest integer less than or equal to a specified value.
DATE_FROM_PARTS	Creates a date from individual parts (year, month, day).
LAST_QUERY_ID	Returns the query ID of the last executed query.
INITCAP	Capitalizes the first letter of each word in a string.
LEAD	Provides access to a row at a given physical offset after the current row.
LAG	Provides access to a row at a given physical offset before the current row.
DATEDIFF	Returns the difference between two dates or timestamps.
TIMEDIFF	Returns the difference between two time values.
HASH	Returns the hash value for the input.
ANY_VALUE	Returns any value from a group, without guarantees about which one.
CONTAINS	Returns TRUE if a string contains a substring.
BETWEEN	Tests whether an expression lies between two other expressions.
MODE	Returns the most frequent value in a group.
AVG	Returns the average (arithmetic mean) of the non-NULL values.
CBRT	Returns the cube root of a number.
CEIL	Returns the smallest integer greater than or equal to a specified value.
ZEROIFNULL	Converts NULL to 0.
ARRAY_UNIQUE_AGG	Returns an array of distinct elements from the input array.
DEGREES	Converts radians to degrees.
RADIANS	Converts degrees to radians.
EXP	Returns e raised to the power of the input.
REPEAT	Repeats a string a specified number of times.
REVERSE	Reverses the characters in a string.
SQRT	Returns the square root of a number.
ASCII	Returns the ASCII code of the first character of a string.
LOG	Returns the logarithm of a number to a specified base.
LN	Returns the natural logarithm of a number.
IS_NULL_VALUE	Tests if an expression evaluates to NULL.
OBJECT_KEYS	Returns the keys of a Snowflake object.
PARSE_IP	Parses an IP address into its component parts.
DESCRIBE FUNCTION	Describes the details of a user-defined function.
CREATE … CLONE	Creates a copy of an existing object in the system.
COPY INTO	Unloads data from a table (or query) into one or more files in internal/external stage or an external location.

You can find more in our functions coverage documentation.

New Web User Interface

We have released a new Web User Interface for LocalStack for Snowflake that provides a user-friendly experience for managing your local Snowflake resources. The interface includes a dashboard that allows you to:

• Run Snowflake SQL queries and view the results using a Query Editor.
• View detailed request/response traces of API calls made to Snowflake.
• Forward queries from the Snowflake emulator to a real Snowflake instance using a proxy.

You can access the Web User Interface by navigating to https://snowflake.localhost.localstack.cloud in your web browser, after starting your Snowflake emulator.

The Web User Interface is still in preview, and we are actively working on adding new features and improving the user experience. Learn more about the latest Web User Interface in our documentation.

Support for LocalStack Ephemeral Instances

We have launched Ephemeral Instances, enabling you to run a LocalStack for Snowflake sandbox in the cloud rather than on your local machine. This ephemeral environment is a short-lived, encapsulated deployment of the Snowflake emulator in the cloud. With these sandboxes, you can run tests, preview features in your Snowflake applications, and collaborate asynchronously within and across your team!

After launching an ephemeral instance, you can switch the Snowflake host in your application to the ephemeral instance’s hostname. This change enables you to interact with the Snowflake emulator running in the cloud as if it were on your local machine. Additionally, Ephemeral Instances allow you to generate a preview environment from GitHub Pull Request (PR) builds.

The feature is in preview, and you can learn more about it in our documentation.

Improved parity with Snowflake

We have made several enhancements to LocalStack for Snowflake to improve compatibility with the Snowflake service. These improvements include:

• Metadata queries and schema lookups can now utilize fully qualified table names, enhancing parity with the Snowflake service.
• Support for identifiers in JDBC prepared statements to incorporate query parameters, with additional improvements to ensure full parity.
• Functions returning VARIANT values, including those that encode dictionary and list values, are now supported.
• Enhanced parsing of parameters in CREATE STAGE statements, now supporting alternative formats such as s3compat://....
• Parity improvements for the creation and deletion of schemas with fully qualified names.
• Enhanced support for inserting timestamps with subsecond precision to match Snowflake behavior.
• Improvements in handling CREATE TABLE AS SELECT (CTAS) with nested subqueries, ensuring better parity.
• Adjustments to the MIN_BY/MAX_BY aggregate functions for more accurate results.
• Proper extraction of database and schema parameters for JDBC connections, ensuring improved compatibility.

Support for Snowpipe

Snowpipe enables data loading into Snowflake tables from files in an external stage, continuously processing files as they become available. It uses a queue to manage this near real-time data loading.

LocalStack for Snowflake now includes support for Snowpipe, allowing you to create and manage Snowpipe objects within the emulator. This functionality lets you load data into Snowflake tables from files stored either in a local directory or an S3 bucket, both locally and remotely. The supported SQL statements for managing Snowpipe include:

• CREATE PIPE
• DESCRIBE PIPE
• DROP PIPE
• SHOW PIPES

Learn more about Snowpipe support in our documentation.

Support for Iceberg tables

Iceberg tables utilize the Apache Iceberg open table format specification, providing an abstraction layer over data files stored in open formats. In Snowflake, Iceberg tables enable schema evolution, partitioning, and snapshot isolation for efficient table data management.

LocalStack for Snowflake now includes support for Iceberg tables, enabling you to create and manage these tables locally. You can use Iceberg tables to query data in Snowflake using the Iceberg format, with data stored in external volumes or local/remote S3 buckets.

For more detailed information on using Iceberg tables, refer to our documentation.

Support for Hybrid tables

Snowflake Hybrid tables, also known as Unistore hybrid tables, facilitate fast, single-row operations by enforcing unique constraints on primary keys and incorporating indexes to expedite data retrieval. These tables are tailored to support both analytical and transactional workloads concurrently, forming the backbone of Snowflake’s Unistore architecture.

LocalStack for Snowflake now includes support for Hybrid tables, enabling the creation and management of these tables locally. The supported SQL statements for managing Hybrid tables include:

• CREATE HYBRID TABLE
• DROP HYBRID TABLE
• SHOW HYBRID TABLES

For more detailed information on using Hybrid tables, refer to our documentation.

Support for Dynamic Tables

Snowflake Dynamic Tables allow a background process to continuously load new data from sources into the table, accommodating both delta and full load operations. These tables automatically update to reflect query results, eliminating the need for a separate target table and custom data transformation code. They are regularly updated through scheduled refreshes by an automated process.

LocalStack for Snowflake now supports Dynamic tables, enabling you to create and manage them locally. The supported SQL statements for managing Dynamic tables include:

• CREATE DYNAMIC TABLE
• DROP DYNAMIC TABLE

For more detailed information on using Dynamic tables, refer to our documentation.

Support for new integrations

We have expanded the integration capabilities, enhancing the Snowflake emulator with new tools and services that streamline development and testing workflows. These integrations focus on improving data quality, schema management, and service compatibility, effectively bridging local development and cloud environments. You can now:

• Run Soda data quality checks within the Snowflake emulator. You can find our sample app on GitHub.
• Apply and manage database migrations locally using Flyway, facilitating schema changes efficiently. Find our detailed guide on our documentation.
• Connect to real or emulated AWS S3 using external volumes for enhanced storage integration and data handling.
• Broaden your local developer environment with integration for AWS services like MWAA, Glue, and EMR, to facilitate multi-cloud testing.

What’s next?

LocalStack for Snowflake is designed to be with you in every step of your Software Development Life Cycle (SDLC) — right from developing data applications, testing infrastructure in continuous integration (CI) pipelines, to deploying ephemeral application previews for team collaboration — to make you confident in shipping your applications to production!

As we look to the future, we are preparing to enhance our interoperability with the actual Snowflake service, expand integrations with AWS and other providers, and introduce emulation of access control and security policies. Here’s what you can look forward to in the upcoming months:

• Enhanced support for the internal type system of the emulator, to further enhance parity for different data types.
• Advanced integration of Cloud Pods to support test data management in CI, and allow merging of database table rows.
• Enhanced implementation coverage for additional Snowflake SQL functions.
• Support for automated replication mechanism to copy resources from the real Snowflake service into the Snowflake emulator.
• Implement further integrations between AWS services and Snowflake in the local emulator, including Kinesis Firehose streaming, EMR/Glue data jobs, SageMaker for ML training/inference, etc

We are immensely thankful to our community and users for their insightful suggestions, feedback, and bug reports shared through our Slack community. Your ongoing support has been instrumental, and we are grateful for it!

Learn more

• Install the LocalStack for Snowflake emulator by following our new tutorial video.
• Watch the webinar recording on testing data pipelines with the Snowflake emulator.
• Navigate to our documentation & tutorials to try out the various features.
• Check out our Snowflake samples to explore a variety of use cases that you can run locally.
• Have questions? Join the LocalStack Slack Community to get help.

New to LocalStack? Create a free account today and get started!