Apache Superset virtual datasets and metrics guide

Apache Superset virtual datasets and metrics: what they do and when teams need them

Analysts often expect to build a chart from orders joined to customers, products and regions, and then find that Explore will not do it. In Explore, a Superset datasource can only be a single table or view. Joins therefore have to be prepared ahead of time, either as a physical table built by the data team or as a virtual dataset defined by a SQL query. SQL Lab has no such limitation, which is why analysts can join freely there but still need a modeled dataset before a dashboard can be shared with a wider audience. Before rollout, decide which joins are business-ready and who owns them, because every dashboard built on a dataset inherits its definition.

A *physical dataset* points at an existing table or view in a connected database and exposes its columns as they are. A *virtual dataset* stores a SQL query in Superset and presents its result as if it were a table. Both can carry *calculated columns*, which are row-level expressions added in Superset, and *saved metrics*, which are named aggregations such as a revenue total or a distinct customer count that users can select in any chart built on that dataset. Teams usually model metrics in the dataset rather than in each chart so that the same definition appears everywhere. Confirm in your Superset version how dataset edit permissions are granted, since changing a virtual dataset's SQL changes every chart that depends on it.

The FAQ advises joining a large main table only to small tables inside the view, and avoiding GROUP BY in the view, because Superset applies its own GROUP BY on top of the query. Violating either rule can push heavy work onto the warehouse for every chart load. Before building dashboards, check the row counts and join keys with the data owner, test the view's query plan on the warehouse, and decide where aggregation belongs. Teams that need many joins, or joins between large tables, usually move that work into a maintained table in the warehouse, which keeps Superset's query simple and gives the data team control over refresh timing.

Which join strategy fits your data model

A data team builds the joined result as a physical table in the warehouse, usually on a schedule through the team's existing pipeline tooling. Superset then treats that table as a single dataset, so every chart reads one table and the join is computed once per refresh rather than once per chart load. Superset's Explore view can only query one table or view at a time, so this approach is the one that handles wide joins of large tables without relying on Superset to do them.

Ownership: the data engineering team owns the pipeline, the schema and the join logic, and Superset owners only manage the dataset definition.
Refresh behavior: data is as current as the last pipeline run, so the refresh schedule is a business decision that users need to know about.
Governance: changes go through the pipeline's review process, which keeps the logic out of the BI layer.
Query cost: the expense lands on the pipeline run and on storage, while dashboard queries stay simple and predictable.

Before committing, confirm with your data team how long a full rebuild takes and whether incremental loads are supported for your largest tables.

Physical, virtual and metric options compared

This table compares physical datasets, virtual SQL datasets, saved metrics and calculated columns across where the logic lives, who can change it, how it affects warehouse load and when each option fits. Read it as a sequence: decide the table or view first, then decide which logic belongs in the dataset and which belongs in the chart.

Option	Where the logic lives	Who can edit it and load effect	When it fits
Physical dataset	A single existing table or view in the connected database	Edited in Superset dataset settings; confirm the edit permissions in your Superset version. Queries run against the table directly	Analysts need a clean, governed table with no reshaping
Virtual SQL dataset	A saved SQL query in Superset that Explore treats as one table	Editable by users granted dataset-editing rights; confirm the role in your version. Each chart runs the saved SQL, so heavy SQL adds warehouse load	Joins, filters or reshaping that a prepared view cannot yet provide
Saved metric	An aggregate expression stored on the dataset and reused by charts	Defined by dataset editors; charts add the aggregation on top. Load depends on the underlying dataset and the chart's grouping	Business definitions, such as revenue or active users, that must match across charts
Calculated column	A row-level expression added to the dataset	Defined by dataset editors; evaluated by the database for each query. Expensive expressions slow every chart that uses the dataset	Simple derived fields used for grouping or filtering

How views and GROUP BY change warehouse load

A virtual dataset is a saved query that Superset runs for every chart built on it, so its SQL shape decides how much work the warehouse does each time someone opens a dashboard. The cards below cover the costs that most often surprise teams, and each one names the check to run in staging before users arrive.

GROUP BY inside the view

Superset applies its own GROUP BY on top of a virtual dataset, so an aggregation inside the view can force the warehouse to aggregate twice. The double aggregation adds work on every chart query, and the result shape may not match what the chart expects. Test a view with and without its GROUP BY against the same chart, and compare the generated SQL and query time in the database logs.

Joins repeated on every query

