[SIP-7] Ensuring Superset accurately represents the underlying data warehouse

[john-bodley]

[SIP] Proposal for ensuring Superset accurately represents the underlying data warehouse

Motivation

Over time there can be a discrepancy between the actual metadata of a Druid datasource or SQLA table and the corresponding Superset representation. Columns or metrics can be added, updated, or deleted, and thus overtime creep grows between the systems. The proposal is to try to add functionality to better ensure consistency between Superset and the underlying data warehouses which should help boost user confidence.

Proposed Change

Currently there is a mechanism to refresh Druid metadata per the following menu item:

thus the plan would be to also include an option for refreshing SQLA table metadata. We could optionally also provide a mechanism to scan for new SQLA tables though we should be aware that there scale of datasources (and their corresponding columns/metrics) could negatively impact the performance of Superset.

Refreshing

Regarding the term "refreshing" I proposed the following is instrumented for both Druid and SQLA datasources.

Columns/Metrics

• New entities are added
• Existing entities are updated
• Obsolete entities are deleted†

†Note derived entities which reference an obsolete entity will also be deleted.

For reference here's the current UI behavior. Starting with a baseline:

when one drops a column (sum_boys in this instance) from the underlying table the UI state remains unchanged and the query fails to execute (as expected) with the no such column: sum_boys error:

Finally if one deletes the column and corresponding metrics from the Superset datasource, the UI state remains unchanged (also expected as the state is defined in its entirety from the form-data), though the query never runs as Superset rightfully raises an error stating that the metric is non-valid:

These behaviors seem correct and should remain unchanged.

Datasources

An unanswered question remains about what should happen when a Druid or SQLA datasource is defined in Superset but no longer resides in the underlying data warehouse. One school of thought is that if the underlying Druid datasource or SQLA table no longer exists we should:

• Delete all slices which reference the datasource
• Delete all empty dashboards (if appropriate)†
• Delete the datasource

†Note I'm unsure what the current logic is for having a dashboard with no slices.

The concern with this approach is it is a fairly destructive process, i.e., significant IP is potentially lost when one deletes a slice. Would it make more sense that the underlying institution controls when/how datasources are deleted? For example one could define a policy that if said datasource has been deleted (and not restored) for n consecutive days then it's probably safe to delete it from Superset.

New dependencies

The following existing PRs are required which ensures we have uniqueness at the datasource/column/metric level and the appropriate fields are non-nullable:

• [wtforms] Using wtforms-json which prevents encoding empty strings in the database #5445
• [wtforms] Strip leading/trailing whitespace #7084
• [schema] Updating the tables schema #5449
• [schema] Updating the datasources schema #5451
• [schema] Updating the base column schema #5452
• [schema] Updating the base metric schema #5453

Note many of these PRs require migrations which may need manual intervention as the lack of constraints and non-nullability may have resulted in a corrupted (and thus complex) database in which procedurally defined migration rules are non-viable.

Open Questions

1. Should we delete datasources which no longer exist in the data warehouse?
2. How do we deal with custom SQL definitions when the underlying datasource changes? I suspect this is out-of-scope.

to: @betodealmeida @michellethomas @mistercrunch @timifasubaa

[kristw]

Should be SIP-7

[john-bodley]

Note this is somewhat related to #8639 with regards to the auto-cleanup.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions. For admin, please label this issue .pinned to prevent stale bot from closing the issue.

[michael-s-molina]

@john-bodley Closing this due to inactivity as part of SIP board cleaning. Please re-open if you think it's still active.