Skip to content

README.md

Latest commit

 

History

History
101 lines (79 loc) · 3.33 KB

README.md

File metadata and controls

101 lines (79 loc) · 3.33 KB
Raw

Data Pipeline Specification: ecomm-data-pipeline

Pipeline Design

1. Overview

  • Pipeline Name: ecomm_data_pipeline
  • Description: Data pipeline for ecommerce data analytics of {company_name}
  • Objective: Ingest OLTP data into S3, process through Databricks DLT (Bronze/Silver/Gold), use dbt to define aggregate metrics, and expose results to Tableau dashboards.
  • Frequency: Daily batch (01:00 ICT)

2. Data Sources

Source Type Location Description
orders PostgreSQL RDS: ecomm-db Sales orders
products PostgreSQL RDS: ecomm-db Product catalog
customers PostgreSQL RDS: ecomm-db Customer profiles
order_items PostgreSQL RDS: ecomm-db Order items details

3. Data Landing Zone (Raw)

  • Destination: s3://{bucket-name}/ecomm_db_extracts/daily_extracts
  • Format: CSV
  • Partitioning: ingestion_date=YYYY-MM-DD
  • Method: Incremental extract via updated_at
  • Tool: Airflow DAG → PythonOperator

4. Transformation Pipeline (Databricks DLT)

  • Catalog: ecomm_dw

Bronze Layer

  • Schema: bronze
  • Raw ingestion from S3
  • Schema evolution mode: rescue

Silver Layer

  • Schema: silver
  • Cast column type into proper type
  • Add data processed timestamp
  • Add quality check on primary keys
  • Fact & dimension models:
    • fact_orders (orders + order_items + customer + product)
    • dim_orders (order details info)
    • dim_customer (clean customer profile)
    • dim_product (product details info)

Gold Layer

  • Schema: gold
  • Analytics & agg models:
    • sales_analytics (analytics obt for data serving)
    • agg_daily_sales (daily sales aggregation)

5. Semantic Layer (dbt — Metrics Only)

  • dbt Schema: dbt_prod_metric
  • Purpose: Business aggregations on top of Gold tables
  • Models:
    • agg_sales_kpi: SUM(item_price), COUNT(distinct order_id) by order_date
    • agg_product_kpi: SUM(quantity) by order_date, product, category
    • agg_customer_kpi: COUNT(1) by order_date
  • dbt Tests:
    • Ensure source columns not null

6. Consumption Layer (Tableau)

  • Connection: Tableau → Databricks SQL Warehouse
  • Datasets Exposed: dbt metrics tables
  • Dashboards:
    • Ecommerce Analytics Dashboard (Revenue, AOV, Top Selling Categories, Customer Acquisition)
  • Refresh: Extract refresh after dbt job completes

7. Orchestration

  • Delarative Pipeline: batch_ecomm_pipeline
    • Tasks:
      1. Bronze with Autoloader
      2. Silver star schema
      3. Gold analytics & agg models
    • Schedule:
      • 0 1 * * * (ICT)
  • dbt Job: DailyRefresh
    • Tasks:
      1. Regenerate daily agg models
    • Schedule:
      • 0 5 * * * (ICT)

8. Data Quality

  • DLT Expectations: valid id, valid status ('shipped', 'delivered', 'cancelled', 'pending'), valid price (>= 0)
  • dbt Tests: source checks (not null)

9. Monitoring

  • DLT: built-in pipeline logs & notification on pipeline failure
  • dbt: artifacts + run results in dbt Cloud

10. Security

  • Raw (S3): DE-only access
  • Silver/Gold: Unity Catalog RBAC
  • Metrics: Tableau consumers access only to dbt metric tables