Business process modeling using dbt Cloud from Coalesce 2023

“I’m not here to talk about some lofty strategy…I’m here to tell you how to get shit done.”

Jason Hodson, Director of Data & Analytics at Routable, explains business process modeling using dbt and Looker, an analytics tool. Jason describes the importance of planning models, building them, and using them for business process modeling. He also covers the challenges you could face while data warehousing and how to address them.

Business process modeling with dbt and Looker can streamline operations

Jason focuses on the application of dbt and Looker in business process modeling. He highlights the importance of planning, building, and utilizing models for optimal results. He also emphasizes the need for clear definitions when commencing the modeling process.

"We all know what business processes are. We deal with them…we see them all the time or sometimes we're involved in them." He explains the need for a systematic approach, outlining a “Plan, Do, Study, Act” process for building these models.

Jason also stresses the value of a Fact Stage Matrix–a vital tool used at Routable for planning and executing models: "For every one of those metrics that we defined back in that stage matrix, you'll have one of these little macros." This matrix, he says, is a "living, breathing document" that needs to be updated and maintained as the models are developed.

Balancing data quality and quantity in business modeling

Jason elaborates on the challenges faced during the data warehousing process including dealing with multiple source systems, ensuring data quality, and predetermining time grains. He also highlights the need to adapt to historical changes in processes and address scenarios where stages get skipped during a process.

"The nice thing is, we can template the creation of these models, both from a dbt perspective in our data warehouse, all the way as they come out into Looker for usage, and be able to combine these metrics for effective analysis.” However, he notes that this can be complex and requires an understanding of whether a metric is an event or a snapshot.

Jason also highlights the need for data validation and ongoing refinement of the underlying logic: "Once you get this stood up, and you do the data validation, and you get the logic all worked, and all the kinks worked out, you can crank as many metrics through this model as fast as you want."

The importance of metrics in business process modeling

Jason emphasizes the significance of understanding and utilizing metrics in business process modeling. He discusses various metrics, including occurrence metrics (“How many prospects were qualified?”), volume metrics (“Active pipeline at the end of a given period”), and duration metrics (“How long is our sales cycle from start to end?).

Jason underscores the need to determine which are applicable at the end of the selected period, stating, "Once we understood that flow…we were able to answer those credible questions and be able to provide information to our boards and our sales team about effectiveness..."

He also points out the model’s limitations, like the need for a predefined process and the potential for business process changes to disrupt the model. However, he believes the benefits outweigh the challenges and encourages people to rely on the Fact Stage Matrix, stating, "Regardless of all that, it all comes back to this: the Fact Stage Matrix. That's your takeaway. That's your bible for this."

Jason’s key insights

• Understanding business processes is crucial for data analytics
• Planning, building, and using models are essential steps in business process modeling
• It's important to define stages in the business process and handle data inconsistencies
• dbt can be effectively used for building and validating models iteratively
• Challenges in data warehousing can be managed with proper planning and strategies