Forem: Davy Baraka

Understanding DDL and DML SQL concepts

Davy Baraka — Wed, 15 Apr 2026 11:14:10 +0000

1. INTRODUCTION

This article will cover an introduction to SQL concepts
SQL (Structured Query Language) is a standard language that is used to communicate with relational databases. It is used to store, retrieve, manipulate, and manage data that is organised in tables.

Core concepts in SQL consist of the following:

DDL (Data Definition Language) Used to define database structure Includes: create, alter, drop, truncate, rename
DML (Data Manipulation Language) Used to modify data Includes: insert, update, delete
DQL (Data Query Language) Used to retrieve data Includes: select, where, order by, group by, having, join
DCL (Data Control Language) Used to control access Includes: grant, revoke
TCL (Transaction Query Language) Used to manage transactions Includes: commit, rollback, savepoint

## 2. What DDL and DML are, and the difference between them

DDL

DDL commands deal with the structure of a database. They define, modify, or remove the objects that hold data — like tables, schemas, and columns. Think of DDL as the blueprint stage: you are not yet putting data in; you are deciding how that data will be organised.
DDL commands include:

CREATE is used to create new tables or databases.
ALTER is used to modify existing structures.
DROP is used to delete tables or databases.

DML

DML commands, on the other hand, deal with the actual data inside those structures. Once your table exists, you use DML to add records, update them, or remove them.
DML commands include:

INSERT adds new records
UPDATE modifies existing records
DELETE removes records
SELECT retrieves data

Key difference:
DDL deals with the structure of the database, while DML deals with the data inside the database.

**3. How you used CREATE, INSERT, UPDATE, and DELETE in your assignment

**
CREATE:I used it to create tables such as students.

INSERT:I used it to insert records in the tables.

UPDATE: I used to modify existing records, such as correcting a student's score

DELETE: I used it to delete unwanted or incorrect records, such as deleting a student entry.

3. How filtering with WHERE works (mention some operators like =, >, BETWEEN, IN, LIKE)

The WHERE clause is how SQL narrows down which rows a query applies to. Whether you are reading data with SELECT, changing it with UPDATE, or removing it with DELETE, WHERE lets you be precise.

SQL provides several operators you can use inside a WHERE condition:

= (equals) — Matches an exact value. Example: WHERE score = 80 returns only students with a grade of A.
> and < (greater/less than) — Compare numeric or date values. Example: WHERE score > 80 returns students who scored above 80.
BETWEEN — Selects values within a range (inclusive on both ends). Example: WHERE score BETWEEN 50 AND 80 returns students who scored between 50 and 80.
IN — Checks whether a value appears in a given list. Example: WHERE subject IN ('Math', 'Science') returns students doing either maths or science.
LIKE — Enables pattern matching in text. The % symbol acts as a wildcard. Example: WHERE full_name LIKE 'A%' returns all students whose names start with the letter A.

These operators can also be combined using AND and OR to build more complex filters, making WHERE one of the most powerful tools in SQL.

4. How CASE WHEN helps in transforming data

CASE WHEN is SQL's way of applying conditional logic — similar to an if/else statement in programming. It lets you create a new column in your query result based on conditions, without changing the underlying data in the table.

The basic structure looks like this:

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

Davy Baraka — Sun, 29 Mar 2026 17:58:28 +0000

What Is Data Modeling?
Data modeling is how you organize tables in Power BI so reports are accurate and fast
A good model keeps data clean, calculations correct, and DAX formulas simple
Two places where modeling happens in Power BI:
Power Query - where you load and clean tables
Model View - where you connect tables using relationships
SQL Joins
A join combines two tables based on a shared column (called a key)
In Power BI, joins are done in Power Query under Home > Merge Queries

The 6 Join Types

INNER JOIN - returns only rows that match in both tables
Example: customers who have placed an order (customers with no orders are excluded)
LEFT JOIN - returns all rows from the left table, and matched rows from the right
Example: all customers, whether they ordered or not (blank if no order)
RIGHT JOIN - returns all rows from the right table, and matched rows from the left
Example: all orders, even if the customer record is missing
FULL OUTER JOIN - returns all rows from both tables
Example: full data audit - show everything whether it matches or not
LEFT ANTI JOIN - returns left rows with no match in the right table
Example: customers who have never placed an order
RIGHT ANTI JOIN - returns right rows with no match in the left table
Example: orders with no matching customer (orphan records)
Power BI Relationships
Relationships link tables in Model View without merging them
Filters pass between related tables when slicers or visuals are used

Cardinality

One-to-Many (1:M) - most common. One row in Table A matches many rows in Table B
Example: one product appears in many sales rows
Dimension table is always on the '1' side, fact table on the 'Many' side
Many-to-Many (M:M) - many rows in both tables can match each other
Example: students and courses (one student takes many courses, one course has many students)
Avoid if possible - can cause double-counting in measures
One-to-One (1:1) - each row in Table A matches exactly one row in Table B
Example: Employee table and EmployeeDetails table

Cross-Filter Direction

