Skip to main content

Query Console

CLOUD

Translate natural language questions into SQL queries and execute them safely using glossary definitions, asset schemas, and metric SQL definitions.

Overview​

The Query Console translates natural language questions into SQL queries using:

  • Glossary definitions (terms, metrics, dimensions)
  • Asset schemas and metadata
  • Metric SQL definitions
  • Context from project metadata

Executing a Query​

Endpoint​

POST /api/msh/ai/query

Translate NL to SQL and execute query.

Request:

curl -X POST \
  -H "Authorization: Bearer $API_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "Show revenue by region for the last 3 months",
    "project_id": 1,
    "warehouse_connection_id": 1,
    "limit": 100
  }' \
  https://api.msh.io/api/msh/ai/query

Response:

{
  "success": true,
  "sql": "SELECT region, SUM(amount) as revenue FROM {{ ref('revenue') }} WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 3 MONTH) GROUP BY region",
  "explanation": "This query calculates total revenue grouped by region for the last 3 months using the revenue asset",
  "assets_used": ["revenue"],
  "metrics_used": ["metric.revenue"],
  "results": [
    {"region": "North", "revenue": 150000},
    {"region": "South", "revenue": 120000}
  ],
  "row_count": 2,
  "execution_time_ms": 245
}

Query History​

Get Query History​

Endpoint: GET /api/msh/ai/projects/{project_id}/queries

Retrieve query history for a project.

Request:

curl -X GET \
  -H "Authorization: Bearer $API_TOKEN" \
  https://api.msh.io/api/msh/ai/projects/1/queries

Response:

{
  "success": true,
  "queries": [
    {
      "id": 1,
      "sql": "SELECT region, SUM(amount)...",
      "executed_at": "2024-01-15T10:30:00Z",
      "execution_time_ms": 245,
      "row_count": 2
    }
  ],
  "count": 1
}

Example Queries​

Revenue Queries​

# "Show total revenue for last month"
# "What is the revenue by region?"
# "Calculate monthly recurring revenue"

Customer Queries​

# "How many customers do we have?"
# "Show customer lifetime value"
# "What is the customer retention rate?"

Time-Based Queries​

# "Show revenue trends over the last 6 months"
# "What was revenue last quarter?"
# "Compare this month to last month"

Safety Features​

The Query Console includes multiple safety features:

Read-Only Execution​

  • Blocks DROP, DELETE, TRUNCATE operations
  • Only allows SELECT queries
  • Validates queries before execution

Query Validation​

  • Validates SQL syntax
  • Checks for dangerous operations
  • Verifies asset references

Result Limits​

  • Limits result size (default: 1000 rows)
  • Limits execution time (default: 30 seconds)
  • Prevents resource exhaustion

Execution Limits​

  • Row count limits
  • Execution time limits
  • Memory limits

Best Practices​

  1. Use Business Terms: Use glossary terms in queries
  2. Be Specific: Provide clear, specific queries
  3. Review SQL: Always review generated SQL before execution
  4. Check History: Review query history for patterns
  5. Use Limits: Specify result limits for large queries