DEV Community: Lameck Odhiambo

Python Functions

Lameck Odhiambo — Sat, 03 Oct 2026 07:54:23 +0000

Python functions are reusable blocks of code designed to perform a specific task. They help prevent code repetition, break complex programs into smaller manageable pieces, and only run when explicitly called.

Basic Syntax - Defining and Calling a Function

Functions are defined using the def keyword, followed by a custom name, parentheses (), and a colon :. The body of the function must be indented.

# 1. Defining the function
def greet():
    print("Hello! Welcome to Python.")

# 2. Calling the function
greet()

Parameter vs Arguments

Parameters are names used in a function defination that describes what data the function expects.
Arguments are the actual values passed in a function call that are assigned to parameters.

def greet_user(username):  # username is a parameter
    print(f"Hello, {username}!")

greet_user("Alice")       # "Alice" is an argument

Core Argument Types

Positional

Values map directly to parameters based on the order they are written.

# 'name' is the first parameter, 'age' is the second
def greet(name, age):
    print(f"Hello {name}, you are {age} years old.")

# Calling the function using positional arguments
greet("Alice", 25)  
# Output: Hello Alice, you are 25 years old.

Keyword

Keyword argument (or named argument) is an argument passed to a function by explicitly stating the parameter name and its value using the syntax parameter_name = value

def greet_user(name, greeting, punctuation="!"):
    return f"{greeting}, {name}{punctuation}"

# 1. Standard positional call (order matters)
print(greet_user("Alice", "Hello"))  # Output: Hello, Alice!

# 2. Keyword argument call (order doesn't matter)
print(greet_user(greeting="Hi", name="Bob"))  # Output: Hi, Bob!

# 3. Mixing both (positional MUST come first)
print(greet_user("Charlie", greeting="Welcome", punctuation="."))  # Output: Welcome, Charlie.

Default argument

Default argument is a fallback value assigned to a function parameter during its definition. If you omit that argument when calling the function, Python automatically uses this predefined default value; if you provide a value, it overrides the default

def greet(name="Guest"):
    print(f"Hello, {name}!")

greet()        # Output: Hello, Guest! (Uses default)
greet("Alice") # Output: Hello, Alice! (Overrides default)

Args

*args is a special syntax used in function definitions to allow a function to accept a variable number of positional arguments.

def multiply_all(*args):
    result = 1
    # args is treated as a tuple: (2, 3, 4)
    for number in args:
        result *= number
    return result

# You can call the function with any number of inputs
print(multiply_all(2, 3))        # Output: 6
print(multiply_all(2, 3, 4, 5))  # Output: 120

Kwargs

**kwargs stands for keyword arguments. It is a special syntax used in function definitions to allow a function to accept an arbitrary (variable) number of named arguments
The values are stored in a dictionary.

def display_info(**kwargs):
    # kwargs is a dictionary inside the function
    print(kwargs) 

# Calling the function with different keyword arguments
display_info(name="Alice", age=30, city="New York")
# Output: {'name': 'Alice', 'age': 30, 'city': 'New York'}

Return Values

Use the return statement to exit a function and send data back to the program. If no return statement is defined, the function automatically returns None.

def add_numbers(a, b):
    return a + b

result = add_numbers(5, 10)
print(result)  # Output: 15

You can return multiple values by separating them with commas. Python automatically packs them into a tuple, which you can then unpack into separate variables

def get_user_data():
    name = "Bob"
    age = 25
    return name, age  # Returns a tuple: ("Bob", 25)

user_name, user_age = get_user_data()  # Unpacking
print(user_name)  # Output: Bob

Conclusion

Python functions are the fundamental building blocks of clean, efficient, and reusable code. They allow developers to group a specific block of logic together, reducing repetition and making complex programs manageable.

Python Loops

Lameck Odhiambo — Sat, 26 Sep 2026 11:09:13 +0000

Introduction

Loops control the flow of a code, repeats a block of code until a condition is met.
In Python, loops are used to repeatedly execute a block of code. Python provides two main types of loops: for loops and while loops

Iterables : An object/collection that can return its elements one at a time allowing it to be iterated in a loop.

For loops

A for loop is used for iterating over a sequence (that is either a list, a tuple, a dictionary, a set, or a string).
Goes through a list of item one by one, to do something for each item.

fruits = ["apple", "banana", "cherry"]
for x in fruits:
  print(x)

Output:

apple
banana
cherry

For loop ends immediately it reaches the last item in the collection; for example in this case, when it reached cherry - the last item, it automatically ends the loop.

Looping Through a String

Even strings are iterable objects, they contain a sequence of characters.

for x in "banana":
  print(x)

Output

b
a
n
a
n
a

Python Looping Through a Range

To loop through a set of code a specified number of times, we can use the range() function, The range() function returns a sequence of numbers, starting from 0 by default, and increments by 1 (by default), and ends at a specified number.

for x in range(6):
  print(x)

Output:

The range() function defaults to 0 as a starting value, however it is possible to specify the starting value by adding a parameter: range(2, 6), which means values from 2 to 6 (but not including 6)
The range() function defaults to increment the sequence by 1, however it is possible to specify the increment value by adding a third parameter: range(2, 30, 3)

Break statement

With the break statement we can stop the loop before it has looped through all the items, if a condition is met.

fruits = ["apple", "banana", "cherry"]
for x in fruits:
  print(x)
  if x == "banana":
    break

Once a condition is met in the loop, then it will stop at that condition and will not loop over the remaining items in the iterable.
In this case it will stop at the banana and will not loop upto the cherry.

Output:

apple
banana

Continue statement

Continue statement is used to skip an item in an iterable when it matches the condition given and continue on to loop over the others.

fruits = ["apple", "banana", "cherry"]
for x in fruits:
  if x == "banana":
    continue
  print(x)

In this case it will print all items in the iterable but banana.

Output:

apple
cherry

Nested Loops

A nested loop is a loop inside a loop.
For every outer loop print all the inner loop. The "inner loop" will be executed one time for each iteration of the "outer loop".
Round one of the outer loop prints all items in the inner loop.

adj = ["red", "big", "tasty"]
fruits = ["apple", "banana", "cherry"]

for x in adj:
  for y in fruits:
    print(x, y)

Output:

red apple
red banana
red cherry
big apple
big banana
big cherry
tasty apple
tasty banana
tasty cherry

While Loops

With the while loop we can execute a set of statements as long as a condition is true.
Break and continue statements can also be used here.

While Condition

i = 1
while i < 6:
  print(i)
  i += 1

output:

The loop will continue as long as its i is under 6 is True but when it becomes greater than 6 it becomes False and hence breaks the loop and stops.

While True

