DEV Community: Wangeci Ndovu

SQL Joins and Window Functions: The Difference Between Combining Data and Analyzing It

Wangeci Ndovu — Wed, 04 Mar 2026 02:27:32 +0000

Let us talk about joins and windows functions in sql

Joins combine tables

Window Functions analyze data without collapsing it

Many beginners confuse the two. Let’s break them down properly step y step with real examples, clear explanations, and practical queries

Part 1- SQL Joins — Combining Data Across Tables

Imagine you have two tables:

customers

customer_id | first_name|second_name
1           | Alice     |Johnson
2           | Bob       |Njagi

orders

order_id| customer_id |Order_amount
1       | 1           | 250
2       | 1           | 300
3       | 2           | 150

If you want to see who made which order, you need a ##JOIN##.

What is a JOIN?

A JOIN allows you to combine rows from two or more tables based on a related column.

In simple terms:

A JOIN connects tables using a common key.

INNER JOIN

Returns only matching rows.

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

Result:

Alice | 1 | 250
Alice | 2 | 300
Bob   | 3 | 150

If there’s no match, the row is excluded

LEFT JOIN

Returns all rows from the left table, even if there’s no match.

SELECT customer_name,
order_id,orders_amount,
FROM customers,
LEFT JOIN orders,
ON customer_id = customer_id;

If a customer has no orders, they still appear with NULL values for order columns.

RIGHT JOIN

Opposite of LEFT JOIN returns all rows from the right table.

FULL OUTER JOIN

Returns all rows from both tables matched where possible.

Key Insight About Joins

Joins increase columns.

They bring data from multiple tables into a single result set.

They do NOT calculate ranking, running totals, or row by row analytics.

That’s where Window Functions come in.

Part 2- Window Functions Analyzing Without Collapsing Data

Window functions are different.

They:

Do NOT reduce rows (unlike GROUP BY)
Perform calculations across related rows
Allow row-level analytics

This is extremely important.

Example question

What if we want:

Total spending per customer, but still show each individual order?

If you use GROUP BY:

SELECT customer_id,
SUM(amount) AS total_spent,
FROM orders
GROUP BY customer_id;

You get

1 | 550
2 | 150

But you lose individual orders.

Enter Window Functions

SELECT order_id,customer_id,amount,
SUM(amount) OVER (PARTITION BY customer_id) 
AS total_spent
FROM orders;

Result:

1 | 1 | 250 | 550
2 | 1 | 300 | 550
3 | 2 | 150 | 150

Now you have

Each order
AND total per customer
Without collapsing rows

That’s a better way to do it.

Understanding OVER()

The magic happens inside the OVER() clause.

PARTITION BY

Groups rows logically (like GROUP BY), but does not collapse them.

ORDER BY

Defines order within each partition(basically how you want them show).

Example: Ranking orders by amount.

SELECT order_id, customer_id,amount,
RANK() OVER (PARTITION BY customer_id ORDER BY amount DESC) 
AS customer_rank,
FROM orders;

This ranks each customer’s orders separately.

Common Window Functions You Should Know

ROW_NUMBER()

Gives unique row numbers.

ROW_NUMBER() OVER (ORDER BY amount DESC)

RANK()

Gives same rank for ties, skips numbers.

DENSE_RANK()

Gives same rank for ties, does NOT skip numbers.

SUM() OVER()

Running totals

SELECT order_id, amount,
SUM(amount) OVER (ORDER BY order_id) 
AS running_total
FROM orders;

LAG() and LEAD()

Compare rows to the ones before or the ones after.

SELECT order_id, amount,
LAG(amount) OVER (ORDER BY order_id) 
AS previous_amount
FROM orders;

Very useful for time-series analysis

Joins vs Window Functions

The Real Difference

Here’s the more clearer distinction

Joins

Combine tables
Increase columns
Used to bring related data
Based on keys

Window Functions

Analyze rows within a table
Add calculated insights
collapsing rows
Based on partitions and order

When Should You Use Each?

Use JOIN when-

You need data from multiple tables
You’re connecting facts and dimensions
You’re building analytical datasets

