Skip to content 
Data Platform Migration for Enhanced Reporting and Auditability
Media
Guiding a migration to Snowflake and building a reliable, auditable data platform on the cloud.
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
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.
Migraing 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
The technology we useD
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.
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.
We have helped over 50 organisations to deliver projects at different scales with over £100m in ROI.