It creates an infinite loop, meaning the code inside it will repeat indefinitely because the condition is permanently set to True.To stop a while True: loop, you must explicitly exit it from the inside—usually by using a break statement when a certain condition is met.

Common Use Cases

Validating User Input: Keep asking the user for data until they provide a valid response.
Game Loops or Event Listeners: Keep a program running continuously to listen for user actions, network data, or events.
Emulating a "Do-While" Loop: Forcing a block of code to run at least once before checking an exit condition

while True:
    user_input = input("Type 'exit' to stop the loop: ")

    if user_input.lower() == 'exit':
        print("Exiting the loop...")
        break  # Immediately exits the loop

    print(f"You typed: {user_input}")

If you forget to include a break statement (or if your if condition can never be met), the loop will run forever and lock up your program.
You can stop it by pressing Ctrl + C to force-kill the program.

Difference between while condition and while true

While condition exits normaly when the condition is false, while true must have if and break statements
While condition is safer and more readable, while true has risks of infinite loop and is more flexible
While condition is used in counters and retries, while true is used when getting response from databases, APIs or stream

Difference between for loops and while loops

For loops loop over a fixed sequence, while loops loop while the condition is true
For loops uses predefined condition, while loops uses your condition
For loops number of iteration is known, while loop number of iteration is unknown

Enumerate

The enumerate() function in Python adds a counter to an iterable (like a list, tuple, or string) and returns it as an enumerate object. This allows you to loop through a collection while keeping track of both the index and the value simultaneously, eliminating the need to manage a counter variable manually.

fruits = ["apple", "banana", "cherry"]

for count, fruit in enumerate(fruits, start=1):
    print(f"Line {count}: {fruit}")

Output:

Line 1: apple
Line 2: banana
Line 3: cherry

Conclusion

Loops in Python are essential control flow structures that allow you to execute a block of code repeatedly, eliminating the need for redundant code and making programs more efficient. Python primarily relies on two types of loops: for loops (for iterating over a known sequence or iterable) and while loops (for repeating code as long as a specific condition remains true).

Python basics, Operators and Conditions

Lameck Odhiambo — Sun, 20 Sep 2026 08:57:36 +0000

Introduction

Python is a high-level language (its closer to the natural language such as English compared to C++, Assembly which are low level languages - closer to the Machine language), general-purpose programming language known for its clear syntax and readability.

How python works

Python code in your source code is Compiled, the converted to Byte Code (which is a low level language) then to a Virtual Machine (which converts the code to 1s and 0s to be understood by the machine) after these steps the Output is produced.
The step from compiler to virtual machine is called Interpreter

Common Uses

Python is applied across many different industries and technical fields:

Web Development: Building server-side applications with frameworks like Django and FastAPI.
Data Science & Machine Learning: Processing numbers and training models using Pandas, NumPy, and PyTorch.
Automation: Writing simple scripts to handle repetitive daily file management or data entry tasks

Getting started with Python

Print()

Print() function displays values or text on the screen. It shows the result of a particular program that was run. Basically it displays an output of a program.

print("Hello World")

You can also use single quotes

print('Hello World')

A variable can also be printed as shown but in this case we do not use quatation

age = 30 print(age)

Variables

Variables are containers or box holding a particular value. It can be text, number or a float

first_name = 'Lameck' 
first_name = "Lameck"

print(first_name)

first_name here is a variable.

Data types in Python

Strings

Strings are surrounded by either single quatation marks or double quatation.

first_name = 'Lameck' 
first_name = "Lameck"

print(first_name)

'Lameck' here is a string

Integer

Integers are whole numbers and are not inside any quatatation.

age = 30
quantity = 3
number_of_students = 68

Float

Float are numbers containing a decimal point, also they are not put inside quatations.

price = 10.99
weight = 75.3

Booleans

These are True of False values. The are also not put inside the quatations and the first letters must be capital.

is_student = True
is_adult = False

Type Casting

Type casting is converting a variable to another data type. For example converting a float to an integer or a string to an integer and so forth.

gpa = 3.2
new_gpa = int(gpa)
print(new_gpa)

age = 32
new_age = float(age)
print(new_age)

F - Strings / String formatting

Format strings, f-strings or String formatting inserts variables, expressions, or specific formatting rules directly into a text string.

age = 33
name = "Nashon"
print(f"{name} is {age} years old.")

Input()

This is a function that prompts the user to enter data. It returns the entered value as a string.

name = input("Enter your name: ")
print(f"Hello {name}")

Operators in Python

1. Arithmetic operators

Used to perform standard mathematical calculations

2. Comparison (Relational) Operators

Used to compare two values. They always return a Boolean value: either True or False

3. Logical Operators

Used to combine conditional statements

and: Returns True if both statements are true

or: Returns True if at least one statement is true

not: Inverts the result (returns False if the result is true)

4. Membership Operators

Used to test if a sequence (like a string, list, or tuple) is present in an object.

in: Returns True if a sequence with the specified value is present in the object (e.g., 'a' in 'apple' → True).

not in: Returns True if a sequence with the specified value is not present in the object.

5. Identity Operators

Used to compare objects, not if they are equal, but if they are actually the same object with the same memory location.

is: Returns True if both variables point to the same object.

is not: Returns True if both variables do not point to the same object.

Conditions in Python

Conditional statements control the flow of execution by evaluating expressions to either True or False.
Operators discussed above can be used within these conditional statements.

1. The if Statement

Executes a block of code only if the condition evaluates to True

age = 20
if age >= 18:
    print("You are an adult.")  # Runs because the condition is True

2. The if-else Statement

Executes one block if the condition is True, and an alternate block if it is False

score = 45
if score >= 50:
    print("Passed")
else:
    print("Failed")

3. The if-elif-else Chain

Used to evaluate multiple sequential conditions. It stops evaluating as soon as it finds the first True condition

temperature = 25

if temperature > 30:
    print("It's hot.")
elif temperature >= 15:
    print("It's warm.")  # Runs and skips the rest
else:
    print("It's cold.")

Nested Conditions

Nested conditionals occur when you place a conditional statement (if, elif, or else) inside another conditional statement. They are used to test a secondary condition only after a primary condition has already passed.

is_logged_in = True
age = 20

if is_logged_in:
    print("User is authenticated.")

    # Secondary check inside the first one
    if age >= 21:
        print("Purchase approved.")
    else:
        print("Purchase denied: You must be at least 21.")

else:
    print("Purchase denied: Please log in first.")

Conclusion

Python operators and conditional statements are the fundamental building blocks that give programs the ability to process data, make decisions, and control execution flow.

Window Functions in SQL

Lameck Odhiambo — Tue, 15 Sep 2026 12:29:10 +0000

