Snowflake has recently announced product enhancements, and in our previous post, we introduced the first instalment dedicated to SQL. Now, let's delve into part 2 of these significant announcements that promise to greatly assist you in your upcoming projects and work.
In this instalment, we focus on four of the most recent SQL advancements introduced by Snowflake in 2023.
Search Optimization Service Updates
Whenever the need arises to enhance the performance of lookups or any analytical query that involves specific predicate-based filtering, the optimal solution is to leverage search optimization. This service has proven invaluable in accelerating the execution of queries on structured, semi-structured, and geographic data, all while being more cost-effective.
Latest Improvements
Search Optimization has expanded its capabilities to include the following additional features:
- Ability to select specific columns for search optimization
Maintaining search optimization can become costly when the data in the table experiences frequent changes. In such scenarios, enabling search optimization on specific columns proves to be advantageous. Here are the recently introduced methods to facilitate this.
| Search Method | Description |
|---|---|
| EQUALITY | Equality and IN predicates. |
| SUBSTRING | Predicates that match substrings and regular expressions (e.g. LIKE, ILIKE, RLIKE, REGEXP_LIKE, etc.) |
| GEO | Predicates that use GEOGRAPHY types. |
Here are some examples demonstrating the utilization of the methods mentioned above
- Faster searches in semi-structured data and geography data types
As per new feature update, the support for semi-structured data and geography data type has been extended. It has improved the efficiency of point lookup queries executed on VARIANT data, like JSON structures. For this, specifically we need to enable this feature using ON command as below.
- Faster substring searches in string columns
The new feature updates have enhanced the efficiency of lookup queries which involves substring operation like LIKE and ENDSWITH.
Whenever a request of this nature is made, it will result in a notable enhancement in performance.
LIKE '%STRING%'
LIKE '%COMPANYNAME%ID%STREET%'
- Improved performance with Search Optimization and Query Acceleration Service working together
The additional computational capacity offered by the query acceleration service can be integrated with the performance enhancement delivered by search optimization. By collaborating, query acceleration and search optimization can synergize to enhance query performance.
It works in below manner:
Step 1: Initially, search optimization trims down unnecessary micro-partitions for a query.
Step 2: Subsequently, for eligible queries, query acceleration can delegate parts of the remaining workload to shared compute resources furnished by the service.
The performance of queries benefiting from both services can fluctuate based on the workload and the resources at hand
Image Credits: Snowflake Documentation
Email Notifications
Previously, Snowflake had restricted support for email features, primarily accessible within Snowflake Resource Monitor and Snowflake task error notifications for Snowflake-AWS.
With the latest feature enhancements, Snowflake now provides a pre-built stored procedure for sending emails, which can be valuable whenever the need arises for email functionality.
To make use of this, your first step will be to establish the notification integration, as outlined below.
Call the send email stored procedure.
Notes
- This feature is compatible with Amazon Web Services (AWS) Simple Email Service (SES).
- Snowflake will preserve the email content for a duration of up to 30 days.
- Each account can set up a maximum of ten email integrations and can activate one or more of them simultaneously.
- Notification history can be check from notification history table function using below query.
Dynamic Tables
Previously, the process of loading incremental data was quite intricate, requiring numerous steps, including the creation of Streams, tasks, and the development of complex SQL logic for incremental data loading.
However, these steps have been streamlined and simplified thanks to the introduction of Dynamic tables. Dynamic tables are akin to views but offer a broader range of functionality compared to traditional views.
Now, whenever data arrives at the source, the dynamic tables automatically update with only the incremental data. This eliminates the need for developing intricate pipelines, streams, and task logic.
Dynamic tables also come with the added benefit of refresh lag capacity, allowing us to define refresh intervals that instruct the system on how frequently to refresh our data.
Why should we use Dynamic Table?
- Setting up automatic incremental refreshes is a breeze, requiring just two simple steps and no complexity.
- Dynamic tables help in reducing compute usage.
- Enables consumers to access it in nearly real-time without relying on streams or intricate SQL logic.
- Demonstrated a notable performance enhancement compared to conventional table streams for tables experiencing high turnover.
Image Credits: Snowflake Documentation
2 simple steps to schedule your incremental load-
At BlueCloud we investigate and maximize the potential of the mentioned features for every scenario where they can be effectively implemented.