# SQL Model Spec

`model_spec` defines SQL queries, how the table is created, and any [`ids`]({{< ref "profiles/dev-docs/inputs-yaml/identifiers.md" >}}) and [`entities`]({{< ref "profiles/dev-docs/pb-project-yaml/entities.md" >}}) for the table.

```yaml
model_spec:
    materialization:
        output_type: table or ephemeral     # Optional
        run_type: discrete       # Optional
    single_sql: sql_query       # single_sql or multi_sql required
    occurred_at_col: timestamp  # Optional
    ids:               # Optional
        - select: column
          type: id_type
          entity: entity
```

| Key | Description |
| :------------- | :----------- |
| `output_type` | Output type - accepted values are `table` or `ephemeral`. |
| `run_type` | Run type - accepted value is `discrete`. |
| `single_sql` <br/> <span style="color: #4D4DFF;font-size:12px;">Required</span> | Evaluates a single SQL statement. |
| `multi_sql` <br/> <span style="color: #4D4DFF;font-size:12px;">Required</span>  | Evaluates multiple SQL statements. Requires one state to be a `CREATE` statement to materialize the model into a table. |
| [`ids`]({{< ref "profiles/dev-docs/inputs-yaml/identifiers.md" >}}) | Define the ID fields, `select`, `id_types`, and related `entity` parameters. |

## Syntax

- Begin `single_sql` or `multi_sql` with `|` to write SQL queries over multiple lines.
- To import a SQL file, use `{% exec %} {{this.ReadFile("models/sql1.sql")}} {% endexec %}` with the `single_sql` key.
- To include `inputs` defined in `inputs.yaml`, assign the inputs to a placeholder in a `{$ with %}` statement and using `this.DeRef("models/input_name")`. For example: `{% with input1 = this.DeRef("models/input_name") %}`
- To reference in the SQL, surround with double curly brackets: `{{ input1 }}`.

## Example

```yaml
# basic example
model_spec:
    single_sql: |
        select * from table

# import file saved in models folder
model_spec:
    single_sql: |
        {% exec %} {{this.ReadFile("models/sql_file.sql")}} {% endexec %}

# multiple sql queries
model_spec:
    multi_sql: |
        with ids (
        select distinct id from IDS)

        create table events as 
        select * from events where id not in (select * from ids)

# add ids
model_spec:
    single_sql: |
        {% exec %} {{this.ReadFile("models/sql_file.sql")}} {% endexec %}
    ids:
        - select: lower(email_address)
          type: email
          entity: user

# use Profiles input source
model_spec:
    single_sql: |
        {% with input_table = this.DeRef("inputs/rsIDS) %}
          select 
            id,
            first,
            last
          from
            {{input_table}}
          where
            id_type = 'email'
        {% endwith %}
```