DEV Community: Lawrence Murithi

ETL vs ELT: Which One Should You Use and Why?

Lawrence Murithi — Sat, 11 Apr 2026 17:51:32 +0000

Introduction

Imagine you are running a massive kitchen. Every day, trucks arrive carrying raw ingredients from different farms. Some boxes have dirty potatoes, some tomatoes are bruised, and the meat needs to be separated from the bone.
Can you just throw all of this straight onto a customer’s plate? Definitely not. You have to wash, chop, season, and cook the ingredients first.

In the business world, data works the same way. Every day, companies generate tons of raw data from apps, websites, payment gateways, customer service logs etc. This raw data is usually dirty and messy. It has errors, missing fields and mismatched formats. Before it can be used for reporting or decision-making, it needs to be moved, processed and organized. This process of moving and cleaning data is called data integration.
The two main approaches are used in data integration are ETL (Extract, Transform, Load) and ELT (Extract, Load, Transform). Although both methods aim to prepare data for analysis, they follow different steps and are suited for different situations.
If you are just stepping into data engineering, software engineering or backend development, ETL and ELT are common terms you will encounter.
This article explains both approaches in detail, compares them, and helps you understand when to use each one.

What is ETL?

ETL stands for Extract, Transform, Load. It is the traditional method used to move and prepare data.
The key idea in ETL is that data is cleaned and transformed before it is stored in the final system. This means that by the time the data reaches the data warehouse, it is already structured, organized, and ready for use.
This approach was developed at a time when computing resources were limited, and companies had to be very careful about what data they stored.

Steps in ETL

1. Extract
This step involves collecting raw data from different sources such as:

Databases
APIs
Excel files

In real-world scenarios, data rarely comes from a single source. A company may have customer data in one system, sales data in another, and marketing data in a third system. This extraction step pulls all this data together.

2. Transform
In this stage, data is processed in a separate system before being stored. This transformation step ensures that all data is consistent, accurate, and usable.

Common transformations include:

Standardizing data formats
Handling missing values
Removing duplicate records
Fixing errors in data
Masking sensitive data such as credit card numbers
Combining data from different sources

This step is where raw data is made meaningful. Without transformation, data would remain inconsistent and difficult to analyze.

3. Load
After transformation, the cleaned data is loaded into a data warehouse or database.
At this stage, the data is ready for carrying out analysis, creating dashboards and reporting.
Simple Diagram of ETL

Why ETL Was Popular

In the past, data warehouses were physical servers sitting in basements. Storage space was incredibly expensive and computing power was very limited. Companies, therefore, could not afford to store raw, useless data. They had to clean it up and shrink it down before loading it into the warehouse.

What is ELT?

ELT stands for Extract, Load, Transform. It is a modern approach made possible by cloud computing. Here data is loaded first and transformed later inside the data lake.
This approach takes advantage of modern systems that can store large amounts of data and process it quickly.

Steps in ELT

1. Extract
Data is collected from different sources just like in ETL.

2. Load
This is a major shift from ETL. Instead of first cleaning the data, you load the raw data directly into your target data lake without any changes.

3. Transform
The transformation happens inside the data lake. This means analysts can use the warehouse's own computing power to clean, format, and organize the data..

Simple Diagram of ELT

Why ELT Became Popular

The emergence of modern cloud data warehouses such as Snowflake, Google BigQuery, and Amazon Redshift changed the game. Today, storing data in the cloud is incredibly cheap. Furthermore, these cloud warehouses have massive, scalable computing power.
Instead of buying a separate, expensive server just to transform data (like in ETL), companies no longer need to clean data before storing it. They can store everything and process it later.

Differences Between ETL and ELT

1. Order of Steps
in ETL, transformation happens before loading while in ELT transformation happens after loading.

2. Where the Transformation Happens
In ETL, transformation happens in a separate server outside the warehouse while in ELT, the transformation happens right inside the destination data warehouse.

3. Speed of Loading
ELT is usually much faster at the loading stage since there is no cleaning of the data. ETL takes longer because the data has to wait in line to be processed before it can be loaded into the warehouse.

4. Maintenance and Flexibility
ETL is less flexible and changes require rebuilding pipelines. If a mistake is made in an ETL pipeline, or if you want to format the data differently, you have to go back to the source, re-extract the data, and run it through the whole pipeline again.
With ELT, the raw data is already sitting in your warehouse. Any mistake during transformation, you simply write a new SQL query and transform the raw data afresh.

5. The Skills Required
ETL often requires specialized tools and programming such as software engineers who know Java, Python or drag-and-drop tools. ELT uses SQL and since the data is transformed inside a database, it is accessible to analysts.
NB:

ETL focuses on control, structure, and quality before storage
ELT focuses on speed, flexibility, and scalability after storage.

Advantages and Disadvantages

ETL

Advantages
Security and Compliance - If you are dealing with highly sensitive data (like medical records or credit cards), ETL allows you to strip out/mask the sensitive parts before storage in the main warehouse.
Reduced and cheaper Storage - Because you are only loading refined data, you take up much less storage space in your destination database.

Disadvantages
Rigid - Setting up an ETL pipeline takes a lot of time. If a source system needs to make a change, the whole ETL pipeline might break and need to be rewritten.
Bottlenecks - If you have massive amounts of data, the processing server can easily get overwhelmed and slow down the whole operation.

ELT

Advantages
Agility - Since raw data is loaded quickly and directly into the warehouse, analysts do not have to wait for engineers to build complex pipelines to access the raw data.
Future-Proof - Because you keep a copy of the exact raw data, reprocessing of raw data is always possible. You can also go back and answer new business questions that you hadn't thought of previously.
Scalability - Cloud warehouses are designed to scale automatically thus are able to support large datasets.

Disadvantages
Security Risks - Since you are loading raw, unfiltered data into your warehouse, you have to be careful about who has access to the warehouse if that data contains sensitive information such as passwords, personal addresses or financial details.
Higher computing costs - While cloud storage is cheap, cloud computing can get expensive. If you have bad SQL code running inefficient transformations inside your warehouse every hour, your monthly cloud bill will skyrocket.

ETL Tools

