Improvements to Dataform Query Testing

[bmagyarkuti]

I propose the following extensions to Dataform’s query testing framework. We already have implementations and use them on several projects at my company. I’d like to hear the package developers’ opinion about whether they think any of these features might be a viable candidate for upstreaming.

Convenience Features

These help developers write better tests or be quicker as they work on their local machine. The same tests would be possible without them.

.only, .skip

When there are test cases marked with .only, no other test cases are executed. When any test cases are marked with .skip, those test cases are not executed. Current workarounds involve commenting out code, as well as deleting / renaming files.

For JS, I propose an interface that is like that used by BDD frameworks such as Mocha:

test('case 1');

test.only('case 2')
  .dataset('my_table')
  .input('some_referenced_table', 'SELECT 1 as r')
  .expect('SELECT 1 as r');

test('case 3');

The interface for .skip is similar.

.columns

Helps write expectations that focus on just those columns that are relevant for the test case.

without .columns	with .columns
test('students: calculates GPA using new way when student was born after 1993') .dataset('students') .dataset( 'registry', defaultsFor( 'John Doe', {dateOfBirth: '1994-06-23'} ) ) .input('grades', gradesFor( 'john doe', [ {subject: 'math', grade: 4}, {subject: 'math', grade: 2.0}, {subject: 'arts', grade: 3}, ] )) .expect(students([{ name: 'John Doe', gpa: '3.0', worstGrade: '2.0', bestGrade: '4.0', dateOfBirth: '1994-06-23', enrolledAt: '2020-08-29' }]));	test('students: calculates GPA using new way when student was born after 1993') .dataset('students') .dataset( 'registry', defaultsFor( 'John Doe', {dateOfBirth: '1994-06-23'} ) ) .input('grades', gradesFor( 'john doe', [ {subject: 'math', grade: 4}, {subject: 'math', grade: 2.0}, {subject: 'arts', grade: 3}, ] )) .columns( ['name', 'gpa', 'dateOfBirth'] ) .expect(students([{ name: 'John Doe', gpa: '3.0', dateOfBirth: '1994-06-23' }]));
Using only the current tools, I must write out each column in the expectation. worstGrade and bestGrade are easy to understand but are just unnecessary noise. They are not what I want to test here. enrolledAt, which comes from the defaults, is also irrelevant, and is difficult to understand without examining the defaults. Usually, there would be many more tests to write about GPA calculation, and each of those tests would need to repeat the noise as well.	Limiting the list of selected columns allows for a more focused test, where I only need to write out the relevant parts.

.where

Similar to .columns, but instead of filtering out irrelevant columns, it filters out irrelevant rows. Imagine the same example, just with a students table that uses the long representation, where columns effectively become rows.

The interface: .where("name = 'John Doe' AND field IN ('gpa', 'date_of_birth')").

.orderBy

Takes a list of columns. Can be used to ensure results arrive in a particular order, which helps avoid flaky tests. The current workaround is to include ordering in the implementation, or to always test just a single row.

The inclusion of this feature might resolve Issue 1265. An alternative to this feature could be an .expect that ignores the order of rows.

Feature Additions

These enable tests that are not currently possible with Dataform query testing.

.vars to Test Dataform Variables

Dataform can generate different queries depending on variables that are passed in at compile time. For example, the following query would parse a birthday using a format string that is specified at compile time.

SELECT
  name,
  PARSE_DATE(${dataform.projectConfig.vars.dateFormat}, raw_date) AS parsed_birthday
FROM ${ref('birthdays')}

I propose the introduction of a .vars method, which could be used to write test cases that specify different values for a variable. For example, the above query might be tested like so:

test('parsed_birthdays works when the format string is "%m/%d/%Y"')
  .vars({ dateFormat: '%m/%d/%Y'})
  .input('birthdays', 'SELECT "6/22/2017" AS raw_date')
  .expect('SELECT DATE("2017-06-22") AS parsed_birthday')

.calculateInput for Integration Tests

There might be cases when instead of testing the behavior of individual tables, it is better to test the behavior of a succession of tables. For example, given an ETL pipeline that consists of a series of transformations, tests that describe just the initial input and the final output might be more expressive.

input -> transformation_1 -> transformation_2 -> ... -> transformation_n -> output

In general, such tests leave more space for refactoring. For example, if developers choose to split up transformation_2 into two steps, they can perform this refactor without having to adjust any tests. Similarly, it might be beneficial even in a unit test of transformation_2 to specify just the input and the expected state after transformation_2. That way, the test suite might be used as a guide during a refactor when the output of transformation_1 changes, but the expected outputs of transformation_2 remain the same.

An integration test of transformation_2, which specifies input rather than the output of transformation_1 might have this interface. In this example, transformation_1 and transformation_2 each increase value by 1.

test('transformation_2, test case 1')
  .input('input', 'SELECT 1 AS value')
  .calculateInput(['transformation_1'])
  .expect('SELECT 3 AS value')

Note that, in our experience, these tests execute slower on BigQuery than simple query tests. Nevertheless, they remain significantly faster than tests that actually execute the pipeline.

This would resolve Issue 1387. The solution I propose is in line with @BenBirt's suggestion and requires that the user explicitly specify any intermediate steps that need executing.

Incremental Table Testing and .incremental

dataform test currently doesn’t support testing incremental tables at all. I propose testing incremental tables the same way that regular tables are tested: by executing the tested query against inline tables.

• An .incremental(true) call would be used to test when the target table already exists before the execution. In this case, the expectation would list only those rows that will be newly added (or merged) into the table. The insert / merge operation itself would not be tested. However, any conditional logic in the implementation that only activates on an incremental execution would be tested.
• An .incremental(false) call would simulate the case when the target table does not exist. In such a case, an incremental table behaves the same way as a regular table, and tests would also behave in the same way.

This would resolve Issue 1132.

.preOps

This would allow the user to specify some SQL to run ahead of the test query. For example, a query that defines a User-Defined Function as part of its preOps block can include the same UDF declaration as part of the tests. This would resolve Issue 1472.

implementation	test
const addFourAndDivide = ' CREATE TEMP FUNCTION AddFourAndDivide(x INT64, y INT64) RETURNS FLOAT64 AS ( (x + 4) / y ); '; publish('my_table') .preOps(addFourAndDivide) .query(' SELECT val, AddFourAndDivide(val, 2) FROM UNNEST([2,3,5,8]) AS val; ');	import { addFourAndDivide } from './my_table'; test('my_table includes calculated values') .preOps(addFourAndDivide) .expect(' SELECT val FROM UNNEST([3, 3.5, 4.5, 6]) AS val');

Note: Any upstreamed implementation would likely need an sqlx interface as well, which I’m open to create if requested.