Count Verification Test Example

• Use case: Verify that a CSV file from a vendor matches a relatively standard format when loaded into an analytics pipeline.
• Example recipe: the recipe pulls a global superstore data set, provided by Tableau for training purposes, from an SFTP server and loads it to an Amazon S3 resource. Two count verification tests have been written, the first to compare the file's line/row count and the second to compare the file size to expected ranges.
• Node type: DataMapper node.

Step 1: Define runtime variables

When configuring the mapping to the source file, declare runtime variables to record the size of the source file and its data.

Visual example in Automation

A source mapping for a DataMapper node has two built-in runtime variables declared to capture metrics from an incoming data file.

Summary of how-to

1. Open the node editor.

2. From the Mappings tab, define the Runtime Variables for File Size and Line Count.

   • Click to select from built-in runtime variables.

3. Select Add notes for changelog , describe the changes, then click Update.

Step 2: Configure test for row count

Write a verification test for the node, confirming the row count.

Visual example in Automation

The test verifies that the source file has the expected content. The test will generate a warning on failure and a threshold can be set in the variation to send email alerts after a specified number of test warnings. The row count test uses the same runtime line count variable declared in the source mapping and checks that the file contains a specific number of rows.

Summary of how-to

1. In the same node, select the Tests tab.

2. Create a row count test.

   1. Click Add Source Test.

   2. Fill out the fields Test Name, Failure Action, Test Logic, and Control Value.

      Where Test Logic is Compare variable against metric and the Test Variable is the runtime variable defined for Line Count in Step 1.

3. Select Add notes for changelog , describe the changes, then click Update.

Step 3: Configure test for file size

Write a verification test to confirm the file size.

Visual example in Automation

The test ensures that the file is an expected size (in bytes). The test runs a Jinja expression against the file size variable to ensure that the source file is not empty and is greater than a set MB. This minimum threshold was determined from previous runs.

Summary of how-to

1. Create a file size test.

   1. Click Add Source Test.

   2. Fill out the fields Test Name, Failure Action, and Test Logic.

      Where Test Logic is Specify Jinja expression.

   3. Enter the Test Variable, Type Conversion, and Expression.

      Where the Test Variable is the runtime variable defined for File Size in Step 1.

2. Select Add notes for changelog , describe the changes, then click Update.

File contents

The system records the test configurations shown above in the data_sources/source.json.

{
    "name": "source",
    "type": "DKDataSource_SFTP",
    "config": {
        "username": "{{sftpConfig.username}}",
        "hostname": "{{sftpConfig.hostname}}",
        "pem_file": "{{sftpConfig.key_file}}"
    },
    "keys": {
        "global_superstore_data_source": {
            "file-key": "{{global_superstore_sftp_path}}",
            "use-only-file-key": true,
            "set-runtime-vars": {
                "size": "sftp_global_superstore_data_file_size",
                "row_count": "sftp_global_superstore_data_line_count"
            }
        }
    },
    "tests": {
        "Ensure_SFTP_Min_Row_Count": {
            "action": "warning",
            "test-variable": "sftp_global_superstore_data_line_count",
            "type": "test-contents-as-integer",
            "test-logic": {
                "test-compare": "greater-than",
                "test-metric": 1500
            }
        },
        "Ensure_SFTP_Min_File_Size_in_Bytes": {
            "action": "warning",
            "test-variable": "sftp_global_superstore_data_file_size",
            "type": "test-contents-as-integer",
            "test-logic": "sftp_global_superstore_data_file_size > 1024*512"
        }
    }
}