Design efficient Tableau Workbook with Snowflake Data Sources
Blog | September 15, 2022 | By Gajender Kumar
Most of us while developing Tableau dashboards using Snowflake follow common practices and are still unable to have optimal performance in Tableau using snowflake.
Here are some key considerations which we need to keep in mind while developing Tableau Workbooks with Snowflake data sources.
Choose the right driver while creating a data source.
This is especially important that we use a suitable connector when we connect any data source with Tableau. Tableau provides various native drivers for different data sources. We should always use native drivers instead of ODBC drivers.
For Snowflake, we have a native driver.
Connection Type, Live Connection is recommended
- Live Connection
- Extract Connection
In some conditions, we should be using extract connection as explained below
- When a user requires offline data, then we can use an extract connection.
- When the user needs to join Snowflake data with other data sources which are slow.
- When the user needs to be an aggregate data instead of a large and raw data set.
Note: To create or modify an extract connection, you need a live connection.
Relationships are recommended versus joins
Tableau recommends Relationships as the first approach to combining data because it is an easier way to prepare data for analysis instead of joining. We should use joins when we absolutely need to.
Advantages to using Relationship
- Data sources are easier to define, change and reuse.
- It is easier to use data sources with multiple tables and the correct level of details.
- We do not require to create any complex calculated field for LOD.
- It only queries for those files which are used in the viz.
Custom SQL: –
Tableau can create efficient queries when we define the relationship between the tables and let the query engine write SQL specific for the viz. Sometimes when we have a table with n number of columns then custom SQL is useful, but it can reduce the performance.
Custom SQL also prevents Tableau from using Join Culling. Often, for things like quick filters, Tableau will only be grabbing information from one table.
When Join Culling is active, Tableau will only send a query to the necessary table(s) instead of all tables in the connection. Since Tableau cannot use Join Culling with custom SQL, every table gets hit on every query.
So, Tableau always recommends using Tables and views instead of custom SQL.
Initial SQL: –
If for some reason, we must use custom SQL, we can avoid the repeated run by using initial SQL to create a temporary table on the fly. As Initial SQL is executed only once when we open the workbook, Initial SQL will increase the performance of the workbook, but it has static data for the session existing even if the data has been changed in the database.
Note: If you plan to publish your workbook to share with others, be sure that the administrator has not restricted initial SQL from running.
Views are alternative to custom SQL; unlike initial SQL the view are always having current data. It takes a longer run as it does not have a sufficient query plan and executes every time for up-to-date data.
Materialized View: –
Sometimes when view performance against a single table is less than expected, then we should use Materialized view. MVs are automatically and transparently maintained by Snowflake. A background service updates the MVs when data changes in the base table. Data in MVs are always up-to-date data.
Once data part will be finalize let talk about Visual part.
Fill up the most viewed spot.
Most of the viewers scan web content starting top from left to the right corner of a web page. So, we should keep the main visual of your dashboard at top left corner.
Limit the number of views
It is an innovative idea to limit the number of views included in the dashboard to three or four. If we add many views to the dashboard, it is possible to lose visual clarity and details. If you find more scope in your story, you can create more dashboard.
Add interactivity to encourage exploration
Show Filters: Filter will help user to show user specific data in the view.
Enable Highlights: You can use the Highlight button on the toolbar to set up highlighting between views. When highlighting is turned on, a selection in one view will highlight related data in the other views. You can turn on highlighting for all fields or select specific fields.
To display a highlighter:
- Go to the worksheet where the view is (or select Go to Sheet from the dashboard).
- Right-click the field you want to highlight and choose Show Highlighter:
In the highlighter, your users will be able to select or enter terms to highlight data in the view:
Tableau and Snowflake’s platforms combine to enable you to analyse massive amounts of data.
Using best practices for connecting, caching, and performance monitoring enable faster queries and taking advantage of features like Snowflake Time Travel and Secure Data Sharing empowers you to become even more data-driven.
The Next article of this series will be release soon “Best way to Deal with Snowflake Semi-Structured Data”