DEV Community: Steve Mwangi

lets explore data Structures in Python

Steve Mwangi — Sun, 20 Sep 2026 15:33:24 +0000

Introduction

Data science begins with data, and how that data is stored determines how easily it can be cleaned, explored, and analyzed.
Python is a data science tool tat anyone who is interested in data science would be wise to utilize.
Python's four core data structures (lists, tuples, dictionaries, and sets) form the foundation for this work.
Lists hold ordered collections such as a column of measurements or a series of records. Tuples protect values that should not change, such as fixed coordinates or configuration settings. Dictionaries pair keys with values, which mirrors the labelled data found in JSON files, APIs, and tabular records. Sets store only unique values, which makes them well suited to removing duplicates and comparing datasets.

This guide covers how to create, access, modify, and remove data in each structure, with worked examples.

1. Lists

A list can hold data of any type, and data of various types can be stored in the same list.
Lists are mutable, i.e. the data in a list can be modified once the list has been created.

1.1 Creating a list

A list is defined using a set of square brackets.

list_name = []

1.2 Storing data in a list

Data in a list can be added during the creation of the list.
To illustrate this, let us create a list, mixed_bag with a mix of data types, and use it to explore how to add data to a list.

mixed_bag = [1, 2, 'bob']

Data can be added to an existing list using the following list functions:

1.2.1 `.append()` function

This function adds a data item to the end of the list.

mixed_bad.append(3)
print(mixed_bag)
# outputs [1, 2, 'bob', 3]

1.2.2 `.insert()` function

This function adds an item at a specified index in the list.

mixed_bag.insert(1, 'ross')
print(mixed_bag)
# outputs [1, 'ross', 2, 'bob']

1.2.3 `.extend()` function

This function adds multiple data items to the end of a list.

mixed_bag.extend([3, 'moon'])
print(mixed_bag)
# outputs [1, 2, 'bob', 3, 'moon']

1.3 Accessing data in a list

1.3.1 Indexing

Data in a list is accessed through indexing.
Every item in a list is assigned a value that identifies its position in the list; the first item is assigned the position 0.

We can access the item at position 1 in our list as follows:

print(mixed_bag[1])
# outputs 2

Python allows positive and negative indexing. Negative indexing allows one to select items from the end of the list, starting with the last item by calling the index -1.

we can access the last item in our list by negative indexing as follows:

print(mixed_bag[-1])
# outputs bob

1.3.2 Slicing

Slicing is a method that enables you to select multiple values in a list.

Syntax of slicing

list_name[start_index : stop_index : step]

start index indicates where the slicing will start.
stop index is endpoint exclusive, i.e. it is not included in the slice. Slicing stops at stop_index - 1.
Step is an optional parameter that indicates the increment value between each selected element. By default it is 1.

mixed_bag = [1, 2, 'bob', 3, 'ross', 5, 6]

print(mixed_bag[1:4])
# outputs [2, 'bob', 3]

# using step

print(mixed_bag[0:5:2])
# outputs [1, 'bob', 'ross']

1.4 Removing data from a list

To remove data from a list you can use the following functions:

1.4.1 `.remove()` function

It removes an item from a list based on the value of the item.

mixed_bag = [1, 2, 'bob', 3]
mixed_bag.remove(2)
print(mixed_bag)
# outputs [1, 'bob', 3]

1.4.2 `.pop()` function

This function removes an item from the list and returns the removed item. By default, it removes the last item in a list.

removed_item = mixed_bag.pop()
print(removed_item)
# outputs 3

You can refine the use of pop() by passing an index, to remove an item at a particular index and return it.

removed_item = mixed_bag.pop(-2)
print(removed_item)
# outputs bob

2. Tuples

A tuple is an immutable list.
The data in a tuple cannot be changed once the tuple is defined.

A tuple is defined using a set of parentheses.

tuple_name = ()

The data is placed in the parentheses, separated by commas.

Lets place the data in our prior list to a tuple.

mixed_tuple = (1, 2, 'bob', 3)

2.1 Accessing data in a tuple

Data in a tuple can be accessed using both positive and negative indexing.

mixed_tuple = (1, 2, 'bob', 3)

print(mixed_tuple[2])
# outputs bob

# using negative indexing

print(mixed_tuple[-1])
# outputs 3

You can also use slicing to access a section of a tuple.

print(mixed_tuple[:2])
# outputs (1, 2)

2.2 Modifying a tuple

Tuples are immutable, i.e. once a tuple is defined, attempting to modify a value in it, or adding a value to it, will raise an error.

mixed_tuple[2] = 5
# This error is thrown: 
# TypeError: 'tuple' object does not support item assignment

2.3 Unpacking a tuple

You can unpack the items in a tuple into variables; this is called unpacking.

To do this, you must create variables equal in number to the items in the tuple you are unpacking.

mixed_tuple = (1, 2, 'bob', 3)

num_1, num_2, name, num_3 = mixed_tuple
print(f"num 1 : {num_1}")   # outputs num 1 : 1
print(f"num 2 : {num_2}")   # outputs num 2 : 2
print(f"num 3 : {num_3}")   # outputs num 3 : 3
print(f"name : {name}")     # outputs name : bob

3. Dictionaries

A Python dictionary stores data in a key and value pair format.
Each key in the dictionary must be unique, and the key must be an immutable type of data.

During dictionary definition, the key and value in each pair are separated by a colon (:), and multiple pairs in the dictionary are separated by commas. The list of pairs is surrounded by curly braces.

3.1 Syntax of a dictionary

dictionary_name = {
    key_1 : value_1,
    key_2 : value_2
}

3.2 Adding data to a dictionary

You can add new data to a dictionary by assigning a value to a new key that does not exist in the dictionary.

dict = {
    1: 'bob',
    2: 'ross',
}

dict['new'] = 'doe'
print(dict)  
# outputs {1: 'bob', 2: 'ross', 'new': 'doe'}

You can modify data in the dictionary by assigning a new value to an existing key in the dictionary.

dict[1] = 'mon'
print(dict)
# outputs {1: 'mon', 2: 'ross'}

3.3 Accessing data in a dictionary

You access the value in a dictionary by calling its key. Remember that Python is case sensitive, thus one must call the key in the case it was stored.
If you call a non-existent key, an error occurs.

print(dict[1])
# outputs bob

3.4 Removing data from a dictionary

To remove data from a dictionary, you use the delete statement (del) or the pop() function.

When using del, you pass the dictionary name and the key of the value you want to remove. This will remove the key-value pair from the dictionary.

del dict[1]
print(dict)
# outputs {2: 'ross'}

Using the pop() function removes the key and its value and returns the value.

dict = {
    1: 'bob',
    2: 'ross',
}

removed = dict.pop(2)
print(removed)  # outputs ross
print(dict)     # {1: 'bob'}

You can remove all items in the dictionary using the clear() function. This removes all the key-value pairs and makes the dictionary empty.

dict = {
    1: 'bob',
    2: 'ross',
}

dict.clear()
print(dict) # outputs {}

3.5 Iteration over a dictionary

You can loop through a dictionary by its keys using the .keys() function.

