> ## Documentation Index
> Fetch the complete documentation index at: https://docs.sdf.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Creating a model

> Our first model in SDF, that's exciting!

## Overview

Imagine your mom is trying to optimize marketing campaigns for mobile app
installations, because she's proudly running a tech-forward boutique flower shop.
She wants to get some metrics per campaign to examine their performance.

To help your mom, you plan to create a new table in the analytics layer called `dim_marketing_campaigns`.

In short, creating a new model using SDF is as simple as two basic steps:

1. Create a SQL file with the query of the model
2. Run `sdf run`

Let's check it out!

<Note>
  In this series of tutorials, we will execute our queries locally using SDF's built-in infrastructure.
  To learn more about using other integrations see our [integrations guides](/guide/setup/integrations).
</Note>

## Prerequisites

Completion of the [previous tutorial](/tutorials/tutorials-intro).

<Tip>
  A complete version of the workspace (after the tutorials) is available in [this GitHub project](https://github.com/sdf-labs/sdf-cli/tree/main/examples/moms_flower_shop_completed) for you
  to follow along these guides if you wish.

  Alternatively, create the folder locally by running the command `sdf new --sample moms_flower_shop_completed`
</Tip>

## Guide

<Steps>
  <Step title="Create a New Analytics Layer Model">
    To create a new model in SDF, all you need to do is create a new SQL file.
    To follow best practices, we will create the model in the `models/analytics` directory.

    Create a file called `dim_marketing_campaigns.sql` under `models/analytics` and copy the SQL below.

    <Tip>
      Note that SDF can infer dependencies and other information from SQL natively
      with no special adjustments needed from you. We do not require Jinja refs, sources
      definitions or additional configurations.

      Just write your SQL as you would run it.
    </Tip>

    ```sql models/analytics/dim_marketing_campaigns.sql theme={null}
    SELECT 
        -- marketing campaigns dimensions
        m.campaign_id,
        m.campaign_name,
        -- metrics
        i.total_num_installs,
        total_campaign_spent / 
            NULLIF(i.total_num_installs, 0) AS avg_customer_acquisition_cost,
        campaign_duration / 
            NULLIF(i.total_num_installs, 0) AS install_duration_ratio
    FROM staging.marketing_campaigns m
        LEFT OUTER JOIN staging.stg_installs_per_campaign i
        ON (m.campaign_id = i.campaign_id)
    ORDER BY total_num_installs DESC NULLS LAST
    ```
  </Step>

  <Step title="Validate the Model Through Static Analysis">
    In just one command, SDF will analyze our entire data warehouse and provide immediate
    feedback. Through this static analysis we can fix our query or dependency graph before sending it to
    run, saving time and compute.

    <Note>
      To run SDF commands your terminal should execute from the workspace's root folder - `moms_flower_shop`.

      If not already there, run `cd moms_flower_shop` in your terminal.
    </Note>

    Let's compile the workspace:

    ```shell theme={null}
    sdf compile
    ```

    A successful run will show all our models in their order of execution.

    SDF intelligently caches previous compiles. Since we compiled the source models
    under `models/raw` in the [previous tutorial](/tutorials/tutorials-intro#),
    those will not be re-compiled in this run. If you'd like to re-compile all files,
    you can clear SDF's cache by running `sdf clean`.

    It will look like this:

    <div className="bg-[#0F1117] dark:bg-codeblock rounded-xl dark:ring-1 dark:ring-gray-800/50 relative">
      <pre style={{ fontFamily: 'monospace', backgroundColor: 'transparent' }} className="language-shell">
        <code className="language-shell">
          Working set 11 model files, 22 .sdf files
            Compiling moms\_flower\_shop.raw\.raw\_inapp\_events (./models/raw/raw\_inapp\_events.sql)
            Compiling moms\_flower\_shop.raw\.raw\_customers (./models/raw/raw\_customers.sql)
            Compiling moms\_flower\_shop.raw\.raw\_marketing\_campaign\_events (./models/raw/raw\_marketing\_campaign\_events.sql)
            Compiling moms\_flower\_shop.raw\.raw\_addresses (./models/raw/raw\_addresses.sql)
            Compiling moms\_flower\_shop.staging.marketing\_campaigns (./models/staging/marketing\_campaigns.sql)
            Compiling moms\_flower\_shop.staging.inapp\_events (./models/staging/inapp\_events.sql)
            Compiling moms\_flower\_shop.staging.app\_installs (./models/staging/app\_installs.sql)
            Compiling moms\_flower\_shop.staging.app\_installs\_v2 (./models/staging/app\_installs\_v2.sql)
            Compiling moms\_flower\_shop.analytics.agg\_installs\_and\_campaigns (./models/analytics/agg\_installs\_and\_campaigns.sql)
            Compiling moms\_flower\_shop.staging.customers (./models/staging/customers.sql)
            Compiling moms\_flower\_shop.staging.stg\_installs\_per\_campaign (./models/staging/stg\_installs\_per\_campaign.sql)
             Finished 11 models \[11 succeeded] in 0.915 secs
        </code>
      </pre>
    </div>

    We will see how it looks like when this command fails in later tutorials.
  </Step>

  <Step title="Run the Query">
    Now that we validated our query, let's see its results.

    All we need to do is execute `sdf run` in our terminal. To only run our table,
    we will indicate its name in our command.

    ```shell theme={null}
    sdf run analytics.dim_marketing_campaigns
    ```

    Notice that to execute our query, SDF ran all the models it is dependent on.

    <div className="bg-[#0F1117] dark:bg-codeblock rounded-xl dark:ring-1 dark:ring-gray-800/50 relative">
      <pre style={{ fontFamily: 'monospace', backgroundColor: 'transparent' }} className="language-shell">
        <code className="language-shell">
          Working set 12 model files, 15 .sdf files
              Running moms\_flower\_shop.raw\.raw\_inapp\_events (./models/raw/raw\_inapp\_events.sql)
              Running moms\_flower\_shop.raw\.raw\_marketing\_campaign\_events (./models/raw/raw\_marketing\_campaign\_events.sql)
              Running moms\_flower\_shop.staging.marketing\_campaigns (./models/staging/marketing\_campaigns.sql)
              Running moms\_flower\_shop.staging.inapp\_events (./models/staging/inapp\_events.sql)
              Running moms\_flower\_shop.staging.app\_installs (./models/staging/app\_installs.sql)
              Running moms\_flower\_shop.staging.stg\_installs\_per\_campaign (./models/staging/stg\_installs\_per\_campaign.sql)
              Running moms\_flower\_shop.analytics.dim\_marketing\_campaigns (./models/analytics/dim\_marketing\_campaigns.sql)
             Finished 7 models \[7 succeeded] in 0.999 secs

          Table moms\_flower\_shop.analytics.dim\_marketing\_campaigns
          ┌─────────────┬─────────────────────────────────┬────────────────────┬───────────────────────────────┬────────────────────────┐
          │ campaign\_id ┆ campaign\_name                   ┆ total\_num\_installs ┆ avg\_customer\_acquisition\_cost ┆ install\_duration\_ratio │
          ╞═════════════╪═════════════════════════════════╪════════════════════╪═══════════════════════════════╪════════════════════════╡
          │ 25          ┆ friends\_referrals\_25\_f1a9344085 ┆ 12                 ┆ 174.903432075169              ┆ 1                      │
          │ 18          ┆ instagram\_ads\_18\_92b545ab18     ┆ 10                 ┆ 288.52496746162666            ┆ 2                      │
          │ 61          ┆ friends\_referrals\_61\_17d7bb85f3 ┆ 10                 ┆ 313.0172449950501             ┆ 2                      │
          │ 51          ┆ instagram\_ads\_51\_94f540ce54     ┆ 9                  ┆ 261.3704859860934             ┆ 2                      │
          │ 26          ┆ google\_search\_26\_bb6932ae6d     ┆ 9                  ┆ 301.34941277893944            ┆ 2                      │
          │ 17          ┆ google\_search\_17\_a400715565     ┆ 9                  ┆ 245.21392390373887            ┆ 1                      │
          │ 49          ┆ friends\_referrals\_49\_16b0b159fa ┆ 9                  ┆ 288.469560173737              ┆ 2                      │
          │ 32          ┆ google\_search\_32\_893397b50d     ┆ 8                  ┆ 369.19360069982207            ┆ 2                      │
          │ 4           ┆ friends\_referrals\_4\_c554b77114  ┆ 8                  ┆ 347.85997737922236            ┆ 2                      │
          │ 103         ┆ instagram\_ads\_103\_cd5421fbd9    ┆ 8                  ┆ 223.14798839074024            ┆ 1                      │
          └─────────────┴─────────────────────────────────┴────────────────────┴───────────────────────────────┴────────────────────────┘
          114 rows, showing only 10 rows.
            Run with --limit 0 to show all rows.
        </code>
      </pre>
    </div>
  </Step>
</Steps>

Congratulations! You just created your first model using SDF!

## Next Steps

**Our work here isn't done!**

As you look at the results of the query you realize that the `total_num_installs`
column just doesn't make sense.

Of course, your query is perfect! So what could've possible gone wrong?
Let's debug together using SDF in our [debugging tutorial](/tutorials/debugging).