Forem: PETER AMORO

ETL Vs ELT

PETER AMORO — Mon, 13 Apr 2026 07:22:41 +0000

ABSTRACT

Data engineering has existed in some form since companies started dealing with with data such as predictive analysis, descriptive analytics and reports to give meaningful insight. This article examines the process of sourcing data which is the extraction, the transformation of data which involves the formatting of data into structured dat i.e cleaning data and the loading of data which involves the ingestion, transformation and serving data into the destination like databases.

INTRODUCTION

In this article, i will take you through the 2 different processes of the data engineering lifecycles which are mainly ETL and ELT and the processes involved in each of them.

ETL (EXTRACTION, TRANSFORMATION, LOADING)

Extraction: This involves getting data from various sources like APIs, IoT devices (IoT short for Internet of Things — these are devices that collect data through sensors, like the fingerprint scanners on doors that collect fingerprints and store them in a central system), databases, flat files like CSVs, and web scraping. The goal of extraction is simply to pull raw data from wherever it lives and bring it into a staging area — think of it like gathering all your ingredients from the fridge before you start cooking. The data at this stage is messy, unorganized, and not yet ready to be used.
Transformation: This is the most critical step in ETL. Once the raw data has been extracted, it is processed and cleaned before being loaded into the destination. Transformation can include:

Cleaning — removing duplicates, fixing typos, handling missing values
Formatting — converting date formats, standardizing column names, changing data types
Filtering — removing data that is not relevant to the business use case
Aggregating — summarizing data, for example calculating total sales per month
Joining — combining data from multiple sources into one unified dataset

This transformation happens in an intermediate processing layer or staging environment, outside the final destination database.
Loading: After the data has been cleaned and transformed, it is loaded into the destination — usually a data warehouse like Amazon Redshift, Google BigQuery, or Snowflake. Because the data is already clean and structured at this point, it is immediately ready for analysts and business intelligence tools to query and use.

ELT (EXTRACTION, LOADING, TRANSFORMATION)

ELT follows the same three steps as ETL but in a different order — the transformation happens after the data has already been loaded into the destination.
Extraction: Same as in ETL — raw data is pulled from various sources such as APIs, databases, IoT devices, and flat files.
Loading: The raw, unprocessed data is loaded directly into the destination — typically a modern cloud data warehouse. These modern warehouses are powerful enough to store and process huge volumes of raw data without needing it to be cleaned first.
Transformation: Once the raw data is sitting inside the data warehouse, transformations are performed there using tools like dbt (data build tool). This approach takes advantage of the processing power of the warehouse itself rather than relying on a separate transformation layer.

Which One Should You Use?
The answer depends on your use case:

Use ETL when you are dealing with sensitive data that must be cleaned and masked before it enters your system, or when working with older, on-premise databases that cannot handle raw data at scale.
Use ELT when you are working with large volumes of data, using a modern cloud data warehouse, and need flexibility to transform data in multiple ways for different teams.

In modern data engineering, ELT has become the more popular approach because cloud warehouses have become powerful and affordable enough to handle transformations internally, making the process faster and more flexible.

CONCLUSION
Both ETL and ELT are fundamental processes in the data engineering lifecycle. They both serve the same ultimate goal — getting clean, reliable data into the hands of the people who need it. The key difference lies in where and when the transformation happens. As a beginner in data engineering, understanding these two processes gives you a solid foundation for working with data pipelines, warehouses, and analytics workflows.

--- title: Understanding SQL Joins and Window Functions published: false tags: sql, database, programming, beginners ---

PETER AMORO — Wed, 08 Apr 2026 13:19:24 +0000

Understanding SQL Joins and Window Functions

Abstract

Relational databases store data across multiple tables to maintain organization and reduce redundancy. Retrieving meaningful insights from these tables often requires combining data and performing analytical operations. Two powerful SQL features that help accomplish this are joins and window functions.

Joins allow developers to combine data from different tables based on relationships between columns. Window functions, on the other hand, enable advanced calculations across sets of rows without collapsing the dataset into grouped results. This article introduces the key concepts behind SQL joins and window functions and demonstrates how they can be used in real-world queries.

Introduction

Structured Query Language (SQL) is the standard language used to interact with relational databases. In most real-world systems, data is not stored in a single table but distributed across multiple related tables.

For example, employee information might exist in one table while department information exists in another. To analyze this data effectively, developers must combine related tables and perform calculations across multiple rows.

SQL provides two powerful mechanisms for this:

