Snowflake SQL Transforms

Here we'll delve into how you can leverage Ascend.io's transform capabilities with Snowflake SQL, employing Jinja syntax for dynamically referencing upstream components. This guide is crafted for users who have a solid grasp of SQL and are somewhat acquainted with the basics of Jinja templating.

Prerequisites

Before proceeding, ensure you have:

• A good understanding of Snowflake SQL.
• A basic familiarity with Jinja syntax.
• An active account on Ascend.io with the requisite permissions.

Understanding Jinja Syntax in Ascend

Jinja, a templating engine for Python, is utilized by Ascend to facilitate the generation of dynamic SQL queries. Within Ascend's platform, Jinja empowers you to dynamically reference components and variables in your SQL queries, enhancing flexibility and efficiency.

Basic Syntax

• {{ ... }}: Employed to render expressions or variables.

Referencing Upstream Components

To dynamically incorporate an upstream component into your SQL query, follow these steps:

1. Identify the Component: First, pinpoint the component you wish to reference, be it a table, a view, or another transform.
2. Utilize Jinja Syntax: Next, deploy Jinja templating syntax to seamlessly integrate the chosen component's name into your SQL query.

In the example below, {{ Raw_Weather_Data }} dynamically targets an upstream component labeled 'Raw Weather Data,' seamlessly replacing spaces in the component name with underscores.

SELECT
 w.*,
 CASE
   WHEN w.PRECIPITATION >= 0.55 THEN 'Rainy'
   ELSE 'Not Rainy'
 END as weather
FROM
 {{ Raw_Weather_Data }} AS w

Testing Your Queries

For guidance on testing your queries to ensure they execute as anticipated before going live, see Query Testing(coming soon!). This segment offers comprehensive instructions and best practices for query verification.