SQL window functions perform calculations across a set of table rows that are related to the current row - window functions allow you to perform advanced data analysis (like calculating running totals, rankings, or moving averages) on a specific group of related rows without losing the details of individual rows. Unlike regular aggregate functions (GROUP BY), window functions do not collapse your rows into a single output row; every individual row retains its separate identity while displaying the calculated value.
A window function performs a calculation across a set of table rows that are somehow related to the current row. This is comparable to the type of calculation that can be done with an aggregate function. But unlike regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row — the rows retain their separate identities. Behind the scenes, the window function is able to access more than just the current row of the query result.
They are commonly used for tasks like aggregates, rankings and running totals. The OVER clause defines the “window” of rows for the calculation

Window function syntax

The defining element of a window function is the OVER() clause.

PARTITION BY: Divides the rows into groups or "partitions" (similar to GROUP BY, but without collapsing rows). If omitted, the function treats the entire table as one single group.

ORDER BY: Dictates the sequence in which the rows are processed within each partition. Crucial for running totals, moving averages, and rankings.

ROWS/RANGE (Frame Clause): Further subsets the rows within the partition (e.g., "only the last 3 rows up to the current row").

Common types of Window Functions

1. Aggregate Window Functions
These perform standard math calculations but keep the row-level detail intact
SUM(), AVG(), COUNT(), MIN(), MAX()

SUM()

select 
      t.rider_id,
      t.trip_id, 
      t.fare,
      sum(fare) over(partition by rider_id) as rider_spend
from safari.trips t;

AVG()

select
    t.rider_id,
    t.trip_id,
    t.fare,
    avg(t.fare) over(partition by t.rider_id) as rider_avg_fare
from safari.trips t ;

COUNT()

select 
    t.trip_id,
    t.fare,
    count(*) over() as driv_tt_trip_count
from safari.trips t 
where t.driver_id = 2
order by t.trip_id;

2. Ranking Window Functions
Used to assign a sequential rank or number to rows based on a specific order.

ROW_NUMBER(): Assigns a unique, sequential integer to every row starting at 1.

select 
    t.driver_id, 
    t.trip_id,
    t.trip_date, 
    row_number() over(partition by driver_id) as fare_rank
from safari.trips t;

RANK(): Assigns ranks, but skips numbers if there are duplicate values (e.g., 1, 2, 2, 4).

select
      d.driver_id,
      d.driver_name,
      rank() over(partition by driver__id) as driver_rank
from safari.drivers d;

DENSE_RANK(): Assigns ranks, but does not skip numbers for duplicates (e.g., 1, 2, 2, 3).

select
      name, 
      department, 
      salary,
      dense_rank() over (partition by department order by salary desc) as emp_dense_rank
from employee;

3. Value & Offset Functions
Used to grab values from other rows relative to the current row without doing math.

LAG(): Fetches data from a previous row (great for calculating month-over-month growth).

select
    t.trip_date,
    t.fare,
    lag(t.fare) over(partition by t.driver_id order by t.trip_date ) as prev_fare
from safari.trips t
order by t.trip_date asc;

LEAD(): Fetches data from a subsequent row.

select 
    t.trip_id,
    t.trip_date,
    t.rider_rating,
    lead(t.rider_rating) over(partition by t.rider_id order by t.trip_date) as next_trip
from safari.trips t 
where t.rider_id = 2;

FIRST_VALUE() / LAST_VALUE(): Grabs the very first or very last value in the partitioN

Functions in SQL

Lameck Odhiambo — Fri, 11 Sep 2026 12:19:54 +0000

SQL functions are built-in, reusable blocks of code that accept input values, manipulate or perform calculations on them, and return a specific output. They make writing queries significantly easier by handling complex formatting, transformations, and math directly inside the database engine.SQL functions are broadly divided into two major execution types: Single-Row (Scalar) Functions and Multi-Row (Aggregate/Window) Functions

Multi-Row (Aggregate) Functions
These functions look at a set of values across multiple rows and condense them down into a single, summarized output. They are frequently paired with the GROUP BY clause.

SUM(): Adds all numeric values in a column together.

AVG(): Calculates the average value of a set.

COUNT(): Returns the total number of rows or non-null values.

MAX() / MIN(): Returns the highest or lowest value in a group

SELECT Department, COUNT(EmployeeID), AVG(Salary)
FROM Employees
GROUP BY Department;

Single-Row (Scalar) Functions

These functions operate on an individual value in a single row and return exactly one result per row. They are widely used for data cleaning and formatting.

LOWER() / UPPER(): Converts text to lowercase or uppercase.

CONCAT(): Joins two or more strings together.

SUBSTRING() / SUBSTR(): Extracts a specific section of text.

LENGTH() / LEN(): Returns the total character count of a string.

Mathematical / Numeric Functions

Used to handle arithmetic operations

ROUND(): Rounds a number to a designated decimal place.

ABS(): Returns the absolute (positive) value of a number.

CEILING() / FLOOR(): Rounds a number up or down to the nearest integer.

Date and Time Functions

Used to manage calendar inputs, timelines, and intervals:

NOW() / CURRENT_TIMESTAMP: Pulls the database server's exact current date and time.

DATEDIFF(): Calculates the difference in days/months/years between two dates.

DATE_ADD(): Adds a time interval (e.g., 30 days) to an existing date.

SELECT UPPER(FirstName), ROUND(Bonus, 2), NOW() 
FROM Employees;

Subqueries and CTEs in SQL

Lameck Odhiambo — Thu, 10 Sep 2026 12:43:15 +0000

Subqueries

From what I understand, a subquery is a query within a query.Subquery (also known as an inner query or nested query) is simply a SQL query embedded inside another SQL statement. It is used to fetch an intermediate dataset that the main (outer) query can use to filter, calculate, or manipulate data.

Common locations of Subqueries

1. In the WHERE Clause (Dynamic Filtering)

This filters rows based on a calculation or a list generated by the inner query.

-- Finds all employees who make more than the company average
SELECT name, salary 
FROM employees 
WHERE salary > (SELECT AVG(salary) FROM employees);

2. In the FROM Clause (Derived Tables)

The subquery acts like a temporary table that the outer query selects data from. These must be given a table alias

-- Treats the subquery results as a temporary table named "dept_salaries"
SELECT department_id, max_salary
FROM (SELECT department_id, MAX(salary) AS max_salary FROM employees GROUP BY department_id) AS dept_salaries
WHERE max_salary > 100000;

3. In the SELECT Clause

Calculates a single value to display as a standalone column alongside individual row data

-- Shows each employee's salary right next to the overall company average
SELECT name, salary, (SELECT AVG(salary) FROM employees) AS overall_average
FROM employees;

Important to know:

Isolating a single calculation or subset to filter main data.
Can only return columns from the outer table
Easier to read for multi-step, sequential logic.
Can be slower if highly nested or poorly optimized by the engine.

CTEs

