# Models

A model is a query that processes data from sources, applies transformations, and outputs structured datasets.

<figure><img src="/files/jGUbeQ6kGUfVDXHilio5" alt=""><figcaption></figcaption></figure>

Models are the fundamental building blocks in the data modeling module, with each model representing a discrete unit of transformation. This approach shifts development from monolithic SQL scripts toward a more modular, maintainable structure that supports incremental development.

ReOrc currently supports writing models in SQL.&#x20;

As an example, the following snippet showcases the basic syntax of SQL models:

* At its core, models are regular queries to data sources.
* Transformation is finalized with a single SELECT statement.
* Jinja is integrated to make references and enhance logic. See: [Jinja templating](/data-modeling/jinja-templating.md).

```sql
-- Select the total number of orders per customer
select
    customers.customer_id,
    customers.first_name,
    customers.last_name,
    count(orders.order_id) as total_orders
from
    {{ source('jaffle_shop', 'raw_customers') }} as customers
join
    {{ source('jaffle_shop', 'raw_orders') }} as orders
on
    customers.customer_id = orders.customer_id
group by
    customers.customer_id,
    customers.first_name,
    customers.last_name
order by
    total_orders desc
```

When ReOrc runs your SQL models, it goes through a process of compiling them into executable SQL queries. This means parsing all Jinja expressions, resolving model and source dependencies based on `ref()` and `source()` calls, and combining with configurations (like materialization types) to produce the final SQL.

Implementing data transformation logic as models provides several benefits:

* Simplicity: Complex transformation can be broken down into smaller, manageable steps.
* Modularity and reusability: Some transformations can be extracted into foundational models that are referenced in multiple places. This allows you to build transformation incrementally, rather than start from scratch.
* Data lineage and transparency:  Linkage between models are automatically tracked and presented in data lineage. This helps you understand the dependencies between models and make debugging easier. See: [Data lineage](/data-modeling/data-lineage.md).
* Testing and validation: You can write tests and apply them to each model to ensure data quality. A model is run together with its tests to ensure data issues are caught early. See: [Data tests](/data-modeling/data-tests.md).

## Create a SQL model

To create a SQL model, follow these steps:

1. In the **Models** tab, click on the **+** icon and select **New SQL model**.
2. Provide a name for your model and click **Create**.

   The created model will be organized in the **models** folder.
3. In the model editor, input your SQL query.&#x20;

<figure><img src="/files/tAC7rGIb0VLSbFvVBcLG" alt=""><figcaption></figcaption></figure>

For example:&#x20;

```sql
-- select customers from raw_customers table
select 
        id as customer_id
        name as customer_name
from {{ source('jaffle_shop', 'raw_customers') }}
```

4. Click **Save** to confirm the changes.
5. Click **Preview** to view the query output in the **Result** tab.

<figure><img src="/files/dH9Ct0cCd2PhLM6dObRs" alt=""><figcaption></figcaption></figure>

6. To inspect the compiled code of your model, click on the **Compiled code** tab.

## Reference a model

Referencing one model in another allows you to build on existing transformations without rewriting. This modular approach streamlines your transformations, making the logic more maintainable and readable.

Within a model, you can reference another model using the `ref()` function. This function establishes dependencies between models, ensuring that the models are built in the correct order.

Here's an example:

```sql
-- model: stg_orders
select
    id as order_id,
    user_id as customer_id,
    order_date,
    status,
    amount
from {{ source('jaffle_shop', 'orders') }}
```

```sql
-- model: mart_daily_revenue

select
    date_trunc('day', order_date) as date,
    count(*) as number_of_orders,
    sum(amount) as daily_revenue
from {{ ref('stg_orders') }}
where status = 'completed'
group by 1
order by 1 desc
```

In  `mart_daily_revenu`, the `ref('stg_orders')` function references `stg_oders` as a dependency. This reference allows one model to use the results of another, and also ensures their order when building models.

## Organize models

As data transformation can follow different strategies and go through several stages, you can organize the models into folders that reflect the transformation stages and help maintain a clean project structure.

To create a new folder, click on the action button of an existing folder and select **Add sub-folder**:

<figure><img src="/files/mANwz2RPEkuuUmrQlODe" alt=""><figcaption></figcaption></figure>

To move a model into the new folder, click on the action buton of the model and select **Move**. Then select the target folder from the list.

<figure><img src="/files/QZMn5sP7K3Z1GVG28Aeu" alt=""><figcaption></figcaption></figure>


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.reorc.com/data-modeling/models.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.