dict = {
    1: 'bob',
    2: 'ross',
}

for key in dict:
    print(key)

# outputs 1
#         2

You can also iterate over a dictionary by its values using the .values() function.

dict = {
    1: 'bob',
    2: 'ross',
}

for value in dict.values():
    print(value)

# outputs bob
#         ross

You can also use the .items() function to get both the key and value as a tuple while iterating over the dictionary.

dict = {
    1: 'bob',
    2: 'ross',
}

for key, value in dict.items():
    print(f"{key}, {value}")

# outputs 1, bob
#         2, ross

4. Sets

A set is a collection of unique data; duplicate values are automatically removed.
Sets are unordered, such that data cannot be accessed using indexing, since the order of the data may change every time the set is called.

4.1 Defining a set

Sets are defined using curly brackets.

set = {1, 2, 'bob', 'ross', 2}

print(set) # 2 is only displayed once

4.2 Adding data to a set

You can add data to a set by using the .add() function.

set = {1, 2, 'bob', 'ross', 2}
set.add('water')
print(set) 
# outputs {'ross', 1, 2, 'water', 'bob'}

4.3 Removing data from a set

4.3.1 `.remove()` function

You can remove an item from a set by using the .remove() function, where you pass the item to be removed.
If the item is not found in the set, an error occurs.

set = {1, 2, 'bob', 'ross', 2}
set.remove(2)

print(set) 
# outputs {1, 'bob', 'ross'}

# Attempting to remove an item that does not exist
set.remove(5)
print(set) 
# This error is shown: KeyError: 5

4.3.2 `.discard()` function

The .discard() function works like .remove() but differs in that if the item to be removed is not found in the set, no error occurs.

set = {1, 2, 'bob', 'ross', 2}
set.discard('bob')

print(set) 
# outputs {1, 2, 'ross'}

# Attempting to remove an item that does not exist
set.discard(5)
print(set) 
# outputs {1, 2, 'ross', 'bob'} i.e nothing has been removed

4.4 Set operations

Set operations use the characteristic of sets to store unique data to perform comparisons between data sets.

We can create two sets to explore set operations.

set_1 = {1, 2, 'bob', 'ross', 2}
set_2 = {1, 3, 5, 6, 'ross'}

4.4.1 Intersection (&)

This operation returns only the data available in both data sets.

in_both = set_1 & set_2
print(in_both)
# outputs {1, 'ross'}

4.4.2 Union (|)

This operation returns unique data that is available in either data set.
It simply retuns all the unique data items fron both data sets.

in_either = set_1 | set_2
print(in_either)
# outputs {1, 2, 3, 5, 6, 'ross', 'bob'}

4.4.3 Difference

This operation returns only the data that is in one data set but not in the other.
It returns the unique data in one data set when compared to another.

in_only_1 = set_1 - set_2
print(in_only_1)
# outputs {'bob', 2}

4.4.4 Symmetric difference

This operation returns the data that is in either data set but not in both.

not_replicated = set_1 ^ set_2
print(not_replicated)
# outputs {2, 3, 5, 6, 'bob'}

Conclusion

You have now seen how each structure stores data and which operations it supports. Lists and dictionaries are the workhorses for holding and reshaping data, tuples guarantee that certain values stay fixed, and sets make it quick to find duplicates and overlaps between datasets.
Understanding these basics will enable one to explore data science libraries such as pandas and NumPy that build on these ideas.

A quick refresher on python operators and conditionals.

Steve Mwangi — Sat, 19 Sep 2026 11:20:11 +0000

Operators are the symbols Python uses to work with values, whether that means doing math, comparing two things, or combining conditions.
I will go over the three main categories of operators: arithmetic, comparison, and logical operators.
Then show how comparison and logical operators drive conditional expressions (if, if-else, elif, and nested conditions), which let a program choose what to do based on whether a condition is True or False.

Python Operators

Python operators can be divided into three categories: arithmetic, comparison and logical operators.

Types of Operators

A Python operator can be either a unary or a binary operator.

A unary operator is an operator with only one operand.

A binary operator is an operator with two operands.

Python has left-handed binding i.e. the expression is solved from left to right, except for the exponent operator which uses right-handed binding.

1. Arithmetic Operators

Used to perform basic mathematical calculations.

Operator	Name	What does it do?
`+`	Addition	Adds values together.
`-`	Subtraction	Subtracts the right value from the left.
`*`	Multiplication	Multiplies operands together.
`/`	Division	Divides two operands and returns a float (decimal).
`//`	Floor division	Divides and rounds down to the nearest whole number.
`%`	Modulus	Returns the remainder of a division operation.
`**`	Exponent	Raises a number to the power of another.

This can be illustrated in the following python code:

value_1 = 10
value_2 = 20
value_3 = 3

