---
name: superset
description: "Manage local Apache Superset resources (datasets, charts, dashboards, filters) via REST API. Use when user asks to create, update, delete, or inspect Superset dashboards, virtual datasets, or charts."
argument-hint: "[action] [resource]"
---

# Superset Management Skill

Operate a local Apache Superset instance via its REST API. Covers the full lifecycle: authenticate, discover databases, CRUD virtual datasets / charts / dashboards / native filters.

## Connection & Credentials

**Before making any API call, ask the user for the Superset username and password.** Do NOT assume default credentials. The password often contains special characters that break shell escaping — this is expected.

```
SUPERSET_URL  = http://127.0.0.1:8088   # default, confirm with user
SUPERSET_USER = <ask user>
SUPERSET_PASS = <ask user>
```

**IMPORTANT: Always use Python (stdlib) for Superset API calls.** Curl is fragile due to: (1) password special character escaping, (2) HTTP proxy interference, (3) cookie jar state management. The Python helper below handles all of these reliably.

## Authentication Flow (MUST do first)

**Use this Python pattern for ALL Superset API operations.** It handles proxy bypass, session cookies, JWT, and CSRF in one place.

```python
import http.cookiejar, urllib.request, json

SUPERSET_URL = 'http://127.0.0.1:8088'
SUPERSET_USER = '<ask user>'
SUPERSET_PASS = '<ask user>'

# 1. Cookie jar + proxy bypass — BOTH are ESSENTIAL
cj = http.cookiejar.CookieJar()
opener = urllib.request.build_opener(
    urllib.request.HTTPCookieProcessor(cj),
    urllib.request.ProxyHandler({})  # bypass http_proxy env var
)

# 2. Form-based login to establish Flask session cookie
#    (JWT alone is NOT sufficient for write operations — causes 302 redirect)
login_data = urllib.parse.urlencode({
    'username': SUPERSET_USER,
    'password': SUPERSET_PASS
}).encode()
req = urllib.request.Request(
    f'{SUPERSET_URL}/login/',
    data=login_data, method='POST',
    headers={'Content-Type': 'application/x-www-form-urlencoded'}
)
try:
    opener.open(req)
except urllib.error.HTTPError:
    pass  # 302 redirect after successful login is expected

# 3. JWT token (for API Authorization header)
jwt_data = json.dumps({
    'username': SUPERSET_USER,
    'password': SUPERSET_PASS,
    'provider': 'db', 'refresh': True
}).encode()
req = urllib.request.Request(
    f'{SUPERSET_URL}/api/v1/security/login',
    data=jwt_data, method='POST',
    headers={'Content-Type': 'application/json'}
)
token = json.loads(opener.open(req).read())['access_token']

# 4. CSRF token (MUST use same opener to capture session cookie)
req = urllib.request.Request(
    f'{SUPERSET_URL}/api/v1/security/csrf_token/',
    headers={'Authorization': f'Bearer {token}'}
)
csrf = json.loads(opener.open(req).read())['result']

# 5. Reusable headers
headers_mut = {
    'Authorization': f'Bearer {token}',
    'Content-Type': 'application/json',
    'X-CSRFToken': csrf,
    'Referer': f'{SUPERSET_URL}/'
}
headers_get = {'Authorization': f'Bearer {token}'}

# 6. Helper functions — ALL calls go through opener (not urlopen)
def api_get(path):
    req = urllib.request.Request(f'{SUPERSET_URL}{path}', headers=headers_get)
    return json.loads(opener.open(req).read())

def api_post(path, payload):
    data = json.dumps(payload).encode()
    req = urllib.request.Request(f'{SUPERSET_URL}{path}', data=data, headers=headers_mut, method='POST')
    return json.loads(opener.open(req).read())

def api_put(path, payload):
    data = json.dumps(payload).encode()
    req = urllib.request.Request(f'{SUPERSET_URL}{path}', data=data, headers=headers_mut, method='PUT')
    return json.loads(opener.open(req).read())

def api_delete(path):
    req = urllib.request.Request(f'{SUPERSET_URL}{path}', headers=headers_mut, method='DELETE')
    return json.loads(opener.open(req).read())

# 7. Database ID lookup
db_list = api_get('/api/v1/database/?page_size=1000')
DB_ID = next((d['id'] for d in db_list['result']
              if d['database_name'] == 'FinOPSGoogleBigQuery'), None)
```

**CRITICAL rules:**
- Always use `opener.open()`, never `urllib.request.urlopen()`. The latter does not share cookies.
- Always include `ProxyHandler({})` in the opener. Without it, `http_proxy` env var routes requests through a proxy, causing 302 redirects.
- Always do the form-based login to `/login/` BEFORE JWT login. JWT alone does not establish the Flask session cookie needed for write operations.

### Bash Alternative (for bootstrap scripts only)

Use bash/curl only in committed bootstrap scripts. For ad-hoc operations, always use Python.

The form login requires a **WTF CSRF token** extracted from the HTML page. Without it, the POST returns 302 and no session cookie is set. The full flow is: GET `/login/` → extract WTF CSRF → POST `/login/` → JWT → API CSRF.

```bash
COOKIE_JAR="${TMPDIR:-/tmp}/superset_skill_$$.txt"
: > "$COOKIE_JAR"
CURL_NO_PROXY='*'
trap 'rm -f "$COOKIE_JAR"' EXIT

# 1. GET /login/ to obtain session cookie + WTF CSRF token from the HTML form
LOGIN_PAGE="$(curl --noproxy "$CURL_NO_PROXY" -sS "$SUPERSET_URL/login/" \
  -c "$COOKIE_JAR" -b "$COOKIE_JAR")"
WTF_CSRF="$(echo "$LOGIN_PAGE" | python3 -c "
import sys, re; html = sys.stdin.read()
m = re.search(r'name=\"csrf_token\"[^>]*value=\"([^\"]+)\"', html)
if not m: m = re.search(r'value=\"([^\"]+)\"[^>]*name=\"csrf_token\"', html)
print(m.group(1) if m else '')
")"

# 2. POST /login/ with form data + WTF CSRF token
#    Do NOT use -L (follow redirects) — 302 IS success, following it causes 405
#    and loses the session cookie
ENCODED_PASS="$(python3 -c "import urllib.parse,sys; print(urllib.parse.quote(sys.argv[1]))" "$SUPERSET_PASS")"
curl --noproxy "$CURL_NO_PROXY" -sS -X POST "$SUPERSET_URL/login/" \
  -c "$COOKIE_JAR" -b "$COOKIE_JAR" \
  -d "username=${SUPERSET_USER}&password=${ENCODED_PASS}&csrf_token=${WTF_CSRF}" \
  -o /dev/null 2>/dev/null || true

# 3. JWT token — use jq to build JSON (handles special chars in password)
LOGIN_PAYLOAD="$(jq -cn --arg u "$SUPERSET_USER" --arg p "$SUPERSET_PASS" \
  '{username:$u, password:$p, provider:"db", refresh:true}')"
LOGIN_RESP="$(curl --noproxy "$CURL_NO_PROXY" -sS -X POST "$SUPERSET_URL/api/v1/security/login" \
  -c "$COOKIE_JAR" -b "$COOKIE_JAR" \
  -H "Content-Type: application/json" \
  -d "$LOGIN_PAYLOAD")"
ACCESS_TOKEN="$(echo "$LOGIN_RESP" | jq -r '.access_token // empty')"

# 4. CSRF token
CSRF_RESP="$(curl --noproxy "$CURL_NO_PROXY" -sS "$SUPERSET_URL/api/v1/security/csrf_token/" \
  -c "$COOKIE_JAR" -b "$COOKIE_JAR" \
  -H "Authorization: Bearer $ACCESS_TOKEN")"
CSRF_TOKEN="$(echo "$CSRF_RESP" | jq -r '.result // empty')"
```

