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

# Tests

## Understanding Tests

Tests are a commonly used data quality feature allowing you to scan results of queries and set *expectations* of your data.
You can validate that a particular column is never null, or within a certain thresholding range, between a certain set of values, and more.

SDF provides a standard [testing library](https://github.com/sdf-labs/tests). The functions provided in the library are included natively in SDF.

<Tip>
  All SDF Tests for all columns are compiled into table scans. This allows SDF to run multiple tests with a single scan. This dramatically reduces the cost of data tests.
</Tip>

An SDF data test is a simple assertion test that runs a SQL query against your database and, if any data is returned, fails. The simplest example might be a non-null test on a column.

This would look like:

```sql non_null.sql theme={null}
select * from table where column is null
```

When `sdf test` runs this query, it will check if any data is returned and fail if so.

<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 1 model file, 1 test file, 1 .sdf file
          Running hello.pub.main (./models/main.sql)
          Testing hello.pub.test\_main (./sdftarget/dbg/tests/hello/pub/test\_main.sql)
         Finished 1 model \[1 succeeded], 1 test \[1 passed] in 1.753 secs
      \[Pass] Test hello.pub.test\_main
    </code>
  </pre>
</div>

There are three types of tests builtin.

* **Scalar Column Tests** - validate expectations of individual values in columns (eg. `x > 100` )
* **Aggregate Column Tests** - validate expectations of all values in a columns (eg. `sum(x) > 100` )
* **Table Tests** - validate expectations for all columns in a table (eg. columns a and b are unique).

### Severity

Each test may be configured with a severity flag. By default all tests have a severity of **error** meaning that a constraint violation will result in a non-zero exit code for sdf.

Tests may have three results, with text highlighting providing an indication of the output.

| Test Result | Meaning |
| ----------- | ------- |
| 🟡          | Warning |
| 🔴          | Error   |

## Using a Test

Tests are added to columns in table blocks. Each column has 'constraints' and test functions should be expressed as the expectation you have of the column.

Test libraries are written as SDF workspaces that are imported by your workspace.

Below, we add a simple aggregate uniqueness test, and a scalar null values test to a `cool_column`.

```yml theme={null}
table:
  name: a_really_great_table
  columns:
    - name: cool_column
      tests:
      - expect: unique()
      - expect: not_null()
        severity: warning
```

<Tip>
  SDF's tests library is an out-of-the-box optimized testing mechanism.
  All tests associated with a table will be run within one query.

  One query --> one table scan --> significant compute savings compare to other
  data quality solutions.
</Tip>

## SDF Standard Library Tests

### Column Tests

| Test Name                      | Type      | Description                                                |
| ------------------------------ | --------- | ---------------------------------------------------------- |
| `not_null()`                   | Scalar    | Column elements must not be null                           |
| `valid_scalar(condition)`      | Scalar    | Column elements all meet scalar condition                  |
| `valid_aggregate(condition)`   | Aggregate | Column elements all meet aggregate condition               |
| `unique()`                     | Aggregate | Column elements must be unique                             |
| `in_accepted_values([values])` | Aggregate | Column values are all elements of array                    |
| `minimum(value)`               | Aggregate | Column can not have values less than value                 |
| `maximum(value)`               | Aggregate | Column can not have values greater than value              |
| `exclusive_minimum(value)`     | Aggregate | Column can not have values less or equal to value          |
| `exclusive_maximum(value)`     | Aggregate | Column can not have values greater than or equal to value  |
| `between(lower, upper)`        | Aggregate | Column values must be between lower and upper              |
| `max_length(value)`            | Aggregate | Column values (as strings) are greater than a given length |
| `min_length(value)`            | Aggregate | Column values (as strings) are less than a given length    |
| `like(string)`                 | Aggregate | Column elements must contain string                        |
| `try_cast(type)`               | Aggregate | Column elements must include string                        |
| `primary_key(column)`          | Aggregate | Column is primary key of column                            |

### Table Tests

| Test Name                  | Type  | Description                                                                 |
| -------------------------- | ----- | --------------------------------------------------------------------------- |
| `unique_columns([c1, c2])` | Table | Table with columns `c1` and `c2` has unique values across these two columns |

<Tip>
  The full repo of `sdf_tests`, our open-source tests library, is is available on GitHub [here](https://github.com/sdf-labs/tests).
</Tip>

## Add Your First Tests

<Steps>
  <Step title="Prerequisite">
    SDF's built-in tests library `sdf_tests` uses Jinja macros. Therefore, setting Jinja
    as a preprocessor is required.

    Create the sample workspace `tests`:

    ```shell theme={null}
    sdf new --sample tests
    ```

    In the file `workspace.sdf.yml`, we have Jinja set as the default preprocessor:

    ```yml workspace.sdf.yml theme={null}
    workspace:
      name: Tests workspace
      ...
      defaults: 
        preprocessor: jinja
    ```
  </Step>

  <Step title="Add Tests">
    Open the table metadata file `src_metadata/raw_inapp_events.sdf.yml`.
    In the file, find the three commented out tests and uncomment them. They should look like this:

    <CodeGroup>
      ```yml column_name: event_id theme={null}
      ...
      - name: event_id
        description: A unique identifier of an in-app event within mom's flower shop's mobile app
        # Uncomment the following tests:
        tests:
        - expect: unique()
          severity: error
        - expect: not_null()
          severity: error
      ...
      ```

      ```yml column_name: event_name theme={null}
      ...
      - name: event_name
        description: The name of the in-app events. Supported events - install, add_to_cart, go_to_checkout, place_order
        # Uncomment the following tests:
        tests:
        - expect: in_accepted_values(['install', 'add_to_cart', 'go_to_checkout', 'place_order'])
          severity: warning
      ...
      ```

      ```yml column_name: event_value theme={null}
      ...
      - name: event_value
        description: >
          Any numeric value associated with the event. 
          For example, upon placing an order, the event value would be the total order amount in USD.
        # Uncomment the following tests:
        tests:
          - expect: between(0, 1000)
            severity: warning
      ...
      ```
    </CodeGroup>
  </Step>

  <Step title="Run Tests">
    Run the tests. In the terminal, run:

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

    <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 1 model file, 1 test file, 2 .sdf files
              Running tests\_workspace.pub.raw\_inapp\_events (./models/raw\_inapp\_events.sql)
              Testing tests\_workspace.pub.test\_raw\_inapp\_events (./sdftarget/dbg/tests/tests\_workspace/pub/test\_raw\_inapp\_events.sql)
             Finished 1 model \[1 succeeded], 1 test \[1 passed] in 1.796 secs
          \[Pass] Test tests\_workspace.pub.test\_raw\_inapp\_events
        </code>
      </pre>
    </div>
  </Step>

  <Step title="Inspect the Test">
    This step is not mandatory, but sometimes it can be helpful to view the compiled test query.
    To access the query, navigate to the file located in `sdftarget/dbg/preprocessed/tests_workspace/pub/test_raw_inapp_events.sql`.

    The file will hold the compiled tests query and 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">
          WITH
          raw\_inapp\_events\_constraints AS (
              SELECT
                  CASE WHEN COUNT(DISTINCT event\_id) != COUNT(event\_id) 
              THEN  
              'err: column event\_id is NOT unique' ELSE NULL END AS event\_id\_1
                  ,
                  CASE WHEN COUNT(CASE WHEN event\_id IS NULL THEN 1 ELSE NULL END) > 0 
              THEN  
              'err: column event\_id has NULL values' ELSE NULL END AS event\_id\_2
                  ,
                  CASE WHEN COUNT(CASE WHEN event\_name NOT IN ('install', 'add\_to\_cart', 'go\_to\_checkout', 'place\_order') THEN 1 ELSE NULL END) > 0 
              THEN  
              'wrn: column event\_name has values outside accepted values ''install'', ''add\_to\_cart'', ''go\_to\_checkout'', ''place\_order''' ELSE NULL END AS event\_name\_1
                  ,
                  CASE WHEN NOT(MIN(event\_value) >= 0) or  MAX(event\_value) > 1000
          THEN 
          'wrn: column event\_value has values outside of 0..1000' ELSE NULL END AS event\_value\_1
              FROM tests\_workspace.pub.raw\_inapp\_events
          )

          SELECT reason 
          FROM (
             
              SELECT event\_id\_1 as reason FROM raw\_inapp\_events\_constraints WHERE event\_id\_1 IS NOT NULL
              UNION ALL
                 
              SELECT event\_id\_2 as reason FROM raw\_inapp\_events\_constraints WHERE event\_id\_2 IS NOT NULL
              UNION ALL 
                 
              SELECT event\_name\_1 as reason FROM raw\_inapp\_events\_constraints WHERE event\_name\_1 IS NOT NULL
              UNION ALL 
                 
              SELECT event\_value\_1 as reason FROM raw\_inapp\_events\_constraints WHERE event\_value\_1 IS NOT NULL 
              ) AS \_combined\_errors
          ORDER BY reason;
        </code>
      </pre>
    </div>
  </Step>

  <Step title="Fail a Test">
    Let's see how it'll look like if the test fails.
    In `src_metadata/raw_inapp_events.sdf.yml`, edit the test associated with the column `event_value`
    and reduce the maximum in the range to be 10 instead of 1000:

    ```yml theme={null}
    ...
    - name: event_value
      description: >
        Any numeric value associated with the event. 
        For example, upon placing an order, the event value would be the total order amount in USD.
      # Uncomment the following tests:
      tests:
        - expect: between(0, 10)  <-- Change here - from 1000 to 10
          severity: warning
    ...
    ```

    Now let's run the test again:

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

    <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">
          warning: SDF2001: tests\_workspace.pub.raw\_inapp\_events: attempting to execute Trino, local execution is currently experimental
            --> models/raw\_inapp\_events.sql:1:1

          ... and 1 more warnings not shown
        </code>
      </pre>
    </div>
  </Step>
</Steps>

## Add a Generic Test

SDF supports the ability to turn any SQL query into a data test. This is useful for more complex tests that are not covered by the standard library.

<Steps>
  <Step title="Create a New Workspace">
    Let's start by creating a new workspace called `generic_test_example`

    ```shell theme={null}
    sdf new generic_test_example
    ```
  </Step>

  <Step title="Write a Generic Test">
    Next, let's write a SQL query that will represent our generic test. For our example, we'll write the most basic of tests which will always fail (since it will always return data).

    Create a new file `generic_test.sql` in a new directory called `tests`:

    ```sql tests/generic_test.sql theme={null}
    select 1 as col; 
    ```
  </Step>

  <Step title="Configure Generic Tests">
    Next, let's update the `workspace.sdf.yml` to include our new generic test.
    To do so, add `- path: tests` to your `workspace.sdf.yml` includes block like so:

    ```yml workspace.sdf.yml theme={null}
    workspace:
      name: hello
      edition: "1.3"
      description: "A minimal workspace"

      includes:
        - path: models
        - path: tests
    ```

    <Note>
      The `- path: tests` includes item does not need the `type: test` property since naming your directory `tests` defaults
      the include type to `test`. For a list of all default include types, check out our [includes block documentation](/guide/setup/workspaces#includes-blocks)
    </Note>
  </Step>

  <Step title="Run the Test">
    Now that we have a our new generic tests added, let's trying running it! Note that we are expecting this test to fail, as the SQL query we wrote will always return data.

    In the terminal, run:

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

    <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">
          warning: SDF2001: hello.pub.generic\_test: attempting to execute Trino, local execution is currently experimental
            --> tests/generic\_test.sql:1:1
        </code>
      </pre>
    </div>

    Great! It's the first time in your life you've been happy to see a test fail.
  </Step>
</Steps>

## Add Custom Tests to the Testing Library

SDF supports an easy way to write custom tests that will utilize our built-in testing infrastructure
to run custom testing macros.

<Steps>
  <Step title="Setup">
    In our workspace, let's create a new folder for our custom test macros. We can call it `custom_tests`.

    The new directory 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">
              Cleaned sdftarget
             Finished clean in 0.316 secs
          .
          ├── custom\_tests
          ├── models
          │   └── raw\_inapp\_events.sql
          ├── seeds
          │   └── inapp\_events.csv
          ├── src\_metadata
          │   └── raw\_inapp\_events.sdf.yml
          └── workspace.sdf.yml

          5 directories, 4 files
        </code>
      </pre>
    </div>

    We also need to include the new folder in our `workspace.sdf.yml` file:

    ```yml theme={null}
        workspace:
          name: tests_workspace
            ...
            include:
                - path: custom_tests
                  type: metadata
            ...
    ```

    Now we are ready to add some custom tests!
  </Step>

  <Step title="Create a Custom Test">
    To extend SDF's built-in testing library, add a jinja file named `my_test.jinja`
    with the following custom test in the newly created folder:

    ```jinja custom_tests/my_test.jinja theme={null}
    {% macro custom_column_test(severity, column_name) -%}
        COUNT(CASE WHEN {{ column_name }} > 1000 THEN 1 ELSE NULL END) > 0 
        ==> 
        '{{severity}}: column {{ column_name }} is over 1000"' 
    {%- endmacro %}
    ```

    <Note>
      In order to use SDF's testing infrastructure, we need to maintain
      a similar macro signature as in the official library.
    </Note>
  </Step>

  <Step title="Try It!">
    First, we need to add the test to our table yml file, just like we did with
    SDF's built-in tests.
    In the file `src_metadata/raw_inapp_events.sdf.yml`, add our new custom test
    to the column `event_name`:

    ```yml src_metadata/raw_inapp_events.sdf.yml theme={null}
    table:
        ...
        columns:
            - name: index
                description: row_number
                tests:
                    - expect: tests_workspace.custom_column_test()  <-- this is our new test!
                      severity: warning
        ... 
    ```

    <Tip>
      Unlike with regular tests, to call a custom test we need to use the syntax
      `{sdf_workspace_name}.{custom_test_name}`. In the example above, the workspace name
      is `tests_workspace` and the custom\_test\_name is `custom_column_test`.
    </Tip>

    Let's run the test! In your terminal, execute:

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

    The test passed!

    <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">
          /bin/sh: 1: sdf: not found
        </code>
      </pre>
    </div>

    We can look at the compiled tests query located in the file
    `sdftarget/dbg/preprocessed/tests_workspace/pub/test_raw_inapp_events.sql`.
    Notice our custom logic in the compiled query:

    <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">
          cat: sdftarget/dbg/preprocessed/tests\_workspace/pub/test\_raw\_inapp\_events.sql: No such file or directory
        </code>
      </pre>
    </div>
  </Step>
</Steps>