Background

A monthly first Friday run to sheets-output was the interim solution since T365404 was closed.

Research & Notes

Superset’s SQL Lab works for writing, running, and exploring SQL queries and saving them as virtual datasets, but it isn’t designed for full, multi-step ETL/orchestration. A virtual dataset in Superset is essentially a saved SQL query plus some metadata (metrics, calculated-columns, default filters, cache settings, etc.). Every time you open a chart or dashboard that uses it, Superset will fire off that SQL (or pull from its cache) against the warehouse. Virtual datasets run on demand, so we might notice long load times whenever the underlying query is heavy or the cache has expired.
Downsides: There's no consistent performance or built-in observability. Load time can increase as the queries increase in complexity and as queries are added to a dashboard (when on a dashboard).
A materialized table built by a pipeline (orchestrated by Airflow) will usually return much faster, since it’s just “SELECT * FROM my_prebuilt_table” rather than a complex join/aggregation every time. And it has retry logic and history to observe any issues encountered.

Queries currently pull from:
campaign_events CORE METRIC
ce_participants CORE METRIC
ce_organizers CORE METRIC
table for gauging newcomer reg dates
ce_invitation_lists (nice to have?)

Potential issues with running on Superset:

• Not all of the metrics can be calculated on Superset. The team will still need to grab the count of new-campaign-editors using another tool.
• Maintenance and data issues will likely apply at some point (see the highlighted chunk in the doc). Other issues may apply as well.

Quick updates:

• Data engineering notes—Heads-up: keep queries under 60 seconds as Superset will time out aggressively thereafter AND keep in mind that this will be an internal-access dashboard.
• @cmelo created a Superset dashboard in T399006 which has 7 charts (Superset virtual datasets) with last_x_months views.
• new-campaign-event-participants which is not reported on at a present and which is used for general team tracking, will be output by @cmelo on a monthly basis.

@cmelo will review the latest update before conferring with the team. We'll sync thereafter and confirm timeline.

This work was completed in the first half of Q1. The dashboard is now available for on demand view.

@cmelo is now outputting New Accounts Created Per Month counts every month and adding this data to the dashboard.