DEV Community: grace wambua

Managing Academic Data: A Practical Application of SQL in PostgreSQL

grace wambua — Tue, 14 Apr 2026 01:16:43 +0000

Introduction

Databases are essential for storing and managing data in modern applications. Many organizations turn to PostgreSQL, a powerful, open-source object-relational database management system known for its reliability and data integrity.
By leveraging Structured Query Language (SQL), we can build an interconnected system that not only stores information but also reveals meaningful insights through advanced relationships and filtering.
Most DBMSs operate using two core components: Data Definition Language (DDL) and Data Manipulation Language (DML). Together, DDL and DML handle the computation within a DBMS, while the database itself stores the data.
Here are the key differences between DDL and DML:

Aspect	DDL	DML
Purpose	Defines and manages the database schema and structure.	Manipulates and manages actual data records.
Commands	`CREATE`, `ALTER`, `DROP`, `TRUNCATE`, `RENAME`	`SELECT`*, `INSERT`, `UPDATE`, `DELETE`, `MERGE`
Effect	Changes the structure of tables, indexes, or views.	Changes only the rows or records within existing tables
Auto-Commit	Changes are permanent immediately (auto-committed) in most databases.	Changes are not permanent until a `COMMIT` command is issued.
Rollback	Generally cannot be undone once executed.	Can be rolled back (undone) if not yet committed.
WHERE Clause	Cannot use a `WHERE` clause.	Can use a `WHERE` clause to target specific records.

In my recent database assignment, I developed a structured system to manage student records, curriculum details, and examination performance.
Using PostgreSQL, I implemented the four core SQL operations: CREATE, INSERT, UPDATE, and DELETE, to build and maintain a functional educational database.

CREATE

The first step involved defining the architecture of the database. I began by creating a dedicated schema to ensure all school data remained organized.

create schema nairobi_academy;

Within this schema, I used the CREATE TABLE command to build three interconnected entities:

students

create table students(
student_id INT primary key,
first_name VARCHAR(50) not null, 
last_name VARCHAR(50) not null,
gender VARCHAR(1), 
date_of_birth DATE,
class VARCHAR(10),
city VARCHAR(50)
);

subjects

create table subjects(
subject_id INT primary key,
subject_name VARCHAR(100) unique not null,
department VARCHAR(50),
teacher_name VARCHAR(100),
credits INT
);

exam_results

create table exam_results(
result_id INT primary key,
student_id INT not null, 
subject_id INT not null, 
marks INT not null,
exam_date DATE, 
grade VARCHAR(2)
);

INSERT

I used INSERT INTO to transform the empty tables into a live database. I successfully migrated data for 10 students and 10 core subjects. This phase also included recording initial exam marks and grades, providing a guideline of data to work with.

insert into exam_results (result_id, student_id, subject_id, marks, exam_date, grade)
values
(1, 1, 1, 78, '2024-03-15', 'B'),
(2, 1, 2, 85, '2024-03-16', 'A'),
(3, 2, 1, 92, '2024-03-1', 'A'),
(4, 2, 3, 55, '2024-03-17', 'C'),
(5, 3, 2, 49, '2024-03-16', 'D'),
(6, 3, 4, 71, '2024-03-18', 'B'),
(7, 4, 1, 88, '2024-03-15', 'A'),
(8, 4, 6, 63, '2024-03-19', 'C'),
(9, 5, 5, 39, '2024-03-20', 'F'),
(10, 6, 9, 95, '2024-03-21', 'A');

UPDATE

I utilized the UPDATE command to ensure the records remained accurate over time.

update students 
set  city = 'Nairobi'
where student_id = 5;

update exam_results 
set marks = 59
where result_id = 5;

DELETE

The final part involved data cleanup. Using the DELETE statement, I removed obsolete entries from the system.

delete from exam_results 
where result_id = 9;

Additionally, I used structural commands like ALTER TABLE to drop unnecessary columns.

alter table students
drop column phone_number;

Filtering with WHERE

The WHERE clause only allows rows that meet specific criteria to appear in the results. To make these filters precise, I utilized several key SQL operators.

The = Operator: Used in this case to retrieve specific records.

select * from students 
where class = 'Form 4';

The > Operator: This is essential for analyzing performance and numerical data.

select * from exam_results
where marks >= 70;

The BETWEEN Operator: Here BETWEEN provides a cleaner way to filter within a specific range.

select * from exam_results
where marks between 50 and 80;

The IN Operator: This filters records where a column matches one of the specified values.

select * from students
where city in ('Nairobi', 'Mombasa', 'Kisumu');

