Skip to main content

SQL Validator

Usage​

spectacles sql
    [-h]
    [GLOBAL_FLAGS ...]
    --project PROJECT
    [--branch BRANCH]
    [--remote-reset | --commit-ref COMMIT_REF]
    [--explores EXPLORES [EXPLORES ...]]
    [--exclude EXCLUDE [EXCLUDE ...]]
    [--mode {batch,single,hybrid}]
    [--concurrency CONCURRENCY]
    [--profile]
    [--runtime-threshold RUNTIME_THRESHOLD]

Options​

All required options in Global Flags are also required for the SQL Validator.

branch​

Usage: --branch dev-branch-abc123

The Git branch of your Looker project to validate.

If neither branch nor commit-ref are passed, Spectacles will test production.

Environment Variable: LOOKER_GIT_BRANCH

commit-ref​

Usage: --commit-ref ae4d8c

The commit of your project that Spectacles should checkout to validate. Spectacles will create a temporary branch for the tests based on this commit and delete the branch at the end of the validation or if an exception is handled.

If neither branch nor commit-ref are passed, Spectacles will test production.

Environment Variable: SPECTACLES_COMMIT_REF

concurrency​

Usage: --concurrency 40

The number of simultaneous queries that Spectacles will run through Looker. The default concurrency is 10. Increasing concurrency will decrease the duration of SQL validations but will increase the load on your data warehouse and Looker instance.

Most modern data warehouses can readily handle a greater query concurrency than 10, but we conservatively set the default. Learn about how Spectacles limits resource consumption.

exclude​

Usage: --exclude model_a/explore_a model_b/*

Exclude specific models or explores from validation. This flag accepts a list of / delimited model and explore names. The wildcard operator * is also accepted to exclude all models or explores, for example model_a/* will exclude all explores in Model A.

Here's an example:

All explores except for Explore B

--exclude model_a/explore_b

explores​

Usage: --explores model_a/explore_a model_b/*

Select specific models or explores for validation. This flag accepts a list of / delimited model and explore names. The wildcard operator * is also accepted to choose all models or explores, for example model_a/* will select all explores in Model A.

Here are some additional examples:

Only Explore A

--explores model_a/explore_a

All explores in Model A

--explores "model_a/*"

Every explore named Explore A across all models

--explores "*/explore_a"

Explore A and Explore B

--explores model_a/explore_a model_a/explore_b

mode​

Usage: --mode hybrid

One of batch, hybrid, or single. The query mode determines how Spectacles builds queries from explores. In batch mode, Spectacles will create one query per explore with all dimensions selected. In single mode, Spectacles will create one query per dimension. In hybrid mode, Spectacles will run in batch mode first, then in single mode, but only for the explores that have SQL errors.

You can read more about the tradeoffs of these query modes here.

profile​

Usage: --profile or -p

Runs the query profiler during validation, which tracks the runtime for each query. After validation, the profiler results show each query that took longer to execute than the runtime threshold (the default is 5 seconds).

The profiler is useful for debugging long-running queries that might be a bottleneck for SQL validation on particularly large explores and models. The results from the profiler look something like this:

............................ Query profiler results ............................

| Type      | Name                            | Runtime (s)   | Query ID   | Explore From Here                                      |
|-----------|---------------------------------|---------------|------------|--------------------------------------------------------|
| dimension | users.city                      | 10.4          | 1919       | https://spectacles.looker.com/x/pPPjILaHzUzHnTH0sXJuJ3 |
| dimension | users.id                        | 7.3           | 1931       | https://spectacles.looker.com/x/BZHuziOJYrnDfHGpvMi6BJ |
| dimension | users.first_name                | 7.3           | 1929       | https://spectacles.looker.com/x/HHTcwq6vdIOtzActhFCJ3H |
| dimension | users.state                     | 5.2           | 2047       | https://spectacles.looker.com/x/U54sjv1c3mB6lQKyWmY0rh |

project​

Required. Usage: --project ecommerce

The LookML project you want to validate. Projects are listed at the bottom of the Develop menu in Looker.

Environment Variable: LOOKER_PROJECT

remote-reset​

Usage: --remote-reset

Reset the branch to the version of the branch that is on the remote repository. This flag cannot be used with --commit-ref because it implicitly specifies a commit to use.

caution

Providing this flag will delete any uncommited changes in the user’s workspace.

Environment Variable: SPECTACLES_REMOTE_RESET

runtime-threshold​

Usage: --runtime-threshold 10

The cutoff, in seconds, used by the query profiler to define "long-running" queries. The default is 5 seconds. For example, if you only wanted to see queries that lasted longer than 30 seconds during SQL validation, set this argument to 30.

If --profile or -p is not passed, this argument does nothing.