A join inside a virtual dataset runs again for each chart, filter change and dashboard refresh that uses it, because the view is not materialized by Superset. A join that looks cheap for one chart can dominate load when a dashboard holds many charts. Ask the data team whether the view can be replaced by a prepared table that a scheduled pipeline refreshes, and measure the difference on your busiest dashboard.

Large tables joined to large tables

Joining a large fact table to another large table multiplies the rows the engine must scan and shuffle before any filter or aggregation applies. The FAQ advises joining the main large table only to small lookup tables inside views. Check row counts for each side of every join, and confirm that the warehouse can push filters down into both tables before the join.

Cache behavior follows query shape

Superset caches results per query, so two charts that differ only in a filter or a column list may not share cached results. A view that embeds user-specific or time-dependent logic changes the query text and can disable reuse across users. Test cache hit behavior for the same dashboard opened by two users, and confirm that the caching layer is configured as expected for your deployment.

Saved metrics versus calculated columns

A saved metric is aggregated by the warehouse for each chart, while a calculated column is evaluated row by row before aggregation. Putting heavy expressions in calculated columns on a large dataset can slow every query that touches them. Review each expression, move aggregations into saved metrics where the logic allows, and test chart load times on the full data volume.

How to model datasets and metrics before rollout

This sequence turns the questions your users need answered into tested datasets, saved metrics and calculated columns that return the same numbers as the source database. Each step ends with a check, so problems in the data model surface before dashboards reach wider audiences.

Inventory the questions users need answered
Collect the business questions from the teams who will use Superset, such as which orders were placed per region last month or how many active accounts each product line has. For each question, record the grain (one row per order, per day, per account), the measures, and the filters users expect. Owners of the source data should review the list. The step is complete when every planned chart maps to a written question with a named owner and a stated grain.
Decide which joins belong in a prepared table
Explore charts query a single table or view, so every question that spans several tables needs a join decision made in advance. Keep a large main table joined only to small lookup tables, and place those joins in a view or a prepared table owned by the data team. Avoid GROUP BY inside the view, because Superset applies its own GROUP BY on top of the dataset. The step is complete when each join is written down with its owner, its cardinality, and the reason it sits in a view rather than in a chart.
Create physical and virtual datasets in staging
In a staging Superset, register the physical tables that users will query and create virtual datasets from SQL for the joined or filtered views decided in the previous step. Use a dedicated database connection with read-only privileges for the staging environment. Give each dataset a name that describes its grain and its source. The step is complete when every dataset loads its preview rows and the column types match what the source database reports.
Define saved metrics and calculated columns
Add saved metrics for aggregations users will repeat, such as order count, revenue, or distinct customers, and calculated columns for row-level logic such as fiscal period or status grouping. Agree on naming conventions with the users and the data owners before anyone builds a chart, and document the SQL expression behind each name. Confirm that the metric definitions do not depend on which chart or dashboard uses them. The step is complete when each metric has an agreed name, a written definition, and an owner.
Build the core charts on each dataset
Create one chart per planned question on the staging datasets, using the saved metrics rather than ad hoc expressions wherever possible. Keep filters at the dataset level when they apply to every chart, and avoid filters that exist only to hide a modeling error. Record the query each chart sends, which you can view in the chart's query output. The step is complete when every planned question has a chart, and every chart uses the agreed metric names.
Reconcile chart totals with direct SQL
For each chart, run an equivalent query directly against the source database, using the same filters, date range and grain, and compare the result with what Superset displays. Start with a small date range, then check a full period, and investigate any difference in row counts, nulls, duplicates or time zones before moving on. Confirm that totals agree after the join. The step is complete when each chart matches its direct SQL result, and each known difference has a documented explanation.
Check performance and record the model
Time the slowest charts and review the SQL they generate, paying attention to views that scan large tables or repeat expensive joins. Ask the data owners to confirm whether the source database has indexes or partitions that match the filters users apply. Publish the final model as a short reference listing datasets, metrics, joins and their owners, and schedule a review before the datasets move to production. The step is complete when performance is acceptable to the data owners and the reference is approved.

Who may edit datasets and metrics once models go live

Once a virtual dataset feeds shared dashboards, its SQL and saved metrics are production logic, and whoever can edit them can change the numbers every viewer sees. Decide before go-live who owns each virtual dataset, who may change its SQL and metrics, and who approves those changes. Superset's dataset editing permissions are granted through roles in its access model, so the owner list should be mapped to specific roles rather than left to whoever built the dataset. Confirm in your Superset version which built-in roles can edit datasets and which can only read them, since the default role set has changed across releases and your custom roles may inherit more than you expect.

