gen-metrics

Generate Metrics Skill

Guide the user through metric generation using natural language business descriptions.

Phase 0: SQL Modeling Preflight

For requests that directly contain SQL or explicitly name a readable workspace SQL file, follow the required sql-modeling-preflight skill and call prepare_sql_modeling_plan before other artifact reads or any writes. Reading an explicitly named SQL file is allowed before preflight: the parent may read it and pass its contents, or this agent may call read_file; then copy every complete SQL statement from that content into the tool call. Existing-YAML maintenance and natural-language-only authoring skip this tool call. For SQL-backed requests, use the returned existing metric catalog and authoritative candidate plan throughout the remaining phases to:

• Skip redundant work — don't recreate metrics that already exist. "Already exists" requires the same aggregation AND the same window/offset semantics: a cumulative/window/period-over-period variant (e.g. running_x, moving_n_x, previous_period_x) is a new metric even when its base metric x is already published
• Reuse existing measures — reference measures from existing models instead of creating duplicates
• Detect conflicts — warn the user if a proposed metric name collides with an existing one
• Enable derived/ratio metrics — know which metrics can serve as building blocks for more complex definitions

Only inspect and edit semantic model YAML files under the current datasource directory shown in the system prompt, such as subject/semantic_models/<current_datasource>/.... Do not reuse or sync YAML files from sibling datasource directories; those files are outside the active MetricFlow adapter scope.

Phase 1: Understand Intent

Analyze the user's request and confirm the generation scope before proceeding. When ask_user is available, call it to confirm the metric name(s), business meaning, and calculation logic. When ask_user is not available (for example workflow or batch mode), infer from the provided SQL/request and stop only if the scope is materially ambiguous.

Input Mode Detection

• Single mode: User describes one metric or provides one SQL → follow Step 1a–1d below
• Batch mode: The current task directly contains multiple SQL queries or explicitly names a readable workspace SQL file → follow Step 1-batch below

Single Mode: Step 1a–1d

Step 1a: Inspect the table — Call describe_table(table_name) to understand the columns and types. Optionally call execute_sql(sql="SELECT * FROM <table> LIMIT 5") to sample data.

Step 1b: Ask for reference SQL (optional) — When ask_user is available, use it to ask:

"Do you have any existing SQL queries for this table that show the aggregations you care about? You can paste them here, or skip if not available."

When ask_user is not available, skip this question and infer SQL/aggregation context from the user's request, attached files, or discovered query/table evidence. If that is not enough, stop and explain the missing information instead of calling ask_user.

If the user provides SQL, use only the completed preflight's candidate_plan, output_contracts, and queryability_contracts to identify:

• Final business output expressions (e.g., SUM(amount) / COUNT(DISTINCT user_id) AS arppu → candidate metric arppu)
• Aggregation functions + columns that the final metric depends on (e.g., SUM(amount) → candidate measure total_amount, COUNT(*) → candidate measure record_count)
• GROUP BY columns → recommended dimensions
• WHERE conditions → potential metric constraints

If the provided SQL contains no metric-producing output, keep filter-only or detail-query evidence as filters, dimensions, segments, or view evidence instead of generating fake metrics.

If the user skips, proceed to Step 1c using only table structure and the user's description.

Step 1c: Propose metric candidates — Based on the table structure, reference SQL (if provided), and user's request, identify potential metric scenarios. See "Metric type detection rules" below.

Step 1d: Confirm scope — when ask_user is available, call it to confirm and present proposed metrics with multi_select: true (see Step 1-batch-d for format). If ask_user is not available, proceed with the confirmed/inferred scope from the input.

Batch Mode: Step 1-batch

Step 1-batch-a: Collect SQL queries

• SQL statements may be pasted directly or stored in a workspace SQL file explicitly named by the user.
• For a named workspace SQL file, the parent may read it and pass its contents, or this agent may call read_file on the preserved path before preflight.
• Copy every complete SQL statement from the request or read_file result verbatim into the preflight tool. Prefer one call; use finalize=false batches only when the input is large, then finalize the last batch. Do not rewrite, normalize, or split a statement across batches.
• Call describe_table for each unique table found in the SQL queries

