# Help: `getting_started` (concept)

# FeatureQL quick reference

FeatureQL is a functional, entity-centric DSL that transpiles to SQL (DuckDB, Trino, BigQuery, DataFusion). Queries are flat lists of named feature definitions. No CTEs, no subqueries, no implicit joins.

## Decision tree: what to load

Pick the first match:

1. **Pure computation** (no tables, no INPUT): write WITH/SELECT. This page is enough.
2. **One entity + table lookups or aggregations**: you need EXTERNAL_COLUMNS, RELATED, possibly TRANSFORM. Load [`getting_started_more`](/llms/getting_started_more.md).
3. **Multiple entities with cross-entity joins**: you need ENTITY, RELATED with VIA, NESTED bindings. Load [`getting_started_more`](/llms/getting_started_more.md) and [`entity_model`](/llms/entity_model.md).
4. **Debugging a failing query**: load [`debugging_workflow`](/llms/debugging_workflow.md).
5. **Looking for a specific function**: run `SHOW SIGNATURES WHERE NAME = 'function_name'` or [`index_functions`](/llms/index_functions.md).

**Rule of thumb:** if your query involves any table data, load [`getting_started_more`](/llms/getting_started_more.md) before writing code. For any non-trivial problem (business question, benchmark, analytics query), follow the structured approach in [`query_methodology`](/llms/query_methodology.md) before writing code.

## 1. Query anatomy

```sql
CONST                          -- compile-time constants (optional)
    MY_CONST := 42,
WITH                           -- feature definitions (only evaluated if referenced in SELECT)
    FEATURE1 := INPUT(BIGINT),
    FEATURE2 := FEATURE1 * 2,
SELECT                         -- features to return
    FEATURE1,
    FEATURE2,
FROM fm.namespace              -- pull persisted features (optional)
FOR                            -- required when any INPUT() exists in the dependency graph
    FEATURE1 := BIND_VALUES(ARRAY[1, 2, 3]),
WHERE FEATURE2 > 2             -- filter rows (optional)
ORDER BY FEATURE2 DESC         -- sort (optional)
LIMIT 10 OFFSET 5              -- pagination (optional)
;
```

### Minimal examples

```sql
-- No INPUT: no FOR needed
WITH X := 1 SELECT X + 2
-- Result: 3

-- With INPUT: FOR is mandatory
WITH
    ID := INPUT(BIGINT),
    DOUBLED := ID * 2,
SELECT ID, DOUBLED
FOR ID := BIND_VALUES(ARRAY[1, 2, 3])
-- Result: (1,2), (2,4), (3,6)
```

More: [`missing_binding`](/llms/missing_binding.md), [`for_clause`](/llms/missing_binding.md).

## 2. Type system (no automatic coercion)

Cast explicitly with `::TYPE` or `CAST(x AS TYPE)`. Mixing types without casting is an error.

**Types:** `BIGINT`, `INT`, `SMALLINT`, `TINYINT` (integers); `DECIMAL` (auto-precision from literals: `1.25` is `DECIMAL(3,2)`); `FLOAT`, `DOUBLE` (use `1.25e0` scientific notation); `VARCHAR` (single quotes); `BOOLEAN`; `TIMESTAMP`, `DATE`, `INTERVAL`; `JSON`; `ARRAY`, `ROW`; `NULL`, `NULL(TYPE)`, `EMPTY`.

**Entity annotations:** `BIGINT#CUSTOMERS` links a type to an entity.

### Critical type traps

```sql
-- WRONG: 1.25 is DECIMAL, not DOUBLE. Mixing families = error.
revenue := price * 1.25

-- RIGHT: use e0 suffix for DOUBLE literals
revenue := price * 1.25e0

-- WRONG: ARRAY with mixed type families
arr := ARRAY[1.25, 1]  -- DECIMAL + BIGINT = error

-- RIGHT: same family
arr := ARRAY[1.25, 1.00]  -- both DECIMAL

-- WRONG: INTERVAL syntax
d := INTERVAL '3' DAY  -- invalid

-- RIGHT: unit inside the quoted string
d := INTERVAL '3 DAY'

-- WRONG: IF/CASE with bare NULL against a typed branch
ts := IF(flag, TIMESTAMP '2024-01-01', NULL)

-- RIGHT: typed NULL
ts := IF(flag, TIMESTAMP '2024-01-01', NULL::TIMESTAMP(3))
```