print(value_1 + value_2)        # outputs 30
print(value_1 - value_2)        # outputs -10
print(value_1 * value_2)        # outputs 200
print(value_2 / value_1)        # outputs 2.0
print(value_1 // value_3)       # outputs 3
print(value_1 % value_3)        # outputs 1
print(value_1 ** value_3)       # outputs 1000

2. Comparison Operators

A comparison operator compares two values and gives you back True or False.

Operator	Meaning
`==`	Equal to
`!=`	Not equal to
`>`	Greater than
`<`	Less than
`>=`	Greater than or equal to
`<=`	Less than or equal to

2.1 Equal To (`==`) Operator

It is a binary operator that compares two values and returns True if both are equal, else it returns False.

value_1 = 10
value_2 = 20
value_3 = 10

print(value_1 == value_2)  # outputs False
print(value_1 == value_3)  # outputs True

2.2 Not Equal To (`!=`) Operator

It is a binary operator that compares two values and returns True if they are not equal, else it returns False if they are equal.

Comparison operators can be used to compare strings, in which case you must remember that Python is case sensitive.

str_1 = "cow"
str_2 = "Cow"
str_3 = "cow"

print(str_1 != str_2)   # outputs True
print(str_1 != str_3)   # outputs False

2.3 Greater Than (`>`) Operator

It is a binary operator that compares two values and returns True if the value on the left is greater than the value on the right, else it returns False.

value_1 = 10
value_2 = 20

print(value_1 > value_2)  # outputs False
print(value_2 > value_1)  # outputs True

2.4 Greater Than or Equal To (`>=`) Operator

It is a binary operator that compares two values and returns True if the value on the left is greater than or equal to the value on the right, else it returns False.

value_1 = 10
value_2 = 20
value_3 = 10

print(value_1 >= value_2)  # outputs False
print(value_2 >= value_1)  # outputs True
print(value_1 >= value_3)  # outputs True

2.5 Less Than (`<`) Operator

It is a binary operator that compares two values and returns True if the value on the left is less than the value on the right, else it returns False.

value_1 = 10
value_2 = 20

print(value_1 < value_2)  # outputs True
print(value_2 < value_1)  # outputs False

2.6 Less Than or Equal To (`<=`) Operator

It is a binary operator that compares two values and returns True if the value on the left is less than or equal to the value on the right, else it returns False.

value_1 = 10
value_2 = 20
value_3 = 10

print(value_1 <= value_2)  # outputs True
print(value_2 <= value_1)  # outputs False
print(value_1 <= value_3)  # outputs True

3. Logical Operators

3.1 And (Conjunction) Operator

It is a binary operator that outputs True if both conditions are met, else it outputs False.

value_1 = 10
value_2 = 20

print(value_1 == 10 and value_2 > 10)   # outputs True
print(value_1 == 10 and value_2 < 10)   # outputs False

3.2 Or (Disjunction) Operator

It is a binary operator that outputs False if both conditions are false, else it outputs True.

If either condition or both conditions are true, it outputs True.

value_1 = 10
value_2 = 20

print(value_1 == 10 or value_2 < 10)   # outputs True
print(value_1 != 10 or value_2 < 10)   # outputs False

3.3 Not (Negation) Operator

It is a unary operator that performs a logical negation.

It outputs False if the input was True and outputs True if the input was False.

value_1 = 10

print(not value_1 == 10)    # outputs False
print(not value_1 != 10)    # outputs True

Python Conditional Expressions

Comparison and logical operators are commonly used in conditional expressions where you need to execute a Python statement depending on the Boolean value of the condition during execution.

4.1 If Expressions

This expression performs the statement only once if the condition is True.

If the condition is True, the statement is executed, else the statement is skipped.

value_1 = 10

if value_1 == 10:
    print("Value 1 is equal to 10")   
  # The print is shown since the condition is met.

if value_1 == 20:
    print("Value 1 is equal to  20")   
 # This statement isn't printed since the conditions is not met.

4.2 If Else Expressions

This expression allows one to check a single condition and have two statements where one statement is performed if the condition is True, otherwise the other statement in the else clause is executed.

value_1 = 10

if value_1 == 10:
    print("Value 1 is equal to 10") 
    # The print statement is shown since the condition is met.
else:
    print("Value 1 is not equal to 10")
    # this statement is not printed since the first condition is met.

if value_1 > 10:
    print("Value 1 is greater than 10") 
    # The print statement is not shown since the condition is not met.
else:
    print("Value 1 is not greater than 10")
    # this statement is printed since the first condition is not met.

4.3 elif Expression

This is an expression where you can set multiple conditions in a sequence with a statement for each condition.

The expression is executed from the top to the bottom.

When the first True condition is encountered, the statement for that condition is executed and the remaining expression is skipped.

An else condition is placed at the bottom of the expression with a fallback statement to ensure that if no condition is met, the fallback statement in the else clause is executed, thus closing the expression.

To illustrate where no condition is met, thus resorting to the else clause, consider the following code:

value_1 = 10

if value_1 < 10:
    print("Value 1 is less than 10") 
    # The print statement is not shown since the condition is not met.
elif value_1 > 10 :
    print("Value 1 is greater than 10")
    # this statement isn't printed since the condition is not met.
elif value_1 == 20:
    print("Value 1 is equal to 20")
    # this statement isn't printed since the condition is not met.
else:
    print("Value 1 is not greater than 10, less than 10 or equal to 20")
    # this statement is printed since all the other conditions are not met.

To illustrate where a condition is met, consider the following example:

value_1 = 10

if value_1 < 10:
    print("Value 1 is less than 10") 
    # The print statement is not shown since the condition is not met.
elif value_1 == 10:
    print("Value 1 is equal to 10")
    # this statement is printed since the condition is met.
elif value_1 > 10 :
    print("Value 1 is greater than 10")
    # this statement is not printed since a condition above is met.
else:
    print("Value 1 is not greater than 10, less than 10 or equal to 20")
    # this statement is not printed since a condition above is met.

4.4 Nested Expressions

Python expressions can also be used within other expressions.

This enables one to have finer control of statements where multiple conditions have to be met before the statement is executed.

Consider the following example.

value_1 = 10
value_2 = 20

if value_1 == 10:
    if value_2 == 20:
        print("Value 1 is equal to 10 and value 2 is equal to 20")
        # this statement is printed since both conditions are met
    else:
        print("Value 1 is equal to 10 and value 2 is not equal to 20")
        # this statement is not prinetd since the condition above is already met.

if value_1 == 10:
    if value_2 > 30:
        print("Value 1 is equal to 10 and value 2 is greater than 30")
        # this statement is not printed since the nested condition is not met.
    else:
        print("Value 1 is equal to 10 and value 2 is not greater than 30")
        # this statement is printed since no condition above has been met.

Conclusion

Operators are the foundation of decision-making in Python. Arithmetic operators handle calculations, comparison operators test how values relate, and logical operators combine those tests into more complex conditions. When paired with if, else, and elif, they let a program respond differently depending on the data it receives. Becoming comfortable with these tools, enables you can move on to loops, functions, and other structures that build on the same logic thus building more complex programs.

Have fun in your python journey!

Python Basics for Data Analysis

Steve Mwangi — Fri, 18 Sep 2026 21:09:32 +0000

Performing data analysis requires a proper understanding of how the tool you are using works with data.
Python is a valuable tool for data analysis, but before you jump the gun and load up your datasets, let's explore how Python handles data.

Python Data Handling

Data in Python is usually stored in a variable. A variable is a named location reserved to store values in memory. In order to store any data in a variable, one must name it. A variable is defined using an equals (=) sign.

Syntax of a Variable

variable_name = data

Variable Naming Conventions

A variable name must follow these rules:

The name can be composed of uppercase, lowercase, and/or the underscore character (_).
The variable name must begin with a character.
The underscore is considered a letter when used in a variable name.
The variable name is case sensitive.
The variable name must not be a Python reserved keyword.

Reserved keywords have their meaning predefined and thus cannot be used to name variables. You can view the reserved keywords by running the following code:

import keyword
print("The list of keywords are : ")
print(keyword.kwlist)

Variables make code reusable since data is only assigned once, the data can be accessed anywhere in the code by calling the variable rather than rewriting it, and updating the variable updates the data in all instances where it has been used in the code.

A Python variable can only hold data of one type at a time. Thus, it is important to have a quick overview of Python data types.

Python Data Types

Category	Data Types	Description
Text Type	`str`	Used to store data in text format.
Numeric Types	`int`, `float`, `complex`	Used to store numeric values.
Sequence Types	`list`, `tuple`, `range`	Used to store an ordered collection of items, which can be accessed by indexing.
Mapping Type	`dict`	Used to store data in key-value pairs where each key in a dictionary must be unique. Values are accessed through the key.
Set Types	`set`, `frozenset`	Used to store data in an unordered collection where each data item must be unique.
Boolean Type	`bool`	Used to store boolean values `True` or `False`.
None Type	`NoneType`	Used to assign a variable with no value assigned to it.

Data can be assigned to a variable manually in the code, or more usually, through user input. This is done using the input() function.

Input() function

The input() function collects the user input and directly assigns that input to a variable.

Syntax of the input() Function

variable_name = input("input message")

For example:

name = input("Enter your name : ")

In this instance, the user will be prompted to enter their name, and their input is then assigned to the variable name.

If we need to also know the user's age, we can create another variable and assign it the user's input using the input() function.

age = input("Enter your age : ")

However, at this point it is important to note that the input() method, by default, collects data in the string data type. Thus, even if our user enters numeric data for their age, it will be stored as a string in the age variable.

You can confirm this by running the following code to check the data type of our two variables using the type() function.

print(type(name))
print(type(age))

You should get the output:

class 'str'

Indicating both variables are of the data type string.

Do not despair, this can easily be mitigated using a Python technique called type casting.

Type casting

Type casting is the process of converting a Python variable from one data type to another. This can be done when the variable is being defined, or at any point once a variable is defined.

I can illustrate this by changing the data type of our age variable to an integer as follows:

age = input("Enter your age : ")
age = int(age)
print(type(age))

Now when we check the data type of the age variable, we get the following output:

class 'int'

This shows that we have successfully changed the data type of our variable.

However, it is more efficient to perform type casting while defining the variable. This is done by wrapping the input() function in the data type function we want our variable to store.

In our case, we can assign the age variable as an integer as follows:

age = int(input("Please enter your age : "))
print(type(age))

Data Output in Python

In order to view the data stored in our variables, it is important to understand the print() function. I have already provided a few examples on what it is used for, when viewing the data types of our variables and viewing the Python reserved keywords, but let's delve into its functionality a bit further.

The print() function displays data passed to it to the standard output of the device, usually the console.

This data can be a variable, where it will display the data in the variable. For example, if we run the following code:

name = input("Enter your name: ")
age = int(input("Enter your age: "))

print(name)
print(age)

Once the user enters the requested data, it is printed to the console.

We can combine the two print statements into one to make a more cohesive output using f-strings.

An f-string (formatted string literal) is a Python functionality that enables one to embed variables and expressions directly inside a string by prefixing the string with f and using curly braces.

We can rewrite our code as follows:

name = input("Enter your name: ")
age = int(input("Enter your age: "))

print(f"Hello {name}! You are {age} years old.")

In this case, we have a single output that displays data from both of our variables.

Conclusion

Mastering the foundational building blocks of handling data in Python, which are essential first steps before doing any data analysis.

Almost every analysis task starts with getting data into the right shape.
This will often require understanding variables and their data types to prevents subtle bugs (e.g., trying to do math on a string). Type casting lets you turn messy, string-based input into usable numeric data. And clear output formatting (via print()/f-strings) is how you inspect, validate, and communicate results as you build up more complex analysis code.

The fundamentals of SQL Subqueries and Common Table Expressions (CTEs)

Steve Mwangi — Wed, 09 Sep 2026 10:09:02 +0000

As you work on data with SQL you will encounter situations where you are trying to achieve very specific results depending on the conditions of your query.

For instance given a study group schema, with members table and quiz results table, and you are required to figure out the members who scored above the average for all the tests.

To solve this you will end up with a series of queries, or a complex query, where you need to filter out the members results , find the average score for the tests and finally filter out only the members who scored above the average found.

While this may be achievable for a small dataset or static data, what will you do when you are working with a large dataset with thousands of records or a dynamic dataset with records constantly being added?

Well fear no more, that's where SQL subqueries and Common Table Expressions (CTEs) come to the rescue.

These two methods provide a way to use SQL queries within your main SQL query but they differ in structure, reusability, and capability.

Lets explore them individually.

SUBQUERY

A subquery in SQL is a query nested inside another SQL query, it is written inline within the SELECT, FROM, or WHERE clauses of the main query.

It allows complex filtering, aggregation and data manipulation by using the result of one query inside another and are concise thus used for simple, single-use filters or scalar values.

syntax of a subquery

SELECT Columns
From table
outer query functionality 
operator clause (   SELECT something FROM table
              subquery functionality);

To make sense of this syntax I will refer to the example I had mentioned earlier. We will use the outer query to go through the members test results then use subquery to find the average of the same and finally compare them to get our desired result set.

The operator clause is what determines how our two queries will engage with each other. The operator is significant to note since it has to work with the output of your subquery.

You can use SQL comparison operators or logical operators in instances where you want to filter based on a single calculated value derived from the subquery.

In our case we will use a greater than (>) operator since we want to compare all the members results in the tests to the average test score, which is a single result.

Our SQL becomes:

select 
    score_id,
    member_id,
    score 
from study_group.quiz_scores
where score > (select avg(score) from study_group.quiz_scores)
order by score asc;

The line where score > (select avg(score) from study_group.quiz_scores) is where the magic of subqueries is happening.
We are comparing every score for each member to a dynamically calculated score average.

This ensures that regardless of whatever data manipulation occurs in our tables we are always comparing our scores to the correct average thus no need to figure out the average score beforehand.

We also have another instance where our subquery returns a set of results in the form of a temporary table. In these cases, we have to give the table an alias that will enable us to reference it while performing further queries.

Note: The result is temporary as it only exists while the query is running.

Consider the same study group schema, you are now told to calculate each members average score then show those who have a average score above 70.

We can use a subquery to calculate the average score for each member, this will give us a temporary table having the member_id and their average score, .We can then compare this to our threshold of 70 to solve our problem.

Our SQL query becomes:

select 
    member_id,
    average_member_score
from (select 
            qs.member_id,
            avg(score) as average_member_score
        from study_group.quiz_scores qs 
        group by qs.member_id
        ) as member_averages
where average_member_score > 70;

As you can see we are able to reference the average_member_score in our main query from our subquery whose result is stored in a temporary table we name member_averages.

Subqueries are incredible when you only need a result once in a query as they cannot be reused and have to be duplicated if the same result is needed multiple times in a query. This makes your query harder to read and maintain.

Common Table expressions (CTEs) solve this issue.

CTE (Common Table Expressions)

A CTE defines a temporary result set that you can reference, possibly multiple times within the scope of a SQL statement. CTEs organize code into logical, sequential stages enabling reusability and recursion.

A CTE is used mainly in a SELECT statement.

Syntax of CTE

WITH cte_name AS (
    SELECT column1, column2
    FROM table_name
    condition
)
SELECT * 
FROM cte_name
main query functionality;

A CTE is defined at the start of the query using the WITH keyword followed by the name for the CTE.

The CTE name is followed by a set of parenthesis () that contain the CTEs functionality, closing the CTE, we now write the main query functionality where we are able to pull from the CTE as many times as we need; since CTEs are reusable within a query statement.

This makes complex, multi-step logic significantly easier to read and maintain.

For example we can redo the question of finding the members who scored above the score average using a CTE as follows:

with group_avg as ( -- namimg the CTE
    select avg(score) as avg_score      -- CTE functionality
    from study_group.quiz_scores q
) 
select score_id, member_id,score 
from study_group.quiz_scores q 
cross join group_avg                    -- calling CTE using a cross join
where q.score > avg_score
order by score asc;

In this case we create a CTE named group_avg and we calculate the score average inside the CTE. We can then use a cross join to call the CTE and compare the scores to the average score found in the CTE.

While the query achieves the exact same functionality as when using a subquery, our query is much more readable and easier to maintain incase changes are made.

We can also write multiple CTEs in sequence to handle complex queries, in such cases the CTEs are separated by a comma (,).

syntax for multiple CTEs

WITH cte_1 AS (
    SELECT columns
    FROM table_1
    cte_1 functionality
), cte_2 AS (
    SELECT columns
    FROM table_2
    cte_2 functionality
)
SELECT columns
FROM cte_1 ct1
INNER JOIN cte_2 ct2
ON ct1.column_a = ct2.column;

For instance when asked to find members whose personal average is higher than the group average we can write the following query:

with group_avg as  ( 
    select avg(score) as group_avg_score
    from study_group.quiz_scores
),
member_avg as (
    select member_id, avg(score) as member_avg_score
    from study_group.quiz_scores
    group by member_id
)
select m.member_id, m.member_avg_score 
from member_avg m
cross join group_avg g
where m.member_avg_score > g.group_avg_score
order by m.member_avg_score;

Limitations of CTEs

While useful, CTEs come with a few practical constraints.

Temporary: A CTE works only while the query runs, then it disappears.
Performance: On very large data, CTEs can be slower if reused many times.

Summary

Feature	CTE	Subquery
Definition	WITH clause (before main query)	Nested inside SELECT, FROM, WHERE
Readability	High (modular, named steps)	Low when deeply nested
Reusability	Can be referenced multiple times	Used once per inline location
Recursion	Supported	Not supported
Best Use Case	Complex logic, recursion, repeated logic	Simple filters, scalar values, single-use

Understanding SQL joins.

Steve Mwangi — Tue, 08 Sep 2026 10:24:38 +0000

The SQL JOIN Clause

The JOIN clause is used to combine rows from two or more tables, based on a related column between them, i.e., a primary key and foreign key relationship between the two tables.

JOIN Syntax

SQL joins are typically in the format:

SELECT STATEMENT
FROM TABLE A
JOIN CLAUSE TABLE A
ON clause;

The select statement is used to choose the exact columns one wants returned from each table being joined.
One can also can use select * to return all columns from both tables.

The join clause is where you specify the exact join operation you will be performing on the two tables.

The on condition is where the primary key in table A is linked to the foreign key in table B. This relation is what enables one to perform a join operation between two tables.

Types of Joins in SQL

(INNER) JOIN
LEFT (OUTER) JOIN
RIGHT (OUTER) JOIN
FULL (OUTER) JOIN

In order to demonstrate the different joins, I will create a shop schema called duka with three tables: products, customers and orders.
The link to the SQL code to create the schema is available here.

1. SQL INNER JOIN

The INNER JOIN returns only rows that have matching values in both tables.

Inner join syntax

SELECT columns
FROM table_a a
INNER JOIN table_b b
ON a.key = b.key

We can use inner join in our schema to figure out which customers placed which order.

In this case we will need the following columns:

customers name (name)
product id (product_id)
quantity (quantity)

This data is found in the tables:

duka_customers
duka_orders

The common column between the two tables is:

customer_id

Thus we can use inner join to return only the instances where a customer appears in the orders table, as follows:

select c.name, o.product_id, o.quantity
from duka.duka_customers c
inner join duka.duka_orders o
on c.customer_id = o.customer_id;

We get the following output showing each customers purchases:

One is not limited to one join clause, as long as a primary key - foreign key relationship exists between tables in a schema, you can use as many joins as needed.

We can further use a second inner join to pull the product name, to replace the product id, from the products table by joining it to the orders table.

The SQL query thus is modified to:

select c.name, p.product_name, o.quantity
from duka.duka_orders o
inner join duka.duka_customers c on c.customer_id = o.customer_id
inner join duka.duka_products p on p.product_id = o.product_id;

Our final output is a much more readable table showing the customer name, the product and the quantity of items they purchased.

2. LEFT JOIN

The LEFT JOIN returns all rows from the left table (table A), and only the matched rows from the right table (table B).
If there is no match in the right table, the result for the columns from the right table will be NULL.

Left join syntax

SELECT columns
FROM left_table a
LEFT JOIN right_table b 
ON a.key = b.key

We can use left join to figure out which products have never been ordered.

In this case we need the columns :

product name From the tables :
products table
orders table The common column is :
product_id

The SQL query is written as:

select p.product_name
from duka.duka_products p
left join duka.duka_orders o
on p.product_id = o.product_id
where o.order_id is null;

We use the condition where o.order_id is null to filter only the rows where orders table is returning a null, showing no orders for that product exist.

Our output is therefore:

3. SQL RIGHT JOIN

The RIGHT JOIN returns all rows from the right table (table A), and only the matched rows from the left table (table B).
If there is no match in the left table, the result for the columns from the left table will be NULL.

Right join syntax

SELECT columns
FROM left_table a
RIGHT JOIN right_table b
ON a.key = b.key;

We can use a right join to figure out which products have never been ordered.
This time the SQL query will be:

select dp.product_name
from duka.duka_orders t  
right join duka.duka_products dp
on t.product_id = dp.product_id
where t.order_id is null;

This time the order in which we have called the tables is swapped, but the output remains the same.

4. SQL FULL JOIN

The FULL JOIN returns all rows when there is a match in either the left or right table.
If a row in the left table has no match in the right table, the result set includes the left row's data and NULL values for all columns of the right table.
If a row in the right table has no match in the left table, the result set includes the right row's data and NULL values for all columns of the left table.

Full join syntax

SELECT columns
FROM table A 
FULL JOIN table B
ON table_a.key= table_b.key
WHERE condition;

We can once gain use a full join to show the products that have never been ordered.

we will write the SQL query to illustrate this as follows:

select t.order_id, t.product_id, dp.product_name, dp.product_category, dp.stock_level, dp.supplier 
from duka.duka_orders t  
full join duka.duka_products dp
on t.product_id = dp.product_id;

We expect to see the order_id and product_id for products that were ordered since we are getting that data from the orders table. For products that were never ordered, those two columns will remain be null.

We get the following output:

Conclusion

summary

(INNER) JOIN: Returns only rows that have matching values in both tables.
LEFT (OUTER) JOIN: Returns all rows from the left table, and only the matched rows from the right table.
RIGHT (OUTER) JOIN: Returns all rows from the right table, and only the matched rows from the left table.
FULL (OUTER) JOIN: Returns all rows when there is a match in either the left or right table.

SQL joins can be visualized using the this image below, sourced from Acquity Training, which succinctly summarizes all the operation that we have performed on our duka database..

Understanding SQL joins is a key tool that one must master. These simple operations form the skills that one needs in order to work on larger, complex databases and develop as a developer and data analyst.
Good luck on your journey.

A practical journey into SQL

Steve Mwangi — Mon, 20 Jul 2026 10:00:57 +0000

What is SQL (Structured Query Language)

SQL is the standard programming language used to manage and query data in relational database management systems (RDBMS).

SQL enables users to: retrieve data, insert new data, update existing data, delete data, create database structures, modify database structures, control access to data and manage transactions.

Users utilize SQL through commands.

SQL commands

SQL commands are grouped into 5 categories

DDL (Data Definition Language):
Used to manage database structure.

Key commands include:
CREATE used to create tables/databases,
ALTER used to modify tables,
DROP used to delete tables,
TRUNCATE used to remove all data but keeps structure.

DML (Data Manipulation Language):
Used to manage data within tables.

Key commands include:
INSERT used to add data into rows,
UPDATE used to make edits to existing data,
DELETE used to remove data from the database.

DQL (Data Query Language):
Used to retrieve data.

The primary command is SELECT, often used with clauses like
WHERE used to apply filters,
GROUP BY used to aggregate data,
ORDER BY used to sort data,
JOIN used to combines tables.

DCL (Data Control Language):
Used to control access permissions.

Key commands include:
GRANT used to give users data access permissions,
REVOKE used to remove data access permissions.

TCL (Transaction Control Language):
Used to manage transactions.

Key commands include:
COMMIT used to saves changes made to the data,
ROLLBACK used to undo changes made to the data,
SAVEPOINT used to mark a point for rollback.

SQL data types

When using SQL, each column can only hold one type of data thus it is important to define the columns' data type.

Data types define the kind of data that can be stored in each column of a table.

Data types are declared when creating a table, where each columns data type has to be set.

They include
Numeric data types

Data Type	Description
SMALLINT	Stores small whole numbers
INTEGER / INT	Stores standard whole numbers
BIGINT	Stores very large whole numbers
SERIAL	Auto-incrementing integer
BIGSERIAL	Auto-incrementing large integer

Character or string data types

Data Type	Description
CHAR(n)	Fixed-length text
VARCHAR(n)	Variable-length text with a maximum limit
TEXT	Variable-length text with no strict short limit

Date and time data types

Data Type	Description
DATE	Stores date only
TIME	Stores time only
TIMESTAMP	Stores date and time
TIMESTAMPTZ	Stores date and time with time zone

Boolean data type
Used to store True or False values.

Importance of SQL data types:
Using the correct data type enables one to:

Ensure correct data entry
Prevent invalid values
Improve storage efficiency
Improve query performance
Support accurate calculations
Maintain consistency across the database

SQL constraints

A SQL constraints are rules applied to database columns or tables to enforce data integrity, accuracy, and reliability by limiting the type of data that can be inserted, updated, or deleted.

SQL constraints are set for the column during table creation along with the data type.

Common SQL constraints include:

Constraint	Description
NOT NULL	Ensures a column cannot have a `NULL` (empty) value.
UNIQUE	Ensures all values in a column are distinct, preventing duplicates.
PRIMARY KEY	Uniquely identifies each row in a table.
FOREIGN KEY	Establishes a link between data in two tables by referencing a primary key in another table, ensuring referential integrity.
CHECK	Validates that values in a column satisfy a specific condition.
DEFAULT	Sets a default value for a column if no value is specified during insertion.

Bringing it all together:

In order to illustrate how SQL commands, data types and constraints merge together to work with data we will use SQL to create a database for a school and perform some SQL queries.

Greenwood Academy

SECTION A - Building the Database (DDL)

The project starts with creating the database schema named greenwood-academy that will contain all our tables and setting the search path to our schema to ensure all our commands are executed in it.

Creating the schema

create schema greenwood_academy;

set search_path to greenwood_academy;

Creating the tables

Now we create our students, subjects and exam results tables while setting the data types and the needed constraints for each column.

Creating students table:

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)
    );