Step 1-batch-b: Mine metric candidates from SQL ASTs

Use the candidate_plan returned by prepare_sql_modeling_plan to preserve final business metric expressions and their dependencies:

1. Preserve final output metrics — SQL aliases and final SELECT expressions are the primary metric candidates.
2. Keep base measures as dependencies — base measures support the final metric but do not replace it.
3. Reuse only outside the output contract — reusable base measures and catalog evidence may be deduplicated, but never merge two explicit metric_requirements merely because their aliases or normalized expressions match.
4. Separate non-metrics — filter-only/detail SQL belongs in non_metric_evidence, not metric YAML.
5. Respect output-level lowering — metric_requirements is the final-output completeness contract. Keep requirements distinct by output_id, even when aliases repeat, and process each target_mode independently. A statement may contain both direct_metric and query_backed_metric requirements: lower the direct outputs normally and follow each query-backed requirement's dataset_requirement_id to its authored dataset. Exact SQL matching and injection are owned by the dataset tool, so do not reconstruct SQL. Use the actual authored semantic name rather than dataset_name_hint, and do not generate direct physical-table metrics from intermediate CTE aggregates. Query-level classifications and recommendations are explanatory summaries; they must not replace the output-level contract.
6. Choose business-safe names — if a candidate has requires_name_translation: true, treat name as a technical fallback only. Also inspect every source_alias: when the alias appears generated or lacks business meaning, do not use it as the final MetricFlow name. In interactive mode, ask the user to confirm if the business meaning is unclear; in batch/bootstrap mode, infer a clear English snake_case name from the SQL expression, question, and table/column context without stopping.
7. Preserve SQL literal values — if literal_mappings is present, keep the literal value exactly as it appears in SQL predicates/CASE/sql_query output. Only MetricFlow object names may be translated or normalized.
8. Preserve SQL time grain — if time_grain_evidence is present, expose an equivalent time dimension in any derived data source. Do not replace a projected date such as CURDATE() AS part_dt or DATE(create_time) AS part_dt with raw create_time as the primary time dimension. Define type: TIME only for physical DATE/TIME/TIMESTAMP columns or SQL expressions / sql_query aliases guaranteed to return DATE/TIME/TIMESTAMP values. Numeric surrogate keys such as *_date_sk, *_date_key, *_dt_key, or integer YYYYMMDD keys must be identifiers or categorical dimensions unless converted to a real date.
9. Preserve post-aggregation constraints — if post_aggregation_constraints is present, keep each HAVING/post-aggregation condition as a query constraint, metric usage note, or later derived data source. Do not silently drop it or push it into a base measure.
10. Cross-reference with Phase 0 — remove any candidate that already exists in the knowledge base.
11. Separate fixed comparison and derived metrics — treat direct_metric_candidates with metric_type: period_over_period as fixed final period-over-period metrics. Treat derived_metric_candidates only as second-stage metrics over existing metrics. Do not mix true derived metrics into base semantic model or measure generation.
12. Ignore passthrough references — entries in identity_metric_references show existing metrics selected without new business formula; do not generate new metrics for them.
13. Do not promote support measures — a SELECT projection that only supports another final KPI, such as a denominator, row count, or intermediate aggregation, may be added as a semantic-model measure. Do not also wrap it as a top-level business metric unless the user question or candidate plan identifies it as a final KPI.
14. Respect support_measure_candidates — these are dependency or comparison measures, not direct metrics. You may add them to a semantic model only if a generated metric needs them, but do not publish a metric: block for them.
15. Review llm_review_candidates critically — these are possible metric evidence, not confirmed metrics. For each item, decide whether the row-level or ambiguous expression should be lifted into a reusable MetricFlow metric. Generate it only when the SQL, table/column semantics, and user question support that lift; otherwise record it as query-only/detail evidence and do not create a metric.
16. Do not assume lifted equivalence — for candidates with equivalence: "lifted" or requires_validation: true, do not assume exact equivalence to the historical SQL. Prefer a semantically correct aggregate metric such as SUM(numerator) / SUM(denominator) only when that lift is business-valid; otherwise skip the metric.
17. One metric per required output identity — outside metric_requirements, equivalent candidate aliases may resolve to one business metric. Inside the output contract, publish one uniquely named metric per output_id; do not collapse requirements across SQL statements.
18. Names/questions are naming evidence only — natural-language fields in the candidate plan (question, name, summary, or source_context) never override the SQL expression structure or turn a detail query into a metric by themselves.
19. Preserve source SQL byte-for-byte in query-backed sources — do not reconstruct source SQL from the candidate plan. Follow the active authoring skill's requirement-reference flow so the tool layer inserts the exact request-local SQL. Normalize only authored object names.
20. Cover every required output — every metric_requirements[].output_id must end as a generated metric or a concrete blocker. For a generated run, pass a typed array of {output_id, metric_name} objects as metric_output_bindings to publish_metrics, and return the same array in final JSON. Counts in tests are derived from these contracts; never hardcode a benchmark-wide expected count in production code.
21. Do not reparse for queryability — consume queryability_contracts from the same preflight plan. publish_metrics owns compile validation and warehouse dry-runs for the complete required grouping combinations.

