My Brush with Analytics Engineering
Tags: dbt, vscode, analytics, sql
Published: 2022-05-14 09:05:32

**TLDR; dbt-cli + VS Code + [VS Code dbt-related extensions](https://marketplace.visualstudio.com/search?term=dbt&target=VSCode&category=All%20categories&sortBy=Relevance) + [gitpod](https://gitpod.io/workspaces) = [best online dbt development workflow](https://github.com/butchland/dbt-bigquery-gitpod-config/blob/main/quickstart.md)!**

![gitpod vscode editor with dbt](https://github.com/butchland/dbt-bigquery-gitpod-config/raw/main/images/term7.png)

## Introduction 

To give some background, I have been a longtime programmer, and have worked on all kinds of enterprise software systems for various verticals (aka Java enterprise systems). I have worked with all kinds of RDBMSes over the years (Oracle, MS SQL Server, Postgres, etc.) and while I am not a DBA, I considered myself somewhat proficient with SQL (including knowing how to use the native query optimizers and optimizing ORM queries).


Boy oh boy, did I have a lot to learn when confronted with the _Modern Data Stack (MDS)_!  Instead of an ad hoc, almost mystical process of building queries to visualize the data, now we had a clear template to create a pipeline of SQL data transformations from standardization, deduplication, materialization and joins and other steps that makes it easy to create dashboards and other visualization frontends. I also learned about CTEs (Common Table Expressions) that make SQL more maintainable and window functions that make it more powerful. I loved that part of the MDS development workflow.


## 10 Things I hate about...

However, there were also some things I hated. One was having to use a [web console to run queries](https://console.cloud.google.com/bigquery) (despite some cool stuff like query syntax validation and schema aware autocompletion). The other was not being able to use version control. The SQL queries were stored in an opaque storage location that was accessible only through the web console. Other stuff I didn't like was having to manually run queries to materialize views after updating the SQL and having to check the downstream views to see if they broke. 


## Towards a better workflow

I know now that a lot of these issues could have been dealt with by [dbt](https://www.getdbt.com/), except for **ONE**: having to use a web console to build and run queries - even **dbt cloud** provides a [web IDE] (https://docs.getdbt.com/docs/dbt-cloud/cloud-ide/the-dbt-ide) which is a web console app to build and maintain SQL views and tables as well as run queries. 

Of course I also know about the [dbt cli](https://docs.getdbt.com/dbt-cli/cli-overview) - I love _dbt cli_ (aka _dbt-core_)!!! All I needed was a good editor - and right now, **VS Code** is the _editor-du-jour_ for folks like me. So I was so happy to find some [VS Code extensions](https://marketplace.visualstudio.com/search?term=dbt&target=VSCode&category=All%20categories&sortBy=Relevance) that made the dbt development workflow a joy for me -- specifically the [vscode-dbt-power-user extension](https://marketplace.visualstudio.com/items?itemName=innoverio.vscode-dbt-power-user) which provides lots of cool stuff too long to mention, the [vscode-dbt extension](https://marketplace.visualstudio.com/items?itemName=bastienboutonnet.vscode-dbt) which provides really useful dbt and sql snippets, and [sqlfluff](https://marketplace.visualstudio.com/items?itemName=dorzey.vscode-sqlfluff) -- the sql formatter and linter. 


## Previewing SQL Results

However, there was still one piece missing that would allow me to be weaned from any sql web console -- the ability to preview sql results. In the dbt cloud web IDE, this is known as [preview sql button](https://docs.getdbt.com/docs/dbt-cloud/cloud-ide/the-dbt-ide#compiling-and-running-sql). Whenever I used VS Code with the dbt cli, I still had to login to my cloud DWH web console and run the compiled queries in order to preview the results -- not a good developer experience IMHO.


## VS Code, dbt, and BigQuery

So I decided to built it myself and here it is: the [vscode-dbt-bigquery-power-user](https://marketplace.visualstudio.com/items?itemName=butchland.vscode-dbt-bigquery-power-user) -- I know that's a mouthful, haha. It's a fork of the [vscode-dbt-power-user](https://marketplace.visualstudio.com/items?itemName=innoverio.vscode-dbt-power-user) extension that adds a preview of the sql results (as well as download the results in csv, text and json formats)
![query runner preview](https://github.com/butchland/vscode-dbt-bigquery-power-user/raw/master/images/query-runner-screenshot.png)

But there's a catch -- right now it only supports **Big Query**. But with some elbow grease, I plan to support all the other major cloud DWHs as well as Postgres (and maybe SQL Server?) databases. I also plan to enhance my vscode extension to add the other stuff like analyzing the _dbt manifest json_ file and extracting the schema from the underlying tables. Plus of course incorporating any other cool stuff the original **vscode-dbt-power-user** will be adding (my extension is a fork of the **vscode-dbt-power-user query**).

What's the experience like? It's not perfect -- it doesn't provide the same level of smart autocompletion that native DWH sql cloud consoles have -- but most of the time I just do a star query to get all the column names and copy the results over to a temp file, so its not a big deal. But at least for me, its a 100 times better since I can now do with SQL all the other stuff I do with other programming language files like JavaScript and Python -- version control, diffs, linting, autoformatting etc. So, in my so humble, but of course **BIASED** opinion -- **it's a total game changer!**

## VS Code, dbt and Big Query Online 

**But wait, there's more**! I got interested in what would make dbt more effective in improving the lives of analytics engineers -- and learned about the [Analytics Engineers Club](https://analyticsengineers.club/) which was founded by Claire Carroll (formerly of dbt labs) and and provided training to grow data analysts into effective analytics engineers. And I agree with their viewpoint that at some point, analytics engineers need to learn to use the _dbt cli_ and be comfortable with using the terminal as well as the other software engineering practices. 

But as a teaching company, they weren't really happy with having to deal with installation and configuration issues that their students had to undergo every time they enrolled in a course and [sought to look for alternatives](https://analyticsengineers.club/teaching-the-real-tools/). As of late, their solution has been to provide to use a desktop VS Code editor connecting to pre-provisioned EC2 instance. 

But I think I've found a something BETTER! [Gitpod](https://gitpod.io/workspaces) is the solution! It provides an **VS Code** editor online experience so close to my desktop experience that I pretty much would use it when I can't use my desktop VS Code editor -- and with very little friction compared to a desktop experience. No need to pre-provision instances or even install VS Code on your desktop.

So I built a project that would allow you to create a pretty good dbt work environment with a VS Code editor online -- the only thing you need is a dbt project hosted on github. If you want to try it out [click here](https://github.com/butchland/dbt-bigquery-gitpod-config/blob/main/quickstart.md) for more _(note: of course it installs and leverages my [vscode-dbt-bigquery-power-user](https://marketplace.visualstudio.com/items?itemName=butchland.vscode-dbt-bigquery-power-user) extension)_!

If you find the extension useful, don't forget to rate the extension or star the github repo. And if you have suggestions or experience problems with the extension or the setup, please file a issue in the project's issue tracker!