Contact Us

Building a Full-Stack Shopify Data Platform to Power Real-Time Analytics and Operational Self-Service

Case Study

About the client

A U.S.-based e-commerce company operating in the beauty and cosmetics industry runs its entire retail and wholesale business through a leading commerce platform. With a catalogue spanning hundreds of SKUs across multiple vendors and fulfillment locations, the client needed a reliable, scalable data foundation to support decision-making across sales, inventory, finance, and customer analytics.

Customer service hospitaity

Industry

E-Commerce / Beauty & Cosmetics

Revenue (USD)

Million

Head Count

Employees

Countries Of Operation

USA

What Does a Business Consultant Do

Overview

  • Diacto Technologies designed and delivered a comprehensive, cloud-native data platform covering the full data lifecycle — from automated API extraction across eleven business domains to a multi-layer Snowflake warehouse, auto-refreshing analytics models, dual-layer pipeline monitoring, and five self-service Streamlit applications.
  • The engagement eliminated manual reporting, established a complete historical data archive, and gave business teams direct access to live operational data for the first time.
istockphoto 1322139094 612x612 1

Challenges

  • No Centralized Data Warehouse: All business data resided exclusively within the commerce platform’s interface, making cross-domain analysis and long-term trend reporting impossible. 
  • Manual, Fragmented Reporting: Generating any insight required manual CSV exports, leading to inconsistent data, high effort, and no single source of truth across sales, finance, and operations teams. 
  • No Historical Record: Without an automated ingestion layer, the client had no mechanism to accumulate or preserve historical data for trend analysis, customer segmentation, or financial reconciliation. 
  • Zero Pipeline Visibility: No monitoring existed to detect data refresh failures, leaving teams unaware of data quality issues until they caused downstream problems. 
  • Insecure Credential Management and No Self-Service Access: API keys and database credentials were managed without a centralized system, and business users had no way to query or interact with their own data without depending on technical teams. 

Solution

3

Automated Three-Stage ETL Pipeline

Diacto built a fully automated ingestion pipeline across eleven business domains — orders, products, customers, inventory, payouts, refunds, returns, balance transactions, gift cards, and transactions — running three times daily via AWS Step Functions with parallel domain execution. o Stage 1 — API to S3: AWS Glue Python Shell jobs extract data from the commerce API using credentials retrieved from AWS Secrets Manager and write structured files to an Amazon S3 data lake. o Stage 2 — S3 to Raw: A second Glue job loads each file into a fault-tolerant RAW schema in Snowflake, preserving source data exactly as received. o Stage 3 — Raw to Curated: A third Glue job cleans, type-casts, and de-duplicates data into an append-only CURATED schema that accumulates a complete historical archive

6

Multi-Layer Snowflake Warehouse

Diacto structured the warehouse across four schemas — RAW, CURATED, and CONSUMPTION with the CONSUMPTION layer housing six auto-refreshing Snowflake Dynamic Tables powering a product master, SKU-level replenishment recommendations (using 13 months of rolling sales, QOH, QOO, and YoY velocity), and monthly stockout tracking.

Implementation Speed

Dual-Layer Monitoring and Alerting

Diacto implemented real-time failure alerting for both AWS Glue jobs (via Amazon EventBridge and SNS) and Snowflake Dynamic Table refreshes (via a stored procedure, notification integration, and scheduled Snowflake Alerts in a dedicated ADMIN schema), with deduplication logic to prevent repeat notifications.

Group 65

Five Self-Service Streamlit Applications

Diacto delivered five Snowflake Streamlit applications — an executive analytics dashboard, a bank-to-payout reconciliation tool with intelligent amount-based matching, a read-only product catalogue viewer with missing SKU detection, a read-write product catalogue editor with MERGE-based upserts, and a purchase order updater for non-technical operations staff.

FinanceCSAbout

Impact

  • Complete Data Automation: Eleven Shopify data domains are now ingested, staged, and warehoused automatically three times daily, eliminating all manual CSV exports and establishing a continuously growing historical archive.
  • Operational Efficiency for Finance: The payout reconciliation application reduced a manual, error-prone bank-matching process to a file upload with automated matching, ambiguity flagging, and a structured Excel export.
  • Proactive Pipeline Observability: Dual-layer alerting across AWS and Snowflake replaced zero visibility with immediate email notifications on any failure, enabling the support team to respond before downstream impacts occur.
  • Product and Inventory Intelligence: Auto-refreshing replenishment recommendations and stockout tracking gave the merchandising team a live, SKU-level reorder view that did not previously exist.
  • Self-Service for Business Teams: Five Streamlit applications removed the dependency on technical teams for routine reporting, data updates, and financial reconciliation across sales, product, operations, and finance functions.