Reference Queries are pre-defined SQL queries that teach camelAI how to calculate your business metrics correctly. They serve as building blocks that camelAI can reference, adapt, and combine to answer complex questions about your data.

​
What are Reference Queries?

Reference Queries are SQL queries stored in a vectorized database that camelAI searches when processing user questions. Unlike the Knowledge Base which stores textual context, Reference Queries provide actual SQL implementations of your business logic.

​
Why Use Reference Queries?

Reference Queries help camelAI:

Standardize Metrics

Ensure consistent metric calculations across all analyses

Navigate Schema

Handle complex schema relationships through example joins

Replicate Logic

Maintain consistency with existing dashboard logic

Business Rules

Handle intricate business rules that require specific SQL patterns
  • Accelerate query generation by providing proven templates

​
When to Use Reference Queries vs Knowledge Base

Use Reference Queries for:
  • Complex SQL logic with multiple joins
  • Specific metric calculations
  • Dashboard query replication
  • Table relationship examples

​
Best Practices

​
1. Use SQL Comments for Context

Comments in your SQL queries are included when we vectorize them, improving search accuracy: 
-- Title: "Monthly Recurring Revenue (MRR)"
SELECT 
    DATE_TRUNC('month', subscription_date) as month,
    -- Convert all subscription types to monthly equivalent
    SUM(CASE 
        WHEN billing_period = 'monthly' THEN amount
        WHEN billing_period = 'yearly' THEN amount / 12
    END) as mrr
FROM subscriptions
WHERE status = 'active'  -- Only active subscriptions count toward MRR
GROUP BY 1;
The comments help camelAI understand not just what the query does, but why certain logic is applied.

​
2. Replicate Dashboard Queries

If your users already have dashboards, add those queries as references to ensure consistency: 
-- Title: "Customer Churn Rate"
SELECT 
    COUNT(CASE WHEN last_order < CURRENT_DATE - 90 THEN 1 END) / 
    COUNT(*) * 100 as churn_rate
FROM customers;

​
3. Document Complex Joins

Show camelAI how your tables relate through example queries: 
-- Title: "Customer Order Summary"
SELECT c.email, COUNT(o.order_id) as order_count, SUM(o.total) as lifetime_value
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.status = 'completed'
GROUP BY c.email;

​
4. Include Business Logic

Capture complex business rules in your reference queries: 
-- Title: "Active Users"
-- Active = logged in within 30 days AND has valid subscription
SELECT COUNT(DISTINCT user_id) as active_users
FROM users
WHERE last_login >= CURRENT_DATE - 30
  AND subscription_status = 'active';

​
5. Title your Reference Queries

Titles of queries are optional fields, but we strongly recommend creating a descriptive, to the point title. It strongly improves RAG performance.
Use language your users will use to ask questions.
Example: If your dashboard shows “Customer Acquisition Cost”, title your reference query exactly that way, not “CAC Calculation” or “Marketing Efficiency Query”. Good Titles:
  • “Weekly Active Users”
  • “Customer Churn Rate”
  • “Average Order Value by Region”
  • “Product Performance Metrics”
Poor Titles:
  • “Query_1”
  • “complicated_join_v2”
  • “SELECT statement for users”

​
Implementation Strategy

1

Start with Core Metrics

Add queries for your 5-10 most important KPIs
2

Add Dashboard Queries

Include all queries powering existing dashboards
3

Document Relationships

Add example joins between commonly used tables
4

Include Edge Cases

Add queries handling special business logic
5

Iterate Based on Usage

Monitor what users ask and add missing patterns

​
Maintenance and Optimization

Version Control

Track changes to critical metric definitions

Performance Test

Ensure reference queries run efficiently

Regular Review

Update queries as schema evolves
  • User Feedback: Add queries for commonly requested analyses
  • Clean Up: Remove outdated or unused queries periodically

​
Next Steps

Start small with your most important metrics, then expand based on user needs.
  1. Identify your most important business metrics
  2. Extract SQL from existing dashboards and reports
  3. Create reference queries with descriptive titles
  4. Test by asking camelAI questions about those metrics
  5. Refine based on accuracy and performance
For general context and definitions, see our Knowledge Base Guide.