migrate-explorer-grapher

name: migrate-explorer-grapher description: >- Translate a non-ETL explorer (lives in MySQL explorers table; TSV mirrored in owid-grapher/explorers/) whose graphers block references variable IDs or chart IDs into the inputs /create-explorer needs. Resolves IDs to catalog paths, pulls per-chart config when needed, and produces the dimension + view layout. Trigger when the user says "bring explorer into ETL", "migrate grapher explorer ", or refers to one of: energy, co2, democracy, global-health, food-prices, fertilizers, natural-disasters, food-footprints, plastic-pollution, conflict-data, conflict-data-source, countries-in-conflict-data. metadata: internal: true

Translate a non-ETL grapher-style explorer for /create-explorer

The TSV in owid-grapher/explorers/<slug>.explorer.tsv (and the live config in MySQL explorers.config) is the legacy source of truth. This skill pulls the IDs out of it, resolves them to ETL catalog paths, recovers any chart-stored config, and hands the result to /create-explorer to write the actual export://explorers/<ns>/latest/<short> step.

Scope of this skill: the ID-resolution and TSV-translation half. The export-step authoring (Python skeleton, YAML schema, full-YAML vs table-driven choice, FAUST upstream, post-processing, DAG, verification) lives in /create-explorer and is shared with the other migrate-explorer-* skills and with /create-multidim. Don't duplicate that content here.

Inputs

Required:

• <slug> — explorer slug (matches the .explorer.tsv filename and the slug column in MySQL explorers)
• <ns> — target namespace under etl/steps/export/explorers/<ns>/latest/
• <short> — file/short name for the new step

Step 0 — Classify the explorer

Different graphers columns require different lookups. Sample the legacy config:

cat <<'EOF' > /tmp/classify_explorer.py
from etl.config import OWID_ENV
import json, sys
slug = sys.argv[1]
df = OWID_ENV.read_sql(f"SELECT config FROM explorers WHERE slug = '{slug}'")
cfg = json.loads(df["config"].iloc[0]) if isinstance(df["config"].iloc[0], str) else df["config"].iloc[0]
blocks = cfg.get("blocks", [])
print("blocks:", [b.get("type") for b in blocks])
gb = next((b for b in blocks if b.get("type") == "graphers"), None)
if gb:
    cols = set().union(*(r.keys() for r in gb.get("block", [])))
    print("graphers cols:", sorted(cols))
EOF
.venv/bin/python /tmp/classify_explorer.py <slug>

Step 1 — Read the legacy explorer config

Two sources: the TSV in owid-grapher/explorers/<slug>.explorer.tsv, and the live config in MySQL explorers.config. Prefer the DB if they disagree. The TSV has three sections:

• Settings rows at the top (key/value): explorerTitle, explorerSubtitle, isPublished, hasMapTab, selection, pickerColumnSlugs, subNavId, subNavCurrentId, wpBlockId, entityType, originUrl, googleSheet, downloadDataLink, hideAlertBanner, thumbnail, yScaleToggle, yAxisMin, hideAnnotationFieldsInTitle, … These map verbatim into the explorer's top-level config: block (see /create-explorer for the schema).
• graphers table: one row per view. Columns include dimension widget names (e.g. Metric Dropdown, Source Radio, Per capita Checkbox), the ID column(s) from Step 0, plus per-view chart config (title, subtitle, type, hasMapTab, minTime, yAxisMin, …).
• columns table (optional): per-(view, indicator) display overrides.

Step 2 — Map IDs to catalog paths

# yVariableIds (and xVariableId, colorVariableId, sizeVariableId) — direct lookup
make query SQL="SELECT id, catalogPath FROM variables WHERE id IN (...)"

# grapherId — two-step lookup. Chart config lives in chart_configs (joined via charts.configId).
make query SQL="SELECT c.id, JSON_EXTRACT(cc.full, '\$.dimensions[0].variableId') AS yVarId FROM charts c JOIN chart_configs cc ON c.configId = cc.id WHERE c.id IN (...)"
# then feed the resulting variable IDs into the variables query above.

# Pull the full chart config in one go (variable IDs + title/subtitle/type/map/etc.) for grapher-chart explorers:
make query SQL="SELECT c.id, cc.full FROM charts c JOIN chart_configs cc ON c.configId = cc.id WHERE c.id IN (...)"

Variables without catalogPath are not yet in ETL — their underlying datasets need to be migrated first (use the /migrate-dataset skill). Halt and report which datasets are missing rather than guessing.

For multi-indicator charts (e.g. stacked bars), dimensions[0] only gives the first y variable. Pull all dimensions from chart_configs.full and treat each one as an entry in view.indicators.y[].

Step 3 — Identify upstream grapher datasets

For each catalog path (<ns>/<v>/<dataset>/<table>#<short>), the underlying step is data://grapher/<ns>/<v>/<dataset>. Collect the unique set — these become the explorer step's DAG dependencies (handed to /create-explorer in step 5).

Mental model for grapher-chart-based explorers: each grapherId is a thin wrapper around an indicator (or a few). The migration "unwraps" the chart, recovers its underlying indicators, and rebuilds the explorer directly from those indicators. Anything the chart has stored — title, subtitle, color scale, map config — either flows from the indicator's garden metadata (preferred for single-indicator views; see /create-explorer Step 5) or has to be re-stated in the explorer YAML.

Step 4 — Translate the legacy tables

For grapher-chart-based explorers, also pull each chart's stored config (SELECT cc.full FROM charts c JOIN chart_configs cc ON c.configId = cc.id WHERE c.id = ?) and merge title/subtitle/type/hasMapTab/yAxis/map.colorScale/etc. into view.config — or, for single-indicator views, into the indicator's garden metadata.

Step 5 — Hand off to /create-explorer

Invoke /create-explorer with the materials prepared above:

• the top-level config: settings (from TSV settings rows)
• the dimensions list (slugs, names, presentation types, choices)
• the views list (per-view dimension tuples + catalogPaths + per-view config overrides)
• the upstream data://grapher/... deps for the DAG entry

/create-explorer covers: the Python skeleton (full-YAML vs table-driven), the YAML schema (config:, definitions.common_views, dimensions:, views:), block-style YAML rule, hyphens vs underscores, conditional dimensions, FAUST upstream, post-processing (sort_choices, group_views), DAG entry, verification, common pitfalls, reference examples.

Reference: existing migrations to model after

(see /create-explorer for the canonical list — examples that originated as /migrate-explorer-grapher runs include food_prices, fertilizers, food_footprints, countries_in_conflict_data)

Follow-up

Once the explorer is in ETL, it's a candidate for the Track-B port to MDIM (export://multidim/...) once feature parity is reached. See umbrella issue #6014.