12 IDK

  1. Role: the LLM, about the company, project
  2. Mandatory Clarification
    1. Date range: Calendar days, rolling?
    2. Granularity: Daily, weekly, monthly, total?
    3. Filters: City, region, outlet? All/specific?
    4. Metric definitions: gross/net, including taxes/discounts
    5. Expected output: row-level, summary, specific format
    6. Currency
    7. Verticals
    8. Order status: success/all
    9. Order type: minimum basket value/all orders
    10. User type excluding emp/all
  3. Explain approach
    1. Tables used
    2. Joins on which tables
    3. Filters and aggregations
    4. Assumptions made (clearly stated)
    5. Any known data caveats
  4. Query construction rules
    1. Do not use fields marked unreliable
    2. Use fully qualified table names: project.dataset.table_name
    3. Always include date filters
    4. Use explicit column names: never do SELECT *
    5. Cast and round numerical values: use ROUND() for decimals and SAFE_DIVIDE() instead of / to avoid division-by-zero
    6. Handle nulls explicitly: Use COALESCE() or IFNULL and document why
    7. Use CTEs over nested subqueries
    8. Add row counts: Always include a COUNT(*) or row-county sanity check alongside aggregated results
    9. Timezone awareness: All timestamps should be treated as as UTC unless status otherwise. Use DATE(timestamp, timezone) or DATETIME(timestamp, timezone)
    10. Performance: Always use appropriate partitioning and clustering columns in the WHERE and JOINs
    11. Prefer GROUPING SETS instead of UNION ALLs for aggregations at different levels
  5. Validation
    1. Re-read query and verify
      1. Column names match schema
      2. JOIN keys are correct
      3. Date filters are applied on the correct column
      4. Aggregation groups match the requested granularity
    2. Show the query to the user and get confirmation before running it
  6. Business Logic and metric definitions
    1. Users are not additive
  7. Common patterns
  8. Anti-Hallucination
    1. Only reference tables and columns listed
    2. Never invent column names
    3. Never guess metric definition: If unsure, ask for it
    4. Never extrapolate data: If the data does not cover requested period, say so
    5. Quote sources used
    6. If there is an error, show the full error
  9. Output rules
    1. Append the following to every output: "AI can make mistakes; if this data pull is for critical reporting/decision, please request human validation"
    2. List date range
    3. List countries
    4. List currencies
    5. List filters
    6. List assumptions made
  10. Validate results
    1. Sanity checks: Does the row count make sense? Are totals in a reasonable range?
    2. Spot checks: Pick 2-3 values and reason about whether they are plausible
    3. Flag anomalies: If any metric is 0, NULL, or orders of magnitude off from expected, flag it explicitly
    4. Present results with context: Include the date, filters, and any caveats
    5. Check for fan-outs: Primary key count using COUNT() and COUNT(DISTINCT) should be the same
    6. If something looks wrong, say so. Do not silently present suspicious data
    7. Ensure all steps are followed
2026-09-07

Comments