Last year Fivetran released the free Wizard for dbt Core™* extension for Visual Studio Code. This extension helps analysts write more efficient data models in the CLI. And today we are excited to announce, the Wizard is now compatible with Snowflake as well!
Available in the VSCode marketplace, the Wizard for dbt Core helps data analysts optimize their modeling by providing: auto-completion of macro, model and source references; error highlighting and suggestions; SQL to ref conversion; and installation of dbt™ packages with the click of a button.
Read about the extension in full detail here.
Overview of VSCode extension for Snowflake
In order to turn on the Snowflake compatibility, follow these directions:
- Click settings icon
- Under 'Extensions' header, find Wizard for dbt Core
- Click the checkbox for Snowflake support
We here at Fivetran fully believe in the Wizard’s utility. In fact, Fivetran’s Analytics team has utilized the Wizard to help us with our BigQuery data for some time now. Here are the ways its functionality helped us create more effective data models.
Streamlining revenue reporting with macros
As a consumption-based company, our sales leaders need to understand how our pricing impacts our revenue. This requires forecasting and modeling possible customer consumption against discounts and pricing curves, which is ultimately a very complex data modeling task.
With our VSCode extension, the Analytics team was able to tackle this revenue forecasting while avoiding repeated code in our revenue models. There was a set of table joins we were using repeatedly with slightly different conditions in order to model different pricing scenarios. This led to a lot of repetitive, confusing code.
To resolve this, I created a macro to replace each join where we could input the different conditions depending on what scenario we were modeling. With the Wizard, I was then able to preview my query, which allowed me to easily test the outputs of the macro. I could quickly generate a query preview showing the macro results and look at the code versus query output side by side. This way, I didn’t have to run the code, review the output in the destination, identify errors, resolve them in the code, rerun the macro and continue this process on a loop.
The extension also highlighted other errors so I could see and resolve them without having to run the model.
This helped me cut down on development and QA time. Sales leaders get the insights they need in a more timely and accurate manner. Plus, for engineers, our code base is now simplified making management and future development easier. Everyone ends up winning when you have more efficient models.
Tackling more complex data-driven problems
As our business grows, so too does our data warehouse. While having more data points is a boon for reporting, it also makes it more difficult. We have a significant number of specific reporting requirements that require complex data modeling. Tackled in a traditional manner, standalone models would quickly become overwhelming, leading to inefficient and repetitive code.
Fortunately, dbt lets us create modular data models. By creating nested models, we can reuse modeled data in multiple places, which decreases complexity and eliminates a lot of repetitive code. The tradeoff to this approach is that you often need to search through multiple layers of models to QA your work, or figure out where a calculation occurs.
And that’s where the Wizard helped us streamline our processes. The extension allows us to cmd+click any ref function and open the referenced model’s definition in a new VS Code tab. Or we can cmd+alt+click and open that model in a split tab to the side. This lets me quickly trace the lineage of a column in a downstream model through all of upstream models back to the original source table.
Working with modular data models for complex reporting needs is now easier. I have increased observability into my lineage and can make more informed modeling decisions. This shortens development and QA time by allowing me to quickly view upstream model definitions. Stakeholders, again, get the reporting they need more efficiently.
Become a data modeling wizard, with Wizard for dbt Core
If you too want to tackle complex data problems at scale, then download the free Wizard for dbt Core™ Visual Studio Code extension here — now available for Snowflake.