I want to write about how the Model Timing tab helped identify a long running model that was fixed

[bennieregenold7]

Core Thesis

• Using Model Timing to find a model bottleneck
• Go through the steps required to troubleshoot, optimize, test, and deploy the code

Specific Target Audience

Those that have long running jobs and may not know how to best approach getting the run times down

Supporting Example Use Case

Us!! See the mock intro below for how we addressed fct_dbt_invocations

Narrative Arc

• Start with how big our project is, and why that makes it hard to visualize run times
• Model Timing tab to the rescue!
• Explain how finding the long-running model is just step 1, next up you have to fix it
• Walk through identifying the possible performance gains using Snowflake profiler
• Show how we tested out theories and compared run times
• Touch on how we tested the change to make sure things were still looking good
• Mention the deployment process
• Save the fun for the end...the $$$ saved with this change (specifically how Cloud providers make it possible to assign a $ figure to improved run times)

Sample Intro

The dbt Labs internal project is a beast. Our daily incremental job that runs 4x/day invokes over 1,700 models, and used to run for nearly 3 hours utilizing 8 threads. Sifting through the run to find bottlenecks would be incredibly difficult without the Model Timing tab in dbt Cloud. By showing run times in a graphical interface it quickly becomes apparent which models are slowing a run down. Here's a quick example of our incremental job before a fix was applied to our longest running model:

As you can see, it's pretty easy to identify the model that's causing the long run times. The model fct_dbt_invocations takes, on average, 1.5 hours to run. This isn't completely surprising, it's a relatively large dataset (~5B records) and we're doing some intense calculations within the SQL. However, when it came time to add some new metrics to that model it felt like a golden opportunity to revisit how this is being built.

After refactoring this code, we ended up with a new incremental model named dbt_model_summary that took the bulk of the processing out of the main fct_dbt_invocations model. Instead of recalculating this complex logic every run, we pull only new models, and run that logic on the smaller subset of those records. The combined run time of the new dbt_model_summary and fct_dbt_invocations is now ~15-20 minutes, a savings of over an hour per run!

[bennieregenold7]

tagging @dbt-labs/devhub on this idea

[barryaron]

Also don't want to scope creep further -- I think it's great to be focused to one specific use case. But an option at the end would be: re: $$$ saved --> is there more we would want to add to a broader "benefits" section. Meaning do we benefit (beyond cost) to getting this done faster? If not do we want to allude to other organizations benefiting from hitting their SLAs? Need to think about this one.

[bennieregenold7]

Check if it's kosher to include any screenshot of our timing tab - seems it should be fine, most data model names are hidden

This is actually why I didn't show the after shot in this. It makes way more model names visible, and I wanted to get approval before I did that lol.

Do we want to include the point on Enterprise vs. Team -- I think we can add Murphy's point to the cost savings section ("Having dbt experts helping you configure your environment can pay for itself")

Yes! I think that should certainly be included as part of the article.

Also, noted on linking out to the docs, I'll make sure that's included here.

Also don't want to scope creep further -- I think it's great to be focused to one specific use case. But an option at the end would be: re: $$$ saved --> is there more we would want to add to a broader "benefits" section. Meaning do we benefit (beyond cost) to getting this done faster? If not do we want to allude to other organizations benefiting from hitting their SLAs? Need to think about this one.

Excellent point! I think the final section of the article can enumerate all the benefits, including monetary, time savings (can add new logic without hitting a time limit issue), ability to focus on more improvement now that the timing tab is less dominated by a single model, etc.

[barryaron]

Amazing - I think BTW that we could always blur out the model names in a final shot if it is not kosher but still show how much the configuration changes

[bennieregenold7]

ah, yeah that will certainly work. I can just show the two models in question and blur the rest.

[bennieregenold7]

added the new image showing the after for this

[tacastillo]

I'm just here to say that I'd love to hear any and all insights/lessons learned from building out dbt Labs' internal dbt project. whether as docs, blog posts, or talks.

[bennieregenold7]

welcome to the party! I'm hoping you'll find this article very informative, and I know there are other articles in the works around our internal project. More the come!!