**CRITICAL**:
- The cookie jar (`-c` and `-b`) MUST be passed on every curl call. Without it, DELETE/PUT/POST will return HTTP 302.
- Form-based login to `/login/` MUST come before JWT login. In Superset 6.x, the JWT endpoint itself returns `{"message": "Not authorized"}` without a session cookie.
- The form login POST requires a **WTF CSRF token** (`csrf_token` hidden field from the login page HTML). Without it, the POST silently fails with 302 and no session cookie is written.
- Do NOT use `-L` (follow redirects) on the form login POST. The 302 redirect IS the success signal. Following it with `-L` results in HTTP 405 and **clears the cookie jar**.
- NEVER build JSON payloads with shell string interpolation (`-d "{\"password\":\"$PASS\"}"`). Passwords with `\`, `"`, `!`, `@` will break JSON parsing. Always use `jq -cn` to build payloads.
- When running bootstrap scripts, use `export` for env vars (not inline `VAR=x && bash script.sh`). Without `export`, the child bash process falls back to default values.

## Bash API Helper Pattern (for bootstrap scripts)

Wrap all bash API calls in helpers that share the cookie jar and auth headers:

```bash
api_get()       { curl --noproxy "$CURL_NO_PROXY" -sS "$SUPERSET_URL$1" \
                    -c "$COOKIE_JAR" -b "$COOKIE_JAR" \
                    -H "Authorization: Bearer $ACCESS_TOKEN"; }

api_post_json() { curl --noproxy "$CURL_NO_PROXY" -sS -X POST "$SUPERSET_URL$1" \
                    -c "$COOKIE_JAR" -b "$COOKIE_JAR" \
                    -H "Authorization: Bearer $ACCESS_TOKEN" \
                    -H "X-CSRFToken: $CSRF_TOKEN" \
                    -H "Referer: $SUPERSET_URL" \
                    -H "Content-Type: application/json" \
                    -d "$2"; }

api_put_json()  { curl --noproxy "$CURL_NO_PROXY" -sS -X PUT "$SUPERSET_URL$1" \
                    -c "$COOKIE_JAR" -b "$COOKIE_JAR" \
                    -H "Authorization: Bearer $ACCESS_TOKEN" \
                    -H "X-CSRFToken: $CSRF_TOKEN" \
                    -H "Referer: $SUPERSET_URL" \
                    -H "Content-Type: application/json" \
                    -d "$2"; }

api_delete()    { curl --noproxy "$CURL_NO_PROXY" -sS -X DELETE "$SUPERSET_URL$1" \
                    -c "$COOKIE_JAR" -b "$COOKIE_JAR" \
                    -H "Authorization: Bearer $ACCESS_TOKEN" \
                    -H "X-CSRFToken: $CSRF_TOKEN" \
                    -H "Referer: $SUPERSET_URL" \
                    -H "Content-Type: application/json"; }
```

## REST API Reference

### Datasets (Virtual Datasets)

```bash
# List all datasets
api_get "/api/v1/dataset/?page_size=5000"
# → .result[] | {id, table_name, schema, database_id, sql}

# Create virtual dataset
api_post_json "/api/v1/dataset/" "$(jq -cn \
  --arg db "$DB_ID" --arg s "$SCHEMA" --arg t "$NAME" --arg sql "$SQL" \
  '{database:($db|tonumber), schema:$s, table_name:$t, sql:$sql}')"
# → .result.id  (or .id, .table_id)

# Update dataset SQL
api_put_json "/api/v1/dataset/$DS_ID" "$(jq -cn \
  --arg sql "$SQL" '{sql:$sql, main_dttm_col:"billing_period_month"}')"

# Refresh dataset metadata (try PUT first, fallback POST)
api_put_json "/api/v1/dataset/$DS_ID/refresh" '{}'

# Delete dataset
api_delete "/api/v1/dataset/$DS_ID"
```

