This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Cookie Policy. Click Preferences to customize your cookie settings.
on - edited on by
DRay
As a member of the Solutions team, I rely on several handy troubleshooting tricks to debug numerous queries. I've put a few of my favorites below - feel free to comment below with any additional debugging tools that you like to use!
1. Run portions of a longer query
Sometimes our queries can be fairly long. To narrow down on a specific sub-query/CTE or to ensure results are matching expectations at every step of the query, highlight the desired section of your code. The "Run" button of the SQL editor will turn into a "Run Selected"
Note that the highlighted text must be complete query itself.
2. Make the Query tab your friend
SQL snippets are a great way to keep your business logic centralized. However, if there are errors in a SQL snippet, how are we able to target that in the larger query? The query tab shows the full expanded SQL for the query we are trying to run.
From the example above, it's clear that we are missing a single quote at the end of our snippet.
Note that the query tab also expands out the SQL for any built-in formatters. This is a useful tool to expose what's going on in the backend - and can be leveraged in your other queries!
3. (For users on the Cache or Warehouse), verifying the query is hitting the right source
Verify the source that your query is hitting. This can be found by hovering over the info icon or green check mark found under the "Run SQL" button of your chart editor.
By default, queries follow this workflow:
- First, Sisense for Cloud Data Teams runs the query against the Cache/Warehouse
- If Sisense for Cloud Data Teams encounters reach an error in (1), the query will run directly against your database
- If Sisense for Cloud Data Teams encounters reach an error in (2), it will surface the error message explaining why the query failed against your database
Now, if your query is supposed to run on the cache, and then a syntax error is causing it to fail (1), the query will run against the database and likely return an unhelpful error. To surface a more appropriate error, select "Cache" or "Warehouse" in your database selector (Cache users will see "Cache" and Warehouse users will see "Warehouse".) When you do this, be sure that table names are fully qualified with the database and schema name.
This updates the workflow as such:
- First, we run the query against the Cache/Warehouse
- If Sisense for Cloud Data Teams encounters reach an error in (1), Sisense for Cloud Data Teams will not run the query against your database and will surface the error explaining why the query failed to run against the cache.
The biggest source of errors for these cases are either that (1) the underlying tables used are not cached, or (2) the query isn't leveraging Redshift syntax.
What other debugging tricks have you found useful?