Joins, which allow data from multiple tables to be combined.
Window functions, which allow analytical calculations while keeping the original dataset intact.

Understanding these features is essential for writing efficient SQL queries and performing advanced data analysis.

SQL Joins

A join is used to combine rows from two or more tables based on a related column.

Joins are commonly used when information is stored across different tables but needs to be viewed or analyzed together.

INNER JOIN

An INNER JOIN returns only the rows where matching values exist in both tables.

SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments
ON employees.department_id = departments.department_id;

In this query, only employees whose department IDs match entries in the departments table will appear in the result.

LEFT JOIN

A LEFT JOIN returns all rows from the left table and the matching rows from the right table. If no match exists, the result will contain NULL values.

SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.department_id;

This type of join is useful when you want to ensure that all records from the main table are included, even if related data is missing.

RIGHT JOIN

A RIGHT JOIN returns all rows from the right table and the matching rows from the left table.

SELECT employees.name, departments.department_name
FROM employees
RIGHT JOIN departments
ON employees.department_id = departments.department_id;

This ensures that all records from the right table appear in the result.

FULL JOIN

A FULL JOIN returns all rows from both tables. When no match exists between rows, the missing values are represented as NULL.

SELECT employees.name, departments.department_name
FROM employees
FULL JOIN departments
ON employees.department_id = departments.department_id;

This join is useful when you want to view all available records from both tables.

SQL Window Functions

While joins allow us to combine tables, window functions allow us to perform calculations across rows without grouping the data into a single result.

Unlike GROUP BY, which aggregates rows into one output per group, window functions maintain the original rows while adding calculated values.

The general syntax for a window function is:

function_name() OVER (PARTITION BY column ORDER BY column)

Ranking Example

Window functions are often used to rank data. The RANK() function assigns a rank to each row based on a specified order.

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

This query ranks employees based on their salary from highest to lowest.

Using PARTITION BY

PARTITION BY divides rows into groups and applies the window function separately within each group.

SELECT
name,
department_id,
salary,
RANK() OVER (
PARTITION BY department_id
ORDER BY salary DESC
) AS department_rank
FROM employees;

In this example, employees are ranked within their own departments rather than across the entire table.

Running Totals

Window functions can also calculate cumulative values, such as running totals.

SELECT
employee_id,
salary,
SUM(salary) OVER (ORDER BY employee_id) AS running_total
FROM employees;

This query calculates a running total of salaries as the rows progress.

Discussion

Both joins and window functions are widely used in real-world database systems. Joins enable developers to retrieve related data from multiple tables, which is essential for reporting and application development.

Window functions provide powerful analytical capabilities that allow developers and analysts to perform calculations such as ranking, running totals, and comparisons without losing row-level detail.

Together, these SQL features make it possible to build complex queries that provide deeper insights into structured datasets.

Conclusion

SQL joins and window functions are essential tools for working with relational databases. Joins allow data from multiple tables to be combined based on relationships, while window functions enable advanced analytical calculations across rows.

By understanding and applying these concepts, developers and data professionals can write more efficient queries and unlock meaningful insights from their data.

How Analysts Translate Messy Data, DAX, and Dashboards into Action Using Power BI: A Case Study of Kenya Crop Data

PETER AMORO — Mon, 09 Feb 2026 13:58:49 +0000

Abstract

Data-driven decision-making depends on the ability to transform raw, unstructured data into meaningful insights. In practice, datasets are often messy, incomplete, and difficult to interpret without proper analytical tools. This article examines how analysts use Power BI to translate messy agricultural data into actionable insights, using a Kenya crops dataset as a case study. Through data cleaning, modeling, DAX calculations, and dashboard design, Power BI enables analysts to convert complex datasets into interactive visual reports that support informed decision-making in agriculture and policy planning.

1. Introduction

Modern data analysis goes beyond collecting information; it focuses on extracting insights that can guide action. In sectors such as agriculture, where data influences food security, economic planning, and sustainability, accurate analysis is especially critical. However, agricultural datasets are often inconsistent, poorly structured, and difficult to analyze in their raw form.

During practical training with Power BI, a Kenya crops dataset was used to demonstrate how analysts transform messy data into meaningful dashboards. This article explores the analytical process, highlighting the role of data cleaning, DAX (Data Analysis Expressions), and visualization in converting raw agricultural data into actionable insights.

2. Methodology

This is the raw Kenya Crop Data as an excel spreadsheet before its loaded into the Power BI