These tools are designed for structured, enterprise-level data pipelines.

Informatica
IBM DataStage
Talend

ELT Tools

Modern ELT uses different tools for each step:
These tools allow analysts to work directly with data using SQL.

Fivetran / Airbyte → Extract and Load
dbt (Data Build Tool) → Transform
Cloud Warehouses → Snowflake, BigQuery, Redshift

Real-World Use Cases

Banking System (ETL)
A bank handles sensitive data from mobile app banking, ATMs and physical branch locations. This data contains raw account numbers, account balances, passwords and PIN, personal details and financial transactions thus must be secured before storage.

E-commerce Startup (ELT)
An online store that wants to track user behavior will generate large amounts of data daily just from people clicking around their website, viewing products, adding items to carts etc. The marketing team thus has to constantly change what they want to measure. One week they may want to track abandoned carts while the following week they may want to track how long people look at a specific product. The business has to frequently change what it wants to analyze.

Which One Should You Use and Why?

If you are starting a new project and trying to choose between ETL and ELT, here is a practical guide to help you decide.
Choose ETL if
- You are bound by strict privacy laws - If you work with sensitive data (healthcare, banking), the ability to scrub data before it lands in a database should be key.
- Your system uses on-premise databases - If your company still keeps its servers in a physical server room, your database may not have high processing power required to do transformations internally hence you will need a separate ETL server.
- Your data source is unstructured - If you are extracting data from highly complex, old mainframes that output weird file types, standard ELT tools might not know how to read them. You will need a custom ETL script to decode and format the data before it can be saved.

Choose ELT if
- You are using a cloud data warehouse - If you have Snowflake, BigQuery, or Redshift, ELT is most convinient since it takes advantage of what you are already paying for.
- You work with large volumes of diverse data - If you are tracking millions of tiny events (like website clicks, product views or IoT sensor readings), pushing it directly to the cloud is the only way to keep up with the volume.
- You need flexibility in analysis and fast data processing - ELT allows data engineers to focus purely on moving data from point A to point B, while empowering data analysts to handle the business logic and formatting using SQL.

Conclusion

The debate between ETL and ELT is less about which one is better and more about matching your business needs, data size, and system architecture. Understanding both approaches helps you design better data pipelines and make smarter decisions when working with data.

Advanced SQL Techniques for Data Analytics Every Data Analyst Should Know

Lawrence Murithi — Thu, 09 Apr 2026 13:21:19 +0000

Introduction

In today’s data-driven environment, organizations rely heavily on data to make decisions. Businesses collect large amounts of information from different sources such as sales systems, customer platforms, and operational databases. However, raw data alone is not useful unless it can be analyzed and transformed into meaningful insights.

SQL (Structured Query Language) plays a central role in this process. It allows analysts to retrieve, clean, and analyze data stored in relational databases. While basic SQL skills are important, advanced SQL techniques are what truly enable analysts to solve complex business problems.

This article explains advanced SQL concepts in simple terms and shows how they are applied in real-world data analytics scenarios. The goal is to help you understand not just how to write SQL queries, but how to use them effectively in practical situations.

The Role of SQL in Data Analytics

SQL is the foundation of data analytics. Most business data is stored in databases, and SQL is the language used to interact with that data.

Data analysts use SQL to:

Extract data from databases
Filter and clean datasets
Combine data from multiple tables
Perform calculations and aggregations
Prepare data for reporting tools like Power BI

SQL is often the first step before using any visualization tools. If the data is not properly prepared using SQL, the final reports may be inaccurate or misleading.

Working with Complex Queries

As data becomes more complex, simple queries are not enough to handle it. Advanced SQL, therefore, introduces techniques that help break down complex problems into manageable steps.
In real-world data analysis, datasets are often large and contain multiple tables with different relationships. Moreover, analysts are expected to answer questions that involve comparisons, calculations and multiple layers of logic. These techniques therefore allow analysts to solve the problems step by step instead of trying to do everything in one single query.
Complex query techniques thus help analysts organize their queries in a way that is easier to understand, maintain, and scale.

They are useful when:

Comparing values against aggregated results
Reusing part of a query
Working with multi-step transformations
Simplifying long and confusing SQL statements

Some of the advanced SQL techniques include:

Subqueries

A subquery is a query inside another query. Subqueries are useful when you need to perform a calculation first and then use that result in another query. They allow you to embed logic directly inside your main query.

SELECT name
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

Explanation:
_- The inner query calculates the average salary

The outer query returns employees earning above average_

Subqueries can be used in different parts of a query:

In the WHERE clause
In the SELECT clause
In the FROM clause (called derived tables)

Real-World Case Scenarios:

Identify high-performing employees based on salary or performance metrics.
Finding customers who spend more than the average customer
Identifying products priced above the average price

NB: While subqueries are powerful, they can become slow if used incorrectly, especially with large datasets.

Common Table Expressions (CTEs)

A CTE is a temporary result in an SQL query that helps improves readability and organization(temporary table that exists only while the query is running).

CTEs allow you to define a query once and then use it in the main query. This makes complex queries easier to read and understand, especially when working with multiple steps.

WITH sales_summary AS (
    SELECT product_id, SUM(amount) AS total_sales
    FROM sales
    GROUP BY product_id
)
SELECT *
FROM sales_summary
WHERE total_sales > 1000;

Types of CTEs:
- Recursive CTE: A specialized CTE that references itself, which is essential for querying hierarchical data like organizational charts or family trees.
- Non-Recursive CTE: The most common type, used to simplify standard queries by creating manageable logical steps.
Benefits:

Makes queries clean and easier to read
Breaks complex logic into steps, thus easier to debug and modify
Improves maintainability

NB: You can also have multiple CTEs in one query, which is useful for complex data transformations.

In business reporting, analysts often build layered queries. CTEs allow them to structure their logic clearly when working with large datasets.
Step 1: Calculate total sales per product
Step 2: Filter high-performing products
Step 3: Join with other tables for reporting

Advanced Joins

Joins are used to combine data from multiple tables. In advanced SQL, joins become more powerful when dealing with complex relationships.

SELECT c.customer_name, o.order_date, p.product_name
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN products p ON o.product_id = p.product_id;