In PostgreSQL, a Common Table Expression (CTE) is a temporary result set that you can reference within a larger SQL statement (SELECT, INSERT, UPDATE, or DELETE). They act like temporary tables that exist only for the duration of a single query execution, making complex queries much easier to write, read, and maintain.

1. Basic CTE Syntax

A standard CTE begins with the WITH keyword, followed by the CTE name and the inner query.

WITH regional_sales AS (
    SELECT region, SUM(amount) AS total_sales
    FROM orders
    GROUP BY region
)
SELECT region, total_sales
FROM regional_sales
WHERE total_sales > 10000;

2. Chaining Multiple CTEs

You can define multiple CTEs sequentially by separating them with a comma. A later CTE can even reference a previously defined CTE within the same query.

WITH total_orders AS (
    SELECT customer_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY customer_id
),
top_customers AS (
    SELECT customer_id 
    FROM total_orders
    WHERE order_count > 5
)
SELECT name, email 
FROM customers 
WHERE id IN (SELECT customer_id FROM top_customers);

Key differences between Subqueries and CTEs

4 SQL Joins You MUST Know

Lameck Odhiambo — Wed, 09 Sep 2026 13:19:26 +0000

SQL JOIN clauses are used to combine columns from two or more tables based on a related column between them. Because relational databases isolate data into separate normalized tables to eliminate redundancy, joins are the mechanism used to stitch that data back together into a single, cohesive result set

For the purposes of practice we'll use these two tables; customers and items

SQL Join Types

Inner join
An INNER JOIN in SQL combines rows from two or more tables based on a related column between them. It returns only the records that have matching values in both tables; if a row in one table does not have a matching row in the other, it is excluded from the final result


SELECT orders.order_id, customers.customer_name
FROM orders
INNER JOIN customers 
ON orders.customer_id = customers.customer_id;

Left Join
In SQL, a LEFT JOIN (also known as a LEFT OUTER JOIN) returns all rows from the left table (the first table mentioned), along with the matching rows from the right table. If there is no match for a row from the left table, the columns from the right table will simply display NULL

SELECT o.order_id, c.customer_name
FROM orders AS o
LEFT JOIN customers AS c 
ON o.customer_id = c.customer_id;

Right Join
The SQL RIGHT JOIN (or RIGHT OUTER JOIN) returns all records from the right table (table2) and the matched records from the left table (table1). If there is no match for a row from the right table, the resulting columns from the left table will contain NULL values.

SELECT table1.column_name, table2.column_name
FROM table1
RIGHT JOIN table2 
ON table1.common_column = table2.common_column;

Full Join
A SQL FULL JOIN (also known as a FULL OUTER JOIN) returns all records from both tables whenever there is a match in either the left or right table. If a row from the left table matches a row from the right table, the columns are combined. If there is no match, the missing sides are populated with NULL values. Essentially, a FULL JOIN acts as a combination of a LEFT JOIN and a RIGHT JOIN

SELECT table1.column_name, table2.column_name
FROM table1
FULL OUTER JOIN table2 
ON table1.common_column = table2.common_column;

DDL and DML in SQL

Lameck Odhiambo — Tue, 08 Sep 2026 13:00:31 +0000

In relational database management systems, interacting with data requires two distinct capabilities: building the structure and managing the information within it. SQL achieves this by dividing its capabilities into specialized sub-languages, primarily Data Definition Language (DDL) and Data Manipulation Language (DML). Together, these two components form the backbone of all database administration and development.

DDL

DDL stands for data defination language
It is a subset of SQL commands used exclusively to define, modify, and manage the structure of database objects (like tables, schemas, indexes, and views) rather than the data itself.

Commands used are CREATE,ALTER, DROP, TRUNCATE & RENAME

Difference between DROP and TRUNCATE
The primary difference is that DROP deletes both the data and the entire table structure from the database, while TRUNCATE deletes all rows of data but keeps the table structure intact for future use

DML

DML (Data Manipulation Language) is a subset of SQL commands used to manage and modify data stored within existing database tables.
Unlike DDL (Data Definition Language), which creates or alters the structure of the database itself, DML only affects the rows and records inside those structures.
The four primary DML commands are INSERT, UPDATE, DELETE, and SELECT. (Note: In strict database theory, SELECT is sometimes classified as DQL or Data Query Language, but in daily practice, it is widely grouped under DML)

Getting started with SQL is 'HARD' until you follow these 10 steps.

Lameck Odhiambo — Fri, 24 Jul 2026 07:12:40 +0000

Introduction

SQL (Structured Query Language) is the standard programming language used to manage, query, and manipulate data within relational database management systems (RDBMS). It allows you to talk directly to databases to perform tasks like retrieving data, adding new records, or building database structures.

A database is an organized collection of digital data stored in a computer system so it can be easily accessed, managed, modified, and updated. It relies on software called a database management system (DBMS) to control access and secure the information.

SQL commands are divided into five main categories based on their functionality: DDL, DML, DQL, DCL, and TCL. These groupings allow database professionals to easily manage database structures, manipulate or retrieve data, control security permissions, and oversee multi-step transactions.

1. DDL(Data Defination Language)

DDL commands define and modify the database structure or schema. They operate on the design of the layout (like tables, indexes, and views) rather than the actual rows of data. DDL actions automatically save to the database and cannot be undone. e.g

CREATE: Builds new database objects
ALTER: Modifies the structure of an existing object
DROP: Deletes tables or entire databases permanently
TRUNCATE: Removes all records from a table while keeping its structure intact

2. DML (Data Manipulation Language)

DML commands modify and manipulate the data stored within existing tables. Unlike DDL, these changes do not automatically save immediately and can be rolled back if an error occurs.

INSERT: Adds new rows of data to a table.
UPDATE: Modifies existing rows of data
DELETE: Removes specific rows based on a given condition

3. DQL (Data Query Language)

DQL commands are explicitly focused on retrieving and reading data from the database. It is the most common command category used by data analysts.

SELECT: Fetches records from one or more database tables

4. DCL (Data Control Language)

DCL commands handle security, authentication, and access control within the database environment. Database administrators use DCL to set up system permissions for specific user accounts

GRANT: Gives a user account specific privileges to read or modify data
REVOKE: Withdraws previously granted access permissions from a user account

5. TCL (Transaction Control Language)

TCL commands manage operations grouped as transactions to ensure overall data integrity and consistency. They work directly alongside DML statements to finalize or discard multi-step changes.

COMMIT: Permanently saves all active transactional changes to the database
ROLLBACK: Reverts uncommitted operations, restoring the database to its previous state.
SAVEPOINT: Creates an intermediate checkpoint within a transaction to roll back to if needed.
In this particular case, we are going to create a database for a school, Greenwood Academy using PostgreSQL.

