SQL Editor

The SQL Editor is the primary interface for writing and testing model queries in SignalSmith. It provides a full-featured editing experience with autocomplete, syntax highlighting, query preview, and validation.

Editor Features

Syntax Highlighting

The editor provides syntax highlighting for SQL keywords, strings, numbers, comments, and identifiers. It automatically detects the SQL dialect based on the selected source:

• Snowflake SQL — Snowflake-specific functions and syntax
• BigQuery SQL — BigQuery Standard SQL syntax
• Databricks SQL — Spark SQL / Databricks SQL syntax

Autocomplete

The editor offers intelligent autocomplete suggestions as you type:

• SQL keywords — SELECT, FROM, WHERE, JOIN, GROUP BY, etc.
• Table names — Tables and views available in the source schema
• Column names — Columns from tables referenced in the FROM and JOIN clauses
• Functions — Built-in SQL functions for the source’s dialect (e.g., COALESCE, DATE_TRUNC, CONCAT)
• Aliases — Table aliases defined earlier in the query

To trigger autocomplete manually, press Ctrl+Space (or Cmd+Space on macOS).

Line Numbers and Formatting

• Line numbers — Displayed in the gutter for easy reference, especially when debugging SQL errors
• Indentation — Auto-indentation for SQL blocks (subqueries, CTEs, CASE expressions)
• Bracket matching — Highlights matching parentheses and brackets

Keyboard Shortcuts

Query Preview

The Preview button executes your SQL query and displays the results in a tabular format below the editor.

How Preview Works

1. SignalSmith takes your SQL query and wraps it with a LIMIT clause (typically 100 rows) to return results quickly
2. The query is executed against your source warehouse using the configured credentials
3. Results are displayed in a scrollable table with column headers
4. Column data types are inferred from the result set

Preview Metadata

Along with the results, the preview shows:

• Column count — Number of columns in the result set
• Row count — Number of preview rows returned (up to the limit)
• Execution time — How long the query took to run
• Data types — The detected type of each column (text, number, boolean, date)

Preview Limitations

• Preview is limited to 100 rows by default
• Binary and blob columns are not rendered in the preview
• Very wide result sets (50+ columns) may require horizontal scrolling
• Queries that take longer than 30 seconds may time out

Query Validation

The editor performs several validations on your SQL:

Syntax Validation

Before executing, the editor checks for basic SQL syntax issues:

• Unmatched parentheses or brackets
• Missing FROM clause
• Incomplete JOIN conditions
• Unterminated string literals

Execution Validation

When you click Preview, the query runs against the actual warehouse. If the query fails, the editor displays the error message from the warehouse, including:

• Line number — Where the error occurred (if available)
• Error message — The warehouse-specific error description
• Error code — The warehouse error code

Example error display:

Error at line 5: Column 'emal' not found in table 'customers'.
Did you mean 'email'?

Column Validation

After a successful preview, SignalSmith validates the result set:

• At least one column must be present
• Column names must be unique (use aliases to rename duplicates)
• The query must return at least one row in preview (empty result sets are warned, not blocked)

SQL Dialect Tips

Snowflake

-- Date functions
SELECT DATEADD(day, -30, CURRENT_DATE())
SELECT DATEDIFF(day, start_date, end_date)
SELECT DATE_TRUNC('month', created_at)
 
-- String functions
SELECT CONCAT(first_name, ' ', last_name) AS full_name
SELECT SPLIT_PART(email, '@', 2) AS domain
 
-- Semi-structured data (VARIANT columns)
SELECT raw_data:user.email::STRING AS email
FROM events
 
-- Lateral flatten (unnest arrays)
SELECT e.event_id, f.value::STRING AS tag
FROM events e, LATERAL FLATTEN(input => e.tags) f

BigQuery

-- Date functions
SELECT DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
SELECT DATE_DIFF(end_date, start_date, DAY)
SELECT DATE_TRUNC(created_at, MONTH)
 
-- String functions
SELECT CONCAT(first_name, ' ', last_name) AS full_name
SELECT SPLIT(email, '@')[OFFSET(1)] AS domain
 
-- JSON
SELECT JSON_VALUE(raw_data, '$.user.email') AS email
 
-- Unnest arrays
SELECT event_id, tag
FROM events, UNNEST(tags) AS tag

Databricks

-- Date functions
SELECT DATE_SUB(CURRENT_DATE(), 30)
SELECT DATEDIFF(end_date, start_date)
SELECT DATE_TRUNC('MONTH', created_at)
 
-- String functions
SELECT CONCAT(first_name, ' ', last_name) AS full_name
SELECT SPLIT(email, '@')[1] AS domain
 
-- JSON
SELECT get_json_object(raw_data, '$.user.email') AS email
 
-- Explode arrays
SELECT event_id, tag
FROM events LATERAL VIEW EXPLODE(tags) t AS tag

Common Patterns

CTEs for Readability

Break complex queries into logical steps:

WITH base_customers AS (
  SELECT customer_id, email, name, created_at
  FROM customers
  WHERE email IS NOT NULL
),
order_metrics AS (
  SELECT
    customer_id,
    COUNT(*) AS order_count,
    SUM(amount) AS total_spend
  FROM orders
  GROUP BY customer_id
),
final AS (
  SELECT
    bc.customer_id,
    bc.email,
    bc.name,
    COALESCE(om.order_count, 0) AS order_count,
    COALESCE(om.total_spend, 0) AS total_spend
  FROM base_customers bc
  LEFT JOIN order_metrics om ON bc.customer_id = om.customer_id
)
SELECT * FROM final

Window Functions

Rank, partition, and compute running aggregates:

SELECT
  customer_id,
  email,
  order_date,
  amount,
  ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS order_rank,
  SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_total
FROM orders

Conditional Logic

Use CASE expressions to categorize data:

SELECT
  customer_id,
  email,
  lifetime_value,
  CASE
    WHEN lifetime_value >= 10000 THEN 'platinum'
    WHEN lifetime_value >= 5000 THEN 'gold'
    WHEN lifetime_value >= 1000 THEN 'silver'
    ELSE 'bronze'
  END AS tier
FROM customers

Troubleshooting

Next Steps

• Configure columns after writing your query
• Create a sync to activate model data
• Explore field mapping options