Toucan 3.0
YouPrep documentationHelp centerGet a demo
  • Welcome
    • ๐Ÿ‘‹Welcome to Toucan
    • โš™๏ธTechnical resources
      • โš™๏ธToucan stack
      • Setup mode
        • Toucan SaaS mode
      • โš™๏ธSecurity
        • Application Security
        • Source Code Quality
        • Global Security Practices
        • Security of Docker Images
  • TUTORIALS
    • ๐Ÿ“ŠGetting Started : Embedded Analytics
    • ๐Ÿค“Advanced tutorials
      • Embedding a story with user attributes
        • Dynamic filter with user attributes
        • Dynamic Tables
        • Dynamic Database
        • Dynamic Host
      • Using the HTTP API connector in advanced use cases
      • Using advanced syntax for SQL queries
      • Merging filters with our tool
      • Deep customization chart (CSS)
        • Homepage customization
        • Chart customization
        • Dashboard customization
  • Data Management
    • ๐ŸงฎOverview of Data In Toucan
    • ๐Ÿ“กDatasources in Toucan
      • ๐Ÿ”ŒManaging Connectors
        • ๐Ÿ”ŒCreating, editing and deleting a connector
        • ๐Ÿ”ŒSet up OAuth2 credentials for your platform
        • ๐Ÿ”ŒSetting up a connector
          • ๐Ÿ”ŒGeneric Connectors
            • ๐Ÿ”ŒSetting up an HTTP API connector
            • ๐Ÿ”ŒSetting up an ODBC Connector
          • ๐Ÿ”ŒDatabase and data warehouse Connectors
            • ๐Ÿ”ŒSetting up an AWS Redshift Connector
            • ๐Ÿ”ŒSetting up a Snowflake Connector
            • ๐Ÿ”ŒSetting up a PostgreSQL Connector
            • ๐Ÿ”ŒSetting up a Google Big Query Connector
            • ๐Ÿ”ŒSetting up an AWS Athena connector
            • ๐Ÿ”ŒSetting up a MySQL connector
            • ๐Ÿ”ŒSetting up a MongoDB connector
            • ๐Ÿ”ŒSetting up a Microsoft SQL Server connector
            • ๐Ÿ”ŒSetting up an Azure SQL connector
            • ๐Ÿ”ŒSetting a Databricks Connector
            • ๐Ÿ”ŒSetting up a ElasticSearch Connector
            • ๐Ÿ”ŒSetting up a Clickhouse Connector
          • ๐Ÿ”ŒOnline services connectors
            • ๐Ÿ”ŒSetting up a Sharepoint Connector
            • ๐Ÿ”ŒSetting up a Google Sheets Connector
            • ๐Ÿ”ŒSetting up a Salesforce Connector
            • ๐Ÿ”ŒSetting up a Hubspot Connector
          • ๐Ÿ”ŒSetting up an AWS S3 connector
      • ๐Ÿ—„๏ธManaging remote file storages
        • ๐Ÿ—‚๏ธSetting up a file storage
          • SFTP
        • ๐Ÿ“„Create a dataset from a file
      • ๐Ÿ“Managing Files
        • ๐Ÿ“Adding, editing and deleting local files
        • ๐Ÿ“‚Using advanced file settings
        • ๐Ÿ“Adding and combining remote files in Toucan
    • ๐Ÿ”ขDatasets in Toucan
      • ๐Ÿ”ขStored and Live Datasets
      • ๐Ÿ’ฟManaging datasets
        • ๐Ÿ”ขCreating datasets
        • ๐Ÿ”ขEditing, Duplicating and Deleting a dataset
        • ๐Ÿ”ขRefreshing and Publishing Datasets
        • ๐Ÿ“ˆOptimize data performance
        • ๐Ÿ—‚๏ธAdding indexes to stored datasets
        • ๐Ÿ‘ฉโ€๐Ÿ’ปCode mode and single mode
      • ๐Ÿ›‘Setting permissions on dataset
      • ๐Ÿ—ƒ๏ธMaintaining Data
        • ๐Ÿ—ƒ๏ธTagging datasets
        • ๐Ÿ—ƒ๏ธIdentifying datasets dependencies
        • ๐Ÿ—ƒ๏ธSet validation rules
    • ๐Ÿง‘โ€๐ŸณPreparing data
      • Overview of YouPrepโ„ข
        • ๐ŸŽนColumn header
          • Rename column
          • Duplicate column
          • Fill null values
          • Replace values
          • Sort values
          • Convert columns data types
        • Add
          • Add text column
          • Add formula column
          • Add conditional column
        • Filter
          • Delete columns
          • Keep columns
          • Filter rows
          • Top N rows
          • ArgMax
          • ArgMin
        • Aggregate
          • Group by
          • Add total rows
          • Hierarchical rollup
          • Get unique groups/values
        • Compute
          • Compute evolution
          • Cumulated sum
          • Percentage of total
          • Rank
          • Moving average
          • Compute statistics
          • Absolute value
        • Text
          • Concatenate
          • Split column
          • Extract substring
          • To lowercase
          • To uppercase
          • Compare text columns
          • Trim spaces
          • Replace text
        • Date
          • Convert text to date
          • Convert date to text
          • Extract date information
          • Add missing dates
          • Compute duration
          • Normalize date granularity
        • Reshape
          • Pivot
          • Unpivot
          • Waterfall
        • Combine
          • Append datasets
          • Join datasets
        • Geo
          • Geographic dissolve
          • Geographic hierarchy
          • Geographic simplify
          • Prepare geo data (with basemap)
      • YouPrepโ„ข Native SQL
      • Hybrid pipeline
    • โžฟManaging variables in Toucan
      • โžฟVariables hub
      • โ™ˆUse variables in YouPrepโ„ข
      • โžฟEasy reference to variables
    • ๐ŸงžUsing advanced data concepts
      • ๐ŸงžData personnalisation with user attributes
        • Connector setup with a user attribute
        • Database selection with a user attribute
        • YouPrep data filtering with a user attribute
        • Filter data in SQL with a user attribute
      • ๐ŸงžAdvanced syntax for variables
      • ๐ŸงžData cache
  • Visualizations and Layouts
    • ๐Ÿ“บApps
      • ๐Ÿ“บManaging Apps
        • โž•Creating Apps
        • ๐Ÿ“„Duplicating Apps
        • ๐Ÿ–จ๏ธPublishing Apps
        • ๐ŸšฎDeleting Apps
        • โœ๏ธEditing within an App
      • ๐Ÿ–Œ๏ธCustomizing Apps
        • Customizing chart color elements
        • Customizing the app's font
        • Adding Assets
        • Adding Glossary
        • Setting up, Managing and testing custom visibilities
        • Customizing the "no data error" message
        • Creating a dynamic background based on an Filter's column
      • ๐Ÿ Home
        • Creating the Home
        • Creating Tiles
          • Tile Dynamic Value
          • Tile Leaderboard
          • Tile Line
          • Tile Scorecard
          • Tile Bullet
          • Tile Heatmap
          • Tile PDF
          • Tile Video
          • Tile Image
          • Tile Text
          • Tile HTML
          • Tile Separator
      • โœจStories
        • Creating a Story
        • KPIs
        • Narrative
        • Crossfilter
      • ๐Ÿ“ฝ๏ธFilters
        • Managing Filters
          • Creating, reusing and editing Filters
          • Applying Filters
          • Unpinning and deleting Filters
        • Type of Filters
          • Dropdown
          • Checkboxes
          • Buttons
          • Date Range
          • Hierarchical
          • Slider
        • Templating from Filters' values
        • Dependant Filters
      • ๐Ÿ“ˆPDF Report
      • ๐ŸŽกDatawall
      • ๐Ÿ—๏ธDashboard Builder
        • Create a Dashboard Builder
        • Embed a Dashboard Builder
        • Dashboard export options
      • ๐ŸŒŸMyFavorites
    • ๐Ÿ“ŠCreating Visualizations
      • ๐ŸคฉViz Gallery
        • Barchart
        • Barlinechart
        • Bubblechart
        • Bulletchart
        • Circularchart
        • Funnelchart
        • Gantt chart
        • Heatmap
        • HTML
        • Leaderboard
        • Leaderboard Centered Average
        • Linechart
        • Mapchart
          • Configure a drill
        • Mediachart
        • Radarchart
        • Tablechart
        • Timeline
        • Versuschart
        • Waterfallchart
        • Score Card
        • Stacked Barchart
      • ๐Ÿง Common Chart Configuration
      • ๐Ÿ’…Customizing chart colors
      • ๐Ÿงžโ€โ™‚๏ธAdvanced chart configuration
        • Templating from chart's dataset
        • Add units, precisions and sentiments
        • Adding Tutorials
        • Add sparklines
        • Navigate with stories
        • Group informations in your stories
        • Multiple charts in one story
        • Manage dates
        • Customize tiles' sources
        • Add stars to tiles' title
        • Manage data order in your tiles
        • Navigate with tiles
    • ๐Ÿ‘ฉโ€๐Ÿ’ปEmbedding
      • ๐Ÿ”Authentication
      • ๐Ÿ–‡๏ธIntegration
        • Generate and manage embeds
        • Customize embeds
        • Embedding a Toucan App Using iFrames
        • Passing Extra Variables to Your Toucan Embed
      • โš™๏ธEmbed SDK
        • Embed SDK Authentication
      • โ“FAQ
    • ๐Ÿ™‹Self-Service
      • Self-Service Dashboard
      • Self-service PDF Report
  • Collaboration
    • โฐCreating alerts
    • ๐Ÿ“งManaging notifications
    • โž•Enriching a story with descriptions
    • ๐Ÿ’ŒSharing content
    • ๐Ÿ’ฌAdding comments to stories
  • Administration
    • Page
    • โš™๏ธInstance Management
      • โš™๏ธManaging operations in SaaS
      • โš™๏ธCustomizing your instance (whitelabel)
    • ๐Ÿ‘ฅManaging Users
      • ๐Ÿ‘ฅUsers
      • ๐Ÿ‘ฅManaging user groups
      • ๐Ÿ‘ฅManaging user properties
      • ๐Ÿ‘ฅSetting up permissions and visibilities
    • ๐ŸŒManaging languages in Toucan (internationalisation)
    • ๐Ÿ“ˆMonitoring Engagement with User Analytics
      • ๐ŸŽ›๏ธHow to Filter your User Analytics?
      • ๐Ÿ–ฅ๏ธUnderstanding your User Analytics Dashboards
  • Additional Ressources
    • ๐Ÿ“šExternal documentation
    • ๐ŸšSupport for App-builders
    • ๐Ÿ†•Latest releases
      • ๐ŸŽ2025 Releases
      • ๐ŸŽ2024 Releases
      • ๐ŸŽ2023 Releases
    • ๐Ÿ”งTroubleshooting
      • Troubleshoot:: DataHub
      • Cross-Site Cookies
      • How to :: read the inspector error
      • How to :: troubleshoot the toucan way
