Forem: John Wakaba

Building an AI-Powered Personalized Learning Platform with FastAPI, PostgreSQL, and Mistral AI

John Wakaba — Tue, 10 Mar 2026 08:55:50 +0000

Artificial Intelligence is transforming education by enabling systems
that adapt to individual learning needs. In this article, I'll walk
through how I built an AI-powered personalized learning platform
that generates quizzes, tracks student progress, and provides real-time
insights for teachers.

The Problem

Traditional learning platforms often deliver the same content to every
student, regardless of their performance. However, students learn at
different speeds and struggle with different topics.

The goal of this project was to build a system that:

• Generates quizzes automatically using AI
• Tracks student learning behavior
• Detects struggling students
• Provides teachers with data-driven insights

System Architecture

The system consists of four main components:

Student Interaction Layer

FastAPI Backend

PostgreSQL Database

AI Engine (Mistral)

Architecture overview:

Students

↓

FastAPI API

↓

PostgreSQL Database

↓

Mistral AI

↓

Analytics Dashboard

AI Quiz Generation

Instead of manually creating quizzes, the platform uses Mistral AI
to generate questions dynamically.

Example API endpoint:

GET /generate-quiz/algebra

The AI returns:

Question
Multiple choice answers
Correct answer
Explanation

This allows the platform to generate unlimited quizzes for any topic.

Real-Time Feedback

When students submit answers, the backend evaluates correctness and generates explanations.

POST /submit-answer

Example response:

correct: true\
score: 100\
feedback: Explanation of the answer

Students receive immediate feedback, improving engagement and learning efficiency.

Adaptive Learning

One of the most important features is adaptive difficulty.

If a student performs well, the system generates harder questions.

If a student struggles, the system provides simpler explanations and
easier quizzes.

This creates a personalized learning experience.

Data Analytics with SQL

Every interaction is stored in PostgreSQL, allowing powerful analytics.

Example insights:

Average student performance
Topic difficulty analysis
Learning trends over time
Detection of struggling students

Example SQL query:

SELECT student_id, AVG(score) FROM quiz_results GROUP BY student_id;

Teacher Dashboard

To visualize insights, I built a Streamlit dashboard.

Teachers can view:

Student performance
Difficult topics
Performance trends
At-risk students

This allows educators to identify learning gaps early.

Technologies Used

FastAPI
PostgreSQL
Mistral AI
SQL Analytics
Streamlit
Python

Final Thoughts

AI-powered learning platforms have the potential to transform education by making learning personalized, adaptive, and data-driven.

This project is a simplified prototype of what modern EdTech platforms can achieve using open-source tools and AI models.

# Understanding Joins and Window Functions in SQL

John Wakaba — Wed, 04 Mar 2026 09:31:49 +0000

When working with relational databases, data is rarely stored in one single table. Instead, it is organized into multiple related tables to reduce redundancy and improve structure.

In a typical transactional database, you might have:

customers → customer information
orders → order transactions
books → product details

To analyze meaningful business insights, we must first combine this data. That’s where SQL Joins come in.

Once the data is combined, we can apply Window Functions to perform advanced analysis such as ranking, running totals, and trend comparisons.

This article walks you through both concepts in a logical flow — starting with joins and finishing with window functions.

SQL JOINS

Why Joins Matter

Relational databases are built on relationships.

For example:

A customer places many orders.
An order references one book.
A book can appear in many orders.

To analyze this properly, we must join the tables together using a shared key — usually customer_id or book_id.

INNER JOIN

An INNER JOIN returns only rows that exist in both tables.

If we want to see which customers placed orders:

SELECT o.order_id,
       c.first_name,
       c.second_name,
       o.order_date,
       o.quantity
FROM orders o
INNER JOIN customers c
    ON o.customer_id = c.customer_id;

✔ Returns only customers who have placed orders

❌ Excludes customers with no orders

This is the most commonly used join in data analysis.

LEFT JOIN (LEFT OUTER JOIN)

A LEFT JOIN returns:

All rows from the left table
Matching rows from the right table
NULL where no match exists

Example: Show all customers, even those who haven’t ordered anything.

SELECT c.customer_id,
       c.first_name,
       c.second_name,
       o.order_id,
       o.quantity
FROM customers c
LEFT JOIN orders o
    ON c.customer_id = o.customer_id;

✔ Every customer appears

✔ Customers without orders will show NULL in order columns

This is extremely useful for identifying inactive customers.

RIGHT JOIN

A RIGHT JOIN does the opposite of a LEFT JOIN.

SELECT c.first_name,
       c.second_name,
       o.order_id
FROM customers c
RIGHT JOIN orders o
    ON c.customer_id = o.customer_id;

This ensures all orders appear, even if customer data is missing.

In practice, RIGHT JOIN is less common because we can usually rewrite it using LEFT JOIN by switching table order.

FULL JOIN (FULL OUTER JOIN)

A FULL JOIN returns all rows from both tables.

SELECT c.first_name,
       c.second_name,
       o.order_id
FROM customers c
FULL JOIN orders o
    ON c.customer_id = o.customer_id;

✔ Shows matches

✔ Shows unmatched customers

✔ Shows unmatched orders

This is useful for data auditing and reconciliation.

Joining Multiple Tables

Real-world analysis often requires more than two tables.

Example: See which customer ordered which book.

SELECT c.first_name,
       c.second_name,
       b.title,
       o.quantity,
       o.order_date
FROM orders o
JOIN customers c
    ON o.customer_id = c.customer_id
JOIN books b
    ON o.book_id = b.book_id;

Now we can see:

Customer name
Book title
Quantity ordered
Order date

This joined dataset becomes the foundation for deeper analysis.

CROSS JOIN

A CROSS JOIN produces all possible combinations between two tables.

SELECT c.first_name,
       b.title
FROM customers c
CROSS JOIN books b;

If you have:

10 customers
5 books

You get 50 rows.

This is useful when generating combinations for simulations or recommendation systems.

Anti-Join (Finding Missing Records)

SQL doesn’t have a direct ANTI JOIN, but we simulate it using LEFT JOIN + NULL filtering.

Example: Find customers who have never placed an order.

SELECT c.customer_id,
       c.first_name,
       c.second_name
FROM customers c
LEFT JOIN orders o
    ON c.customer_id = o.customer_id
WHERE o.customer_id IS NULL;

This is powerful for churn analysis and business reporting.

How Joins Prepare Data for Window Functions

Notice something important:

Most advanced SQL analysis begins like this:

FROM orders o
JOIN customers c ON o.customer_id = c.customer_id

Why?

Because:

Joins combine related data
Window functions analyze that combined dataset

Joins prepare the structure.

Window functions perform the analytics.

Now that we understand joins, let’s move into advanced analytics.

WINDOW FUNCTIONS

They allow you to perform calculations across a set of table rows that are somehow related to the current row.

Window functions can be used to

