Skip to main content

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