Powered by GitBook
On this page
  • Overview
  • Step coverage
  • Hybrid Pipeline
  • How to activate the NativeSQL option
  • Some limitations

Was this helpful?

  1. Data Management
  2. Preparing data

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).

Last updated 11 months ago

Was this helpful?

๐Ÿง‘โ€๐Ÿณ
โœ…
โœ…
โœ…
โœ…
โœ…
โŒ
โŒ
โŒ
โŒ
โŒ
โœ…
โœ…
โœ…
โœ…
โœ…
โŒ
โŒ
โŒ
โŒ
โŒ
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โŒ
โŒ
โŒ
โŒ
โŒ
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โŒ
โŒ
โŒ
โŒ
โŒ
โœ…
โœ…
โœ…
โœ…
โœ…
โŒ
โŒ
โŒ
โŒ
โŒ
โœ…
โœ…
โœ…
โœ…
โœ…
โŒ
โŒ
โŒ
โŒ
โŒ
โœ…
โœ…
โœ…
โœ…
โœ…
โŒ
โŒ
โŒ
โŒ
โŒ
โœ…
โœ…
โœ…
โœ…
โœ…
โŒ
โŒ
โŒ
โŒ
โŒ
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โŒ
โŒ
โŒ
โŒ
โŒ
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โŒ
โŒ
โŒ
โŒ
โŒ
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โœ…
โŒ
โŒ
โŒ
โŒ
โŒ