The LIKE Operator: is used within a WHERE clause to search for a specified pattern in a column. It is specifically case-sensitive by default.

select * from subjects 
where subject_name like '%Studies%';

I also combined these filters for deeper insights. For instance, using WHERE class = 'Form 3' AND city = 'Nairobi' allowed me to find a very specific subset of students: those in a particular year who also live in that city.

Using CASE WHEN to Transform Data

In a school setting, a score of "85" or "49" is more than just a number, it represents a student's progress. I used CASE WHEN to automatically categorize performance.

select marks,
case 
    when marks >= 80 then 'Distinction'
    when marks >= 60 then 'Merit'
    when marks >= 40 then 'Pass'
    when marks < 40 then 'Fail'
end as performance
from exam_results;

Additionally, I used the CASE statement combined with the IN operator to define Student Levels:

select first_name, last_name, class,
case
    when class in ('Form 3', 'Form 4') then 'Senior'
    when class in ('Form 2', 'Form 1') then 'Junior'
end as student_level
from students;

A reflection on my study findings

What I found most interesting was the use of the CASE WHEN statement, being able to take a column of raw numbers and instantly turn them into human-readable labels like "Distinction" or "Senior" without actually changing the underlying data.
The biggest challenge was the strictness of SQL syntax. One missing semicolon or a misspelled column name command can cause errors in the entire system.
The transition from writing basic code to deploying a functional database was a rewarding challenge. This experience highlighted that SQL proficiency goes beyond technical syntax; it is about developing the logical framework required to communicate effectively with data and extract meaningful insights.

A Comprehensive Guide to Publishing and Embedding Power BI Reports on the Web with IFrames

grace wambua — Tue, 07 Apr 2026 20:51:51 +0000

Introduction

Power BI is Microsoft's business analytics platform that helps you turn data into actionable insights. Whether you're a business user, report creator, or developer, Power BI offers integrated tools and services to connect, visualize, and share data across your organization.

Power BI is made up of 3 main elements:

Power BI Desktop: a free desktop application for building and designing reports
Power BI Service: the online publishing service for viewing and sharing reports and dashboards.
Power BI mobile apps: for viewing reports and dashboards on the go.

The purpose of BI is to track Key Performance Indicators (KPIs) and uncover insights in business data to better inform decision-making across the organization.

Publishing Reports to Power BI Service

Reports and dashboards can be shared with others by users who have a Power BI pro license and have published them to a PBI workspace where others can view them.
A workspace is essentially a centralized location for collaboration.
Reports and dashboards can be distributed in many different ways including being downloaded as .pbix files, shared via teams, etc.

Here are step-by-step instructions on how to publish a Power BI report and embed it on a website using iframes, with an Electronic Sales Data report.

Publish Report to Power BI Service

Navigate to Workspaces on the BI Service

Create a workspace where you will publish your report.

Open your report in Power BI Desktop.
Click Publish on the Home ribbon.
Select the workspace and click Select.

Generate Embed Code

Log in to the Power BI Service and navigate to your report.
Go to File > Embed report > Website or portal (for secure embedding)

Embed in Website

Copy the <iframe> HTML code provided in the dialog box.
Paste this code into the HTML editor of your website.

Open the HTML file on a web browser and your BI report will successfully be embedded on a website

Conclusion

Mastering the art of embedding BI reports not only enhances user experience but also positions your organization to thrive by transforming data into actionable strategies.

Data Modeling in Power BI: Joins, Relationships, and Schemas

grace wambua — Tue, 31 Mar 2026 20:02:02 +0000

Introduction

Data modeling is the process of organizing your data into tables, defining relationships between them, and enhancing the data with calculated fields, measures, and hierarchies. This process ensures accurate analysis and sets you up to create clear, impactful Power BI reports.

Types of SQL Joins

Joins are one of the most important features that SQL offers. Joins allow us to make use of the relationships we have set up between our tables.
In this article, we’ll break down the core SQL join types:

1. INNER JOIN

The SQL INNER JOIN statement joins two tables based on a common column and selects rows that have matching values in these columns.

Example:

SELECT *
FROM employees
INNER JOIN departments
ON employees.department_id = departments.id;

In this query:

employees.department_id refers to the department_id column from the employees table.
departments.id refers to the id column from the departments table.

The ON clause ensures that rows are matched based on these columns, creating a relationship between the two tables.
The query above returns all the fields from both tables. The INNER keyword only affects the number of rows returned, not the number of columns. The INNER JOIN filters rows based on matching department_id and id, while the SELECT * ensures all columns from both tables are included.

2. LEFT JOIN

