Building the ultimate orchestrator for dbt Cloud
How to fit dbt Cloud and Orchestra into your data architecture
Foreword
Some of the biggest pains we see facing data engineers and analytics engineers is that they spend too much time maintaining boilerplate infrastructure, and still have no visibility into pipeline failures.
It means you’re constantly fighting fires and don’t have time to focus on building. What’s worse is that the business doesn’t trust the data.
At Orchestra we’re building a Unified Control Plane for Data Ops. A Data Status page if you like, with some incredible features to give data teams their time back so they can focus on building. You can try it now, free, here.
Introduction
Over the last few years we’ve seen a huge rise in analytics engineering that has transformed our industry.
From the domain of IT, where knowledge of how data flows, where data resides, and what is used for can easily be gate-kept by a select few, analytics engineering has allowed the Business (Capital “B”) to blow open the door to the database and start actually using data for good.
By lowering the barrier to entry for writing SQL-based data pipelines, IT practitioners have been able to offload the burdensome task of codifying business logic with software engineering best practices, which has rapidly accelerated the rate at which businesses get value from their data.
In no small part, dbt Labs has helped the industry enormously here. dbt Cloud provides some incredibly helpful features for analytics professionals new to the world of software engineering to help us get up to speed
A browser-based IDE that is specifically built for writing dbt models with best practices built-in
A scheduler for running pipelines
Slim CI, for ensuring changes are tested before they are pushed to production, and of course
The dbt and dbt-fusion projects themselves
However something we’ve seen is that analytics engineering cannot survive without data engineering. While the transition from ETL to ELT might have opened the doors to the warehouse, the doors to the other rooms in the enterprise architecture mansion remain firmly shut.
I’ve always wanted to build a world where someone mildly technical, curious, and willing to learn can easily build end to end data pipelines within a scalable architecture.
This isn’t the same thing as wanting to build a platform like Talend or Informatica or one of these “Legacy players” that force you to do things a certain way. There are two nice empirical results of working in data:
It moves quickly — so you should remain flexibility in your architecture
Data Pipelines are complicated so you should keep things modular — which also means you should remain flexibility in your architecture
Which means we need to find a way to democratise the building of end to end data pipelines outside of just dbt and data modelling.
That is why we’ve been investing so much in our integrations with dbt, and I’ll share a bit more about these below.
Warehouses → Data Tools
dbt gives data teams an easy way to connect to any data warehouse.
But Data Teams often only use one warehouse, like Snowflake or Databricks.
What happens if you have a more complicated flow?
Let’s say you use the classic Modern Data Stack Pipeline. You have some ingestion tools you need to connect to, then once the ingestions are complete, you want to run dbt, and then finally you want to action some kind of Dashboard.
In today’s age, you can replace “Dashboard” with “AI workflow”, of course.
dbt doesn’t give an easy way to connect to all of these, so at Orchestra we built over a hundred integrations so you can easily connect to the entire stack without having to learn data engineering “bottom-up” and build all these integrations yourself in-house.
Is dbt an orchestrator?
Yes! dbt is an orchestrator!
With dbt you can trigger and monitor the following processes
Synchronously send SQL Queries of certain types to your data warehouse
Write python code that is compiled to SQL that can also be sent to your data warehouse
Run simple HTTP requests wrapped in declarative syntax to do things like trigger Power BI Refreshes
Typically, we see a few other features being added to an “orchestrator” (however you want to define it) these days
Complex scheduling capabilities (see below)
Ability to run any code
Integrations with tools
Power for granular alerting
Metadat aggregation
Support for AI-based workflows
Observability and Monitoring Capabilities
Fine-grained permissions and role-based access control
Which means typically, people do not try to use dbt as a company-wide orchestrator because it wasn’t really made for that.
For example, something many companies do is run python scripts, or just scripts.
You cannot use dbt to trigger or monitor an arbitrary script — unless of course it starts with the letters dbt !
Cron scheduling → Any scheduling
dbt works nicely with a cron scheduling paradigm — running things on a cadence.
However, when data teams scale there are many scenarios where this scheduling needs to handle more use-cases.
Event-based scheduling: when certain data arrives, trigger a dbt run with specific parameters to materialise recent data
Sensor-based scheduling: when we don’t know when data will arrive, create a “check” (or sensor) that runs and can tell us, and use that to kick off a dbt run
Non-linear scheduling: a type of sensor based scheduling, where we need to run dbt models after multiple other pipelines have completed at unspecified times (our vendor will send us the data today, at some point, but we don’t know when)
Calendar-based scheduling: in large enterprises in regulated industries, calendar-based considerations are imperative to account for things like national holidays. This means being able to select or exclude dates from a calendar is imperative, or have an integration with a database that itself contains the calendar
For Analytics Engineers and Data Analysts to get this functionality, an enterprise scheduler like Tidal or a legacy Orchestration framework like Airflow needs to be learned.
It’s like being told you have to learn how a plane works in order to catch a flight. Completely unnecessary, overkill, and a waste of time.
At Orchestra we built out all of these custom triggers in an easy-to-use way, so anyone building pipelines has complete flexibility in how things are triggered.
No Conditionality or Branching → Branching
Sometimes you might want to change what you do depending on what happens in a data pipeline.
For example, let’s say you’re retraining a machine learning model that calibrates how much you can spend on a google Ads campaign based on the latest data.
Google Ads data is kinda funky. Which means sometimes, the latest day of data just isn’t there. You could easily catch this test using a dbt test like:
select * from google_ads where date >= current_date
If no rows are returned, the test fails.
If the test fails, you might not want to simply not take any action. You might prefer to deploy your “backup” machine learning model, that ensures your spend doesn’t skyrocket in the wrong direction. To do this, analysts need branching.
Branching or conditionality describes an orchestration pattern where we say the graph has contingent paths. The paths are contingent on specific things occuring.
In this example, if the dbt test succeeds then the top path is chosen. If it fails, the bottom path is chosen.
dbt does not support branching or conditionality, which means often companies fall back to a purpose-built orchestration tool to get this functionality.
We’ve spoken to thousands of analytics engineers and here are some of the common use-cases we see for analytics folks for branching the DAG:
Cost-Aware Processing
Incremental vs. full loads: Only run smaller incremental jobs during peak hours, saving heavy full refreshes for off-peak times.
Warehouse tiering: Branch jobs to use different compute warehouses depending on urgency (e.g., XS for ad-hoc queries, L for critical production runs).
SLA-driven choices: Prioritize critical business metrics when deadlines are tight, deferring non-essential transformations until resources free up.
Stakeholder-Specific Outputs
Audience tailoring: Branch logic so Finance sees granular transaction-level data, while Marketing only gets aggregated campaign metrics.
Frequency control: Generate daily dashboards for leadership, but only weekly or monthly extracts for external partners.
Format variations: Push results into BI dashboards for internal teams, while creating CSV/Excel exports for less technical stakeholders.
Failure scenarios
Safe testing: Run Tests if Pipelines fail instead of materialising downstream outputs
Cost-driven Quality → Fractional Tests
dbt is a brilliant framework for writing data quality tests. With a few lines of .yml you can prevent bad data ever reaching your customers
version: 2data_tests: - name: assert_total_payment_amount_is_positive description: > Refunds have a negative amount, so the total amount should always be >= 0. Therefore return records where total amount < 0 to make the test fail.
However, this is an expensive approach. Every test you do sends queries to your warehouse, which incurs more cost.
One discussion I remember with someone I used to work with went like this.
Me: “The unique value for the row gets derived from the timestamp and the ID; why can’t we incrementally test the column as it goes in? Then as long as we know all the old tests pass and the current tests pass every row in the table will be unique”
Colleague: “That’s not how the algorithm works. There is no guarantee the unique key will be unique across the new data and the existing data. You need to fully scan the table every time”
Which is expensive, doing afull scan, every time you get new data. We wrote this guide for dbt testing that highlights some of the problems associated with running every test willy-nilly on every table. It’s expensive and slow.
The other interesting thing we realised is that often data quality errors aren’t really data quality errors at all. Consider this.
Someone changes a column upstream
The ingestion job fails
The dbt jobs fail; there is no new data
The data quality test alerts — no new data in important table
Data Analyst spends an hour triaging after Head of Sales says the data looks off, and eventually traces it back to Mr. Someone in Step [1]
This entire flow could have been avoided if Analysts were in the orchestration tool. They would realise immediately that a column got changed and an error occured.
That’s why with Orchestra, we built a framework that helps us get closer to the $1:
With Orchestra, you can:
Identify errors early: stopping pipelines immediately and easily starting them from the point of failure
Run Data Quality Tests on Iceberg Tables: by leveraging our integration with our Partners Bauplan. This means you test at the $1 stage and Bauplan’s arrow capabilities mean the DQ testing is all done in-memory so it’s free
Run anomaly detection: easily identify long-running jobs and tests to proactively test for DQ ahead of errors
Run tests on a schedule outside of dbt: learning dbt can be a tricky thing for folks looking to easily run Data Quality Tests. In Orchestra, we have a number of DQ tests you can run outside of dbt, as well as a first class integration with dbt
This makes Orchestra and dbt a match made in heaven for monitoring and proactively preventing data quality. You go from having a few basic tests in dbt to a full-on enterprise end to end data quality framework.
Metadata Aggregation and End to End Lineage
While dbt Cloud automatically does things like flatten artifacts and gives lineage for dbt projects, data teams often find themselves relying on other important metadata that sits in different places.
For example, having a separate observability pane is nice when you need to bring in logging data from ingestion ELT scripts or perhaps if you have multiple teams using different flavours of dbt.
This is critical for debugging root causes of failures, and ensuring the dreaded “Hey the numbers look off” requests are kept to a minimum.
With Orchestra, we built a proprietary “metadata layer” for dbt and other metadata. We automatically gather metadata from the entire data stack and clean in into a format that works really nicely with dbt data.
dbt code → any code
Something we love about dbt is that it allows you to send queries to your warehouse in a structured way.
Something we would have LOVED in dbt is if you could write ANY sql and indeed, ANY CODE.
That’s why with Orchestra we augmented dbt so we could run any code in the Orchestrator. Packages like Airflow are written in python and run on servers, which means you can basically run any code (including dbt code) within them.
You can do the same thing in Orchestra, which allows you to blend SQL (in dbt core or Cloud), Warehouse-specific SQL dialect commands (e.g. Snowflake Tasks or BigQuery EXPORT commands) and python, bash and so on in a single view.
Conclusion — dbt is even more powerful with Orchestra
We all remember our first dbt run . It was the keynote headline the first time I ever went to Coalece and I think the sentence rings true today.
As more and more of us embrace data engineering as a whole, dbt Cloud is a brilliant starting point for anyone looking to run dbt in a best-in-class way.
For advanced analytics teams, there is a clear need to augment dbt Cloud with tools like Orchestra to really create scalable data architectures with robust data pipelines that are easy to debug, easy to maintain and quick to build.
Over the last couple of years, we’ve had an immense amount of success with different companies using this pattern of dbt Cloud for Analysts / SQL development and Orchestra for the overarching control plane:
Graniterock slashed maintenance time by 50%, doubled the number of Data Products and were easily able to identify root causes with Orchestra and dbt Cloud
Hellmann built their enteprise data stack in record time using Orchestra, dbt Cloud, Snowflake and AWS
Akebia slashed costs and TCO by 80% moving away from legacy tools to a combination of Orchestra, dbt Cloud and Snowflake
We’re excited to keep pushing the boundaries of analytics and empowering analysts, engineers, and everything to do more with Data and AI.
Until next time!
Learn more about Orchestra
Find out more about Orchestra
Orchestra is a unified control plane for Data and AI Operations.
We help Data Teams spend less time maintaining infrastructure, make them proactive instead of reactive, and ultimately win trust in data and AI from the Business
We do this by consolidating Orchestration with monitoring, data quality testing, and data discovery. You don’t need an observability, lineage, catalog etc. with Orchestra.
Check out
This is interesting, I wasn't aware Orchestra could be integrated with dbt-core.