DEV Community: MbatiaK

Mastering the Basics: A Deep Dive into SQL Fundamentals

MbatiaK — Tue, 14 Apr 2026 10:05:32 +0000

This week, I have worked on a project of creating a Database Administrator for Nairobi Academy. My task was to build a school database from scratch, progressing from initial schema design to data population and complex querying.

Understanding the Language: DDL vs. DML

To manage a database effectively, one must distinguish between the blueprint and the content.

DDL (Data Definition Language): This is used to define the structure or schema of the database. It deals with the containers. Examples: CREATE, ALTER, DROP.
DML (Data Manipulation Language): This is used to manage the data within those structures. It deals with the content.Examples: INSERT, UPDATE, DELETE.

The Key Difference: DDL changes the shape of the database (e.g., adding a new table), while DML changes the information stored inside it (e.g., adding a new student).

Building and Managing the Academy Database.

In this assignment, I utilized four core SQL commands to manage the school's records:

CREATE: I used this DDL command to establish the nairobi_academy schema and its three primary tables: students, subjects, and exam_results.

INSERT: This DML command allowed me to populate the tables with 10 unique student profiles, subject details, and their corresponding exam marks.

UPDATE: I used this to maintain data accuracy. For instance, when Esther Akinyi moved to Nairobi, I updated her city record specifically using her student_id.

DELETE: When an exam result was cancelled, I used this to remove specific rows from the exam_results table to ensure the database remained clean and relevant.

Precision Filtering with the WHERE Clause

The WHERE clause is the most powerful tool for extracting specific insights from data.

I applied several operators:

Equality (=): Used to find all students in a specific class, such as 'Form 4'.
Comparison (>): Used to identify high-achieving students with marks greater than or equal to 70.
BETWEEN: Excellent for ranges. I used this to find exams held within a specific four-day window in March 2024.
IN: This allowed me to filter students living in multiple cities (Nairobi, Mombasa, or Kisumu) without writing multiple OR statements.
LIKE: I used pattern matching (e.g., 'A%') to find students whose names started with specific letters or subjects containing the word 'Studies'.

Transforming Data with CASE WHEN

Using the CASE WHEN statement, I transformed raw numeric marks into meaningful labels. For example, I created a performance column that categorized marks into 'Distinction', 'Merit', 'Pass', or 'Fail'. I also used it to categorize students as 'Senior' or 'Junior' based on their form level, which makes the data much more readable for school management.

Weekly Reflection

What I found most challenging was the strict syntax of SQL. Missing a single comma or semicolon can halt an entire script.

However, it was incredibly interesting to see how a few lines of code could instantly organize hundreds of data points.

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

MbatiaK — Sun, 29 Mar 2026 18:16:16 +0000

If you’ve ever opened a Power BI report only to find that your numbers are doubled, your filters aren’t working, or the whole thing is moving at a snail's pace, you probably have a data modeling problem. It’s the "engine under the hood" of your report. You can have the prettiest charts in the world, but if the model is broken, the insights are useless.

Using the production and efficiency data from the Yield 1.pbix file, let’s break down how to build a rock-solid model.

What Exactly is Data Modeling?
Think of data modeling as the blueprint for your data's house. It’s the process of taking messy, individual tables—like your Production logs and your Machine details—and showing them how to talk to each other. In Power BI, this happens in the Model View, where you define the relationships that allow a filter on a "Machine Name" to actually update your "Yield" numbers.

The Glue: SQL Joins in Power Query
Before your data even gets to the model, you often need to "mash" tables together. This happens in Power Query using Joins. Using the Yield 1 project as an example, here is how you should think about them:

Inner Join: This is for when you only want the "perfect matches." If you join your Production table with a QualityCheck table, an Inner Join only shows you batches that actually had a check-up. No check? It disappears from the list.

Left Outer Join (The "Go-To"): This keeps everything from your primary table. If you join YieldData with MaintenanceLogs, you’ll see every single yield record. If a batch happened during maintenance, the details are there; if not, you just see a null. You don't lose any production data.

Left Anti Join (The Detective): This shows you rows in the first table that don't have a match in the second. In your file, this is perfect for finding ProductionIDs that were logged in the system but are missing from the FinalYield output table—highlighting gaps in your data.

Relationships: Teaching Tables to Talk
Once your data is loaded, you create Relationships. This is different from a Join because the tables stay separate, but they are "linked."

Cardinality (The "How Many" Rule)
One-to-Many (1:M): This is the gold standard. In your model, one Machine can have many Yield entries. Power BI loves this; it’s fast and predictable.

Many-to-Many (M:M): Use this with caution. If you have many Operators working on many different ProductionLines, things can get blurry. Power BI will ask you how you want to handle the ambiguity.

One-to-One (1:1): Rare. Usually, if it’s 1:1, those two tables should probably just be merged into one.

Direction and Status
Cross-Filter Direction: Usually, filters flow from the "One" side (the Machine) to the "Many" side (the Yield). Avoid "Bi-directional" filtering unless you really need it, as it can cause performance issues.

