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
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.
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.