DEV Community: Lorraine Njagi

Mastering SQL Fundamentals: From Data Definition to Data Transformation

Lorraine Njagi — Tue, 14 Apr 2026 19:47:39 +0000

Introduction

Structured Query Language (SQL) is a standardized language used for managing and interacting with relational databases. It enables users to define database structures, manipulate stored data, and retrieve information efficiently. SQL plays a critical role in modern data management systems, making it an essential skill in data-related fields.

SQL commands are broadly categorized into different groups, among which Data Definition Language (DDL) and Data Manipulation Language (DML) are fundamental. This article explores these categories, demonstrates their application, and reflects on the learning experience gained through practical exercises.

Data Definition Language (DDL)

Data Definition Language (DDL) refers to the set of SQL commands used to define and manage the structure of a database. These commands are concerned with creating, modifying, and deleting database objects.

Key DDL commands include:

CREATE

This command is used to create database objects such as tables, schemas, and databases.
ALTER

The ALTER command is used to modify the structure of existing database objects, such as renaming a column or changing a data type.
DROP

This command is used to permanently remove database objects such as tables or databases.

DDL operations focus on structure rather than the data itself.

Data Manipulation Language (DML)

Data Manipulation Language (DML) consists of SQL commands used to interact with and manipulate data within a database.

INSERT

Used to add new records into a table.

SELECT

Used to retrieve data from one or more tables.

SELECT * FROM table_name;

SELECT column_name FROM table_name;

UPDATE

Used to modify existing records.

UPDATE students
SET city = 'Nairobi'
WHERE first_name = 'Esther' AND last_name = 'Akinyi';

DELETE

Used to remove records.

DELETE FROM exam_results
WHERE result_id = 9;

These commands demonstrate how data within a database can be dynamically managed.

Filtering Data Using the WHERE Clause

The WHERE clause is used to filter records based on specific conditions, allowing for precise data retrieval.

Common operators include:

= (equal to)
> (greater than)
BETWEEN (range of values)
IN (multiple values)
LIKE (pattern matching)

Examples:

SELECT * FROM students
WHERE city IN ('Nairobi', 'Mombasa', 'Kisumu');

SELECT * FROM exam_results
WHERE marks BETWEEN 50 AND 80;

SELECT * FROM students
WHERE first_name LIKE 'A%';

Filtering enables users to extract meaningful subsets of data from larger datasets.

Data Transformation Using CASE WHEN

The CASE WHEN statement allows conditional logic to be applied within SQL queries, transforming raw data into meaningful categories.

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

This approach is particularly useful in reporting and data analysis.

Reflection

The learning experience throughout was both challenging and intellectually engaging. One of the main challenges was ensuring accuracy in SQL syntax, as even small mistakes could lead to errors. This emphasized the importance of precision when writing queries.

Despite these challenges, I found querying to be highly enjoyable. I particularly enjoyed interacting with the database and observing how different SQL commands produce meaningful outputs. I found querying both interesting and engaging, and each successful query provided a sense of accomplishment.

The CASE WHEN statement was especially insightful, as it demonstrated how raw data can be transformed into meaningful categories. This experience has significantly increased my interest in data manipulation and analysis, and I am motivated to continue improving my SQL skills.

Conclusion

SQL provides powerful tools for defining and managing databases and manipulating data. Understanding the distinction between DDL and DML, along with filtering and transformation techniques, is essential for effective data management.

Publishing and Embedding Power BI Reports into a Website

Lorraine Njagi — Mon, 06 Apr 2026 16:26:47 +0000

Publishing and Embedding Power BI Reports into a Website

Introduction

As a seasonal Power BI user, I always found it to be an easy and simple tool to use. Well, this perspective affected how much I was able to utilize it. This resulted in me not seeing it as much of a challenge, hence not putting it into practice as much as I should have.

The first time I used Power BI was during a bootcamp, and it was the easier part of the program before getting to the more challenging and time-demanding modules related to machine learning.

I recently got back to Power BI after enrolling in a course that includes it as part of the modules taught. Honestly, Power BI is easy, but it requires practice and one has to be keen just like with any other tool. One is more likely to forget what they learned and stay stuck in the beginner loop.

Well, this article is not about me. I will cover how to publish and embed your Power BI reports into your websites or web pages.

What is Power BI?

Power BI makes data visual. This is the tool that makes it easy to communicate what our data is about to stakeholders. Through Power BI, you tell a story about your data.

After working on your report, you need to save it and publish it.

Step 1 — Save Your Report

To save your report, click on the File icon on the top left of your screen.

Step 2 — Publish Your Report

To publish:

Go to Home on the ribbon
At the far right, you will see the Publish button

