Forem: Rachel Muriuki

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

Rachel Muriuki — Sun, 12 Apr 2026 14:57:50 +0000

Introduction

In today’s world, organizations generate massive amounts of data every second from website clicks, banking transactions and social media interactions. However, raw data on its own is usually messy, inconsistent and difficult to use for decision-making. This is where data integration processes come in.
Data integration processes such as ELT and ETL combine data from different sources to be used for analysis and decision making.

1.What is ETL?

ETL (Extract, Transform, Load) is the traditional approach used to integrate data where data is extracted, cleaned and transformed before storing only the final result

How It Works

Extract

Data, often messy and inconsistent, is pulled from multiple sources:

Databases - Structured Systems like MySQL, PostgreSQL, Oracle that store and organize business data such as customers, orders, transactions
APIs - Used to pull data from external services such as payment systems, social media platforms, weather services
Files - Static data stored in files such as CSV, Excel, JSON or XML files
Logs - Automatic generated systems records such as website page visits, login attempts

Transform

Data is cleaned, filtered and aggregated before it is stored:

Removing duplicates
Handling missing values
Standardizing formats (eg. currency, dates)
Filtering unnecessary data
Creating new calculated fields (eg. profit = revenue - cost)
Joining data from multiple sources
Aggregating data (eg. total sales per day)
Validating data - to ensure it's correct & consistent

Load

The transfomed (cleaned and structured) data is moved into a storage system such as a data warehouse(eg. Snowflake, BigQuery, Redshift) , datalake or data marts, making it available for analysis, reporting and decision making.

Types of loading

Full load-It's mostly used during the initial setup of a system whereby the entire dataset is loaded into the system from scratch
Example: Moving all historical sales data into a new warehouse for the first time
Incremental load-Only new or changed data is loaded after the initial load
Example: Only today's new bank transactions are added to the warehouse instead of reloading all records
Batch loading-Data is loaded at scheduled intervals(hourly, daily, weekly)
Example: An e-commerce company updates sales dashboards every night
Real-time load-Data is loaded continuously as it is generated
Example: Fraud detection systems updating transaction data instantly

Example of ETL

A bank collects customer details, transactions and card payments then verifies accuracy, cleans errors, ensures compliance and finally loads only trusted data into its system.

Why ETL was popular

Older systems had limited storage
Data warehouses were expensive
Data had to be cleaned before storing

Advantages of ETL

High data quality
Only cleaned, validated data is stored, reducing errors and inconsistencies in analytics
Strong data governance
Rules implemented during transformation ensure compliance, security and controlled data flow into storage systems
Optimized storage usage
Only processed and necessary data is loaded, reducing storage of raw or irrelevant information
Faster analytics performance
Since data is already transformed and structured before loading, queries run faster
Suitable for regulatory environments
Industries like finance and healthcare benefit because ETL supports strict validation and compliance before storage.

Disadvantages of ETL

Slower
Data must be fully transformed before loading, causing delays in accessing information
Limited flexibility for analysis
Since raw data is not stored, redoing analysis with new logic often requires re-running the entire ETL process
High pipeline complexity
ETL workflows require carefully designed transformation logic, making system design more complex
Difficult to scale with Big Data
As data volume grows, transformation becomes an obstacle before loading, slowing down the entire pipeline.
Loss of raw data
Because only transformed data is stored, original raw data may be unavailable for future analysis
Expensive
Can be costly to maintain over time

2.What is ELT?

ELT (Extract, Load, Transform) is a modern approach mostly used in cloud data platforms where raw data is first extracted, loaded into a storage system, then transformed later when needed.

How It Works

Extract

Pulls raw data from different sources:

APIs eg. payment systems, weather services, social media platforms
Databases eg. users, orders, inventory systems
Logs - eg. session activity, uptime, system performance data
Files eg. CSV, Excel, JSON

Load

Stores raw data that contains duplicates, missing values, inconsistencies directly into a cloud data warehouse (eg. Snowflake, BigQuery, Redshift) or data lakes (eg. Amazon S3, Google cloud storage, Azure data lake)

Transform

Data is cleaned and processed inside the data warehouse using tools such as SQL, dbt(data build tool), python based workflows, built-in warehouse functions.
Common transformations include:

Filtering unnecessary data
Joining multiple datasets
Aggregating metrics
Building dashboards
Cleaning missing or invalid values

Example of ELT

A streaming platform like Netflix collect watch history, clicks, searches, device data then loads everything into a warehouse. Analysts later decide what to clean and analyze. This helps them run experiments and build recommendation systems

Why ELT became popular

ELT grew with cloud platforms because:

Storage became extremely cheap
Warehouses became powerful enough to process raw data quickly
Tools like dbt made transformation easier after loading
Cloud platforms removed hardware limitations
Businesses shifted toward real time analytics

Advantages of ELT

Faster data ingestion
ELT loads data immediately without transformation delays
Highly flexible
Raw data is preserved allowing analysts to interpret new transformations and insights at any time
Easily scalable
Cloud warehouses can handle massive volumes of raw data, making ELT ideal for large scale modern systems
Preserves raw data
Since data is stored in its original form, it can be reprocessed later with new business requirements
Ideal for Machine Learning & AI
Raw datasets help train models and build predictive systems
Works well with cloud architecture
ELT is optimized for cloud platforms that support distributed computing and scalable storage.

Disadvantages of ELT

Requires powerful storage systems
ELT depends on powerful cloud warehouses capable of handling large volumes of unprocessed data.
Risk of messy data
Since raw data is stored directly, poor governance can lead to inconsistent datasets
Higher responsibility for data governance
Data quality is not enforced beforehand, so organizations must manage standards after loading
Security challenges
Sensitive raw data may be stored before filtering, requiring strict access control
Increased costs*
Storing large volumes of raw data and repeatedly transforming it can increase cloud compute costs

When to use ETL

1.When data must be clean before storage
Used when data needs to be validated, standardized and verified before it is stored, ensuring only accurate and trusted data enters the system.
2.In Highly Regulated Industries
It's ideal for industries like finance, healthcare and government where strict compliance rules require data to be cleaned and audited before storage
3.When data accuracy is critical
If business decisions rely on highly accurate and consistent data, ETL ensures errors are removed early in the pipeline
4.When using traditional data warehouses
Older data warehouses often require structured and processed data before loading, making ETL the preferred approach
5.When storage is limited or expensive
It reduces storage usage by filtering out unnecessary or redundant data before loading, which is useful in systems with storage limitations
6.For Batch Processing Workflows
It works well when data is processed at scheduled intervals (e.g., daily or weekly) rather than needing real-time updates
7.When you need strong data governance
ETL enforces strict control over data flow, making it easier to manage data quality, security and consistency across the organization

When to use ELT

1.Big data & analytics companies
Organizations handling massive datasets benefit from ELT scalability
2.Data science & machine learning teams
ELT provides raw, unmodified data needed for training models and experimentation
3.Cloud-Native Systems
Platforms like Snowflake, BigQuery and Redshift are designed specifically for ELT workflows
4.Real-Time analytics
Companies needing fast data availability for dashboards and monitoring systems
5.Businesses with evolving requirements
When data questions change frequently and flexibility is important

Conclusion

ETL and ELT each serve different purposes, and the right choice depends on your needs. ETL is best when data quality, structure and control are required before storage while ELT is ideal for flexibility, scalability and modern cloud-based analytics.
As data continues to grow, many organizations combine both approaches to balance control with flexibility.

Mastering Joins and Window Functions in SQL

Rachel Muriuki — Tue, 03 Mar 2026 18:54:45 +0000

Joins in SQL

Joins allow one to combine rows from two or more tables based a related column. This is important when data is stored across multiple tables. There are several types of joins:

a.INNER JOIN

-Returns only the matching rows from both tables.
Syntax

SELECT columns
FROM table1
INNER JOIN table2
ON table1.common_column = table2.common_column;

Example

SELECT c.customer_id, c.first_name, o.order_id,o.quantity
FROM customers AS C
INNER JOIN orders AS O
ON c.customer_id = o.customer_id;

This displays only customers who have placed an order.

b.LEFT JOIN (LEFT OUTER JOIN)

-Returns all rows from the left table and only matching rows from the right table.
-If there's no match, NULL is returned on the right table columns.
Syntax

SELECT columns
FROM table1
LEFT JOIN table2
ON table1.common_column = table2.common_column;

Example