Rank Rows.
Calculate cumulative totals.
Find the difference between consecutive rows in a dataset.

Window functions return a value for each row while still providing information from the related rows.

ROW_NUMBER ()

Assign a unique row number to each row in the result set.

In a real world scenario it can help us track which order each customer made first, second.......

Assigns a unique number to each row, starting from 1 based on the order specified by the ORDER BY clause.

The number will reset for each position if PARTITION BY is used.

Assign a unique row number to each order based on the order date and we want to reset numbering for each customer

SELECT o.order_id, c.first_name, c.second_name, o.order_date,
 ROW_NUMBER() OVER (PARTITION BY o.customer_id ORDER BY o.order_date) AS row_num
 FROM orders o
 JOIN customers c ON o.customer_id = c.customer_id;

ROW_NUMBER () : Assigns a unique number to each order.

PARTITION BY o.customer_id : Ensures that the row numbering starts fresh for each customer.

Query will list orders for each customer showing their row number (1,2,3---) in the sequence of orders.

Ranking the orders globally based on order date without resetting the numbering for each customer

What is needed just remove the PARTITION BY Clause

SQL Query Without Resetting Row Number:

SELECT o.order_id, c.first_name, c.second_name, o.order_date,
 ROW_NUMBER() OVER (ORDER BY o.order_date) AS row_num
 FROM orders o
 JOIN customers c ON o.customer_id = c.customer_id;

Without PARTITION BY the numbering is continous for all orders across customers based on their order_date.

RANK() AND DENSE_RANK()

RANK() assigns a rank to each row, with ties getting the same rank but leaving gaps in subsequent ranks.

DENSE_RANK() works similarly but without leaving gaps in the ranking.

RANK() SQL QUERY

Rank the customers based on the total quantity of books as they are ordered

SELECT c.first_name, c.second_name, SUM(o.quantity) AS total_quantity,
 RANK() OVER (ORDER BY SUM(o.quantity) DESC) AS rank
 FROM orders o
 JOIN customers c ON o.customer_id = c.customer_id
 GROUP BY c.first_name, c.second_name;

RANK() assigns a rank based on the SUM(o.quantity) in descending order.

If two customers have the same total quantity ordered they will receive the same rank and the next rank will have a gap. *Two customers rank 1 will result in the next customer being ranked 3rd.

USING DENSE_RANK()

Assigns a rank without gaps for ties.

Assigns a rank to each row but it does not leave gaps in the rankings if there are ties.

Calculate the dense rank of customers based on the total quantity of books they ordered

SELECT c.first_name, c.second_name, SUM(o.quantity) AS total_quantity,
 DENSE_RANK() OVER (ORDER BY SUM(o.quantity) DESC) AS dense_rank
 FROM orders o
 JOIN customers c ON o.customer_id = c.customer_id
 GROUP BY c.first_name, c.second_name
 ORDER BY dense_rank;

If two customers are tied they will receive the same rank but the next customer will receive the next consecutive rank.

1, 1, next will be 2nd

This key difference in RANK() and DENSE_RANK() is crucial for how you want to treat tied values in your analysis.

LEAD() AND LAG()

LEAD()

LEAD() access next row's value.

It is used to access a row that follows the current row at a specific physical offset.

Generally employed to compare the value of the current row with the value of the next row following the current row.

Compare quantity ordered by each customer in the current row with the quantity ordered in the next row

SELECT o.order_id, o.customer_id, o.quantity,
 LEAD(o.quantity) OVER (ORDER BY o.order_id) AS next_quantity
 FROM orders o;

LEAD(o.quantity) allows you to access the quantity of the next row for each customer.

Query gives the quantity ordered by the customer in the current row and the quantity ordered by the same customer in the next row.

For the last row for each customer the next quantity will be NULL because there is no next row.

LAG()

Access previous rows value

It is crucial for analyzing trends or behavior change over time.

Allows you to access data from a previous row within the same result set and is crucial for comparing values in the current row with the preceding row.

Operates on partitions created by the PARTITION BY clause.

Compare quantity ordered by each customer in the current row with the quantity ordered in the previous row using the LAG() function

SELECT o.order_id, o.customer_id, o.quantity,
 LAG(o.quantity) OVER (ORDER BY o.order_id) AS prev_quantity
 FROM orders o;

LAG(o.quantity) allows you to access the quantity ordered in the previous row for each customer

The query shows the quantity ordered in the current row and the quantity ordered in the previous row for the same customer.

First row previous quantity will be NULL as there is no previous row.

NTILE() FUNCTION

Partitions data into specified number of buckets.

Crucial for data analysis and reporting as it allows users to efficiently distribute rows and analyze data in a structured manner.

We want to divide customers into 2 groups (quartiles) based on their total order quantity.

SELECT c.first_name, c.second_name, SUM(o.quantity) AS total_quantity,
 NTILE(2) OVER (ORDER BY SUM(o.quantity) DESC) AS quantity_tile
 FROM orders o
 JOIN customers c ON o.customer_id = c.customer_id
 GROUP BY c.first_name, c.second_name
 ORDER BY quantity_tile;

NTILE(2) divides customers into two equal groups (quartiles) based on their total quantity ordered.

PARTITION BY

Divides result set into partitions to apply window functions independently.

This clause divides the result set into partitions and the window function works independently within each partition.

calculate the total quantity of orders for each customer and the average price of the books ordered by each customer.

SELECT c.first_name, c.second_name,
 SUM(o.quantity) AS total_quantity,
 AVG(b.price) AS avg_price,
 SUM(o.quantity) OVER (PARTITION BY o.customer_id) AS total_order_quantity
 FROM orders o
 JOIN customers c ON o.customer_id = c.customer_id
 JOIN books b ON o.book_id = b.book_id
 GROUP BY c.first_name, c.second_name, o.quantity, o.customer_id
 ORDER BY c.first_name;

SUM(o.quantity) gives total quantity ordered by each customer.

PARTITION BY o.customer_id ensures the total order quantity is calculated for each individual customer.

## I Built an AI Tourism Assistant for Kenya Using RAG, pgvector, and Streamlit

John Wakaba — Wed, 04 Mar 2026 09:21:41 +0000

Imagine asking:

"What's the best luxury safari in Maasai Mara?"

and instantly getting personalized travel recommendations powered by
AI.

That's exactly what I built --- an AI Tourism Intelligence Assistant
that helps travelers discover the best travel packages in Kenya based on
their budget, travel style, duration, and preferred destination.

In this article, I'll walk you through:

• The idea behind the project
• How I built the AI recommendation system
• The RAG architecture powering it
• How vector search makes travel discovery smarter
• Deployment with Streamlit

✨ The Idea

Kenya is one of the world's most beautiful tourism destinations,
offering:

Wildlife safaris 🦁
Tropical beaches 🏝
Mountain adventures ⛰
Cultural experiences

But planning trips can be frustrating because:

• Travel packages are scattered across multiple websites
• Platforms rarely provide personalized recommendations
• Comparing destinations based on budget or style is difficult

So I decided to build an AI-powered tourism assistant that could:

✔ Understand traveler preferences
✔ Retrieve relevant travel packages
✔ Generate intelligent recommendations

🧠 What the AI Assistant Does

Users simply input their preferences:

Budget
Travel duration
Travel style
Preferred destination

The system then returns relevant travel packages from a tourism
database.

Example query:

Budget: $2000
Days: 5
Style: Relaxing
Destination: Diani

The assistant responds with recommended travel packages matching those
criteria.

⚙️ Tech Stack

Programming

Python

Data Engineering

PostgreSQL
pgvector

AI

Mistral AI embeddings
Retrieval-Augmented Generation (RAG)

Data Collection

Playwright
BeautifulSoup

Backend

SQLAlchemy

Frontend

Streamlit

Deployment

Streamlit Cloud
Neon PostgreSQL

🏗 System Architecture

Tourism Websites
      │
      ▼
Web Scraping (Playwright)
      │
      ▼
PostgreSQL Database
      │
      ▼
Embedding Generation (Mistral AI)
      │
      ▼
Vector Database (pgvector)
      │
      ▼
Recommendation Engine
      │
      ▼
Streamlit Web Application

🔎 How the RAG System Works

The project uses Retrieval‑Augmented Generation (RAG) to deliver
intelligent responses.

Instead of the AI guessing answers, it retrieves real travel packages
from the database first.

Pipeline:

User Query
     │
     ▼
Convert Query → Embedding
     │
     ▼
Vector Similarity Search
     │
     ▼
Retrieve Relevant Travel Packages
     │
     ▼
Generate Personalized Response

This ensures the AI responds with real tourism data rather than
hallucinations.

🗄 Database Design

The database stores travel information in structured tables such as:

travel_packages
destinations

Each travel package contains:

Package name
Destination
Duration
Price
Description
Vector embedding

🔍 Why Vector Search Matters

Traditional search relies on keywords.

Vector search understands meaning and context.

For example, if a user searches:

"Affordable safari in Kenya"

The system can still return:

• Budget Maasai Mara packages
• Lake Nakuru safari deals
• Amboseli wildlife tours

Even if those exact words were not used.

💻 Building the Interface

The frontend is built using Streamlit, which makes it easy to create
interactive data apps.

Users can:

✔ Enter travel preferences
✔ Browse travel packages
✔ Receive AI‑powered recommendations

🚀 Deployment

The application is deployed using:

Streamlit Cloud for hosting the web app.

Neon PostgreSQL for the managed database.

This allows the project to run fully online.

📊 Key Results

The project successfully delivers:

✔ AI-powered tourism recommendations
✔ Semantic search using vector embeddings
✔ A fully deployed web application
✔ Personalized travel package discovery

⚠️ Challenges I Faced

Web Scraping Complexity

Many travel websites load content dynamically, which required
Playwright.

Data Quality Issues

Scraped data often contained:

• Missing prices
• Duplicate packages
• Inconsistent destination names

Embedding Rate Limits

Embedding generation triggered API rate limits, requiring retry
logic.

Deployment Configuration

Deployment required careful setup of:

Environment variables
Streamlit secrets
Database connection strings

🔮 Future Improvements

Future versions of the system could include:

• AI itinerary generation
• Social media tourism trend analysis
• Integration with booking APIs
• User accounts and saved trips

🌍 Final Thoughts

Combining vector databases, AI retrieval systems, and interactive web
apps opens powerful opportunities for building intelligent data
products.

This project demonstrates how AI can improve tourism discovery and
travel planning.

