Enyinnaya
Daberechi

Overview

Designed and executed a production-style ETL pipeline that takes raw retail sales data from a flat Excel file and engineers it into a structured, queryable SQLite database — fully automated and orchestrated with Apache Airflow.

The Problem

The Mariana Mall sales dataset existed only as a static Excel file — manually maintained, prone to inconsistencies, and not queryable at scale. Every analysis had to start from scratch with no automated refresh, validation, or downstream serving.

What Was Built

• Extract: Python reads the raw Excel source file and captures row count metadata via Airflow XCom
• Transform: 8 transformation steps — column standardisation, date parsing, retailer deduplication, numeric validation, revenue recalculation, and enrichment with year, month, day of week, and quarter columns
• Load: 7 tables written to SQLite — sales_fact, revenue_by_retailer, revenue_by_sales_method, revenue_by_region, revenue_by_product, monthly_trend, and kpi_summary
• Validate: Post-load assertions confirm row counts and revenue totals before marking the pipeline successful
• Orchestrate: Airflow DAG schedules daily runs at 6:00 AM with 2-retry logic and failure alerting

Pipeline Output

• 350 rows loaded into sales_fact with full data lineage
• 6 aggregation tables serving downstream analytics
• KPI confirmed: ₦14,477,336.13 total revenue | 79.38% YOY growth
• Full execution log generated on every run

Outcome

Transformed a one-time manual Excel analysis into a repeatable, automated, and auditable data pipeline — demonstrating the core DA→DE transition: from consuming data to engineering the infrastructure that powers analysis.

Overview

Built a production-style dbt data modelling project implementing a 3-layer architecture (Staging → Intermediate → Marts) that transforms raw transactional data into clean, tested, and documented analytical tables ready for dashboard consumption.

The Problem

Raw sales data, even after ETL cleaning, is not structured for efficient analytics. Analysts querying the fact table directly face repeated logic duplication, no data quality guarantees, and no reusable business definitions. A modelling layer was needed to centralise logic and serve purpose-built tables downstream.

What Was Built

• Layer 1 — Staging (stg_sales.sql): Standardises column names and types, fixes retailer name duplicates, recalculates revenue for accuracy, and filters invalid rows. Materialised as a view.
• Layer 2 — Intermediate (int_sales_enriched.sql): Enriches data with date dimensions (year, month, quarter, day of week) and business classifications — transaction tier (High/Mid/Standard) and order size category (Bulk/Standard/Small).
• Layer 3 — Marts (materialised as tables): mart_retailer_performance (revenue rank, channel breakdown, portfolio share %) and mart_sales_summary (monthly KPIs with rolling YOY growth via SQL window functions).
• Data Quality Tests: not_null, unique, and accepted_values constraints enforced on all critical columns via schema.yml files.

Outcome

Delivered a fully documented, tested, and layered data model that separates raw source logic from business logic — serving two consumption-ready mart tables that any BI tool can connect to directly without repeating transformation logic.

Overview

Designed and executed a 5-stage ETL pipeline against a 1,500+ row Amazon India product dataset scraped from the live platform. The raw data arrived with multiple encoding corruption issues, non-standard number formats, and multi-valued fields — requiring systematic engineering before any analysis was possible.

The Problem — 8 Data Quality Issues

• ₹ currency encoding corruption: The rupee symbol was encoded as "₹" (UTF-8/Windows-1252 mismatch) — stripped with regex re.sub(r'^[^\d]+', '', str(v))
• Indian number formatting: Rating counts like "1,79,691" couldn't parse as integers — resolved by stripping all commas before casting
• Pipe-delimited multi-category fields: Category stored as "Electronics|Cables" — split and stored as main_category and sub_category
• Discount percentage as decimal: 0.65 stored instead of 65% — multiplied ×100 and rounded to 1dp
• Missing rating counts: NaN values imputed with the column median before integer casting
• Duplicate product IDs: Multiple rows per product — deduplicated keeping highest review count
• Mixed rating data types: String "ratings" coerced to float with error handling
• Missing savings column: Derived as actual_price − discounted_price

Pipeline Architecture — 5 Stages

• Stage 1 — Extract: Load raw Excel file, capture row count, identify all quality issues
• Stage 2 — Transform: Apply all 8 cleaning steps; derive new columns (savings, main_category, sub_category, discount_band)
• Stage 3 — Build Tables: Aggregate to category summary, top-50 products, and discount-band performance tables
• Stage 4 — Load: Write 5 relational tables to SQLite using pandas to_sql()
• Stage 5 — Validate: Post-load assertions confirm row counts, KPI totals, and referential integrity

Validated Output KPIs

• 1,351 unique products across 9 main categories loaded successfully
• Average discounted price: ₹3,305 | Average actual price: ₹6,000+
• Average discount: 46.7% | Average rating: 4.09 / 5.0
• Total reviews across all products: 23.8 million
• Pearson correlation (discount % vs rating): −0.162 — heavier discounts do NOT produce better ratings

Outcome

Transformed a corrupted, unstructured Excel product dump into a clean, validated, queryable SQLite database — ready to serve dashboards, BI tools, or further modelling. Demonstrates the core data engineering skill: making data trustworthy before it ever reaches analysis.

