skip to content

In Tableau, what is the difference between a live connection and an extract?

level: juniorimportance: must knowfreq 84%

answer

  1. freshness against speed, pick one
  2. where does the query actually run?
  3. one mode keeps a local copy
  4. the local copy is a .hyper file
  5. refreshed on a schedule, not continuously

basics

~20 s

A live connection sends generated queries to the source database every time a view renders, so it always shows current data. An extract is a snapshot of the data stored in Tableau's own columnar Hyper file, refreshed on a schedule.

solid answer

~50 s

With a **live connection**, every worksheet action makes Tableau generate SQL and send it to the source system; what you see is whatever the source holds at that moment, and every user's interaction costs the source a query. With an **extract**, Tableau runs the query once, materialises the rows into a `.hyper` file, and afterwards queries that file instead of the source. The extract is columnar and compressed, so it is usually much faster than a slow or heavily contended source, and it takes the interactive load off the warehouse — but it is only as fresh as its last refresh, and someone has to schedule that refresh. Extracts also unlock offline use and some features the source may not support. The choice is basically freshness and source-side governance versus speed, isolation and cost control.

code

text · 8 lines
text
LIVE
  worksheet -> Tableau generates SQL -> source executes -> results
  freshness: current      speed: source-dependent   load: on the source

EXTRACT (.hyper)
  scheduled refresh -> source executes once -> rows written to .hyper
  worksheet -> Tableau queries the .hyper -> results
  freshness: last refresh  speed: Hyper columnar    load: on Tableau

go deeper

for a junior

Be ready to state plainly that live queries the source on every interaction while an extract queries a stored snapshot file, and to say which one shows current data.

for a middle

Explain the mechanics: Hyper's columnar file, what extract filters and aggregation do to the stored rows, and why refresh cadence — not the connection mode — determines freshness.

for a senior

Show judgment about audience size, warehouse cost per query, refresh windows and failure handling, and be able to defend a mixed estate where some sources are live and some extracted.

for a principal

Own the policy: which data sources across the deployment are allowed to be live, who pays for refresh capacity, and how freshness expectations are published to consumers so nobody discovers staleness in a meeting.

## The two connection modes Every Tableau data source is either **live** or **extracted**, and the choice decides where the query runs. A **live connection** is a pointer to the source system — a warehouse, a relational database, a cloud service. Nothing is copied. When you drag a field onto a shelf, Tableau translates the visual specification into a query in that source's dialect, sends it over the connector, and renders the returned aggregate. Change a filter and Tableau sends another query. The numbers on screen are the numbers the source can answer for right now. An **extract** is a materialised snapshot. Tableau runs the extraction query once, writes the resulting rows into a `.hyper` file (the format used since Tableau 10.5, replacing the older `.tde`), and from then on the workbook's queries go to that file, executed by Tableau's Hyper engine. The file lives beside the workbook on Desktop, or on Tableau Server / Tableau Cloud when the data source is published there. ## What an extract buys you **Speed on slow sources.** Hyper is a columnar, compressed, in-memory-friendly engine. A CSV, a spreadsheet, an application API connector or an over-subscribed transactional database will almost always answer more slowly than a `.hyper` file will. That is the single most common reason teams extract. **Isolation and cost.** Under a live connection, one user dragging a filter around a dashboard is one query against the warehouse; a hundred users are a hundred queries, at whatever your warehouse bills per query or per second. An extract turns that into one scheduled query per refresh, no matter how many people browse the dashboard. **Shaping at extraction time.** When creating an extract you can apply extract filters (rows that fail them are never written into the file), aggregate the data for visible dimensions (which stores rows pre-aggregated to the fields actually used, changing the stored grain), roll dates up to a coarser level, and take a sample of rows. Each of these makes the file smaller and faster. **Portability.** A workbook with an embedded extract opens without any connectivity to the source — useful on a plane, and useful when the source is behind a network the viewer cannot reach. ## What an extract costs you **Staleness.** The extract shows the world as of the last refresh, full stop. Nothing about an extract is continuous; there is no change stream from the source. **A refresh to operate.** Someone must schedule and monitor refreshes on Tableau Server or Cloud, and that schedule competes for resources with every other refresh on the site. Refresh failures become an operational surface you did not have with live. **Storage and refresh window.** A very large fact table may not be a sensible extract at all, and a full refresh of it may not fit in the nightly window. Incremental refresh helps, but it appends only rows whose key value exceeds the highest already stored — it does not see updates to existing rows or deletions. **Loss of source-side governance.** Row-level rules enforced by the database on a live connection do not automatically travel into an extract; the extract holds whatever rows the extracting credentials could read. ## Choosing Go **live** when freshness genuinely matters (operational monitoring), when the source is a fast analytical warehouse that you are happy to let carry the interactive load, or when security must be enforced by the database on every query. Go **extract** when the source is slow, expensive per query, rate-limited, or unavailable to viewers; when the dashboard's audience is large relative to the data's volatility; or when you need the workbook to work offline. ## Common traps "Extracts are always faster" is false: against a well-tuned columnar warehouse with a large dataset, a live connection can beat a bloated extract, and an extract that no longer fits comfortably in memory on the node running it is not fast at all. "Live means real-time" is also false: live means *queried on demand*, and if your ETL loads the warehouse hourly, a live connection is hourly-fresh. Finally, a dashboard on a live connection is not free — its cost simply appears on the warehouse bill rather than in a refresh schedule.

  • If an extract is only as fresh as its last refresh, why do teams still extract from a fast cloud warehouse?
    Cost and concurrency. A live dashboard sends a query per interaction per user, and cloud warehouses bill per second or per byte scanned. An extract collapses that into one scheduled query, isolates BI traffic from ETL and other workloads, and gives predictable response times even when the warehouse is busy.
  • What does the option to aggregate an extract for visible dimensions change?
    It changes the stored grain. Instead of writing source rows, Tableau writes rows already aggregated to the dimensions used in the workbook, which shrinks the file dramatically. The cost is that you can no longer drill to fields you excluded, and row-level calculations that need the original grain will be wrong.
  • Can a single workbook mix live and extracted data sources?
    Yes. Connection mode is a property of each data source, not of the workbook, so one dashboard can pair a live warehouse connection with an extracted spreadsheet. Be explicit about it in the documentation, because the two panes on that dashboard then have different freshness guarantees.

A live connection is asking the kitchen to cook each dish to order; an extract is a buffet laid out at 2am — instant to serve, but only as fresh as the moment it was prepared.

saying these in an interview costs you the question

  • Says extracts are always faster than live connections
  • Thinks an extract updates itself when the source changes
  • Believes a live connection means real-time streaming data
  • Assumes an extract can only hold aggregated data
  • Ignores the query load a live dashboard puts on the source

context

resolving links…