🔰
SQL Transform
Previously known as Flows. This page contains overview and Detailed Feature Walkthrough
SQL Transform is a powerful feature on Sprinkle that enables you to create advanced tables from the input source tables using SQL queries. It serves as the building block for creating models.
⚡
Data from various sources are imported into Sprinkle as tables. These tables can be further joined to create SQL Transform tables (tables created in a SQL Transform) from which the models can be created.
Using SQL Transform, you can join various tables in the warehouse together, cast data types of columns & create new measures, as required.
💡
SQL Transform : Explanation & Feature Walkthrough
From the left navigation panel, Click on SQL Transform to go to the SQL Transform listing page. The listing page has two tabs, My SQL Transform & Shared with me, it lists the various SQL Transforms that has been created.
🖱
Click on New to create a new SQL Transform.
🖱
Create a New SQL Transform
SQL Transform View Page
Enables browsing through the Schemas & tables in the warehouse. Through the search bar, easily search for the table that is needed. Click on the table, to see the names and the data types of the columns in the table.
🔍
🖱
Create/Edit SQL scripts. Write scripts to join the tables to create a new table, cast the data types of the columns, create new measures, etc. All SQL queries are supported here. Click on Save and Run, once you are done editing the script to view the results.
👨💻
🖱
The Job section shows the last three jobs that were run and related information. Jobs are run when Clicking Save and Run or Run on the top right corner.
🖱
The SQL Transform owner or other users with access can add tags to the SQL Transform. Go through the detailed documentation on Tags.
More Options
- Auto-Run: Set Auto-Run, to run the SQL Transform periodically
- Clone: SQL Transforms can be Cloned (or Copied) and used to build over the top of existing SQL Transform.
- Share: SQL Transforms can be shared with users and user groups like Admin, Developer, Analyst, Report Viewer
- Delete: To Delete the SQL Transform
- View Activity: User activity on the SQL Transform can be viewed using this option
- Settings: Manage Folder, Owner, Run Settings, Variables, Location, Managed Data Loading, Incremental Processing
From the More Options tab, click on Settings to add advanced settings to your SQL Transform.
There are 4 tabs in the settings, General, Variables, Cleanup, and Data Loading.
This section enables you to edit the Folder and Owner of the SQL Transform created.
Folder: You can choose any folder you have access to, from the drop-down menu. By default, the SQL Transform is created in the SharedSpaces folder.
📁
Owner: A drop-down list also lets you choose the owner from the list.
👤
Cleanup is useful when creating external tables. Specify the location directly in the field when creating external tables. The contents of the directory will be deleted before the SQL Transform script is executed. The data is recreated at the location afterwards.
🧹
Variables can be set here to the initial values, making it easy to manage and edit them. Variables come in handy if you are using the same value at multiple places in the SQL Transform script. Use them as $(variable_name) in the script.
Your SQL Transform may sometimes have a DROP TABLE in the script based on your requirement. But if there are reports that are based on the table that is getting dropped, it may end up failing. While dropping temporary tables is not a problem, dropping tables that power reports can become a problem.
Solution! Let Sprinkle manage the loading of the data onto the final table, while you drop, and load the Staging Table.
🎉
✨
A Staging table is a table where data is stored temporarily
💡
How Does it Work?
🤓
Create a Staging Table and specify Staging Table and Final Table in the Settings section. First, start with making changes to your script. Modify the code that handles loading the data into the final table. Instead, load the data into the Staging Table.
Staging Table Highlighted in the SQL Transform Script
Then Save the script, click on Settings and browse to the Data Loading tab. Click on the checkbox, and provide values for Staging and Final Tables. Save the settings.
🖱
☑
Under the hood, Sprinkle takes care of loading data into the final table. In the end, the final table will look exactly like the staging table.
Incremental processing helps in reducing the pipeline run time by reducing the data crunched. This builds on the concept of managed data loading by adding a few more abstractions. You still have to provide staging and final table names. Just like above, the final table is actually a view over a sprinkle-managed table. The sprinkle-managed table is not visible to you, but the view is.
⏳
When data gets loaded into the final table, instead of replacing the complete data, if you want to upsert only new/changed data, then you can enable the incremental processing.
☑
For Incremental processing, a time column having data in the format of yyyy-mm-dd must be present in your final table. Sprinkle will internally maintain the checkpoint of the time column for the last successful run. In the SQL Transform script, you can get the value of the checkpoint using the ${sprinkle_latest_checkpoint} variable.