Step 1-batch-c: Business metric principle

From N SQL queries, propose a focused set of business metrics. Ask yourself for each candidate:

• Is this a final output a business user would recognize as a KPI?
• Are its base measures complete enough to validate and dry-run?
• Should the evidence be a metric, or only a filter/dimension/segment/view definition?
• Is this alias only a supporting count/sum used by another final output? If yes, create or reuse the measure but do not publish a separate metric for it.
• Does the tool say the metric depends on a ranked/windowed CTE or other derived data source? If yes, generate the derived data source first instead of forcing a direct metric.
• Are SQL literals, output time grain, and HAVING/post-aggregation constraints preserved from the tool evidence?

Step 1-batch-d: Confirm with the user when possible

• When ask_user is available, present the mined business metric candidates as options with multi_select: true
• Pass questions as an actual array argument, not a JSON string. Example tool arguments:

  {
    "questions": [
      {
        "title": "Metrics",
        "question": "I analyzed N SQL queries and identified the following metric candidates. Select which ones to generate:",
        "options": ["paid_arppu - SUM(paid_amount) / COUNT(DISTINCT user_id)", "gross_margin_rate - (SUM(revenue) - SUM(cost)) / SUM(revenue)"],
        "multi_select": true
      }
    ]
  }
  

• Clearly show how many SQL queries were analyzed, how many metric candidates were extracted, and which candidates were skipped as non-metric evidence.
• When ask_user is not available, proceed with the mined metrics only if the input makes the scope unambiguous; otherwise stop and explain what needs to be provided.

Metric type detection rules

1. Simple counting + filter: "How many completed orders" → conditional measure in the semantic model + measure_proxy metric referencing that measure by string
2. Aggregation + filter: "Total revenue from premium customers" → conditional measure in the semantic model + measure_proxy metric referencing that measure by string
3. Ratio: "Order completion rate", "Refund rate", "Revenue share", "Revenue per user" → ratio type
4. Expression: "Gross profit", "Gross margin rate" → expr type combining measures
5. Derived: "ROAS over existing revenue and ad_spend metrics" → derived type combining metrics
6. Cumulative: "Running total of revenue", "MTD sales", "Year-to-date signups" → cumulative type

Detection keywords:

• "running total", "MTD", "YTD", "cumulative", "to-date" → cumulative
• "rate", "ratio", "percentage of", "share of" → ratio
• "per", "divided by", "average ... per" → ratio or expr depending on expression shape
• "list all...", "show me the..." → not a metric, better suited for gen_sql

