How to Profile SQL Validation

When running SQL validation on an extra large explore or model, long-running queries can become a bottleneck for speedy validation. This can happen for a variety of reasons, but one common example is a query that triggers a lengthy PDT rebuild.

To diagnose these issues, you can run the query profiler to identify these long-running queries. Once identified, you can consider ignoring these dimensions, excluding the explore, or otherwise modifying the dimensions to speed them up.

To run the query profiler, pass the --profile or -p argument when running SQL validation:

spectacles sql --project my_project --profile

By default, Spectacles returns information on queries that run longer than 5 seconds.

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

| Type      | Name                            | Runtime (s)   | Query ID   | Explore From Here                                      |
|-----------|---------------------------------|---------------|------------|--------------------------------------------------------|
| dimension | users.city                      | 6.3           | 1919       | https://spectacles.looker.com/x/pPPjILaHzUzHnTH0sXJuJ3 |
| dimension | users.id                        | 5.3           | 1931       | https://spectacles.looker.com/x/BZHuziOJYrnDfHGpvMi6BJ |
| dimension | users.state                     | 5.1           | 2047       | https://spectacles.looker.com/x/U54sjv1c3mB6lQKyWmY0rh |

You can configure the time cutoff for returned queries with the --runtime-threshold argument. For example, here Spectacles would only return queries that ran for 30 seconds or longer:

spectacles sql --project my_project --profile --runtime-threshold 30