DEV Community: Kelvin Ndirangu

Integrating Jupyter Notebooks, PostgreSQL, and Power BI in Modern Data Workflows

Kelvin Ndirangu — Sun, 18 May 2025 19:16:09 +0000

Organizations are currently drowning in a sea of information in this information age. But data itself is not power, what we do with it that makes all the difference. For a lot of contemporary businesses, there is simply more to successful data transformation than easily available tools can offer. It requires a smart combination of technologies responsible for the whole journey. gathering, cleaning, stocking and analyzing the data before the final visualizing of the data. This is where the trio of Jupyter Notebooks, PostgreSQL, and Power BI excels.

A Real-World Need: From Raw to Insight

Imagine working for a real estate company in Kenya. You have thousands of listings: each with details like price, number of bedrooms, location, and size. But not all entries are complete. Some lack key information, others have inconsistent formatting, and a few are just messy. Your task is to turn this scattered data into meaningful dashboards your team can use to make real business decisions.

Where do you start?

That’s where modern data workflows come in. Let’s break it down with tools that many data professionals already know and love.

Step 1: Data Wrangling with Jupyter Notebooks (The “E” and “T” in ETL)

Jupyter Notebooks have become a favorite playground for data scientists. With the help of Python libraries like Pandas, data wrangling becomes efficient and transparent.
You start by importing your CSV or JSON files into Jupyter. Using code, you clean up your dataset, removing null values, splitting text like “4 Bedrooms” into the number 4, converting price fields into the numeric format, or even detecting duplicates.

But it’s more than just cleaning. You might engineer new features like calculating price per square meter or categorizing locations into zones (e.g., urban, suburban, rural). With a few lines of Python, you’re reshaping data to make it analysis-ready.
In technical terms, this is the Extract and Transform part of the ETL (Extract, Transform, Load) pipeline.

Step 2: Structured Storage in PostgreSQL (The “L” in ETL)

Once your data is clean, the next step is storing it in a reliable database. PostgreSQL, a powerful open-source relational database is a natural choice. It’s stable, scalable, and handles structured data like a charm.
Using libraries like SQLAlchemy or psycopg2, you can write your cleaned data from Jupyter directly into a PostgreSQL table. Now, your data isn’t just floating around in Excel sheets—it’s secure, queryable, and ready to serve a growing team.
The beauty of PostgreSQL? It allows you to write powerful SQL queries, join data from multiple tables, enforce data validation rules, and prepare filtered views specifically for reporting.

Step 3: Visualizing with Power BI

With your data safely stored and structured, it’s time to bring it to life.
Power BI is a top-tier visualization tool. It connects directly to your PostgreSQL database (either via a live connection or scheduled import), letting you pull in the latest data in just a few clicks.
Here’s where business decisions happen.
You create:
•A heatmap of Kenya showing the most expensive areas.
•Scatter plots showing the relationship between price and bedrooms.
•Bar charts ranking locations by average price.
•KPI cards that show how many listings are missing key data like size or price.

The best part? Power BI dashboards are interactive. A manager can filter to view only listings in Nairobi or only apartments with at least 3 bedrooms.

Building an Automated, End-to-End Pipeline

The magic happens when you automate this entire process.
•A new dataset arrives every week.
•Your Jupyter notebook cleans and transforms the data.
•The transformed data is automatically written to PostgreSQL.
•Power BI refreshes the dashboard daily.

No more late nights manually cleaning spreadsheets. No more “Where’s the latest file?” emails. Instead, you have built a workflow that runs like a well-oiled machine.

The Human Side of the Workflow

This is not just about tools. It’s about trust. Decision-makers need to trust the data they see. Automation and structure reduce human error. Clean dashboards increase confidence. Teams collaborate better when everyone sees the same source of truth.

As the analyst or data scientist behind the scenes, you become more than a number cruncher; you become a translator of data into decisions.

Conclusion

Modern data workflows are not a luxury they are a necessity. Jupyter Notebooks give you the flexibility and control to clean data intelligently. PostgreSQL provides a strong backbone for storing and querying structured data. Power BI turns those numbers into stories that executives can act on.
When integrated properly, these three tools empower teams to move from chaos to clarity and from data to direction.

Mastering SQL Through a Real-World Project: Building a Student Course Management System By Kelvin Ndirangu

Kelvin Ndirangu — Sat, 03 May 2025 21:18:11 +0000

Why SQL Projects Matter

**
Learning SQL through tutorials is helpful, but nothing beats applying it to a real project. I built a Student Course Management System from scratch using SQL. This project helped me understand how real-world databases are structured, how data flows between tables, and how to write powerful queries to extract insights.
In this blog post, I’ll Walk you through how I applied key SQL concepts like schema design, joins, aggregate functions, views, indexes, and triggers throughout this project.

Project Overview: Student Course Management System
This system was designed for an EdTech school to manage:

Students
Courses
Instructors
Enrollments and Grades

Step 1: Designing the Database Schema
The first challenge was designing a relational schema with the right relationships.
Here are the main tables:

students student_id (PK) first_name, last_name, email, date_of_birth
instructors instructor_id (PK) first_name, last_name, email
courses course_id (PK) course_name, description, instructor_id (FK)
enrollments enrollment_id (PK) student_id, course_id (FKs) enrollment_date, grade

Schema Lesson:
Design your foreign keys to enforce data consistency. This prevents students from enrolling in courses that don’t exist or instructors from teaching phantom subjects.

Step 2: Populating the Database
I created sample data using INSERT statements for:

10 students
3 instructors
5 courses
15 enrollments with grades This gave me a realistic dataset to work with in the next step queries!

Step 3: Writing Real-World SQL Queries
Here’s where it got exciting. I wrote a series of queries that simulate the actual reporting needs of an EdTech platform.

