Oldcastle Infrastructure Migrates On-Prem Data to the Cloud With Fivetran
- Working with InterWorks, Oldcastle Infrastructure migrates its on-premise SQL Server data into Snowflake with Fivetran after abandoning a months-long internal data-warehousing project.
- Fivetran saves Oldcastle Infrastructure a projected $360,000 over a three-year period.
- Fivetran handles the NetSuite API better than other solutions and loads the data into Snowflake quickly and reliably.
- By joining NetSuite data with SQL Server data, Oldcastle Infrastructure can generate global perspective reports.
- Financial reports that were limited to monthly rolled up figures, and took about ten days to generate, are now presented in up-to-date Tableau dashboards.
- Pipeline: Fivetran
- Connectors: SQL Server, NetSuite, Salesforce
- Destination: Snowflake
- BI Tool: Tableau
- SI: InterWorks
Oldcastle Infrastructure, a CRH company, is an industry leader in engineered building solutions offering more than 16,000 pipe, precast, stormwater, enclosure and building accessory products. Since its founding in 1978, the business has maintained most of its data on-premise but moved a small portion to the cloud a few years ago. To generate effective and comprehensive business insights, Oldcastle Infrastructure decided it was time to migrate all of its data to the cloud.
Building Pipelines Internally Leads to Cloud Migration Challenges
Oldcastle Infrastructure had visibility into its on-prem SQL Server database and its NetSuite ERP individually, but did not have a single view across both ERPs. It couldn’t view transactional, manufacturing or production data collectively. About 90% of its business data was in one on-premise ERP in SQL Server, while 10% lived in NetSuite in the cloud.
In an effort to migrate to the cloud and become more data-driven, the company started a warehousing project focused on gathering the sales data from both ERPs that had been going on for eight months when Nick Heigerick, IT Manager of BI, started at Oldcastle Infrastructure in December of 2018. The team leading the project had spent nearly five months just planning out how they were going to move the SQL Server data from on-premise into Azure cloud. While the process that the team was building could potentially work, Heigerick recognized a scalability issue: “We didn't have the expertise to manage so many moving parts in-house, so we had to decide: Are we going to pay consultants to do this forever or is there a better way?”
Once it was clear that the project wouldn’t work long-term, Heigerick and BI developers Evan Schairbaum and Parimal Shukla, neither of whom had extensive knowledge of the full SQL Server stack, needed to find a new way to achieve a more data-driven culture. “I was basically forced to innovate,” Heigerick says. “Having worked with InterWorks in the past and knowing their reputation in the BI space, I brought them on to help us figure out how to build out our BI.”
Moving Data From On-Prem to the Cloud
Oldcastle Infrastructure chose Tableau as its BI platform and Snowflake as its warehouse, and then brought on SI InterWorks to help it choose a data pipeline tool. Here's how Heigerick explains the process:
We started looking at which ETL tools were innovative enough to handle this migration. This piece of the puzzle had been our downfall in the previous effort. InterWorks recommended Fivetran and it was the first SaaS solution that we found just worked. We thought, This is going to get us to where we need to be.
A reliable NetSuite connector was critical. Heigerick trialed another solution, but found that the NetSuite data loaded very slowly, and he wasn’t confident that it would maintain the API:
NetSuite is constantly changing its API, making conventional pipeline strategies difficult to maintain, especially when it has been, and continues to be, customized. I initially thought I would have to pay someone a lot of money to monitor what happens in the background, but Fivetran keeps up with the API, grabs all of the data, and automatically centralizes it into Snowflake. The first project never even attempted to grab the data. They just didn’t think it was possible.
In addition to providing reliable, centralized data, Fivetran has saved Oldcastle Infrastructure substantial time and money. “If the team had used Fivetran from the start, it would have been successful in weeks,” says Heigerick. “With Fivetran, I replicated all of our data in 10 business days – every table and every field – from both our on-prem and cloud ERPs, which saved us about $360,000 in initial setup and maintenance of our SQL Server and Netsuite connectors.” Oldcastle Infrastructure is considering adding more Fivetran connectors, including ServiceNow, and is also evaluating ways to leverage cloud storage options.
Moving to the cloud with its modern data stack was a critical decision for the business’ future scalability. “Migrating to the cloud allowed us to minimize maintenance and increase stability without sacrificing flexibility," says Heigerick. "It also brings the scalability that traditional on-prem setups cannot attain without significant cost."
Adding Snowflake to the Stack
Heigerick notes that while Oldcastle brought on other turn-key solutions, it decided to move its data from Azure SQL Server into Snowflake to reduce manual warehouse maintenance and administrative DBA tasks:
Snowflake does a lot of the optimization around SQL Server for you with its micro-partitioning and so many other wonderful things. I don’t have to have a dedicated DBA; I don’t have to learn a new coding language; I don’t have to manage APIs. The ability to scale up and down dynamically and the ability to break out load compute from transformation compute, from consumption compute – there is infinite value in that.
Global Reports From a Single Source of Truth
According to Heigerick, the inability to generate global perspective reports was one of the biggest reporting challenges Oldcastle Infrastructure faced:
We couldn’t look at sales for all of Oldcastle Infrastructure in a single report because our data did not live in a single place. No one had figured out how to unite sales from NetSuite and from our on-prem ERP and even if we did figure it out, it wasn’t going to be scalable. I knew that if we didn’t build the right foundation the house was just never going to hold up. We would not be able to innovate fast enough to keep up with the demand for visibility into the data. Now we’ve created a scalable BI solution that is not a bottleneck in our ability to deliver on projects.
While the business was able to build reports around financials in the past, it was limited to monthly rolled-up figures that took about ten days to generate. By the time the business had the results, the numbers were stale. But with Fivetran automated ELT, speed and scalability with Snowflake, and reporting and visualization within Tableau, reports are created much more quickly, with reliable and up-to-date data.
Heigerick says that the reports he built using Tableau are the most popular standardized reports across the business, including a report that shows sales, the cost of goods sold, and margin dollars and percentage, carved out by the five most popular dimensions that the business has, including customer and item:
We’re answering the most common questions that people didn’t have common visibility into, and we’re answering them at a global level. We have transaction-level data updating every three hours and we could have it every 15 minutes if we wanted. It is giving us a single source of truth and allowing us to harmonize processes and identify inefficiencies. We were never going to get here prior to this stack. Now, we have enabled the entire business. We can do whatever we want.
About Fivetran: Shaped by the real-world needs of data analysts, Fivetran technology is the smartest, fastest way to replicate your applications, databases, events and files into a high-performance cloud warehouse. Fivetran connectors deploy in minutes, require zero maintenance, and automatically adjust to source changes — so your data team can stop worrying about engineering and focus on driving insights.
About Snowflake: Snowflake is the leading data warehouse built for the cloud. Its unique architecture delivers proven breakthroughs in performance, concurrency and simplicity. For the first time, multiple groups can access petabytes of data at the same time, up to 200 times faster and 10 times less expensive than solutions not built for the cloud. Snowflake is a fully managed service with a pay-as-you-go-model that works on structured and semi-structured data.
About Tableau: Tableau helps people see and understand their data. The Tableau platform provides the breadth and depth of capabilities that enterprises need, and adapts to your environment with unmatched flexibility and choice, while meeting the toughest governance and security requirements. People love using Tableau because it is both powerful and intuitive. Tableau leads the industry with the most passionate user community, over 86,000 customer accounts, and a commitment to customer-focused innovation.
About InterWorks: InterWorks is a people-focused tech consultancy delivering premier service and expertise in collaboration with strategic partners. Our clients trust us to guide them in approaching their unique challenges, and we steward that responsibility seriously by bringing together the right people and technologies to position everyone for their greatest success. From the science of data to its storage, management and visualization, we can support you in every aspect of your BI strategy. We value laying a strong foundation, and our relational approach leads us to customize solutions that meet your precise needs and empower you to do data your way.