ahmedmfathi101-droid/Retail-Data-Warehouse-ETL

End-to-end retail data engineering pipeline that scrapes Amazon Egypt product data, validates data quality, loads PostgreSQL/Snowflake warehouses, orchestrates with Airflow, and powers Power BI insights. # MarketPulse ETL ### Retail Intelligence Data Pipeline An end-to-end data engineering project that extracts near real-time retail product data from Amazon Egypt (`amazon.eg`) using web scraping, cleans and transforms it with `pandas`, and loads it into PostgreSQL and Snowflake data warehouses. The full workflow is orchestrated and scheduled with Apache Airflow. ## Overview This project demonstrates a complete ETL pipeline for retail analytics without relying on a public API. The scraper collects Amazon Egypt product search results, the transformation layer standardizes the raw data, and the loading layer persists product dimensions and historical product snapshots for analytics and dashboarding. The pipeline includes automated execution, Airflow logging, data quality checks, warehouse freshness validation, analytical SQL queries, and Power BI dashboard guidance. ## Architecture ```text Amazon Egypt Search Pages | v Python Scraper (requests + BeautifulSoup) | v Raw JSON Files in data/ | v pandas Transformation | v Data Quality Validation | +--------------------+ | | v v PostgreSQL DW Snowflake DW | | +---------+----------+ v SQL Analytics / Power BI ``` ## Key Features - Scrapes Amazon Egypt listing pages and optionally enriches products from detail pages. - Collects SKU/ASIN, title, product name, brand, device type, prices, discount, rating, seller, availability, and technical specs. - Cleans and standardizes raw product data using `pandas`. - Creates a compact `Product Name` field from the first useful words in the full title, trimming trailing prepositions, conjunctions, and standalone numbers. - Runs local AI-style semantic validation to detect column mismatches, such as a device type being loaded as a brand. - Removes duplicate products within each batch before warehouse loading. - Loads dimensional warehouse tables into PostgreSQL. - Loads the same warehouse model into Snowflake. - …