After selecting publish, it will ask you to select a workspace.

If you do not have a workspace, you have to log into your Power BI account and create one.

Note: A workspace is only available in Power BI Service and not available in Power BI Desktop.

Step 3 — Embedding to a Website

After publishing:

Go to your Workspace
Open the Report
Navigate to File
Click on Embed report

After clicking on Embed report, select Website and copy the iframe HTML to paste into your web page.

Conclusion

Power BI is a powerful tool for data visualization and storytelling. Publishing reports to Power BI Service and embedding them into websites allows you to share insights with a wider audience in an interactive and visually appealing way.

However, like any other tool, Power BI requires practice and consistency to master and fully utilize its capabilities.

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

Lorraine Njagi — Mon, 30 Mar 2026 21:05:40 +0000

Data modeling is how you structure your tables so Power BI understands how they connect. Get it right, and your reports are fast, accurate, and easy to maintain. Get it wrong, and you'll spend hours debugging.

SQL Joins in Power Query

Joins combine tables into one and happen in Power Query (Transform Data). Use them when you need a flat table.

Sample tables:

CustomerID	CustomerName
1	John Smith
2	Sarah Jones
3	Mike Brown
4	Emma Wilson

OrderID	CustomerID	Amount
101	1	100
102	1	150
103	2	200
104	5	75

INNER JOIN

Returns only matching rows from both tables.

Result: Customers 1 and 2 with their orders.

LEFT JOIN

Returns all rows from left table, matching from right.

Result: All customers; customers 3 and 4 show NULL for orders.

RIGHT JOIN

Returns all rows from right table, matching from left.

Result: All orders; order 104 shows NULL for customer.

FULL OUTER JOIN

Returns all rows from both tables.

Result: All customers and all orders; NULLs where no match.

LEFT ANTI JOIN

Returns rows from left with no match in right.

Result: Customers 3 and 4 (no orders).

RIGHT ANTI JOIN

Returns rows from right with no match in left.

Result: Order 104 (orphaned order).

How to create: Merge Queries > select tables > choose join kind > expand column.

Joins vs. Relationships

Joins	Relationships
Power Query	Model View
Combine into one table	Keep tables separate
Use for preparation	Use for analysis

Best practice: Use relationships unless you specifically need a flat table.

Power BI Relationships

Create relationships in Model View (drag between columns) or Manage Relationships.

Relationship Types

Type	Description
One-to-Many (1:M)	One row matches many rows. Most common.
Many-to-Many (M:M)	Many match many. Use bridge table if possible.
One-to-One (1:1)	One matches one. Rare.

Active vs. Inactive

Active (solid): Used automatically
Inactive (dotted): Use with USERELATIONSHIP in DAX

Cardinality & Cross-Filter

Cardinality: Many-to-One is default
Cross-filter: Single direction is default; Both can cause issues

Fact vs. Dimension Tables

Fact Tables	Dimension Tables
Numeric, measurable data	Descriptive attributes
Grows with transactions	Relatively static
Foreign keys	Primary keys
Examples: Sales, Orders	Examples: Customers, Products, Date

Schemas

Star Schema

Fact table in center, dimensions connected directly. Optimal for Power BI.

Snowflake Schema

Dimensions normalized into sub-tables. Avoid if possible—hurts performance.

Flat Table

One table with all data. Only for simple exports; avoid for reports.

Role-Playing Dimensions

One dimension used multiple ways (e.g., Date table used for Order Date and Ship Date).

Setup: One active relationship, one inactive, use USERELATIONSHIP when needed.

Common Issues & Fixes

Issue	Fix
Circular dependencies	Remove unnecessary relationship
Many-to-many	Add bridge table
Bi-directional filtering	Change to Single direction
Missing date table	Create and mark date table
Wrong cardinality	Clean duplicates in dimension

Quick Steps to Build a Model

Load data (Get Data)
Clean in Power Query (fix types, remove duplicates)
Create relationships (drag in Model View)
Configure (Many-to-One, Single direction)
Hide technical columns (Properties > Is Hidden)
Mark date table (right-click > Mark as Date Table)
Verify (Manage Relationships)

Summary

Use relationships over joins
Build star schema with fact at center
Set one-to-many with single cross-filter
Create a date table for time intelligence
Clean data in Power Query, model in Model View

A solid model saves hours of troubleshooting. Get the foundation right first.

How Linux is Used in Real-World Data Engineering

Lorraine Njagi — Sat, 28 Mar 2026 21:09:43 +0000

What is Data Engineering?

Data engineering refers to the transformation of data and preparing it for analysis or use by data analyst and data scientist. This is what ensures the infrastructure and the data to be used is in the right form. They convert vast amounts of raw data into usable data sets.