The SQL LEFT JOIN combines two tables based on a common column. It then selects records having matching values in these columns and the remaining rows from the left table.

Example:

-- left join Customers and Orders tables based on their shared customer_id columns
-- Customers is the left table
-- Orders is the right table

SELECT Customers.customer_id, Customers.first_name, Orders.item
FROM Customers
LEFT JOIN Orders
ON Customers.customer_id = Orders.customer_id;

Here, the SQL command combines data from the Customers and Orders tables.

The query selects the customer_id and first_name from Customers and the amount from Orders.

Hence, the result includes rows where customer_id from Customers matches customer from Orders.

3. RIGHT JOIN

The SQL RIGHT JOIN statement joins two tables based on a common column. It selects records that have matching values in these columns and the remaining rows from the right table.

Case Example

-- join Customers and Orders tables
-- based on customer_id of Customers and customer of Orders
-- Customers is the left table
-- Orders is the right table

SELECT Customers.customer_id, Customers.first_name, Orders.amount
FROM Customers
RIGHT JOIN Orders
ON Customers.customer_id = Orders.customer;

Here, the SQL command selects the customer_id and first_name columns (from the Customers table) and the amount column (from the Orders table).

And, the result set will contain those rows where there is a match between customer_id (of the Customers table) and customer (of the Orders table), along with all the remaining rows from the Orders table.

4. FULL OUTER JOIN

The SQL FULL OUTER JOIN statement joins two tables based on a common column. It selects records that have matching values in these columns and the remaining rows from both of the tables.

Use Case Example

SELECT Customers.customer_id, Customers.first_name, Orders.amount
FROM Customers
FULL OUTER JOIN Orders
ON Customers.customer_id = Orders.customer;

Here, the SQL command selects the customer_id and first_name columns (from the Customers table) and the amount column (from the Orders table).

The result set will contain all rows of both the tables, regardless of whether there is a match between customer_id (of the Customers table) and customer (of the Orders table).

5. ANTI JOIN

The SQL LEFT ANTI JOINreturns rows in the left table that have no matching rows in the right table. How it works: Achieved with LEFT JOIN + WHERE [key in right table] IS NULL.
The SQL RIGHT ANTI JOIN returns rows in the right table that have no matching rows in the left table. How it works: Achieved with RIGHT JOIN + WHERE [key in left table] IS NULL.

Case Example

SELECT *
FROM tableA
LEFT JOIN tableB ON tableA.key = tableB.key
WHERE tableB.key IS NULL;

This query will return all rows from tableA that do not have a corresponding row in tableB

CARDINALITY

In Power BI, the term “cardinality” describes the type of relationship between two tables according to how many related rows each table has. It specifies the relationships between rows in one table and rows in another.

One-to-Many (1 : *): In this relationship, one record in the first table connects to many records in the second table.
Example:
Each customer can have multiple orders, but each order belongs to only one customer.
In the model view, this appears as 1 --> *
The “1” side is usually a dimension table (like Customers).
The “many” side is a fact table (like Orders).
Many-to-One (* : 1): This is the reverse direction of a one-to-many relationship. It happens when the filter starts from the many side and moves to the one side.
Example:
If you select a particular order in a visual (from the Orders table), Power BI can trace it back to the correct Customer in the Customers table.
Multiple orders (many) point to one customer (one), that is a many-to-one relationship.
One-to-One (1 : 1): Each record in one table matches exactly one record in another table.
Example:
Assume, you have a Customer table and a Customer Profile table. Each customer ID appears only once in both tables.
Useful when you split a large table into smaller parts for better performance.
Many-to-Many (* : *): Both tables can have repeating values in their key columns
Example:
Imagine you are a student and you want to join club, each student can join multiple clubs and each club can have multiple students.
Power BI manages this scenario using a bridge table, which maps each student to each club.

Active Vs. Inactive Relationships

Active
An active relationship in Power BI is the primary, default connection between two tables. Power BI automatically uses active relationships for filtering and calculations unless you specify otherwise.
You can only have one active relationship between two tables at a time, even if there are multiple potential ways they could be related.
Example:
Consider a Sales table and a Dates table. You might have a relationship based on the OrderDate field. If this is the main date you want to use for your analysis, it will be marked as the active relationship.

Inactive
An inactive relationship is a secondary connection between two tables that Power BI does not automatically use for filtering or calculations. These relationships are useful when you need multiple ways to connect tables, but only one connection should be used by default.
Inactive relationships can be activated manually in specific measures or calculations using DAX (Data Analysis Expressions).
Example:
In addition to OrderDate, your Sales table might also have a ShipDate field that relates to the Dates table. You can create an inactive relationship between ShipDate and Dates, which you can activate selectively when needed.