**"Already exists" handling**: If create returns an existing-dataset error, search by `table_name` to find its ID and update instead.

### Charts

```bash
# List all charts
api_get "/api/v1/chart/?page_size=5000"
# → .result[] | {id, slice_name, viz_type, datasource_id}

# Create chart
PARAMS="$(jq -cn --argjson fd "$FORM_DATA" --arg ds "${DS_ID}__table" --arg vt "$VIZ_TYPE" \
  '$fd + {datasource:$ds, viz_type:$vt}')"
api_post_json "/api/v1/chart/" "$(jq -cn \
  --arg sn "$SLICE_NAME" --arg vt "$VIZ_TYPE" --arg params "$PARAMS" \
  --arg dsid "$DS_ID" --argjson dashboards "[$DASHBOARD_ID]" \
  '{slice_name:$sn, viz_type:$vt, params:$params,
    datasource_id:($dsid|tonumber), datasource_type:"table",
    dashboards:$dashboards, query_context_generation:true}')"

# Update chart
api_put_json "/api/v1/chart/$CHART_ID" "$PAYLOAD"

# Delete chart
api_delete "/api/v1/chart/$CHART_ID"

# Find chart by name + dataset
echo "$CHART_LIST" | jq -r --arg n "$NAME" --arg ds "$DS_ID" \
  '.result[]? | select(.slice_name == $n and ((.datasource_id|tostring) == $ds)) | .id' | head -n1
```

**viz_type fallback**: If `echarts_timeseries_bar` or `echarts_timeseries_line` fails with "not registered", retry with `viz_type: "table"`.

### Dashboards

```bash
# List all dashboards
api_get "/api/v1/dashboard/?page_size=1000"
# → .result[] | {id, dashboard_title, slug}

# Create dashboard
api_post_json "/api/v1/dashboard/" "$(jq -cn \
  --arg title "$TITLE" --arg slug "$SLUG" --argjson meta "$META_JSON" \
  '{dashboard_title:$title, slug:$slug, published:true, json_metadata:($meta|tojson)}')"

# Update dashboard
api_put_json "/api/v1/dashboard/$DASH_ID" "$PAYLOAD"

# Get charts in a dashboard
api_get "/api/v1/dashboard/$DASH_ID/charts"

# Delete dashboard
api_delete "/api/v1/dashboard/$DASH_ID"
```

### Native Filters

Native filters are stored inside `json_metadata.native_filter_configuration` on the dashboard.

```bash
# Read current metadata
DETAIL="$(api_get "/api/v1/dashboard/$DASH_ID")"
CURRENT_META="$(echo "$DETAIL" | jq -c '
  (.result.json_metadata // {}) | if type == "string" then (try fromjson catch {}) else . end')"

# Build filter array (example: time + select)
FILTERS="$(jq -cn --arg ds "$DS_ID" '[
  {
    id: "NATIVE_FILTER-time",
    name: "Time Range",
    filterType: "filter_time",
    targets: [{datasetId:($ds|tonumber), column:{name:"billing_period_month"}}],
    required: false, cascadeParentIds: [],
    scope: {rootPath:["ROOT_ID"], excluded:[]}
  },
  {
    id: "NATIVE_FILTER-account",
    name: "Account",
    filterType: "filter_select",
    targets: [{datasetId:($ds|tonumber), column:{name:"account_display_name"}}],
    controlValues: {multiSelect:true, enableEmptyFilter:false, searchAllOptions:true},
    required: false, cascadeParentIds: [],
    scope: {rootPath:["ROOT_ID"], excluded:[]}
  }
]')"

# Merge and update
MERGED="$(jq -cn --argjson cur "$CURRENT_META" --argjson nf "$FILTERS" \
  '$cur + {native_filter_configuration:$nf}')"
api_put_json "/api/v1/dashboard/$DASH_ID" "$(jq -cn --argjson m "$MERGED" \
  '{json_metadata:($m|tojson)}')"
```