Use Window Functions when-

You need ranking
You need running totals
You need comparisons between rows

-You want aggregates without GROUP BY

In everyday analytics and data engineering, you often use BOTH together.

Example

SELECT customer_name, order_id,order_amount,
SUM(order_amount) OVER (PARTITION BY customer_id) AS total_spent
FROM customers
JOIN orders
ON customer_id = customer_id;

This combines tables AND applies analytics

That’s production-level SQL.

In conclusion

Joins connect tables using keys.

Window functions perform analytics without collapsing rows.

GROUP BY reduces rows, window functions do not.

PARTITION BY is like GROUP BY, but keeps detail rows.

Modern data work heavily relies on window functions.

If you’re serious about becoming strong in SQL especially as a Data Engineer mastering both concepts is non-negotiable.

you can check more of my articles on https://www.linkedin.com/in/thomas-wangeci-065469194/

How Analysts Translate Messy Data, DAX, and Dashboards into Action Using Power bi

Wangeci Ndovu — Mon, 16 Feb 2026 14:30:16 +0000

When I started working with large amounts of data I quickly realized one thing and that is that, raw data isn’t valuable until it tells a story. This is where analysts step in, turning messy datasets into actionable intelligence. At the heart of this transformation is Microsoft's Power BI, a powerful analytics platform that helps analysts organize data, build logic with DAX, and deliver dashboards that drive decisions.

In this article, we'll look at how analysts approach messy data, apply DAX (Data Analysis Expressions) to add intelligence, and design dashboards that empower stakeholders, who are often times the key decision makers in organizations to act with confidence.

Understanding the Messy Data

Messy data is everywhere. sometimes customers have inconsistent names, at other times dates are stored as text. decimals may sometimes use commas instead of full stops. The first step in any analytics initiative is data understanding and cleaning, key on the cleaning.

Common Messy Data Issues

Missing values
Inconsistent formats
Duplicated records
Mis-typed entries
Irrelevant data

What Analysts Do First

go through the dataset
Identify inconsistencies
Transform the data through data query

Before building anything in Power BI, analysts most times start in Power Query, cleaning data using an intuitive UI or M language.

Data Transformation using Power Query

Power BI’s Power Query Editor is where the heavy lifting actually happens.

Analysts use it to

Split columns
Change data types
Replace inconsistent text
Merge and append tables
Handle missing values

The key here is: “Prepare once, reuse many”. With Power Query steps, transformation logic persists every time the dataset refreshes.

Adding Intelligence With DAX

Once the data is clean and structured, it’s time for one of Power BI’s most powerful tools: DAX (Data Analysis Expressions)

DAX is the language that fuels calculated columns, measures, time intelligence, and business logic within Power BI, and it is designed specifically for data analysis.

so how do analysts use DAX in real life scenarios

Creating Important Metrics

Instead of simple raw columns, analysts define business critical metrics using DAX, such as:

Total Sales = SUM(Sales[Amount])

This creates a reusable measure that aggregates sales dynamically across filters.

Time Intelligence

Common business questions involve time comparisons. With DAX, you can express these like:

Sales Last Year = CALCULATE([Total Sales],

SAMEPERIODLASTYEAR(Calendar[Date]))

Now you can compare year-over-year trends with ease

Designing Dashboards That Tell Stories

Data without visualization is pontless it fails to tell the story that is intended overally. Dashboards are where analytics actually communicate.

Key Principles Analysts Follow

Start with questions — What business decisions do stakeholders need to make?
Choose clear visuals — Bar charts for comparisons, line charts for trends, cards for key numbers.
Use slicers thoughtfully — Let users filter context without clutter.
Avoid noise — Too many visuals complicate and dilute the point of focus.

A typical Power BI dashboard MUST answers:

What happened?
Why did it happen?
What might happen next?

This turns passive data into actionable insight

Refreshing and Operationalizing Insights

Power BI dashboards aren’t static reports — they’re living, refreshing assets. Analysts schedule data refreshes, connect to live data sources, and configure alerts on a daily so stakeholders don’t miss key changes.

This is where analytics becomes practically actionable, not just descriptive