Creating subjects table:

create table greenwood_academy.subjects(
    subject_id      INT          primary key,
    subject_name    VARCHAR(100) not null unique,
    department      VARCHAR(50),
    teacher_name    VARCHAR(100),
    credits         INT
    );

Creating exam results table:

create table greenwood_academy.exam_results(
    result_id INT primary key,
    student_id INT not null references greenwood_academy.students(student_id),
    subject_id INT not null references greenwood_academy.subjects (subject_id),
    marks INT not null,
    exam_date DATE,
    grade VARCHAR(2)
);

Modifying the table structure

We can use some DDL commands to alter the tables after creation, such as renaming the credits column in the subjects table to credit_hours.

alter table greenwood_academy.students 
add column phone_number VARCHAR(20);

SECTION B - Filling the Database (DML)

Now that we have our tables with the columns set, we need to insert data into the tables.

Inserting data into the tables

Inserting data into 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 data 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 data 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')
;

Modifying table contents
We can use SQL commands to modify data in our tables, such as updating the location of student Esther Akinyi, whose student ID is 5, from Nakuru to Nairobi.

update greenwood_academy.students
set city = 'Nairobi'
where student_id = 5;

SECTION C - Querying the Data

Now that we have all the data we need, we can proceed to utilize SQL to query and pull insights from the data through filters.

