Virtual Datasets

Federated SQL views across multiple physical datasets — spanning different storage systems

Physical vs. Virtual Datasets

The Workbench has two kinds of datasets. Physical datasets are defined by data found in database connections — tables and views in PostgreSQL, MySQL, MSSQL, or MS Access — or DuckDB tables created by file uploads (CSV, JSON, Excel). Virtual datasets are federated views across any combination of physical datasets. They are effectively SQL views that span multiple storage systems — a virtual dataset can join a PostgreSQL table with an uploaded CSV in DuckDB and a MySQL table, all in a single query.

How They Work

A virtual dataset is a stored SQL query that references one or more physical datasets. When you run a recipe that uses a virtual dataset, the SQL execution engine compiles the federated query and dispatches it across the relevant backends. The result is assembled from the contributing sources and presented as a single dataset. Because it is a view, the data is always current — it reflects the live state of every underlying physical dataset at query time.

Lazy Evaluation

The Workbench uses lazy evaluation — also called lazy loading — for virtual datasets. Computation and data loading are deferred until the results are actually needed. When a virtual dataset is used as input to a statistical analysis, the SQL view is evaluated at that moment, producing a pandas DataFrame for the statistical libraries. This view can optionally be materialised — the data is read and stored, creating a snapshot — or left unmaterialised, re-executing the federated query each time. Lazy evaluation avoids unnecessary work: if you never run the recipe, the federated query is never executed.

Pandas Conversion

Any dataset — physical or virtual — can be converted into the pandas DataFrame format used as input by the Workbench's statistical libraries. For a physical dataset this reads the data from its source. For a virtual dataset this triggers the federated query, assembling the result across all contributing physical datasets. The pandas representation is the lingua franca of the statistics pipeline — charts, tests, and models all consume DataFrames regardless of where the original data lives.

Virtual vs. Physical

Aspect	Physical Dataset	Virtual Dataset
Definition	A table or view in a single storage system, or an uploaded DuckDB table	A SQL view spanning one or more physical datasets
Storage	Data lives in the source database or DuckDB	No storage — defined by a query
Freshness	Always reflects the live source	Always reflects the live sources — re-evaluated on each use
Cross-backend	Single backend only	Can join across PostgreSQL, MySQL, MSSQL, Access, and DuckDB in one query
Materialisation	Always materialised (data exists somewhere)	Optionally materialised — can snapshot or remain live

Virtual datasets are federated views, not file shortcuts. They let you write a single SQL query that joins a production PostgreSQL table, an uploaded CSV in DuckDB, and a MySQL reference table — and present the result as one dataset. The SQL execution engine handles dispatch and assembly across backends transparently.

← File Import 📋 Contents Datasets →