How to mimic dbt Cloud "run sql" on the CLI

Workflows
#1

Hello,

After hearing so much about dbt, I’m very excited to finally have a chance to try it at my workplace. This is my first week spending time on it, and I have a question:

When using dbt Cloud, there’s a button “run sql” that will run the file I’m working on straight away. If I understand the concepts correctly, this button is both compiling my file and then running it against my database, but running only the query, and not the materialization.

Is there one single command in the CLI that mimics exactly the behavior of this button?

Thanks for the time and I hope this is the right forum to ask questions like that. Or should I actually ask this on the Slack community (that I haven’t checked out yet)?
Fernando

2 Likes
#2

Hey @fernandobrito! dbt’s CLI doesn’t have this functionality as of now. Generally, the dbt CLI doesn’t expect to report results back to the command line…the command line is not a place that most data analysts want to parse through the result set of a query, although you may want that!

dbt Core does bundle in the dbt server, which is under-the-hood what is performing the compile-and-run functionality that dbt Cloud provides. So you could spin up a UI on top of the dbt server that might work for you. There is a decent amount of work involved in making sure that the project is in a good state, though, because once you bring up the dbt server and then change code that it’s plugged into it needs to be told about those changes.

Can you say a little more about how this would fit into your workflow? How would you expect to use this? Is there a reason that the dbt Cloud interface doesn’t work for you?

#3

Hey, @tristan. Thanks for the answer! You raised very interesting points.

The dbt Cloud Develop interface is a great idea and I think it will reduce a lot the friction to start using dbt for our analysts that are not familiar with CLIs, IDEs, Git. I believe that with time, they will naturally learn more and more about tooling, but the transition should be much smoother with Develop. For me, coming from a software engineering background and having spent a lot of time on IDEs, I feel more comfortable developing locally. Especially for long refactoring sessions, structuring my initial project, etc.

That said, I do have some initial feedback on Develop (those are my first weeks on dbt and we are still slowly building our proof-of-concept):

  • It worries me a bit that there’s no auto-saving on the tabs and compiling or running the SQL won’t auto-save either, so I see the risk of analysts working for quite some time without saving their files. Trying to close an unsaved tab on Develop will prompt for a warning dialog, but trying to close my browser or my browser’s tab won’t, when I think it should
  • Some git operations are a bit slower than I would expect (pulling, committing, or even creating a new file). You mention “making sure the project is in a good state”, so maybe that’s related?
  • It would be amazing to keep the state of which tabs I have open (and maybe even the state of my file/project tree on the left-hand side of the page) even if I reload the page

Going back to my workflow, I find it amazing how easy it is to see my compiled model and to run it against my database while working on Develop. For working locally, I might do a simple shell script to watch for file changes on my model’s folder and either compile them automatically, or compile and run them (run outside dbt, using my data warehouse CLI client), so I can have another window open on my second screen that will react for file changes that I do in my local IDE. Or I might try to go even fancier and have better integration with my IDE (IntelliJ), which has good database integrations (for running queries, showing results, etc).

Those are my thoughts for now. Probably once I finish our proof-of-concept and my entire team starts using dbt I will have more feedback to share :slight_smile:

#4

Second this. I also prefer working with the CLI as much as possible and this functionality would definitely benefit our workflow. Ideally, I would like to be able to do all development in local IDE/shell, including being able to see output of any sql models.

#5

Love this. I don’t disagree–my personal ideal-state is to have a client-side application that is wired up to a dbt server (for me, likely hosted in dbt Cloud). That is the architecture that you are naturally gravitating towards as well, so it sounds like we’re on the same page. We are going to be moving in this direction to give users more options from a product development standpoint, and will be very interested in hearing about any experiments you have cooking with wiring up InteliJ (or others!). Might take a bit to get to a fully-realized version of this, but it’s where we’re heading.

Super-cool, thanks for sharing! :smiley: