Snowflake, Azure Data Factory, Python
Our client is one of the Entertainment Industry giants in the Middle East that is responsible for providing various forms of entertainment to the public with the responsibility to comply with local laws and regulations related to entertainment, and ensure the safety of their audience.
The client required a single pipeline for data integrity and data standardization, along with incremental data loading, which could help to improve the accuracy and consistency of the data, reduce the risk of errors and inconsistencies, and streamline the data operations. This can perpetually lead to better decision-making and improved business outcomes, as the company has access to more accurate and reliable data that can be used to inform your business strategy.
Multiple systems were pushing data from various source systems, causing chaos in the orchestration between jobs. The lack of a centralized dataset covering end-to-end business operations and the manual triggering of transformation jobs further compounded the problem. Additionally, the daily full load of source data caused historical values to be altered, which negatively impacted revenue reporting.
1. Extracted the data from the source system into the blob storage layer using ADF
2. Once the data was loaded into the ADF, then a data bricks job was set up which was in orchestration in ADF that loads the data stored in the blob storage layer using dimension table logic
3. Finally, we loaded the specific data incrementally into the Snowflake layer.
For the Incremental data load approach, we identified hash key columns and using other keys we created a combined hashcol. So, by comparing these two keys we incrementally loaded the data into the snowflake layer. In case of any changes in combined hashcol, we made the current flag of the old record ‘N’ and the new record ‘Y’.
The ability to enable segmented pricing models and optimize product assortments on a per-location or region basis. This allowed the client to tailor its business offerings to better meet the needs of its customers, leading to increased sales and margins, resulting in a 10% EBITDA increase.
The financial benefits, optimizing pricing and product assortments can also lead to other positive outcomes. For example, it helped our client’s business to accelerate location expansion by 40% by providing data-driven insights into where to open new locations and what products to offer. This also reduced poor location choices by 70%, ensuring that businesses are investing in locations with the greatest potential for success.
Helped suppliers drive sales, customer loyalty, and business collaboration with a data-sharing portal.
By having a single pipeline for data management, our client ensured data integrity and consistency across different systems and departments. This improved the overall accuracy of data analysis and decision-making.
Incremental data load helped in dealing with unwanted historical changes. This allowed the company to efficiently manage and update data without having to reprocess large amounts of historical data, saving time and resources. By streamlining the data operations, the organization was able to improve the overall accuracy of the Daily Revenue Report, by capturing the historical changes more efficiently.