Such as:
Using a query to find all the students who are in form 4.

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

Using a query to find all the exam results where marks are greater than or equal to 70.

select * 
from greenwood_academy.exam_results er 
where er.marks >= 70
order by marks asc;

SECTION D - Range, Membership & Search Operators

We can further use SQL operators to filter data based on certain conditions.
Such as:
Using a query to find all exam results where marks are between 50 and 80 (inclusive).

select er.result_id, er.marks, er.grade 
from greenwood_academy.exam_results er 
where er.marks between 50 and 80;

Using a query to find all students who live in Nairobi, Mombasa, or Kisumu

select s.first_name, s.last_name, s.city  
from greenwood_academy.students s 
where city in ('Nairobi', 'Mombasa', 'Kisumu')
order by s.student_id ;

SECTION E - COUNT

We can use SQL to count based on certain criteria.
Such as:
Using a query to find out how many students are currently in Form 3.

select count (*) as form_three_students_count
from greenwood_academy.students s 
where class = 'Form 3';

SECTION F - CASE WHEN

We can futher use SQL to create labels for data based on certain conditions.
Such as:
Using a query to label each exam result with a grade description:

select subject_id, marks, grade ,
case 
    when marks < 40 then 'Fail'
    when marks between 40 and 60 then 'Pass'
    when marks between 61 and 79 then 'Merit'
    else 'Distinction'
