When implementing the Modern Data Stack (MDS) for your data warehousing and analytics needs, there are many options to consider. At Stratos, we emphasize multitenant cloud architecture, and more specifically, the Snowflake platform, given its many benefits (scalability, low maintenance, and governance, to name a few).
But the core data warehouse/lakehouse platform is just one (albeit large) piece of the puzzle. You also need to figure out how to source the data to your warehouse, how to ensure its quality and model it, and how to share it downstream with other users, systems, and analytical tools in a secure fashion. Then there’s the question of how you will manage the platform and the processes you will put in place that allow it to grow with your organization’s needs. All put together, your environment might resemble something like this:
Diagram provided by Snowflake
Unsurprisingly, there are many different technologies and approaches you can implement to get the job done. Snowflake gives you the functionality and flexibility to build, manage, and schedule most of your pipelines and processes directly in the platform through SQL, Python, and a variety of GUI configurations in SnowSight.
You might be thinking, “Wow, that’s awesome! You mean to tell me I don’t need to spend hundreds of thousands of dollars on integration, analytics, and governance tools anymore?!” This thought crossed my mind, too! However, throughout my MDS journey, I’ve grown to appreciate the different technologies in the Snowflake ecosystem.
Here’s why. Different situations call for different approaches; in many cases, it makes sense to implement complementary technologies in your stack. Imagine you are doing a house project. Sure, there are plenty of times you can DIY, and a hammer and nail are all you need, but sometimes the job will be much quicker and easier if you bring in a professional or break out the power tools.
That’s the inspiration for this blog, the first of a series of “Build-or-Buy” posts in which we walk through different areas of the MDS, review the implementation options, and recommend how we would go about it in different circumstances.
That brings us to our first topic: Data Orchestration Tools
What is Data Orchestration (and why do you need it)?
In the world of data warehousing and analytics, Data Orchestration is what allows you to take all the individual jobs (loads, transformations, exports, etc.) and run them in the necessary order and timeframes to complete your end-to-end process in a repeatable and automated fashion.
It is similar to the role of an Air Traffic Controller who monitors all incoming and outgoing flights (in our case, integrations and transformations) and notifies the pilots with the latest information (logging, webhooks, APIs) to ensure safe air traffic (error-free pipelines) while minimizing delays (efficient performance).
Data Orchestration has taken on an even newer meaning with the rise of the Modern Data Stack, drawing on workflow orchestration tools like Apache Airflow. In the past, an on-premise or early cloud environment might have a traditional ETL (Extract, Transform, Load) approach, using an all-in-one tool like Informatica or Talend.
These platforms build all-encompassing workflows to manage your source connections, data staging, transformations, and downstream integrations. Data Architects don’t need to overthink data pipeline orchestration because the data platform handles executing jobs and ETL dependencies in a sequential flow (e.g. first run mapping 1 to load source file into stage table, then run mapping 2 to join this data to your dimensions and incrementally load it to the fact table). You could kick off these jobs based on timed schedules, completion of previous jobs, or filewatch/eventwaits.
As demand for more data use cases and the data size grows, it becomes necessary to implement a modular data architecture. Consider the following architecture, which is more typical to the ELT (Extract, Load, Transform) approach taken with Snowflake:
- On the left-hand side, we’ve implemented Fivetran as our ingestion tool to load data from various source systems (ERP, CRM, etc.) and file storage locations (S3, SFTP, etc.) to a staging schema in our Snowflake data warehouse
- In the middle section, we’re using a data transformation tool like dbt or Coalesce to transform, model, and aggregate our data in Snowflake to create the star schema tables and reporting views necessary for analytics
- On the right-hand side, we have an analytics platform like Sigma, Power BI, or Tableau that connects directly to your reporting layer in Snowflake, along with Hightouch or Census as your Reverse ETL tool to export data out of Snowflake into downstream systems and file storage locations
As you can tell, this flow is quite different from the Informatica ETL model I referenced before. Instead of using an all-in-one tool, we’re using multiple purpose-driven technology to handle each part of the architecture.
While this approach certainly has its benefits (performance, cost, scalability, etc.), it makes orchestration a bit more complicated. For example, we used Fivetran to load our ERP source data into a Snowflake staging table, but how do we now ensure that dbt and Hightouch pick up the next parts of the process to model and export data for the downstream needs? How can we ensure appropriate centralized logging and metadata aggregation?
Here, we have some options—and as the title of the article implies, we can Build or Buy!
Option 1: Data pipeline orchestration with tool-native capabilities
Sometimes, the simplest solution works best! Let’s say you need to automate the following pipeline to extract data from your CRM, enrich it with other data in Snowflake, and upload these enriched results back to your CRM. This process must run daily at 8 AM sharp, with minimal downtime.
In this case, you would set up a sync in Fivetran to run daily at 8 AM. Then, using Fivetran’s integrated capabilities with dbt, configure the sync to execute the necessary dbt job upon completion to transform and enrich your data. (Note: I recommend checking out the Fivetran documentation for the detailed configuration steps).
Screenshot provided by Fivetran
To finish automating your process, you can use Hightouch to set up a sync to load your enriched data in Snowflake back to the CRM. In doing this, you can configure this sync to trigger whenever your dbt job completes automatically. (Note: I recommend checking out the Hightouch documentation for the detailed configuration steps).
Screenshot provided by Hightouch
Voila! Your end-to-end CRM enrichment pipeline is orchestrated to run every morning at 8 AM. without any manual intervention! This is a linear flow with no complicated dependency graphs.
In an ideal world, you could set up all your pipelines to run this way without needing to custom code anything or pay for another tool. In reality, things won’t always be this simple. You might eventually get the following questions from your business stakeholders:
- How can we visualize and monitor the entire end-to-end process?
- Can we also include information from our ERP system and vendor flat files in this flow?
- Can we return the fully updated data from our CRM to Snowflake for analytics?
- It’s 8:30, and I don’t see my updated data in the CRM. When can we get this resolved?
- We’re looking to take a more hands-on approach to managing our data transformations, can we migrate from dbt to another tool like Coalesce?
What are some limitations of tool-native orchestration capabilities?
This is where we start to see some of the limitations of this approach, such as:
- Rigid order of operations in your data orchestration: While Fivetran can execute dbt, and dbt, in turn, can execute Hightouch, you’re limited to a linear flow. One Fivetran sync cannot execute another Fivetran sync, dbt also cannot execute a Fivetran sync, and Hightouch can neither execute a Fivetran sync nor a dbt job. So, the following pipeline becomes very complicated to automate without some heavy workarounds:
- Not all MDS technologies are integrated: In addition to the examples covered above, there is also currently no direct integration with Coalesce (which I prefer using over dbt for transformations in many cases – read why here) to Fivetran or Hightouch.
- No central location for logging or error handling: While each tool has its own logging and error handling capabilities, they are all separate. So, if a user reports that their data isn’t coming through as expected, you will likely need to log into 2 or 3 different tools to troubleshoot which part of the process failed and why subsequent steps in another tool didn’t run.
This approach will effectively complete the job with minimal cost or effort for more straightforward use cases but will be challenging to scale up when more complexity is added. The following options are better equipped to handle these situations.
Option 2: Code a fully customized solution using an open-source-software (OSS) Workflow orchestration tool
If you need to handle some of the above use cases and don’t have the budget to license another technology, there are some free* options (we’ll get into why the asterisk is there soon). You can implement an open-source tool like Apache Airflow and create containers of Python code for webhooks and APIs to execute jobs in the different tools in the necessary order.
In the more complicated pipeline we described above, you would be able to have Airflow execute your Fivetran ERP and CRM syncs in whatever order needed, then execute the dbt job (when both Fivetran syncs are completed), next execute Hightouch to load the enriched data back to your CRM, and once that’s done, run the next Fivetran sync to load the updated CRM data back to your data warehouse for reporting in Tableau.
That all sounds great, right?!
In some ways, it is, and in some situations, creating custom orchestration is the right option. Custom coding will often give you the most flexibility, but getting back to the asterisk above – this can come at a cost.
This cost won’t come in the form of technology vendor invoices; it will be hidden within your infrastructure (on-premise or cloud server required), development resources (whether internal or external), and total cost of ownership down the line.
Take a look at some example Python code one might use to set up their orchestration.
Unless you already have a Python expert on your data team, this setup can get complex quickly. You will likely need to pay an expert resource to develop this solution. And this won’t be a one-time cost either. As you continue to support this pipeline and add new pipelines for other business areas, you will need to allocate more budget to resources capable of building and maintaining this code, all while trying to manage consistency between processes.
If you don’t have a tight grasp of this, you could easily find yourself in a situation where John from the Sales Data Engineering team is out on PTO this week, and the CRM integration failed. Nobody else can step in to resolve the issue because they can’t easily untangle John’s code, which is structured differently from how Finance and HR run their pipelines, so now the monthly sales report will be created a week late when John is back in and able to resolve the issue. This sounds crazy, but I’ve heard many horror stories from clients that resemble this situation with their legacy processes!
To summarize:
- Increased cost from hand-coding every step in the data pipeline
- Reliance on highly technical platform engineering resources
- No guarantee of centralized metadata and logging – this needs to be custom-built as well
- Additional infrastructure headaches and costs (where does Airflow run?)
So, unless you already have an extensive data team in-house and ready to support your custom orchestrations, what sounds free can quickly add up to hundreds of thousands in annual support costs.
Option 3: Implement a cloud-based Data Orchestration tool: Orchestra
What if you need to manage complex integrations on the modern data stack but don’t want to go the custom route? What’s the alternative?
Until earlier this year, there wasn’t a great option. But with the introduction of Orchestra, the game has changed quite a bit!
Orchestra takes a holistic approach to data pipeline management and describes itself as a “Unified Control Plane for Data Operations”. While the platform’s crux is a GUI-driven, code-first data orchestration platform, it also centralizes metadata and provides alerting out-of-the-box. Data teams use it to build, monitor, and visualize all their data pipelines from a single place.
At its core, Orchestra builds on frameworks like Apache Airflow and abstracts away boilerplates for monitoring and metadata aggregation. It leverages APIs and webhooks to execute all the jobs in your different tools in the necessary order, but with some major differences:
- Orchestra is a fully cloud-based platform: Unlike Airflow and similar tools, where you need to configure everything on a server or virtual machine, with Orchestra, all you need to do is create an account on their website, and you can build and run any pipelines directly in the cloud. So, not only do you eliminate infrastructure overhead, but you also don’t need to spend time setting up Visual Studio or doing PIP installs, which brings us to our next point.
- Code not required: A big reason why we don’t need to worry about installing code management server tools is because with Orchestra, you don’t need to write any code! You log into your account, create a new pipeline, and from there, you can instantly access their fast-growing library of connectors (including Fivetran, dbt, Coalesce, Hightouch, and Snowflake) in an intuitive GUI. So, within minutes, you can create a multi-step workflow to kick off single or multi-branch tasks with all your integration tools and their necessary dependencies. Under the hood, Orchestra provides a .yml configuration (like Coalesce), so you get the best of both worlds.
Compare the Python code from the previous section to this screenshot from Orchestra’s GUI, which was set up in a matter of minutes by simply dragging and dropping the necessary components:
Another benefit worth noting with Orchestra is that you can easily manage all your scheduling, logging, and error handling in one place. In the previous examples, if one part of your pipeline failed, you would need to dig across different technologies to pinpoint the issue—there is no end-to-end, asset-based data lineage.
With Orchestra, all your log and error notifications are in one place. So, if that same pipeline fails now, you can see what step failed in what application and the error message provided by the application to narrow down your troubleshooting.
Now that we’ve covered our 3 main options let’s get to our recommendation…
Conclusion:
When putting it all together, I like to consider a few different criteria when recommending which approach to take, knowing that some will be more important for your situation than others. So, in that light, the verdict here will be a general recommendation, knowing there is no perfect “one size fits all” solution for every circumstance.
Disclaimer aside, let’s summarize the information we covered into the key decision criteria – Software Licensing Costs, Flexibility/Scalability, and Cost of Ownership.
That brings us to our verdict – Buy Orchestra
In most situations, I believe Orchestra is the best choice. While it does incur additional licensing costs compared to the other options, these compare favorably to the $100K + it could cost in the data engineer salaries and infrastructure needed to support the custom approach. Lead and implementation times are also much faster.
The custom option can be considered flexible if you already have a large infrastructure footprint and data engineering team that can absorb these costs. However, Orchestra also connects with services like AWS EC2 and Virtual Machines, which means Data Teams still retain the flexibility of running custom scripts while reaping the efficiency rewards from running Python.
Even then, you will need to consider the lead time it takes to build new custom connections as your business needs evolve, with critical functionality like timeouts, retries, error handling, and so on.
So, if Orchestra covers the tools you need to integrate with, it’s difficult to recommend another option. Even if they aren’t listed in their integrations today, it is worth reaching out to their team to see if a new tool you need is on the roadmap. They operate in an agile model and will often be able to integrate new tools quickly if there is a strong business case.
Putting it all together, here is your updated architecture:
Honorable Mention: Implement an all-in-one ETL tool like Informatica
Lastly, while this post focuses more on the orchestration options specific to the Modern Data Stack, I also want to note Informatica as an honorable mention. While licensing costs have historically been higher for the platform, they have recently begun shifting their pricing to a consumption model that aligns more with the cloud-based approach. Informatica is still a great option for enterprise-size companies looking for an all-in-one platform that can handle ingestion, transformations, reverse ETL, file movements, and orchestration across various sources, targets, and database platforms.
Stratos
Need help getting the most out of your data stack? Whether you want to scale out your existing environment or are just starting, Stratos is here to help! Reach out today to get in touch.
Orchestra
If you’d like to learn more about Orchestra, visit their website or sign up for the Free Tier of the platform today.
Written by Anthony Patullo
