[SIP-222] Data Extracts (Warm Cache Tier)
- Dominant language
- Python
- Stars
- 74.8k
- Forks
- 18.3k
- Avg merge
- 2d 5h
- Merged PRs (30d)
- 685
Description
### [SIP-222] Data Extracts (Warm Cache Tier)
#### Motivation
In Superset, dashboard filter modifications (e.g., dropdowns, date pickers) alter the SQL `WHERE` clause of underlying chart queries. This forces new queries to the target database and generates new Redis cache keys for every filter combination.
This behavior causes:
1. High database query concurrency during peak dashboard usage.
2. Cache key inflation with redundant dataset slices.
3. Rendering latency for large datasets on analytical databases (Trino, Snowflake, Presto).
We propose introducing a Data Extract mechanism, a warm, in-process columnar cache tier, to evaluate runtime dashboard filters without re-querying the source database.
#### Proposed Change
Implement an extract execution tier using embedded DuckDB to query Parquet datasets stored in S3, MinIO, or local disk.
1. Definition: Users configure an extract on a dataset or chart, defining base filters, optional aggregation/rollups, and refresh schedules (Celery Beat or external API trigger).
2. Extraction: Celery workers execute the base query against the source database, stream results to Parquet format, and upload the file to object storage.
3. Query Execution: When a dashboard filter is applied, the query engine intercepts the request and routes it to DuckDB using `read_parquet()`, evaluating filters locally instead of pushing queries upstream.
4. Management UI: A new management view under Data > Extracts tracks task state (Pending, Running, Success, Failed), execution logs, and allows cancelling long-running jobs.
5. Governance: Admins configure maximum extract size (e.g., 5GB), row limits, job execution timeouts, worker concurrency limits, and database-level feature toggles.
#### New or Changed Public Interfaces
- **Database Models**:
- `Extract`: A new SQLAlchemy model mapping to a dataset or chart. It will store parameters such as base filters, dimensions, schedule configuration, execution state (e.g., pending, running, success, failed), and metadata (e.g., file size, row count).
- **REST API**:
- `GET /api/v1/extract/`: Retrieve a paginated list of extracts.
- `POST /api/v1/extract/`: Create a new extract configuration.
- `PUT /api/v1/extract/{id}`: Update extract settings or schedule.
- `DELETE /api/v1/extract/{id}`: Remove an extract and delete its associated Parquet file.
- `POST /api/v1/extract/{id}/refresh`: Manually trigger an immediate execution of the extract.
- **UI Components**:
- **Extracts Management List**: A new view under the "Data" dropdown menu to monitor all extract jobs, view logs, and manage configurations.
- **Explore / SQL Lab**: A configuration modal accessible from the dataset editor or chart builder to define extract properties.
- **Dashboard Indicators**: Visual badges on charts indicating when the data is sourced from an extract, along with the timestamp of the last successful refresh.
- **Dashboard Controls**: A toggle to bypass the extract and force a live query for real-time data access.
- **Configuration Variables (`superset_config.py`)**:
- `EXTRACTS_ENABLED`: Feature flag to toggle the feature globally.
- `EXTRACTS_STORAGE_BACKEND`: Define the storage backend (e.g., S3, local filesystem) for Parquet files.
- `DUCKDB_MEMORY_LIMIT`: Control the maximum memory allocated to the embedded DuckDB engine.
- `EXTRACT_MAX_SIZE_MB`: Enforce a maximum file size for generated extracts.
- **RBAC Permissions (FAB)**:
- `can_create_extract`: Allows creating an extract for datasets the user has read access to.
- `can_read_extract`: Allows viewing extract configurations and consuming them.
- `can_edit_extract`: Allows modifying an owned extract.
- `can_delete_extract`: Allows deleting an owned extract.
- `can_manage_all_extracts`: Admin role granting full control over any extract.
#### New Dependencies
- `duckdb` (MIT): Embedded OLAP SQL engine for querying Parquet files.
#### Migration Plan and Compatibility
- Database Schema: Alembic migration to create `extracts` metadata tables and add FAB permissions.
- Backward Compatibility: Fully opt-in per dataset. Unconfigured datasets continue executing live queries.
- Fallback: Optional automatic fallback to live database queries if an extract file is unavailable or expired.
#### Rejected Alternatives
- Redis + Pandas: Reading large datasets from Redis into Python Pandas for runtime filtering bottlenecks on worker RAM, CPU, and Redis network throughput.
- Dedicated External OLAP (Pinot/Druid): Requires users to deploy and maintain additional complex distributed infrastructure.
- Database Materialized Views: Maintains network latency for every filter change and relies on source database execution capacity.
#### References
- Discussion #41852
- Discussion #28348
- Tableau Help: [Extract Your Data](https://help.tableau.com/current/pro/desktop/en-us/extracting_data.htm)
Contributor guide
Research direction
Start by reading Discussion #41852 and Discussion #28348, then map the proposed Extract model and REST endpoints to the Data > Extracts, Explore / SQL Lab, and dashboard entry points. Done means the opt-in extraction tier, refresh flow, storage, permissions, configuration, migration, fallback behavior, and listed UI surfaces are implemented.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- python, sql
- Domain
- data-engineering, data-visualization, databases
- Issue type
- Feature
- Difficulty
- 5/5
- Estimated time
- Over a week
- Activity status
- Quiet
- Clarity
- Needs clarification
- Newbie friendliness
- 25/100