Harness: Using SQLMesh to Power their Business Insights
Alexander Butler came across SQLMesh soon after its initial announcement in March of 2023. He immediately found the GitHub repository and liked what he saw which inspired him to dig further.
Stats at a Glance
- Warehouse: BigQuery
- Warehouse size: ~10TB
- Number of Models: 500-1000
- Previous Transform Tool: dbt
- North America
- harness.io
Alex works for Harness, a software delivery company that helps teams manage the entire software delivery life cycle. Harness can handle CI for running tests, feature flags, cloud cost management, and many other modules so you can pick and choose what's suitable for your team. Alex was the first and only data engineer on the team responsible for creating ETL pipelines, transforming data, and doing analytics for the go-to-market side of the business.
When Alex joined, all their data pipelines were built on the Segment platform, which forwarded data to BigQuery. It was very early in the company’s data infrastructure development and part of Alex’s job was to design and build a new system that was both reliable and flexible enough for Harness to use for years to come. With what they had, Alex recounts,
“…we were kind of scraping by. So I was tasked with designing the entire system from choosing the technologies we were going to use to extract and load data to the technologies we're going to use for transforming the data…”
Alex pointed out that this was a double-edged sword. He had the freedom and power to pick from the latest tools, but he also had the burden of setting the company’s future workflows: a mammoth task. He needed tools that maximized efficiency and minimized the complexity of setting up his pipelines.
He actually started by using dbt.
“I was using dbt to transform data and, generally speaking, I initially thought it was a very powerful tool. It was very useful and the best at what it does in its simplicity. I had generally positive opinions, and to dbt's benefit, there are ways in which it was flexible. However, that flexibility leaves you in a situation where there are pros and cons. And in the honeymoon phase of the Dunning-Krueger, I felt like, wow, I could probably hack up whatever I want here for any situation.
And this is the moment, a preclude, before you see there might be greener grass on the other side or other angles to solve the same problem. So I was using dbt and I definitely hit pain points, and I had to do my fair share of hackery to work around it… from managing artifacts and deferral to better incremental loads; but it felt like I was building a house of cards, and I thought there had to be a better way. ”
One of his pain points was excessive model build times, where specific model executions would take 30 minutes! This wasn’t a crazy cross-join but rather a shortcoming of dbt’s one shot incremental approach combined with over-leveraging recently GA’d BQ JSON support.
Fast forward to SQLMesh: once he saw its approach and started
understanding incremental models, he realized the tool's potential.
I started to slowly open my eyes and say ‘okay, there's a much better
way to do this and we've been accepting the status quo because a lot of
us who use dbt just don't know the better engineering/data principles
that a framework can catalyze.’
Alex was wrangling large, complex tables – 20 million+ records and 300-500 columns. However, the real challenge was that their large tables were unbounded, so there was no telling how much it could have grown from one query to the next.
Harness has tens to hundreds of terabytes of data across various tables. Loading, transforming, and downsampling that data to something reasonable — and then interpreting the results — is no small feat.
SQLMesh’s built-in virtual data environments, plan and apply flow, and CLI
interface sold Alex on SQLMesh as the future of data transformation. With
dbt, blue-green deployments were error-prone and required a lot of prep:
saving a manifest.json artifact, pulling that into his CI jobs, and
figuring out state changes himself. SQLMesh eliminated all of that:
With SQLMesh, because it uses a state database out of the box, I no
longer had to think about any of that. I would just run 'plan dev', make
changes, then 'plan prod' and then it would just do the changes without
recomputation.
SQLMesh saved Harness both money and time. It helped Alex cut their
BigQuery spending by 30-40%
just because of how inefficiently dbt was running our models.
But
on the human side of Alex’s job, a more meaningful number was the increase
in his productivity. He can now iterate very quickly, with full confidence
in the data’s correctness.
And those troublesome models that took 30 minutes to run on dbt? SQLMesh
brought a more than 80% reduction in model build time
without a doubt, for those specific models.
In fact, Alex is now
able to work effectively right up to 10 minutes before a meeting, knowing
that when he promotes changes to prod they will not only be ready in time
for him to present his findings but that it will only
take a few seconds to update all these pointers, that all my audits are
running the way that they're designed to be run, etc…
At Harness, the company's most viewed dashboard is now built with the
power of SQLMesh in the backend handling its transformations. Alex
stressed the importance of correctness in this dashboard, as it outlines a
pulse on really important numbers.
SQLMesh gave Alex exactly what he needed to succeed: a tool that maximized efficiency and minimized complexity. The data pipelines at Harness are future-proofed because what was built is now scalable. In fact, the deployment of SQLMesh on Alex’s business analytics side was so successful that SQLMesh is now being expanded and adopted across more functions in Harness. We are scaling SQLMesh quickly, bringing in billions of rows of data successfully and efficiently. He credits SQLMesh’s reliability as part of his success in pulling off the mammoth pipeline redesign project:
“…the reliability that SQLMesh provided to me during my iteration cycles strongly helped the company and the consumers gained confidence in the datasets because of the sheer amount of time that dataset existed without blatant errors or exploding joins or missing data all while adding features (columns). I think it just builds up this huge amount of trust in the data, and it really helped make this project successful as the logic got more and more layered.”
Alex’s job at Harness started with a monumental task: design and build a data system that manages and runs complex transformations and can scale up to hundreds and hundreds of terabytes of data—and do it as a team of one. With these massive datasets and goals to move towards more advanced analytics, Harness plans to eventually coalesce SQLMesh across all its projects to understand the complex lineage it has across teams.
SQLMesh’s efficiency allowed him to build fast, and its reliability gave him confidence that doing so wouldn’t risk the system’s quality. His choice of SQLMesh solved an immediate pain but also set Harness up for a reliable data future, with scalable pipelines, cloud cost reduction, and improved developer effectiveness. And finally, the stakeholders see the results: a consistent flow of accurate, timely data.