To put it at a glance

Here’s an example of a simple step-by-step analytics workflow in Power BI:

Phase Tool Output

Ingest Power Query Clean tables
Logic DAX Dynamic measures
Visualize Reports & Dashboard Actionable views
Share Power BI Service Published insights

Each step builds on the last and without any of them, insights remain heavily inaccurate.

key take home

Power BI is powerful, but it’s the analyst’s mindset that turns raw data into actionable insights/ logic:

Curiosity — What story is the data trying to tell?
Precision — Is this metric calculated correctly?
Clarity — Can a user understand this at a glance?
Impact — Does this lead to better decisions?

Power BI is the tool — but it is virtually useless without the analyst

In conclusion

Whether you’re building your first dashboard or optimizing complex enterprise analytics, the process remains the same:

Understand the data
Clean and shape it
Add intelligence with DAX
Visualize simply and with clarity
Empower users to take action

Schemas and Data Modelling in Power BI: A Practical Guide for Accurate and High-Performance Reporting

Wangeci Ndovu — Mon, 02 Feb 2026 15:08:17 +0000

When working with Power BI, most beginners focus heavily on visuals—charts, tables, slicers, and dashboards. However, the real foundation of reliable, fast, and accurate Power BI reports is data modelling.

A poorly designed model can lead to:

Slow reports
Incorrect totals
Confusing relationships
Hard-to-maintain dashboards

In this article, we’ll explore schemas and data modelling in Power BI, focusing on:

Star schema
Snowflake schema
Fact and dimension tables
Relationships

Why good modelling is critical for performance and accurate reporting

What Is Data Modelling in Power BI?

Data modelling is the process of structuring your data into tables and defining how those tables relate to each other.

In Power BI, this happens in the Model view, where you:

Organize tables
Create relationships
Decide filter directions
Design a structure that supports efficient analysis

Think of data modelling as designing the blueprint before building the house.

Fact Tables vs Dimension Tables

Before discussing schemas, it’s important to understand the two main table types.

Fact Tables

Fact tables store measurable, numerical data.

Examples:

Sales amount
Quantity sold
Revenue
Cost
Profit

Characteristics:

Usually very large

Contain foreign keys to dimensions

Contain metrics used in calculations

Example:
Fact_Sales

DateKey ProductKey CustomerKey SalesAmount Quantity
Dimension Tables

Dimension tables store descriptive attributes used for filtering and grouping.

Examples:

Product name
Customer name
Region
Category

-Date details

Characteristics:

Smaller than fact tables

Contain descriptive columns

Used in slicers and axes

Example:
Dim_Product

ProductKey ProductName Category Brand

What Is a Star Schema?

The star schema is the recommended and most efficient data model for Power BI.

Structure

One central fact table

Multiple dimension tables

Dimensions connect directly to the fact table

The model visually resembles a star

Example:

Dim_Date Dim_Product Dim_Customer
\ | /
Fact_Sales

Why Star Schema Is Best for Power BI

Simple relationships
Faster performance
Easier DAX calculations
Clear filter flow
Easier to understand and maintain

Power BI’s engine VertiPaq is optimized for star schemas.

Example Star Schema in Power BI

Fact_Sales
Dim_Date
Dim_Product
Dim_Customer
Dim_Region

Each dimension connects one-to-many to the fact table.

What Is a Snowflake Schema?

A snowflake schema is a variation of the star schema where dimension tables are further normalized into sub-dimensions.

Structure

Fact table at the center

Dimension tables split into multiple related tables

More relationships

Example:

Dim_Product → Dim_Category
\
Fact_Sales

When Snowflake Schema Appears

Data comes directly from normalized databases

Dimensions have many hierarchical levels

Storage optimization is a priority

Drawbacks in Power BI

More complex relationships
Slower performance
Harder DAX calculations
Confusing filter paths

In Power BI, denormalizing dimensions back into a star schema is usually recommended.

Relationships in Power BI

Relationships define how tables filter each other.

Common Relationship Type

One-to-Many (1:*)
Dimension (1)
Fact (*)

Example:

Dim_Product[ProductKey] → Fact_Sales[ProductKey]

**Relationship Direction##

Power BI relationships usually use:

Single direction (Dimension → Fact)

Avoid:

Bi-directional filters unless absolutely necessary

Many-to-many relationships (performance risk)

Why Good Data Modelling Is Critical
##Performance##

Star schema reduces joins

Smaller, denormalized dimensions compress better

Faster report loading and interactions

Accurate Calculations

Bad models cause:

Double counting
Incorrect totals
Broken time intelligence

Good models ensure:

Correct aggregation
Predictable DAX behavior

##Simpler DAX##

With a clean star schema:

Measures are shorter
Logic is clearer
Debugging is easier

Example:

Total Sales = SUM(Fact_Sales[SalesAmount])

No complex filters needed.

##Easier Maintenance##

Adding new visuals is straightforward

New measures don’t break existing reports

New data sources integrate cleanly

Best Practices for Power BI Data Modelling

Use star schema whenever possible
Separate facts and dimensions
Avoid unnecessary bi-directional filters
Use surrogate keys (IDs)
Flatten snowflake dimensions when possible
Validate relationships early
Keep the model simple and readable

Conclusion

In Power BI, great visuals come from great models.

You can have the best charts in the world, but without:

Proper schemas
Clean relationships
Well-defined fact and dimension tables

your reports will be slow, inaccurate, and difficult to trust.

Mastering data modelling is what separates a Power BI user from a Power BI professional.

Introduction to Linux for Data Engineers, Including Practical Use of Vi and Nano with Examples

Wangeci Ndovu — Mon, 26 Jan 2026 16:34:43 +0000

#Introduction#

Linux is one of the most important technologies behind modern data systems. While many beginners focus first on programming languages like Python or SQL, most real-world data engineering work happens on Linux-based systems. Understanding Linux basics—especially how to work with files using terminal editors—is a key step in becoming a confident data engineer.

This article introduces Linux from a beginner’s perspective, explains why it matters in data engineering, and demonstrates practical text editing using Vi and Nano, supported by real terminal examples.

Why Linux Is Important for Data Engineers

Most data engineers do not work only on personal computers. Instead, they manage and maintain:

Cloud servers (AWS EC2, Google Compute Engine, Azure VMs)

Big data platforms (Hadoop, Spark, Kafka)

Workflow tools (Airflow, Luigi)

Databases and data warehouses

All these systems primarily run on Linux

Key benefits of Linux in data engineering

Server dominance Linux is the default operating system for servers

Stability Data pipelines can run for days or weeks without interruption

Automation Linux supports scripting and scheduling with ease

Cost-effective Open-source and widely supported

Command-line power Faster and more precise than graphical interfaces

For these reasons, Linux skills are often listed as a core requirement in data engineering job descriptions.

Getting Comfortable with the Linux Terminal

The Linux terminal allows users to interact with the system using text commands.

Example terminal prompt:

ndovu@NDOVU:~$

Explanation:

ndovu → username

NDOVU → computer name

~ → home directory

$ → ready to accept commands

Essential Linux Commands for Beginners
Checking Your Current Location
pwd

Output:

/home/ndovu

This command shows the current directory you are working in.

Viewing Files and Directories
ls

Sample output:

data scripts notes.txt

To see detailed information

ls -l
Creating Directories
mkdir pipelines

Creating multiple levels at once

mkdir -p data/raw data/processed

Creating Empty Files
touch readme.txt

Moving Between Directories
cd data

Go back one level

cd ..

Why Text Editors Matter in Linux

Data engineers frequently edit:

Configuration files

Shell scripts

SQL and Python files

Log files

On Linux servers, graphical editors are often unavailable. This is why terminal-based editors such as Nano and Vi are essential.

Editing Files with Nano (Beginner Friendly)

Nano is easy to learn and ideal for beginners.

Opening a File with Nano
nano readme.txt

If the file does not exist, Nano creates it automatically.

Writing Content in Nano

Type the following text

This project contains data engineering examples.
Linux is essential for managing pipelines.

Saving and Closing Nano

At the bottom of the screen, Nano shows helpful shortcuts:

^O Write Out ^X Exit

Steps:

Press CTRL + O to save

Press Enter to confirm

Press CTRL + X to exit

Confirming the File Content
cat readme.txt

Expected output

This project contains data engineering examples.
Linux is essential for managing pipelines.
Editing Files with Vi (Industry Standard)

Vi (or Vim) is more complex than Nano but extremely powerful.

Opening a File Using Vi
vi config.conf

Vi starts in command mode, not insert mode.

Switching to Insert Mode

Press

Now type

source=mysql
format=csv
target=hdfs

Saving and Exiting Vi

Press ESC to return to command mode

Type:

:wq

Press Enter

Common Vi Commands

Command Description
i Enter insert mode
ESC Return to command mode
:w Save file
:q Quit
:wq Save and quit
:q! Quit without saving

Practical Data Engineering Scenario

A common task for a data engineer is editing pipeline configurations on a remote server.

ssh user@analytics-server

cd /etc/pipelines
vi ingestion.conf

File content example

source=kafka
format=json
target=data_lake

This simple task reflects real production work done daily by data engineers.

Why Terminal Editors Are Still Relevant

They work on remote servers

No graphical interface required

Lightweight and fast

Essential for troubleshooting production issues

Conclusion

Linux is a foundational skill for data engineers. By learning basic commands and mastering text editors like Nano and Vi, beginners gain the confidence to work on real servers and real data systems.

Starting with Nano and gradually learning Vi is a practical approach that prepares you for professional data engineering environments.

What to Learn Next

Linux file permissions (chmod, chown)

Shell scripting basics

Running Python and SQL scripts on Linux

Exploring Spark and Airflow on Linux

With consistent practice, Linux will become a powerful and natural tool in your data engineering journey.

Happy coding

Understanding Git: How to Track Changes, Push, and Pull Code Like a Pro

Wangeci Ndovu — Fri, 16 Jan 2026 11:27:47 +0000

code 101

When you start writing code, you quickly realize something:
files change, mistakes happen, and things break

So how do professional developers keep track of what changed, how it changed, how to correct it or in words, how to go back in time when something breaks?

This article simply explains:

How Git tracks changes
How to push code to GitHub
How to pull data from GitHub

beginner friendly

What is Version Control

Version control is a system that keeps track of:

Every change you make to your code
When the change happened
Who made the change
What exactly was modified

Think of it like Google Docs history for your code.

If your code breaks, Git lets you correct it to a working version.

Git also helps you collaborate with other like minded individuals in shared projects.

What is GitHub?

GitHub is a cloud platform where Git repositories are stored online.
You use
Git on your computer
GitHub to back it up and share it with others

How to create a Git Repository

A repository is a folder that Git tracks.

git init

This creates a hidden .git folder inside your project.
Now Git is watching this directory.

Save a Version (Commit)

A commit is a snapshot of your project.

git commit -m "Add file"

Now Git has stored that version forever.
You can always go back to it.

Push Code to GitHub

First connect your project to GitHub:

git remote add origin git@github.com:yourname/yourrepo.git

Push:

git push -u origin main

Note: The default branch name is often main. If yours is "master"

use

git push -u origin master.

Your code is now safely stored online.

Pull Code from GitHub

If someone else updates the repository, or you work from another computer:
Navigate to your local repository directory using the command

cd

For example, if your repository is in a folder named Mombasa then,

cd Mombasa

Ensure you are on the correct branch by using the command

git status

Pull the latest changes from the remote repository using the git pull command

git pull

This downloads the latest changes into your project.

How Git Tracks Changes

When you edit a file:

git status

Git will show:

modified: Mombasa

To save the change:

git add Mombasa

then

git commit -m "Mombasa"

Git stores only what changed, not the entire file.

This makes Git fast and powerful.

Why Git is a Superpower

With Git you can

Undo mistakes
Work on features safely
Collaborate with others
Track project history
Work on multiple versions

This is why every professional developer uses Git.

conclusion

Git is not just a tool — it is how software is built.

Once you understand:

add
commit
push
pull

You can work on any real-world engineering project.