Active vs. Inactive: You can only have one "active" path. If you have a TargetDate and an ActualDate in your yield data, only one can link to your Calendar table at a time. You call the "Inactive" one using the DAX function USERELATIONSHIP.

Facts vs. Dimensions: The "How Much" and the "Who"

To keep your Yield 1 model organized, you need to know which table is which:

Fact Tables: These are the "action" tables. They have the numbers you want to sum up—YieldAmount, ScrapQuantity, or TotalHours. They are usually very long and full of numbers.

Dimension Tables: These are the "context" tables. They give you the labels—MachineName, ShiftType, ProductCategory, or Date.

Picking a Schema (The Layout)
The Star Schema
This is what you should aim for. One Fact table (Yield) in the middle, with all your Dimensions (Machine, Shift, Date) branching out like points of a star. It’s clean, it’s fast, and it’s how Power BI was designed to work.

The Snowflake Schema
This is just a Star Schema where the dimensions get fancy. For example, your Machine table might link to a PlantLocation table. It’s more organized for the database, but it makes Power BI work a little harder.

The Flat Table (DLAT)
This is when you put everything into one giant spreadsheet-style table. It might seem easy at first, but it’s a nightmare to manage as your production data grows. Avoid this if you want your reports to stay fast.

Pro Tips: Role-Playing and Issues
Role-Playing Dimensions: Sometimes a table needs to play two parts. Your Date table might need to be the "Planned Date" for one visual and the "Actual Date" for another.

Common Issues: Be careful of Circular Dependencies. Don't let your relationships form a loop. If Table A filters B, and B filters C, don't let C filter A. Power BI will get confused and throw an error.

Step-by-Step: How to do it in Power BI
To Join (Merge) in Power Query:
Go to Transform Data.

Click Merge Queries on the Home tab.

Select your two tables (e.g., Yield and MachineList) and click the columns that match (like MachineID).

Pick your Join Kind (usually Left Outer) and hit OK.

To Create Relationships in Model View:
Click the Model View icon (the flowchart icon) on the left sidebar.

Simply drag a column from your Dimension table (e.g., Date in your Calendar table) and drop it onto the matching column in your Fact table (ProductionDate).

Double-click the line that appears to adjust the Cardinality or Direction if Power BI didn't guess it right.

How Excel is Used in Real-World Data Analysis

MbatiaK — Tue, 24 Mar 2026 07:32:24 +0000

What is Excel?- Microsoft Excel is a spreadsheet software that allows users to organize, store, and analyze data using rows and columns. It provides a wide range of functionalities, from simple calculations to advanced data modeling and visualization. Its accessibility and versatility make it one of the most popular tools for data analysis worldwide.

How Excel is Used in Real-World Scenarios

Financial Analysis

Excel is heavily used in finance for budgeting, forecasting, and financial modeling. Analysts use it to track expenses, project revenues, and evaluate business performance.

2.
Data Cleaning and Preparation

Before data can be analyzed, it often needs to be cleaned. Excel helps in removing duplicates, correcting errors, and organizing data into a usable format. Features like “Text to Columns” and “Remove Duplicates” are commonly used in this stage.

3.
Data Analysis and Reporting

Excel allows users to summarize large datasets and extract insights. Tools such as Pivot Tables enable analysts to group and analyze data efficiently. For instance, a sales team can use a Pivot Table to analyze revenue by region, product, or time period.

4.
Data Visualization

Excel provides charting tools that help transform data into visual insights. Bar charts, line graphs, and pie charts make it easier to communicate findings to stakeholders and support decision-making.

Key Features and Formulas in Excel

SUM Function

The SUM function is used to add a range of numbers.
Example:
=SUM(A1)
This is useful in calculating totals such as total sales or expenses.

IF Function

The IF function allows for conditional analysis.
Example:
=IF(A1>100, "High", "Low")
This can be used to categorize data, such as identifying high-value transactions.

VLOOKUP / XLOOKUP

These functions are used to search for data in a table.
Example:
=VLOOKUP(A2, Sheet2!A, 2, FALSE)
They are useful when combining datasets, such as matching customer IDs to customer names.

Pivot Tables

Pivot Tables are one of Excel’s most powerful features. They allow users to summarize and analyze large amounts of data quickly without complex formulas.

To make Pivot Tables more interactive, Excel provides Slicers. Slicers are visual filtering tools that allow users to quickly filter data by simply clicking buttons instead of using dropdown menus. For example, in a sales dataset, you can use slicers to filter results by region, product category, or time period with a single click. This makes dashboards more user-friendly and enhances real-time data exploration.

Charts and Graphs

Excel’s visualization tools help present data clearly. For example, a line chart can show sales trends over time, making patterns easier to understand.

Personal Reflection

With hands-on experience using Excel, my approach to data has evolved significantly. What once felt complex is now more structured and manageable, as I can efficiently organize datasets, uncover patterns, and generate meaningful insights. Over time, Excel has enhanced my ability to perform detailed analysis, support data-driven decision-making, and present findings in a clear and impactful way. It has become an essential tool in my work, strengthening both my analytical thinking and confidence when working with data.