query-writing

Mozilla BigQuery Query Writing

For table selection and aggregation hierarchy, see data-catalog.md. For query templates and best practices, see query-writing.md. For data platform architecture, see architecture.md. For external sources (Metric Hub, Confluence, UDF discovery), see external-sources.md.

Guardrails

• Use "clients" or "profiles" not "users" — BigQuery tracks client_id, not actual users
• Do not suggest joining across products by client_id — each product has its own namespace
• Always check for aggregate tables before suggesting raw tables

Workflow

1. Identify query type (user counts, specific metric, events, search)
2. For standard metrics (DAU, MAU, retention, etc.), look up the authoritative definition and SQL via Metric Hub MCP (get_metric_sql) if available. For broader context on metric calculation logic, check Confluence via Atlassian MCP. If neither is available, use the templates in this plugin's knowledge files.
3. Select optimal table using the aggregation hierarchy in knowledge/data-catalog.md
4. Add required filters per knowledge/query-writing.md
5. Write the query following templates in knowledge/query-writing.md
6. If BigQuery MCP tools are available (mcp__bigquery__*), offer to execute the query directly:
   • mcp__bigquery__execute_sql to run queries
   • mcp__bigquery__get_table_info to inspect schemas
   • mcp__bigquery__list_dataset_ids / mcp__bigquery__list_table_ids to explore data
   • Always include partition filters and sample_id in executed queries