SELECT c.customer_id, c.first_name, o.order_id, o.quantity
FROM customers AS C
LEFT JOIN orders AS O
ON c.customer_id = o.customer_id;

This displays all customers even those who didn't place an order.

c.RIGHT JOIN (RIGHT OUTER JOIN)

-Returns all rows from the right table and only matching rows from the left table.
-If there'so match, NULL appears on the left table
Syntax

SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.common_column = table2.common_column;

Example

SELECT c.customer_id, c.first_name,o.order_id,o.quantity
FROM customers AS c
RIGHT JOIN orders AS O
ON c.customer_id = o.customer_id;

This displays all orders, even if there's no matching customer

d.FULL OUTER JOIN

-Returns all rows from both tables, matching where possible and filling NULL when there's no match.
Syntax

SELECT columns
FROM table1
FULL JOIN table2
ON table1.common_column = table2.common_column;

Example

SELECT c.customer_id, c.first_name, o.order_id, o.quantity
FROM customers AS C
FULL OUTER JOIN orders AS O
ON c.customer_id = o.customer_id;

This displays all customers and orders, even unmatched ones.

e.CROSS JOIN

-Returns all possible combinations of both tables, where every row from one table is paired with every row from the other table.
Syntax

SELECT columns
FROM table1
CROSS JOIN table2;

Example

SELECT c.first_name, o.order_id
FROM customers AS C
CROSS JOIN orders AS O;

f.SELF JOIN

-Joins the same table to itself, to compare rows within the same table.
Syntax

SELECT a.column, b.column2
FROM table_name a
JOIN table_name b
ON a.common_column = b.related_column

Example

SELECT 
    c1.customer_name AS customer, 
    c2.customer_name AS referred_by
FROM customers c1
JOIN customers c2
ON c1.referred_by = c2.customer_id;

c1 - represents the customer who was referred
c2 - represents the customer who did the referring
This finds the rows where the referred_by column of one customer (c1) matches the customer_id of another (c2)

Window Functions in SQL

Window Functions perform calculations across a set of rows that are related to the current row.
They are three types of window functions:

Aggregate window functions
Ranking window functions
Value/Analytical window functions

Syntax

SELECT columns
     windowfunction_name(expression)
     OVER (
          [PARTITION BY column_name]
          [ORDER BY column_name]
FROM table_name;
)

Partition By:-Divides the data into groups
Order By:-Specifies the order of rows within each group

1.Aggregate Window Functions

-They compute totals, average, counts across rows.

MIN()

-Shows the smallest value within a window

SELECT customer_id, order_id, total_amount,
     MIN(total_amount) 
     OVER(
        PARTITION BY customer_id) AS smallest_order,
FROM orders;

MAX()

-Shows the largest value within a window

SELECT customer_id, order_id, total_amount,
     MAX(total_amount) 
     OVER(
        PARTITION BY customer_id) AS biggest_order,
FROM orders;

COUNT()

-Counts rows but keeps all rows visible
-Used for showing how many records are in a group without GROUP BY

SELECT customer_id,
    COUNT(order_id) 
    OVER(
       PARTITION BY customer_id) AS total_orders
FROM orders;

This shows how many orders each customer has made

AVG()

-Gives the average value for a window of rows
-Used to find average spend, average score, etc.

SELECT customer_id, order_id, total_amount,
    AVG(total_amount) 
    OVER(
       PARTITION BY customer_id) AS avg_spending
FROM orders;

This shows the customer's average order value

SUM()

-Adds up values without grouping the rows
-Used for running totals or cumulative sums

SELECT customer_id, order_id,
    SUM(total_amount) 
    OVER (
       PARTITION BY customer_id 
       ORDER BY order_date) AS running_total
FROM orders;

This calculates running totals per customer

2.Ranking Window Functions

-They assign ranks, row numbers or categories to each row within its partition.
-Examples include

ROW_NUMBER()

-Assigns a unique number to each row
-Used to find the first, second, third item in a list

SELECT customer_id, order_date,
     ROW_NUMBER() 
     OVER (
        PARTITION BY customer_id 
        ORDER BY order_date) AS order_number
FROM orders;

In this example, each customer's first order gets 1, second gets 2...

RANK()

-Gives a rank number based on order, but skips numbers when there are ties
-Used for ranking by price, score, sales

