Query

mcs query workflow

Hard rule

Never run mcs build or mcs package propose while answering a query. mcs build is maintenance / onboarding work and must be run only when the user explicitly asks to build, refresh, onboard, or maintain a profile.

Profile resolution

All mcs commands auto-resolve the active profile via --profile → MCS_PROFILE → cwd-link → env-var fallback. Do not pass identity flags unless you need to override. Do not cd before running mcs; cwd-link binding is keyed on the starting directory.

Use mcs -f json ... whenever the next step depends on parsing output.

Read-only by default

mcs sql execute and mcs sql submit are read-only by default. The same guard is enforced by the MaxCompute client APIs unless a managed write path sets allow_write=True. They refuse INSERT/UPDATE/DELETE/MERGE/CREATE/DROP/ ALTER/TRUNCATE/GRANT/REVOKE, session mutations such as SET, and any statement sqlglot can't classify as a known read shape before the cost gate, returning a WriteOpRejected envelope (exit code 2). Pass --allow-write only when the user explicitly asked for a write; it confirms write intent but does not skip the cost gate. For UDF lifecycle use mcs udf *; for profile rebuilds use mcs build.

Workflow

1. Understand the question: metrics, fields, time range, filters, candidate tables.
2. Try package-backed schema:

``bash mcs -f json show mcs -f json show --table T mcs -f json show --tables T1,T2,T3 ``

1. If no semantic package exists, use live metadata:

``bash mcs -f json meta list-tables mcs -f json meta search-tables KEYWORD mcs -f json meta search-columns KEYWORD mcs -f json meta describe-table TABLE ` See references/cold-start.md`.

1. Probe ambiguous low-cardinality literal values before final SQL. See

references/value-discovery.md.

1. Compose SQL using references/rules.md, references/sql.md,

references/projection.md, and references/from-table.md (FROM-table choice + join cardinality / COUNT(DISTINCT) discipline). Before deriving an aggregation from scratch, check for a named metric — the user may have vetted the math once already: ``bash mcs -f json metric list mcs -f json metric show ` If one matches, copy its expression` verbatim instead of reimplementing it (same name should mean the same number). Then run the SQL correctness checklist below before finalizing the SELECT.

1. Review SQL:

``bash mcs -f json sql review '' ` Fix every error-severity issue. If the envelope has reviewmode: syntaxonly and semanticchecksskipped: true, the profile has no package; fix syntax / dialect / tier issues, ignore missing semantic hints, and continue with cold-start metadata. Do not run mcs build` to make review more complete.

1. Cost-gate real table scans:

``bash mcs -f json sql cost '' ` Read the JSON verdict; the command exits 0 even on blocked`.

1. Choose the execution mode from the cost verdict and query shape:

• verdict=blocked: do not run; explain the cost and add a tighter

partition filter, predicate, or preview LIMIT.

• verdict=confirm: ask the user. After confirmation, prefer async

submit / wait / result and pass -y so the confirmed query does not stop at the non-TTY cost prompt.

• verdict=ok (or cost skipped because the SQL is clearly tiny): use

synchronous execute only for probes and small-result queries: SELECT 1, schema/value probes, explicit small LIMIT previews, or tightly partition-filtered lookups/aggregations expected to finish in the current turn.

• Use async for final analytical scans, joins, or aggregations over real

tables; SQL without a tight partition/filter/LIMIT; any query after a prior timeout; or any query the user says can run in the background. Synchronous path: ``bash mcs -f json sql execute '' ` execute waits --timeout seconds (default 30). If it exceeds that wait it does **not** fail: the instance keeps running, so it returns data.synctimedout: true with data.instanceid (+ data.logviewurl / data.nextstep). Continue with the async path below using that instanceid — do **not** resubmit the SQL. Async path: `bash mcs -f json sql submit -y '' mcs -f json sql wait mcs -f json sql result ` If submit returns data.status == "Submitted" with data.statusprobeerror, the SQL was submitted but the immediate status probe failed. Keep data.instanceid and use sql status / sql wait later. Read data.lifecyclestate, data.terminal, data.successful, and data.taskstatuses[].statusname; do not decide from raw data.status == "Terminated" because MaxCompute's instance status can be terminated even when the task failed or was cancelled. Call result only after data.lifecyclestate == "success". execute and result cap returned rows at 10000 by default without rewriting SQL. If data.hasmore is true, fetch the next page with --offset ; use --max-rows N` to change page size.

1. If the user confirms correctness, record:

``bash mcs memory verify --question Q --sql '' --tables T1,T2 ``

SQL correctness checklist (inline)

The highest-leverage rules from the references — they apply to almost every query, so they live in the workflow body, not behind --full. Load mcs skill get query --full only when you need the worked examples.

Projection — SELECT only what the question names.

• "which / who / list X" → project the one column naming X (its label or

id). "what is the highest / total / average X" → project the scalar aggregate of X, not the group it falls under.

• Columns used only in WHERE / JOIN / ORDER BY are filter / ranking

signal, not output. Don't project intermediate values either.

• Don't wrap with ROUND / CAST / CONCAT unless the question asked

for that format — it breaks exact result-set comparison.

• One statement per answer: a compound "what is X and list Y" is one

JOIN-ed SELECT, not two ;-separated queries.

FROM — the subject of the question decides the FROM table.

• "how many / what percentage of X" → FROM x_table with

COUNT(x_table.pk); pull filter tables in via JOIN. Don't count from a fan-out child table (it inflates the denominator).

• A filter column living in another table → JOIN to it; don't switch the

FROM to the filter's table.

Join cardinality — read the joins_to [..] markers in mcs show.

• After a [1:n] JOIN where the partner is only in WHERE, use

COUNT(*). Reach for COUNT(DISTINCT pk) ONLY when the question says "distinct / unique / different X" or a partner column is in SELECT. Defensive DISTINCT on every 1:n JOIN changes the answer.

References

• Cold-start query path: references/cold-start.md
• Projection discipline: references/projection.md
• FROM-table choice + join cardinality: references/from-table.md
• Literal value probing: references/value-discovery.md
• MaxCompute rules (syntax, column markers, tier-aware table form): references/rules.md
• Advanced SQL: references/sql.md

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: aliyun
• Source: aliyun/maxcompute-semantic
• License: Apache-2.0
• Homepage: https://aliyun.github.io/maxcompute-semantic/

Install and usage instructions live in the source repository linked above.