Students who enrolled in at least one course:

SELECT DISTINCT s.first_name, s.last_name
FROM students s
JOIN enrollments e ON s.student_id = e.student_id;

Students enrolled in more than two courses:

SELECT s.first_name, s.last_name, COUNT(e.course_id) AS course_count
FROM students s
JOIN enrollments e ON s.student_id = e.student_id
GROUP BY s.student_id
HAVING COUNT(e.course_id) > 2;

Courses with number of enrolled students:

SELECT c.course_name, COUNT(e.student_id) AS total_students
FROM courses c
LEFT JOIN enrollments e ON c.course_id = e.course_id
GROUP BY c.course_name;

Average grade per course:

SELECT c.course_name,
AVG(CASE grade
WHEN 'A' THEN 4
WHEN 'B' THEN 3
WHEN 'C' THEN 2
WHEN 'D' THEN 1
WHEN 'F' THEN 0
END) AS avg_gpa
FROM enrollments e
JOIN courses c ON e.course_id = c.course_id
GROUP BY c.course_name;

Top 3 students by average grade:

SELECT s.first_name, s.last_name,
AVG(CASE grade
WHEN 'A' THEN 4
WHEN 'B' THEN 3
WHEN 'C' THEN 2
WHEN 'D' THEN 1
WHEN 'F' THEN 0
END) AS avg_gpa
FROM students s
JOIN enrollments e ON s.student_id = e.student_id
GROUP BY s.student_id
ORDER BY avg_gpa DESC
LIMIT 3;

SQL Lessons:

Use CASE statements for custom logic like grade conversion.
JOIN is your best friend for combining data across tables.
GROUP BY + HAVING is essential for filtering aggregates.

Step 5: Hosting on GitHub
I wrapped everything into a public GitHub repository with:

SQL scripts
README.md with setup instructions
Entity Relationship Diagram (ERD) GitHub link: https://github.com/KELVINNDIRANGU/SQL-PROJECT-2

Key Learnings

Schema Design matters normalize wisely.
Foreign Keys keep your data sane.
Views simplify repeated queries.
Triggers help with automation and audit logging.
Indexing is crucial for performance as your data grows.

Conclusion
This project transformed how I view SQL not as a list of commands, but as a tool to build real, functional systems. Whether you're preparing for a job, managing data, or automating reports, SQL is foundational.

Unlocking Excel’s Power: Master Data Analysis with Real-World Examples

Kelvin Ndirangu — Sun, 27 Apr 2025 19:56:31 +0000

Microsoft Excel isn’t just a spreadsheet tool; it’s your secret weapon for transforming raw data into actionable insights! Whether you're tracking sales, managing budgets, or analyzing trends, mastering Excel can elevate your data game.

Let’s dive into how Excel can make data analysis faster, clearer, and more interactive with some real-world examples.
Charts & Graphics: Turn Numbers into Stories
Want to showcase your monthly sales or track performance over time?

Excel's charts are your go to for visualizing data effectively:

Column Charts: Imagine you’re comparing total sales across products the Column Chart will help you clearly see which product performs best.
Line Charts: Looking to track sales growth over several months? A Line Chart will show you exactly where you’re gaining or losing momentum. For example, if you sell notebooks, this can help you spot seasonal trends.
Pie Charts: Need to show how much each category contributes to total sales? Use a Pie Chart to see the percentage breakdown – think "what portion of my sales come from electronics versus stationery?"
Scatter Charts: Ever wondered if there's a relationship between price and stock levels? A Scatter Chart is perfect for visualizing price vs. inventory to see if your product prices align with stock availability.

Sparklines & Data Bars: Data At a Glance
Sometimes you need a quick way to see trends without cluttering your worksheet. Sparklines and Data Bars let you display mini charts within cells, perfect for fast decision-making. For instance, with Sparklines, you can quickly track monthly sales trends next to each product's data. Data Bars visually represent sales volume, allowing you to instantly compare which products are driving the most revenue.

PivotTables & Pivot Charts: Summary and Insights at Your Fingertips
Managing large datasets is a breeze with PivotTables. Imagine you’re managing sales data across different regions, you can easily summarize and group by region, category, or product. Need a dynamic visual? Convert your PivotTable into a PivotChart to instantly see trends and comparisons.

Bonus: Slicers add interactivity! Filter your data with just a click to focus on specific regions, categories, or periods, perfect for creating dashboards or reports.

Data Cleaning: Prepare Your Data for Analysis
Before diving into analysis, clean your data to ensure accuracy. Let’s say you have a list of customers, but there are duplicates and inconsistent names. With Remove Duplicates, TRIM(), and PROPER(), you can ensure that names are properly formatted and that no duplicate entries mess up your analysis. Use IF(ISBLANK()) to handle missing values – replace them with zeros or placeholders to ensure calculations are correct.

Real-Life Example: Track Product Sales
Let’s say you’re a sales manager for a retail company. Your team tracks product sales across different regions, and you need to create a report for an upcoming meeting. Here’s how Excel can help:
1.Use PivotTables to summarize total sales by region and product category.
2.Generate a PivotChart to show a visual breakdown of sales by region.
3.Add Sparklines in the adjacent columns to display monthly trends for each product.
4.Create a Column Chart to compare the total sales per product.
With just these simple steps, you’ve not only summarized your data but made it interactive and easy to understand.

Excel Is Your Data Superpower
Excel is an essential tool for anyone handling data. Whether you’re organizing finances, tracking sales, or analyzing performance, learning how to use its powerful features like charts, PivotTables, and data cleaning techniques will help you unlock a world of possibilities.
Keep experimenting, and soon, Excel will become your go-to tool for all your data analysis needs. Ready to master it? Let’s get started!