Objective

• Integration Requirement: Establishing connectivity with E2Open to flow data from Walmart Luminate into semantic models for user-ready sales reports.
• Data Volume Management: Developing ETL for large-scale POS data integration from Luminate-specific tables while maintaining performance.
• Retailer-Level Insights: Providing data at a granular level, segmented by Stores and Categories.
• Historical Data Retention: Ensuring compliance with data retention policies by retaining POS data older than mandated durations in Azure.
• Designing a Secure ETL Pipeline: Architected a robust and secure ETL pipeline to ingest Luminate POS and category data from an FTP site into the Azure Data Warehouse, ensuring efficient and seamless data integration.
• Implementing a New DVR Process: Developed and deployed an enhanced Data Validation and Reconciliation (DVR) process to address inefficiencies in the existing reconciliation method. This proactive approach ensures data consistency checks between the Azure Data Warehouse and the Luminate source system, identifying and resolving anomalies before loading data into reporting models, thereby reducing manual intervention and enhancing data accuracy.

Solution

Data Integration and ETL Pipeline Development 

• E2Open System Integration: Established seamless data transfer from E2Open’s SFTP to Azure. 

• ETL Pipeline: Designed a robust pipeline triggered by a “Manifest File,” signaling readiness for data ingestion. 

• Automation: Implemented Logic Apps to automate data transfer, improving efficiency and reducing manual effort.

Data Transformation and Structuring

• Azure Databricks: Leveraged Databricks for comprehensive data transformation across multiple data sources. 

• Data Organization: Created distinct data lake containers and segmented tables by Retailer, Store, and Category. 

• Data Snapshots: Enabled snapshot views at multiple processing layers and partition levels for detailed analytics. 

Data Validation and Reconciliation (DVR) Process

• Aggregation and Anomaly Detection: Built aggregation processes and Databricks notebooks for reconciling data with E2Open’s aggregated files. 

• Automated Notifications: Configured automated emails to flag anomalies beyond a 0.5% margin for immediate resolution by E2Open’s Data Team. 

Benefits

• Improved Reporting: Enabled seamless integration of daily POS data, enhancing sales report accuracy and usability.
• Enhanced Data Reliability: The new DVR process ensured consistency between Azure DW and E2Open, minimizing errors in Power BI reports.
• Historical Data Compliance: Retention of older POS data in Azure adhered to compliance requirements while maintaining accessibility.
• Scalable Solution: Optimized Azure data processing and storage to handle growing data volumes without compromising performance.
• Actionable Insights: Enabled detailed analysis of top-performing and underperforming products, aiding strategic decision-making.
• Balanced Scorecard Metrics: Provided metrics like sales growth, gross margin, and inventory turnover to align POS analytics with business goals.