Tableau has continually evolved Tableau Prep since its release in 2018. The most recent Tableau 2020.3 release added an especially important feature. With this release, Tableau Prep can now write data directly to an external database.
Below are some of the on premise/cloud systems that are supported currently for exporting the data from Tableau Prep:
- SQL Server
- PostgreSQL
- MySQL
- Teradata
- Oracle
- Snowflake
- Amazon Redshift
In this blog post, we will explore the new feature with Snowflake as the database.
For this blog, we assume basic knowledge of the Snowflake setup.
For more details, please refer to our blog:
– Snowflake Live Connector in Salesforce Einstein Analytics Studio
– Snowflake on Google Cloud
Getting Started
We are using an existing Superstore flow in Tableau Prep.
In this flow, we will export the data of Superstore sales to Snowflake.
In the Output step, under the Save Output To dropdown, select ‘Database Table’
Choose the Snowflake connection under the Connection -> Select a server dropdown. This will open a new dialog box where you can enter your connection details.
Upon successful login to the Snowflake server, specify the Warehouse, Database and Schema to which the data needs to be exported. Under the Table drop down, choose the table if it is already existing or enter the Table name that needs to be created. In this demo, we are creating a new table “Superstore Sales”.
Writeback Options
Now that we have all the necessary information, Tableau has provided us with 3 different ways in which we can write the data:
| Option | Description |
| Create Table | If the table exists, it will be deleted and replaced with a new table using the fields in the flow. Any existing structure will be discarded |
| Append to Table | Add data to the existing table. If the table doesn’t exist, it’s created when the flow is first run |
| Replace Date | Replace Data in the existing table, if the table doesn’t exist, it’s created when the flow is first run |
In addition to the above 3 options, we have an option to provide the Custom SQL which can be executed Before or After the data is written to the table.
In this demo, we have selected the Option “Replace Data”.
Like every step in the Tableau Prep, we have the option of previewing the data which provides us the mapping between the fields in the flow with the database table.
Validating the Data
Upon successful completion of the flow, we can validate the data in Snowflake.
Note:
Before we connect the Output Step to Snowflake, we need to have the
warehouse mode enabled “Auto Resume” or have a Custom SQL “alter warehouse ‘warehouse_name’ ” so that the data export is successfully completed.
Handling Network Failures
Tableau Prep also prevents partial or corrupt loads due to network or system outages. This is accomplished by loading data first to a temporary table before the full data load is pushed into the main table.
Conclusion
With the release of Tableau 2020.3, we now have the option to write data directly to external databases. And using Tableau Prep’s “Append to Table” setting, we can incrementally update data into our target system, giving users the data they need for actionable insights as quickly as possible.