🏠 Building a Machine Learning Property Price Predictor (From Web Scraping to Deployment

John Wakaba — Mon, 23 Feb 2026 07:53:09 +0000

In this project, I built a complete end-to-end machine learning system
that:

Scrapes property listings
Cleans and engineers features
Trains multiple ML models
Deploys a pricing app
Builds a business-ready dashboard

This article walks through the entire pipeline from raw web data to a deployed ML product.

Step 1 --- Web Scraping

I built a Selenium scraper to extract:

Location
Property Type
Bedrooms
Bathrooms
Size (sqm)
Amenities
Price (KES)
Listing Date

Sample Scraping Logic

listings = driver.find_elements(
    By.XPATH,
    "//div[contains(@class,'listing') or contains(@class,'property') or contains(@class,'card')]"
)

for listing in listings:
    link = listing.find_element(By.XPATH, ".//a[contains(@href,'/listings/')]")
    property_url = link.get_attribute("href")

Sample Scraping Logic

listings = driver.find_elements(
    By.XPATH,
    "//div[contains(@class,'listing') or contains(@class,'property') or contains(@class,'card')]"
)

for listing in listings:
    link = listing.find_element(By.XPATH, ".//a[contains(@href,'/listings/')]")
    property_url = link.get_attribute("href")

Step 3 --- Exploratory Analysis

Most Expensive Locations

location_prices = df.groupby("Location")["Price (KES)"].median().sort_values(ascending=False)
print(location_prices)

Step 4 --- Modeling

Train/Test Split

from sklearn.model_selection import train_test_split

X = df[["Bedrooms", "Bathrooms", "Size (sqm)", "amenity_score"]]
y = df["Price (KES)"]

X_train, X_test, y_train, y_test = train_test_split(
    X, y, test_size=0.2, random_state=42
)

Linear Regression (Baseline)

from sklearn.linear_model import LinearRegression
from sklearn.metrics import mean_absolute_error, mean_squared_error, r2_score
import numpy as np

lr = LinearRegression()
lr.fit(X_train, y_train)

pred = lr.predict(X_test)

mae = mean_absolute_error(y_test, pred)
rmse = np.sqrt(mean_squared_error(y_test, pred))
r2 = r2_score(y_test, pred)

Random Forest

from sklearn.ensemble import RandomForestRegressor

rf = RandomForestRegressor(
    n_estimators=200,
    random_state=42
)

rf.fit(X_train, y_train)
rf_pred = rf.predict(X_test)

XGBoost

from xgboost import XGBRegressor

xgb = XGBRegressor(
    n_estimators=300,
    learning_rate=0.05,
    max_depth=5,
    random_state=42
)

xgb.fit(X_train, y_train)
xgb_pred = xgb.predict(X_test)

Step 5 --- Deployment (Streamlit App)

The pricing app allows users to input:

Location
Bedrooms
Bathrooms
Size
Amenities

And returns:

Predicted price
Estimated range (± MAE)
Explanation of price drivers

Run locally:

streamlit run Streamlit_app.py

Step 6 --- Executive Dashboard

Built using Streamlit with interactive filters.

Includes:

Median price by location
Monthly price trends
Price per sqft comparison
Amenity impact analysis

Run:

streamlit run Dashboard.py

Key Insights

Size is the strongest determinant of price.
Premium neighborhoods significantly increase valuation.
Amenities increase value but are secondary drivers.

From Messy Data to Confident Decisions: How Analysts Use Power BI, DAX, and Dashboards in the Real World

John Wakaba — Tue, 10 Feb 2026 08:48:51 +0000

Power BI skills are often misunderstood as “just reporting.” In reality, professional analysts use Power BI as a decision-support system — one that transforms messy, unreliable data into insights leaders trust to allocate budgets, adjust strategy, and measure performance.

This article demonstrates how technical Power BI skills translate directly into real-world business decisions and measurable impact, following the same workflow used in real organizations.

Messy Data Is a Business Risk, Not a Technical Issue

In real organizations, data arrives incomplete and inconsistent:

Regions spelled differently across systems
Missing transaction dates
Revenue stored as text
Duplicate customer records
Placeholder values like N/A and Error

When these issues are ignored, dashboards show incorrect KPIs and misleading trends.

Business Impact

When analysts clean data correctly:

Financial metrics become trustworthy
Performance comparisons are accurate
Leaders focus on decisions instead of debating numbers

This is why analysts begin in Power Query, not visuals.

Power Query: Turning Raw Inputs into Reliable Data

Power Query is where analysts reduce business risk.

Using repeatable transformation steps, analysts:

Standardize categories for consistent grouping
Remove invalid or duplicate records
Apply correct data types for calculations and time analysis
Replace pseudo-blanks with true null values

Real-World Outcome

After proper Power Query transformations:

Monthly revenue no longer fluctuates unexpectedly
Forecasts align with finance systems
Data refreshes produce consistent results automatically

Reliable data is the foundation of every decision.

Data Modeling: Structuring Data for Decision-Making

Power BI does not analyze spreadsheets — it analyzes data models.

Professional analysts design star schemas:

Fact tables store measurable business events
Dimension tables provide descriptive context

Why This Matters

Well-designed models ensure:

Predictable filter behavior
Accurate KPIs across dashboards
Strong performance as data volumes grow

Poor modeling leads to conflicting answers and erodes stakeholder trust.

DAX: Translating Business Questions into Logic

DAX allows analysts to express business logic directly in calculations.

Executives ask questions such as:

Are we improving compared to last year?
Which regions are underperforming?
How close are we to our targets?

Using DAX, analysts move beyond raw totals to meaningful metrics like:

Profit margins
Year-over-year growth
Year-to-date performance

Business Impact

DAX enables:

Fair comparisons across time
KPI tracking against targets
Scenario-based decision-making

Without DAX, dashboards show numbers. With DAX, they show meaning.

Time Intelligence: Supporting Strategic Decisions

Time intelligence allows organizations to understand performance trends.

Using time-based analysis, analysts:

Compare current results to prior periods
Detect early signs of growth or decline
Measure progress toward annual goals

Decisions Enabled

Expanding high-growth regions
Addressing seasonal declines proactively
Adjusting forecasts based on YTD performance

Time intelligence transforms historical data into forward-looking insight.

Dashboards: From Information to Action

Dashboards are not collections of charts. They are decision interfaces.

Effective dashboards:

Highlight critical KPIs
Show trends requiring attention
Surface underperformance and exceptions
Enable fast filtering without technical effort

Measurable Outcomes

Well-designed dashboards help organizations:

Reduce time spent validating numbers
Detect issues earlier
Align teams around shared metrics
Act faster with confidence

Dashboards succeed when users know what to do next.

Measuring Success: Business Impact of Power BI

When Power BI is used effectively:

Leaders trust the data without manual validation
Decisions are backed by consistent metrics
Reporting effort decreases
Performance improvements are measurable

The real value of Power BI is not the report —

it is the decisions enabled by the report.

Conclusion: Power BI as a Strategic Asset

Analysts create measurable impact by combining:

Power Query for data reliability
Data modeling for meaningful analysis
DAX for business logic
Dashboards designed for action

This is how messy data becomes:

Trusted insights
Confident decisions
Real business outcomes

Power BI, when used professionally, is not a reporting tool — it is a strategic decision-making engine.

📊 Understanding Schemas and Data Modelling in Power BI

John Wakaba — Thu, 05 Feb 2026 11:13:12 +0000

Data modelling is the foundation of building scalable and
high-performance dashboards in Power BI. While many developers focus
heavily on visuals and DAX calculations, the true performance and
accuracy of a report depend heavily on how data is structured.

🧱 What Is Data Modelling in Power BI?

Data modelling refers to structuring data into logical formats that
support analysis and reporting. Dimensional modelling organizes data
into:

Facts (measurable metrics)
Dimensions (descriptive attributes)

A well-designed Power BI model determines:

How tables relate
How filters propagate
How fast reports load
How accurate calculations are

⭐ Star Schema

✅ What Is Star Schema?

A Star Schema organizes data using:

One central fact table
Multiple dimension tables connected directly to the fact table

The structure resembles a star, where dimension tables surround the
central fact table.

📊 Components of Star Schema

Fact Table

Contains measurable and quantitative data such as:

Sales revenue
Quantity sold
Profit
Discount

Each row represents a business event like a transaction.

Dimension Tables

Provide descriptive context to fact data such as:

Customer details
Product attributes
Date/Time
Store location

🚀 Benefits of Star Schema

✔ High query performance
✔ Simple design
✔ Easier DAX calculations
✔ Optimized for reporting and dashboards

⚠️ Limitations

Data redundancy
Higher storage usage

❄️ Snowflake Schema

✅ What Is Snowflake Schema?

A Snowflake Schema extends star schema by normalizing dimension
tables into multiple related tables.

Example:

Customer → City → Country

📌 Features

Normalized dimension tables
Supports hierarchical drill-down analysis
Improves data integrity
Requires additional joins

⚖️ Advantages

✔ Reduced redundancy
✔ Better data consistency
✔ Supports complex hierarchies

⚠️ Limitations

More complex design
Slower performance due to joins

📊 Fact Tables vs Dimension Tables

📊 Fact Tables

Store numeric metrics and foreign keys linking to dimensions.
Usually contain transactional data and large volumes of records.

🧾 Dimension Tables

Store descriptive attributes that provide context to fact tables.
Used for filtering, grouping, and categorizing data.

🔗 Relationships in Power BI

Relationships connect tables and enable filtering across datasets.

📌 Types of Relationships

One-to-Many -- One dimension record links to many fact records
Many-to-One -- Reverse of one-to-many
Many-to-Many -- Multiple matching records on both sides

🚨 Why Good Data Modelling Is Critical

⚡ Performance Optimization

Improves query speed
Reduces memory usage
Enables faster dashboard loading

🎯 Accurate Reporting

Ensures correct aggregations
Maintains reliable filter behavior

🧠 Easier DAX Calculations

Simplifies analytical queries
Improves calculation accuracy

🔧 Scalability

Supports future data expansion
Easier troubleshooting and maintenance

⭐ Star Schema vs Snowflake Schema

Feature Star Schema Snowflake Schema

Structure Denormalized Normalized
Performance Faster queries Slower queries
Complexity Simple Complex
Storage Higher storage Lower storage
Use Case Reporting dashboards Large complex warehouses

🏆 Power BI Data Modelling Best Practices

Use Star Schema whenever possible
Separate fact and dimension tables
Maintain clear relationships
Optimize data types
Reduce unnecessary joins

📌 Real-World Analogy

Think of a library:

Fact tables = Books (contain measurable information)
Dimension tables = Catalogue system (organizes and locates books)

🎯 Conclusion

Data modelling is one of the most important skills for Power BI
developers. Understanding schema design ensures dashboards are:

Fast
Accurate
Scalable
Maintainable

Before building visuals or writing DAX formulas, always ask:

Is my data model structured correctly?

Getting Started With Linux for Data Engineers (With Vi and Nano Examples)

John Wakaba — Thu, 05 Feb 2026 10:55:10 +0000

If you're getting into data engineering, there's one skill that keeps
showing up everywhere: Linux.

Whether you're working with cloud servers, big data tools, or pipeline
automation, Linux is almost always running behind the scenes. The good
news? You don't need to be a Linux wizard to get started.

In this guide, we'll break down:

Why data engineers need Linux
Basic commands you'll actually use
How to edit files using Vi
How to edit files using Nano
Real-world examples

Why Should Data Engineers Learn Linux?

Here's the honest truth --- most production data systems run on Linux
servers.

When you deploy Spark jobs, schedule Airflow pipelines, or manage
databases, you'll likely connect to a Linux machine.

It's Built for Performance

Linux handles heavy workloads really well, which is perfect for big data
processing.

It's Highly Customizable

Since Linux is open source, companies tailor it for their
infrastructure.

It Runs the Cloud

Most AWS, Azure, and Google Cloud servers run Linux.

It Supports Automation

Data engineers constantly automate workflows using shell scripts.

Linux Commands Every Beginner Should Know

Check Where You Are

pwd

List Files

ls

Move Between Folders

cd folder_name

Create a Folder

mkdir data_project

Create a File

touch notes.txt

Read a File

cat notes.txt

Why Text Editors Matter in Linux

When you log into a server, there's usually no graphical editor like VS
Code or Notepad.

Instead, you use terminal editors like: - Vi (powerful but tricky) -
Nano (simple and beginner-friendly)

Using Vi (The Power Tool)

Open or Create a File

vi sample.txt

Enter Insert Mode

Press i and start typing.

Save and Exit

Press ESC, then type:

:wq

Exit Without Saving

:q!

Example Script

vi pipeline.sh

Add:

#!/bin/bash
echo "Pipeline started"

Using Nano (The Friendly Editor)

Open a File

nano notes.txt

Save Your Work

Press:

CTRL + O

Exit Nano

CTRL + X

Example Config

nano config.conf

Add:

database=postgres
username=admin

Real-Life Data Engineering Scenario

You may need to:

Update Airflow configuration
Fix pipeline scripts
Modify database credentials
Check logs

Commands might include:

nano airflow.cfg

vi pipeline.sh

Pro Tips for Beginners

Always back up files before editing
Practice Vi commands slowly
Use Nano when learning
Learn basic shell commands daily

Final Thoughts

Linux is part of the foundation of modern data infrastructure.

Learning Linux commands and text editors gives you confidence when
working with production servers and cloud platforms.

Start with Nano.
Grow into Vi.
Practice consistently.

Real-World ETL Pipeline from a Public Google Sheet

John Wakaba — Wed, 04 Feb 2026 08:52:02 +0000

Spreadsheets are everywhere.

They’re easy to use, easy to share, and often become the first home of business data. But they’re terrible for analytics, automation, and scale.

In this article, I’ll walk through how I built a production-style ETL pipeline that:

Extracts data from a public Google Sheet
Cleans and validates the data using Python
Loads the data into PostgreSQL and MongoDB
Handles real-world issues like UUIDs, connection strings, and performance bottlenecks

The Problem

A supermarket dataset was stored in a Google Sheet. While this works for manual inspection, it introduces several problems:

No schema enforcement
No support for analytics or BI tools
Poor performance for large queries
No safe way to integrate with applications

The goal was to move this data into proper databases while following real ETL best practices.

Architecture Overview

Public Google Sheet (CSV Export)
        ↓
     Python ETL
        ↓
  Transform & Validate
        ↓
 PostgreSQL (Analytics)   MongoDB (Documents)

Why two databases?

PostgreSQL acts as the system of record for analytics and reporting
MongoDB provides flexible, document-based storage for application access

Tools Used

Python 3.12
UV for dependency and environment management
Pandas for data transformation
Requests for HTTP-based extraction
PostgreSQL (via SQLAlchemy)
MongoDB (via PyMongo)
Loguru for structured logging

Step 1: Extracting Data from a Public Google Sheet

Instead of dealing with Google Cloud authentication, I used a simpler (and very realistic) approach.

Google Sheets exposes a CSV export endpoint for public sheets.

A human-friendly link like this:

https://docs.google.com/spreadsheets/d/<sheet-id>/edit

can be converted to:

https://docs.google.com/spreadsheets/d/<sheet-id>/export?format=csv&gid=<gid>

Python can then fetch the data directly:

response = requests.get(csv_url)
df = pd.read_csv(StringIO(response.text))

No API keys. No OAuth. Fully automated.

Step 2: Transforming the Data (Where Things Get Real)

This is where assumptions break.

I initially assumed the id column was numeric. It wasn’t.

It contained UUIDs like:

47d54138-a950-4ec0-9d4a-e637e8dfb290

Trying to cast this to an integer caused the pipeline to fail.

Lesson #1: The data always wins

The fix was simple but important:

Treat id as a string
Update both transformation logic and database schemas

Step 3: Loading into PostgreSQL

PostgreSQL is the backbone of the pipeline.

Key design decisions:

Strong schema enforcement
Idempotent inserts
Safe re-runs of the pipeline

The table is created if it doesn’t exist, and inserts use:

ON CONFLICT (id) DO NOTHING

This ensures:

No duplicate records
No need to truncate tables
Safe incremental runs

Step 4: Loading into MongoDB (and Fixing Performance)

My first MongoDB implementation used update_one() in a loop.

It worked — but it was painfully slow.

The fix was switching to bulk operations:

collection.bulk_write(operations, ordered=False)

This reduced load time.

Step 5: Debugging a Nasty PostgreSQL Error

One of the most confusing errors I hit was:

could not translate host name "4401@localhost"

It turned out the PostgreSQL password contained an @ symbol.

Lesson #3: Database passwords must be URL-safe

The solution was URL encoding:

KIM@4401 → KIM%404401

Results

After fixing these issues, the pipeline:

Runs end-to-end.
Can be safely re-run without duplicates
Loads clean data into both databases
Handles real-world data quirks correctly

Key Takeaways

Spreadsheets are common sources — but not suitable destinations
Never assume data types without inspecting real data
UUIDs are extremely common in production systems
Bulk operations matter for performance
Environment variables and connection strings are frequent failure points

Final Thoughts

This project wasn’t about flashy tools.

It was about building something real, breaking it, and fixing it.

Building a MedAdvantage RAF Engine with dbt & PostgreSQL (Step-by-Step Guide)

John Wakaba — Tue, 13 Jan 2026 10:09:54 +0000

In this project, I built a mini Medicare Advantage Risk Adjustment Factor (RAF) engine using PostgreSQL, dbt Core, and synthetic healthcare data.

The goal was to simulate a real-world healthcare analytics pipeline that transforms raw claims data into member-level risk scores.

This article walks through the entire process step by step, from raw CSV files to a final analytics mart.

1. Project Overview

The project models how healthcare organizations calculate risk scores using:

Member demographic data
Medical claims with ICD-10 diagnosis codes
Pharmacy claims with NDC drug codes
Reference mapping tables that convert codes into HCC and RxHCC categories

The final output is a table that shows:

Member ID
Service year
Gender and plan
Total HCC weight
Total RxHCC weight
Final risk score

This type of table is commonly used for actuarial analysis, reimbursement modeling, and population health analytics.

2. Tools & Environment Setup

Before starting the project, I installed and configured:

PostgreSQL as the data warehouse
DBeaver as the database client
Python + virtual environment (venv)
dbt Core with the Postgres adapter

This setup allows a modern ELT (Extract → Load → Transform) workflow where data is first loaded into the warehouse and then transformed using dbt.

3. Database Design

I used one PostgreSQL database with two schemas:

med_project → holds raw data and reference tables
analytics → holds all dbt models (staging, core, and marts)

Raw tables stored in `med_project`:

Members
Medical claims
Pharmacy claims
ICD-to-HCC mapping table
NDC-to-RxHCC mapping table

dbt models stored in `analytics`:

Staging models
Core models
Mart models (final analytics tables)

This separation keeps raw data immutable and transformed data fully governed by dbt.

4. Loading the Raw Data

Synthetic CSV files were generated and loaded into PostgreSQL using DBeaver’s Import Tool.

Each CSV corresponded to one raw table:

Members
Medical claims
Pharmacy claims

All raw table columns were stored as TEXT initially. This prevents ingestion failures during loading and allows all data type enforcement to be handled inside dbt.

5. Initializing the dbt Project

A new dbt project was created inside a Python virtual environment.

During initialization:

PostgreSQL was selected as the adapter
A profile was created in profiles.yml
The default target schema was set to analytics

The dbt connection was validated to ensure connectivity between dbt and PostgreSQL.

6. Registering Raw Tables as dbt Sources

To allow dbt to reference raw tables safely, a source configuration file was created.

This file tells dbt:

Which schema the raw tables are in
Which tables are considered authoritative raw sources
Which tables act as reference data (HCC & RxHCC mappings)

This enables consistent use of dbt sources and prevents hard-coding schema names inside models.

7. Building the Staging Layer

The staging layer is where all raw data is cleaned and standardized.

At this stage, I performed:

Explicit parsing of U.S.-formatted dates
Numeric type conversions for amounts and quantities
Trimming and upper-casing of ICD and NDC codes
Basic null handling

Three staging models were created:

stg_members
stg_medical_claims
stg_pharmacy_claims

These models ensure that all downstream data has:

Consistent data types
Clean formats
Reliable date values

8. Building the Core Layer

The core layer represents analytics-ready entities.

Here I focused on:

Creating one clean row per member
Passing through only validated claim and pharmacy records
Preparing the data for aggregation

Core models included:

Clean member dimension
Medical claim fact table
Pharmacy claim fact table

This layer removes duplication and creates stable structures used by the marts.

9. Mapping Diagnoses to HCCs

Medical claims were mapped to HCC categories using the ICD-to-HCC reference table.

At this stage:

Each member’s diagnosis codes were expanded
Codes were normalized
Valid diagnoses were matched to HCC categories

The output produces one record per member per year per HCC category, with an associated risk weight.

10. Computing Final Member Risk Scores

The final step combined:

Aggregated HCC weights from medical claims
Aggregated RxHCC weights from pharmacy claims
Member demographic attributes

For each member and service year:

HCC weights were summed
RxHCC weights were summed
A base score was added
A final risk score was computed

This produced the final analytics mart: member_risk_scores.

11. Results & Key Outcomes

Results

A fully functional end-to-end healthcare analytics pipeline
Clean transformation workflow from raw CSVs to final mart
A production-style member-level risk score table
Data ready for reporting tools like Power BI or Tableau

Key Takeaways

dbt enforces strong modeling discipline through layered architecture
Reference mapping tables are the backbone of healthcare risk analytics
Explicit type casting prevents silent data quality issues
Separation of raw, staging, core, and marts ensures scalability and auditability

12. Key Challenges Faced & Resolved

PostgreSQL CSV permission issues → Resolved by using client-side imports
Cross-database reference errors in dbt → Fixed by aligning the dbt database configuration
U.S. date format parsing errors → Solved by explicitly controlling date parsing in staging
Semicolon syntax errors in dbt models → Resolved by removing trailing semicolons
Source configuration mismatches → Fixed by correcting schema references

Final Thoughts

This project demonstrates how modern analytics engineering tools can simulate real Medicare Advantage risk modeling workflows using open-source technologies.

INTRODUCTION TO DBT(Data Build Tool)

John Wakaba — Mon, 24 Nov 2025 08:58:17 +0000

A Beginner-Friendly Guide to Modern Analytics Engineering

This article introduces dbt (data build tool) and explores several foundational concepts:

What dbt is
Core principles
Why we transform data
How dbt structures SQL development
How macros, tests, documentation, and ref work

🚀 What is dbt?

dbt is an open-source transformation framework that allows anyone comfortable with SQL to build modular, version-controlled, production-grade data pipelines.

Unlike ingestion tools, dbt does not move data; it transforms the data already in your warehouse.

🧹 Why Transform Data?

dbt helps reshape and standardize raw data for analytics:

Cleaning
Deduplication
Restructuring
Filtering
Aggregation
Joining

🏗 dbt Is Open Core

DBT CORE	DBT CLOUD
Open-source data transformation	Fully managed dbt experience
License: Apache 2.0	SaaS platform
SQL compiler, Jinja, adapters	IDE, scheduling, logging, alerting
CLI interface	Authentication & SSO

🔑 Core Concepts in dbt

Write transformations using SELECT statements
Build DAGs using ref()
Test models to ensure accuracy
Generate documentation
Use macros to write reusable SQL

🧩 1. CORE 1 : Express all transforms with SELECT statements

In dbt:

Everything is a SELECT statement

dbt takes care of the boilerplate and SQL DDL.

dbt Model Example

{{ config(materialized='table') }}

select *
from public.orders
where is_deleted = false

Compiled:

create table analytics.orders as (
    select *
    from public.orders
    where is_deleted = false
);

DBT supports several materialization strategies

Description	Syntax
Table	create table analytics.orders as (...)
View	create view analytics.orders as (...)
Ephemeral	Model interpolated into model 2
Incremental(Advanced)	Selective rebuild for new rows
Build your own

🔗 CORE 2 : Express relationships with {{ref}} statement

The {{ref (...)}} statement automatically handles dependencies in dbt models

The ref statement allows you to do two things

Interpolates the name of your schema
Builds an edge in the DAG between two models helping dbt understand dependencies

Use it like you would any table

select *
from {{ ref('base_orders') }}

🧪 CORE 3 : Easily build tests to ensure model accuracy

DBT has a framework for testing your models and datasets

dbt can test:

uniqueness
non-null
accepted values
foreign key relationships

📚 CORE 4 : Documentation is accessible and easily updated

When dbt generates documentation, it takes many aspects into account.

Everything it knows about your project

Description (from .yml file)
Model dependencies
Model SQL
Sources
Tests

Generate docs:

dbt docs generate && dbt docs serve

🧠 5. CORE 5 : Use Macros to write reusable/modular SQL

Using Jinja turns your dbt project into a programming environment for SQL

Use control structures (e.g., if statements and for loops) in SQL
Use environment variables in your dbt project for production deployments
Operate on the results of one query to generate another query
Abstract snippets of SQL into reusable macros — these are analogous to functions in most programming languages.

Macros in Jinja are pieces of code that can be used multiple times
Macro (macros/cents_to_dollars.sql)

{% macro cents_to_dollars(column_name, precision=2) %}
    ({{ column_name }} / 100)::numeric(16, {{ precision }})
{% endmacro %}

Model (models/stg_payments.sql)

select
    id as payment_id,
    {{ cents_to_dollars('amount') }} as amount_usd
from app_data.payments

🎯 Conclusion

dbt helps teams deliver:

Higher-quality datasets
Faster development cycles
Lower maintenance costs
Clear lineage

🌍 Building a Live Weather Dashboard for East Africa Using Power BI and OpenWeatherMap API

John Wakaba — Tue, 11 Nov 2025 08:02:27 +0000

💡 Introduction

Have you ever wished you could monitor real-time weather conditions across multiple cities — all in one dashboard?

This project started with a simple question:

“Can I connect Power BI directly to a live API and visualize up-to-date weather data?”

The answer turned out to be yes.

Using the OpenWeatherMap API, I built an interactive Power BI dashboard that tracks live temperature, humidity, and pressure for four East African cities — Nairobi, Mombasa, Kampala, and Kigali.

This article walks you through the journey, design decisions, and insights.

🧩 The Problem

Weather data in East Africa is often scattered across various platforms — news sites, mobile apps, and government portals. Comparing weather conditions between cities can be time-consuming and inconsistent.

As a data analyst, I wanted a unified, automated, and interactive dashboard that could:

Fetch live weather data automatically
Display real-time metrics
Let users explore and compare cities with intuitive visuals

In other words, a single dashboard where data meets interactivity.

🛠 Tools & Approach

Tool	Purpose
Power BI Desktop	Data visualization and dashboard design
Power Query (M language)	Connecting and transforming API data
OpenWeatherMap API	Live data source for weather metrics
DAX (Data Analysis Expressions)	Creating dynamic interactivity
GitHub	Hosting project files and documentation

The Workflow

Connect Power BI to OpenWeatherMap — to fetch weather data in real time.
Transform JSON responses in Power Query — to clean and standardize the data.
Design visuals that show temperature, humidity, and pressure.
Add interactivity with slicers, tooltips, and drillthrough pages.
Publish & document the project for sharing and reproducibility.

🎨 Designing the Dashboard

The dashboard is minimal, clean, and interactive.

At the top, three cards display the current temperature, humidity, and pressure for the selected city.

Below, a bar chart compares temperatures across all four cities.

A city slicer allows filtering, while hover tooltips and drillthrough pages reveal more details about each city.

Dashboard Highlights

Feature	Description
🌍 City Filter (Slicer)	Filter visuals by city dynamically
🌡 Interactive Cards	Display temperature, humidity, and pressure in real time
📊 Bar Chart Comparison	Compare temperatures across multiple cities
🧭 Drillthrough Page	Explore detailed city metrics
💬 Tooltip Page	Get insights by simply hovering
🔁 Automatic Refresh	Updates data directly from the API

📈 Insights & Key Takeaways

After visualizing the live data, a few trends became clear:

Mombasa consistently shows the highest temperatures, typical of coastal climates.
Kigali and Kampala are cooler and more humid due to their higher elevation.
The ability to compare cities side by side reveals regional climate variations in real time.

This dashboard goes beyond static reports — it offers contextual, live weather insights at a glance.

🧠 Lessons Learned

Building this project was a practical exercise in real-world API integration within Power BI.

Here are the biggest takeaways:

Power BI can handle live APIs, not just static data sources.
Error handling in Power Query is essential when dealing with real-time data.
DAX measures are the secret to responsive dashboards.
Good design is clarity — simplicity and focus always win.
Parameterizing API keys keeps your work secure and scalable.

🧭 Why This Project Matters

This project demonstrates how data visualization and API integration can solve everyday challenges — from environmental monitoring to urban planning.

For organizations, such dashboards can:

Support agriculture by tracking temperature and humidity trends.
Aid energy planning by monitoring pressure and heat variations.
Help logistics and travel businesses adjust operations based on weather.

The concept extends beyond weather — it’s a blueprint for any real-time data visualization project.

🌍 Automating Africa’s Energy Data Collection Using Python, Playwright(+Why Playwright ?), and MongoDB (2000–2024)

John Wakaba — Tue, 04 Nov 2025 12:07:16 +0000

⚡ Introduction

In today’s data-driven world, access to reliable and structured energy data is critical for decision-making, research, and policy planning.

However, most open data platforms in Africa — such as the Africa Energy Portal (AEP) — present information in dashboard views, which makes large-scale analysis tedious.

To address this challenge, I built a fully automated ETL (Extract, Transform, Load) pipeline that:

Scrapes energy indicators for all African countries (2000–2024),
Formats and validates the data for consistency,
And stores it in a MongoDB database for easy access and analysis.

This project uses Python, Playwright, and MongoDB, with automation powered by the lightweight dependency manager uv.

🧩 Problem Statement

While the Africa Energy Portal provides valuable country-level datasets, it does not offer a bulk download option.

Researchers, analysts, and energy planners need historical time-series data — such as:

Electricity generation and consumption
Renewable energy contribution
Access to clean cooking
Population electrification (rural vs urban)

Manually downloading data for 50+ African countries and 20+ years would take days — not counting inconsistencies in data formats and missing years.

The solution: automate it end-to-end.

🧠 Project Goals

Extract data for all African countries directly from the AEP website.
Transform it into a structured, tabular format for analysis.
Store it efficiently in MongoDB for scalability and retrieval.
Validate data completeness and consistency across countries and indicators.
Export the final cleaned dataset for analysis and sharing.

⚙️ Tools & Technologies

Purpose	Tool / Library	Role
Web scraping	Playwright	Automates browser-based data capture
Environment & Dependency Management	uv	Manages virtual environment and packages
Data storage	MongoDB	Stores country-wise metrics and year data
Data validation & analysis	`pandas`, `pydantic`	Cleans and structures data
Export	`openpyxl`	Saves Excel files
Scripting	`Python`	Glue for the entire ETL process

🔄 ETL Pipeline Overview

The pipeline consists of four modular stages:

Stage 1 – Data Extraction

Uses Playwright to navigate to each country’s profile page.
Intercepts the /get-country-data XHR response.
Extracts JSON payloads containing all available indicators and yearly values.

Each JSON record includes:

{
  "country": "Kenya",
  "metric": "Population with access to electricity - National",
  "sector": "ELECTRICITY ACCESS",
  "yearly": {
    "2015": 19.65,
    "2016": 25.73,
    "2022": 42.62
  }
}

Stage 2 – Data Formatting

Converts raw JSON into a tabular schema: ["country", "country_serial", "metric", "unit", "sector", "sub_sector", "sub_sub_sector", "source_link", "source", "2000", ..., "2024"]
Ensures each row represents one metric for one country.
Fills missing years with null values to maintain consistency.

Stage 3 – Data Storage

Inserts formatted records into MongoDB using pymongo.
Adds a unique index (country, metric, source) to prevent duplicates.
Upserts records — ensuring updates don’t create duplicates.

Each MongoDB document looks like this:

{
  "country": "Kenya",
  "metric": "Access to Clean Cooking%",
  "source": "Tracking SDG7/WBG",
  "2000": null,
  "2015": 11.9,
  "2020": 23.6,
  "2024": null
}

Stage 4 – Validation

Identifies missing years or inconsistent units.
Detects countries with incomplete datasets.
Exports a detailed validation_report.csv that flags issues automatically.

Sample output:
| issue_type | country | metric | details |
|-------------|----------|--------|----------|
| MISSING_YEARS | Kenya | Access to Clean Cooking% | 2000–2014, 2023–2024 |
| UNIT_INCONSISTENCY | ALL | Electricity Access | %; MW |

🧾 Data Export

Once the ETL pipeline finishes, data is exported to both CSV and Excel formats for analysis.

uv run python export_to_csv.py

Output files:

reports/exports/energy_data.csv
reports/exports/energy_data.xlsx

🎭 Why Playwright Was Essential for This Project

Many would wonder:

“Why not just use requests or BeautifulSoup to get the data?”

The Africa Energy Portal (AEP) website is highly dynamic — it doesn’t serve raw data directly in the page HTML. Instead, when you open a country page like
https://africa-energy-portal.org/country/kenya,
the browser first loads a basic template, and then JavaScript makes a hidden request to an internal endpoint:

POST https://africa-energy-portal.org/get-country-data

This is the actual source of all the energy statistics.

🔹 1. Dynamic JavaScript Rendering

The data (electricity access, renewables, etc.) is fetched asynchronously after the page loads.
Traditional libraries like requests only download the HTML shell, missing all those dynamic values.

Playwright, however, executes JavaScript in a real browser, allowing it to:

Wait until the data request is made,

Intercept the /get-country-data response,

Capture the full JSON payload in real time.

This gives us clean, structured data instead of messy HTML scraping.

🔹 2. Cloudflare and Anti-Bot Protection

The AEP website uses Cloudflare security, which blocks automated clients that don’t behave like browsers.
When we tried using requests, we got frequent 403 (Forbidden) and 500 (Server Error) responses.

Playwright solves this because it:

Runs a full Chromium browser (just like Chrome or Edge).

Sends real headers, cookies, and browsing patterns.

Is indistinguishable from a human visitor.

This made it the only reliable way to consistently access data without breaking terms of service or scraping hidden content.

🔹 3. Network Interception

Playwright allows us to listen for specific network calls using:

page.expect_response(lambda r: "get-country-data" in r.url and r.status == 200)

That means we can:

Capture the JSON the site uses internally,

Save it instantly,

And avoid parsing the visual layout at all.

It’s essentially like “catching the data packet” as it flies through the browser.

🔹 4. Reliability & Control

Because we were scraping 50+ countries, we needed:

Timeout handling (some pages take 30–60s to load),

Retries for network issues,

Throttling to avoid overwhelming the server.

Playwright gives us those controls — ensuring we get consistent, ethical, and stable scraping.

5. Clean ETL Integration

With Playwright, our Stage 1 data extraction directly produces structured JSON, like:

{
  "country": "Kenya",
  "metric": "Access to Clean Cooking%",
  "sector": "Basic data",
  "yearly": {
    "2015": 11.9,
    "2016": 13.8,
    "2022": 30.0
  }
}

That JSON is ready for transformation and MongoDB storage, making the pipeline efficient end-to-end.

In short:
Playwright wasn’t just a choice — it was a necessity.
It allowed this ETL project to move from fragile scraping to robust, browser-level automation, ensuring accurate and repeatable extraction of Africa’s energy data. ⚡

⚠️ Challenges Faced

Challenge	Description
Cloudflare protection	The AEP website blocked simple HTTP requests (403, 500). Solved by using Playwright’s browser simulation to mimic human behavior.
Slow response times	Some pages took >30 seconds to return data. Added retry logic and longer timeouts.
Inconsistent URL naming	Country URLs (like `cote-d’ivoire` vs `cote-divoire`) required slug normalization logic.
Incomplete datasets	Some countries lacked data for certain years, handled via validation.
Browser resource use	Playwright’s real browser automation was resource-heavy; introduced throttling to manage load.

📊 Results

✅ Successfully extracted data for 50 African countries
✅ Collected 500+ indicators covering 2000–2024
✅ All records stored in MongoDB with proper schema
✅ Automated validation caught missing and inconsistent data
✅ Exportable formats ready for visualization and analysis

💡 Key Takeaways

Automating data extraction from protected websites is possible using browser-level automation (Playwright).
Designing modular ETL stages makes maintenance and debugging easier.
Data validation is just as important as extraction — raw data is rarely clean.
Storing data in MongoDB offers flexibility for hierarchical (nested) data structures.

🧠 Future Work

Build an interactive dashboard using Streamlit or Power BI.
Automate periodic updates (monthly/quarterly).
Add country-level time-series visualization modules.