end
as performance
from greenwood_academy.exam_results er 
order by marks asc;

Conclusion

Understanding how SQL works is important and working with the basics provides key skills in anyone who wants to work with data. The Greenwood academy project provides a simple example on the basics on SQL and explores the SQL commands, data types and constraints illustrating how they are used to create a relational database.

I have provided a repository for the Greenwood Academy project on my GitHub page here, where further SQL Queries have been performed with a readme file that outlines all the queries in the project.

How to connect to a local vs online PostgreSQL database. A quick guide.

Steve Mwangi — Sun, 05 Jul 2026 15:01:00 +0000

In this article I will provide a step by step guide on how to connect to a local PostgreSQL database and a PostgreSQL database hosted on an online service, in particular I will be using Aiven to host and connect to the online database.
I will use powerBI to illustrate how to connect and pull data from both the local and online database.

This guide presumes one is familiar with PostgreSQL, DBeaver, powerBI and Aiven. You must have PostgreSQL, DBeaver and powerBI installed and set up properly in your device, as well as a running Aiven service.

Connecting to local database

Connecting to the local PostgreSQL database:

step 1
Start by creating a new database connection through DBeaver and select PostgreSQL from the dropdown menu.

step 2
Enter the connection details prompted ensuring you enter the correct username and password that you set up when installing PostgreSQL in your device.
For a local PostgreSQL database the host is localhost and the port number 5432.

