After careful consideration, our Content team chose Google Cloud Platform (GCP) BigQuery to form the basis for the business solution.
Orders, sales, enrollments, product, and user accounts data are streamed through Kafka. The validated data then flows into a set of BigQuery nested tables. A data profile testing process identifies schema mismatches. Subsequently, an end-to-end test is performed that mitigates discrepancies where records were dropped in the reporting layer.
Our team used BigQuery to define several dashboards and reports, including:
- Daily Sales Report
- Orders Dashboard
- Customer Dashboard
- Product Dashboard
- Promotion Dashboard
- Auto-ship Dashboard
We validated performance by running datasets with a massive volume of data. We also defined event-based real-time pipelines to get transactional and continuous incremental data. The QE team then verified the reports and dashboards for near-real-time data.
The validated historical data was migrated from SQL Server to BigQuery using an automation framework. Our team then defined several BigQuery views in accordance with the company’s reporting requirements.
Key points in our solution include:
- Comprehensive and accurate data view: The earlier system reports were getting inaccurate results due to data redundancy and the lack of a single, comprehensive view of data.
- Highly optimized platform: Using GCP, our team has optimized performance, enabled ease of maintenance, enhanced self-service capabilities, and improved execution capabilities.
- Zero data loss: The new and improved architecture results in zero data loss. The ingestion layer combines and consolidates data, allowing efficient data handling and ensuring that no business-critical information is wasted or lost.
- Modern analytics platform: GCP migration turns the old system into an enterprise data warehouse (EDW) with up-to-date analytics capabilities.
- Near real-time response: The reports are generated using BigQuery tables and views, improving performance and providing near real-time values in reports.
