# YouPrep™ Native SQL ## Overview YouPrep™ Native SQL is our powerful option that allows to leverage all the power of your Data warehouse 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 {% hint style="info" %} In our [new data query execution system](https://docs-v3.toucantoco.com/data-management-in-datahub/new-data-execution-system), we support the following connectors in NativeSQL: * PostGreSQL * Google Big query * MySQL * AWS Athena * ClickHouse and we plan to support more, keep track of our progress in our [release note](https://docs-v3.toucantoco.com/additional-ressources/latest-releases) {% endhint %} ## 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. {% hint style="info" %} This feature is only available for datasets connected to Snowflake, Google Big Query, AWS Redshift, AWS Athena, PostgreSQL {% endhint %} ## How to activate the NativeSQL option This option is activated by default on Toucan. {% hint style="warning" %} **Warning** This option is activated at the instance level, which means that it will be applied for all your Apps. {% endhint %} ## Some limitations {% hint style="info" %} **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). {% endhint %}