Single - filters flow from the '1' side to the 'Many' side only (recommended)
Both - filters flow in both directions (use carefully, can cause errors)

Active vs Inactive Relationships

Only one active relationship is allowed between two tables at a time
Extra relationships can be inactive and used in DAX with USERELATIONSHIP()
Example: Sales has OrderDate and ShipDate, both connected to a Date table
OrderDate is active, ShipDate is inactive and activated in a DAX measure

How to Create a Relationship

Method 1: In Model View, drag a column from one table onto the matching column in another
Method 2: Home > Manage Relationships > New, then select both tables and key columns

Power BI Relationships
Relationships link tables in Model View without merging them
Filters pass between related tables when slicers or visuals are used

Cardinality

One-to-Many (1:M) - most common. One row in Table A matches many rows in Table B
Example: one product appears in many sales rows
Dimension table is always on the '1' side, fact table on the 'Many' side
Many-to-Many (M:M) - many rows in both tables can match each other
Example: students and courses (one student takes many courses, one course has many students)
Avoid if possible - can cause double-counting in measures
One-to-One (1:1) - each row in Table A matches exactly one row in Table B
Example: Employee table and EmployeeDetails table

Cross-Filter Direction

Single - filters flow from the '1' side to the 'Many' side only (recommended)
Both - filters flow in both directions (use carefully, can cause errors)

Active vs Inactive Relationships

Only one active relationship is allowed between two tables at a time
Extra relationships can be inactive and used in DAX with USERELATIONSHIP()
Example: Sales has OrderDate and ShipDate, both connected to a Date table
OrderDate is active, ShipDate is inactive and activated in a DAX measure

How to Create a Relationship

Method 1: In Model View, drag a column from one table onto the matching column in another
Method 2: Home > Manage Relationships > New, then select both tables and key columns
Fact and Dimension Tables

Fact Table

Stores measurable numbers: revenue, quantity, cost
Each row = one transaction or event
Usually large (millions of rows)
Always on the 'Many' side of a relationship
Example: Sales table with OrderID, Date, CustomerID, ProductID, Revenue

Dimension Table

Stores descriptions: product names, customer details, dates
Each row = one unique item
Usually smaller (thousands of rows)
Always on the '1' side of a relationship
Example: Products table with ProductID, Name, Category, Brand

Schemas

Star Schema (Recommended)

One fact table in the center, dimension tables connected around it
Best performance in Power BI
Simple to understand and write DAX for
Example: Sales connected to Date, Products, Customers, Location

Snowflake Schema

Like a star schema but dimension tables are split into sub-tables
Example: Product > Sub-Category > Category (three tables instead of one)
Slower in Power BI - flatten dimension tables where possible

Flat Table

Everything in one big table, no relationships needed
Easy to set up but causes data repetition and slow performance
Only suitable for very small or prototype projects

Role-Playing Dimensions
When one dimension table is used more than once in the same model
Common example: a Date table used for both OrderDate and ShipDate
Power BI only allows one active relationship per table pair
Solution: make one relationship active, others inactive
Use USERELATIONSHIP() in DAX to switch on an inactive relationship in a measure
Common Modeling Issues
No Date table: creates problems with time intelligence functions - always create a dedicated Date table
Wrong cardinality: duplicates on the '1' side break the relationship - key column must be unique
Too many bidirectional filters: causes circular filter errors - use single direction by default
Flat table in a complex model: hard to maintain and slow - break it into fact and dimension tables
M:M without a bridge table: leads to double-counting - use a junction table to split into two 1:M links

Quick Summary

Data modeling = organizing tables for accurate, fast Power BI reports
6 join types in Power Query: INNER, LEFT, RIGHT, FULL OUTER, LEFT ANTI, RIGHT ANTI
Relationships link tables in Model View - use 1:M for most cases
Joins merge tables physically; relationships filter between separate tables
Fact tables hold numbers; dimension tables hold descriptions
Star schema is the best structure for Power BI
Role-playing dimensions use USERELATIONSHIP() for inactive relationships
Avoid flat tables, wrong cardinality, and unnecessary bidirectional filters

How Linux is Used in Real-World Data Engineering

Davy Baraka — Tue, 24 Mar 2026 08:30:46 +0000

Linux is widely used in real-world data engineering because it provides a stable, efficient, and flexible environment for handling large amounts of data. Most data systems are hosted on cloud platforms like Amazon Web Services and Google Cloud Platform, which rely heavily on Linux servers. Data engineers use these Linux systems to store, process, and manage data pipelines. Many powerful data tools such as Apache Spark for processing data and Apache Kafka for handling real-time data streams are built to run on Linux, making it the preferred environment.

In addition, Linux allows engineers to automate repetitive tasks using scripts and scheduling tools, which is important for running data pipelines smoothly without constant manual input. It also supports databases like PostgreSQL, where processed data is stored and accessed. With its powerful command-line tools, Linux makes it easy to handle large files, monitor system performance, and control access to data securely. Overall, Linux acts as the foundation of modern data engineering by enabling efficient data processing, automation, and system management.