CROSS FILTER

Cross filtering in Power BI determines how filters are applied across related tables in a relationship. It defines the direction in which the filter context flows between tables.
There are two types of cross filter direction:

Single Direction: Filters flow from one table to another, for example: from Customer to Orders. This is the default and most efficient setup.
Both Direction/Bidirectional: Filters flow both ways between tables. Used when both tables should influence each other, for example: Region and Sales.

How Cardinality and Cross Filtering work together

Cardinality defines how tables are connected, while cross filtering defines how filters move through those connections. Together, they ensure that your visuals respond correctly to user actions.
Example:
Cardinality: One-to-Many between Customers and Orders.
Cross Filter Direction: Single from Customers to Orders.
Result: Selecting a customer filters their orders but not the other way around.

Difference between Relationships and Joins

Relationships:

Are displayed as flexible noodles between logical tables
Require you to select matching fields between two logical tables
Do not require you to select join types
Make all row and column data from related tables potentially available in the data source
Maintain each table's level of detail in the data source and during analysis
Create independent domains at multiple levels of detail. Tables aren't merged together in the data source.
During analysis, create the appropriate joins automatically, based on the fields in use.
Do not duplicate aggregate values (when Performance Options are set to Many-to-Many)
Keep unmatched measure values (when Performance Options are set to Some Records Match)

Joins:

Are displayed with Venn diagram icons between physical tables
Require you to select join types and join clauses
Joined physical tables are merged into a single logical table with a fixed combination of data
May drop unmatched measure values
May duplicate aggregate values when fields are at different levels of detail
Support scenarios that require a single table of data, such as extract filters and aggregation

Fact Vs. Dimension Tables

Characteristic	Fact Table	Dimension Table
Basic Definition	It contains data (often transactional) that you want to analyze	It accompanies the fact table and stores information that describe records in the fact table
Purpose	It contains measures and is used for analysis and decision making	It contains information about a business and its process
Type of Data	Numeric and textual format	Textual format
Primary/Foreign Key	A primary key for each dimension which is acts as a foreign key in the dimension table	A foreign key associated with the primary key of the fact table
Hierarchy	No hierarchy	Contains a hierarchy
Attributes	Less attributes	More Attributes
Records	More Records	Less Records
Table Growth	Grows vertically	Grows horizontally
Data Model	Fewer fact tables in the data model	More dimension tables in a data model
Update Frequency	Records added very frequently	Records not added frequently

Star Schema

A star schema is a way to organize data in a database, especially in data warehouses, to make it easier and faster to analyze. At the center, there's a main table called the fact table, which holds measurable data. Around it are dimension tables.

Snowflake Schema

A snowflake schema splits dimension tables into smaller sub-dimensions to keep data more organized and detailed; just like snowflakes in a large lake.

Flat Table

A flat table in Power BI is a single, wide table containing all data, including measures and descriptive attributes, without relationships, similar to a spreadsheet.

OrderID	Date	Product	Category	Customer	Region	Qty	Unit Price
1001	01/01/26	Laptop	Electronics	Alice	North	1	1000
1002	01/02/26	Desk	Furniture	Bob	South	2	150
1003	01/02/26	Laptop	Electronics	Charlie	North	1	1000

Role-playing Dimensions

Role-playing dimensions in Power BI occur when a single dimension table (e.g. Date) connects to a fact table multiple times, representing different roles (e.g. Order Date, Ship Date). Power BI allows only one active relationship between tables; subsequent roles are inactive.

Common Modeling Issues

Building Models from CSV exports.
Missing Unique Keys.
Using Multiple date tables.
Overlapping attributes across tables.
Lack of normalisation.
Ignoring virtual relationships.
Keeping unnecessary columns.

Conclusion

I hope you found this blog helpful in understanding the significance of data modeling and the basic principles crucial to building an effective data model. By understanding these fundamental concepts, such as star schemas, cardinality, cross-filter direction, and active and inactive relationships, you should be well on your way to becoming a more proficient Power BI developer.

Resource Monitoring for Data Pipelines

grace wambua — Sat, 28 Mar 2026 09:21:30 +0000

As a data engineering student, I came to a realization that sometimes the errors that slowly starve resources, don't always throw a code. Monitoring how our pipelines consume resources isn't just about performance, its about respect for the hardware. This article is about understanding the machines we built on, to maintain fast and efficient pipelines.

Introduction

When running data pipelines, especially in production, resource monitoring is critical to prevent slowdowns, crashes, or system-wide failures. Simple Linux command-line tools like top, htop, df -h, and free -h provide real-time visibility into system health and help you catch issues before they escalate.