SELECT customer_id, total_amount,
    RANK() 
    OVER(
       PARTITION BY customer_id 
       ORDER BY total_amount DESC) AS order_rank
FROM orders;

In this example, if two orders have the same amount, they both get the same rank and the next rank is skipped eg.1,2,2,4

DENSE_RANK()

-Similar to RANK() but does not skip numbers
-Used for ranking when you want continuous ranks eg.(1,2,2,3)

SELECT customer_id, total_amount,
    DENSE_RANK() 
    OVER (
       PARTITION BY customer_id 
       ORDER BY total_amount DESC) AS dense_rank
FROM orders;

NTILE()

-Divides rows into n equal groups
-Used for dividing data into quartiles or percentiles

SELECT order_id, total_amount,
    NTILE(4) 
    OVER (
       ORDER BY total_amount DESC) AS quartile
FROM orders;

*In this example, orders are divided into 4 groups(top 25%, next 25%...)

3.Value/Analytical Window Functions

LAG()

-Looks at the previous row's value
-Used for comparing current and previous rows

SELECT customer_id, order_date, total_amount,
    LAG(total_amount) 
    OVER(
       PARTITION BY customer_id 
       ORDER BY order_date) AS prev_order
FROM orders;

This example shows what the previous order's amount was for each customer

LEAD()

-Looks at the next row's value
-Used for comparing to the next record

SELECT customer_id, order_date, total_amount,
      LEAD(total_amount) 
      OVER(
         PARTITION BY customer_id 
         ORDER BY order_date) AS next_order
FROM orders;

This example shows what the next order's amount will be

Conclusion

Joins help us bring related data together from different tables while window functions allow us to perform advanced calculations like rankings and totals.

Transforming Messy Data into Reliable Insights Using Power BI

Rachel Muriuki — Thu, 12 Feb 2026 09:17:17 +0000

How Analysts translate messy data, DAX and dashboards into action using Power BI

In every organization, data is being generated from sales transactions, website clicks, customer feedback. However this data is often inconsistent, incomplete and scattered across multiple systems. An analyst can turn this messy data into insights that drive business actions using Power BI.

1.Cleaning and Organizing Messy Data

Since data often arrives being messy, analysts perform a process known as ETL in Power Query. It consists of;
-Extracting data from multiple sources (Excel files, SQL databases)
-Transforming it through cleaning, structuring data, adding logic
-Loading it to Power BI for visualizing

2.Turning numbers into logic

Analysts use DAX(Data Analysis Expressions) to create calculations that turn raw numbers into meaningful insights
Example;

Customer Retention Rate

Using filters and time intelligence functions, analysts can calculate how many customers make repeat purchases

3.Designing dashboards that drive decisions

Dashboards are visual tools that show data in charts and graphs
A good dashboard:
-Highlights KPIs(Key Performance Indicators) which are the most important numbers
-Allows one to click on charts to see details by region, product or time
-Tells a story that guides the user to derive insights and make actions

4.From insight to actions

Creating visuals helps businesses take action.
How skills in Power BI translate into real impact:
-Data Cleaning-Improves accuracy of reports and decisions
-DAX Calculations-Quantifies performance, identifies trends and reveals root causes
-Interactive Dashboards-Helps leaders see trends and act fast
-Data modelling-Combines data from different departments into one view

5.Turning data into decisions

Analysts take messy data, organize it, calculate key metrics and build dashboards that show managers what's working and what's not.

Conclusion

Power BI is more than a reporting tool. It transforms data into insights that help organization measure results, understand key drivers and take informed actions.

SCHEMAS AND DATA MODELLING IN POWER BI

Rachel Muriuki — Mon, 02 Feb 2026 11:42:15 +0000

PowerBI is a powerful tool for turning raw data into insightful reports.
In power BI tables show what data you have, relationships show how it connects and schema shows why it all fits together.

Data Model

A data model defines how your tables connect and interact in Power BI. It tells Power BI:
-Which tables hold raw facts eg. Sales
-Which tables describe those facts eg. Products

Fact Tables

They store measurable, quantitative data such as Sales transactions.
They contain numeric columns like Revenue, Quantity and contain foreign keys such as ProductID, CustomerID.

Dimension Tables

They contain descriptive textual information that adds context to facts
Examples of such columns:
-Customers
-ProductName
-Category

