# Building Intermediate/Marts Models

Developing intermediate and marts models requires understanding table relationships, writing complex joins, applying business logic, and ensuring consistent formatting. This process demands careful consideration of data relationships and performance implications.

DinoAI Agent can analyze your existing models, understand their relationships, and create properly structured intermediate models with appropriate joins.

{% embed url="<https://youtu.be/vIiTrtbZ8cE?si=TayyUPIKejGUns9K>" %}

### Example Prompt

> I want to create an intermediate model which makes use of or allows me to understand data across races as well as constructor and driver standings for the season.

{% hint style="info" %}
**Optional**: Add context by selecting the relevant base models you want to join. Adding specific files as context helps DinoAI understand exactly which tables to include and how they should be related.
{% endhint %}

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

After you enter your prompt:

1. DinoAI analyzes the structure of your provided models
2. It identifies the relationships between tables (e.g., foreign keys)
3. It creates a new model with properly structured CTEs and join conditions
4. If configured, DinoAI applies your .dinorules preferences

### Example Output

DinoAI will generate professional quality model(s). See example below:

<details>

<summary>Intermediate Model example</summary>

```sql
{{ config(materialized='table') }}

WITH races AS (
    SELECT * FROM {{ ref('stg_f1_races') }}
),

circuits AS (
    SELECT * FROM {{ ref('stg_f1_circuits') }}
),

results AS (
    SELECT * FROM {{ ref('stg_f1_results') }}
),

constructors AS (
    SELECT * FROM {{ ref('stg_f1_constructors') }}
),

driver_standings AS (
    SELECT * FROM {{ ref('stg_f1_driver_standings') }}
),

-- Join race data with circuit information
race_details AS (
    SELECT
        r.race_id,
        r.year,
        r.round,
        r.name AS race_name,
        c.circuit_name,
        c.location,
        c.country,
        r.date AS race_date
    FROM races r
    LEFT JOIN circuits c ON r.circuit_id = c.circuit_id
),

-- Join results with constructors
race_results AS (
    SELECT
        res.race_id,
        res.driver_id,
        res.constructor_id,
        con.constructor_name,
        con.nationality AS constructor_nationality,
        res.grid,
        res.position,
        res.points
    FROM results res
    LEFT JOIN constructors con ON res.constructor_id = con.constructor_id
)

-- Final combined query
SELECT
    rd.race_id,
    rd.year,
    rd.round,
    rd.race_name,
    rd.circuit_name,
    rd.country,
    rd.race_date,
    rr.constructor_id,
    rr.constructor_name,
    rr.constructor_nationality,
    rr.driver_id,
    rr.grid,
    rr.position,
    rr.points,
    ds.position AS driver_standing_position,
    ds.points AS driver_total_points
FROM race_details rd
JOIN race_results rr ON rd.race_id = rr.race_id
LEFT JOIN driver_standings ds ON rr.driver_id = ds.driver_id AND rd.race_id = ds.race_id
ORDER BY rd.year, rd.round, rr.position
```

</details>

### Key Benefits

* **Relationship Understanding**: Correctly identifies and implements join conditions
* **Code Organization**: Creates well-structured CTEs that make the logic easy to follow
* **Proper Formatting**: Maintains consistent SQL style according to your standards
* **Time Savings**: Reduces complex model development from hours to minutes
* **Visualization**: Can generate mermaid diagrams to visualize data flow when using .dinorules

### When to Use This

* When building analytical models that combine multiple data sources
* When implementing complex business logic across several tables
* When standardizing existing intermediate/marts models
* When exploring new analytical capabilities from your existing data


---

# 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.paradime.io/app-help/guides/paradime-101/getting-started-with-the-paradime-ide/dinoai-accelerating-your-analytics-engineering-workflow/dinoai-agent/building-intermediate-marts-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.