Snowflake Transformations Best Practices

Introduction to Snowflake Transformations in Keboola

Snowflake is a powerful cloud-based SQL data platform, widely leveraged within Keboola for managing data transformations. However, to harness its full potential, it's essential to adopt certain best practices that significantly enhance query performance, maintainability, and reliability. In this comprehensive guide, we explore key strategies and practical examples to help you optimize your Snowflake transformations effectively in Keboola.

Avoid Using 'ALTER SESSION' Statements

One crucial best practice for Snowflake transformations within Keboola is to avoid using ALTER SESSION statements. Altering sessions can lead to unpredictable results and inconsistencies between loading, unloading, and workspace sessions. For example, altering session-level time zone settings can cause discrepancies in timestamp data across different parts of your transformation.

Instead of relying on session-specific settings, explicitly use Snowflake's built-in functions to adjust timestamps to your desired format and time zone:

SELECT CONVERT_TIMEZONE('UTC', 'America/New_York', current_timestamp()) AS ny_timestamp;

This approach provides consistency and avoids unexpected issues arising from session-specific settings.

Explicitly List Columns in SELECT Queries

While using SELECT * may seem convenient, it can cause unpredictable results. Adding new columns unexpectedly can lead to issues in downstream processes relying on specific column structures. Explicitly listing columns ensures your transformations remain consistent and predictable.

Instead of this:

SELECT * FROM customer_data;

Use:

SELECT customer_id, customer_name, customer_email FROM customer_data;

This practice is especially beneficial for users migrating from legacy databases such as Oracle, as Snowflake performs better with simpler, explicit queries.

Break Complex Nested Queries into Manageable Steps

Complex nested queries can be challenging to read, debug, and maintain. Snowflake performs optimally when queries are broken down into smaller, simpler steps. Consider splitting complex queries into separate CREATE TABLE statements and intermediate tables.

Instead of a complex nested query:

SELECT a.customer_id, b.total_spent FROM (SELECT customer_id FROM customers WHERE active = 1) a JOIN (SELECT customer_id, SUM(amount) AS total_spent FROM purchases GROUP BY customer_id) b ON a.customer_id = b.customer_id;

Simplify by creating intermediate tables first:

CREATE TABLE active_customers AS SELECT customer_id FROM customers WHERE active = 1;CREATE TABLE customer_spending AS SELECT customer_id, SUM(amount) AS total_spent FROM purchases GROUP BY customer_id;SELECT a.customer_id, b.total_spent FROM active_customers a JOIN customer_spending b ON a.customer_id = b.customer_id;

This method greatly improves readability, ease of debugging, and query performance.

Utilize Stored Variables for Efficient Query Management

Stored variables in Snowflake scripts enhance maintainability and flexibility. Define variables at the start of your script to reference repeatedly throughout your queries. If a value needs updating, you only need to change it in one place.

SET reporting_date = '2023-01-01';SELECT * FROM sales_data WHERE sale_date = $reporting_date;

Replace Complex CASE Statements with Mapping Tables

Complex CASE statements often degrade query performance. Instead, create mapping tables for categorical conversions and join them with your result tables. This approach simplifies query logic and improves overall efficiency.

Instead of:

SELECT customer_id, CASE WHEN country='US' THEN 'North America' WHEN country='DE' THEN 'Europe' ELSE 'Other' END AS region FROM customers;

Use mapping tables:

CREATE TABLE country_region_map (country VARCHAR, region VARCHAR);INSERT INTO country_region_map VALUES ('US', 'North America'), ('DE', 'Europe');SELECT c.customer_id, COALESCE(m.region, 'Other') AS region FROM customers c LEFT JOIN country_region_map m ON c.country = m.country;

Create Views for Repeated Joins

Utilizing views in Snowflake enhances performance by pre-caching join results. Instead of repeating the same join logic across multiple queries, create a view to reuse the join efficiently.

CREATE VIEW customer_orders AS SELECT c.customer_id, o.order_id, o.order_date FROM customers c JOIN orders o ON c.customer_id = o.customer_id;

This practice simplifies queries and boosts performance.

Use 'NULLIF' for Handling Null Values Efficiently

For handling null values, Snowflake's NULLIF function is faster and more efficient than alternatives such as TRY_CAST:

SELECT product_id, NULLIF(price, 0) AS adjusted_price FROM products;

Careful Handling of Current Timestamps and Timezones

Timestamps can be tricky when handling multiple time zones or formats. Always test your queries in workspace environments to validate accuracy before deployment.

SELECT CURRENT_TIMESTAMP() AS default_tz,CONVERT_TIMEZONE('UTC', 'America/Los_Angeles', CURRENT_TIMESTAMP()) AS la_tz;

Use 'Abort Transformation' in Keboola for Data Integrity

Keboola offers the Abort Transformation option, which allows you to halt transformations if certain conditions are unmet. This feature helps maintain data integrity by preventing execution errors or incorrect results.

IF (SELECT COUNT(*) FROM source_table) = 0 THENABORT_TRANSFORMATION('Source table is empty, aborting process.');END IF;

Adjust Backend Size for Complex Queries (Non-Pay-As-You-Go)

For large datasets or complex long-running queries, Keboola allows the configuration of different Snowflake backend sizes (small, medium, large). Increasing the backend size can substantially speed up transformations. Note that this feature is unavailable in the current pay-as-you-go model.

Conclusion: Optimize Transformations for Maximum Efficiency

Implementing these best practices for Snowflake transformations within Keboola ensures your data transformations run efficiently, reliably, and maintainably. By explicitly handling timezones, simplifying queries, leveraging variables, avoiding complex statements, and utilizing Keboola's advanced features, you can significantly enhance your data processes and business outcomes.