Ensuring data quality

There are three types of builtin tests.

• 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 table (eg. columns a and b are unique).

In this tutorial, we will create one test of each type.

Since SDF’s tests library leverages Jinja, we also need to add Jinja as a preprocessor. In the workspace.sdf.yml file, uncomment the following:

# Uncomment here to begin the "Ensuring Data Quality" tutorial >>>>>>
defaults: 
  preprocessor: jinja
# <<<<<<

For the sake of this tutorial, we will add tests on the inapp_events staging model located in models/staging/inapp_events.

To add tests to the model, we need to create a YML file to hold the model’s metadata.

Under metadata/staging, create a new file called inapp_events.sdf.yml containing the following definition:

table:
    name: inapp_events

Let’s say we want to verify there are no negative orders, meaning the event_value which represents the total order amount in USD.

We can use the scalar test valid_scalar(condition) where condition = event_value >=0. Add to the newly created YML file the following test:

table:
    name: inapp_events
    # Add the code below:
    columns: 
      - name: event_value
        tests:
          - expect: valid_scalar("""event_name >= 0""")
            severity: error

A few things to note:

1. This is a column-level test so we need to add a column definition
2. To specify the condition, we need to wrap it with triple quotes """condition"""
3. We can define a severity level for the test - either error or warning

To execute the test, simply run the command:

sdf test --show result

[Pass] Test moms_flower_shop.staging.test_inapp_events

We can write a similar test as an aggregate column test. Instead of validating each value individually, we can just look at the minimum value of event_value and assert whether it is positive.

Let’s add the new test to the YML file:

table:
  name: inapp_events
  columns: 
      - name: event_value
        tests:
          - expect: valid_scalar("""event_name >= 0""")
            severity: error
          # Add the code below:
          - expect: minimum(0)
            severity: error

Let’s run the test again:

sdf test --show result

[Pass] Test moms_flower_shop.staging.test_inapp_events

On a table level, we want to make sure that our unique key is indeed unique. In this case, the table key is event_id. However, in other cases, it could be a combination of multiple columns. SDF supports all scenarios.

Let’s add this table-level test to the YML file:

table:
    name: inapp_events
    # Add the code below:
    tests:
      - expect: unique_columns(["event_id"])
        severity: error
    columns: 
        - name: event_value
            tests:
                ...

We can run the tests again:

sdf test --show result

[Pass] Test moms_flower_shop.staging.test_inapp_events