In a retail company:

Customers table stores customer details
Orders table stores transactions
Products table stores product information

Using joins, analysts can create a full view of customer purchases.

Poor joins can lead to:

Duplicate data
Incorrect totals
Misleading reports

Window Functions

Window functions allow us to perform advanced calculations across a group of related rows while keeping the original data. They are useful for ranking, running totals, moving averages, and analytical reporting.
Window functions often remove the need for complex self-joins and provide an analytical layer within SQL.
Window functions:

Keep every row
Add calculated values to each row

SELECT column_1,
       function() OVER (
           PARTITION BY column
           ORDER BY column
       ) AS output_column
FROM table_name;

Window functions are widely used in business intelligence and reporting for:

Rankings within a group
Calculating running totals
Compare rows (current vs previous)
Analyzing trends over time

Companies use ranking to:

Identify top performers
Allocate bonuses
Compare employee performance

## Ranking employees

SELECT name, salary,
RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

Businesses use running totals to:

Track revenue growth
Monitor daily or monthly performance
Forecast future trends

## Running totals

SELECT date, sales,
SUM(sales) OVER (ORDER BY date) AS running_total
FROM sales;

Aggregations and Grouping

Aggregation helps summarize large datasets. Raw data is often too detailed to understand directly. Aggregation thus helps turn large datasets into meaningful summaries.

SELECT region, product_id, SUM(sales) as total_sales
FROM sales
GROUP BY region, product_id;

Aggregation allows analysts to answer questions such as:

Total sales by region
Sales by product category
Monthly revenue trends

Aggregation is often used together with:

Filtering (HAVING)
Sorting (ORDER BY)

Data Cleaning and Transformation

Data cleaning is one of the most important steps in analytics. Since raw data is usually dirty and messy, SQL helps clean and prepare it before analysis.
Raw data may contain:

Duplicates
Missing values
Incorrect formats
Inconsistent entries

Removing Duplicates

Removes repeated values and ensures each entry appears only once.

SELECT DISTINCT customer_id
FROM customers;

Handling Missing Values

Replaces NULL values with a default value thus preventing errors in reports

SELECT COALESCE(phone, 'Not Available')
FROM customers;

Data Transformation

Creates a new calculated column
Data transformation also includes:

Changing data types
Formatting dates
Standardizing values

SELECT price, quantity, price * quantity AS total_sales
FROM sales;

Using SQL for Real-World Business Problems

Advanced SQL is not just about writing queries but solving real problems.
In organizations, SQL is used daily to answer business questions and support decisions.

Customer Segmentation

Businesses use customer segmentation to:

Target high-value customers
Design marketing strategies
Improve customer retention

## Grouping customers based on spending

SELECT customer_id,
CASE 
    WHEN total_spent > 1000 THEN 'High Value'
    WHEN total_spent > 500 THEN 'Medium Value'
    ELSE 'Low Value'
END AS segment
FROM customer_sales;

Sales Performance Analysis

Total sales are calculated per product and sorted products by performance to identify best-selling products.

SELECT product_id, SUM(sales) AS total_sales
FROM sales
GROUP BY product_id
ORDER BY total_sales DESC;

Segmentation helps organizations to:

Understand performance
Identify opportunities
Solve operational problems

Performance Optimization

SQL queries must be clean, easy to understand and efficient.
In large databases, poor queries can slow down systems and delay reports.

Best Practices:

Use indexes on important columns to speed up data retrieval
Avoid selecting unnecessary columns
Filter data early to reduces data size
Use CTEs instead of repeated subqueries
Avoid unnecessary joins

Conclusion

Advanced SQL is a critical skill for data analysts. It goes beyond basic queries and allows analysts to work with complex datasets, perform advanced calculations and solve real-world business problems.

In this article, we explored key advanced SQL techniques such as subqueries, CTEs, joins, window functions, aggregations, and data transformation and how they are applied in real business scenarios

In data analytics, SQL is not just a tool but is a core skill that connects raw data to meaningful insights. Mastering advanced SQL allows analysts to move from basic reporting to deeper, more impactful analysis

Connecting Power BI to SQL Databases: A Practical Guide for Data Analysts

Lawrence Murithi — Tue, 17 Mar 2026 12:03:47 +0000

Introduction

In most modern organizations, data is one of the most valuable assets. Companies collect large amounts of information from sales systems, websites, customer platforms, and operational databases. To make sense of this information, businesses use tools that can transform this raw data into clear insights. One of the most widely used tools for this purpose is the Microsoft Power BI platform.

What is Power BI

Power BI is a business intelligence and data visualization tool developed by Microsoft. It allows users to connect to different data sources, analyze data, and create interactive dashboards and reports. These reports help organizations monitor performance, understand trends, and support decision-making among other uses.

Power BI is commonly used by data analysts, business managers, and decision makers because it can present complex data in simple visual forms such as charts, tables, maps, and dashboards.

Most organizations store their operational and analytical data in SQL databases. SQL databases are designed to store large amounts of structured data in tables. They allow users to query, filter, update, and analyze data efficiently using Structured Query Language (SQL). SQL databases are reliable, secure, and scalable, hence they are widely used in business systems such as sales platforms, customer management systems, and inventory systems.

Connecting Power BI to a database allows analysts to access this stored data directly. Instead of manually exporting data into spreadsheets, Power BI can retrieve the data automatically, refresh it when the database changes, and build dashboards that always reflect the latest information.

This article explains how Power BI connects to SQL databases, how to connect to a local PostgreSQL database, how to connect to a cloud database such as Aiven PostgreSQL, and how the loaded data is modeled for analysis.

Understanding the Power BI Interface

Before connecting to a database, it is helpful to understand the Power BI Desktop interface. Power BI Desktop is the main application used for building reports and dashboards.

The Power BI Desktop interface includes several sections such as:

Ribbon (Top Menu) – Contains commands and tabs such as Get Data, Transform Data, and Publish.
Report Canvas – The workspace where charts and dashboards are created.
Visualizations Pane – Used to select and customize charts.
Fields Pane – Displays the tables and columns loaded into Power BI.

