SQL Steps

User interface

General Principles Add Missing Dates Add text column Add Total Rows Aggregate Append Compare Text Columns Argmax Argmin Concatenate columns Convert Convert date to text Convert text to date Cumulated sum Custom step Delete column(s) Geographically dissolve data Duplicate Duration Evolution Extract date information Extract substring Fill null Filter Formula Geographical hierarchy Get unique groups/values Hierarchical rollup ifthenelse Join Keep column(s) Moving Average Percentage Pivot Rank Rename Replace Geographical simplification Sort Split column Column's Statistics To lowercase To uppercase Top N rows Unpivot Waterfall

Technical documentation

Packages Steps Designing a new step Known limitations Variables UI components Custom code editor Python package

Steps definition

This document describes the steps that can be used in our SQL Translator pipeline. A step is the unit of transformation generated by a user interaction with the SQL visual query builder.

Implementation logic

The steps were implemented using the following concepts:

A step is named following this convention: **\_STEP\_**
A pipeline starts with a select step using the query provided by the user like below
```
WITH SELECT_STEP_0 AS (<USER'S QUERY STRING>)
```
Each step concatenates the resulting query string it generates to the query string received in input For example, a SelectStep followed by a FilterStep results in
```
WITH SELECT_STEP_0 AS (<USER'S QUERY STRING>),
FILTER_STEP_1 AS (SELECT * FROM SELECT_STEP_0 WHERE PRICE > 10)
```
Each step updates the final selection query with its name For example, the query string sent to the data provider for a SelectStep followed by a FilterStep would be
```
WITH SELECT_STEP_0 AS (<USER'S QUERY STRING>),
FILTER_STEP_1 AS (SELECT * FROM SELECT_STEP_0 WHERE PRICE > 10)
SELECT * FROM FILTER_STEP_1
```

Metadata

The pipeline alone is not sufficient to be able to produce the query. SQL required the exhaustive list of columns to select from the moment we want to exclude or rename one.

For this reason, while crafting the query, the translator needs to request the columns names, once per source table. It keeps them in a MetadataManager that get transmitted and updated across steps.

Requirements

snowflake_query_describer
snowflake_query_executor