STEDI Lakehouse on AWS
Problem
Raw semi-structured sensor and app data arrived from three separate sources with no curated layer to query.
Source
Semi-structured JSON from a customer website, a mobile app, and IoT devices.
Pipeline
Landed raw JSON in S3, cataloged and transformed it through AWS Glue into a landing, trusted, and curated zone structure, then served the curated tables to Athena for querying.
Quality
Records filtered at the trusted zone so that only consented customer data flows into curated tables.
AWSGlueS3AthenaPythonSparkETLLakehouse
Redshift Data Warehouse
Problem
Raw JSON event logs in S3 were not queryable for analytics without a modeled warehouse.
Source
Raw JSON log and song metadata files stored in S3.
Pipeline
Extracted JSON from S3 into Redshift staging tables, then transformed into a star schema with a songplays fact table and users, songs, artists, and time dimensions using SQL joins and filters.
AWSRedshiftSQLPythonStar SchemaETLData Modeling
Azure Bike Share Data Warehouse
Problem
Operational OLTP records for trips, riders, stations, and payments were not shaped for analytical queries.
Source
Bike share operational data covering trip records, rider profiles, station locations, and payment transactions.
Pipeline
Modeled the operational data into a galaxy schema with separate fact tables for trips and payments sharing conformed dimensions, targeting an Azure Synapse analytics environment.
AzureSynapseSQLData ModelingGalaxy SchemaDimensional Modeling
KSA Dust Health Risk
Problem
Aerosol and population data existed in incompatible raster grids with no governorate-level output.
Source
NASA MERRA-2 aerosol optical depth rasters and WorldPop 2020 population rasters, covering 147 Saudi governorates.
Pipeline
Ingested both raster sources, aligned them to a common grid, computed a weighted Dust Respiratory Exposure Index per cell, then aggregated to governorate boundaries as the serving layer.
PythonRaster ProcessingGeospatialETL