2.1 Data Preparation

The initial dataset contained information on crop types, production levels, regions, and time periods in Kenya. Before analysis, the data required cleaning and transformation. Power Query in Power BI was used to:

Remove duplicate and missing records
Standardize crop and region names
Rename columns for clarity
Ensure numerical fields were correctly formatted

These steps ensured data consistency and reliability, forming a strong foundation for further analysis.

2.2 Data Modeling and DAX

After cleaning, the dataset was modeled within Power BI to support analytical calculations. DAX was used to create measures such as:

Total Cost of crop production by region

Total Profit

Total Revenue

-Total Yield

DAX enabled dynamic calculations that adjusted automatically when filters and slicers were applied, allowing deeper exploration of the data.

2.3 Dashboard Development

Interactive dashboards were designed to present insights visually. Bar charts, line charts, and geographic maps were used to display trends, regional comparisons, and crop distributions. Slicers allowed users to filter data by crop type, region, or year, making the dashboard accessible to both technical and non-technical users.

3. Discussion

The Kenya crops Power BI dashboard demonstrated how structured analysis turns raw data into insight. Patterns in crop production across regions became immediately visible, enabling comparison and trend identification. Analysts could quickly identify high-performing regions, declining production trends, or dominant crops.

This process highlights the analyst’s role as a translator between data and decision-makers. Rather than presenting raw tables, analysts use DAX and dashboards to communicate insights clearly and efficiently. In agriculture, such insights can inform policy decisions, resource allocation, and long-term planning.

4. Conclusion

Power BI provides analysts with the tools needed to transform messy datasets into actionable intelligence. Through careful data preparation, logical DAX calculations, and effective dashboard design, raw agricultural data can be converted into meaningful insights.

The Kenya crops dataset demonstrated how Power BI supports data-driven decision-making in agriculture. By translating complex data into clear visuals and metrics, analysts enable stakeholders to make informed decisions that impact food security, economic development, and sustainability. Ultimately, the value of Power BI lies not in visualization alone, but in its ability to bridge the gap between data and action.

Getting Started with Linux for Data Engineers: Working with Servers, Permissions and Nano Editor.

PETER AMORO — Mon, 26 Jan 2026 07:17:07 +0000

Abstract

In class i learnt that Linux is the dominant operating system in modern data engineering environments. Most data pipelines, databases and cloud-based data platforms are deployed on Linux servers. This article introduces fundamental Linux concepts for data engineers, presents essential Linux commands in the order they are typically used, and demonstrates how the nano text editor can be applied in practical data engineering tasks.

1. Introduction

Data engineering involves building, maintaining, and optimizing data pipelines that operate in production environments. Linux plays a central role in this ecosystem due to its stability, performance, and extensive command-line tooling. As a result, data engineers are expected to interact with Linux systems directly through the terminal.

2. Why Data Engineers Use Linux

Linux is widely used in data engineering for several reasons:

Most databases and distributed data platforms are optimized for Linux
Powerful command-line tools enable efficient automation
Remote access simplifies server and cloud infrastructure management
Strong support for scripting and scheduling data pipelines

Because of these advantages, Linux proficiency is considered a foundational skill for data engineers.

3. Server Access Using SSH

In real-world environments, data engineers access servers remotely using SSH (Secure Shell).

While logged in as root, create a new user:

Switch from the root user to the newly created user:

Confirm the active user and working directory:

In order to create directories i make my user account a super user

Verify the user is now sudo-enabled

4. Essential Linux Commands in a Typical Workflow

Linux commands are usually executed in a logical sequence during real-world data engineering tasks. The commands below are ordered according to a typical workflow.

5. Checking the Current Directory

creating directories or folders

A directory for organizing project files is created using mkdir:

Listing Files and Directories

Navigating into the Directory

The newly created directory is accessed using:

cd example

Creating Files

An empty file is created using:

touch etl.py

Downloading Data

A dataset is downloaded from the internet using:

wget https://raw.githubusercontent.com/mwaskom/seaborn-data/master/iris.csv

Viewing File Contents

The contents of a file are displayed using:

cat iris.csv

Viewing Large Files

Large files are viewed page by page using:

more iris.csv

Previewing File Data

The first and last lines of a file are viewed using:

head iris.csv

tail iris.csv

Searching Within Files

Specific patterns within files are searched using:

grep petal_width iris.csv

Appending Output to a File

Text is appended to a file using output redirection:

echo ETL job completed successfully >> iris.csv

