5.3 Tableau File Types, Extracts & Data Refresh Workflows

5.3 Tableau File Types, Extracts & Data Refresh Workflows

Managing Tableau files, choosing between live connections and data extracts, and configuring data refresh workflows are fundamental skills tested on the Tableau Desktop Specialist exam. Tableau utilizes several distinct file extensions, each serving a specific role in storing workbook structure, data connection metadata, or physical data rows. Mastering these file formats and data architecture patterns ensures efficient performance and seamless collaboration across enterprise environments.

---

Overview of Tableau File Extensions

Tableau employs structured XML files and packaged zip archives to manage workbooks, data connections, and individual worksheets.

---

Detailed Analysis of Essential File Formats

1. Tableau Workbook (.twb)

A .twb file is a pure XML document. It contains all instructions required to render your dashboards: worksheet layouts, dashboard layouts, story points, custom calculated field definitions, parameter configurations, color palettes, and data source connection strings. Crucially, a .twb file contains zero data rows. If you email a .twb file connected to a local Excel spreadsheet to a colleague, they will receive an error when opening it because the external data file is missing.

2. Tableau Packaged Workbook (.twbx)

A .twbx file is a compressed Zip archive. It packages the main .twb XML file together with all required local external assets. These assets include local data files (such as .hyper extracts, .xlsx spreadsheets, .csv text files, or Access databases), background images, and custom shape files. When sharing work for review with stakeholders who cannot connect to your local network or database, saving as a .twbx guarantees they can view the fully populated workbook.

3. Tableau Data Source (.tds)

A .tds file is a lightweight XML metadata file. It does not contain data rows. Instead, it stores all data connection details (server name, port, database name, authentication mode) alongside all customized metadata properties:

• Default field renames and aliases.
• Default aggregations (e.g., setting Discount to default to AVG instead of SUM).
• Default number formatting (e.g., currency formatting for Sales).
• Pre-built calculated fields, sets, groups, and hierarchies.

Publishing or sharing a .tds file allows data stewards to distribute curated data configurations so that all analysts across an organization maintain uniform metric definitions.

4. Tableau Packaged Data Source (.tdsx)

A .tdsx file is a compressed Zip archive containing both the .tds metadata file and any associated local data files, such as a .hyper extract file or local Excel workbook. It provides a self-contained data source package for team distribution.

---

Live Connections vs. Tableau Data Extracts

When connecting to data in Tableau, developers must choose between a Live Connection and a Tableau Data Extract (.hyper).

+-----------------------------------------------------------------------+
|                         LIVE CONNECTION                               |
| Tableau Pass-Through Queries ---> Source Database (SQL Server/Snowflake)|
| Real-Time Updates | Leverages DB Performance | Requires Network Access|
+-----------------------------------------------------------------------+
|                         TABLEAU DATA EXTRACT                          |
| Data Snapshot ---> Tableau Hyper Columnar Engine (.hyper)             |
| In-Memory/Disk Performance | Offline Access | Scheduled Refresh Needed  |
+-----------------------------------------------------------------------+

Live Connections

• Mechanism: Tableau translates visual drag-and-drop actions into native SQL (or database-specific) queries sent directly to the data source in real time.
• Advantages: Reflects real-time data updates instantly; leverages the processing power and security architecture of high-performance cloud data warehouses (e.g., Snowflake, BigQuery).
• Disadvantages: Performance is strictly dependent on network speed and database responsiveness; high user concurrency can strain database infrastructure.

Data Extracts (.hyper)

• Mechanism: Tableau creates a high-performance, compressed, columnar snapshot of data saved locally or on Tableau Server using the Hyper engine.
• Advantages: Delivers exceptional query execution speed; enables offline access; reduces query load on production databases; supports advanced analytical functions not supported by legacy databases.
• Disadvantages: Represents a point-in-time snapshot that requires periodic scheduled refreshes to reflect new database rows.

---

Extract Optimization & Data Refresh Workflows

Extract Optimization Techniques

To maximize extract performance and minimize .hyper file size, developers can configure extract settings before creation:

1. Hide All Unused Fields: Strips unused columns from the extract file, reducing disk footprint.
2. Filter Data: Applies Extract Filters to exclude irrelevant historical rows or geographic regions.
3. Aggregate Data for Visible Dimensions: Aggregates measures to the specified date level (e.g., rolling up daily transactions into monthly totals) to compress millions of rows into summary rows.

Full Refresh vs. Incremental Refresh

Updating a data extract can be performed through two distinct refresh mechanisms:

1. Full Refresh: Tableau completely wipes the existing .hyper extract file and executes a fresh query against the underlying data source to rebuild the extract from scratch. Full refreshes guarantee 100% data consistency but can be time-consuming for large datasets.
2. Incremental Refresh: Tableau queries the data source for newly added rows and appends them to the existing .hyper extract file without rebuilding historical rows.

Key Requirement for Incremental Refresh

To configure an Incremental Refresh in Tableau Desktop, you must specify a single key column (typically an auto-incrementing integer like Row ID, or a sequential timestamp like Transaction Date or Created At). During an incremental refresh, Tableau inspects the maximum value in that key column within the existing extract and queries the source database only for rows with key column values greater than that recorded maximum.

---

Summary Matrix: Live Connection vs. Extract Decision Guide