You can download Power BI desktop app here.

Connecting Power BI to a Local PostgreSQL Database

PostgreSQL is one of the most popular open-source relational databases used in data analytics. Many organizations run databases locally on their own servers.
The steps below explain how to connect Power BI to a local PostgreSQL database.

Step 1: Open Power BI Desktop

Start by opening Power BI Desktop on your computer.
When the application opens, a blank report canvas appears. This is where you will build your report after loading the data.

Step 2: Get Data

On the Home tab of the ribbon, click Get Data.
This button opens a list of available data sources. Power BI supports many data sources including:

Excel
SQL Server
PostgreSQL
Web APIs The Get Data feature is the starting point for connecting Power BI to any data source. Other data sources are as shown on the image.

Step 3: Select PostgreSQL Database

From the list of available data connectors, click more to view more options. Scroll down, select PostgreSQL Database and click Connect.

Step 4: Enter the Database Connection Details

After selecting PostgreSQL and clicking connect, Power BI opens a connection window that requires connection details for the connection to go through.

Server – The location of the database server. If the database is on your computer, use localhost.

Database – The name of the database you want to connect to.

Step 5: Provide Login Credentials

After a connection is made, Power BI will ask for authentication details.
You will need to provide:
Username
Password

These credentials were set up during installation of the PostgreSQL and allows Power BI to securely access the database.

Once the credentials are entered, click Connect.

Step 6: Select Tables to Import

After connecting successfully, Power BI opens the Navigator Window which displays all available tables in the database.
You can preview the contents of each table before loading them.
There are two options:
Load – Import the data directly.
Transform Data – Clean or modify the data before loading it.

Connecting Power BI to a Cloud Database (Aiven PostgreSQL)

Many organizations now store their databases in the cloud. Cloud databases are accessible through the internet and provide benefits such as scalability, backups, and easier management.
Aiven is a cloud platform that provides managed PostgreSQL databases.
Connecting Power BI to a cloud database is not different to connecting to a local database, only that additional security steps are required.

The steps below explain how to connect Power BI to an Aiven PostgreSQL database.

Step 1: Get the Database Connection Details from Aiven

Login to Aiven and inside the dashboard, you will find the connection information for your PostgreSQL service. These details are used by Power BI to locate and connect to the database.

Step 2: Download and install the SSL Certificate

Cloud database providers often require SSL encryption to secure the connection.
An SSL certificate ensures:

Data transferred between Power BI and the database is encrypted
Unauthorized users cannot intercept the connection
The database server identity is verified

In Aiven, download the certificate file(CA Certificate) from the Connection Information section of the service dashboard.
Rename the downloaded file from ca.pem to ca.crt and install the Certificate on your PC.

Choose Local Machine as the location of the installation and click next.

Choose place all certificates in the following store and browse certificate store to Trusted Root Certification Authorities.
Click ok and finish.

Step 3: Connect Power BI

Open Power BI desktop as before, click Get Data, click more, scroll down and select PostgreSQL Database.
Copy the Server Name from the service URL(host_name:port_number) on Connection Information and paste on Power BI.
Input the name of your database and click ok.

Copy the username and password from Aiven, input them on the Power BI credentials window that opens and click connect.
Once the connection is successful, a navigator window opens and displays all tables in the database.
Select the tables you want to work with and click on load/transfrom data depending on what you wish to do with the data.
Transform data option is used to clean raw data e.g delete any duplicates and address null values using the most appropriate method.

Successfully loaded data displays on the data pane as shown on figure below.

Creating Relationships Between Tables

Once loaded, Power BI automatically detects relationships between tables based on matching columns using primary and foreign keys. Relationships not created can be manually created by dragging a column from one table onto the matching column in another table.
These relationships allow Power BI to combine information across multiple tables.
For example:

In the Model View, the tables appear as connected boxes. The relationships show how data flows between tables.

Data Modeling and Why its Important

Data modeling is the process of defining how data is stored, structured, and related within a database. It ensures that Power BI understands how different tables are related.

Good data modeling allows Power BI to:

Filter data correctly
Calculate totals accurately
Create meaningful visualizations
Avoid duplicated values

For example, when analyzing sales:

The sales table stores transaction records.
The customers table provides customer information.
The products table describes the items sold.

why SQL skills are important for Power BI analysts

Power BI is a powerful tool for building reports and dashboards, but it does not replace the need for strong data handling skills. Most business data is stored in SQL databases, and before that data can be visualized in Power BI, it must first be retrieved, cleaned, and structured properly.
SQL skills give Power BI analysts a real edge by providing an easier way to grab just what you need without pulling everything into Power BI.
Without SQL, analysts may rely too much on raw data, which can lead to slow reports, incorrect results, and inefficient workflows.
SQL allows analysts to:

1. Retrieve Data
Analysts can write queries to select specific rows and columns relevant to their analysis from a database.

-- selecting only products name and price columns from products table

SELECT product_name, price
FROM products

Why this matters in Power BI:

Reduces the amount of data imported
Improves performance
Makes the model easier to manage
Avoids unnecessary columns

2. Filter Data
In real-world scenarios, not all data is useful for analysis. Analysts often need to focus on specific time periods, regions, or business conditions. SQL thus makes it easy to filter datasets based on a specific criteria before loading them into Power BI.

-- Retrieving only sales from 2024 onwards.

SELECT *
FROM sales
WHERE sale_date >= '2024-01-01'

Why this matters:

Reduces dataset size
Speeds up report loading
Focuses analysis on relevant data
Avoids unnecessary processing inside Power BI

3. Perform Aggregations
Aggregation is the process of summarizing data. In business analysis, analysts often need totals, averages, counts, and other summary metrics. SQL can summarize large datasets quickly by using functions such as GROUP BY, SUM, COUNT, and AVG.

-- Calculating total sales per product

SELECT product_id, SUM(quantity) AS total_quantity
FROM sales
GROUP BY product_id;

Why aggregation in SQL is important:

Reduces data volume before loading
Improves Power BI performance
Simplifies data models
Avoids heavy calculations in DAX

Preparing data for Analysis

Raw data must be cleaned or transformed before it is ready for visualization.
SQL can be used to:

Joining Tables and Combining Data

Business data is usually stored in multiple tables.
SQL allows analysts to combine these tables using joins. Joined datasets in SQL can simplify the data model.

SELECT c.customer_name, s.sales_amount
FROM customers c
JOIN sales s
ON c.customer_id = s.customer_id;

Why this is important:

Combines related data into one dataset
Reduces the need for complex relationships in Power BI
Makes analysis easier
Prevents duplication errors

Data Cleaning and Preparation

Raw data is often messy. It may contain:

Missing values
Duplicate records
Incorrect formats

SQL helps clean and prepare the data before it is loaded into Power BI hence leading to better insights.

-- Eliminating duplicates

SELECT DISTINCT customer_id
FROM customers;

--Handling missing values

SELECT 
  COALESCE(phone_number, 'Not Provided') AS phone
FROM customers;

Why data cleaning matters:

Ensures data accuracy
Improves report reliability
Reduces cleaning work in Power BI
Prevents errors in calculations

Creating Calculated Fields

SQL allows analysts to create new columns based on existing data.

-- Calculate total sales

SELECT 
  product_name,
  price,
  quantity,
  price * quantity AS total_sales
FROM sales;

Why calculated fields are useful:

Prepares key metrics before loading
Reduces need for DAX calculations
Keeps logic centralized in the database

Supporting Advanced Analysis

SQL also supports more advanced operations such as:

Window functions (running totals, ranking)
Subqueries
Common Table Expressions (CTEs)
Data transformations

SELECT 
  sale_date,
  SUM(sales_amount) OVER (ORDER BY sale_date) AS running_total
FROM sales;

Conclusion

Power BI is a powerful tool that helps organizations transform raw data into meaningful insights. By connecting directly to SQL databases, Power BI allows analysts to access structured data stored in business systems and convert it into interactive dashboards and reports.
SQL prepares the foundation, and Power BI builds the story on top of it. Strong SQL skills allow analysts to work more efficiently, produce accurate reports, and deliver better insights for decision-making.
When SQL and Power BI are used together, they provide a powerful combination for modern data analysis and business intelligence.

Mastering SQL Joins and Window Functions

Lawrence Murithi — Tue, 03 Mar 2026 10:36:56 +0000

Introduction

SQL (Structured Query Language) is a powerful tool used to search, manage, and analyze large amounts of data. It is widely used by data enthusiasts, software developers and even marketing professionals.
In real-world databases, data is not stored in one large table. It is divided into multiple related tables. This makes storage efficient and avoids duplication. To work effectively with such data, you must understand SQL joins and window functions. These two features allow you to combine data correctly and perform advanced analysis without losing important details.

SQL Joins

A JOIN in SQL is used to combine rows from two or more tables based on a related column. This relationship is usually created using:

A primary key (unique identifier in one table)
A foreign key (reference to that key in another table) Joins are essential when working with relational databases because data is often split across multiple tables.

Importance of Joins

Combining related data from multiple tables
Maintaining relational integrity
Supporting one-to-many and many-to-many relationships
Building meaningful reports and analytics
Preventing unnecessary duplication of data

The type of join you use directly affects:

The number of rows returned
Whether NULL values appear
How business logic is interpreted NB: Choosing the wrong join can lead to missing data, duplicated records, or incorrect analysis.

Types of SQL Joins

INNER JOIN

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

Combines records based on a related column
Returns only matching rows
Excludes non-matching rows INNER JOIN is used when:
You only need matched data
You want to exclude incomplete relationships

LEFT (OUTER) JOIN

The LEFT (OUTER) JOIN returns:

All rows from the left table
Matching rows from the right table
NULL values if no match exists LEFT JOIN is used when:
You want all records from the main table
You want to identify missing matches
You need complete reporting from one side

RIGHT (OUTER) JOIN

The RIGHT (OUTER) JOIN returns:

All rows from the right table
Matching rows from the left table
NULL where no match exists on the left NB: RIGHT JOIN works like LEFT JOIN but from the opposite direction.

FULL (OUTER) JOIN

The FULL JOIN returns:

All rows from both tables
Matching records where possible
NULL values where no match exists The FULL JOIN is used when:
Comparing two datasets
Identifying differences between systems
Performing reconciliation tasks

CROSS JOIN

A CROSS JOIN returns all possible combinations of rows thus can create very large results.
If Table A has 5 rows and Table B has 10 rows:
Result = 50 rows.
It does not use a matching condition.

A CROSS JOIN is used to:

Generate combinations
Create calendar expansions
Test scenarios

SELF JOIN

A self join joins a table to itself. Aliases are used to refer to the same tale
Example:
Employee table:
| EmployeeID | ManagerID |
To show each employee and their manager name, the table is joined to itself.

Self joins are useful for hierarchical data.

NATURAL JOIN

A natural join Joins all tables using columns that have the same name.

Performance Considerations for Joins.

Joins can affect performance, especially in large databases.
Best practices:

Index join columns (primary and foreign keys)
Avoid unnecessary joins
Filter data early using WHERE
Understand execution plans
Be careful with joins that multiply rows unintentionally

Improper joins can cause:

Duplicate results
Data inflation
Slow query execution

Window Functions

Keep every row
Add calculated values to each row

Structure of a window function:

SELECT column_1,
       function() OVER (
           PARTITION BY column
           ORDER BY column
       ) AS output_column
FROM table_name;

1. OVER()

The OVER() clause defines how the window function operates and controls:

Partitioning
Ordering
Optional frame boundaries

2. PARTITION BY()

The PARTITION BY divides rows into logical groups. If omitted, the entire dataset is treated as one group.

3. ORDER BY()

ORDER BY defines the sequence of rows inside each partition.

It is essential for:

Ranking
Running totals
Time-based comparisons

If ORDER BY is omitted, row processing order is undefined.

4. Frame Clause (ROWS vs RANGE)

Used to define a range of rows(boundary) and commonly used for moving averages and cumulative calculations.
In the ROWS subclause, the frame is defined by beginning and ending row positions while in the RANGE subclause, the frame is defined by a value range.