Relationships

PowerBI connects fact and dimension tables through relationships using shared keys like ProductID

Types oF relationships:

i)One-to-Many

One record in a dimension connects to many in a fact table
Example: One Customer can make many orders

ii)Many-to-One

Multiple records in one table are connected to a single record in another table
Example:Multiple cities belong to one state

iii)Many-to-Many

Used when both tables can have repeating values
Example: A student can take many courses and each course has many students

Schemas

Schema is how your tables are arranged and linked together

Star Schema

It's simple, looks like a star and yet powerful
One central fact table (eg. Sales) connects directly to dimension tables (eg.Customers, Products, Regions)
It is easy to understand, performs fast, ideal for slicing and filtering

Snowflake Schema

It adds extra detail tables branching off the dimensions
Used to avoid repeating data and save space
The only limitations are that it involves more tables and relationships, resulting to slow performance.

Why Good Modelling Matters

It ensures accurate visuals and measures that show correct results
It simplifies DAX queries and reduces processing time
It's easy to add new data sources later
It offers clarity to anyone reviewing your model
It's easy to maintain incase of issues

A well designed schema ensures reports load quickly, calculations stay accurate and business users can explore data confidently.

SCHEMAS AND DATA MODELLING IN POWER BI

Rachel Muriuki — Mon, 02 Feb 2026 11:42:15 +0000

PowerBI is a powerful tool for turning raw data into insightful reports.
In power BI tables show what data you have, relationships show how it connects and schema shows why it all fits together.

Data Model

A data model defines how your tables connect and interact in Power BI. It tells Power BI:
-Which tables hold raw facts eg. Sales
-Which tables describe those facts eg. Products

Fact Tables

They store measurable, quantitative data such as Sales transactions.
They contain numeric columns like Revenue, Quantity and contain foreign keys such as ProductID, CustomerID.

Dimension Tables

They contain descriptive textual information that adds context to facts
Examples of such columns:
-Customers
-ProductName
-Category

Relationships

PowerBI connects fact and dimension tables through relationships using shared keys like ProductID

Types oF relationships:

i)One-to-Many

One record in a dimension connects to many in a fact table
Example: One Customer can make many orders

ii)Many-to-One

Multiple records in one table are connected to a single record in another table
Example:Multiple cities belong to one state

iii)Many-to-Many

Used when both tables can have repeating values
Example: A student can take many courses and each course has many students

Schemas

Schema is how your tables are arranged and linked together

Star Schema

Snowflake Schema

Why Good Modelling Matters

A well designed schema ensures reports load quickly, calculations stay accurate and business users can explore data confidently.

Intoduction to MS Excel for Data Analytics

Rachel Muriuki — Sun, 25 Jan 2026 15:13:02 +0000

Microsoft Éxcel is one of the most beginner friendly tools for cleaning, visualizing and analyzing data.
In this article, we'll go through the basics of using Excel for data analytics.

Step 1: Importing and Viewing Data

Loading your dataset into Excel can be done into two ways;

Copy and paste data directly into a worksheet
Go to Data → Get Data → From Text/CSV → Select file to import data from → Load dataset

Each column represents a variable (like "ID", "Name" or "Gender") and each row represents a record.

Step 2: Cleaning Data

Raw data is usually messy, containing missing values, duplicates, data type errors, extra spaces or wrong alignment. Excel has simple tool to handle that:

Remove Duplicates:

Select your whole data(Ctrl+A) → Go to Data → Remove Duplicates

Data Types:

Select column → Right Click → Format Cells

*Unique numbers such as ID, Phone-numbers, Postal addresses should be formatted as Texts.

Alignment:

Numbers and Dates should be right-aligned while Texts should be left-aligned

Step 3:Exploring with Sorting and Filtering

Sorting:Arrange data in ascending/descending order.

Select column → Go to Home → Sort & Filter → Sort A to Z or Sort Z to A

Filtering:Allows one to display only the rows that meet a certain criteria.

Select column → Go to Home → Sort & Filter → Turn on Filter, then select drop-down arrow to show only relevant records

To replace blanks;
Select column → Go to Data → Under Data Tools → Click Data Validation → Set Allow to List → In the source box, type the values you want to allow(separated by commas) → Click OK

This prevents invalid data entry or flags errors during processing

