How to validate queries with DBT?

user_324315 0
Viewed 663

I am using DBT 0.19.2 with DBT-Spark and I am wondering if there is a way to validate the queries for some eventual "syntax error". Let's say a query introduce a typo like zelect * from... instead of select * from.... If I just run dbt compile this won't be spotted as only the Jinja compiling phase is considered.

The only way to see if the query is valid, AFAIK it is using dbt run which actually will forward the query to the Spark server, run it and eventually crashing later on cos of the typo.

Is there any other way to spot errors like this without running the query?

dbt
1 Answers

@Randomize,

Fundamentally, what it sounds like you're asking for is: "What can I use to correct / error-check my sql functionality before running it."

And fundamentally, that's not what dbt is. It's a transformation engine for sql statements. Not a linter. If we lookup that definition of a linter or linting, you'll find something like:

Linting is a process by a linter program that analyzes source code in a particular programming language and flag potential problems like syntax.

So to answer your question strictly from a dbt product perspective, I doubt dbt would consider linting in scope for their project.

Instead, I'd recommend looking into some google results for SQL linters that may work better with your environment / ide etc. Ex.

Example of how a tool like this would catch errors like what you described is here:

SQLFluff Sandbox Example

But that quickly becomes a "tool-recommending" conversation which I would re-direct from stack-overflow to a more appropriate venue like:

Edit: Also, not trying to be condescending by any means! Just trying to make an easy on-ramp for non-technically backgrounded folks (which the dbt community has a lot of!) who may not be familiar with linters, parsers, etc.

Edit 2: As part of dbt Coalesce annual conference this week - Fivetran, the ELT provider has released a language server extension for visual studio code which provides some of the above requested functionality starting with the BigQuery warehouse / adapter. Hopefully more adapters (including spark) will get built and released next year?

user_30733400
Related