IMPORTANT: Do NOT proceed to Phase 2 with materially ambiguous scope. Use ask_user when available; otherwise stop and explain what information is needed.

Phase 2: Ensure Semantic Model Exists

For each table involved in the metric:

2a. Check Existing Model

1. Call check_semantic_object_exists(name="{table_name}", kind="table") to check if a semantic model exists.
2. If the semantic model exists:
   • Use read_file to read the existing semantic model YAML
   • Verify that it contains the measures and dimensions needed for this metric
   • If missing measures/dimensions, use edit_file to add them, then validate_semantic

2b. Create Missing Model

If the semantic model is missing, follow the metricflow-semantic-authoring workflow when that skill is available. In brief: use preflight semantic_source_evidence; only if it is partial or missing required tables, call inspect_semantic_sources once with all remaining physical tables. Use the combined schemas, request-SQL field usage, and relationship candidates to write semantic model YAML under the directory shown in the system prompt, then run validate_semantic and fix issues until it passes before continuing.

2c. Multi-Table / JOIN SQL Modeling

When the metric involves multiple tables (detected from JOIN in SQL or user description), choose the modeling strategy based on SQL complexity:

Strategy A: Identifier-based JOIN (default — use when possible)

Use when: simple equi-JOIN between 2-3 tables via foreign keys, ≤ 2 JOIN hops.

• Each table gets its own data_source with sql_table
• Tables are linked via matching identifiers (same name, one PRIMARY, one FOREIGN)
• Use inspect_semantic_sources.relationships to set up correct identifier linkages
• Example: orders.customer_id (FOREIGN) links to customers.customer_id (PRIMARY) — both identifiers share name: customer
• MetricFlow engine automatically resolves the JOIN path at query time

Strategy B: sql_query pre-joined data source (complex cases)

Use when: non-equi JOINs, > 2 hop joins, subqueries, LATERAL/CROSS joins, complex ON conditions, or window functions in the JOIN.

• Create a single data_source with sql_query containing the pre-joined SQL
• Flatten the result: measures and dimensions reference the output columns directly
• Example:

  data_source:
    name: order_customer_summary
    sql_query: |
      SELECT o.order_id, o.amount, o.order_date,
             c.name as customer_name, c.segment
      FROM schema.orders o
      JOIN schema.customers c ON o.customer_id = c.id
    measures:
      - name: total_revenue
        agg: SUM
        expr: amount
    dimensions:
      - name: customer_name
        type: CATEGORICAL
      - name: order_date
        type: TIME
        type_params:
          is_primary: true
          time_granularity: DAY
  

• Trade-off: dimensions from the pre-joined query are NOT reusable by other data sources (no identifier linkage). Only use this when Strategy A cannot handle the complexity.

Decision rule: Default to Strategy A. Use it when the join can be represented as identifier-level keys (single-column or derived expressions). Use Strategy B for composite multi-column equi-joins unless they are represented in source SQL as a derived key expression, and for non-equi conditions, 3+ hop joins, or subquery-based logic.

Phase 3: Generate and Validate

File paths: All write_file / edit_file / read_file calls use paths relative to the filesystem sandbox root. Always use the semantic model directory shown in the system prompt so subsequent reads find the file. For example:

• Semantic model: subject/semantic_models/<current_datasource>/{table_name}.yml
• Metric file: subject/semantic_models/<current_datasource>/metrics/{table_name}_metrics.yml

Bare filenames are silently normalized by the host, but the prefixed form is preferred for clarity. Absolute paths are also tolerated. Do not read, edit, or pass metric_file / semantic_model_files paths from another datasource directory such as subject/semantic_models/other_datasource/....

1. Check existing: Call check_semantic_object_exists(name="{metric_name}", kind="metric") for each metric confirmed in Phase 1. If it already exists, inform the user and skip it.

