You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@superset.apache.org by "hjadm (via GitHub)" <gi...@apache.org> on 2026/06/27 20:43:33 UTC
[I] [SIP] Dataset Relationship Model β declare and manage relationships between datasets [superset]
hjadm opened a new issue, #41496:
URL: https://github.com/apache/superset/issues/41496
## [Feature] Dataset Relationship Model β declare and manage relationships between datasets
> Reference implementation: https://github.com/apache/superset/pull/40981
---
### π― Problem
Apache Superset has no first-class concept of relationships between datasets. In normalized databases (star schemas, relational models), users must manually create denormalized views or virtual datasets with JOINs every time they need to reference columns from a related table.
This creates friction:
- **Duplication:** multiple virtual datasets that are just thin JOIN wrappers
- **No navigability:** no way to see how datasets connect to each other
- **No infrastructure:** features like cross-filtering, drill-down, and dataset merging lack a standardized metadata layer to consume
### β
Proposed Solution
Introduce a data model + API + UI for declaring **typed, directed relationships** between any pair of Superset datasets.
```
βββββββββββββββββββββββββββββββββββββββββββ
β FEATURE FLAG β
β DATASET_RELATIONSHIPS (bool) β
β Default: False (opt-in) β
βββββββββββββββββββββββββββββββββββββββββββ
βββββββββββββββββββ βββββββββββββββββββ
β SQLAlchemy β β Alembic β
β Models βββββΆβ Migration β
β β β β
β - source/target β β create table β
β - relationship β β dataset_ β
β type (enum) β β relationships β
β - join_type β β create table β
β - column pairs β β dataset_ β
β - soft delete β β relationship_ β
β - audit cols β β columns β
βββββββββββββββββββ βββββββββββββββββββ
ββββββββββββββββββββββββββββββββββββββββββ
β REST API β
β β
β GET/POST/PUT/DELETE /api/v1/ β
β dataset_relationship/ β
β GET /api/v1/dataset_relationship/ β
β dataset/{id} β
β GET /api/v1/dataset/{id} β includes β
β relationships (feature-gated) β
ββββββββββββββββββββββββββββββββββββββββββ
ββββββββββββββββββββββββββββββββββββββββββ
β FRONTEND (React Flow) β
β β
β RelationshipCanvas β Graph editor β
β DatasetNode β Custom node β
β RelationshipEdge β Edge with label β
β ColumnPickerModal β Map columns β
β RelationshipBadge β Explore sidebar β
β DrillDownConfig β Drill-down config β
ββββββββββββββββββββββββββββββββββββββββββ
ββββββββββββββββββββββββββββββββββββββββββ
β CROSS-DATASET FILTERS β
β β
β useCrossDatasetFilters hook β
β β translates active chart filters β
β from source to target WHERE clauses β
β β cross-database merge engine β
β (Redis cache, schema drift detect) β
β β hierarchical drill-down across β
β related datasets β
ββββββββββββββββββββββββββββββββββββββββββ
```
### π€ User Stories
**US1 β Visual navigation**
> "As an analyst, I want to see a graph of how my datasets relate to each other, so I can understand the data model without hunting through documentation."
**US2 β Cross-dataset cross-filtering**
> "As an analyst, I want a filter applied to an Orders chart to automatically filter a related Customers chart, without manually creating JOINs."
**US3 β Hierarchical drill-down**
> "As an analyst, I want to click a country in a Sales chart and drill down to state-level detail, navigating through a many-to-one relationship."
**US4 β Cross-database merge**
> "As an admin, I want to relate a PostgreSQL dataset with a MySQL dataset and let Superset merge them automatically, without an intermediate ETL."
### π Technical Design
#### Models
**DatasetRelationship**
| Field | Type | Description |
|-------|------|-------------|
| id | Integer PK | Auto |
| source_dataset_id | FK β tables.id | Source dataset |
| target_dataset_id | FK β tables.id | Target dataset |
| relationship_type | Enum | one_to_one, one_to_many, many_to_one, many_to_many |
| join_type | Enum | INNER, LEFT, RIGHT, FULL |
| is_cross_database | Bool | Auto-detected |
| is_active | Bool | Soft toggle |
| name | String(255) | Human-readable name (optional) |
| description | Text | Description (optional) |
| created_on / changed_on | DateTime | Audit |
| created_by_fk / changed_by_fk | FK | Audit |
**DatasetRelationshipColumn**
| Field | Type | Description |
|-------|------|-------------|
| id | Integer PK | Auto |
| relationship_id | FK β dataset_relationships | Cascade delete |
| source_column_name | String(255) | Source column |
| target_column_name | String(255) | Target column |
| operator | String(10) | Default "=" |
| ordinal | Integer | Order for multi-column join keys |
Constraints:
- Unique on `(source_dataset_id, target_dataset_id)`
- At least one column pair (Python validation)
- Cascade delete: removing a dataset removes its relationships
#### REST API
| Method | Endpoint | Description |
|--------|----------|-------------|
| GET | /api/v1/dataset_relationship/ | List (filterable, paginated) |
| POST | /api/v1/dataset_relationship/ | Create |
| GET | /api/v1/dataset_relationship/{id} | Get by ID |
| PUT | /api/v1/dataset_relationship/{id} | Update |
| DELETE | /api/v1/dataset_relationship/{id} | Delete |
| DELETE | /api/v1/dataset_relationship/?q=[ids] | Bulk delete |
| GET | /api/v1/dataset_relationship/dataset/{id} | List all for a dataset |
The dataset detail endpoint (`GET /api/v1/dataset/{id}`) gains an optional `relationships` field (feature-gated).
#### Frontend (React Flow)
- **RelationshipCanvas** β interactive graph with zoom, pan, and auto-layout
- **DatasetNode** β custom node showing name, database icon, key columns
- **RelationshipEdge** β custom edge with directional arrow and label (cardinality + join type)
- **ColumnPickerModal** β modal for mapping source β target columns
- **DrillDownConfig** β hierarchical drill-down configuration panel
- **RelationshipBadge** β badge in Explore sidebar showing relationship count
#### Cross-Dataset Filters
- `useCrossDatasetFilters` hook that translates active chart filters into WHERE clauses targeting the related dataset
- Cross-database merge engine with Redis caching
- Automatic JOIN injection for related datasets
- Schema drift detection via scheduled Celery task
### π§ Feature Flag
**`DATASET_RELATIONSHIPS = False`** in `config.py`.
When disabled:
- API returns 403 on relationship endpoints
- Relationship fields do not appear in responses
- Frontend does not render the canvas or badges
- Zero impact on performance or existing behavior
### π¦ Dependencies
| Package | Version | License | Purpose |
|---------|---------|---------|---------|
| @xyflow/react | ^12.x | MIT | Graph canvas rendering |
Zero new Python dependencies.
### π Migration
One Alembic revision creating:
- `dataset_relationships`
- `dataset_relationship_columns`
Safe rollback via `DROP TABLE ... CASCADE`.
### β Open Questions
1. **Cross-database merge engine**: should it ship in v1 or as a follow-up feature?
2. **Drill-down config**: how to expose it in the UI without cluttering the chart builder?
3. **Performance**: is Redis caching sufficient for frequent cross-database merges, or is something more needed?
4. **Schema drift**: ideal frequency for the detection Celery task? Daily? Weekly?
5. **Naming**: is "Dataset Relationship Model" clear enough, or are there better suggestions?
### π Links
- **Implementation PR:** https://github.com/apache/superset/pull/40981
- **SIP Document:** https://github.com/hjadm/hibi/blob/main/SIP-DATASET-RELATIONSHIPS.md
### ποΈ Implementation Status
| Block | Status |
|-------|--------|
| Models + Migration | β
Complete |
| REST API | β
Complete |
| Feature Flag | β
Complete |
| Frontend Canvas | β
Complete |
| Cross-DB Merge Engine | β
Complete |
| Cross-Dataset Filters | β
Complete |
| Drill-Down Navigation | β
Complete |
| Tests (156 passing) | β
Complete |
| Schema Drift Detection | β
Complete |
### π§ Blockers for Upstream
- Decision on whether cross-database merge should ship in v1
- Security review of automatic JOIN injection
- Performance benchmarks for cross-database queries
- Schema drift periodicity definition
- SIP approval by the community
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] [SIP-217] Dataset Relationship Model β declare and manage relationships between datasets [superset]
Posted by "rusackas (via GitHub)" <gi...@apache.org>.
rusackas commented on issue #41496:
URL: https://github.com/apache/superset/issues/41496#issuecomment-4884189401
Once the sip is split/rescoped (feel free to just start a new one and close this one if it's easier) you'll want to subscribe to the dev@ list to be able to post to the listserv.
If you haven't already, you can subscribe to the Apache Superset Dev Mailing Listserv ([dev@superset.apache.org](mailto:dev@superset.apache.org), with public visibility [here](https://lists.apache.org/list.html?dev@superset.apache.org)) by sending an email to [dev-subscribe@superset.apache.org](mailto:dev-subscribe@superset.apache.org).
The remainder of the process is laid out here:
[Superset Improvement Proposal (SIP)](https://github.com/apache/superset/issues/5602)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] [SIP] Dataset Relationship Model β declare and manage relationships between datasets [superset]
Posted by "rusackas (via GitHub)" <gi...@apache.org>.
rusackas commented on issue #41496:
URL: https://github.com/apache/superset/issues/41496#issuecomment-4828017576
Thanks @hjadm, there's a lot to like here, and a relationship graph is something folks have wanted for ages.
I do have some concerns though, so here are my thoughts so far on all this:
The cross-database merge engine is the big one. Pulling both sides into Superset and joining them in-process is a line we've pretty deliberately not crossed... it basically turns Superset into a federated query engine, with all the memory and scale questions that come with it. I'd be a lot more comfortable carving that out (your Open Question #1) and shipping the relationship model + canvas on their own first. The Superset Meta Database toys with this idea a bit, but flags all the risks that it's NOT intended for large datasets and will cause problems.
Related, and maybe the part that worries me most: how do RLS and perms travel across a relationship? If I can see dataset A but not B, a declared AβB join is a fairly direct way to read B's rows. That feels like it needs designing in up front rather than a later security pass if and when people discover problems.
I also want to be sure we're not building a second version of things we already have. Virtual datasets already express JOINs, and dashboards already have native filters with cross-filtering. How does this sit alongside those instead of competing with them? And on the model itself, I'm not clear how many-to-many join types avoid metric fan-out / double-counting, which is usually where this gets gnarly.
The bigger one: this overlaps a fair bit with SIP-182 (#35003). That one is basically trying to loosen Superset's coupling to the dataset model (the Explorable idea), and relationships are arguably a semantic-layer concern. External semantic layers define their own joins, so we'd risk two sources of truth. Hanging relationships off datasets pushes the opposite direction from where #35003 wants to go, so I don't think we want to consider them in isolation. Would love to see the two reconciled, or at least for this to say how it fits the Explorable direction. CC @betodealmeida / @michael-s-molina, who'll likely have opinions here.
Last thing, more process than substance: this might be an easier yes if it were split into a smaller SIPs (model & UI, the cross-DB engine, the cross-filtering). Each is kind of its own thing, and bundling them means we can't approve the safe parts without taking on the risky ones too
Thoughts? Happy to help navigate the dev@ side if it's useful.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] [SIP-217] Dataset Relationship Model β declare and manage relationships between datasets [superset]
Posted by "hjadm (via GitHub)" <gi...@apache.org>.
hjadm commented on issue #41496:
URL: https://github.com/apache/superset/issues/41496#issuecomment-4902911451
@rusackas thanks for the guidance, I'll follow the SIP process on dev@.
Before I start writing the formal proposal, I'd like to hear what @betodealmeida and @michael-s-molina have to say about SIP-182, if possible. If there's anything to align for compatibility, I'd rather incorporate it from the start than have to adjust midway.
This is my first time opening a SIP in Apache Superset, so I want to do it right.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org