🧑🍳YouPrep™ Native SQL
Overview
YouPrep™ Native SQL is our powerful option that allows to leverage all the power of your Datawarehouse or database, by delegating YouPrep transformation steps directly to your datasource.
How does it work? We translate your transformation pipeline made with YouPrep directly into one SQL statement. It's very powerful, specially when you must deal with a lot of data within your tables.
The NativeSQL option is available for several connectors:
Athena
BigQuery
PostgreSQL
Redshift
Snowflake
Step coverage
Find below the availability of YouPrep steps under Native SQL:
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
✅
✅
✅
✅
✅
Delete columns
✅
✅
✅
✅
✅
Geographically Disolve
❌
❌
❌
❌
❌
Duplicate
✅
✅
✅
✅
✅
Duration
✅
✅
✅
✅
✅
Evolution
✅
✅
✅
✅
✅
Extract date information
✅
✅
✅
✅
✅
Extract substring
✅
✅
✅
✅
✅
Fill null
✅
✅
✅
✅
✅
Filter
✅
✅
✅
✅
✅
Formula
✅
✅
✅
✅
✅
Geographical hierarchy
❌
❌
❌
❌
❌
Get unique group(s) / value(s)
✅
✅
✅
✅
✅
Hierarchical rollup
❌
❌
❌
❌
❌
IfThenElse
✅
✅
✅
✅
✅
Join
❌
❌
❌
❌
❌
Keep column(s)
✅
✅
✅
✅
✅
Moving average
❌
❌
❌
❌
❌
Percentage
✅
✅
✅
✅
✅
Pivot
❌
❌
❌
❌
❌
Rank
✅
✅
✅
✅
✅
Rename
✅
✅
✅
✅
✅
Replace
✅
✅
✅
✅
✅
Geographical Simplification
❌
❌
❌
❌
❌
Sort
✅
✅
✅
✅
✅
Split column
✅
✅
✅
✅
✅
Columns statistics
❌
❌
❌
❌
❌
To lowercase
✅
✅
✅
✅
✅
To Uppercase
✅
✅
✅
✅
✅
Top N rows
✅
✅
✅
✅
✅
Unpivot
✅
✅
✅
✅
✅
Waterfall
❌
❌
❌
❌
❌
Hybrid Pipeline
To address the issues caused by the lack of support of some steps, we have implemented a hybrid pipeline approach.
This means you can have a unique pipeline mixing NativeSQL supported steps and unsupported steps. All transformation steps can be executed regardless of context, with the flexibility to execute the pipeline partially in NativeSQL and partially in Toucan, depending on the steps utilized.
NativeSQL steps are executed first whenever possible. An unsupported step causes the Toucan engine to take over the pipeline.
This feature is only available for datasets connected to Snowflake, Google Big Query, AWS Redshift, AWS Athena, PostgreSQL
How to activate the NativeSQL option
This option is activated by default on Toucan.
Warning
This option is activated at the instance level, which means that it will be applied for all your Apps.
Some limitations
Note
Data transformation made with YouPrep on columns must be explicit (on a precise column). It's not possible to make a data transformation step on a non-predefined column (for example by referring to the column though the usage of a variable).
Last updated