**Filter types**: `filter_time`, `filter_select`, `filter_range`.
**Cascade**: Set `cascadeParentIds` to parent filter IDs for dependent dropdowns.

## Dashboard Metadata (Label Colors)

```json
{
  "color_scheme": "",
  "label_colors": {
    "Compliant": "#1FA8C9",
    "Non-Compliant": "#FF5A5F",
    "Exempt": "#999999"
  },
  "shared_label_colors": { "...same..." },
  "native_filter_configuration": [],
  "cross_filters_enabled": true
}
```

Both `label_colors` and `shared_label_colors` must be set for colors to apply consistently.

## Common Chart form_data Patterns

### Stacked Bar (time series)
```json
{
  "query_mode": "aggregate",
  "granularity_sqla": "billing_period_month",
  "time_grain_sqla": "P1M",
  "time_range": "No filter",
  "groupby": ["dimension_column"],
  "metrics": [{"expressionType":"SQL","sqlExpression":"SUM(metric_col)","label":"metric_name"}],
  "stack": "Stack",
  "row_limit": 10000
}
```

### Line Chart
```json
{
  "query_mode": "aggregate",
  "granularity_sqla": "billing_period_month",
  "time_grain_sqla": "P1M",
  "time_range": "No filter",
  "groupby": ["dimension_column"],
  "metrics": [{"expressionType":"SQL","sqlExpression":"SAFE_DIVIDE(SUM(a), SUM(b)) * 100","label":"pct"}],
  "y_axis_format": ".1f",
  "row_limit": 10000
}
```

### Table
```json
{
  "query_mode": "aggregate",
  "time_range": "No filter",
  "groupby": ["col1", "col2"],
  "metrics": [
    {"expressionType":"SQL","sqlExpression":"SUM(cost)","label":"total_cost"},
    {"expressionType":"SQL","sqlExpression":"SUM(tagged)","label":"tagged_cost"}
  ],
  "order_desc": true,
  "row_limit": 50
}
```

## Upsert Strategy

The project's bootstrap scripts follow an **upsert** pattern:

1. **Dataset**: Try create → if "already exists", find by name → update SQL
2. **Dashboard**: Search by title → if found, update; else create
3. **Chart**: Search by `(slice_name, datasource_id)` → if found, update; else create
4. **Filters**: Always overwrite `native_filter_configuration` on the dashboard

## Deletion Workflow

When renaming charts or restructuring a dashboard, old resources must be deleted first:

```bash
# 1. Find dashboard by slug
DASH_ID="$(api_get "/api/v1/dashboard/?page_size=1000" | jq -r \
  --arg s "$SLUG" '.result[]? | select(.slug == $s) | .id' | head -n1)"

# 2. List charts in dashboard
CHART_IDS="$(api_get "/api/v1/dashboard/$DASH_ID/charts" | jq -r '.result[]?.id')"

# 3. Delete each chart
for cid in $CHART_IDS; do api_delete "/api/v1/chart/$cid"; done

# 4. Delete dataset(s)
DS_ID="$(api_get "/api/v1/dataset/?page_size=5000" | jq -r \
  --arg t "$VDS_NAME" '.result[]? | select(.table_name == $t) | .id' | head -n1)"
api_delete "/api/v1/dataset/$DS_ID"

# 5. Delete dashboard
api_delete "/api/v1/dashboard/$DASH_ID"
```

## Gotchas