Ok lets get hands-on by going through the following steps. Come along!

Step 1: Create Schema

A database schema is the structural blueprint or framework that defines how data is organized, stored, and related within a database.
In PostgreSQL, the search_path configuration variable determines the order in which schemas are searched to find database objects (like tables, views, or functions) when a query uses an unqualified name. It behaves similarly to the PATH environment variable in an operating system.

-- SECTION A - Building the Database (DDL)

-- Create a schema called greenwood_academy 
create schema greenwood_academy;

set search_path to greenwood_academy;
show search_path;

Step 2: Creating tables

A database table is a structured collection of related data organized into a grid format. It serves as the core building block of any relational database management system (RDBMS), allowing you to store, organize, and query information efficiently.

-- Create the students table with the following columns:
create table greenwood_academy.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));

-- Create the subjects table with the following columns:
create table greenwood_academy.subjects(
subject_id  INT PRIMARY key,
subject_name VARCHAR(100) unique not null,
department  VARCHAR(50),
teacher_name    VARCHAR(100),
credits INT
);

-- Create the exam_results table:
create table greenwood_academy.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)
);

Step 3: Alter

Altering a database structure uses the ALTER command to change table designs. You can add columns, drop columns, and modify data types.

-- After creating the students table, the school realises they forgot to include a phone number column. Use ALTER TABLE to add a column called phone_number with data type VARCHAR(20).
alter table greenwood_academy.students 
add column phone_number varchar(20);

-- The column credits in the subjects table needs to be renamed to credit_hours. Write the SQL to rename it.
alter table greenwood_academy.subjects
rename column credits to credit_hours;

-- The school decides they no longer need the phone_number column you added in Q5. Write the SQL to remove it completely from the students table.
alter table greenwood_academy.students 
drop column phone_number;

Step 4: Inserting data in the database

To insert data into a relational database, you use the standard SQL INSERT INTO statement, which adds new rows or records to a specified table.


-- Inserting all 10 students into the students table
insert into greenwood_academy.students(student_id,first_name,last_name,gender,date_of_birth,class,city)
values 
(1,'Amina','Wanjiku','F','2008-03-12','Form 3','Nairobi'),
(2,'Brian','Ochieng','M','2007-07-25','Form 4','Mombasa'),
(3,'Cynthia','Mutua','F','2008-11-05','Form 3','Kisumu'),
(4,'David','Kamau','M','2007-02-18','Form 4','Nairobi'),
(5,'Esther','Akinyi','F','2009-06-30','Form 2','Nakuru'),
(6,'Felix','Otieno','M','2009-09-14','Form 2','Eldoret'),
(7,'Grace','Mwangi','F','2008-01-22','Form 3','Nairobi'),
(8,'Hassan','Abdi','M','2007-04-09','Form 4','Mombasa'),
(9,'Ivy','Chebet','F','2009-12-01','Form 2','Nakuru'),
(10,'James','Kariuki','M','2008-08-17','Form 3','Nairobi');



-- Inserting all 10 subjects into the subjects table 

INSERT INTO greenwood_academy.subjects (
    subject_id,
    subject_name,
    department,
    teacher_name,
    credit_hours
)
VALUES
    (1, 'Mathematics', 'Sciences', 'Mr. Njoroge', 4),
    (2, 'English', 'Languages', 'Ms. Adhiambo', 3),
    (3, 'Biology', 'Sciences', 'Ms. Otieno', 4),
    (4, 'History', 'Humanities', 'Mr. Waweru', 3),
    (5, 'Kiswahili', 'Languages', 'Ms. Nduta', 3),
    (6, 'Physics', 'Sciences', 'Mr. Kamande', 4),
    (7, 'Geography', 'Humanities', 'Ms. Chebet', 3),
    (8, 'Chemistry', 'Sciences', 'Ms. Muthoni', 4),
    (9, 'Computer Studies', 'Sciences', 'Mr. Oduya', 3),
    (10, 'Business Studies', 'Humanities', 'Ms. Wangari', 3);

-- Inserting all 10 exam results into the exam_results table 
INSERT INTO greenwood_academy.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-15', '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');

Step 5: Querying

Querying a database is the process of requesting specific data or performing actions on records stored within a database system. Users and applications write queries using a structured language—most commonly SQL (Structured Query Language)—to filter, calculate, combine, or modify information into a human-readable format.

-- After inserting the data, run a SELECT query to confirm all 10 rows exist in each of the three tables.
select * from greenwood_academy.students;

select * from greenwood_academy.subjects;

select * from greenwood_academy.exam_results;

Step 6: Updating

Update is a SQL operation that modifies existing records in a database table. When you update data, you can change the values stored in one or more columns

-- Esther Akinyi has moved from Nakuru to Nairobi. Write an UPDATE statement to change her city. (Her student_id is 5)
update greenwood_academy.students 
set city ='Nairobi'
where student_id =5;

-- The marks for result_id 5 were entered incorrectly - the correct marks are 59, not 49. Write an UPDATE to fix this.
update greenwood_academy.exam_results
set marks =59
where result_id =5;

-- The exam result with result_id 9 has been cancelled by the school. Write a DELETE statement to remove it from the exam_results table.
delete from greenwood_academy.exam_results
where result_id =9;

Step 7: Filtering

Filtering in a database is the process of selecting a specific subset of records from a larger dataset based on defined conditions or rules. Instead of loading millions of unnecessary rows, filtering isolates only the data relevant to your needs, which optimizes query performance and speeds up analysis.

-- Writing a query to find all students who are in Form 4.

select first_name,last_name from greenwood_academy.students
where class = 'Form 4';

-- Writing a query to find all subjects in the Sciences department.
select subject_name from greenwood_academy.subjects
where department = 'Sciences';

-- Writing a query to find all exam results where the marks are greater than or equal to 70.
select subject_id,marks from greenwood_academy.exam_results
where marks >= 70;

-- Writing a query to find all female students only. (Hint: gender = 'F')
select first_name,last_name from greenwood_academy.students
where gender = 'F';

-- Writing a query to find all students who are in Form 3 AND from Nairobi.
select first_name,last_name,class,city from greenwood_academy.students
where (class = 'Form 3') and (city = 'Nairobi');


-- Writing a query to find all students who are in Form 2 OR Form 4.
select first_name,last_name,class from greenwood_academy.students
where (class = 'Form 2') or (class = 'Form 4');

Step 8: Memebership

Membership operators are used to check if a specific value exists within a collection of data, such as a static literal list, a subquery result, a JSON object, or a native arra

-- Write a query to find all exam results where marks are between 50 and 80 (inclusive).
select * from greenwood_academy.exam_results
where marks between 50 and 80;

-- Write a query to find all exams that took place between 15th March 2024 and 18th March 2024.
select * from greenwood_academy.exam_results
where exam_date between '2024-03-15' and '2024-03-18';

