Data platform migration beyond the tech stack: Patties Food Group

Data teams often find themselves balancing business needs against budget constraints and limited headcount, not to mention the increasing pressures to catch up with ever-changing technologies.

If you have a three-person data team that maintains hundreds of tables & serves thousands of reports for internal and external stakeholders, the challenge multiplies with a tech stack that offers little room for maintainability, governance or AI developments.

This was exactly the challenge faced by our client Patties, a leading Australian supplier of products for the food industry. In this blog post, we will show you how we have gone about solving Patties’ challenges by migrating them to a modern data platform on the cloud.

A migration of data platforms is not just about moving to a new tech stack. It is a journey that requires a holistic change in mindset and processes.

Let’s dive deep into Patties' challenges, their migration journey, and the takeaways. In this article, we will show you what it means to deliver data platform migration beyond the tech stack, ensuring efficient adoption and scalability in the long run.

The starting point

Patties Food Group is a leading Australian supplier of savoury and dessert products for the food industry. They are an early adopter of data platforms like Azure Data Factory (for extraction), TimeXtender (for transformation), Azure (for storage), and on-prem IBM Cognos & ThoughtSpot (for BI) to meet internal analytics needs.

The whole stack, while low-code friendly & fitting the initial needs, had also started bringing some challenges over time, such as:

• Existing data models scattered across transform and BI tools: business users have the flexibility to create thousands of reports independently, creating an additional transformation layer that is not version-controlled;
• The same metric being calculated differently, depending on who created that report / table;
• A report made up of multiple data sources with different transformation schedules, some of which had to be triggered manually, as the team developed multiple data source ingestions separately without a single orchestrator or synchronised schedules;
• Lack of change impact analysis & mechanism to prevent bugs being introduced to pipeline before report consumption.

These challenges are not unique to Patties, but actually quite common across industries, no matter the tech stack used. Patties understood they needed a change, and laid out some basic requirements:

1. Data lineage from storage to BI layer;
2. Maintainable pipelines; fast-track debugging;
3. Tech stack that allows for AI development;
4. Minimum disruption.

Ideal world

In an ideal world, we would want to see the following:

Data models are built in a way that serves multiple purposes, not report-driven, and centralised in the transformation layer. BI tools should not store data transformation logics, it should only utilise curated tables in data mart.

There is separation of development vs production environments, and testing workflows to catch bugs before it occurs on live reports:

• Separate databases for development (where the data team can introduce changes and test them thoroughly before exposing the output to business users) vs production (where business users consume reliable, verified tables and reports);
• Testing mechanisms are in place to ensure changes are not disruptive, and data quality meets certain criteria, before deployed for production;
• Alerting mechanism allows proactive follow-up when a development test fails, or when a production pipeline fails.

Access control in place:

• The data team can access raw and transformed data;
• Business users should access only data mart and BI reports, without the ability to create additional data objects in the warehouse or add more transformation logic in BI tools;
• A change request process should be introduced to help business users and the data team work together on necessary changes in a timely & orderly manner.

Data documentation is accessible throughout the organisation:

Data team has a centralised place to document transformation logic;
Business users can also reference the calculation logic behind the metrics they see, columns and tables definitions, verification status, ownership.

Data lineage is accessible and covers beginning (ingestion) to end (BI endpoints). This helps the data team minimise debugging effort when something fails.

Getting there

Technically, the ideal world is possible. We considered Patties’ requirements and capacities, and helped them implement a series of changes. Check out the rationale and the challenges in the migration checklist below.

From TimeXtender to dbt Cloud

Why?

dbt Cloud serves as transformation and orchestration for the whole project, while also offering a better framework of development, testing, documentation, and lineage in terms of:

• Exposure feature helps oversee lineage from data source to BI reports;
• ThoughtSpot semantic layer integrates with dbt, allows for a single source of truth, and minimises the effort of ensuring curated data assets for consumption.

Challenges

On the one hand, we were looking to reduce the time to the sunsetting of the legacy TimeXTender tool.

On the other, Patties had to adopt a new way of working based on:

• CI/CD integration in the development workflow, where testing is mandatory;
• Moving away from scattered transformation logic to a centralised one.

From Azure SQL Hyperscale to Snowflake

Why?

Snowflake was the go-to solution, as it would offer a managed data warehousing solution. For Patties, it was crucial to minimise the effort of monitoring warehousing costs and fine-tuning performance, while also being able to build more AI-integrated features in the near future.

Challenges

The challenges here again related to adopting a new way of working, separating raw data, development vs production datasets, with proper access control policy in place.

Meeting strict security requirements was also a must, enabling SSO integration and IP blocking.

From ADF and TimeXTender to Fivetran

Why?

While TimeXtender is a handy data warehouse automation tool, it does not fully integrate with Snowflake and also requires more maintenance effort as a self-hosted tool.

Fivetran offers a managed solution for the ingestion layer, with configurable frequencies and integration with dbt Cloud, which, in its turn, handles the orchestration, allowing for dependency management in the data pipeline.

Challenges

There were several giant tables in the ERP systems that were not log-enabled due to third-party control. For these, we could not leverage Fivetran’s CDC feature and a full refresh could turn out costlier.

