---
alwaysApply: false
description: "Graphit: Consult when writing queries. Data source queries (`graphit query --ds`) run DuckDB. Warehouse queries (`graphit query --warehouse`) run Snowflake. You MUST use the correct dialect."
globs: []
---

# SQL Reference

Consult when writing queries. Data source queries (`graphit query --ds`) run DuckDB. Warehouse queries (`graphit query --warehouse`) run Snowflake. You MUST use the correct dialect.

## DuckDB vs Snowflake Translation

| Snowflake | DuckDB equivalent |
|---|---|
| `DATEADD(day, N, date)` | `date + INTERVAL N DAY` |
| `DATEDIFF(day, a, b)` | `DATE_DIFF('day', a, b)` |
| `NVL(a, b)` | `COALESCE(a, b)` |
| `NVL2(a, b, c)` | `CASE WHEN a IS NOT NULL THEN b ELSE c END` |
| `IFF(cond, a, b)` | `CASE WHEN cond THEN a ELSE b END` |
| `TO_CHAR(date, fmt)` | `strftime(date, fmt)` |
| `ARRAY_AGG(x) WITHIN GROUP (ORDER BY y)` | `LIST(x ORDER BY y)` |
| `LISTAGG(col, sep)` | `STRING_AGG(col, sep ORDER BY ...)` |
| `COUNT_IF(cond)` | `COUNT(*) FILTER (WHERE cond)` |
| `GENERATOR(ROWCOUNT => N)` | `generate_series(0, N-1)` |
| `CONVERT_TIMEZONE('tz', ts)` | `timezone('tz', ts)` |

### JSON / VARIANT Access

| Engine | Syntax | Example |
|---|---|---|
| Snowflake | Colon notation | `col:field::STRING`, `col:parent:child::NUMBER` |
| DuckDB | Arrow operators | `col->>'field'` (text), `col->'field'` (JSON) |

NEVER use `->>`  in Snowflake or `:field::STRING` in DuckDB.

### Timezone

| Engine | Correct | Wrong |
|---|---|---|
| DuckDB | `timezone('Asia/Jerusalem', ts_col)` | `AT TIME ZONE` chains (reverses direction on TIMESTAMPTZ) |
| Snowflake | `CONVERT_TIMEZONE('Asia/Jerusalem', ts_col)` (2-arg for _TZ) | `AT TIME ZONE` |

## DuckDB Superpowers (not in Snowflake)

| Feature | Example |
|---|---|
| `GROUP BY ALL` | `SELECT region, SUM(sales) FROM t GROUP BY ALL` |
| `SELECT * EXCLUDE` | `SELECT * EXCLUDE (internal_id) FROM t` |
| `FILTER (WHERE)` | `COUNT(*) FILTER (WHERE status='active')` |
| `UNION BY NAME` | `SELECT ... UNION ALL BY NAME SELECT ...` |

## Snowflake Notes

- Use `DATE_TRUNC('month', date)` for grouping (not EXTRACT/MONTH/YEAR)
- Snowflake does NOT support `FILTER (WHERE)` - use `CASE WHEN` instead
- `COUNT_IF` MUST receive a boolean expression, not a raw INT column. Use `COUNT_IF(is_active = 1)`, not `COUNT_IF(is_active)`
- String matching: prefer `ILIKE` (case-insensitive) over `LIKE`

## SQL Formatting Standards (Both Engines)

- Keywords UPPERCASE: `SELECT`, `FROM`, `WHERE`, `JOIN`, `GROUP BY`, `ORDER BY`
- Table/column names UPPERCASE: `ORDERS`, `CUSTOMER_ID`, `TOTAL_REVENUE`
- Qualify every column with its table alias: `o.ORDER_DATE`, not bare `ORDER_DATE`
- Use descriptive aliases: `total_revenue`, not `sum1`
- String literals in single quotes: `'active'`, `'2024-01-01'`
- Non-ASCII identifiers MUST be double-quoted: `SELECT * FROM "hebrew_table"`

## CTE Pattern

Use CTEs for queries with 3+ JOINs or complex subqueries:

```sql
WITH MONTHLY_ORDERS AS (
  SELECT o.CUSTOMER_ID, DATE_TRUNC('month', o.ORDER_DATE) AS MONTH,
         SUM(o.AMOUNT) AS MONTHLY_REVENUE
  FROM ORDERS o WHERE o.STATUS = 'complete'
  GROUP BY o.CUSTOMER_ID, DATE_TRUNC('month', o.ORDER_DATE)
)
SELECT c.SEGMENT, AVG(mo.MONTHLY_REVENUE) AS AVG_MONTHLY_REVENUE
FROM MONTHLY_ORDERS mo JOIN CUSTOMERS c ON mo.CUSTOMER_ID = c.ID
GROUP BY c.SEGMENT ORDER BY AVG_MONTHLY_REVENUE DESC
```