Overview

Built a fully serverless data lake on AWS (eu-north-1 / Stockholm) using a medallion architecture — raw CSV files land in S3, an event-driven pipeline transforms and promotes them to a processed layer, and Athena enables SQL querying directly on S3 via the Glue Data Catalog.

Architecture

• Storage (Bronze/Silver): S3 bucket with raw/ and processed/ prefixes implementing the medallion pattern — raw files are never mutated, processed files are the clean serving layer
• Event Fan-out: S3 PutObject events publish to an SNS topic which fans out to the Lambda function — decoupling the trigger from the processor and enabling future subscribers (e.g. notifications, additional transforms)
• Transform (Lambda): raw-to-processed function reads the incoming CSV, uppercases string fields, cleans whitespace, writes the clean output to the processed/ prefix
• Crawler Trigger: Lambda auto-triggers the AWS Glue crawler after each run, with a crawler state check and try/except guard — the pipeline never breaks on a concurrent crawler execution
• Catalogue & Query: Glue Data Catalog maintains schema metadata; Athena provides serverless SQL querying directly against the processed S3 layer — no database server required
• Security: All Lambda and Glue roles use least-privilege IAM inline policies scoped to specific S3 prefixes, Glue actions, and CloudWatch log groups

Key Engineering Decisions

• SNS fan-out chosen over direct S3→Lambda trigger to decouple ingestion from processing and support multi-consumer patterns
• Crawler state check prevents Lambda from failing when a previous crawl is still running — critical for high-frequency file drops
• Medallion prefix separation (raw/ vs processed/) ensures raw data is always replayable and transforms are idempotent

Outcome

A fully serverless, event-driven data lake pipeline that automatically ingests, transforms, catalogues, and exposes data for SQL querying — with zero servers to manage, built entirely on AWS managed services with IAM least-privilege throughout.

Overview

Designed and deployed a full observability layer for the AWS data lake pipeline using CloudWatch — covering pipeline health, failure alerting, and real-time email notifications via SNS. A production pipeline without monitoring is incomplete; this layer makes failures visible before they become data outages.

Dashboard — DataLakePipeline

• Widget 1 — Lambda Invocations: Line chart tracking how often the transform function fires — confirms S3 events are flowing through SNS correctly
• Widget 2 — Lambda Errors: Line chart of function errors — the primary signal for transform failures
• Widget 3 — Lambda Duration: Average execution time per invocation — detects performance degradation as data volume grows
• Widget 4 — Lambda Throttles: Monitors concurrency limits being hit — important for high-frequency file ingestion patterns
• Widget 5 — Glue Crawler Elapsed Time: Tracks crawl duration — rising times indicate growing data volume or schema complexity
• Widget 6 — Active Alarms: Alarm state panel showing current pipeline health at a glance

Alerting

• CloudWatch Alarm (DataLake-LambdaErrors): Triggers when Lambda error count ≥1 within any 5-minute evaluation window — catches single failures immediately
• SNS Topic (DataLake-Alerts): Alarm state change publishes to SNS, which delivers real-time email notifications — no manual log-checking required

Outcome

The pipeline moves from "deploy and hope" to fully observable — any Lambda error triggers an email alert within 5 minutes, and the dashboard gives a single-pane view of pipeline health, throughput, and performance trends over time.

Overview

End-to-end data engineering project ingesting live Nigerian macroeconomic data from the World Bank Open Data API, transforming and storing it in SQLite, serving it via a FastAPI REST layer, and visualising it in a dark-themed interactive HTML dashboard — fully orchestrated with Dockerized Airflow on a daily schedule.

Pipeline Architecture

• Ingest: Python requests World Bank Open Data API for key Nigerian indicators (GDP, inflation, unemployment, etc.) with per-indicator fallback handling — if one indicator fails, the rest still load
• Transform: Pandas cleans, normalises, and structures the API response into a consistent schema — handles missing years, null values, and unit standardisation
• Store: Transformed records written to SQLite — lightweight, portable, and queryable without a database server
• Orchestrate: Dockerized Airflow (LocalExecutor + PostgreSQL metadata DB) schedules the full ingest-transform-load pipeline daily at 06:00 UTC with retry logic
• Serve: FastAPI REST API exposes four endpoints: /indicators (all data), /indicators/latest (most recent year per indicator), /indicators/{year} (year filter), /health — with CORS middleware for browser access
• Visualise: Dark-themed HTML dashboard (Chart.js) with 6 KPI cards, 6 charts (line trends, bar comparisons), and a live "Refresh from API" button that re-fetches from FastAPI in-browser

Key Engineering Decisions

• Per-indicator fallback in ingestion ensures partial API failures don't void the entire pipeline run
• FastAPI sits between the dashboard and SQLite — the frontend never queries the database directly, keeping the data layer decoupled from the presentation layer
• Docker Compose bundles Airflow + PostgreSQL + the pipeline into a single docker-compose up — fully reproducible across environments

Outcome

A fully automated, containerised data product that ingests live macroeconomic data daily, serves it via REST, and presents it as an interactive dashboard — demonstrating the complete data engineering stack from API ingestion to user-facing visualisation.