-- Write a query to find all students who live in Nairobi, Mombasa, or Kisumu - use IN.
select first_name,last_name,city from greenwood_academy.students
where city in ('Nairobi','Mombasa','Kisumu');

-- Write a query to find all students who are NOT in Form 2 or Form 3 - use NOT IN.
SELECT first_name, last_name, city, class
FROM greenwood_academy.students
WHERE class NOT IN ('Form 2', 'Form 3');

-- Write a query to find all students whose first name starts with the letter 'A' or 'E' - use LIKE.
select first_name from greenwood_academy.students
where first_name like 'A%' 
     or first_name like 'E%';


-- Write a query to find all subjects whose subject name contains the word 'Studies'.
select * from greenwood_academy.subjects
where subject_name ilike '%Studies%';

Step 9: Count

The COUNT() function in PostgreSQL is an aggregate function used to return the total number of rows or non-null values that match your query parameters.

-- Finding number of students currently in Form 3? 
select count(student_id) as number_of_students_form3 from greenwood_academy.students
where class = 'Form 3';

-- Number of exam results that have a mark of 70 or above? Write the query.
select count(result_id) as marks_above_70 from greenwood_academy.exam_results
where marks >=70;

Step 10: CASE - WHEN Statements

In PostgreSQL, the CASE expression acts as an IF-THEN-ELSE conditional statement that allows you to execute logic directly within your SQL queries. It evaluates conditions sequentially from top to bottom and returns a value as soon as the first condition evaluates to true.

-- Writing a query using CASE WHEN to label each exam result with a grade description:

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

select * from greenwood_academy.exam_results;

-- Writing a query using CASE WHEN to label each student 
select first_name,last_name,class,
case 
     when class = 'Form 3' or class ='Form 4' then 'Senior'
     when class = 'Form 2' or class ='Form 1' then 'Junior'
end as student_level 
from greenwood_academy.students;

Conclusion

Structured Query Language (SQL) is the foundational cornerstone of modern data management and the universal standard for interacting with relational databases. Despite the rapid emergence of non-relational database technologies, SQL remains an irreplaceable skill due to its high efficiency, declarative simplicity, and absolute dominance in corporate data ecosystems.

Publishing and Embedding - BEST ways to SHARE your PowerBi Reports

Lameck Odhiambo — Wed, 15 Jul 2026 10:49:31 +0000

Introduction