Handling change impact on BI layer

Why

Given that dbt is the centralised code base for the transformation layer, we proceeded with refactoring and merging of transformation logic scattered across BI layer and legacy data warehouse. In such cases, optimising the dashboards across BI platforms is a must.

These changes are minimal in disruption for the business, as required, but also improves Patties’ internal reporting layer with an optimised number of used and verified reports.

For business users, change management is fairly smooth and straightforward, as there is no new tool to learn.

For the data team, there is a migration in the backend taking place, but it mainly involves centralising modelling logic into dbt and replacing data connections on the BI layer.

Challenges

Patties had thousands of reports in IBM Cognos and hundreds on-prem in ThoughtSpot. These reports were powered by hundreds of tables, but we did not have the lineage to identify their links yet.

We had to find out what reports end users would actually use, how often, and which ones were worth migrating.

We also needed to ensure that the report contents would remain intact and the numbers consistent.

The solution

As it turned out, the change in the tech stack was just one aspect of the migration. We worked closely with Patties on many other aspects to meet the timeline while ensuring that the new platform would meet all the success criteria.

Let’s see what the project involved.

Identifying migration objects

Patties insights proved crucial here. Their team was willing and eager to optimise the experience of business users by providing fine-tuned, curated reports for them to use.

We leveraged ThoughtSpot’s API and Cognos features to identify the real usage statistics behind thousands of reports and tables. This helped narrow down the objects to maintain & optimise, and allowed the data team at Patties to communicate the changes with business users in an organised, efficient way.

Recommending new practices and processes

After detailed analysis, we recommended best practices for Patties to adopt related to:

• Snowflake configurations, budget monitoring, warehouse performance monitoring;
• dbt Cloud jobs set-up;
• Continuous integration in GitHub, pull request reviews how-to’s;
• Adopting the modular approach in dbt.

Comprehensive upskilling

We would hold weekly sharing sessions to train Patties’s teams on tools and practices: To ensure high engagement, we designed a hackathon format for these sessions. This was also an excellent chance for Infinite Lambda’s team to gain experience with Patties’ old teck stack and glean valuable insights.

The client teams learnt how to use the new tools effectively, and adopted a new way of thinking and working with data.

We would communicate regularly to discuss discoveries and remove blockers, making sure everyone was on the same page, had access to upskilling materials, and could keep track of progress.

Ensuring quality of migration

To ensure data quality, we added the necessary tests to dbt models for the entire project.

The production pipeline has been running at 99% Service Level Agreement (SLA) for 6 months now. This is not because of a lack of testing, but because most of the failures are captured as early as development.

We did a dbt audit on data model results to compare against legacy tables, using the infinitelambda/audit_helper_ext dbt package. This ensured the migrated model would give an identical result to the legacy model, i.e. that all number would match on reports after migration.

The result:
data platform migration beyond the tech stack

What is next

We considered a few CI-integrated data change tools like Recce or Datafold, which could highlight the impact of a change to data model and empower Patties data team with confidence whenever they make a future change;

Another recommendation concerned a future semantic integration between ThoughtSpot Cloud and dbt Cloud, once the integration matures and automatic exposure updates are supported.

Takeaways

The actual implementation took about 2.5 months and included:

• Setting up the new platform;
• Migrating models to dbt
• Creating CI/CD jobs, dbt-job-as-code
• Migrating hundreds of ThoughtSpot objects and optimising Cognos reports

The gradual adoption and preparation process added another 2 months to the project.

The whole journey came in several phases. First, we focused on setting up the foundation platforms and processes. Then, we moved on to review migration items to have proper strategy. Finally, there came the actual work of migrating and refactoring.

Patties’ data team’s willingness to change their data stack, as well as some of the processes and frameworks, were crucial for solving their data challenges.

This has allows Patties’ data team to redirect their time and focus to solving business problems with data, rather than fixing pipelines.

Here are a few statistics to reflect on that journey:

Are you facing the same data challenges?

If a data platform migration is on the map for your team, here are the top 4 pieces of advice we can offer based on projects like Patties’:

• Plan out the timeline, especially if you have a legacy platform about to sunset, allow at least a 6-month head start or possibly longer if your organisation’s processes are longer and more elaborate;
• Leverage your business insights to evaluate how necessary it is to migrate objects. This would make your migration project much more relevancy and efficiency;
• Be agile. There will always be some unforeseen blockers or events out of your control. Implement the project in phases and focusing on high-priority tasks will allow you to manage it all and deliver a successful migration within budget and timeline. For instance, focus on lift and shift first, and when that is ready, move on to refactoring a report and the underlying models so you can stay on strict schedule;
• Do not underestimate the mindset shift. A migration is not a project you can complete overnight, and it comes with changes in processes and frameworks. Embracing these changes would bring the real results from the migration, and it is important to include that in the migration plan in order to ensure efficient adoption.

Working with Patties has been a pleasure and we highly appreciate their collaborative spirit and willingness to commit to the project.

If you are planning a migration, make sure to:

1. Explore more success stories like Patties’;
2. Check out how Infinite Lambda implements cutting-edge technology and methodologies to accelerate the projects;
3. Get in touch to tell us about your challenges — we are happy to offer a free consultation.