2. Write metric YAML: Use write_file to save each metric definition to subject/semantic_models/<current_datasource>/metrics/{table_name}_metrics.yml.

   • For measure_proxy, keep type_params.measure as a string measure name.
   • For filtered metrics, add a dedicated conditional measure to the semantic model first, then reference that measure from the metric YAML.
   • Each generated metric must be an explicit named top-level metric: YAML document. Do not emit unnamed metric: blocks or wrap metrics inside another object.

3. Validate (MUST PASS): Call validate_semantic to check the metric YAML.

   • If validation fails, fix errors with edit_file and retry until it passes.
   • Do NOT proceed to Phase 4 until validation passes. No exceptions.

Phase 4: Batch Sync to Knowledge Base

After all generated metrics have passed validation:

• You MUST call publish_metrics(metric_file, metric_output_bindings) ONCE to sync them to Knowledge Base while you can still fix publish errors. Omit metric_output_bindings only when the preflight plan has no metric_requirements.
• publish_metrics deterministically resolves every source SQL GROUP BY contract to generated dimensions, compiles the corresponding metric query, and checks the compiled SQL with a warehouse dry-run before syncing. Fix the semantic model or metric and retry if it reports a queryability or warehouse error.
• Do not rely on the final JSON host fallback. The host fallback is only a last-resort guard when the tool call was accidentally missed.
• If no metrics were generated, do NOT call publish_metrics

Phase 1 confirms the generation scope; validation plus the publish-time queryability checks are the acceptance gate before syncing.

Common Pitfalls (MUST avoid)

1. Explicit metric files: Write explicit metric YAML files under the semantic model directory's metrics/ subdirectory instead of relying on create_metric: true. Runtime-generated metrics are not part of the persisted metric catalog.

2. Metric name must match measure name: For a measure_proxy metric, the metric name should typically equal the measure name (or be a clear derivative). The type_params.measure must exactly match a measure name from the semantic model. Do NOT invent unrelated names (e.g., measure activity_count → metric name should be activity_count, NOT total_activity_count or activity_count_metric).

3. Filtered metrics: Model reusable filter logic as a conditional measure in the semantic model, such as expr: "CASE WHEN status = 'completed' THEN 1 ELSE 0 END" with agg: SUM, then write type_params.measure: completed_order_count in the metric YAML.

4. Check before creating: ALWAYS call check_semantic_object_exists(name="{metric_name}", kind="metric") before writing a new metric. If the metric already exists, skip it.

5. Verify names after validation: Bind each output ID to the exact metric name authored in the validated YAML when calling publish_metrics.

6. Every metric needs explicit YAML: Whether it's a simple aggregation, filtered variant, ratio, expr, derived, or cumulative — write a metric: entry in the metrics YAML file so it can be persisted and discovered later.

7. Derived metrics are second-stage: Generate non-derived metrics first, including fixed period_over_period direct candidates, and validate them. Generate derived_metric_candidates only when every referenced metric exists in the preflight catalog or was generated earlier in the same run.

8. Support measures are not always metrics: Add support measures needed for ratios, expressions, filters, and validation, but do not publish each support measure as a separate metric unless it is itself a requested/final business KPI.

MetricFlow Metric Structure Reference

measure_proxy (simple aggregation):

metric:
  name: {metric_name}
  description: "{description}"
  type: measure_proxy
  type_params:
    measure: {measure_name}
  locked_metadata:
    tags:
      - "{category}"
      - "subject_tree: {domain}/{layer1}/{layer2}"

For a filtered metric, define a dedicated conditional measure in the semantic model and keep the metric's type_params.measure as a string:

data_source:
  name: orders
  measures:
    - name: completed_order_count
      description: "Completed order count"
      agg: SUM
      expr: "CASE WHEN status = 'completed' THEN 1 ELSE 0 END"
---
metric:
  name: completed_order_count
  description: "Completed order count"
  type: measure_proxy
  type_params:
    measure: completed_order_count

