DEV Community: Joan Wambui

SQL fundamentals: DDL, DML, filtering, and CASE WHEN

Joan Wambui — Mon, 13 Apr 2026 16:06:42 +0000

What is SQL?

SQL (Structured Query Language) is the standard language for interacting with relational databases. Whether you are storing customer records, tracking exam results, or analysing transactions, SQL is how you talk to the database.

The five command categories

SQL commands are grouped into five categories:

Category	Its Function	Commands
DDL (Data Definition Language)	Defines and modifies the database structure	CREATE, ALTER, DROP, TRUNCATE
DML (Data Manipulation Language)	Manages/manipulates data inside tables	INSERT, UPDATE, DELETE
DQL (Data Query Language)	Retrieves data from the tables	SELECT
TCL (Transaction Control Language)	Manages transactions	COMMIT, ROLLBACK, SAVEPOINT
DCL (Data Control Language)	Controls access and permissions	GRANT, REVOKE

This article focuses on DDL, DML, and DQL.

DDL (Data Definition Language)

DDL creates the schemas, tables and columns. It defines the shape of your data before any data exists. It has four commands, as mentioned in the table: CREATE, ALTER, DROP and TRUNCATE.

CREATE TABLE sets up a table with its columns, data types, and constraints. ALTER TABLE modifies an existing table by adding columns, renaming them, or dropping ones that are no longer needed. TRUNCATE TABLE removes the contents of the table; however, it retains the table structure. DROP TABLE removes the entire table.

A lightbulb moment: CREATE TABLE wraps its column definitions in parentheses. ALTER TABLE does not as the instruction follows directly after the table name.

-- Creation of a student table
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)
);

-- Addition of a phone number column
ALTER TABLE students
ADD COLUMN phone_number VARCHAR(20);

DML (Data Manipulation Language)

INSERT INTO adds rows to a table. You list the target columns, follow with VALUES, and can insert multiple rows in one statement by separating them with commas. Always quote dates. Without quotes, PostgreSQL reads 2008-03-12 as arithmetic (2008 − 3 − 12 = 1993).

INSERT INTO students (student_id, first_name, gender, date_of_birth, class, city)
VALUES
    (1, 'Amina', 'F', '2008-03-12', 'Form 3', 'Nairobi'),
    (2, 'Brian', 'M', '2007-07-25', 'Form 4', 'Mombasa');

plaintext

UPDATE modifies existing rows. DELETE removes them. Both require a WHERE clause. Without it, every row in the table is affected, not just the one you intended.

-- Updating a student's current city of residence:
UPDATE students
SET city = 'Nairobi'
WHERE student_id = 5;

-- Deleting an exam result from a student whose id is 9:
DELETE FROM exam_results
WHERE result_id = 9;

plaintext

WHERE clause

SQL has a variety of operators that allow for various filtering conditions. The WHERE clause, when used with these operators, allows you to target specific rows.

Some operators include:

BETWEEN filters a range and is inclusive on both ends.
IN checks against a list of values. It is cleaner than chaining multiple OR conditions.
LIKE matches text patterns, where % represents any sequence of characters.

WHERE marks BETWEEN 50 AND 80
WHERE city IN ('Nairobi', 'Mombasa', 'Kisumu')
WHERE class NOT IN ('Form 1', 'Form 2')
WHERE first_name LIKE 'A%' OR first_name LIKE 'E%'

plaintext

CASE WHEN

CASE WHEN lets you apply conditional logic directly inside a query, generating a new column based on rules you define — without touching the underlying table.

-- Exam results labelling:

SELECT *,
    CASE
        WHEN marks >= 80 THEN 'Distinction'
        WHEN marks >= 60 THEN 'Merit'
        WHEN marks >= 40 THEN 'Pass'
        ELSE 'Fail'
    END AS performance
FROM exam_results;

PostgreSQL evaluates conditions top to bottom and stops at the first match, so order matters. The ELSE clause covers anything that falls outside your defined conditions. Without it, unmatched rows return NULL.

In CASE WHEN, the result exists only in the query output. The table structure is never changed.

What makes CASE WHEN powerful is that it lets you enhance your data at query time without needing extra columns in your schema. For reporting and analysis, that flexibility is very useful.

How I Published My Power BI Report and Embedded It on a Website

Joan Wambui — Sun, 05 Apr 2026 22:07:04 +0000

I recently completed a Power BI project, an electronics sales dashboard built from a raw CSV file. Cleaning the data, modeling the tables, writing DAX measures, all of that happened in Power BI Desktop.

This article covers what happens next: getting that report out of your computer and onto the internet where it can actually be used.

1. Create a Workspace

Before publishing anything, you need a workspace. In Power BI Service, a workspace is essentially a folder that holds your reports and datasets.
Go to app.powerbi.com and sign in. On the left navigation panel, click Workspaces, then click “New workspace”.

2. Publish from Power BI Desktop

Go back to Power BI Desktop, press Ctrl + S to save your file, then go to the Home tab in the ribbon and click Publish on the right side.

A dialogue box appears asking where to publish. Select the workspace you just created and click Select.

3. Generate the Embed Code

