Writing to External Databases…..the Missing Piece in the Tableau Prep Functionality

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.

Superstore flow in Tableau Prep

In the Output step, under the Save Output To dropdown, select ‘Database Table’

Save Output To

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.

Snowflake connection

snowflake

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

save output

 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:

OptionDescription
Create TableIf 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 TableAdd data to the existing table.
If the table doesn’t exist, it’s created when the flow is first run
Replace DateReplace 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.

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

Replace Data

Validating the Data

Upon successful completion of the flow, we can validate the data in Snowflake.

validating the data

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.

Output step to snowflake

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.