ROWS BETWEEN lower_bound AND upper_bound
RANGE BETWEEN lower_bound AND upper_bound

Types of SQL Window Functions

Window functions fall into three main categories.

1. Aggregate Window Functions

These include:

AVG() - Calculates moving averages.
SUM() - Creates running totals.
COUNT() - calculates the number of items found in a group.
MIN() - returns the minimum value.
MAX() - returns the maximum value.

Some use cases of Aggregate window functions include:

Department totals
Running totals
Moving averages
Cumulative metrics

2. Ranking Window Functions

They are used to assign position or rank.

ROW_NUMBER() - Assigns a unique number to each row.
RANK() - Assigns rank with gaps when ties exist.
DENSE_RANK() - Similar to RANK but does not skip numbers and better for ranking reports where gaps are not desired.
PERCENT_RANK() - calculates the relative rank of a row within a group of rows.

Some use cases of Ranking window functions include:

Top N per group
Performance ranking
Leaderboards
Percentile analysis

3. Offset (Value) Window Functions

They are used to access data from other rows.

LAG() - shows previous row value and used in time-based analysis.
LEAD() - shows next row value and used in time-based analysis.
FIRST_VALUE() - returns the first value in an ordered set of values within a partition.
LAST_VALUE() - returns the last value in an ordered set of values within a partition.
NTH_VALUE() - Divides rows into equal groups and useful in performance analysis and segmentation.

Some use cases of Offset window functions are:

Month-over-month growth
Time-series comparison
Trend detection
Sequential analysis

Conclusion

SQL joins and window functions are core tools for designing efficient and powerful queries.
Joins allow you to combine data from multiple tables using defined relationships while Window functions provide an advanced analytical layer in SQL.

The Power of BI; Translating Messy Data, DAX, and Dashboards into Action

Lawrence Murithi — Mon, 09 Feb 2026 17:59:06 +0000

Introduction

In the real world, data rarely comes in a clean and perfect format. Most of the time, it comes from multiple systems, created by different people, and maintained with different rules. It may have missing values, inconsistent naming, or outdated records. Databases may store the same information in different ways. This is where analysts come in. Their work is not just about building reports, but about turning raw, messy data into clear insights that drive real business actions. Power BI is one of the main tools that helps them do this effectively.

Understanding the Business Before Touching the Data

Good analysts first try to understand what problem they want to solve before embarking on the data analysis journey. They ask questions like: What decision needs to be made? Who will use the report? What actions should the dashboard support? Without this context, even the best-looking dashboard can fail.
Understanding who your target is and what their needs are could help analysts decide what data to use, what calculations matter, and what level of detail is required.

Making Sense of Messy Data

Most data comes in a rough and messy state, therefore, a majority of an analyst’s time is spent cleaning and preparing the data to make sure it is reliable. Power BI’s Power Query tool is designed for this task. Analysts use it to load data from many sources such as Excel files, SQL databases, APIs, and cloud platforms. Power Query allows analysts to apply repeatable step-by-step transformations to clean the data.

Common data problems analysts handle

Missing values
Duplicate records that inflate totals
Different spellings or codes for the same category
Incomplete dates or incorrect data types
Columns that mix multiple values in one field

Building a Strong Data Model

After cleaning the data, analysts focus on building a proper data model. This includes defining relationships between tables, choosing the correct granularity, and organizing data in a way that supports accurate analysis.

Characteristics of a well-designed data model

Improves performance
Makes DAX calculations easier
Reduces confusion for report users

Analysts often use star schemas, separating fact tables from dimension tables, to keep the model simple and efficient. This step is invisible to most users, but is critical for reliable results.

Using DAX to Add Meaning to the Data

Raw data alone does not answer business questions. DAX(Data Analysis Expressions), therefore, helps analysts turn raw numbers into useful metrics/insight. Analysts use DAX to create measures that reflect real performance. For example, instead of showing total sales, DAX can show how sales compare over different months or years thus leading to better decision-making.

Examples of Insights obtained using DAX

Year-over-year growth
Employee turnover rate
Running totals and averages
Comparison of current performance to past periods
Percentages, ratios, and growth rates

Building Dashboards That Tell a Story

Once the data and calculations are ready, analysts design dashboards with the end user in mind. Dashboards are not just charts but are tools for communication. They should guide users toward key insights without overwhelming them. The goal is clarity, not complexity. A good dashboard should have the right visuals, avoid clutter and highlight the most important numbers hence telling a clear story. Filters and slicers allow users to explore the data without needing technical skills.
A good dashboard should have:

The right chart type for each metric
Logical layout and flow of information
Clear labels, titles, and tooltips

Good Dashboards help users:

Quickly see what is going well
Spot problems early
Ask better questions
Drill down to find the cause

Turning Insights into Real Business Actions

The real success of a dashboard is determined by the actions it supports. A good dashboard helps teams respond faster and make better decisions. This could include setting targets, tracking performance, or identifying risks early.
Since dashboards can refresh automatically, decisions are based on up-to-date information rather than static reports. Alerts and shared reports also ensure that insights reach the right people at the right time.

Measuring Impact and Improving Over Time

Analyst work does not end after publishing a dashboard. They gather feedback, monitor usage, and refine reports over time. As business needs change, dashboards must evolve with them.

Over time, Power BI reports can show measurable impact, such as reduced costs, improved performance, faster reporting cycles, or better accountability. These outcomes demonstrate how technical skills translate into real business value.

Conclusion

Analysts act as a bridge between raw data and business decisions. They clean messy data, use DAX to add meaning, and design dashboards that help people understand what is happening and what to do next. While Power BI provides the tools, it is the analyst’s understanding of data and business that turns information into action.

The value of Power BI, therefore, is not in the visuals or formulas alone but lies in how analysts use it to support smarter decisions and create measurable impact across the organization.

Schemas and Data Modelling in Power BI

Lawrence Murithi — Tue, 03 Feb 2026 15:19:33 +0000

Introduction

Power BI is a business intelligence tool used to turn raw data into meaningful reports and dashboards. It allows organizations to analyze data, track performance, and make informed decisions. However, the quality of insights produced by Power BI depends heavily on how the data is structured behind the scenes. Good visuals and advanced calculations cannot compensate for poorly designed data.