A virtual dataset is a saved SQL query, so an edit to its SQL changes every chart and dashboard built on it at once. A single altered filter, join or column expression can shift totals across many dashboards without any visible error. Where dataset SQL uses Jinja templating, the risk grows: templated SQL is rendered on the server, so whoever can edit that SQL controls code that runs with the database connection's privileges. Treat templated datasets as a separate, higher-trust category, restrict editing to a small group, and make the templating decision before opening dataset editing to analysts. Test whether analysts can reach the SQL editor for datasets at all, because some access paths allow editing without an explicit dataset-management grant.

For shared metrics, use a change process that matches the cost of a wrong number. A reasonable starting pattern is a named owner per metric, a written definition in the dataset description, a change request that states the expected impact on dashboards, a test run against a staging copy of the database, and a reviewer who is not the author. Record each change in your audit trail and keep the previous SQL so you can roll back quickly. Ask your Superset administrators to confirm how to export dataset definitions for review and whether your deployment can detect edits made outside this process.

Security and operational considerations for shared datasets

A virtual dataset is only as safe as the database account behind it, the views it reads, and the metadata that describes it. The cards below cover the checks that keep shared datasets correct and recoverable as source schemas, Superset versions and access rules change.

Database account privileges

Dataset SQL runs with the database account Superset is configured to use, not with the identity of the person viewing a chart. Give that account read access only to the schemas and tables the datasets need, and deny write and DDL rights. Review who can create or edit virtual datasets, because anyone who can write dataset SQL can query whatever that account can reach.

Testing row level security on views

Row level security filters apply to the dataset, but a virtual dataset built on a view or a complex query can change which columns and rows the filter sees. Test each shared dataset as users in every group that should see a different slice of data. Confirm that filter columns survive the view definition and that no path returns rows outside the intended scope.

Silent breakage from schema changes

When a source table drops or renames a column, a virtual dataset often fails only when someone opens a chart that uses it, and metrics built on that column can stop working without a clear error. Keep a list of which datasets and saved metrics read each source table, and run a test query against every shared dataset after a schema migration in the source database.

Back up the metadata database

Virtual dataset definitions, saved metrics, calculated columns and chart configurations are stored in the metadata database, not in the source warehouse. Take a tested backup before major changes such as upgrades, dataset refactors or bulk edits. Without it, a bad change to shared definitions can be hard to reverse, and the warehouse data itself will not restore those definitions.

Test upgrades in staging

Superset upgrades can change behavior that dataset definitions and metrics depend on. The upgrade guide asks you to back up the metadata database, run the database upgrade and initialization commands, and test in staging first. Check UPDATING.md for breaking changes between your current and target versions, and re-run your dataset and metric checks in staging before promoting the release.

Questions teams ask about Superset datasets and metrics

Can Superset join tables in Explore?

Not directly. In Explore, a Superset datasource can only be a single table or view, so a chart cannot join two tables on its own. You have two ways around this: build a prepared table that already contains the joined data, or create a virtual dataset (a view defined in SQL) that performs the join. SQL Lab does not have this limitation, so analysts can write joins there, but charts built from SQL Lab queries are harder to govern and reuse than charts built on a curated dataset.

Is a SQL view a good place to put joins?

It can be, with clear limits. The Superset FAQ advises joining a large main table only to small tables inside a view, and avoiding GROUP BY in the view because Superset applies its own GROUP BY on top of the dataset query. Joins that fan out across large tables, or that rely on many-to-many keys, often produce slow charts and double-counted metrics. Ask your data team to measure the view's query time against the warehouse directly before users depend on it, and to confirm the join keys produce one row per expected entity.

What drives the cost of modeling datasets and metrics?

The main driver is how many business definitions must be agreed and encoded. Cost usually grows with the number of source systems, the number of joins and calculated columns, how many saved metrics must match finance or operations reports, and how often the definitions change. Each dataset also needs testing against known totals, documentation for users, and access rules, which adds effort. Ask for a quote that lists the datasets, the metrics per dataset, the source systems involved and the validation method, because those items set the scope.

What does the team need to provide before modeling starts?

