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 ...]]
    [--fail-fast
    [--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

chunk-size​

Usage: --chunk-size 500

Control the maximum number of dimensions included in a Spectacles validation query.

By default, Spectacles runs Explore-level queries that select up to 500 of the Explore's dimensions. However, depending on the SQL defined in these dimensions, it's possible to encounter query character length limitations (see previous GitHub issue).

To reduce the size of these queries, you can reduce the chunk size so Spectacles runs more Explore-level queries with fewer dimensions per query. Running more queries will increase the total runtime of validation, so you probably don't need to modify this setting unless you're encountering this issue.

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.

explores​

Usage: --explores model_a/* -model_a/explore_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.

To exclude a model or explore, add a leading hyphen. For example, -orders/order_items will exclude the order_items Explore from validation.

Here are some additional examples:

Only Explore A

--explores model_a/explore_a

All Explores except for Explore B

--explores -model_a/explore_b

All Explores in Model A except for Explore B

--explores "model_a/*" -model_a/explore_b

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
tip

On some shells, like zsh, the * character gets treated as a file globbing wildcard, with causes unexpected results for Spectacles. You might see an output like:

zsh: no matches found

If that happens, you need to wrap your model_name/explore_name strings in quotes, like --explores "model_a/explore_a".

fail-fast​

Usage: --fail-fast

Only run Explore-level queries. Normally (without fail-fast) Spectacles will run one query per Explore with all dimensions selected, then test each failing Explore dimension by dimension, returning all errors associated with their dimensions. In fail-fast mode, only runs the Explore-level query, returning the first error identified in each Explore.

You can read more about the tradeoffs of fail-fast mode here.

ignore-hidden​

Usage: --ignore-hidden

Ignore hidden dimensions from testing. Normally, the SQL validator will test all dimensions, regardless of whether they are hidden or not. With this flag set, Spectacles will only test dimensions visible to the end-user.

pin-imports​

Usage: --pin-imports core_looker_project:ae4d8c

Pins locally imported Looker projects to a specific git ref. By default, Spectacles will set any locally imported Looker projects, defined in your manifest.lkml, to their current production commit. If you want to test a project with a specific commit or branch from the upstream imported project, you can use this flag to do so.

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.