5. Using the Nano Editor

Nano is a lightweight and beginner-friendly text editor available on most Linux systems. It allows users to edit files directly from the terminal without requiring complex commands or modes.

Opening a File with Nano

Files such as Python scripts, SQL files, or configuration files are opened using:

nano iris.csv

Saving and Exiting Nano

Changes are saved using the appropriate nano shortcut, and the editor is exited safely after editing is complete. Nano displays all available shortcuts at the bottom of the screen, making it easy to use for beginners.

7. Conclusion

This assignment demonstrates foundational Linux skills required for data engineering tasks. By securely accessing a remote server using SSH, managing user accounts, organizing files with Linux commands, and editing scripts using the nano editor, data engineers gain practical experience working in real-world Linux environments. Mastery of these skills provides a strong foundation for working with production data systems.

Learning Git & GitHub as a Data Engineering Student at LuxDevHQ

PETER AMORO — Sat, 17 Jan 2026 15:12:26 +0000

As a student who recently started learning Data Engineering, one of the first tools we were introduced to was Git and GitHub it is one of the most important tools for anyone working with code and data and planning to collaborate with my fellow students at LuxDevHQ

This article explains in a simple way:

What Git and GitHub are
How to set up Git Bash
How to connect Git to GitHub
How to push and pull code
How version control works

What is Git?

Git is a version control system.

It helps you keep track of every change you make to your files and keep track of each version of your code.

This is very important in Data Engineering because we:

Experiment with data
Change code often
Work on long projects

Git makes sure nothing gets lost.

What is GitHub?

GitHub is a website where Git projects are stored online.

It allows you to:

Back up your work
Share your projects
Work with other people
Show your work to employers

Git works on your computer.

GitHub stores your Git projects on the internet.

Installing Git Bash

For my case, i use a macbook so i dont install Git Bash natively so i install git via the terminal.

On macOS

Open Terminal and type

git --version



![ ](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/sjljwj0u29l9t7jhenq8.png)



# Connecting to a GitHub Account

## Step 1: Login or Create a GitHub account., since i have an account i just login 



## Step 2: Configure Git with your details

Run the following commands:

bash
git config --global user.name "Your Name"
git config --global user.email "youremail@example.com"


Confirm the setup:

bash
git config --list


![ ](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/oisj0pdxrst6raqbw6zp.png)


# Connecting Git to GitHub Using SSH

Using **SSH** allows you to push and pull code **without entering your password every time**.

## Generate an SSH key

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


Press **Enter** for all prompts.

## Start the SSH agent and add the key

bash
eval "$(ssh-agent -s)"
ssh-add ~/.ssh/id_ed25519


## Copy the SSH key

bash
pbcopy < ~/.ssh/id_ed25519.pub


## Add the SSH key to GitHub

1. Go to **GitHub Settings**
2. Select **SSH and GPG keys**
3. Click **New SSH key**
4. Paste the key and save

Test the connection:

bash
ssh -T git@github.com


A success message confirms the connection.

![ ](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fk86o2r75v3b6cizf9db.png)


# Understanding Version Control

**Version control** is a system that:

* Tracks **file changes**
* Saves a **history of your project**
* Allows you to **restore previous versions**
* Enables **safe collaboration**

Git works like a **timeline**, recording every meaningful change.

# Creating a Local Git Repository

## Create a project folder

bash
mkdir luxDev
cd luxdev





## Initialize Git

bash
git init

![ ](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/j7iait5xdeax62en3tbo.png)

Git now tracks this folder.

# Tracking Changes with Git

## Check file status

bash
git status


![ ](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ibpj5heslws8dfe5rzol.png)



## Add files to the staging area

bash
git add .


## Commit changes

bash
git commit -m "Initial commit"


A **commit** is a saved snapshot of your project.

# Pushing Code to GitHub

## Create a repository on GitHub

* Click **New Repository**
* Do **not** add a README file
* Copy the **SSH repository link**

![ ](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fb673jj5ont71p5lhrsi.png)


## Connect the local project to GitHub

bash
git remote add origin git@github.com:username/repository-name.git


## Push code to GitHub

bash
git branch -M main
git push -u origin main


Your code is now available on **GitHub**.

# Pulling Code from GitHub

To download updates from GitHub:

bash
git pull origin main


This is useful when:

* Working on **multiple devices**
* Collaborating with **other developers**
* Updating **shared projects**

# Viewing Project History

bash
git log




This command shows **previous commits**, authors, and dates.