What are Schemas and Data Modelling

Data modelling is the process of organizing data into tables and defining how those tables relate to each other. A schema is the structure or design of this data model.
Schemas and data modelling define how data is organized, connected, and interpreted in Power BI. They determine how tables relate to each other, how filters flow across the model, and how calculations are performed.

Characteristics of a data model.

A good data model:

Makes reports faster and more responsive
Produces correct totals and calculations
Is easy to understand and maintain
Works naturally with DAX measures and visuals.

A bad data model:

Slows down reports
Produces wrong or inconsistent numbers
Forces complex and hard-to-read DAX formulas
Confuses report users and developers

This article explains schemas and data modelling in Power BI, focusing on:

Star schema
Snowflake schema
Fact and dimension tables
Relationships
Importance of good modelling for performance and accurate reporting

Understanding Data Modelling in Power BI

Data modelling happens after data is loaded from sources such as Excel, databases, or cloud systems. The model is built in the Model view, where tables and relationships are defined.
A Power BI data model usually includes:

Fact tables (business events or measurements)
Dimension tables (descriptive information)
Relationships between tables
A schema design (such as star or snowflake)

Fact Tables and Dimension Tables

Fact tables store measurable business data (i.e) numerical data that you want to analyze and measure.
Characteristics of fact tables:

Very large, with many rows
Contain numeric values used in calculations
Contain keys that link to dimension tables
Grow over time as new transactions are added.

Examples of fact data:

Sales amount
Quantity sold
Profit.

Dimension tables store descriptive information that helps explain the facts. Dimension tables are used for filtering, grouping, and slicing data in reports.

Characteristics of dimension tables:

Smaller than fact tables
Mostly text and categorical data
Used for filtering, grouping, and slicing
Change less frequently than facts.

Examples of dimension data:

Product name
Customer name
Region

Relationships in Power BI

Relationships in Power BI define how tables are connected and how data flows between them. A relationship is usually created between a key column in one table and a matching column in another table. These keys allow Power BI to link descriptive data from dimension tables to numerical data in fact tables. Relationships are mainly defined by cardinality, direction, and status.

Types of Relationships

One-to-Many - This is the most common and recommended relationship where one record in a dimension table matches many records in a fact table.
One-to-One - One row in one table matches one row in another table.
Many-to-One - Many rows in the fact table match one row in the dimension table.
Many-to-Many - multiple rows in one table match multiple rows in another table and is often used when there is no unique key.

Why Relationships Matter

Good relationships:

Ensure correct totals and aggregations
Control how slicers and filters behave
Improve report performance
Make DAX measures simpler and easier to maintain

On the contrary, poorly defined relationships often result in:

Wrong numbers
Missing data in visuals
Confusing filter behavior
Slow reports

Best Practices for Relationships in Power BI

Use one-to-many relationships wherever possible
Connect dimension tables to fact tables, not dimension to dimension
Use numeric surrogate keys instead of text
Avoid unnecessary many-to-many relationships
Use single-direction filtering by default
Keep the model simple and clear

Star Schema

A star schema is the recommended data model in Power BI and consists of:

One central fact table
Multiple dimension tables connected directly to the fact table. A star schema structure looks like a star, with the fact table in the center and dimension tables branching out around it.

Example:

        Date
         |
Product — Sales — Customer
         |
       Region

Benefits of Star Schema

Simple and easy to understand
Faster query performance
Fewer relationships
Easier DAX calculations
Better compatibility with Power BI’s engine.

Power BI can process queries more efficiently because dimension tables are not connected to each other.

Snowflake Schema

A snowflake schema is a more complex version of the star schema. In this structure:

Dimension tables are normalized
Dimension tables are connected to other dimension tables.

Example:

Sales → Product → Category → Department

Benefits of Snowflake Schema

Reduces data redundancy
Useful for very large or complex dimensions

Challenges of Snowflake Schema:

More complex relationships
Slower performance due to extra joins
More complex DAX measures
Harder for users to understand

Why Good Data Modelling is Critical

Performance - Power BI uses an in-memory engine. A clean star schema reduces joins and improves query speed. Poor models can cause reports to load slowly or even fail.
Accurate Reporting - Correct relationships and proper table design ensure that filters and totals behave correctly. Bad modelling often leads to duplicated values or missing data.
Simpler DAX - DAX formulas are easier to write and maintain when the model is simple. Complex schemas often require complicated formulas, which are harder to debug.
Better User Experience - Business users prefer models that are easy to understand. Clear table names, logical relationships, and simple structures help users create reports without confusion.

Common Modelling Mistakes

Using many-to-many relationships unnecessarily
Mixing transactional and lookup data in one table
Using bi-directional relationships everywhere
Not creating a proper date dimension
Loading unnecessary columns

NB: Avoiding these mistakes improves both performance and reliability.

Conclusion

Good data modelling is the foundation of effective Power BI reporting. Visuals and calculations only work well when the underlying model is designed correctly. A clean star schema with clear fact and dimension tables leads to faster performance, accurate results, and reports that are easy to build and maintain.
Good data modelling improves performance, ensures accurate reporting, simplifies DAX, and makes reports easier to use. Without proper modelling, even the best visuals cannot deliver correct insights.

Introduction to Linux for Data Engineers

Lawrence Murithi — Mon, 26 Jan 2026 16:28:35 +0000

Introduction

Linux is one of the most important tools for data engineers. Most data systems today run on Linux servers, including cloud platforms, databases, and big data tools like Hadoop and Spark. Understanding Linux basics is, therefore, a key skill for anyone starting a career in data engineering.

This article introduces Linux in a simple way. It explains why Linux is important for data engineers, shows basic Linux commands, and demonstrates how to create and edit files using Vi and Nano, which are common Linux text editors.

Why Linux for Data Engineers

Linux is important for data engineers for several reasons:

Most data pipelines run on Linux servers
Cloud platforms like AWS, Azure, and Google Cloud use Linux
Tools such as Hadoop, Spark, Airflow, and Kafka are built for Linux
Linux is stable, secure, and efficient for large data processing
Data engineers often work with Log files, Configuration files and Scripts written in Python, SQL, or Bash. Linux makes it easy to manage these files directly from the terminal.

