🧑‍🍳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:

Step	PostgreSQL	Snowflake	Redshift	BigQuery	Athena
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).