Recently, Google announced Looker Continuous Integration (CI), a powerful automated tool to guard against errors and help to maintain data integrity. Using CI suites, developers can configure validators to test LookML and have peace of mind when pushing changes to production.
After an administrator has enabled Looker CI, you can build a continuous integration suite. Suites are built by configuring four validators; SQL, Assert, Content, and LookML. You can either build a suite to focus on one validator to target specific types of errors, or you can run them all at once for a comprehensive error report.
Getting Started
To create and run your first CI suite, create a developer copy of the sample_thelook_ecommerce project or an experimental branch of your own LookML project in development mode. This allows you to experiment without affecting your production environment.
After opening the project, locate and select continuous integration in the left sidebar. Go to the suites tab, click the create suite button, and then give your suite a name that reflects its purpose. Configure the validators according to your needs. On the suite edit page, you can select which validators you want to include in this suite. Use the following examples to build your understanding of the SQL and Assert validators.
The SQL Validator
The SQL validator checks for invalid dimensions in your LookML. If a dimension in your database has a name change, when you run the validator it will alert you. SQL statements in LookML dimensions will run to verify that all queries are valid. To improve performance and focus your results, you can exclude or include certain Looker Explore files. For other configurations, check the Looker documentation. When you toggle the SQL validator on, the default entry in the “Explores to query” field will run all LookML with dimensions. For a more focused example, target only the SQL in a single model by replacing the default entry with “basic_ecomm/*”. This will test all dimensions in the basic_ecomm model. This setting is key for scoping tests and improving performance by focusing the validation on specific parts of the model.
Save and run the suite. You will find that there are no errors returned.
Now, deliberately add a typo to a dimension. This example changes order_id. Make the change and commit your changes.
#This is the correct version in the original code
dimension: order_id {
type: string
sql: ${TABLE}.order_id ;;
}
#Replace that with the incorrect version below
dimension: order_id {
type:string
sql: ${TABLE}.ord_id ;;
}
Run the suite to see the error. The details indicate the main error and the line in the model where the issue arose. From there you can drill into the issue by opening the LookML or by exploring the data from that point to see a more detailed SQL query error. Fix the dimension, commit your changes, and run your CI suite to see that the SQL validator passes when you have the proper field name.
The Assert Validator
Try this example to test out the assert validator. The assert validator runs defined data tests that you’ve written in LookML. A popular example of a data test is one that checks for unique primary keys. Let’s demonstrate this example, and we’ll also explore a counter example where the data is not unique to see the difference in error responses. Add these two tests and assertions at the end of the basic_order_items.view file in the basic_ecomm model.
#This test asserts that the id dimension is unique
test: pK_is_unique {
explore_source: basic_order_items {
column: id {}
column: count {}
sorts: [basic_order_items.count: desc]
limit: 1
}
assert: order_id_is_unique {
expression: ${basic_order_items.count} = 1 ;;
}
}
#This asserts that the status dimension is unique
test: status_is_unique {
explore_source: basic_order_items {
column: status {}
column: count {}
sorts: [basic_order_items.count: desc]
limit: 1
}
assert: status_is_unique {
expression: ${basic_order_items.count} = 1 ;;
}
}
After adding this to the LookML, create a new suite and toggle the assert validator. Similar to the first suite, only test the “basic_ecomm/*” model by indicating that in “Explores to query”. The asterisk (“*”) indicates that all tests in the basic_ecomm model will run. Save and run this suite. After the tests run, check for a green checkmark. If your tests for the basic_ecomm model were written correctly, the primary key, “id”, will pass as unique, but the expression for the status_is_unique assertion evaluates to “No” and fails.
Test suites and assert validators help validate your data with known tests. A common problem in test suites is to find tests that weren’t called or run. The Looker CI assert validator explicitly identifies which tests ran and whether they passed or failed.
You can manually trigger a CI run at any time from the IDE by selecting your suite on the suites page and clicking “run”.
Now that you know how to create and build a Looker CI suite, try to build one yourself. Change your LookML in different ways, save and commit your code, then run a suite to test it out. Let me know in the comments how it went.