**Casting:**
```sql
CAST('123' AS BIGINT)      -- standard
'123'::BIGINT              -- shorthand
ID::BIGINT#ORDERS          -- add entity annotation
```

More: [`types`](/llms/types.md), [`type_mismatch`](/llms/type_mismatch.md).

## 3. SQL differences that will bite you

| SQL habit | FeatureQL | Details |
|-----------|-----------|---------|
| `DATE_TRUNC('month', ts)` | `DATE_TRUNC(ts, 'month')` | reversed argument order |
| `DATE_PART('hour', ts)` | `DATE_PART(ts, 'hour')` | reversed argument order |
| `DATE_DIFF('day', start, end)` | `DATE_DIFF(end, start, 'day')` (boundaries) or `DATE_SUBTRACT(end, start, 'day')` (elapsed) | reversed order; not synonyms |
| `CONTAINS(str, substr)` | `POSITION(substr IN str) > 0` | CONTAINS is array-only |
| `EXTRACT(field FROM row)` | `row[field]` | bracket access for ROW fields |
| `CAST(bool AS BIGINT)` | `IF(bool, 1, 0)` | no bool-to-int cast |
| `GROUP BY col` at query end | `SUM(x) GROUP BY key` per feature | GROUP BY is per feature |
| CTEs, subqueries | flat WITH list | no nesting allowed |
| `SELECT DISTINCT` | use `ARRAY_DISTINCT` or `GROUP BY` | no DISTINCT keyword |

Chained comparisons work: `DATE '2024-02-01' <= d < DATE '2024-03-01'`.

More: [`mistakes_sql_gotchas`](/llms/mistakes_sql_gotchas.md), [`mistakes_index`](/llms/mistakes_index.md).

## 3b. Top LLM mistakes (from real error logs)

Quick fixes before deeper tags:

```sql
-- Declare INPUT before FOR; binding does not define the feature
WITH AMOUNT := INPUT(DOUBLE), SELECT SUM(AMOUNT) FOR AMOUNT := BIND_VALUES(ARRAY[100e0, 200e0])

-- TRANSFORM needs ARRAY(ROW(...)); ZIP scalar arrays first
rows := ZIP(ARRAY[1,2,3] AS n), result := rows.TRANSFORM(SELECT n * 2)

-- Outer scalars invisible inside TRANSFORM — CARRY first
filtered := arr.CARRY(WINDOW_END AS end).TRANSFORM(SELECT * WHERE d < end)

-- Persistence namespaces: FM.* only
CREATE FEATURES IN FM.MY_NS AS SELECT ...
```

Full catalog: [`mistakes_index`](/llms/mistakes_index.md), [`mistakes_syntax`](/llms/mistakes_syntax.md).

## 4. Readable syntax

Prefer these forms for clarity:

```sql
-- Operators over function calls
total := a + b                          -- not ADD(a, b)
label := first || ' ' || last          -- not CONCAT(first, ' ', last)

-- SQL CASE over CASE_WHEN for multi-branch
tier := CASE WHEN amount > 1000 THEN 'platinum' ELSE 'standard' END

-- Method chaining over nesting
clean := ' hello '.TRIM().LOWER()      -- not LOWER(TRIM(' hello '))

-- := over AS
amount := 100e0                        -- not 100e0 AS amount
```

Feature names are case-insensitive.

More: [`readable_syntax`](/llms/readable_syntax.md), [`chained_transform`](/llms/chained_transform.md).

## 5. Binding patterns