Imagine (or if you can't imagine you can try this practically ) you have finished your analysis in powerBi Desktop and you want to share your insights, How do you share these well thought insights? Worry no more you're just at the right place at the right time to get better in PowerBi. Sit tight and Buckle up!
‎
‎Well that's where publishing and embedding comes in - These are just 'fancy' terms trust me, these processes are as easy as ABC. The last step of preparing powerBi dashboard and reports is publishing or embedding so that you can share your reports to the concerned persons.
‎
‎Before we continue learning, Let's get a clear difference between these terms. That is PowerBi Desktop vs powerBi service, publishing vs embedding.

PowerBI Desktop vs PowerBI Service

PowerBI Desktop is a free Windows application used for building data models and designing reports. Power BI Service is a cloud-based web platform used to publish, share, and collaborate on those reports.

Publishing vs Embedding

Publishing is the initial step where you upload a report from Power BI Desktop to the cloud (Power BI Service). Embedding is the secondary step of taking that published report and displaying it directly inside an external website, portal, or custom application (via an iFrame or REST API) ‎

Pre -requisites

Curiosity
Accounts created in PowerBi service in your favourite browser(Premium account)
PowerBi Desktop App
VsCode or any other editor
PowerBi report you want to publish/embedd

1. Publishing Reports

Publishing from Power BI Desktop is required when you want to move your local .pbix file to the cloud for sharing and collaboration. You should publish when your report is ready, requires team collaboration, or needs to be embedded into secured platforms like Microsoft Teams or SharePoint

Below is a sample report I want to publish named ' diaspora remittance '.

Create an account in PowerBi service as earlier stated by going to your search browser and searching for powerBi service.
Using the same details also log in to your powerBi desktop service

Create a workspace in powerBi service to hold your project eg I created one named ' Data_Remittance '

In your powerBi desktop click on publish on the top right and select the workspace you created in powerBi service
My work space was as below - The workspace I created in powerBi service. You should also be able to see yours here
After selecting your workspace. When you go back to powerBi service, you should see your powerBi reports under your workspace.
You can share the report as link to the targeted audience or person. This is one process.

2. Embedding Reports

Power BI embedding is used to integrate interactive data visuals directly into your web applications, internal portals (like SharePoint), or SaaS products. This allows end-users to analyze data without leaving their familiar workflows or logging into the external Power BI service

To get the code to use to embed the report; navigate to the file section within the published report in the powerBi service, select embed report then publish to web
Copy the HTML code. This you can use in your desired website by putting it within the codes of your website.

Below is an example how I embedded it within this sample web using VSCode editor.
This is how it looked within the sample web. Amazing!

Conclusion

Just like that, you can share your powerBi reports. Publishing and embedding reports in Power BI democratizes your data by transitioning static files into dynamic, cloud-based experiences. It bridges the gap between raw analysis and actionable business insights, allowing stakeholders to easily access, filter, and consume live data directly within their daily workflows.

The ONLY Guide you NEED to connect SQL Databases to POWERBI

Lameck Odhiambo — Sun, 05 Jul 2026 08:41:41 +0000

Introduction

Integrating SQL databases with Power BI is one of the most effective and widely adopted approaches for creating powerful, scalable business intelligence solutions. With Power BI, you can easily connect to various relational databases such as Microsoft SQL Server, Azure SQL Database, PostgreSQL, MySQL, Oracle, and many others. This seamless connectivity enables you to analyze massive datasets with ease and build dynamic, interactive reports and dashboards.

In this one particular I accessed data sitting in a database (both local and in cloud), for this case it will be postgresql database. After accessing the database, then connecting it powerBi.

Must have

PostgreSQL must be installed and running on your computer (or a local/server machine you can access). Ensure you are able to access the following while installing the Postgres.

Host (usually localhost or 127.0.0.1)
Port (default is 5432)
Database name
Username (e.g., Postgres)
Password(of the database created)

Aiven account and in it install postgresql
DBeaver(Universal database software)
PowerBi Desktop

Local Database

A local database is a data storage system that resides directly on your device or a restricted local network rather than on a remote cloud server. In this case it resides in our system device.

Step 1: Dbeaver and Database set up

Open DBeaver.
Click Database - New Database Connection - Select PostgreSQL.

Fill in the same details (for your local database): Server Host, Port, Database, Username, Password.
Click Test Connection. If it says "Connected", you’re good.
Expand your database to confirm you can see the tables. Once this works, you can connect the exact same details in Power BI.

Inside your database you can create schema so that you can import or insert data in your database as a test experiment. In this case I uploaded a jcars.csv dataset.

To import data into your postgresql database, right click on the schema, where there will be an option to import data.
Select input files > browse(to get data in the pc) > proceed and finish.

Step 2: Connecting local database to PowerBi

Open Power BI Desktop.
Click Home > Get Data > Database > PostgreSQL database.

In the connection window, fill in: - Server: localhost (or 127.0.0.1 or your server name) eg 127.0.0.1:5432
Database: Your database name eg postgres
Click OK.
Enter your Username eg postgres and Password when prompted. -Click Connect.

Step 3: Load Your Data

In the Navigator window, you will see all available tables and views. Select the tables you want to use.
Click Load or Transform Data (to clean/edit first in Power Query).

A cloud-based SQL database such as Aiven

Step 1: Test Connection in DBeaver

Open DBeaver.
Click New Connection - Select PostgreSQL - Next.
Fill in the details from Aiven account, you simply copy and paste:

Inside your database you can create schema so that you can import or insert data in your database as a test experiment. In this case I uploaded a jcars.csv dataset.
To import data into your postgresql database, right click on the schema, where there will be an option to import data.
Select input files > browse(to get data in the pc) > proceed and finish.

Step 2: Open Power BI Desktop

Go to Home > Get Data > Database > PostgreSQL database.

Enter the connection details:
Server: yourhost.aivencloud.com:port (Example: pg-abc123.aivencloud.com:port provided)
Database: defaultdb (or your database name)
Click OK.
Enter your Username and Password when prompted.
In the Navigator window, select the tables you want - Click Load if it is clean (or Transform Data so that you clean and analyze it).

In my case as below it was unable to connect. To solve this issue you must download the CA certificate

The CA certificate is in the Aiven account
In your search bar seach manage user certificate then follow the steps below
Windows Trusted Root Certification > Right click on the certificates > All task > Import > Next > Browse > Change file types to all files > Click on the downloaded certificate > Next > Finish > Yes > Ok
Under CA Certificate, browse and select ca.pem file you downloaded from Aiven, import then finish installing.
Close your powerbi and enter the details again. This time it will connect to the database

Conclusion

Connecting database data to Power BI completely eliminates the need for manual, error-prone spreadsheet exports . By centralizing information, organizations create a reliable single source of truth . This ensures consistent metrics and unlocks scalable, automated business intelligence.

Data Modeling, Joins, Relationships and Schemas

Lameck Odhiambo — Mon, 22 Jun 2026 07:52:40 +0000

Before data reaches its final used destination it needs to be organized in a structured way to enable easy retrieval performance and good storage - This is data modeling. Data modeling is the process of creating a blue print of how data is connected, stored and retrieved in a system. This enables you to create an organized structure for your tables.

Reasons for data modeling

Data consistency
Optimize performance of the queries
Scalability and maintenance
Optimize cost in storage

Layers of data modeling

1.Conceptual Data Model
The highest-level, business-focused view. It defines what data is being collected and how business concepts relate to one another that is subject, characteristics and relation. Invovles gathering information from stake holders.
Agile and Waterfall method of gathering requirements - Waterfall and Agile are two fundamentally different approaches to project management. Waterfall is a linear, step-by-step process where each phase must be completed before the next begins. Agile is an iterative, flexible approach that breaks projects into smaller cycles for continuous improvement and rapid delivery.

Focus:Business entities (e.g., Customers, Products, Orders) and their relationships.
Audience: Business stakeholders, domain experts, and product managers.
Details: Tech-agnostic; no attributes, data types, or system implementations are specified

2. Logical Data Model
The bridge between the business requirements and the technical solution. It defines structure by establishing facts (events) and dimensions (context).

Focus: Data attributes, primary/foreign keys, and specific data objects.
Audience: Data architects and business analysts.
Details:Technology-neutral; independent of the specific database management system (DBMS) being used

Here we come up with an ER(Entity)- Relation Diagram

3. Physical Data Model
The most technical and concrete layer. It dictates exactly how the data will be stored and structured in a specific database.

Focus: Table names, column specifications, data types, storage methods, and compression techniques.
Audience: Database administrators, developers, and data engineers.
Details: Highly specific to a chosen engine (e.g., PostgreSQL, Snowflake, BigQuery)

Types of Data modeling

1. OLTP (Online Transactional Processing)

The process of designing databases to handle high volumes of fast, real-time, day-to-day transactions (such as e-commerce checkouts or banking transfers). Its primary goal is to ensure data integrity, eliminate redundancy, and support rapid write, update, and delete operations.
This is the fast step taken before moving data to a datawarehouse from a database.

Core Principles of OLTP Modeling

Normalization (up to 3NF): Data is broken down into smaller, logical tables to eliminate duplication. For instance, a customer’s address will live in a single Addresses table rather than being repeated on every single order.
Entity-Relationship (ER) Design: Models are created by identifying distinct entities (e.g., Customers, Products, Orders) and establishing strict relationships (e.g., one-to-many, many-to-many) between them.
ACID Compliance: The model prioritizes atomicity, consistency, isolation, and durability so that complex, multi-step transactions either succeed entirely or roll back cleanly without data corruption

Best Practices

Implement Strong Constraints: Use Primary Keys (PK), Foreign Keys (FK), UNIQUE constraints, and NOT NULL rules at the database level to enforce strict data integrity.
Index Wisely: Index your Primary and Foreign Keys to speed up row retrieval, but avoid over-indexing, as this will slow down write-heavy transactions.
Choose the Right Technology: Utilize robust Relational Database Management Systems (RDBMS) like postgresql, oracle or MySQL.

Common Data Types in Data Modeling

Data types are generally divided into standard primitive types and advanced complex structures.

Numeric Types

Integer: Stores whole numbers without decimals (e.g., ID numbers or inventory counts).
Float / Real: Stores approximate numerical values with fractional decimals for scientific data.
Decimal / Numeric: Stores exact fixed-point decimals, making it ideal for financial amounts.

String and Text Types

CHAR: Holds fixed-length text character strings, padding shorter inputs with spaces.
VARCHAR: Holds variable-length text strings up to a specified maximum length.
TEXT / CLOB: Stores large blocks of character data, such as product descriptions or articles.

Date and Time TypesDATE: Records calendar dates consisting of the year, month, and day.

TIME: Captures precise hours, minutes, and seconds.
TIMESTAMP: Combines date and time to track real-time systemic events or logs.

Logical and Binary

TypesBoolean: Evaluates to true or false states to support logical checks.
BLOB: Keeps raw binary large objects, including uploaded imagery, video files, or document attachments.

Complex and Semi-Structured Types

Array: Groups a list of multiple values inside a single column field.
Struct / JSON: Embeds a nested key-value format block to represent flexible, semi-structured object details

Primary keys in a Database

In a database, a key is an attribute (column) or a collection of attributes used to uniquely identify rows within a table and establish relationships between multiple tables. Keys are foundational for enforcing data integrity, preventing duplication, and ensuring efficient data retrieval.

Why Database Keys Matter

Enforce Uniqueness: They stop identical duplicate rows from muddying your datasets.
Connect Data: They link related concepts (e.g., matching a CustomerID foreign key in an Orders table back to the master Customers profile).
Speed Up Searches: Database engines automatically build indexes around key fields, drastically accelerating query performance.

Relationships in a database

Database relationships are logical links established between two or more tables based on a common column. In a relational database management system (RDBMS) like MySQL or PostgreSQL, these connections dictate how records interact. They use Primary Keys (PK) and Foreign Keys (FK) to eliminate redundant data and maintain data integrity
Inorder to connect different entities in a database we need relationships, to configure relationships we need cardinality

Used Cases

One to Many
A one-to-many (1:N) relationship occurs when a single record in one table (the parent) links to multiple records in another table (the child), but each child record maps back to exactly one parent record. It is the most common pattern in database design because it minimizes redundant data and enforces clear hierarchies
e.g Customers and Orders, Departments and Employees

Many to Many
A many-to-many (M:N) relationship occurs in a database when multiple records in one table are associated with multiple records in another table. Relational database systems cannot link two tables directly in this manner because doing so violates database normalization principles, leading to severe data duplication and maintenance issues.

One to One
A one-to-one (1:1) database relationship occurs when a single record in Table A is linked to exactly one record in Table B, and vice versa. It means each row in either table has a maximum of one matching row on the opposite side. eg Person and Passport,Country and Capital City,Car and License Plate,Store User and Shopping Cart,Employee and Desk Assignment,App Account and Premium Subscription

Normalization

Database normalization is a systematic design process used to organize data in a relational database to minimize data redundancy and eliminate data modification anomalies. e.g 1NF, 2NF,3NF

After creating these relationships, the next process is the last layer that is the physical layer. Implementing the conceptual layer and logical layer by writing SQL scripts.

2. OLAP Data Modeling(Online Analytical Processing)

Source of data are the databases created using OLTP data modeling. Online Analytical Processing (OLAP) data modeling structures data for rapid querying and business intelligence. It organizes information into a multidimensional model

Database ---------> Bronze -----------> Silver ----------> Gold

Bronze

Exact replica of tables from database

Silver

Transformed data
Aggregations e.g One big table(OBT)

Gold

Dimension data model
Fact and Dimensions Tables

The most common ways to physically structure OLAP models are through specific schemas in a data warehouse

1. Star Schema
The most widely used and recognizable model.Structure: Consists of a central fact table surrounded by multiple dimension tables.

Fact Table: Contains the quantitative measurements (e.g., Sales Amount, Units Sold) and foreign keys mapping to the dimensions.

Dimension Tables: Highly denormalized tables containing descriptive attributes (e.g., Customer Name, Store Location, Product Category).

Benefit: Simplicity and extremely fast read times, as it requires fewer table joins to get analytical results.

2. Snowflake Schema
A refinement of the star schema.

Structure: Similar to the star schema, but the dimension tables are normalized, meaning they branch out into sub-dimension tables.

Example: A "Product" dimension might connect to a "Category" sub-dimension, which connects to a "Department" sub-dimension.

Benefit: Reduces data redundancy and takes up less storage space, though queries may require more complex joins

Dimensions tables do change with time, hence need for SCDs( Slowly Changing Dimensions)

Type 0 - No change
Type 1 - Upsert / Overwrite
Type 2 - Tracking history of changes
Type 3 - Adds new column

SCDS explained check here https://en.wikipedia.org/wiki/Slowly_changing_dimension

Joins in data modeling

Joins are heavily used in both OLTP and OLAP, but they are used for completely different reasons and perform differently in each system.
In data modeling, joins are operations used to combine rows from two or more tables horizontally into a single dataset, based on a related common key (such as an ID). They are fundamental for integrating normalized databases and bringing related data together for reporting and analysis.

OLTP (Online Transaction Processing)

How it’s used: Joins are necessary. OLTP systems process day-to-day business transactions (like an e-commerce checkout) and use highly normalized schemas.

The Goal: Data is split into many small tables (e.g., customers, orders, products) to prevent duplication and ensure fast, accurate data entry.

Impact: Queries join a few tables together, but they typically only touch a very small number of rows (e.g., a single customer's specific order), making these joins extremely fast.

OLAP (Online Analytical Processing)

How it’s used: Joins are typically used in relational data warehouses (using Star or Snowflake schemas) to connect a central Fact table to surrounding Dimension tables.

The Goal: OLAP is designed for complex, historical analysis scanning millions of rows. Because large-scale joins are computationally expensive, OLAP models use denormalization (duplicating some data) to keep joins to a minimum and boost query performance.

Impact: Queries involve multi-table joins and massive aggregations, which naturally take longer (seconds or minutes) but yield deep business insights.

The 4 Primary Types of Joins

The type of join you choose determines how unmatched data (rows that don't share a common key) is handled:

INNER JOIN: Returns only the rows where there is a matching value in both tables. If a record doesn't exist on both sides, it is excluded.

LEFT JOIN (Left Outer): Returns all rows from the left table, and the matching rows from the right table. If there is no match on the right side, the result will contain null for the right-hand columns.

RIGHT JOIN (Right Outer): Returns all rows from the right table, and the matching rows from the left table. If there is no match on the left side, the result will show null for the left-hand columns.

FULL JOIN (Full Outer): Returns all records when there is a match in either the left or right tables. If there is no match on either side, the result will contain null.

Point worth noting

Joins vs. Relationships Joins physically merge or combine datasets to create a new, static result set (commonly used in SQL queries or Power Query).Relationships establish an ongoing, logical connection between tables so the modeling tool (like Microsoft Power BI or Tableau) can calculate metrics across the tables dynamically.

Conclusion

The Master Blueprint of Data Success
Data modeling is not just about organizing tables. It is the secret blueprint that turns messy, raw numbers into powerful business insights. By mastering schemas, relationships, and joins, you build a solid foundation for any data project.The Star Schema serves as your ultimate map, keeping your data clean and organized. Relationships act as smart bridges, letting your tables talk to each other without creating clutter. Meanwhile, joins work like glue to merge data when you need a single, complete view.When these three tools work together, magic happens. Your reports run faster, your numbers stay accurate, and your business can grow without slowing down. In short, a great data model turns confusing data into clear, actionable answers.