stmo-cli

CLI for managing queries and dashboards on Mozilla's Redash instance (sql.telemetry.mozilla.org).

Command reference

Before anything else, run stmo-cli --help. It prints LLM-optimized, version-matched output — every command, flag, required YAML field, the slug-derivation rule, enum/date syntax gotchas, and dynamic date tokens — automatically inside this environment (CLAUDECODE is set). Treat that output as the source of truth for the command surface; the sections below are workflow guidance it doesn't cover.

Prerequisites

Before running any stmo-cli command (except init and update), verify REDASH_API_KEY is set. If missing, every command fails immediately with:

Error: REDASH_API_KEY environment variable not set

To get the key, go to https://sql.telemetry.mozilla.org/users/me (API Key section), copy the key, and export it:

export REDASH_API_KEY=your_key_here

If stmo-cli is not available, install it via ./mach bootstrap.

Working directory

stmo-cli creates queries/ and dashboards/ relative to the current directory. Never run file-creating commands from the Firefox repo tree. Always use the artifacts/stmo/ directory, which is already VCS-ignored:

mkdir -p artifacts/stmo
cd artifacts/stmo

Queries created during exploration are ephemeral: fetch → execute → archive → done.

The temp directory is not a git repo, so stmo-cli deploy (which uses git diff to detect changes) won't work — always use stmo-cli deploy --all instead.

Data exploration workflow

1. Find data sources — stmo-cli data-sources (inspect one with --schema)

2. Discover existing queries — stmo-cli discover (full-text search across queries and dashboards with --search)

3. Fetch and read an existing query

   stmo-cli fetch <id>
   # reads: queries/<id>-*.sql and queries/<id>-*.yaml
   

4. Execute — stmo-cli execute <id>; see --help for output format, parameters, interactive prompting, and multi-value enum syntax.

5. Clean up newly created queries

   If you created a new query (the id: 0 → deploy flow) and it's not worth keeping, archive it — throwaway queries clutter the Redash account:

   stmo-cli archive <id>
   

   If the query is useful or you want to share it with others, leave it in Redash instead. The same applies to dashboards created during exploration — archive with stmo-cli dashboards archive <slug> if throwaway, leave if worth sharing.

   Do not archive queries or dashboards you only fetched to read — that would delete them from Redash.

   To restore an archived query:

   stmo-cli unarchive <id>
   stmo-cli fetch <id>
   

Quick one-off SQL (no tracked query)

For a single throwaway question, skip the create/deploy/archive cycle above entirely and run SQL directly against a data source:

stmo-cli data-sources                                   # find the data source ID
echo 'SELECT 1' | stmo-cli execute --data-source <id>   # SQL via stdin
stmo-cli execute --data-source <id> --file scratch.sql  # or from a file

This creates no queries/ file and needs no archive afterwards. It has no parameter schema, so d_* dynamic date tokens and multi-value enum expansion don't apply — inline concrete values directly in the SQL (e.g. IN ('release', 'beta')). --data-source can't be combined with a query ID. Reach for the tracked-query workflow above instead when you want to iterate, save, schedule, or share the query.

Scheduling refreshes

stmo-cli schedule <id> --interval SECS [--time HH:MM] [--day-of-week N] sets a query's refresh cadence; stmo-cli schedule <id> --clear removes it. This only updates the local YAML — run stmo-cli deploy afterwards to push the change to Redash.

Bootstrap context from existing queries

Before answering a new data question, fetch the user's existing queries to understand what tables, patterns, and SQL style they already use:

stmo-cli fetch --all

Then read the downloaded .sql files to learn which tables are queried, how filters are structured, and what metrics are already tracked. This makes new queries fit naturally into the user's existing work.

Beyond Redash: export and analyze

When Redash isn't sufficient — complex statistics, rich visualizations, or analysis over large result sets — export the raw data and analyze it locally:

stmo-cli execute <id> --format json --limit 10000 2>/dev/null > data.json

From there:

• DuckDB or SQLite for SQL-based analysis over the exported data
• Python + pandas/scipy/numpy for real statistics (mean/median alone is almost always wrong)
• Apache Echarts for rich interactive charts in HTML/JS that handle large datasets well
• Jinja2 for templating if generating reports

A static website updated via cron (behind SSO) is a proven pattern for sharing results within Mozilla — see the App Engine static site with IAP runbook.

SQL style

STMO queries run on BigQuery. Use BigQuery SQL syntax: backtick-quoted identifiers, DATE_ADD(date, INTERVAL N DAY), FORMAT_DATE, APPROX_COUNT_DISTINCT, etc.

Dynamic date tokens

Tracked queries (queries deployed with a real ID, not ephemeral ones) can use d_* parameter tokens — e.g. d_today, d_last_7_days — that stmo-cli resolves client-side before execution. See stmo-cli --help for the full token list.

mozdata integration

Use the mozdata MCP tools (mozdata:probe-discovery, mozdata:query-writing) to find the right telemetry probes, metrics, and table schemas. Then use stmo-cli to write, deploy, and execute the actual Redash queries.

Query management

Create a new query:

1. Create queries/0-{slug}.sql with the SQL

2. Create queries/0-{slug}.yaml with metadata — see stmo-cli --help for the required fields and the slug-derivation rule (the {slug} in both filenames must match it):

   id: 0
   name: My Query Name
   data_source_id: <id from stmo-cli data-sources>
   options:
     parameters: []
   visualizations: []
   

   Both options (with parameters) and visualizations are required even when empty.

   Do not add a default Table visualization — Redash creates one automatically for every new query.

3. For enum parameters, use YAML multiline format (see --help for the exact syntax rule — escaped newlines are not valid):

   options:
     parameters:
     - name: normalized_channels
       title: normalized_channels
       type: enum
       value:
       - release
       enumOptions: |-
         nightly
         aurora
         beta
         release
         esr
       multiValuesOptions:
         prefix: ''''
         suffix: ''''
         separator: ','
   

4. Deploy:

   stmo-cli deploy --all
   

5. Sync the server-assigned ID:

   stmo-cli fetch <new-id>  # renames local files to {new-id}-{slug}.*
   

Dashboard management

Dashboards are addressed by slug, not ID. See stmo-cli --help for the full dashboards subcommand list (discover, fetch, deploy, archive, unarchive).

Create: dashboards/0-{slug}.yaml with id: 0, deploy, file auto-renames with real ID.

Query snippets

Redash query snippets are reusable SQL fragments (e.g. a shared set of CTEs) that get pasted into a query's editor by typing their trigger. Pasting is a one-time text expansion, not a live reference — editing a snippet later does not update queries that already pasted it; re-paste (or hand-edit) each affected query after changing a snippet it's used in.

Snippet files are keyed by trigger, not slug: snippets/{id}-{trigger}.sql (the fragment body) + snippets/{id}-{trigger}.yaml (metadata: id, trigger, description).

The .sql file is not standalone SQL — it's a bare list of CTEs (no leading WITH, no trailing SELECT) meant to be pasted into a larger query, so it can't be run or linted on its own.

Create a new snippet: create snippets/0-{trigger}.sql + snippets/0-{trigger}.yaml with id: 0, then stmo-cli snippets deploy 0 — same auto-rename-to-real-ID pattern as queries and dashboards.

Snippets have no archive concept in Redash: stmo-cli snippets delete <ids> removes the snippet on the server and deletes the local files in one irreversible step — there's no unarchive equivalent. See stmo-cli --help for the full snippets subcommand list (list, fetch, deploy, delete).

File format

queries/{id}-{slug}.sql       # SQL text
queries/{id}-{slug}.yaml      # metadata: name, data_source_id, options, visualizations
dashboards/{id}-{slug}.yaml
snippets/{id}-{trigger}.sql   # fragment body (not standalone SQL — see "Query snippets" above)
snippets/{id}-{trigger}.yaml  # metadata: id, trigger, description

New queries/dashboards/snippets use id: 0 in the filename until deployed.