As one of our group of companies supplies products to other businesses, mainly insurance companies, we manage a vast network of suppliers and clients. Each supplier provides us with product feeds containing stock and pricing information in different formats such as Excel, CSV, and text files. Similarly, our clients require customized product feeds with specific margins and delivery charges.
Previously, this entire process was manual/semi-auto, requiring significant effort to collect, process, and distribute feeds. Recognizing the inefficiency and scalability challenges, I designed and implemented an automated Azure-based solution to streamline the entire workflow.
Challenges of the Manual System
– Suppliers sent product feeds in different formats, requiring manual processing.
– Product data had to be categorized and updated manually in our database.
– Each client had unique pricing and delivery rules, making feed generation time-consuming.
– The entire process was prone to human errors and delays.
The Azure-Based Automated Solution
To eliminate inefficiencies, I built an end-to-end automated system using Azure services. The solution handles the entire workflow, from file ingestion to client-specific feed distribution, with minimal manual intervention.
Architecture Overview
Data Ingestion
Azure Blob Storage: Suppliers upload product feeds to a designated Blob Storage container. Blob Storage acts as the central repository for all incoming files.
Data Processing
Event Grid: Automatically triggers when a new file is uploaded to Blob Storage. Initiates the processing workflow by invoking Azure Functions
Azure Functions (Serverless): Processes uploaded files based on their format (Excel, CSV, Text). Validates and standardizes the data into a consistent format (e.g., JSON or CSV).
Data Storage
Azure SQL Database / Cosmos DB: Stores standardized product data in a structured format.
Data Enrichment
Azure App Service: Azure Functions or App Service applies business rules:
1. Categorizes products based on predefined rules.
2. Applies client-specific margins and delivery charges (stored in a configuration table or database).
3. Stores the enriched data back in the database.
Feed Generation and Distribution
Azure Function: Azure Functions or Logic Apps generate customized feeds for each client according to client requirements (e.g., CSV, JSON, or Excel). It also stores the generated feeds in a separate Blob Storage container.
Finally, it sends feeds to clients via email or SFTP based on their preferences.
Automation and Orchestration
Azure Monitor and Log Analytics: Tracks system performance and logs errors for troubleshooting.
Security and Compliance
Azure Active Directory (AAD): Authenticates and authorizes users and applications accessing the system.
Azure Key Vault: Securely stores sensitive information like API keys and connection strings.
Data Encryption: Encrypts data at rest (Azure Storage Service Encryption) and in transit (TLS).
By transitioning to an Azure-based solution, we can automate the entire product feed distribution process, significantly improving efficiency and scalability. The architecture leverages serverless components, ensuring cost-effectiveness and flexibility, while Azure’s robust security features guarantee data protection. This solution not only addresses our current challenges but also provides a foundation for future growth and innovation.