> ## 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.

# Debugging

> The part we like the least, yet spend the most time on. SDF is here to help!

## Overview

In the previous tutorial we learned how to [create a new model with SDF](/tutorials/creating-a-model).
As we were working on `dim_marketing_campaigns`, the result of the query just didn't make sense.

In this tutorial we will see how SDF can simplify complicated debugging scenarios and accelerate
their resolution. It can also provide real-time feedback as you author your queries with unprecedented
speed and accuracy.

More specifically, we will use SDF to:

1. Identify the issue upstream with our built-in column-level lineage.
2. Run impact analysis after fixing the issue.
3. Test some syntax static analysis examples.

## Prerequisites

Completion of the [previous tutorial](/tutorials/creating-a-model).

## Guide

<Steps>
  <Step title="Understand Column Lineage">
    SDF provides column-level lineage out of the box, all the way up to source.

    To understand the lineage of the column `total_num_installs` in our new model, all
    we need to do is run:

    ```shell theme={null}
    sdf lineage analytics.dim_marketing_campaigns --column total_num_installs
    ```

    <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">
          moms\_flower\_shop.analytics.dim\_marketing\_campaigns.total\_num\_installs
          │
          │ copy
          └──────┐
                 moms\_flower\_shop.staging.stg\_installs\_per\_campaign.total\_num\_installs
                 │
                 │ mod
                 └──────┐
                        moms\_flower\_shop.staging.app\_installs.event\_id
                        │
                        │ copy
                        └──────┐
                               moms\_flower\_shop.raw\.raw\_marketing\_campaign\_events.event\_id
                               moms\_flower\_shop.staging.inapp\_events.event\_id
                               │
                               │ copy
                               └──────┐
                                      moms\_flower\_shop.raw\.raw\_inapp\_events.event\_id
        </code>
      </pre>
    </div>

    Now we can pinpoint exactly which models we need to check. Out of the entire warehouse, we only need to check 3 models!

    <Tip>
      To learn more about SDF lineage, visit our [lineage guide](/guide/basics/lineage_metadata)
    </Tip>
  </Step>

  <Step title="Identify the Issue Upstream">
    Let's check the first upstream model -- `stg_installs_per_campaign.sql`
    located under the directory `models/staging/`.

    ```sql models/staging/stg_installs_per_campaign.sql theme={null}
    SELECT 
        campaign_id,
        COUNT(event_id) AS total_num_installs
    FROM app_installs
    GROUP BY 1
    ```

    There is not much going on here. We need to keep moving up.

    In the upstream model `app_installs` defined in `models/staging/app_installs.sql`,
    let's zoom in on the `FROM` statement:

    ```sql models/staging/app_installs.sql theme={null}
    ...
    FROM inapp_events i 
        LEFT OUTER JOIN raw.raw_marketing_campaign_events m
            ON (i.event_id = m.event_id) 
    ...
    ```

    <Tip>
      **Aha!** Who said that inapp\_events and marketing events are the same?
    </Tip>
  </Step>

  <Step title="Compare to Other Models">
    You remember another model, `customers`, has correct marketing campaign data.
    Let's check out the lineage for the customers model:

    ```shell theme={null}
    sdf lineage staging.customers --column campaign_id
    ```

    <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-error">
        <code className="language-error">
          moms\_flower\_shop.staging.customers.campaign\_id
          │
          │ copy
          └──────┐
                 moms\_flower\_shop.staging.app\_installs\_v2.campaign\_id
                 │
                 │ copy
                 └──────┐
                        moms\_flower\_shop.raw\.raw\_marketing\_campaign\_events.campaign\_id
        </code>
      </pre>
    </div>

    <Tip>
      **Aha #2!** There's a V2 version of `app_installs` -- `app_installs_v2`
    </Tip>
  </Step>

  <Step title="Update the Model">
    Once we identify the issue, it's an easy fix.
    Update the `FROM` statement of `models/staging/stg_installs_per_campaign.sql`, to the use `app_installs_v2`:

    ```sql models/staging/stg_installs_per_campaign.sql theme={null}
    ...
    FROM app_installs_v2  -- Change from app_installs to app_installs_v2
    ...
    ```
  </Step>

  <Step title="Analyze">
    Let's analyze the impact.

    First, we should make sure our fix compiles:

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

    We expect to see a successful run:

    <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
            Compiling moms\_flower\_shop.staging.stg\_installs\_per\_campaign (./models/staging/stg\_installs\_per\_campaign.sql)
            Compiling moms\_flower\_shop.analytics.dim\_marketing\_campaigns (./models/analytics/dim\_marketing\_campaigns.sql)
             Finished 11 models \[2 succeeded, 9 reused] in 0.891 secs
        </code>
      </pre>
    </div>

    Next, we can look at all the tables and columns this change will impact. If we add the
    flag `--forward` to the lineage command, we will see the lineage of each column of
    `stg_installs_per_campaign` and how it is used by other models.

    To do that, add a `forward` tag to your lineage command:

    ```shell theme={null}
    sdf lineage staging.stg_installs_per_campaign --forward
    ```

    It should 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">
          Table: moms\_flower\_shop.staging.stg\_installs\_per\_campaign

          moms\_flower\_shop.staging.stg\_installs\_per\_campaign.campaign\_id

          moms\_flower\_shop.staging.stg\_installs\_per\_campaign.total\_num\_installs
          │
          │ copy
          ├──────┐
          │      moms\_flower\_shop.analytics.dim\_marketing\_campaigns.total\_num\_installs
          │ mod
          └──────┐
                 moms\_flower\_shop.analytics.dim\_marketing\_campaigns.avg\_customer\_acquisition\_cost
                 moms\_flower\_shop.analytics.dim\_marketing\_campaigns.install\_duration\_ratio
        </code>
      </pre>
    </div>

    Only our new model will be impacted.
  </Step>

  <Step title="Fix it!">
    To materialize the fix, all we need to do is run the queries:

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

    <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-error">
        <code className="language-error">
          Working set 12 model files, 15 .sdf files
              Running moms\_flower\_shop.raw\.raw\_marketing\_campaign\_events (./models/raw/raw\_marketing\_campaign\_events.sql)
              Running moms\_flower\_shop.raw\.raw\_inapp\_events (./models/raw/raw\_inapp\_events.sql)
              Running moms\_flower\_shop.staging.inapp\_events (./models/staging/inapp\_events.sql)
              Running moms\_flower\_shop.staging.marketing\_campaigns (./models/staging/marketing\_campaigns.sql)
              Running moms\_flower\_shop.staging.app\_installs\_v2 (./models/staging/app\_installs\_v2.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.989 secs

          Table moms\_flower\_shop.analytics.dim\_marketing\_campaigns
          ┌─────────────┬─────────────────────────────────┬────────────────────┬───────────────────────────────┬────────────────────────┐
          │ campaign\_id ┆ campaign\_name                   ┆ total\_num\_installs ┆ avg\_customer\_acquisition\_cost ┆ install\_duration\_ratio │
          ╞═════════════╪═════════════════════════════════╪════════════════════╪═══════════════════════════════╪════════════════════════╡
          │ 3           ┆ instagram\_ads\_3\_3e31b541d8      ┆ 192                ┆ 7.958249909930306             ┆ 0                      │
          │ 1           ┆ friends\_referrals\_1\_9b24ce4c65  ┆ 160                ┆ 10.391763883087673            ┆ 0                      │
          │ 0           ┆ instagram\_ads\_0\_8c1fdf7cb0      ┆ 91                 ┆ 4.513104055002336             ┆ 0                      │
          │ 13          ┆ friends\_referrals\_13\_c6e177ff0a ┆ 72                 ┆ 22.915686962366962            ┆ 0                      │
          │ 14          ┆ google\_search\_14\_3775b59a56     ┆ 48                 ┆ 54.12730118839374             ┆ 0                      │
          │ 19          ┆ friends\_referrals\_19\_3f4a9d9fc3 ┆ 26                 ┆ 59.38710281622366             ┆ 0                      │
          │ 11          ┆ google\_search\_11\_56e2a09ed5     ┆ 19                 ┆ 58.38329050623466             ┆ 0                      │
          │ 4           ┆ friends\_referrals\_4\_c554b77114  ┆ 17                 ┆ 163.69881288433993            ┆ 1                      │
          │ 22          ┆ friends\_referrals\_22\_69b6886d4a ┆ 15                 ┆ 116.4817634058549             ┆ 0                      │
          │ 17          ┆ google\_search\_17\_a400715565     ┆ 15                 ┆ 147.1283543422433             ┆ 1                      │
          └─────────────┴─────────────────────────────────┴────────────────────┴───────────────────────────────┴────────────────────────┘
          114 rows, showing only 10 rows.
            Run with --limit 0 to show all rows.
        </code>
      </pre>
    </div>

    Perfect! We are all set!
  </Step>
</Steps>

## Bonus

Lineage is not all we can do.

SDF provides out of the box static analysis to support the development
process as you author your transformations. This capability has two benefits:

1. Quicker development thanks to the instant feedback on your syntax and semantics
2. Compute cost savings since analysis happens on compile, before we send the query to run

Let's try a few things that **will fail**. In this case, getting an error early is a good thing.
Notice how quickly you get the response!

<Steps>
  <Step title="Open the File">
    To keep it simple, we will use `models/staging/stg_installs_per_campaign.sql`
    throughout the following steps.

    As a reminder, the query looks like this:

    ```sql models/staging/stg_installs_per_campaign.sql theme={null}
    SELECT 
        campaign_id,
        COUNT(event_id) AS total_num_installs
    FROM app_installs_v2
    GROUP BY 1
    ```
  </Step>

  <Step title="Error: No Column Found">
    Let's try to change the column campaign\_id to a non-existing column.

    For example, we can change the column name from `campaign_id` to `non_existing_column`.
    This column definitely doesn't exist in `app_installs_v2` (or `app_installs`)

    ```sql models/staging/stg_installs_per_campaign.sql theme={null}
    SELECT 
        -- We changed campaign_id to non_existing_column below
        non_existing_column, 
        COUNT(event_id) AS total_num_installs
    FROM app_installs_v2
    GROUP BY 1
    ```

    If we try to compile:

    ```shell theme={null}
    sdf compile staging.stg_installs_per_campaign
    ```

    The run will fail and the error message 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-error">
        <code className="language-error">
          error: SDF227: No column non\_existing\_column found. Available are app\_installs.install\_time, app\_installs.campaign\_name, app\_installs.campaign\_id, app\_installs.campaign\_type, app\_installs.event\_id, app\_installs.platform, app\_installs.customer\_id
            --> models/staging/stg\_installs\_per\_campaign.sql:2:5
        </code>
      </pre>
    </div>

    <Tip>
      If you are using Visual Studio Code, you can easily click into the error location
      by CMD-Clicking on the bottom of the error:

      `--> models/staging/stg_installs_per_campaign.sql:2:5`
    </Tip>
  </Step>

  <Step title="Error: Function Arguments Do Not Match">
    Similarly, we can write a function incorrectly.

    For example, the function `ABS` takes one numeric argument. What if we try to give it
    a string like `campaign_name`? In the file `models/staging/stg_installs_per_campaign.sql`,
    change the `campaign_id` selection with the following:

    ```sql models/staging/stg_installs_per_campaign.sql theme={null}
    SELECT 
        -- We changed campaign_id to ABS(campaign_name) 
        ABS(campaign_name), 
        COUNT(event_id) AS total_num_installs
    FROM app_installs_v2
    GROUP BY 1
    ```

    If we try to compile:

    ```shell theme={null}
    sdf compile staging.stg_installs_per_campaign
    ```

    The run will fail and the error message 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-error">
        <code className="language-error">
          error: SDF209: Failed to resolve function ABS: Argument type mismatch: actual: (varchar); candidates: (tinyint); (smallint); (bigint); (double); (decimal(p, s)); (real).
            --> models/staging/stg\_installs\_per\_campaign.sql:2:5
        </code>
      </pre>
    </div>
  </Step>

  <Step title="Error: Un-aggregated columns in aggregation context">
    We can even try some more complicated logic.

    For example, we can add another column to the `SELECT` without updating the `GROUP BY`.

    ```sql theme={null}
    SELECT 
        campaign_id,
        campaign_name,  -- We added an extra column
        COUNT(event_id) AS total_num_installs
    FROM app_installs_v2
    GROUP BY 1  -- We didn't add the new column here
    ```

    Watch what happens when you try to compile:

    ```shell theme={null}
    sdf compile staging.stg_installs_per_campaign
    ```

    The run will fail and the error message 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-error">
        <code className="language-error">
          error: SDF209: Failed to resolve function ABS: Argument type mismatch: actual: (varchar); candidates: (tinyint); (smallint); (bigint); (double); (decimal(p, s)); (real).
            --> models/staging/stg\_installs\_per\_campaign.sql:2:5
        </code>
      </pre>
    </div>
  </Step>

  <Step title="Try it Yourself">
    What is that one syntax mistake you always forget about until you see that
    run error? Try it out! See if SDF can save you the wait next time :).
  </Step>

  <Step title="Don't Forget to Revert!">
    While experimenting, we introduced some breaking logic to our data warehouse.
    Let's revert the model to the original SQL code.

    Update the file `models/staging/stg_installs_per_campaign.sql` with the original SQL:

    ```sql models/staging/stg_installs_per_campaign.sql theme={null}
    SELECT 
        campaign_id, 
        COUNT(event_id) AS total_num_installs
    FROM app_installs_v2
    GROUP BY 1
    ```
  </Step>
</Steps>

## Next Steps

Now that we fixed the issue, we want to prevent anything similar from happening again.
In our next tutorial, "[Deprecating a Model](/tutorials/deprecating-a-model)" we will
deprecate `app_installs`, the old version of `app_installs_v2` that we initially used.

## Related Topics

<CardGroup cols={1}>
  <Card title="Lineage" href="/guide/basics/lineage_metadata">
    Learn more about SDF's advanced column-level lineage capabilities
  </Card>
</CardGroup>