# SQL Transform Syntax: SELECT, Incremental Models, and Custom SQL in Starlake
> Write SQL transforms in Starlake using standard SELECT statements. Configure incremental models with sl_start_date/sl_end_date, document calculated columns, and use custom MERGE/INSERT with parseSQL: false.
# SQL Transforms
Starlake SQL transforms let you materialize any SELECT statement into a target table using a configurable write strategy. This page covers SELECT syntax, column documentation with `attributesDesc`, custom MERGE/INSERT via `parseSQL: false`, and incremental modelling with the `sl_start_date` / `sl_end_date` environment variables. All SQL is standard -- no Jinja templating is involved.
:::info Variable substitution syntax
The `{{variable}}` syntax used in SQL files (e.g. `{{sl_start_date}}`) is Starlake's environment variable substitution, not Jinja templating. These variables are resolved at runtime before the SQL is sent to the database engine. You can use any environment variable with this syntax.
:::
## SELECT Statement Materialization
A [SQL transform](./tutorial#write-the-sql-select-statements) is a SELECT statement that Starlake materializes into a target table using the [write strategy](../load/write-strategies) defined in the companion YAML file. The SELECT can be as simple or as complex as needed -- single table queries, multi-table joins, subqueries, and window functions all work.
**Schema matching rules:**
- If the target table exists, the SELECT result must match its schema.
- If the target table does not exist, Starlake infers the schema (column names and types) from the SELECT result.
- Column documentation from source tables propagates automatically to the target.
## Document Calculated Columns
Starlake infers column metadata from source tables. For calculated columns that do not exist in any source, add documentation using `attributesDesc` in the YAML configuration:
```yaml title="metadata/transform//.sl.yml"
task:
...
attributesDesc:
- name:
comment:
...
```
This metadata appears in the data catalog and helps downstream consumers understand derived fields.
## Custom Write Strategy with parseSQL
If none of the [predefined write strategies](../load/write-strategies) fits your use case, write your own MERGE, INSERT, or UPDATE statement directly in the SQL file. Set `parseSQL` to `false` so Starlake executes the SQL as-is instead of wrapping the SELECT in a materialization statement:
```yaml title="metadata/transform//.sl.yml"
task:
...
parseSQL: false
...
```
With `parseSQL: false`, you have full control over the DML statement. Starlake does not modify or wrap the SQL.
## Incremental Modelling
When data arrives periodically, you often need to process only the new data for each interval. Starlake supports incremental transforms through two reserved environment variables:
- **`sl_start_date`** -- start of the current processing interval
- **`sl_end_date`** -- end of the current processing interval
Use these variables in a WHERE clause to filter input data:
```sql title="metadata/transform//daily_sales.sql"
SELECT
product_id,
SUM(amount) AS daily_revenue,
COUNT(*) AS transaction_count
FROM
sales.transactions
WHERE
transaction_date >= '{{sl_start_date}}'
AND transaction_date < '{{sl_end_date}}'
GROUP BY
product_id
```
The orchestrator (Airflow, Dagster, or Snowflake Tasks) passes the correct values for each execution interval. The computed results append to the existing table, which greatly reduces the volume of data processed on each run.
### Handle Missed Intervals with Catchup
If an incremental job fails for a specific day, Starlake integrates with Airflow's **Catchup** mechanism. Add the `catchup` flag to your YAML DAG definition, and the orchestrator replays missed intervals with the correct `sl_start_date` and `sl_end_date` values.
## Next Steps
- [SQL Transform Tutorial](./tutorial) -- end-to-end walkthrough with DuckDB
- [Transform YAML Configuration](./config) -- write strategies, partitioning, access control
- [Python Transforms](./python) -- use PySpark for complex logic beyond SQL
## Frequently Asked Questions
### Does the SELECT result have to match the target table schema?
If the target table already exists, the SELECT result must match its schema. Otherwise, Starlake automatically infers the schema from the result.
### How do you document calculated columns in a SQL transform?
Use the `attributesDesc` property in the transform YAML file to add a name and comment for each calculated column.
### Can you write custom MERGE or INSERT instead of a SELECT?
Yes. Set `parseSQL: false` in the YAML configuration. Starlake executes the SQL as-is without converting it into a write statement.
### What are the sl_start_date and sl_end_date variables?
They are reserved environment variables representing the current time interval. The orchestrator (Airflow, Dagster, or Snowflake Tasks) passes them automatically for incremental executions.
### What happens if an incremental job fails for a day?
Starlake integrates with Airflow's Catchup mechanism. Missed intervals are automatically replayed with the correct `sl_start_date` and `sl_end_date` values.
### Does Starlake support Jinja templating in SQL files?
No. Starlake SQL files use standard SQL. Environment variables are injected directly without a Jinja layer.
### How does schema inference work when the target table does not exist?
Starlake executes the SELECT and infers the schema (types, column names) from the result. Source column documentation is automatically propagated.