Live vs extract connections in Tableau - Trade-offs & Expert Analysis

Overview - Live vs extract connections

What is it?

Live and extract connections are two ways Tableau connects to data sources. A live connection fetches data directly from the source every time you interact with the dashboard. An extract connection copies data into Tableau's own format, allowing faster access without querying the original source each time. Both methods let you analyze data but differ in speed, freshness, and resource use.

Why it matters

Choosing between live and extract connections affects how fast your reports load and how up-to-date your data is. Without understanding this, you might have slow dashboards or outdated information, leading to poor decisions. Knowing when to use each helps balance performance and accuracy, making your data work better for you.

Where it fits

Before this, you should understand basic Tableau data connections and data sources. After learning this, you can explore data refresh strategies, performance optimization, and advanced data blending techniques.

Mental Model

Core Idea

Live connections fetch fresh data directly from the source every time, while extract connections use a snapshot of data stored locally for faster access.

Think of it like...

It's like ordering food at a restaurant: a live connection is like ordering fresh from the kitchen every time you want a meal, while an extract connection is like having a packed lunch ready to eat anytime without waiting.

┌───────────────┐       ┌───────────────┐
│   Data Source │──────▶│ Live Connection│
└───────────────┘       └───────────────┘
                             │
                             ▼
                      ┌───────────────┐
                      │ Tableau View  │
                      └───────────────┘


┌───────────────┐       ┌───────────────┐       ┌───────────────┐
│   Data Source │──────▶│ Extract       │──────▶│ Tableau View  │
└───────────────┘       │ (Snapshot)    │       └───────────────┘
                        └───────────────┘

Build-Up - 7 Steps

1

Foundation

Understanding Tableau Data Connections

Concept: Introduces what data connections are in Tableau and their role.

In Tableau, a data connection links your workbook to the data you want to analyze. It can be live or an extract. This connection lets Tableau read data to create charts and dashboards.

Result

You know that Tableau needs a connection to get data before you can visualize it.

Understanding that Tableau depends on data connections is the base for learning how data flows into your reports.

2

Foundation

What is a Live Connection?

Concept: Explains live connections and how they work.

A live connection means Tableau asks the original data source for data every time you interact with your dashboard. This keeps data always current but can be slower if the source is large or slow.

Result

You understand that live connections provide real-time data but may impact performance.

Knowing live connections fetch fresh data helps you see why dashboards might slow down with complex queries.

3

Intermediate

What is an Extract Connection?

Concept: Introduces extracts as snapshots of data stored locally.

An extract connection copies data from the source into a Tableau file. Tableau reads from this file instead of the original source. This makes dashboards faster but data is only as fresh as the last extract refresh.

Result

You grasp that extracts improve speed by using stored data but may not be up-to-date.

Understanding extracts helps you balance speed and data freshness in your reports.

4

Intermediate

Comparing Performance and Freshness

🤔Before reading on: Do you think live connections are always faster than extracts? Commit to your answer.

Concept: Compares speed and data freshness trade-offs between live and extract connections.

Live connections always show the latest data but can be slow if the source is large or network is slow. Extracts load data quickly because it's stored locally but need manual or scheduled refreshes to update data.

Result

You see that extracts often perform better but may show outdated data, while live connections are slower but always current.

Knowing this trade-off helps you choose the right connection type based on your needs.

5

Intermediate

When to Use Live vs Extract Connections

🤔Before reading on: Would you use a live connection for a dashboard updated once a month? Commit to your answer.

Concept: Guides decision-making on when to pick live or extract connections.

Use live connections when data changes frequently and you need real-time info. Use extracts when data is large, slow to query, or updates less often. Extracts also help when you want to work offline or reduce load on the source system.

Result

You can decide connection types based on data update frequency, size, and performance needs.

Understanding use cases prevents poor choices that cause slow dashboards or stale data.

6

Advanced

Managing Extract Refreshes and Incremental Loads

🤔Before reading on: Do you think extracts always reload all data every time? Commit to your answer.

Concept: Explains how extracts can be refreshed fully or incrementally to update data efficiently.

Extracts can be refreshed fully, replacing all data, or incrementally, adding only new or changed data. Incremental refresh saves time and resources for large datasets. You schedule refreshes to keep extracts up-to-date without manual work.

Result

You learn how to keep extracts fresh efficiently and avoid long refresh times.

Knowing extract refresh options helps maintain data freshness without sacrificing performance.

7

Expert

Hybrid Approaches and Performance Optimization

🤔Before reading on: Can you combine live and extract connections in one Tableau workbook? Commit to your answer.

Concept: Shows advanced use of mixing live and extract connections and optimizing performance.

Tableau allows combining live and extract connections in one workbook, using extracts for heavy data and live for small, fast-changing data. You can also use data engine features like aggregation and filters to speed live queries. Understanding query load and caching helps optimize dashboards.

Result

You can build fast, up-to-date dashboards by mixing connection types and tuning performance.

Mastering hybrid connections and optimization techniques is key for professional-grade Tableau solutions.

Under the Hood