1. **Always use Python for ad-hoc operations** — Curl is fragile due to password escaping, proxy interference, and cookie state. Use the Python auth flow in the Authentication section above.
2. **HTTP proxy causes 302 on write operations** — If `http_proxy` env var is set, requests route through a proxy and lose auth context. In Python, always include `ProxyHandler({})` in the opener. In bash, always use `--noproxy '*'`.
3. **Form-based login is required for ALL API calls** — JWT token alone is NOT sufficient. You MUST first POST to `/login/` (form-based) to establish the Flask session cookie, then use JWT + CSRF for API calls. In Superset 6.x, even the JWT login endpoint (`POST /api/v1/security/login`) returns `{"message": "Not authorized"}` without a prior session cookie. Without the session cookie, write operations return HTTP 302 redirect to login page. **In bash/curl**, the form POST also requires a WTF CSRF token extracted from the login page HTML (see Bash Alternative section).
4. **Cookie jar is mandatory** — without `CookieJar` + `opener` (Python) or `-c`/`-b` cookie jar (bash), write operations return HTTP 400 "CSRF session token is missing" or HTTP 302 redirect.
5. **Python `urlopen` vs `opener.open`** — `urllib.request.urlopen()` does NOT persist cookies across requests. Must use `opener = build_opener(HTTPCookieProcessor(CookieJar()), ProxyHandler({}))` and call `opener.open()` for every request.
6. **CSRF token** — required for all write operations via `X-CSRFToken` header.
7. **Referer header** — Superset rejects POST/PUT without `Referer: $SUPERSET_URL/`.
8. **API list results contain duplicates** — Chart/dataset list endpoints may return duplicate rows due to internal joins (e.g., API reports `count: 90` but only 20 unique charts exist). Always deduplicate by `id` when processing results.
9. **Pagination URL format** — Use `?page_size=N` (not `?q=(page_size:N)`). The parentheses in `?q=(...)` break in zsh due to glob expansion.
10. **Dataset refresh** — after updating VDS SQL, call the refresh endpoint to sync column metadata.
11. **Chart viz_type** — if a plugin isn't installed (e.g. `echarts_timeseries_bar`), the API returns "not registered". Fall back to `table`.
12. **Dashboard layout** — the API cannot arrange chart positions. Layout must be done manually in the Superset UI.
13. **BigQuery schema** — Superset may store schema as `project.dataset` or just `dataset`. Dataset lookup should handle both formats.
14. **jq for payloads** — in bash scripts, always use `jq -cn` to build JSON payloads to avoid quoting issues with SQL strings.
15. **Bulk deletion order** — dependency chain (top → bottom): Dashboard → Chart → Dataset → Database Connection. SQL defines the query logic, Dataset builds the semantic layer, Chart renders a single visualization, Dashboard assembles charts for presentation. Delete from top down to avoid orphan references. In practice, query the dashboard first to discover its charts, then delete: Charts → Datasets → Dashboard.
16. **Don't mix curl and Python in one auth flow** — Session cookies from curl are NOT available to Python's CookieJar, and Python's `MozillaCookieJar.save()` writes a format that curl's `-b` may not read correctly. Pick one approach and use it end-to-end. Bootstrap scripts should use pure bash/curl auth; ad-hoc operations should use pure Python.
17. **Form login POST: do NOT follow redirects** — Using `-L` or `--max-redirs` on the `POST /login/` causes curl to follow the 302 to a page that returns 405 (Method Not Allowed), and the session cookie gets cleared from the jar. Let the 302 happen without following — it IS the success signal.
18. **Form login requires WTF CSRF token** — Superset's Flask-WTF form protection requires a `csrf_token` hidden field. You must GET `/login/` first, parse the HTML to extract the token, then include `csrf_token=<value>` in the POST body. Without it, the POST returns 302 silently and no session cookie is set.
19. **Bootstrap script env vars must be exported** — When calling `bash dbt/scripts/create_superset_*.sh`, environment variables must be set with `export VAR=value` (not `VAR=value &&`). Without `export`, the child bash process inherits nothing and falls back to default values (e.g., password defaults to `admin`).

## Project-Specific Context

- **Database**: `FinOPSGoogleBigQuery` (BigQuery connection)
- **BQ Project**: `gcp-rd-research-dev`
- **BQ Schema**: `finops_cloud_billing`
- **Bootstrap scripts**: `dbt/scripts/create_superset_*.sh`
- **Design docs**: `dbt/design/superset-*.md` (must stay in sync)
- **Existing dashboards**: see `dbt/design/` for the inventory