home User Guide Getting Started Help Center Documentation Community Training Certification
menu
close
settings
Looker keyboard_arrow_down
language keyboard_arrow_down
English
Français
Deutsch
日本語
search
print
test

Usage

test: historic_revenue_is_accurate {
  explore_source: orders {
    column: total_revenue {
      field: orders.total_revenue
    }
    filters: [orders.created_date: "2017"]
  }
  assert: revenue_is_expected_value {
    expression: ${orders.total_revenue} = 626000 ;;
  }
}

Hierarchy

test

- or -

test

- or -

test

Default Value

None

Accepts

The identifier of the data test, plus subparameters defining the test assertions and query.

Definition

Looker has the LookML validator to verify that your LookML code is syntactically valid and the content validator to verify object references between your content and your model.

In addition to those validators, the test parameter allows you to validate the logic of your model. For each data test, you create a query and a yesno assertion statement. The data test runs the test query and verifies that the assertion is true for each row of the test query. If the assertion statement returns yes for every row of the test query, the data test passes.

If your project settings are configured to require data tests to pass before deploying to production, the IDE will present the Run Tests button after you commit changes to the project. LookML developers must run the data tests before deploying changes to production.

Regardless of whether your project requires data tests before deploying to production, a LookML developer in Development Mode can run data tests at any time to verify the model’s logic.

You can create data tests in model files, view files, or in separate, dedicated data test files. When using a dedicated file to house your data tests, remember to include the data test file in any model file or view file where you want to run your data tests.

A data test cannot have the same name and explore_source as another data test in the same project. If you are using the same explore_source for multiple data tests in your project, be sure that the names of the data tests are all unique.

The test parameter has the following subparameters:

Once you define the LookML for your test, you can run the data test to verify that your test works properly and to see if your model’s logic passes the test. You have the option to Run Data Tests for all files in your project, or to Run LookML Tests for the current file only. You must be in Development Mode to run data tests.

You can also click on the Explore Query button to open an Explore with the query defined in the data test.

explore_source

The explore_source parameter in a data test uses the same syntax and logic as the explore_source parameter of a derived table. The one difference is that a data test’s explore_source doesn’t support the derived_column, bind_filters, and bind_all_filters subparameters.

Handy Tip: An easy way to get the explore_source LookML is to use an existing Explore to create your query, then select Get LookML from the Explore’s gear menu and click the Derived Table tab to get the LookML for the query. See our documentation on creating native derived tables for more information.

Note the following for the explore_source of a data test:

assert

The assert subparameter defines the criteria by which the explore_source query’s result is considered valid. The expression subparameter accepts a Looker expression that results in a yesno (Boolean). After the explore_source query is run, the assertion’s expression is evaluated for every row of the query’s result set. If there is a no response for any row of the query, the data test fails. If the query itself has errors, the test also fails.

Handy Tip: You can use the table calculations dialog box to test the Looker expression syntax to use in your test’s expression parameter.

A test can have multiple assert declarations. For the test to pass, each assertion has to be true for every row of the explore_source query.

Examples

Ensuring That a Primary Key Is Unique

The following data test creates a query from the orders Explore and defines an expression to test that order IDs are unique in the result set. The explore_source query creates a count of rows associated with each ID in the database. If the ID is unique, the database should have only one row for an ID. Furthermore, it sorts on the count and limits the result set to one row, so the query response will be the ID with the highest count. If any ID has a count higher than 1, we know there are multiple rows for that ID and therefore the ID is not unique. If that’s the case, this data test will fail.

test: order_id_is_unique { explore_source: orders { column: id {} column: count {} sorts: [count: desc] limit: 1 } assert: order_id_is_unique { expression: ${orders.count} = 1 ;; } }

Verifying a Known Value

This next data test checks to make sure that the 2017 revenue value is always $626,000. In this data set, that is a known value that should never change.

test: historic_revenue_is_accurate { explore_source: orders { column: total_revenue { field: orders.total_revenue } filters: [orders.created_date: "2017"] } assert: revenue_is_expected_value { expression: ${orders.total_revenue} = 626000 ;; } }

Confirming There Are No Null Values

This next data test checks to make sure that there are no null values in the data. This explore_source uses a sort to be sure that any nulls will be returned at the top of query. Note that sorting for nulls can vary, based on your dialect. The test below uses desc: yes as an example.

test: status_is_not_null { explore_source: orders { column: status {} sorts: [status: desc] limit: 1 } assert: status_is_not_null { expression: NOT is_null(${orders.status}) ;; } }

Top