Now that the report is live in Service, you need the embed code to place it on a website.
With the report open in the browser, click File in the top menu. Hover over Embed report and select Publish to web (public).

You can also embed the report using the other option “Website or portal” copy the iframe code

4. Embed It in a Webpage

Open any HTML file in VS Code or a text editor of choice. Paste the iframe code where you want the report to appear. Save the file and open it in a browser.

Your report is now published and the link is ready to share.

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

Joan Wambui — Tue, 31 Mar 2026 03:15:27 +0000

What Is Data Modeling?

Data modeling refers to how one organises and connects their data so it can be analysed correctly. In Power BI, it defines how tables relate to each other, how filters move across visuals, and how your measures calculate results.

Joins

Joins combine data from two tables based on a shared column. In Power BI, joins happen in Power Query Editor before data enters the model.
INNER JOIN - Returns only rows with a match in both tables.
LEFT JOIN - Returns all rows from the left table only.
RIGHT JOIN - Returns all rows from the right table only.
FULL OUTER JOIN - Returns everything from both tables. Useful for spotting data gaps.
LEFT ANTI JOIN - Returns only rows from the left table with no match on the right.
RIGHT ANTI JOIN - Returns only rows from the right table with no match on the left.

Power BI Relationships

Relationships connect tables inside the model without merging them. They control how filters move between tables when you interact with visuals.
Cardinality defines how rows relate across tables:
• One-to-Many (1:M) - One customer, many orders. The most common type.
• Many-to-Many (M:M) - Requires careful handling, best resolved with a bridge table.
• One-to-One (1:1) - Usually means the tables can be merged.

Active vs Inactive relationship - Only one active relationship is allowed between two tables. Inactive relationships are triggered using USERELATIONSHIP() in DAX when needed.

Cross-filter direction - Single direction is the default and safest. Bidirectional filters flow both ways but can cause performance issues if overused.

Joins vs Relationships

Fact Tables vs Dimension Tables

Fact tables hold measurable data such as sales and transactions. They are long with many rows and link out to dimension tables via foreign keys.
Dimension tables hold descriptive data such as customer names, product categories, dates. They give context to the numbers in your fact table.
In simple terms, your fact table is the center. Dimension tables surround it.

Schemas

Star Schema - One fact table connected directly to multiple dimension tables. Fast, clean, and what Power BI is optimised for. This should be used by default.
Snowflake Schema - Dimension tables broken into sub-dimensions. This is more complex and slower in Power BI. It is recommended for one to use only it when source data is already normalised.
Flat Table - Everything in one table. This is simple but it creates redundancy and performance problems at scale.
Pro Tip:

A clean data model is what makes a Power BI report trustworthy. Start with a star schema, separate your facts from your dimensions, define your relationships carefully, and always build a proper Date table. The model is invisible to the end user, but it is what everything depends on.

How Linux is Used in Real-World Data Engineering

Joan Wambui — Mon, 30 Mar 2026 02:46:26 +0000

Linux has been such a common word in the tech journey, a mountain of a word for beginners, but a simple and helpful foundation once you grasp it. I had heard it before but couldn't tell you what it actually was. Was it a programming language? A tool? The same thing as Bash or Git? For a while I used all four interchangeably. A quiet kind of confusion, the type you don't realise you have until something breaks and you don't know where to look.

The moment it clicked wasn't dramatic. It was sitting at a terminal during my data engineering class, connected to a remote server, and realising the environment I was operating in was Linux. Not a language. Not a tool I had installed. The environment itself.

What Actually Is Linux?

Linux is an operating environment, similar to Windows or macOS, that sits beneath your tools, files, terminal sessions, and pipelines. Bash is the language you speak inside it. Git is a version control tool that runs inside it. GitHub is the cloud platform where your Git repositories live. Four different things, four different layers, blurring together because you encounter them all at once through the same black terminal window.

This matters because data engineering assumes Linux fluency without announcing it.

How Linux Shows Up in Real Data Engineering Work

When you provision a virtual machine on Azure or AWS, you land in a Linux environment. When your pipeline runs on a scheduler, it is running on a Linux server. When you work with Docker containers, they are built on Linux. It is rarely the thing being discussed, but it is always underneath the thing being discussed.

In practice, data engineers use Linux to navigate directories where raw data lands and processed outputs go. They write Bash scripts to automate repetitive ingestion tasks (which include ingesting a file, validating its structure, moving it to a staging folder, logging the result) and schedule them with cron to run automatically.

# Step 1: Navigate to where raw data lands
cd /data/raw

# Step 2: Run your ingestion script
bash ingest.sh

# Step 3: Schedule it to run automatically every day at 6am
0 6 * * * /scripts/ingest.sh

They monitor running jobs, check resource usage, and kill processes that hang. When something breaks on a production server, Linux is the environment you are debugging in. Knowing it is not optional.

Linux will not be the most exciting thing you learn in data engineering. It does not have a sleek interface or a compelling pitch. But it is the ground where real data work happens, on servers, in the cloud, inside containers, underneath every tool you will eventually depend on. The clearer your understanding of what it is, the faster everything else makes sense.