Live connections send queries directly to the original database or data source each time Tableau needs data. The source processes the query and returns results in real-time. Extract connections create a compressed snapshot of data stored in Tableau's proprietary format (.hyper). Tableau reads from this local file, avoiding repeated queries to the source. Extracts can be refreshed on demand or scheduled to sync with source data.

Why designed this way?

Tableau designed live connections to provide real-time data access for up-to-date analysis. Extracts were created to improve performance and enable offline work by reducing dependency on source systems. This dual approach balances freshness and speed, addressing diverse user needs and data environments.

┌───────────────┐       ┌───────────────┐       ┌───────────────┐
│ Data Source   │──────▶│ Live Query    │──────▶│ Tableau View  │
└───────────────┘       └───────────────┘       └───────────────┘


┌───────────────┐       ┌───────────────┐       ┌───────────────┐
│ Data Source   │──────▶│ Extract Build │──────▶│ Extract File  │
└───────────────┘       └───────────────┘       └───────────────┘
                                                      │
                                                      ▼
                                              ┌───────────────┐
                                              │ Tableau View  │
                                              └───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Do you think extracts always have the freshest data? Commit to yes or no.

Common Belief: Extracts always show the most current data because Tableau updates them automatically.

Reality: Extracts only update when you refresh them manually or via schedule, so data can be outdated between refreshes.

Why it matters: Relying on extracts without timely refreshes can lead to decisions based on stale data.

Quick: Do you think live connections are always slower than extracts? Commit to yes or no.

Common Belief: Live connections are always slower because they query the source every time.

Reality: Live connections can be fast if the source is optimized and network is good; extracts may be slower if very large or complex.

Why it matters: Assuming live is always slow may cause unnecessary extract use, missing real-time insights.

Quick: Can you combine live and extract connections in one Tableau workbook? Commit to yes or no.

Common Belief: You must choose either live or extract for the entire workbook, not both.

Reality: Tableau supports mixing live and extract connections in the same workbook for flexibility.

Why it matters: Not knowing this limits your design options and performance tuning.

Quick: Do you think extracts always reload all data on refresh? Commit to yes or no.

Common Belief: Extract refreshes always reload the entire dataset from scratch.

Reality: Extracts can be refreshed incrementally, adding only new or changed data to save time.

Why it matters: Ignoring incremental refresh wastes time and resources on large datasets.

Expert Zone

1

Extracts can include filters and aggregations at extract time, reducing data size and improving performance beyond just caching.

2

Live connections benefit greatly from database indexing and query optimization, which can sometimes outperform extracts for certain queries.

3

Tableau's data engine (.hyper) uses columnar storage and compression, making extracts highly efficient for analytical queries.

When NOT to use

Avoid extracts when you need real-time data updates or when data changes too frequently for scheduled refreshes to keep up. Avoid live connections when the source is slow, unreliable, or when querying it heavily would impact other users. In such cases, consider hybrid approaches or data warehouses optimized for analytics.

Production Patterns

In production, teams often use extracts for large, stable datasets refreshed nightly, combined with live connections for small, fast-changing reference data. Incremental extract refreshes are scheduled during off-hours. Performance monitoring tools help identify slow live queries to optimize or convert to extracts.

Connections

Data Warehousing

Builds-on

Understanding live vs extract connections helps grasp how data warehouses serve as optimized sources for live queries or extract creation.

Caching in Web Browsers

Same pattern

Like extracts, browser caches store snapshots of web content to speed up loading, trading freshness for performance.

Supply Chain Inventory Management

Analogous process

Managing live vs extract data is like deciding between just-in-time inventory (live) and stocked inventory (extract), balancing freshness and availability.

Common Pitfalls

#1 Using live connection for very large datasets without optimization.

Wrong approach:

Connecting live to a huge transactional database without filters or aggregations, causing slow dashboard loads.

Correct approach:

Use extracts with filters or aggregations for large datasets, or optimize the source database before live connection.

Root cause:

Misunderstanding that live connections depend heavily on source performance and network speed.

#2 Not scheduling extract refreshes, leading to stale data.

Wrong approach:

Creating an extract and never refreshing it, expecting data to update automatically.

Correct approach:

Set up scheduled extract refreshes or refresh manually to keep data current.

Root cause:

Assuming extracts update themselves without explicit refresh.

#3 Mixing live and extract connections without understanding impact.

Wrong approach:

Randomly combining live and extract sources without planning, causing inconsistent data freshness and performance issues.

Correct approach:

Plan which data needs real-time access and which can use extracts, then design connections accordingly.

Root cause:

Lack of strategy for connection types in complex workbooks.

Key Takeaways

Live connections provide real-time data by querying the source every time but can be slower depending on source performance.

Extract connections store snapshots of data locally in Tableau's format, improving speed but requiring refreshes to stay current.

Choosing between live and extract depends on data freshness needs, dataset size, and performance considerations.

Extracts can be refreshed fully or incrementally to balance update speed and resource use.

Advanced Tableau users combine live and extract connections strategically to optimize dashboard speed and data accuracy.