Rabbit-In-a-Hat can generate a framework for creating a set of unit tests. The framework consists of a set of R functions tailored to the source and target schema in your ETL. These functions can then be used to define the unit tests.

These are the steps to perform unit testing:

1. Create the testing framework for your source and target database schemas.
2. Using the framework in R, define a set of unit tests
3. Use the framework to generate testing data in the source data schema
4. Run your ETL on the test data to produce data in the CDM schema
5. Use the framework to evaluate whether the CDM data meets the defined expectations

It is advised to use R-Studio for defining your unit tests. One reason is that RStudio will automatically prompt you with possible function and argument names after you've only typed the first few characters.

In Rabbit-in-a-Hat, have your ETL specifications open. The source data schema should be loaded from the White-Rabbit scan report, and the target data schema should be selected (usually the OMOP CDM v5). Go to File → Generate ETL Test Framework, and use a file name with the .R extension, for example MyTestFrameWork.R.

Next, create an empty R script, and start by sourcing the R file that was just created:

source("MyTestFrameWork.R")

Be sure to run this command immediately to make the function definitions available to R-Studio.

The test framework defines the following functions for each table in the source schema:

• get_defaults_<table name> shows the default field values that will be used when creating a record in the table. At the start, these default values have been taken from the White-Rabbit scan report, using the most frequent value.

• set_defaults_<table name> can be used to change the default values of one or more fields in the table. For example set_defaults_enrollment(enrollment_date = "2000-01-01").

• add_<table name> can be used to specify that a record should be created in the table. The arguments can be used to specify field values. For fields where the user doesn't specify a value, the default value is used. For example add_enrollment(member_id = "M00000001").

The following functions are defined for each table in the CDM schema:

• expect_<table name> can be used to state the expectation that at least one record with the defined properties should exist in the table. For example expect_person(person_id = 1, person_source_value = "M00000001").

• expect_no_<table name> can be used to state the expectation that no record with the defined properties should exist in the table. For example expect_no_condition_occurrence(person_id = 1).

• expect_count_<table name> can be used to state the expectation that a specific number of records with the defined properties should exist in the table. For example expect_count_condition_occurrence(person_id = 1, rowCount = 3).

One further function is available:

• declareTest is used to group multiple statements under a single identifier. For example declareTest(id = 1, description = "Test person ID").

Using these functions, we can define tests. Here is an example unit test:

declareTest(101, "Person gender mappings")
add_enrollment(member_id = "M000000101", gender_of_member = "male")
add_enrollment(member_id = "M000000102", gender_of_member = "female")
expect_person(person_id = 101, gender_concept_id = 8507, gender_source_value = "male")
expect_person(person_id = 102, gender_concept_id = 8532, gender_source_value = "female")

In this example, we define a test for gender mappings. We specify that two records should be created in the enrollment table in the source schema, and we specify different values for the member_id field and gender_of_member field. Note that the enrollment table might have many other fields, for example defining the start and end of enrollment, but that we don't have to specify these in this example because these fields will take their default values, typically taken from the White-Rabbit scan report.

In this example we furthermore describe what we expect to see in the CDM data schema. In this case we formulate expectations for the person table.

We can add many such tests to our R script. For an example of a full set of test definitions, see the HCUP ETL unit tests.

If we run our full R script two variables are created in R. The first variable is insertSql, and it contains the SQL for creating the test data. The SQL assumes that the data schema already exists, and will first remove any records that might be in the tables. We can execute the SQL in any SQL client, or we can use OHDSI's DatabaseConnector package. For example:

library(DatabaseConnector)
connectionDetails <- createConnectionDetails(user = "joe",
                                             password = "secret",
                                             dbms = "sql server",
                                             server = "my_server.domain.org")
connection <- connect(connectionDetails)

executeSql(connection, "USE source_schema")
executeSql(connection, paste(insertSql, collapse = "\n"))

Now that the test source data is populated. You can run the ETL process you would like to test.

The second variable created by our R script is called testSql. It contains SQL that will create a table called test_results in your CDM schema, and populate it with the results of the tests. (If the table already exists it will first be dropped). Again, we could use any SQL client to run this SQL, or we could use DatabaseConnector:

executeSql(connection, "USE cdm_schema")
executeSql(connection, paste(testSql, collapse = "\n"))

Aftwerwards, we can query the results table to see the results for each test:

querySql(connection, "SELECT * FROM test_results")

Which could return this table:

In this case we see there were two expect statements under test 101 (Person gender mappings), and both expectations were met so the test passed.