Skip to main content

Manual SQL Mode

Write SQL directly when you want full control—optional for advanced users.

What is Manual SQL Mode?​

Manual SQL Mode lets you write raw SQL queries yourself instead of asking the AI. It's an advanced feature for users who know SQL and want precise control over query logic. You can create Views manually, bypassing natural language entirely.

Manual SQL Editor

SQL code editor for manual query writing

Why Manual SQL Mode Matters​

Precision: Write exactly the SQL you want, no interpretation.

Advanced features: Use SQL techniques the AI might not choose automatically.

Learning: Compare your SQL to what the AI generates.

Speed (for experts): If you know SQL well, typing a query can be faster than describing it.

Complex logic: Handle edge cases or specialized queries that are hard to describe.

When to Use Manual SQL​

Good use cases:

  • You're a SQL expert and know exactly what you want
  • The query requires advanced SQL features (CTEs, window functions, etc.)
  • You want to optimize performance manually
  • The AI didn't understand your natural language request
  • You're porting existing queries from another tool

When to stick with AI:

  • You don't know SQL well
  • The request is straightforward
  • You want to learn how to write the query
  • You prefer transparency (AI-generated Views include explanations)

Most users never need Manual SQL Mode. The AI handles 95% of cases perfectly.

How to Use Manual SQL Mode​

Creating a New View with SQL​

  1. Click the + button next to Views in the sidebar
  2. Select "Write SQL manually"
  3. The SQL editor opens
  4. Write your query
  5. Name your View
  6. Click "Create"

Your manually created View appears as a node in the graph, just like AI-generated Views.

Creating Manual View

+ button to create manual SQL View

SQL Editor Features​

Smart code editor:

  • Syntax highlighting
  • Auto-complete for table and column names
  • Error detection
  • Line numbers
  • Multi-line editing

Available objects:

  • Reference Sources by name
  • Reference other Views by name
  • Use @mention syntax: SELECT * FROM @[orders]

Standard SQL:

  • Full SQL syntax support
  • JOINs, subqueries, CTEs
  • Window functions
  • Aggregations and GROUP BY
  • All standard SQL features

SQL Editor Interface

Code editor with syntax highlighting and autocomplete

Referencing Other Objects​

Use Shadowfax's @mention syntax in your SQL:

SELECT
  c.customer_name,
  COUNT(o.order_id) as order_count,
  SUM(o.total_amount) as total_revenue
FROM @[customers] c
JOIN @[orders] o ON c.id = o.customer_id
GROUP BY c.customer_name
ORDER BY total_revenue DESC

This ensures your manual Views integrate with the reactive system.

Example Queries​

Basic SELECT with Filter​

SELECT *
FROM @[orders]
WHERE order_date >= '2024-01-01'
  AND status = 'completed'

Join Multiple Tables​

SELECT
  p.product_name,
  c.category_name,
  SUM(o.quantity) as total_sold,
  SUM(o.revenue) as total_revenue
FROM @[order_items] o
JOIN @[products] p ON o.product_id = p.id
JOIN @[categories] c ON p.category_id = c.id
GROUP BY p.product_name, c.category_name

Window Function (Ranking)​

SELECT
  customer_id,
  order_date,
  total_amount,
  RANK() OVER (PARTITION BY customer_id ORDER BY total_amount DESC) as order_rank
FROM @[orders]

Common Table Expression (CTE)​

WITH monthly_revenue AS (
  SELECT
    DATE_TRUNC('month', order_date) as month,
    SUM(total_amount) as revenue
  FROM @[orders]
  GROUP BY month
)
SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) as prev_month_revenue,
  revenue - LAG(revenue) OVER (ORDER BY month) as growth
FROM monthly_revenue

Example Queries

Common SQL patterns in manual mode

Manual Views in the Reactive System​

Manual Views participate fully in reactivity:

  • Upstream changes: If a Source or View you reference changes, your manual View recalculates
  • Downstream impact: Other Views depending on your manual View update when you edit it
  • Same as AI Views: No functional difference once created

Editing Existing Manual Views​

  1. Click the View node you created manually
  2. Toggle to the "Code" tab
  3. Click "Edit" button
  4. Modify your SQL
  5. Save changes

Changes trigger downstream recalculation just like AI edits.

Comparing AI vs. Manual SQL​

AI-Generated View​

Process: "Show monthly revenue growth rate"

Result: AI creates Views with:

  • Descriptive names
  • Explanations of logic
  • Classifications (Aggregate, Calculate, etc.)
  • Step-by-step breakdown

Manual View​

Process: Write this query:

SELECT
  month,
  revenue,
  (revenue - LAG(revenue) OVER (ORDER BY month)) / LAG(revenue) OVER (ORDER BY month) as growth_rate
FROM @[monthly_revenue]

Result: View works identically, but:

  • No auto-generated explanation
  • You provide the name
  • Classification might be generic

Both produce identical data and integrate into the pipeline the same way.

Tips & Best Practices​

Reference with @mentions: Always use @[object_name] to reference Sources and Views for proper integration.

Test incrementally: Build complex queries step-by-step, testing each part.

Name clearly: Use descriptive View names since you won't get AI-generated descriptions.

Format for readability: Use proper indentation and line breaks.

Add comments: Document your logic with SQL comments:

-- Filter to active customers from 2024
SELECT * FROM @[customers]
WHERE status = 'active' AND join_date >= '2024-01-01'

Check View dependencies: Remember what downstream Views depend on your manual View before making breaking changes.

Learn from AI: Look at AI-generated SQL to see alternative approaches.

When AI is Better​

Even SQL experts often prefer AI because:

  • Explanations: AI documents why it wrote the query that way
  • Breakdowns: Complex logic split into understandable steps
  • Classifications: Views categorized by operation type
  • Error handling: AI catches common mistakes
  • Iterations: Easier to modify via chat than editing SQL

Manual mode is power-user territory—use it when you genuinely need that control.

Troubleshooting​

Syntax errors?

  • Check that object names use @mention syntax
  • Verify SQL syntax is correct
  • Look for missing commas, parentheses, or quotes

View not updating?

  • Ensure you referenced upstream objects correctly
  • Check that upstream Views completed successfully
  • Verify the SQL executes without errors

Performance issues?

  • Check for Cartesian joins (missing join conditions)
  • Avoid SELECT * when you don't need all columns
  • Use filters early to reduce data volume