Step 4: Using Basic Function Formulas

The formulas let you summarize and analyze data efficiently
Functions are grouped into;

i)Text Functions

They're used to manipulate, extract or clean text in cells.
=LEFT(text,num_chars)
num_chars returns the first characters from a text string

Other useful text functions: RIGHT(),MID(),LEN(),TRIM(),PROPER(),UPPER(),LOWER()

ii)Aggregate Functions

They perform calculations on a range of numbers.
=SUM(range) -Adds up all numbers in a range
=AVERAGE(range) -Finds the mean value of numbers in a range

Other useful aggregate functions: MAX(),MIN(),COUNT()

iii)Date & Time Functions

They allow you to calculate, format or extract information from dates and times
=TODAY() -Returns today's date
=DATEDIF(start_date,end_date,"unit") -Calculates the difference between two dates in days, months or years

Other useful functions: NOW(),DAY(),MONTH(),YEAR()

Step 5:Summarizing & Visualizaing the Data

Visual data such as charts help us visualize trends or comparisons
i)Select your dataset
ii)Go to Insert → PivotTable → From Table/Range → Click on a New Worksheet → OK
iii)On the PivotTable Fields → Drag fields into Rows, Columns and values
iv)Go to Insert → Recommended Charts
v)Choose from column, line, pie or bar charts

Getting Started with Git and GitHub using SSH Keys

Rachel Muriuki — Sun, 18 Jan 2026 08:31:17 +0000

If you're just starting your developer journey, learning Git and Github is one of the best decisions you can make.
In this article, i'll show you how to set up Git, configure it with your name & email and connect it securely to GitHub using SSH.

What is Git?

Git is a version control tool that runs locally on your computer.
It let's you;
-Track file changes
-Create 'checkpoints' called commits
-Work on branches (separate copy) without affecting the main project

What is GitHub

GitHub is a cloud platform where you can store your Git repositories online, share them with others and collaborate on projects.
It let's you;
-Upload your code(push)
-Download updates(pull)
-Work together with others in real time

1.Setting up Git

How to install and configure Git.

For Windows

Download Git (Install for Windows)
Run the installer and choose "Git default editor" as your terminal
Once installed, open Git Bash and type:

git --version

You should see something like:

git version 2.xx.x

For macOS

Open terminal and run:

git --version

If Git isn't installed, macOS will prompt you to install it via Xcode tools.

For Linux (Ubuntu/Debian)

Run:

sudo apt update
sudo apt install git

2.Configure your Git identity

Before you start using Git, tell it who you are by using your name and email which you used to create your GitHub account.
Run these commands replacing with your details:

git config --global user.name "Your Name"
git config --global user.email "Your Email"

To confirm your details:

git config --global --list

3.Connecting Git to GitHub Using SSH Keys

SSH(Secure Shell) Keys let you connect to GitHub without typing your password every time.
How to set up:

(i).Generate a New SSH key

ssh-keygen -t ed25519 -C "Your Email"

Press Enter to save it in the default path.

(ii).Generate SSH Agent

eval "$(ssh-agent -s)"

(iii).Add Key to the Agent

ssh-add ~/.ssh/id_ed25519

(iv).Copy your Public Key

cat ~/.ssh.id_ed25519.pub

Copy the whole key that starts with

ssh-ed25519

(v).Add the Key to GitHub

1.Go to GitHub → Settings → SSH and GPG keys
2.Click New SSH key
3.Paste your copied key
4.Save it

(vi).Test the Connection

ssh -T git@github.com

If you see:

Hi yourusername! You've successfully authenticated.

You're all set.

4.Using Version Control with Git

Initialize a Repository

Go to your project folder and intialize Git:

git init

This creates a hidden .gitfolder.

Track changes

Check what's changed:

git status

Add files to be tracked:

git add.

Commit (save) the changes:

git commit -m "Initial commit"

Push code to GitHub

1.Create a new repository on GitHub
2.Copy the repo's SSH url
3.Connect it to your local repo:

git remote add origin git@github.com:username/repo.git

4.Push your code:

git push -u origin main

Now your project is on GitHub.

Pull updates from GitHub

When you make changes on GitHub, pull them locally:

git pull origin main

See your commit history

To view your project's history:

git log

To see a short summary:

git log --oneline