You need to supply access to the analytics databases, a read-only service account for Superset, and named owners for the business definitions. Provide the current report or spreadsheet that people trust today, because it becomes the reference for reconciling metric totals. You should also share the schema documentation, known data quality issues, the join keys between tables and the access model that determines which users may see which rows. Missing owners for definitions is the most common cause of rework.

Can our own data team build these datasets and metrics?

Yes, if the team already knows SQL, the warehouse, and Superset's dataset editor. Superset has a built-in editor for physical datasets, virtual datasets, saved metrics and calculated columns, so the mechanics are not the hard part. The harder parts are agreeing definitions across departments, checking that joins do not multiply rows, and keeping views fast as data grows. A small internal team can handle this well when it has time and a validation routine. If the team lacks that capacity, a fixed-scope engagement can cover the modeling and handover in writing.

How long does dataset and metric modeling take?

Duration depends mainly on data complexity and how quickly business owners confirm definitions. A small set of tables with a single source system and agreed metrics is far simpler than many sources with conflicting definitions, slow views and several departments reviewing results. Ask for a timeline that is tied to the written scope, with milestones for each group of datasets and a reconciliation step against existing reports. We do not publish fixed durations, so the schedule should come from the scope you approve, not from a general estimate.

How do slow virtual datasets affect the warehouse?

Every chart on a virtual dataset runs the underlying SQL again, unless caching serves a stored result, so a heavy view multiplies load on the warehouse as dashboards refresh and users filter. Each filter change or native filter can send another query. Confirm with your database team which query timeouts and concurrency limits apply, and test the most-used charts under realistic filter combinations. Materializing a heavy join as a prepared table on a schedule is often the safer design for large data, and that decision should be recorded in the scope.

Official Superset documentation to read next

Before you design datasets, read the Superset FAQ entry on joins and views. It is the project's own guidance on the limit that matters most here: in Explore, a datasource must be a single table or view, so any join has to be prepared upstream. The same entry advises joining a large main table only to small tables inside a view, and keeping GROUP BY out of the view, because Superset applies its own GROUP BY on top of your query. Use the Apache Superset FAQ as the reference for your join strategy review, and confirm the wording still matches the release you plan to run.

Read the configuration and architecture documents next to understand where dataset definitions live and what else competes for the same resources. The Configuring Superset guide covers the metadata database, which stores dataset, metric and calculated column definitions, along with the supported PostgreSQL and MySQL versions for production. The Superset architecture document explains the caching layer and Celery workers that serve async queries and reports. Chart load time for a virtual dataset depends on those components as much as on the SQL itself.

Make two decisions after reading. First, who may create or edit virtual datasets, since their SQL runs against your warehouse on every chart render. Second, whether heavy joins belong in a warehouse view maintained by your data team, not in a Superset dataset that analysts edit. Confirm with your own data platform owners which views they will support over time, and record the owner for each one.

How DeployOpen scopes and delivers Superset data modeling

DeployOpen plans, builds, documents and supports Apache Superset data models inside your own infrastructure, including the dataset layer, saved metrics and calculated columns that your users will chart against. Engagements are fixed-scope, so the datasets, metrics and handover items are agreed in writing before build work starts.

How we scope

Inventory the analytics databases, tables and views that datasets will read, with owners for each source
Agree which datasets are physical, which are virtual SQL datasets, and which joins need a prepared view
Record the saved metrics and calculated columns each team needs, and where each definition should live
Confirm the access model that governs datasets, including which roles may edit datasets and metrics

How we build

Build virtual datasets with joins limited to a large main table and small lookup tables, and test query cost against the warehouse
Review every view for GROUP BY statements, since Superset applies its own grouping on top of the dataset query
Define saved metrics centrally and test each one against known source totals before charts depend on it
Test datasets against your row level security rules so filtered users see only the rows they should

How we hand over

Deliver a written dataset catalog listing each dataset's source, owner, refresh expectations and dependent charts
Document the metric definitions, their SQL and the naming conventions users should follow in Explore
Walk through the view and dataset change process with the team that will maintain datasets after go-live
Provide a runbook for updating a source table, renaming a column or retiring a dataset without breaking dashboards

How we support

Offer ongoing support and upgrade work as a retainer, covering dataset changes and Superset version upgrades
Review query performance after new charts or datasets are added, and tune views with your database team
Re-run metric validation after upgrades or source schema changes, and log the results for your records
Confirm with your team which changes need a written scope change before they are built

Apache Superset virtual datasets and metrics