ratio (ratio of two measures):

metric:
  name: {metric_name}
  description: "{description}"
  type: ratio
  type_params:
    numerator: {measure_or_metric_name}
    denominator: {measure_or_metric_name}
  locked_metadata:
    tags:
      - "subject_tree: {domain}/{layer1}/{layer2}"

expr (expression combining measures):

metric:
  name: {metric_name}
  description: "{description}"
  type: expr
  type_params:
    measures:
      - measure_a
      - measure_b
    expr: "{expression}"  # e.g. "(measure_a - measure_b) / measure_a"
  locked_metadata:
    tags:
      - "subject_tree: {domain}/{layer1}/{layer2}"

derived (expression combining existing metrics):

metric:
  name: {metric_name}
  description: "{description}"
  type: derived
  type_params:
    metrics:
      - name: metric_a
        # Optional: period-over-period comparison
        alias: metric_a_prev
        offset_window: 1 week    # compare to 1 week ago (WoW)
      - name: metric_b
        offset_to_grain: month   # compare to start of current month (MTD)
    expr: "{expression}"  # e.g. "metric_a / metric_a_prev"
  locked_metadata:
    tags:
      - "subject_tree: {domain}/{layer1}/{layer2}"

Period-over-period example — a MoM SQL whose final output is metric_a_mom_delta should publish a fixed MoM delta metric, not a query-time compare instruction and not a previous-value helper unless that helper is itself the final requested output:

metric:
  name: metric_a_mom_delta
  description: "{metric_a month-over-month delta description}"
  type: derived
  type_params:
    metrics:
      - name: metric_a
      - name: metric_a
        alias: metric_a_prev
        offset_window: 1 month
    expr: "metric_a - metric_a_prev"

cumulative (running total over time):

metric:
  name: {metric_name}
  description: "{description}"
  type: cumulative
  type_params:
    measure: {measure_name}
    # Use ONE of:
    window: {time_window}        # rolling window, e.g. "7 days", "1 month"
    grain_to_date: month|year    # MTD/YTD - resets at grain boundary
  locked_metadata:
    tags:
      - "subject_tree: {domain}/{layer1}/{layer2}"

Ordinary period-over-period SQL (LAG, previous period, DoD/WoW/MoM/QoQ/YoY, delta, or rate) is fixed long-term metric evidence when it is a final business output: monthly YoY is distinct from weekly YoY, MoM rate is distinct from MoM delta, and previous-period value is distinct from a rate. Publish a previous-period metric only when it is itself the requested final output.

Important Rules

• Phase 1: Confirm which metrics to generate before proceeding. Use ask_user when it is available.
• Validation MUST pass — always call validate_semantic and ensure it passes before proceeding to the next phase. If it fails, fix and retry until it passes.
• Sync automatically after validation — call publish_metrics without another user confirmation. It owns compile and warehouse dry-runs; the final JSON metric_file is only a last-resort fallback.
• COUNT agg must use expr: "1" — never use expr: {column} with COUNT (use COUNT_DISTINCT for that).
• For ratio metrics, both numerator and denominator measures must exist in the semantic model.
• For expr metrics, all referenced measures must exist in the semantic model.
• For derived metrics, all referenced metrics must already be defined, the expression must not be a single metric passthrough, and the dependency graph must not contain cycles.
• For cumulative metrics, the measure must exist and a primary time dimension must be defined.
• Use consistent naming: metric names in snake_case, measure names matching the semantic model.
• Every metric data_source needs a primary time dimension when a reliable DATE/TIME/TIMESTAMP column or expression exists. Do not force a primary TIME dimension from numeric surrogate keys; join/convert to a real date first.
• Measure names must be globally unique across all data sources.
• For snapshot/balance data, always add non_additive_dimension to prevent incorrect time aggregation.
• Keep files scoped — only write semantic model YAML and metric YAML files. Sync metrics through publish_metrics; the final JSON metric_file is only a last-resort fallback.