...

Data Platform Migration for Enhanced Reporting and Auditability

Media

Guiding a migration to Snowflake and building a reliable, auditable data platform on the cloud.

Data platform migration for Avid

The client is a US-based technology and multimedia company that specialises in digital content creation tooling for audio and video editing, music notation, and content management and distribution. The client’s products are widely adopted in the television and video industry, being actively used in the production of television shows, feature films and commercials.

What we did

Migration to dbt and Snowflake
Data ingestion
Data Vault modelling
DOMO to dbt business logic replication for reporting

The Challenge

The client was looking to migrate to a new data platform on Snowflake.

They needed a system that would enable them to hold the history on the Data Cloud to be able to create monthly views every time they needed as opposed to working from a point-in-time baseline.

The client used to ingest data directly into their BI tool and manipulated the data there, but this was not a performant or scalable solution.

In fact, the old system was holding the client back as they had to snapshot the data every single month. The reporting process was inefficient, often taking several days to compile a monthly report.

Finally, as the client was not testing the data quality, they would experience a series of challenges. As it was impossible to detect issues until the reports were ready, spotting errors meant having to go back and redoing the entire report all over again. Instead of the data team proactively catching errors, it was the data consumers would often be the ones to come across inconsistencies, which was reducing their trust in the data and proving a major issue for auditability.

Infinite Lambda partnered with the client to set up dbt and Snowflake between the data sources and DOMO. This way, the data could be consistently ingested and the history could be stored, moving away from snapshots. Moreover, we were going to remove the data transformation logic from the BI tool in order to speed up the report generation process.

The solution

Migrating to Snowflake and building a Data Vault

Business logic rebuilt on Snowflake

The client had built all of their business logic into DOMO. The solution they were using required them to snapshot the data every month, which meant they could not simply go back and rerun the reports historically.
The initial phase involved recreating the business logic the client had in DOMO. We needed to make sure they could go back and rerun the historical reports based on the correct business processes, which was essential for audits.

To facilitate the management of environments and access roles on Snowflake, we used a tool developed by Infinite Lambda. The tool remains available for our clients to use as needed.

Data Vault 2.0

One of the benefits of using the Data Vault 2.0 methodology was that it would allow the client to create historical views on any timescale. Data Vault simplifies business logic whilst offering the highest level of transparency, enabling the client to trace each step and prevent black boxes.

Data quality

Their new platform used dbt for data transformations and it was also where we would do data quality tests. We set up tests to look for incorrect data at the point of ingestion, which would allow the client to spot issues on time, so they would not be able to find their way into the reports.

The client had previously developed some complex processes to transform and integrate their data. This was necessitated by the limitations of using snapshot data and hence the inability to go back and correct a source.

We replicated and simplified the logic which was still needed to create the monthly reports using daily ingested data combined for the month. We would then validate the reports against the period when the business logic was the same, which was the last 9 months in this case.

The Infinite Lambda engineers introduced data quality checks at the point of ingestion, which was a key feature of the solution we built. This ensured the data coming in was in the right format and identified incorrect data points. Now, Snowflake became the interim step, so that the data was ingested daily from SAP and SalesForce, being tested, validated and transformed.

As errors would be spotted throughout the month and corrected at source level, the errors would never find their way into a report and the reports would not require manual reviews, saving time and removing the risk of human error. Overall, we reduced the time it took to generate key operational reports and increased accuracy, which was critical for the client’s regulatory compliance.

Knowledge transfer

We held a number of training sessions with the client’s data team to help them familiarise themselves with dbt and Snowflake. At handover, the client’s team was perfectly capable of leveraging and maintaining their new system, preventing black boxes or underutilisation.

The technology we useD

data platform migration case study for Avid
The Result

A highly performant, auditable data platform

We helped the client migrate to Snowflake and built a reliable, auditable data platform for them. Moving them off a legacy platform to the Snowflake Data Cloud and dbt, we reduced the time it took to create a monthly report from several days to a few hours.

The robust data quality testing processes on dbt that we introduced enabled the timely detection of issues. We also made sure that the client had the flexibility to go back and fix any mistakes in their reports quickly and easily.

Applying Data Vault modelling, we simplified the business logic that was applied to the raw data, making it easy to understand and trace how the data was transformed. This was instrumental for the purposes of the strict audits that the client’s data would continuously undergo.

The client continued using DOMO as a visualisation tool. However, it was now only a tool to view the reports, which reduced the report generation time to mere seconds. If the client needs to change the BI tool, they are now perfectly free to select any other one and introduce it without any risks for their ETL processes.

don’t wait

Let’s walk the walk together

We are a generation of engineers and technologists who are passionate about transforming organisations with digital-age solutions and seeing them thrive on the cloud.

see Related Stories

We have helped over 50 organisations to deliver projects at different scales with over £100m in ROI.

Telecommunications
Cloud Data Transformation in Telecommunications
Insurance
Cloud Data Platform Migration for the Ultimate Reliability
World Health Organisation
Carbon Analytics Platform for a Vehicle Fleet
The Francis Crick Institute
Scalable Global Trusted Research Environment
Railsr
Robust Data Platform for a Digital Bank
Oddbox
Building a Single Source of Truth

We have helped over 50 organisations to deliver projects at different scales with over £100m in ROI.