Why Linux is Used In Data Engineering?

Most Cloud infrastructures such as AWS, Azure and GCP run on Linux. They use Linux for their virtual machines and data services.
Tools such as Kafka, Hadoop, Spark and Apache are more suited by its open source ecosystem.
Linux offers performance and stability for running large data pipelines without needing reboots.
Automation and Scripting Linux offers the command line CLI and tools such as CRON which enable automation of data tasks and Extract Transform and Load (ETL) pipelines.

Linux Basics for Data Engineering

There are a few Linux basics that data engineers should be aware of.

1. The File System Structure

The Linux file system takes the structure of a tree, with the starting point as the root (/) directory.

Important directories under the root are:

/etc
/var
/bin
/tmp

/etc

etc contains configuration files and folders. This folder controls the configuration of the entire system, how the OS and how the user behaves. For example the passwd file which contains details about users.

/var

This folder contains variable data that changes continuously during system operations.

Examples:

System logs
Authorization logs
Databases
Runtime state files

It is of importance to a data engineer in various ways such as:

/var/log/              # Where spark, kafka and apache logs live
/var/lib/postgresql/   # Actual PostgreSQL database storage
/var/spool/cron        # Job queues for scheduled cron tasks

/bin

This contains essential command line programs that are available for all users even in single use or recovery mode such as cd, ls.

cd   # Used to change directory
ls   # Used to list files and directories

ls options:

ls -a   # Include hidden files
ls -l   # Show file permissions and details

Single User / Recovery Mode

Single user or recovery mode refers to a Linux special boot mode used for repair and maintenance.

In this mode:

Only one user logs in (root user)
No networks are started
No GUI is started
Used for system repair (e.g password reset)

2. File Permissions

File permissions are relevant to a data engineer because the role involves:

Moving data between systems
Automatic running of scripts
Handling sensitive credentials
Maintaining data integrity

Permission Table

Permission	Symbol	Numerical Value
Write	w	4
Read	r	2
Execute	x	1

The command used:

chmod   # change mode

Example

Create a directory:

mkdir Yourname

Create a file:

touch file.txt

Restrict permissions so only the owner has full access:

chmod 700 file.txt

Allow:

Group → read & execute
Others → read only

chmod 731 file.txt

To change ownership:

chown

3. Disk Usage

A data engineer should pay attention to disk usage because it directly affects:

Performance
Usage
Cost
Pipeline failures (when disk fills)

Commands:

du -sh   # Check file size
df -h    # Check disk space

4. Searching

Data engineers handle large files, so searching is important.

Commands:

grep    # Search within a file
find    # Search for files and directories
locate  # Fast search using index

5. Process Management

A process is a program that is running in memory.

Commands:

ps                 # View running processes
ps -u yourusername # View your processes
top                # Live process monitor
htop               # Modern version of top
kill               # End a process

Real World Data Engineering Workflow on Linux

Linux servers are used to manage the entire data pipeline.

A data pipeline refers to the entire process of:

Data Collection → Cleaning → Formatting → Storage → Analysis → Presentation

Data Pipelines

Batch Pipelines

Process data in batches
Scheduled (hourly, daily)
Suitable for historical data
Used for computationally expensive operations
Tools: Apache, Hadoop, Airflow

Realtime Processing Pipelines

Data analyzed continuously as it flows
Used in fraud detection and monitoring systems
Requires realtime analysis

Stages of a Data Pipeline

1. Ingestion

This refers to bringing data from different sources into your system storage.

Sources include:

Data lakes
APIs
IoT devices

Two methods of ingestion

Batch ingestion
Streaming ingestion

Ingestion Tool

Apache Kafka – sits between your data source and destination and uses streaming.

2. Transformation

This involves:

Cleaning
Restructuring
Enriching
Standardization
Aggregation
Validation
Filtering

3. Storage

Storage types:

Relational databases (SQL)
Data warehouses (Azure Synapse, Amazon Redshift)

4. Analysis

Type	Description
Descriptive	What happened
Diagnostic	Why it happened
Predictive	What will happen
Prescriptive	What should be done

Conclusion

Linux provides:

Control
Automation
Scale

Automation includes scheduling jobs using CRON.

Dealing With Large Files in Linux

Example: tail

tail -f /var/log/syslog

This:

Shows last 10 lines
Keeps the file open
Prints new lines in realtime
Stops when you press CTRL + C

This uses inotify, a Linux kernel mechanism where the OS notifies tail when new lines are written.

Final Note

This article does not delve so much into every aspect of Linux involved in data engineering. Looking forward to sharing more thoughts in my future articles. There is a first time for everything!!