Foundational ETL and BI Capabilities

Executive Summary

This project solves the critical business problem of data silos and delayed insights by implementing a modern, cloud-native data pipeline that transforms raw e-commerce data into actionable business intelligence. Built with Google Dataform, BigQuery, Cloud Storage and Looker Studio, it delivers:

• Centralized Single Source of Truth (SST) removing data fragmentation and enabling faster time-to-insight through automated, real-time data processing and self-service analytics.
• Data accuracy through comprehensive data quality frameworks ensuring data consistency and providing clarity on data lineage.
• Efficient operations through reduced cost via serverless architecture and modular pipeline design.

The Need Addressed

Before this implementation, the e-commerce company The Look E-commerce faced:

• Data fragmentation across multiple systems.
• Manual, error-prone ETL processes delaying decision-making.
• Lack of standardization and clarity on data lineage causing compliance and trust issues.
• Scalability bottlenecks as data volumes grew exponentially.

Technology Stack Rationale

Data Model

erDiagram
    trx_fct {
        INTEGER order_id PK
        INTEGER product_id PK
        INTEGER inventory_item_id PK
        INTEGER user_id
        STRING order_status
        DATE date
        STRING product_brand
        STRING product_category
        INTEGER user_age
        STRING user_gender
        STRING user_country
        STRING iso_abbreviation
        NUMERIC sale_price
        NUMERIC product_cost
        NUMERIC gross_profit
        TIMESTAMP processing_timestamp
    }
    inventory_dim {
        INTEGER inventory_item_id PK
        DATE date_created
        DATE date_sold
        TIMESTAMP processing_timestamp
    }
    events_fct {
        INTEGER event_id PK
        INTEGER user_id
        INTEGER sequence_number
        INTEGER session_id
        DATE date_created
        STRING traffic_source
        STRING uri
        STRING event_type
        TIMESTAMP processing_timestamp
    }
    orders_dim {
        INTEGER order_id PK
        STRING status
        DATE date_created
        DATE date_returned
        DATE date_shipped
        DATE date_delivered
        INTEGER num_of_item
        TIME order_processing_time
        TIME order_transit_time
        TIME order_lead_time
        TIMESTAMP processing_timestamp
    }
    products_dim {
        INTEGER product_id PK
        NUMERIC cost
        STRING category
        STRING brand
        NUMERIC retail_price
        NUMERIC markup
        STRING department
        TIMESTAMP processing_timestamp
    }
    users_dim {
        INTEGER user_id PK
        INTEGER age
        STRING gender
        STRING country
        STRING traffic_source
        DATE date_created
        STRING iso_abbreviation
        TIMESTAMP processing_timestamp
    }
    trx_fct ||--|| orders_dim : "order_id"
    trx_fct ||--|| products_dim : "product_id"
    trx_fct ||--|| inventory_dim : "inventory_item_id"
    trx_fct ||--|| users_dim : "user_id"
    events_fct ||--|| users_dim : "user_id"
  

Total Cost of Ownership (TCO)

Based on current consumption, this project costs an estimated $13 per month.