1. Monitoring CPU & Processes: `top` and `htop`

`top` (Built-in, lightweight)

The top command gives a live view of system processes and CPU usage.

Shows:

CPU utilization (user, system, idle time)
Running processes and their CPU/memory consumption

Why it matters for pipelines:

Identify CPU bottlenecks during heavy transformations (e.g., Spark jobs, ETL scripts)
Detect runaway processes consuming excessive CPU
Spot when multiple pipelines overload the system

Tip: Press P inside top to sort by CPU usage.

`htop` (Enhanced, user-friendly)

htop is an improved version of top with a more intuitive interface.

Features:

Color-coded CPU, memory, and swap usage
Easy process management (kill, renice)
Tree view of processes (great for pipeline dependencies)

Pipeline use cases:

Visualize parallel jobs in distributed pipelines
Quickly terminate stuck or zombie tasks
Monitor thread-level activity in real time

2. Monitoring Memory Usage: `free -h`

The free -h command shows memory usage in a human-readable format.

Key metrics:

Used
Free
Buffers/cache
Swap usage
Available

Example use:

If your data pipeline loads large datasets into memory (e.g. Pandas, Spark), watch the available memory
If it drops too low, the system may start swapping, drastically slowing performance

Best practice:

Ensure pipelines don’t consume all RAM; leave headroom for the OS and other services

3. Monitoring Disk Space: `df -h`

The df -h command displays disk usage across mounted filesystems.

Shows:

Total, used, and available disk space
Usage percentage per filesystem

Data pipelines often generate:

Temporary files
Logs
Intermediate datasets

If disk fills up:

Jobs may fail unexpectedly
Databases or services can crash

Common risk:

A pipeline writing large intermediate files (e.g. CSV/Parquet) can silently fill up disk,causing job failure or system instability.

Tip:

Watch for partitions approaching 90–100% usage
Clean up temp directories or rotate logs regularly

Preventing Production Failures

By combining these tools, you can proactively protect your system:

High CPU usage (top/htop)
Indicates inefficient code or too many parallel jobs
Low available memory (free -h)
Risk of crashes or heavy swapping
High disk usage (df -h)
Risk of failed writes and system instability

Practical Workflow for Data Engineers

Start your pipeline
Open another terminal and run:

htop: monitor CPU + processes
watch free -h: track memory over time
watch df -h: monitor disk growth

Look for abnormal spikes or steady resource exhaustion
Adjust: Batch sizes, Parallelism, Memory allocation

Key Takeaways

These tools are lightweight, fast, and available on most Linux systems. They provide real-time insights into system health. Regular monitoring helps:

Prevent crashes
Optimize performance
Ensure stable production pipelines

Conclusion

Resource monitoring is about being a good steward of the infrastructure we use. Without proper monitoring, pipelines may crash unexpectedly and systems can become unresponsive.With these tools, you gain early warning signals, debug performance issues faster and ensure stable, reliable data processing.

DEV Community: grace wambua

Managing Academic Data: A Practical Application of SQL in PostgreSQL

Introduction

Filtering with WHERE

Using CASE WHEN to Transform Data

A reflection on my study findings

A Comprehensive Guide to Publishing and Embedding Power BI Reports on the Web with IFrames

Introduction

Publishing Reports to Power BI Service

Conclusion

Data Modeling in Power BI: Joins, Relationships, and Schemas

Introduction

Types of SQL Joins

1. INNER JOIN

Example:

2. LEFT JOIN

Example:

3. RIGHT JOIN

Case Example

4. FULL OUTER JOIN

Use Case Example

5. ANTI JOIN

Case Example

CARDINALITY

Active Vs. Inactive Relationships

CROSS FILTER

How Cardinality and Cross Filtering work together

Difference between Relationships and Joins

Fact Vs. Dimension Tables

Star Schema

Snowflake Schema

Flat Table

Role-playing Dimensions

Common Modeling Issues

Conclusion

Resource Monitoring for Data Pipelines

Introduction

1. Monitoring CPU & Processes: top and htop

top (Built-in, lightweight)

htop (Enhanced, user-friendly)

2. Monitoring Memory Usage: free -h

3. Monitoring Disk Space: df -h

Preventing Production Failures

Practical Workflow for Data Engineers

Key Takeaways

Conclusion

1. Monitoring CPU & Processes: `top` and `htop`

`top` (Built-in, lightweight)

`htop` (Enhanced, user-friendly)

2. Monitoring Memory Usage: `free -h`

3. Monitoring Disk Space: `df -h`