Ensure you test your connection before you finish setting up your connection.

step 3
With the connection established, it it time to create a new schema in your database.
To view the schemas on your database open through:

postgres *(localhost:5432)> Databases > postgres > Schemas

When data is imported into a postgres database with no schema specified, the data will be pushed to the default public schema.
In order to avoid this and to ensure our data is properly arranged, we will create a new schema where we will store our data.
Right click on Schemas and select create new schema.

step 4
To import data into our schema, right click on the created schema and select import data.

Browse through your local files, and select the file you want to import into your local database, then click proceed until the importing process completes.

Connecting powerBI to local PostgreSQL database

Now that we have created a new local database connection and imported data into it, we can open up powerBI and pull the data in our database into powerBI where we can clean, model and perform analysis on it.

step 5
Start with opening a blank report on powerBI and select get data from the HOME ribbon.

Search for PostgreSQL in the data source, and select PostgreSQL database.

step 6
Fill in the connection details in the connect to data source page. Ensure these details match with the details you used when making the database connection through DBeaver.

At this point you should have successfully connected your local database to powerBI and a powerBI should display a list of all the schemas in your local database, from where you can select the file you want to work on and load it into powerBI.

Connecting to an online database.

To connect to a database hosted on an online service, I will start by guiding on how to create a PostgreSQL database connection to an existing Aiven service.
For any of the steps from this point to work, you must note that your Aiven service must be powered on and running.

step A
Begin with creating a connection to the running Aiven service through DBeaver. This process is similar to Step 2 when connecting to a local database, however, it differs in that you must use the connection details provided by your Aiven service.

Copy and paste the connection details from your Aiven service into the required slots, then test your connection to ensure it functions properly before finishing setting up the connection.

step B
With your connection established, you can use the steps 3 and step 4 shown above when discussing working with the local database to create a new schema and import data into your online database.

step C
Before we attempt to connect powerBI to our database, we must ensure we install our certificate, to ensure our connection works properly.
Go to your Aiven service overview and download the CA certificate provided.

Go to the search bar on your windows task bar and search for manage user certificates and open it.

Open the Trusted Root Certification Authorities folders, and right click on the Certificates folder. Click on the ALL Tasks on the pop up then click on Import.

Click next, then browse. Change the file type to all types on the file explore and select the CA certificate you had downloaded, click next until the process completes.

step D
Now its time to access our data through powerBI.
Similar to step 5 open a blank report in powerBI and select get data from the HOME ribbon.
Search for PostgreSQL in the data source, and select PostgreSQL database.

step E
This time you will insert the connection details provided on your Aiven service when setting up the connection settings.

NOTE:
The server detail is filled by inserting the host provided in Aiven, adding a full colon, then adding the port number provided by Aiven.

HOST:port number

Failure to setup the host in this manner leads to a connection failure.
Ensure not to add any blank spaces in the server details.

AT this point powerBI should list the schemas in your Aiven database, you can now select a file to import into powerBI.

The fundamentals of data modelling through Power BI

Steve Mwangi — Thu, 02 Jul 2026 15:53:52 +0000

Power BI is a suite of business analytics and data visualization tools developed by Microsoft. It allows you to connect to hundreds of data sources, clean and model raw data, and transform it into interactive, shareable dashboards and reports.

Data Modelling

A data model is a collection of tables, relationships and calculations that represent the underlying structure of the data. The data model defines how data is stored, how different data entities relate to each other and how calculations are performed.

Reasons for data modelling

Supports Data Exploration - Data modeling enables users to create hierarchies and drill-down paths, which support efficient data exploration.
Affects Performance - How a data model is designed directly impacts the speed and efficiency of data retrieval.
Promotes More Accurate Reports - When a data model is designed correctly, it can guarantee the accuracy, consistency, and dependability of the data used in reports.
Easier Future Maintainability - Creating reusable components, improving documentation, and standardizing data can also ensure that reports are easy to maintain and update over time.

There are three common data models:

1. FLAT TABLE
This is a data model where all the all the data is contained in a single table.
This is the most basic data model, and only recommended for one-off analysis.
Using a flat table can result in functional issues especially when the data scales in volume.

2. STAR SCHEMA
The star schema is a well-established data modeling technique. This approach requires breaking down the data into a facts table and related dimension tables. All of the dimension tables must have a direct relationship to the fact table.

Facts table is the table that stores the transactions that occurred in the data set.
It answers the question "What happened?"
Dimension table is a table that contains explanatory data of the information being presented through the transactions.

The facts table and dimension tables are linked through primary and foreign keys.
A primary key is a unique, non-blank column that identifies the rows in a table, whereas a foreign key is a column that references the primary key of a different table.

The star schema is considered better than a flat table since:

It reduces repetition of data therefore less resource intensive.
Clearly outlines relationships among the data entities thus easier to understand and perform operations on the data.

3. SNOWFLAKE SCHEMA
This model is a further breakdown of the star schema where not all dimension tables have a direct connection to the facts table.
The dimension tables where data is further repeated are broken down further into logical sub-dimensions.

I have mentioned that the facts and dimension tables are connected and it is now time to explore what these connections are and how they work.

HOW RELATIONSHIPS WORK IN POWER BI

Relationship is the connection between tables through common columns, i.e. foreign keys.
Relationships in Power BI are illustrated through cardinality i.e. how rows in one table relate to rows in another table.
These are:
ONE TO ONE (1:1)
This is where column in one table has only one instance of a value and the other related table has only one instance of the value, such that each row in a table matches exactly one row in the other table.

ONE TO MANY (1:*)
This is the most common relationship. It occurs when one value in the first table can be related to many records in the second table, such that one value in a dimension table matches to multiple values in the facts table.

MANY TO MANY (:)
This relationship occurs when multiple records in the first table can be related to many records in the second table. This relationship indicates the data model needs to be restructured properly.

Cross-filter direction
Cross-filter direction determines how filters move between tables.
Single direction means filters flow from the dimension table to the fact table.
Both directions allow filters to flow both ways between tables.
Single direction is recommended in most models because it is:

easier to understand
less error-prone
aligned with star schema logic

Bidirectional filtering can be useful in specific scenarios, but it also increases the risk of ambiguous filter paths and unexpected results.

Active vs inactive relationships
Power BI allows multiple relationships between the same tables, but only one can be active at a time.
Active relationships are used automatically by visuals whereas inactive relationships exist in the model but are ignored unless explicitly activated in a measure.

MERGE QUERIES

Data modelling also involves operating on the structure of the tables in order to end up with an efficient model. This can be achieved through merge queries.

A Power BI merge query is an operation that joins two existing tables together based on matching values from one or more columns.

Types of merge queries

INNER JOIN
This operation returns only the matching values form both tables based on the foreign key.