`FOR` is required when any `INPUT()` exists in the dependency graph of what you SELECT.

```sql
-- Single value
FOR NAME := BIND_VALUE('Alice')

-- Array: declare INPUT in WITH first — FOR only binds, it does not define the feature
WITH ID := INPUT(BIGINT),
SELECT ID FOR ID := BIND_VALUES(ARRAY[1, 3, 5])

-- Multiple inputs paired (no cross product)
FOR (A, B) := BIND_VALUES(ARRAY[ROW(1, 2), ROW(3, 4)])

-- Cartesian product: requires FOR CROSS (plain FOR is rejected)
FOR CROSS
    A := BIND_VALUES(ARRAY[1, 3]),
    B := BIND_VALUES(ARRAY[2, 4])

-- From table columns
FOR (A, B) := BIND_COLUMNS(col1, col2 FROM TABLE(schema.table))

-- NESTED: for child entity resolution in multi-entity queries
FOR
    CUSTOMER_ID := BIND_VALUES(ARRAY[1, 2]),
    NESTED ORDER_ID := BIND_VALUES(ARRAY[101, 102, 103])

-- Batch population: pin a KEYSET / KEYSET_GROUP / direct EXTERNAL|INLINE source
FOR ORDER_ID := @BIND_KEYSET(ORDER_KEYS)
FOR ORDER_ID := @BIND_KEYSET(ORDER_KEYS_GROUP, ROW(ds := TARGET_DATE))
```

More: [`missing_binding`](/llms/missing_binding.md), [`bind_values`](/llms/bind_values.md), [`bind_columns`](/llms/bind_columns.md), [`for_cross`](/llms/missing_binding.md), [`bind_keyset`](/llms/bind_keyset.md).

## 6. Self-service discovery

Before guessing, query the system:

```sql
-- Find function signatures
SHOW SIGNATURES WHERE NAME = 'EXTEND';
SHOW SIGNATURES WHERE NAME LIKE 'ARRAY_%';

-- Browse docs
SHOW DOCS (EXCLUDE (CONTENT)) WHERE CATEGORY='DOC_PAGE' ORDER BY NAME;

-- Find examples
SHOW DOCS WHERE CONTENT LIKE '%EXTEND%' AND CATEGORY = 'CODE_SAMPLE';

-- Find test cases for a function
SHOW TESTS WHERE FUNCTION_NAME = 'RELATED';

-- Explore persisted features
SHOW FEATURES WHERE NAME LIKE 'finance.%';
SHOW CREATE FEATURES FM.TUTORIALS.IMPATIENT.CUSTOMER_NAME;
```

Tag-based help: `help('tag_name')`. Indexes: [`index_concepts`](/llms/index_concepts.md), [`index_functions`](/llms/index_functions.md), [`index_errors`](/llms/mistakes_index.md).

## 7. Debugging methodology

When a query fails:

1. **validate** first: use `POST /validate` (structured=true) or `client.validate(query)`. Check `formatted_featureql` and `output_schema`. If the error has a fix suggestion, apply it.
2. **Read the error code.** Error codes like `UE/...` are searchable: [`index_errors`](/llms/mistakes_index.md).
3. **Simplify.** Remove features from SELECT one by one until the query passes, then add back.
4. **diagnose()** for automated isolation: `client.diagnose(query)` runs features incrementally, stops at the first failure.
5. **Mock external data.** Swap `EXTERNAL_COLUMNS(... FROM TABLE(...))` with `INLINE_COLUMNS(... FROM CSV(...))` to test logic without a database.

More: [`debugging_workflow`](/llms/debugging_workflow.md), [`query_methodology`](/llms/query_methodology.md).

## 8. Persistence

```sql
CREATE FEATURES IN fm.namespace AS SELECT ...;
CREATE OR REPLACE FEATURES IN fm.namespace AS SELECT ...;
CREATE TEMPORARY FEATURES AS SELECT ...;
DROP FEATURES fm.namespace.feature_name;
```