## ORDER BY Rules (Always Include)

| Query type | ORDER BY |
|---|---|
| Time series | `ORDER BY date_column ASC` |
| Category breakdowns | `ORDER BY metric_column DESC` |
| Rankings / top N | `ORDER BY metric_column DESC LIMIT N` |

Do NOT add LIMIT unless the user requests it or the query is a ranking.

## Gap-Filling Pattern (DuckDB)

For heatmaps or continuous time-series needing every cell (even zeros):

```sql
WITH grid AS (
  SELECT UNNEST(generate_series(0, 23)) AS hour_of_day
)
SELECT g.hour_of_day, COALESCE(t.count, 0) AS count
FROM grid g
LEFT JOIN (SELECT hour, COUNT(*) AS count FROM data_source GROUP BY 1) t
  ON g.hour_of_day = t.hour
ORDER BY 1
```

Date series:
```sql
SELECT UNNEST(generate_series(
  CURRENT_DATE - INTERVAL 30 DAY, CURRENT_DATE, INTERVAL 1 DAY
))::DATE AS date
```

## Subquery Column Scope

The outer SELECT can ONLY reference columns the subquery exposes (its aliases). Base-table columns aggregated away in the subquery do NOT exist at the outer level.

## Cache-Friendly SQL (Canvas Resolve)

Canvas `graphit.resolve()` queries that follow these shapes serve from a semantic cache in ~10ms on filter changes instead of a full DuckDB recompute (5-37s on wide data sources). Write resolve SQL in this style by default.

**Shape rules:**
- Single table (no JOIN/UNION)
- WHERE as flat AND of `column = literal`, `column IN (...)`, `column BETWEEN ... AND ...` conjuncts
- Bare aggregates only: `SUM(col)`, `COUNT(*)`, `MIN(col)`, `MAX(col)` - no wrapping functions (`ROUND(SUM(x))`), no aggregate arithmetic (`SUM(a)/NULLIF(SUM(b),0)`), no `AVG` (v2)
- Literal dates (`>= '2026-01-01'`), never `CURRENT_DATE` expressions
- GROUP BY column names or ordinals; ORDER BY / LIMIT allowed (outer only)
- CTEs are fine when the CTE body follows the same rules
- Top-N rank queries: project the sort metric in SELECT (`SELECT dim, SUM(metric) AS rv ... ORDER BY rv DESC LIMIT N`), not only in ORDER BY

**Shapes that skip the cache** (fall back to normal execution, still correct):
- `COUNT(DISTINCT x)`, window functions, HAVING, QUALIFY
- OR / NOT in WHERE
- Ratio metrics (`SUM(a)/NULLIF(SUM(b),0)`) - compute client-side or use two resolves. To display as a percent, multiply by 100 (`* 100.0 ... AS x_pct`): the `"percent"` format only appends `%`, it does not scale (a 0-1 ratio would show as `0.42%`, not `42%`).
- `CURRENT_DATE`-relative predicates
- Top-N with aggregate only in ORDER BY (`SELECT dim FROM t GROUP BY dim ORDER BY SUM(metric) DESC LIMIT N` - no decomposable aggregate in SELECT)

## Data Source Routing

| Situation | Command | Speed |
|---|---|---|
| Table has a cached data source | `graphit query "SQL" --ds <id>` | ~100ms, DuckDB |
| No data source | `graphit query "SQL" --warehouse --connection <id>` | ~10s, Snowflake |

Always prefer cached data sources. Check with `graphit ds list`. If no data source covers the table, suggest creating one for future speed.

## Presenting Query Results

Ground every query in the KB. When using `{{metric:X}}`/`{{dim:X}}` references, show five sections:
1. **KB Assets:** list all referenced metrics, dimensions, tables as bold names
2. **Query:** original SQL with `{{metric:X}}` references in ```sql block
3. **Resolved SQL:** expanded SQL (always use `--verbose`) in ```sql block
4. **Results:** markdown table with right-aligned numbers, row count, DS ID
5. **Governance:** tier, KB refs count, rules enforced (**bold names**), max rows

For inline SQL (no KB refs): show query + results + governance, and suggest the KB reference equivalent to nudge toward governed tier.

On the CLI, every ad-hoc query needs a substantive `--adhoc-reason` when no KB definition fits. An ad-hoc **business measure** (aggregate / `GROUP BY` without `{{metric:X}}`) is hard-blocked when the caller lacks EXPLORE access to any queried scope; a reason never bypasses that access denial.

## Presenting Data Source Results

After `graphit ds list`: table with **bold Name**, ID, Rows (right-aligned), Status, Row cap. End with DS recommendation for current task.