Basic Linux Commands For Beginners

Linux commands are instructions typed in the terminal to tell the operating system what to do, such as creating files, moving between folders, or running programs. They allow users to interact directly with the system in a fast and efficient way. Linux commands help manage files, automate tasks and work effectively on servers, which is essential in data engineering and software development.
Below are some of the beginner linux commands.

ssh root@IP - connects to the server
pwd - Shows current directory
ls - shows all files and folders in the current directory.
cd - Changes directory and cd .. moves back one level
mkdir- Creates a new directory
touch - Creates an empty file
cp - Copies files and cp -r copies folder
mv - Moves or renames files
rm - Deletes a file and rm -r deletes a folder

-cat - display file content

Linux Vi and Nano Text Editors
Linux editors are programs used to create, open, and edit text files directly from the terminal. They are important because many configuration files, scripts, and logs in Linux are text-based. Data engineers and developers often use Linux editors when working on servers where graphical tools are not available.
Some of the common Linux text editors are Vi and Nano.
1. Nano Editor
Nano is a simple and beginner-friendly editor.
To open or create a file with Nano:

nano filename.txt

The command opens the window below.

Other nano commands

Command What it does

Ctrl + O Saves the file
Ctrl + X Exits Nano
Ctrl + G Shows help
Ctrl + W Searches for text
Ctrl + K Cuts (removes) a line
Ctrl + U Pastes a cut line
Ctrl + A Moves cursor to start of line
Ctrl + E Moves cursor to end of line
Ctrl + C Shows current line and column
Ctrl + _ Go to a specific line number

2. Vi/Vim Editor
Vi is a powerful editor and widely used in professional environments.
Vi has 3 main modes:

Normal mode - navigation and commands
Insert mode - typing text
Visual mode - selecting text To open a file in Vi editor:

vi filename.txt

The below window opens when the command is prompted.

Other vi commands

Entering Insert Mode

i - insert before cursor
a - append after cursor
o - open new line below
I - insert at beginning of line
A - append at end of line

Saving and Exiting/quiting

:w - save (write)
:q - quit
:wq or ZZ - save and quit
:q! - quit without saving
:w filename - save as new file

Navigation Commands

h - Move left
l - Move right
j - Move down
k - Move up
gg - Go to start of file
G - Go to end of file
0 - Start of line
$ - End of line

Editing Commands

x - delete character
dd - delete line
yy - copy line
p - paste below
P - paste above
u - undo
Ctrl+r - redo

Conclusion
Linux is a core skill for data engineers because it is used in servers, cloud platforms, and data tools. Basic Linux commands help you move around the system and manage files.
Learning Linux early makes it easier to work with data pipelines, scripts, and production systems.

A Simple beginners Guide to Git & GitHub

Lawrence Murithi — Sun, 25 Jan 2026 12:37:27 +0000

What is Git, GitHub and why version control is important

1. What is Git & GitHub
Git is a distributed version control system (VCS)/tool that helps you save, track, and manage changes in your code. It keeps a history of your work so you can see what changed, when it changed, and who changed it.
Think of Git like a save system for your code, but much better than “Save As”.
GitHub is an online platform where you store Git projects.
GitHub allows you to:

Back up your code
Share code with others
Work on the same project from different computers.

What is version control?
Version control is a system that tracks and manages changes to software code and files over time.

Why is version control important?
Version control helps:

Track changes in your work
Go back to an older version if something breaks
Work safely without fear of losing files
Collaborate with other people on the same project

2. Set Up the Git & GitHub Environments
Step 1: Create a GitHub Account
Sign up on GitHub using you credentials.

Step 2: Install Git
Download Git.
Install the application using the downloaded file.
Open Git Bash (Windows) or Terminal (Mac/Linux).
Configure your identity(Name and Email) to help Git Identify who is making the changes any time the changes are made.
NB: Use the email address used to sign up on GitHub.

git config -global user.name "your name"

git config -global user.email "youremail@example.com

Check to ensure your configuration has been set up.

git config -global --list

Step 3: Connect Git to Your GitHub Account
One of the easiest ways to connect your Git to your GitHub Account is using an SSH key which provides(digital identity) a secure password-less way to connect both to avoid the need for inputing a password every time.
To generate the SSH key on GitBash, run the command:

ssh-keygen -t ed25519 -C "youremail@example.com"

Now, you need to generate an agent; a helper program that holds your key in memory.

eval "$(ssh-agent -s)"

Add the key generated to the agent.

ssh-add ~/.ssh/id_ed25519

Lastly, print the public key and use it to connect your GitHub account.

cat ~/.ssh/id_ed25519.pub

Copy the key, navigate to the SSH and GHG keys on settings in you GitHub account and add a new SSH key.

Finally, authenticate your GitHub account in Git by running this command:

ssh -T git@github.com

3. Set up a Repository in GitHub and Project folder in Git

Create a repository on GitHub
Create a project folder in Git

mkdir Myproject

Initialize Git in your project. Tells Git to start tracking this project.

git init

Add files in your folder. Tells Git which files to track.

git add

Save changes (commit)

git commit -m "your message"

4. Connect your project to GitHub)

git remote add origin https://github.com/yourusername/repositoryname.git

Push code to GitHub (send code from your computer to GitHub) Pushing refers to uploading local commits to a remote server to make your code accessible to others.

git push -u origin main

Pull code from GitHub (send code from GitHub to your computer).

git clone https://github.com/username/repositoryname.git

git pull origin main

clone creates/downloads a new copy on your local computer while pull updates your local files with any changes made.

Check the status of your file.

git status

Checking status shows:

Modified files
New files
Files ready to be committed

Summary Cheatsheet

Conclusion
Git, GitHub, and version control help developers and data professionals manage their work in a clear and organized way. While Git allows users to track changes, save progress through commits, and return to earlier versions when needed, GitHub provides an online platform to store projects, share code, and collaborate with others. Together, they reduce the risk of losing work, make teamwork easier, and support professional development by keeping code history clear and well documented.