Namespaces: `FROM fm.namespace` in queries. Aliases: `FROM fm.ns1, fm.ns2 AS _ns2`.

Query persisted features:
```sql
WITH
    MY_DERIVED := PERSISTED_FEATURE * 2,
SELECT PERSISTED_FEATURE, MY_DERIVED
FROM FM.MY_NAMESPACE
FOR KEY := BIND_VALUES(ARRAY[1, 2])
```

More: [`create_features`](/llms/create_features.md), [`from_namespace`](/llms/create_features.md), help('persistence').

## 9. Hybrid queries (FeatureQL inside SQL)

When you need SQL-level GROUP BY, BI-tool filters, or legacy joins around FeatureQL:

```sql
WITH data AS FEATUREQL(
    SELECT CUSTOMER_ID, IS_ACTIVE
    FROM FM.MY_NAMESPACE
    FOR CUSTOMER_ID := BIND_COLUMNS(id FROM TABLE(schema.customers))
)
SELECT IS_ACTIVE, COUNT(1) AS n
FROM data
GROUP BY IS_ACTIVE;
```

More: [`hybrid_queries`](/llms/hybrid_queries.md).

## 10. Function quick reference

Use `SHOW SIGNATURES WHERE NAME = 'X'` for full signatures. Most important groups:

**Core:** ENTITY, INPUT, BIND_VALUE, BIND_VALUES, BIND_COLUMNS, RELATED, CAST, NULL, EMPTY
**Conditional:** IF, CASE WHEN...END, COALESCE
**Array of rows:** EXTEND, TRANSFORM, ZIP, ARRAY_MERGE, UNWRAP, UNWRAP_ONE
**Aggregation (with GROUP BY):** SUM, AVG, MIN, MAX, COUNT, COUNT_IF, ARRAY_AGG, ANY_VALUE
**Window:** ROW_NUMBER, RANK, LEAD, LAG, FIRST_VALUE + all aggregates with OVER()
**Array:** ARRAY_LENGTH, ARRAY_SUM, ARRAY_SORT, CONTAINS, FLATTEN, SEQUENCE, SLICE
**String:** CONCAT, LENGTH, LOWER, UPPER, TRIM, SUBSTR, REPLACE, SPLIT, LIKE
**Date:** DATE_ADD, DATE_SUBTRACT, DATE_TRUNC, DATE_FORMAT, DATE_PARSE, EXTRACT_FROM_DATE
**Math:** ROUND, FLOOR, CEIL, ABS, SQRT, LN, LOG, EXP, POW, WIDTH_BUCKET
**JSON:** JSON_PARSE, JSON_EXTRACT, JSON_EXTRACT_SCALAR, JSON_FORMAT
**Data sources:** EXTERNAL_COLUMNS, INLINE_COLUMNS
**Metaprogramming:** CONST, @eval_as_text(), @eval_as_literal(), MACRO, VARIANT

Full catalog: [`index_functions`](/llms/index_functions.md). Per-category: [`index_functions_array`](/llms/index_functions_array.md), [`index_functions_string`](/llms/index_functions_string.md), [`index_functions_date_and_time`](/llms/index_functions_date_and_time.md), etc.

## Concept index (for deeper topics)

**Temporal:** bitemporal, scd, sliding_window, window_functions, year_over_year, seasonality, ...
**Transform:** carry, chained_transform, extend, pivot, related_aggregation, transform_scope, ...
**Array:** array_merge, deduplication, global_aggregate, unnest, ...
**Reuse:** macro, variant, udf, projection, ...
**Cross entity:** composite_key, currency_conversion, dual_fk, hierarchical_rollup, many_to_many, ...
**SQL equivalents:** composability, left_join, self_join, union
**NULL / comparisons:** null_handling (three-valued `=` vs DISPLAYS AS; GROUP BY / DISTINCT / ORDER BY)

Full list: [`index_concepts`](/llms/index_concepts.md).