FULL JOIN/FULL OUTER JOIN
This operation returns all the rows from both tables and fills null values where there is no match.

LEFT OUTER JOIN
This operation returns all rows from the table placed on the left and returns only the matching rows from the table placed on the right.

RIGHT OUTER JOIN
This operation returns all the rows from the table placed on the right and returns only the matching rows from the table placed on the left.

LEFT ANTI JOIN
This operation brings in only the row form the table placed on the left that do not have any matching rows from the table placed on the right.

RIGHT ANTI JOIN
This operation brings in only the row form the table placed on the right that do not have any matching rows from the table placed on the left.

CONCLUSION

Data modelling is the foundation for any data analysis. A proper model simplifies the data into an easily understandable structure that can be used to perform calculations, perform accurate analysis and generate reports.
One has to master the foundations such as differences between data models, how to select the proper model for a dataset, how cardinality works and how relationships can be best used within a model and how to use merge queries in order to properly utilize data.

How Excel is Used in Real-World Data Analysis

Steve Mwangi — Sat, 06 Jun 2026 15:06:41 +0000

I have begun my journey as a beginner in the data analysis field by learning excel. I have previously used excel to store data but never really considered put my mind on how excel is used in the broader perspective of data analysis and the process it takes to collect, organize, validate and process data to extract information.

This journey began with a simple question.

What is Excel?

Excel is an electronic spreadsheet software created by Microsoft. A spreadsheet is an application software used to organize, analyze and present data in an intersecting two-dimensional grid of rows and columns.

Excel has become an industry standard for handling data as it provides a variety of tools and features for data collection, organization, visualization and analysis.

Excel features

Excel provides for one to operate on data through functions/formulas.
A function is a special routine built into the worksheet that enable one to perform complex tasks in a standardized manner.

Form of an excel function

An excel function is composed of four parts:

Equal sign (=) - Used to initialize the formula.
Function name - It is a descriptive term that identifies the operation to be performed.
Parenthesis () - used to contain the function arguments.
Arguments - Outlines the range of cells the formula will act on and the conditions to be imposed.

  =Function name(arguments)

Excel functions can be classified into:

Statistical functions
Used to perform statistical computations such as mean, median and mode.

=COUNT(Range)
=COUNTIF(Range, "criteria")
=AVERAGE(Range)
=AVERAGEIF(Criteria range, "criteria", Average range)

Aggregate functions
Used to perform arithmetic, conditional sums and products, exponents and logarithms operations.

=SUM(Sum cell range)
=PRODUCT(Product cell range)
=SQRT(Square root cell range)

Text functions
Used to perform operations on text data.

=LOWER(Range)
=UPPER(Range)
=CLEAN(Range)

Excel as a data analytic tool

After getting a proper understanding of what excel is and how to use it, the next step is to learn how we use these tools to perform data analysis. However, we must begin with knowing what data analysis is.

Data analysis is the process of inspecting, cleaning, transforming and modelling data with the goal of discovering useful information, informing conclusions and supporting decision making in an organization.

Excel is utilized to achieve this in a variety of sectors such as the healthcare, financial, marketing environment, etc. to perform various data analytic operations such as:

Descriptive Analysis
This is analysis that is done to figure out what happened. This is used in the health sector in situations where it is necessary to track the infection rates of communicable diseases in a region region, such as during the covid-19 pandemic where infection records were tracked on a daily basis.

Diagnostic analysis
This is the use of excel functions to go through data to determine why something occurred. This is used in the business sector to outline changes in the business operations, such as changes in the volume of sales, through excel data presentation tools such as charts and graphs.

Predictive Analysis
This involves the use of excel to form projections about the future based on the data. This is used in financial sector to produce possible future investment predictions in a market that is being considered. This is achieved in excel through What-If analysis and Forecast sheets.

Personal reflection

I have spent a week going through excel and learning on how to use it as more than a spreadsheet to hold data. This has led me to discovering the various ways excel provides to engage with data through formulas, sorts, merges and filters to view and produce information for my needs. I have learnt how to validate and clean data so as to ensure data meets the analytic standards.

I believe understanding the use of excel as a data analysis tool is a valuable skill for me as I continue my journey as a data analyst and will provide me with a solid foundation to work with other data tools and platforms in the future.

DEV Community: Steve Mwangi

lets explore data Structures in Python

Introduction

1. Lists

1.1 Creating a list

1.2 Storing data in a list

1.2.1 .append() function

1.2.2 .insert() function

1.2.3 .extend() function

1.3 Accessing data in a list

1.3.1 Indexing

1.3.2 Slicing

1.4 Removing data from a list

1.4.1 .remove() function

1.4.2 .pop() function

2. Tuples

2.1 Accessing data in a tuple

2.2 Modifying a tuple

2.3 Unpacking a tuple

3. Dictionaries

3.1 Syntax of a dictionary

3.2 Adding data to a dictionary

3.3 Accessing data in a dictionary

3.4 Removing data from a dictionary

3.5 Iteration over a dictionary

4. Sets

4.1 Defining a set

4.2 Adding data to a set

4.3 Removing data from a set

4.3.1 .remove() function

4.3.2 .discard() function

4.4 Set operations

4.4.1 Intersection (&)

4.4.2 Union (|)

4.4.3 Difference

4.4.4 Symmetric difference

Conclusion

A quick refresher on python operators and conditionals.

Python Operators

Types of Operators

1. Arithmetic Operators

2. Comparison Operators

2.1 Equal To (==) Operator

2.2 Not Equal To (!=) Operator

2.3 Greater Than (>) Operator

2.4 Greater Than or Equal To (>=) Operator

2.5 Less Than (<) Operator

2.6 Less Than or Equal To (<=) Operator

3. Logical Operators

3.1 And (Conjunction) Operator

3.2 Or (Disjunction) Operator

3.3 Not (Negation) Operator

Python Conditional Expressions

4.1 If Expressions

4.2 If Else Expressions

4.3 elif Expression

4.4 Nested Expressions

Conclusion

Python Basics for Data Analysis

Python Data Handling

Syntax of a Variable

Variable Naming Conventions

Python Data Types

Input() function

Type casting

Data Output in Python

Conclusion

The fundamentals of SQL Subqueries and Common Table Expressions (CTEs)

SUBQUERY

CTE (Common Table Expressions)

Summary

Understanding SQL joins.

The SQL JOIN Clause

Types of Joins in SQL

1. SQL INNER JOIN

2. LEFT JOIN

3. SQL RIGHT JOIN

4. SQL FULL JOIN

Conclusion

A practical journey into SQL

1.2.1 `.append()` function

1.2.2 `.insert()` function

1.2.3 `.extend()` function

1.4.1 `.remove()` function

1.4.2 `.pop()` function

4.3.1 `.remove()` function

4.3.2 `.discard()` function

2.1 Equal To (`==`) Operator

2.2 Not Equal To (`!=`) Operator

2.3 Greater Than (`>`) Operator

2.4 Greater Than or Equal To (`>=`) Operator

2.5 Less Than (`<`) Operator

2.6 Less Than or Equal To (`<=`) Operator