DEV Community: Lawrence Murithi

Folders, Apartments, and Fake Computers: A Guide to Virtual Environments, Docker, and VMs

Lawrence Murithi — Thu, 07 May 2026 12:23:35 +0000

Introduction

If you have been spending a substantial amount of time writing code, you must have run into a frustrating problem: "It works on my computer, but it doesn't work on yours."
This happens because computers are set up differently. You might have a different operating system, a different version of a programming language, or different background software running. When a website or app breaks because of this, developers can lose hours or even days trying to figure out what the problem is.
To solve this, developers came up with ways to isolate software. Instead of installing an app directly onto your main computer, you put it inside a protective bubble. This bubble tricks the software into thinking it has its own private space, with exactly what it needs to run, so it won't mess with the rest of your system.
There are three main tools we use to create these bubbles; Virtual Environments, Virtual Machines (VMs) and Docker. While they all aim to solve similar problems, they do it in completely different ways, using completely different layers of your computer.
Let's break down exactly what each one is, how they compare and when you should use them.

1. Virtual Environments

A Virtual Environment is a localized directory that contains a specific version of a programming language and the specific software packages required for a project. It is the simplest and lightest way to isolate a project and is most commonly used in Python (using tools like venv or virtualenv) although similar concepts exist in other languages.

How Virtual Environments work

A Virtual Environment provides no system-level isolation. It does not share hardware, nor does it isolate the OS. It simply changes the PATH variables in your terminal so that when you install a package or run a script, it uses the isolated folder instead of the computer's global system files.
Imagine you are building two different websites on your laptop. Website A is older and needs version 2.0 of a web framework like Django. Website B is brand new and needs version 4.0 of that exact same framework. If you install these tools directly onto your main computer system, they will conflict and one of your websites will stop working.
A virtual environment fixes this by creating a dedicated, private folder for your project. When you turn on(activate) the virtual, it temporarily rewrites your computer's internal GPS, known as the system PATH. Because of this, your computer temporarily ignores its main, global list of tools. Instead, it only looks at the tools installed inside that specific project folder.

Pros

• Extremely fast - Creating and starting a virtual environment takes less than a second because it is just moving some folders around.
• Lightweight - It only takes up a few megabytes of space on your hard drive. There is no heavy software running in the background.
• Simple to use - Usually, it just takes one or two simple commands in your terminal to get started and shut down.
• No dependency conflicts - it solves the problem of dependency conflicts between different projects

Cons

• Weak isolation - It only isolates programming packages (like Python libraries). It does not isolate the operating system, the system clock, or your hardware settings.
• "It works on my machine" can still happen - Because the isolation is weak, hidden problems can sometimes slip through. If your code secretly relies on a specific font or a hidden system tool installed on your Mac, and you send your virtual environment code to a friend on a Windows PC, the code might still break.

Virtual environments are used on local computer on day to day coding when working on multiple projects using the same programming language but want to keep their dependencies separate from one another.

2. Virtual Machines (VMs)

A Virtual Machine is a complete software emulation of a physical computer. It runs its own full Operating System (Guest OS) entirely separate from the host computer's Operating System. It is the heaviest, most complete, and oldest form of isolation. Software like VirtualBox, VMware, or Microsoft Hyper-V allows you to do this.

How Virtual Machines work

If a virtual environment is like putting your code in a separate folder, a Virtual Machine is like buying an entirely new physical computer, shrinking it down, and putting it inside your current computer.
It uses a piece of software called a Hypervisor(like VMware, VirtualBox, or Hyper-V). The hypervisor carves out a specific amount of your physical computer's RAM, CPU, and storage and dedicates it to the VM. You then install a full Operating System (like Windows or Ubuntu) onto that carved-out space. This new system is called the Guest OS which operates/behaves like a real computer while the main computer is called the Host.

Pros

• Complete isolation - What happens inside a VM stays inside a VM. Because the hypervisor locks the hardware, if a VM gets infected with a severe virus, your main host computer is almost always completely safe.
• Run different operating systems - You can run a full Windows computer inside a Mac, or a Linux computer inside Windows, allowing you to use software made for different platforms.
• Highly secure - Because the hardware is strictly separated at a deep level, it is trusted by banks, governments, and massive corporations for highly sensitive tasks.

Cons

• Massive resource hog - Since you are running a second operating system on top of your current one, VMs eat up a lot of RAM, CPU power, and battery life. Even if the VM is just sitting idle, it is still running background updates, managing a clock, and keeping a digital desktop alive hence wasting power.
• Huge files - A VM can easily take up 20 to 100 gigabytes of storage space just to hold the basic operating system files.
• Slow - Booting up a VM takes just as long as turning on a physical computer, and moving files in and out of it can be tedious.

VMs are used in large corporate cloud servers or on a local machine when strict security is needed. Its critical when you need to test software on a completely different operating system, or when a business is running older, legacy applications that require an outdated OS to survive.

3. Docker (Containers)

Docker is a platform that uses containerization to package an application and all its necessary dependencies (libraries, frameworks, etc.) into a single, standardized unit called a container. Containers are the clever middle ground between the lightness of a Virtual Environment and the strict, heavy isolation of a Virtual Machine.

How docker work

Every operating system is made of two main parts; the core engine (Kernel), which physically tells your RAM and CPU what to do, and the user files/tools that make up the desktop experience you see on screen.
While a Virtual Machine duplicates both parts making it so heavy, Docker only duplicates the user files and tools. All Docker containers share the main host computer's Kernel.
Think of it like an apartment building. A Virtual Machine is like giving everyone their own separate house with their own separate plumbing and electricity. Docker is like an apartment complex where everyone has their own locked, private room(container) and can decorate however they want, but they all share the building's central plumbing and electrical systems hidden in the walls(Host OS Kernel).

To use Docker, you write a simple text file called a Dockerfile. It reads like a recipe; Start with a bare-bones version of Linux, set up some default database passwords, download the latest PostgreSQL and start the database server. Docker reads this file and packages it into a container. This container can be handed to anyone, and it will run exactly the same way, regardless of what computer they have.

Pros

• Consistent everywhere - It solves the "it works on my machine" problem perfectly. A Docker container behaves exactly the same on a Mac, a Windows PC or a cloud server because the environment inside the container never changes.
• Fast and lightweight - Because they don't boot up a full operating system kernel, containers start in seconds and usually only take up a few hundred megabytes of space.
• Easy to share and scale - You can run dozens or even hundreds of containers on the same computer without them fighting over resources. This allows developers to build microservices. Instead of building one massive app, you put the shopping cart in one container, the user login in another, and the payment system in a third. If the payment container crashes, the rest of the website stays up.

Cons

• Steeper learning curve - You have to learn Docker-specific terminal commands, how to write Dockerfiles and how networking works to let containers talk to each other.
• OS limitations - Because Docker shares the host's kernel, you generally run Linux containers on Linux machines. Although Linux can run containers on Mac and Windows, Docker usually installs a tiny, hidden Linux Virtual Machine in the background to provide the Linux Kernel making Docker slightly heavier on Mac and Windows than it is on native Linux.
• Less secure than VMs - Because containers share the host kernel, the wall between them is thinner hence a critical vulnerability in the host OS could potentially affect all containers.

Docker is used almost everywhere. On a developer's laptop, in automated testing environments, and in production running live websites on the open internet. Its used when building modern web applications, working with a team of developers who all use different computers, or breaking a large app down into smaller microservices.
It gives developer's an isolated, highly reliable environment that is identical across all machines, without wasting your computer's RAM and hard drive space.

Similarities between the tools

The core similarity between all three is the concept of isolation.
They all exist to create boundaries between projects and software.
They also all make it easier to delete a project without leaving junk files behind; you just delete the virtual environment folder, the VM file, or the container image, and everything associated with that project is instantly gone, leaving your main computer perfectly clean.
Most times, they are often used together in the real world. A large company might run a giant Virtual Machine in the cloud to provide security, put Docker inside that Virtual Machine to manage different web apps easily, and a developer might use a Virtual Environment inside that Docker container to organize their Python code.

The Major Differences

The difference lies in how much they isolate and how heavy they are.
• Virtual Environment (Lightest) - Isolates only the language packages but relies entirely on your computer for everything else.
• Docker (Middle) - Isolates the application and the operating system files, but shares the core OS engine (the kernel) to save power and speed.
• Virtual Machine (Heaviest) - Isolates absolutely everything. It clones the physical hardware and runs a 100% separate operating system, taking up a lot of space and power to provide maximum security.

Conclusion

If you are just writing a quick Python script to scrape a website, analyze some data, and need to install a few libraries without breaking your computer, use a Virtual Environment.
If you are building a web app, working with a database, collaborating with other developers, and need to make sure your code runs exactly the same way on your laptop as it will on your company's live servers, use Docker.
If you are on a Mac but absolutely need to run a piece of Windows-only enterprise software, or you are testing dangerous malware and need maximum security to protect your real computer, use a Virtual Machine.

The Medallion Architecture: Turning Messy Data into Business Gold

Lawrence Murithi — Wed, 06 May 2026 16:06:09 +0000

Introduction

Imagine drawing water from a muddy river. You would never scoop a glass of water from the bank and drink it straight down. You would want that water pumped into a treatment plant, filtered to remove the debris, and chemically purified until it is crystal clear and safe to consume.
Data requires the exact same treatment.
Ever seen raw data pulled directly from a company’s servers? It's usually a complete mess. Website logs, sales applications, customer service chatbots, and payment gateways all generate endless streams of information. If you take all that raw information, dump it into a single pile, and try to build a revenue report, the results will be a disaster. Your numbers will be wrong, your system will crawl to a halt, and nobody will trust the data.
To process this information safely, data engineers build systems with specific layers that clean and organize records step-by-step. Historically, this was done using traditional data warehouse layers. Today, a modern framework called the Medallion Architecture has taken over the industry.
Here is a deep dive into how data layers work, why the Medallion concept was invented, and how it refines digital mud into a clear, single source of truth.

The Old Way(Traditional Data Warehouse Layers)

Before the Medallion Architecture existed, engineers used a classic three-step method to move data from external software into a company dashboard.
To elaborate on the traditional Data Warehouse architecture, it is essential to ground the concepts in the frameworks introduced by W.H. Inmon (often called the father of the data warehouse) and Ralph Kimball.
Historically known as the Three-Tier Enterprise Data Warehouse (EDW) Architecture, this system was designed to separate operational systems (where data is created) from analytical systems (where data is analyzed).

1. The Staging Layer(The Transient Extraction Zone)
This was the receiving dock. The staging area is defined as a temporary, intermediate storage zone between operational data sources (ODS) and the data warehouse.
Data from a shopify store or a salesforce database was copied and temporarily dropped here. The main goal was speed; get the data out of the live application quickly so the app wouldn't slow down for regular users.

Attributes of staging layer

Decoupling OLTP and OLAP - The primary architectural goal of this layer is to isolate Online Transaction Processing (OLTP) systems (like Salesforce or Shopify) from Online Analytical Processing (OLAP) workloads. Analytical queries are highly resource-intensive; running them directly on a live database can cause catastrophic latency for end-users.
Extraction Mechanics - Data is pulled into this layer using methodologies such as batch processing or Change Data Capture (CDC). The data here is typically stored in its raw, native format.
Volatility - According to traditional DWH design principles, data in the staging layer is transient. Once the data is successfully moved to the next tier, it is generally purged or overwritten in the next batch cycle to conserve expensive storage space.

2. The Integration Layer (The Core Enterprise Data Warehouse)
This is where the heavy lifting happened. Engineers wrote scripts to clean the data and match up records.
This layer represents what W.H. Inmon famously defined as the subject-oriented, integrated, time-variant, and non-volatile collection of data in support of management's decision-making process.
If your billing system called a customer Client_001 and your website called them User_001, the Integration layer linked them together into a central, highly structured database.

Attributes of integration layer

Semantic Reconciliation - The heavy lifting is known as semantic reconciliation and Master Data Management (MDM). Engineers must resolve heterogeneous data formats (e.g., merging Client_001 from an Oracle database and User_001 from a JSON web log) into a unified entity.
Data Cleansing and Normalization - In this layer, data undergoes rigorous cleansing (handling null values, standardizing date formats). Structurally, Inmon advocated for storing this data in the Third Normal Form (3NF). This highly normalized structure reduces data redundancy and ensures mathematical consistency across the enterprise, creating a Single Version of Truth (SVOT).
The Bottleneck - Because of the complex normalization rules, writing data into this layer requires highly complex, tightly coupled SQL scripts, making the Integration Layer notoriously slow to update or modify.

3. The Presentation Layer(Data Marts and Dimensional Modeling)
The highly normalized 3NF data in the Integration layer is too complex for business users to query efficiently, therefore, data must be reshaped for consumption. Engineers would pre-package specific tables for specific teams e.g. creating a Marketing Table or a Sales Table that connected easily to dashboard software.

Attributes of presentation layer

The Data Mart - The Presentation layer is composed of subsets of the data warehouse focused on a specific business unit also called Data Marts (e.g., Sales, HR, Marketing).
Dimensional Modeling (The Kimball Method) - In this layer, engineers apply Ralph Kimball’s dimensional modeling techniques, organizing data into Star Schemas or Snowflake Schemas. Data is divided into Facts (measurable, quantitative data e.g sales amount) and Dimensions (descriptive attributes e.g time, store, or customer).
Optimized for Read-Heavy Workloads - By pre-joining and denormalizing the data, this layer allows Business Intelligence tools like PowerBI to execute complex analytical queries rapidly without requiring end-users to understand underlying SQL structures.

The Problem with the Old Way

This system relied heavily on a process called ETL (Extract, Transform, Load). Engineers would extract the data, transform/clean it and then load it into the warehouse. The fatal flaw was that the raw data was often discarded after it was cleaned to save storage space. If a data engineer accidentally deleted a crucial column during the clean phase, that historical data was gone forever.
1. The Schema-on-Write Constraint
Traditional DWHs operated on a Schema-on-Write paradigm. This means that before data could be loaded into the warehouse, the warehouse's schema (tables, columns, data types) had to be rigidly defined. If a new column was added to the source software, the ETL pipeline would fail, or simply drop the unrecognized data, until an engineer manually updated the database schema.
2. Destructive Transformations and Storage Costs
On-premise relational database storage such as Teradata or Oracle appliances used to be very expensive. To save disk space, raw data was deemed expendable. Data was extracted, transformed to fit the strict schema, and the raw source data was then discarded.
This model had some downsides which included:
• Loss of Auditability and Lineage - If a transformation logic error occurred (e.g., a script incorrectly rounded up financial figures), there was no historical raw data to refer back to since the original data was permanently lost.
• Lack of Flexibility for Machine Learning - Modern Data Science requires massive amounts of raw, unstructured or semi-structured data to train machine learning models. The traditional integration layer stripped away the granular, raw anomalies that data scientists actually need, leaving only highly aggregated, structured data.
As a result of the flaw which resulted to loss of raw data and rigidity of ETL paved the way for Data Lakes, there was a shift from ETL to ELT(where cheap cloud storage allows raw data to be stored before transformation), and ultimately the modern Medallion Architecture (Bronze, Silver, Gold), which preserves raw data while still providing structured analytics.

The Modern Shift(The Medallion Architecture)

As cloud storage became incredibly cheap, companies stopped throwing away their raw data and began dumping everything into massive, cheap storage areas(Data Lakes).
Eventually, companies pioneered the Lakehouses which combined the cheap, infinite storage of a Data Lake with the strict organization of a traditional Data Warehouse.
The need to help companies organize the massive amounts of data inside a Lakehouse therefore gave birth to the Medallion Architecture.
The Medallion Architecture separates data into three specific stages; Bronze, Silver, and Gold. It mimics the logical flow of the traditional layers but fundamentally changes how data is treated, preserved, and upgraded.

How do the three layers work?

1. The Bronze Layer(The Raw Zone)
This is where all the raw data lands from the various sources.
The data is saved exactly as it arrived. You do not fix typos. You do not rename columns. You just capture it.

Features of bronze layer

• Safety and Troubleshooting - since the raw data is completely untouched, you never have to worry about accidentally destroying information. If an engineer writes a bad piece of code that ruins the data in the later layers, they can simply go back to the Bronze layer and restart the process.
• Historical Archive - The Bronze layer acts as an infinite, permanent record of everything that ever happened in the business. It is usually append-only, meaning new records are just added to the pile without overwriting old records.
• Speed - Getting data into the Bronze layer is fast since the computer isn't doing any complex math, translations or cleaning. Engineers often use tools called Change Data Capture (CDC) to stream this raw data in real-time.

2. The Silver Layer(The Cleaned Zone)
Once the data is safely locked away in the Bronze layer, it is copied and moved into the Silver layer. The goal of the Silver layer is to create a Single Source of Truth for the entire enterprise.

What happens here?

• Cleaning and Standardization - Engineers fix the formatting. For example, if one source system writes dates as DD-MM-YYYY and another writes MM-DD-YYYY, the Silver layer standardizes all into one standard format.
• Filtering and Quarantining - Junk data is handled here. If a user accidentally enters an age like 999, the system spots it and instead of deleting it, engineers push that bad record into a separate quarantine table so it doesn't ruin the main data set, but can still be investigated later.
• Deduplication - Sometimes, source systems can glitch and send the same receipt twice. The Silver layer strips out duplicates so every row is unique.
• Joining - Data from different tables is connected using relationships. A log of customer purchases is joined with a product inventory table so that you can see exactly what item was bought, not just a random product ID number.
• Security - This is where sensitive information (like passwords, social security numbers, or personal emails) is scrambled or hidden so that analysts using the data later on cannot see private customer details.

Data scientists and analysts spend a lot of time in the Silver layer. It is clean and trustworthy, but it is still highly detailed. Every single individual action is visible, which makes it the perfect place to look for hidden trends or train machine learning models.

3. The Gold Layer(The Action Zone)
The Gold layer is the final destination. The data here is no longer meant for deep exploration but is designed to answer specific business questions immediately.

What happens here?

In the Silver layer, you might have a table with ten million individual rows. If a user tries to load the rows into a dashboard, the software will freeze. However, in the Gold layer, those millions of rows are turned into highly summarized, bite-sized metrics.
• Aggregations - Instead of listing every single sale, engineers create a Gold table that simply shows Total Sales per Store per Day.
• Business Logic - This is where specific company rules live. If your marketing team defines an active subscriber as someone who has opened an email in the last 30 days, that exact mathematical rule is applied to a Gold table.
• Performance - Data loads instantly since it's heavily summarized and simplified using Star Schema layout. When you connect any Business Intelligence tools to the Gold layer, the charts populate immediately.

Why the Medallion Architecture Wins

The reason nearly every modern data team is adopting this structure is because it solves the biggest headaches that have plagued developers for decades.
1. Bulletproof Data Lineage
When an executive looks at a Gold dashboard and sees that monthly revenue dropped by 50%, panic sets in. The data team needs to find out if the business is actually failing, or if the system is just broken.
With this architecture, they can trace the flow backward. They check the rules in the Gold layer. If those are correct, they look at the cleaned data in the Silver layer. If that looks fine, they check the raw files in the Bronze layer.
2. Extreme Flexibility
If the finance department suddenly requests a completely new way to calculate annual growth, the data team doesn't have to panic. They do not have to go back to the original software sources, and they do not have to re-clean everything. They simply build a new Gold table on top of the already clean Silver data.
3. System Reliability (ACID Transactions)
Modern Medallion architectures are built on specialized table formats like Delta Lake or Apache Iceberg which support ACID transactions. That means if a server crashes halfway through moving data from Silver to Gold, it won't leave you with a half-finished, corrupted table. The system will automatically roll back to the last safe state, preventing bad data from leaking into executive reports.

Conclusion

If you want to remember how the Medallion Architecture functions, just remember these three phrases:
Bronze -Here is everything we found(Messy, huge, exact copies).
Silver -Here is what actually happened (Clean, standardized, truthful).
Gold - Here is what we should do about it (Summarized, fast, ready for action).
The Foundation of Trust
A data platform is only as useful as the trust people put into it. If employees constantly find missing numbers, broken charts, or conflicting reports, they will abandon the dashboards and go back to guessing.
The Medallion Architecture is much more than a way to organize servers; it is a framework for building organizational trust. By moving information systematically through the Bronze, Silver, and Gold layers, a company guarantees that every digital footprint is captured securely, cleaned relentlessly, and presented flawlessly. Just like turning muddy water into something safe to drink, the Medallion Architecture takes the chaos of raw information and refines it into the exact clarity a business needs to survive.

Transactional Power Vs Analytical Precision: The Essential Guide to OLTP and OLAP

Lawrence Murithi — Fri, 01 May 2026 19:58:49 +0000

Introduction

Behind every digital interaction is a fundamental divide in how data is handled. The system required to process your grocery checkout with lightning speed is radically different from the system a corporation uses to analyze a decade of sales growth. This is the core distinction between Transactional Power vs. Analytical Precision. To understand the backbone of modern technology, you must understand OLTP (Online Transactional Processing) and OLAP (Online Analytical Processing).
Though they sound like technical jargon, they are simple concepts that define how businesses operate and grow.
This article serves as your roadmap to understanding how these systems function, their unique strengths, and why the balance between them is the secret to data-driven success.

OLTP(Online Transaction Processing): Handling the Day-to-Day
OLTP is the engine that runs traditional databases. It is designed to manage everyday business operations and process thousands of short, fast interactions per second. It is the system that handles the daily, minute-by-minute work of a business. Whenever a specific action or transaction takes place, OLTP is the system taking care of it.
In a database, a transaction is any small unit of work such as changing your password.
Transaction systems follow important rules called ACID properties.
ACID Properties are a set of four fundamental principles that guarantee reliable database transactions. They ensure data integrity and accuracy, preventing corruption even during system failures or concurrent operations.
The four principles are:
Atomicity(All-or-Nothing) - A transaction is treated as a single unit, it either fully completes or entirely fails and rolls back.
Consistency(Data Integrity) - A transaction ensures the database moves from one valid state to another, adhering to all constraints and rules. That means data remains valid before and after transaction
Isolation(Concurrent Control) - Concurrent transactions are isolated from each other, ensuring they don’t interfere with each other.
Durability(Permanent Data) - Once a transaction is committed, its changes are permanently saved and will survive system failures or crashes.

Examples of OLTP in real life

Adding an item to your online shopping cart.
Booking an airline ticket.
Sending a text message.
Banking systems (Mpesa, ATM transactions)

Think of OLTP like the cashier at a busy grocery store. The cashier’s job is to scan items quickly, take your money, hand you a receipt, and move on to the next person.

How OLTP Works

OLTP systems prioritize speed and accuracy. They use a design concept called normalization. This means the database organizes data into many small tables to avoid saving the same piece of information twice. Because the data is spread out neatly, the system can insert a new record, update a row, or delete a piece of data almost instantly.

Example

Imagine you want to withdraw $50 from an ATM. The bank's OLTP system immediately checks your balance, approves the withdrawal, and updates your account to show $50 less. This has to happen in seconds, and it has to be 100% accurate so you cannot overdraw your account.

Key features of OLTP

• Low latency/Fast response time - When you swipe your card, you expect it to be approved in seconds. OLTP databases are built to respond instantly.
• High number of users - The system ensures that thousands of users can access the same row in a database without failure.
• Normalized Data - Databases are typically highly normalized to reduce redundancy and ensure fast data entry. A single OLTP transaction does not require much data.
• Real-time processing/Accuracy - If you transfer $50 from your current account to your savings account, the system must subtract $50 from one and add $50 to the other. If the system crashes halfway through, the OLTP system cancels the whole thing so your data does not get corrupted. OLTP systems are built to be perfectly accurate and fail-safe.
• Write-heavy operations - Thousands of users might be doing things at the exact same time, the system is therefore constantly writing, updating or deleting information to the database.
• Highly available - Because OLTP systems handle the immediate, day-to-day operations of a business, the system is designed to be online, working, and accessible virtually 100% of the time thus downtime is not an option.
OLTP systems are usually built with backup servers and fail-safes. If one server crashes, another one instantly takes over so the customer doesn't notice a glitch.

Pros of OLTP

• Efficiency in Data Entry - Highly optimized for adding, modifying, or deleting records.
• Data Integrity - High reliability due to ACID compliance.
• Availability - Designed for 24/7 uptime for business-critical applications.

Cons of OLTP

• Inefficient for complex Analysis - If you ask an OLTP database to calculate the average sales of a product over the last five years, it will have to scan millions of everyday records. This takes a lot of computing power and can slow down the system for people trying to use it for normal tasks.
• Limited History - To keep things fast, OLTP systems usually only hold current or recent data. Old data is often moved somewhere else to save space.

OLAP (Online Analytical Processing)
OLAP is the engine behind data warehouses. If OLTP is the system for doing things, OLAP is the system for analyzing things. While OLTP only looks at a tiny slice of data at a time, OLAP is the brains used for strategic planning since its designed for data mining, processing huge amounts of information to find patterns, trends and summaries as well as complex reporting. Managers, data scientists, and business owners use OLAP to spot trends, build reports and make big decisions.

Making Sense of OLAP

Think of OLAP as the manager in the back office of the grocery store. They aren't ringing up customers. They are sitting at a desk, looking at charts and graphs of past sales to decide if they need to order more apples for next week.

How OLAP Works

OLAP systems are not built to process quick, small updates. To make this faster, OLAP uses denormalization. Instead of spreading data across many tiny tables like OLTP, OLAP groups massive amounts of related data together into large tables. This takes up more storage space, but it means the system can read through billions of records very quickly to find patterns.

Key features of OLAP

• Read-heavy operations - Unlike OLTP, which is constantly writing new data (new orders, new users), OLAP mostly just reads old data. It looks at what already happened.
• Complex Queries - OLAP tasks involve complex math—adding, averaging, and grouping massive lists of numbers.
• Multidimensional Analysis - Users can slice and dice data (e.g. viewing sales by region, then by month, then by product category) using data cubes.
• Denormalized Data - Databases often use Star or Snowflake schemas to reduce the number of table joins needed for queries.
• Slower response time - While nobody wants to wait all day, an OLAP report might take a few minutes or even a few hours to run. This usually is not a concern since the person waiting is usually a business manager, not a customer standing at a checkout counter.

Pros of OLAP

• Handles Massive Data - It can easily process millions or billions of rows of historical data.
• Does Not Disrupt the Business - Because OLAP lives in a data warehouse, running a massive, heavy report will not slow down the cash registers running on the OLTP database.
• High Performance for Reporting - Optimized for complex analytical queries.
• Strategic Insights - Allows businesses to identify trends, patterns, and anomalies to drive decision-making.
• User-Friendly: The system is often integrated with Business Intelligence tools like PowerBI for visualization.

Cons of OLAP

• Data is Not Real-Time - OLAP systems are usually updated in batches, often overnight. If you look at an OLAP report at 2:00 PM, it usually only includes data up until the night before.
• Slow to Update - Adding new data to an OLAP system takes time because the data has to be heavily organized and formatted before it is saved.
• Expensive and Complex - Building and maintaining a data warehouse requires specialized engineers and large amounts of server storage.
• Latency - Queries can take seconds, minutes, or even hours because of the massive volume of data being scanned.

Example

A regional manager for a coffee shop chain wants to know, "Between hot chocolate or dark roast coffee, which sold better on rainy days last year?" To answer this, the system has to look at weather data, sales data from fifty stores and a whole year of dates. An OLAP system can pull this specific report together without breaking a sweat.

Examples of OLAP in real life

Netflix figuring out what genres of movies are most popular in different countries during the summer.
A hospital analyzing patient records over ten years to see if a specific treatment is working.
- A retail store deciding how much inventory to buy for Black Friday based on the last three years of sales.

Common OLAP Operations

OLAP systems organize massive amounts of data into multi-dimensional structures, often referred to as OLAP cubes. These cubes allow users to view business metrics from any angle. To explore, analyze, and make sense of this complex data, OLAP systems support several powerful analytical operations.

Here is a detailed look at the five core OLAP operations:
1. Roll-Up (Consolidation)
Roll-up is also known as consolidation or aggregation and involves summarizing data to a higher, more generalized level. This operation reduces the detail of the data by climbing up a concept hierarchy or by removing a dimension entirely. It is primarily used by upper management to view macro-level business trends.
It uses mathematical functions—such as summing, averaging or counting to group smaller data points into larger, overarching categories.
Example (Time Hierarchy)
Daily sales → Monthly sales → Yearly sales.

If a company has millions of records of individual daily transactions, viewing them all at once can be overwhelming. Using a roll-up operation, an executive can consolidate these daily records to see total sales by month, and then roll up again to see the total gross revenue for the entire year.
Business Value - Roll-up provides a big picture view of business performance, stripping away unnecessary granular details to highlight overarching trends.

2. Drill-Down
Drill-down is the exact opposite of roll-up. It involves navigating from highly summarized, macro-level data down to highly detailed, micro-level data. This is done by stepping down a concept hierarchy or by adding a new dimension to the dataset.
It breaks a larger aggregated number into the smaller components that make it up, allowing analysts to uncover the root causes behind a specific metric.
Example (Geography & Time Hierarchy)
Yearly sales → Monthly sales → Daily sales (or Country → Region → Individual Store).

Imagine an annual report shows that total yearly sales are significantly lower than expected. A manager can drill down from the yearly view to the monthly view and discover in what specific month sales plummeted. They can then drill down further into the month's daily sales to find which specific week caused the drop.
Business Value - It is essential for root-cause analysis, troubleshooting anomalies, and investigating sudden spikes or drops in performance.

3. Slice
The slice operation performs a selection on one specific dimension of the OLAP cube, resulting in a new, smaller slice of the data.
Think of it like slicing a single piece of bread from a whole loaf. It locks one variable in place so you can analyze the rest of the data in a two-dimensional table.
You isolate a single value within one dimension (e.g., Time, Geography, or Product) while keeping the other dimensions open.
Example
Show sale records for Nairobi city only.

If a data cube contains sales data across Products, Time, and Cities, applying a slice on the City dimension for Nairobi isolates that market. The resulting view will show the sales of all products over all time periods, but exclusively for Nairobi location.
Business Value - It allows regional managers, department heads or specific product owners to filter out irrelevant data and focus entirely on the one area of the business they are responsible for.

4. Dice
While a slice filters data based on a single condition, a dice operation isolates a highly specific sub-cube by applying multiple filters across two or more dimensions simultaneously.
Think of it like cutting a smaller block out of a larger block of cheese.
It selects specific ranges or values across multiple dimensions to create a highly targeted subset of the original data.
Example
Show laptop sales in Nairobi and Mombasa during January and February.

Here, the user is applying filters across three separate dimensions, Product Dimension(Laptops only), Geography Dimension(Nairobi and Mombasa only) and Time Dimension(January and February only).
Business Value - Dicing is used for highly specialized, multi-faceted analysis. It allows data scientists and marketers to look at exact intersections of data, such as evaluating the success of a specific winter promotion for a specific tech product in key coastal cities.

5. Pivot (Rotate)
Pivot, sometimes called rotation, does not filter or change the underlying data, instead, it changes the visual perspective. It rotates the data axes to provide an alternative presentation, making different relationships easier to spot.
It rearranges the layout of the data, typically by swapping rows and columns, or by moving a dimension from the background into the foreground.
Example
Swapping Products and Time periods.

A manager might be looking at a table where Products (Laptops, Phones, Tablets) are listed in the rows and Months (January, February, March) are the columns. By pivoting the data, they can make Months the rows and Products the columns.
Business Value - Different layouts highlight different trends. A pivot makes it easier to compare data side-by-side depending on what the analyst is trying to prove, ensuring the final report is as readable and impactful as possible.
NB: OLAP is not mainly about recording what is happening right now. It is about understanding what has happened and what it means.

OLTP vs. OLAP

The distinction between OLTP (Online Transaction Processing) and OLAP (Online Analytical Processing) boils down to two distinct phases of business; execution and strategy. Simply put, OLTP runs the business, while OLAP analyzes the business.
These two systems are designed for fundamentally different jobs. Understanding how they differ and how they work together comes down to understanding their relationship with time, purpose, and data architecture.

Here is a detailed comparison of how the two systems operate.
1. Main Purpose and System Goals
OLTP - Its primary objective is to handle daily business operations and execute transactions seamlessly. Its core focus is on accuracy, transaction safety, and ensuring the day-to-day business continues without interruption.
OLAP - Its primary objective is to extract valuable insights from data to help leadership make smart, strategic decisions. Instead of facilitating transactions, it focuses on reporting, identifying long-term trends, and planning for the future.

2. The User Profiles
OLTP - These systems are used by everyday customers, cashiers, front-line staff, and mobile applications. These are the people actively interacting with the business in real-time buying items, logging into portals or booking appointments.
OLAP - These systems are utilized by business analysts, managers, and corporate executives. These users interact with data using dashboards, Business Intelligence reports and complex spreadsheets to evaluate business performance.

3. Data State and Architectural Design
OLTP - Data is current, real-time, and highly operational. Since the data is constantly changing, the database is highly normalized to ensure efficiency and eliminate data redundancy. It is optimized to handle a constant stream of inserting, updating, and deleting small bits of data.
OLAP - Data is historical, static, and rarely changes. It consists of summarized data spanning months or years. Because the goal is fast analysis rather than fast updates, the database is often denormalized allowing the system to efficiently read millions of rows of data at once without altering them.

4. Query Dynamics and Performance Needs
OLTP - Queries are short, simple, and require incredibly fast response times per transaction. They generally touch only a few records at a time.
Example Query - Update bread's price to $10, What is John's email address? or Update a specific customer's order.
OLAP - Queries are heavy, long, and highly complex. While speed is still important, the system is built to process massive analytical workloads rather than split-second individual actions.
Example Query - What is the average age of customers who bought bread in November of 2022? or Show the global sales trends broken down by region over the past 5 years.

5. Real-World Examples
OLTP Systems - ATMs, retail checkout registers, airline booking systems, and e-commerce shopping carts.
OLAP Systems - Corporate data dashboards, annual financial reports, and Business Intelligence (BI) platforms.

The Synergy(How OLTP and OLAP Work Together)

A successful business relies on a symbiotic relationship between both systems. You cannot accurately analyze a business if you do not have an OLTP system reliably recording the daily sales. Conversely, you cannot grow a business if you lack an OLAP system to look back at your history and determine what strategies are actually working.

So, how does the two systems connect?
They are linked through a pipeline process known as ETL (Extract, Transform, Load).
Every day, the OLTP database handles the rapid work of serving customers and processing transactions. At the end of the day, usually in the night when customer traffic and system strain are at their lowest, an automated batch script runs.
Extract - The script pulls a copy of the day's newly generated operational data from the OLTP database.
Transform - It cleans, formats, and aggregates that raw data to ensure it is properly structured for analysis.
Load - Finally, the script deposits that formatted data into the OLAP data warehouse.
By the time the business analysts and executives log into their dashboards the next morning, the OLAP warehouse is fully updated with yesterday's finalized numbers. The data is now perfectly prepped to be searched, graphed, and studied.

The Bottom Line

The difference between OLTP and OLAP simply comes down to time. While OLTP handles the exact moment a transaction occurs, OLAP handles months or years of historical data that the transactions leaves behind. Together, they allow a business to operate today while intelligently planning for tomorrow.

Conclusion

Every time you interact with a screen, you are leaving a digital footprint. Databases are the safe spaces that hold those footprints. OLTP ensures daily transactions are fast and secure. Data warehouses collect all those footprints over time. Finally, OLAP helps businesses look at the giant trail of footprints to figure out where they should step next.
These tools might be invisible, but they are the engine running modern business, keeping our digital lives fast, organized, and constantly improving.

From Tables to Tides: Navigating Databases, Warehouses, Marts, Lakes, and the Lakehouse Revolution

Lawrence Murithi — Fri, 01 May 2026 17:13:47 +0000

Introduction

Every time you buy a coffee with a card, "like" a post on social media, withdraw money from an ATM or buy a shirt online, you are interacting with a database. Behind the scenes of every app and website, data is constantly being created, moved, stored and read.
However, not all data storage is the same. The way a system stores your checkout items at a grocery store is very different from the way that same grocery chain analyzes ten years of sales trends.
To understand how modern software handles data, we need to look at the main types of storage; traditional databases, data warehouses, data marts, data lakes and lake houses.

If you are not a computer guru, these terms might sound very technical but they are not as complex as they sound. But once you break them down, they make perfect sense.
This article gives a simple but detailed breakdown of what these are, how they work, and why software relies on both.

The Basics of Data Storage

In today's data-driven world, organizations generate massive amounts of information. To effectively store, manage, and analyze this data, businesses use different architectural models based on their specific needs.
Before we look at the specific processing types, it helps to understand the physical or virtual places where data lives.

1. The Database (The Daily Worker/Operational Engine)
Think of how you keep track of your personal budget. You might use a spreadsheet. A spreadsheet is great for one person looking at a few hundred rows of information. Now imagine a company like Amazon trying to use a spreadsheet to track millions of orders happening every minute. The spreadsheet would freeze and crash instantly.
A database is like a highly advanced, incredibly secure digital filing cabinet built to store massive amounts of information without crashing. Databases are primarily designed for OLTP (Online Transactional Processing). They are the workhorses that power day-to-day operations, such as processing bank transactions, managing inventory, or storing user profiles. Its main job is to quickly record new information, update existing information, and allow users to quickly look up specific details. More importantly, it is organized so that users can find exactly what they are looking for in a fraction of a second.
Information in a standard database is usually organized into tables with rows and columns. For example, an online store might have one table for Customers, one for Products, and one for Orders. The database connects these tables so the system knows exactly which customer bought which product. Think of a traditional database like a busy cash register. It needs to be fast, accurate, and handle hundreds of transactions at once without freezing.

Key Characteristics of a Database

ACID Compliance – Traditional relational databases follow strict rules (Atomicity, Consistency, Isolation, Durability) to ensure that transactions are processed reliably and that data remains accurate even in the event of a system crash.
Normalized Structure – Data is organized into tables to reduce redundancy. For example, a customer’s address is stored in one place rather than being repeated for every order they place.
Real-Time Interaction – Databases are designed to handle thousands of concurrent users making small, rapid changes to the data simultaneously.

Types of Databases

Relational (SQL) - Uses tables with rows and columns (e.g., MySQL, PostgreSQL, Oracle). Ideal for structured data where relationships are clearly defined.
Non-Relational (NoSQL) - Uses flexible structures like documents or graphs (e.g., MongoDB, Cassandra). Ideal for rapidly changing data types and massive scaling.

2. The Data Warehouse (The Long-Term Archive/Analytical Hub)
As a business runs, over time, its database fills up with millions of past transactions. After a few years, a company manager might want to know, "Which of our stores sold the most winter coats in December over the last five years?"
For the database to answer that question, it has to dig through millions of old records thus it slows down. This causes the system to freeze hence people trying to buy things on the website at that moment cannot check out.
Using the grocery store analogy, a store manager walking up to a cashier who has a long line of customers and asking them to calculate the store's total profit for the last decade would cause a crisis and bring the whole store to a halt. To fix this, companies build Data Warehouses.
A data warehouse is a massive storage system designed to hold historical data from many different sources. It aggregates data from various sources such as different operational databases, CRM systems and flat files to provide a comprehensive, historical view of the entire organization. Periodically, usually in the night, the company copies all the new data from these sources and dumps it into the data warehouse.
From the previous example, if the database is the cash register, the data warehouse is the company's central filing room. A data warehouse takes the daily receipts from all the different cash registers, organizes them and stores them for years.
The data warehouse acts as the company's long-term memory. It doesn't handle everyday customer actions. Instead, it is a quiet, organized space where business analysts can run massive queries and reports without interrupting the live website.
Data warehouses utilize OLAP (Online Analytical Processing). Instead of focusing on individual transactions, they are optimized to scan millions of rows to find trends, averages and insights.

The ETL Process (The Warehouse Engine)

Before data enters a warehouse, it must undergo ETL (Extract, Transform, Load).
Extract - Pulling data from multiple, often messy, source systems.
Transform - Cleaning, deduplicating, and formatting the data into a standardized structure.
Load - Moving the clean data into the warehouse.
This is known as Schema-on-Write, meaning the structure of the data must be defined and validated before it can be stored.

Key Benefits of a Data Warehouse

Data Integration – It breaks down data silos by combining information from marketing, sales, and finance into one single source of truth.
Historical Context – While databases often only show current data, warehouses store years of historical records, allowing for year-over-year comparisons.
Optimized for Performance – Warehouses often use columnar storage, which allows them to perform complex calculations such as, What was the total revenue for 2023?, significantly faster than a standard database.
High Quality & Accuracy – Because data is cleaned during the ETL process, business leaders can trust that the reports they generate are based on accurate, non-conflicting information.
Why use a Data Warehouse?
NB: A data warehouse is the foundation for Business Intelligence. It allows executives to run complex What if? scenarios and generate reports that inform long-term strategy. It also ensures that the operational databases are not slowed down by heavy analytical queries.

3. Data Marts(The Departmental Lens)
A data mart is a highly focused, specialized subset of a data warehouse designed to serve the specific needs of a single department or business unit.
While a traditional Data Warehouse acts as a massive, centralized repository containing all of an organization's structured data, a data mart isolates only the information relevant to a specific team.

Key Benefits of a Data Mart

Enhanced Performance - Because the data mart is smaller and queries are highly specific, reports and dashboards load much faster.
Improved Security - By isolating data, companies can strictly control who has access to sensitive departmental information
Ease of Use - Business users and analysts do not have to sift through irrelevant enterprise data to find what they need.
Data marts can be Dependent (built by drawing data from an existing enterprise data warehouse) or Independent (built directly from operational systems).

4. Data Lakes(The Raw Data Reservoir)
A data lake is a massive, highly scalable storage system designed to hold vast amounts of raw, unprocessed data in its native format.
Unlike a data warehouse, which requires data to be cleaned, transformed, and structured into strict tables before it can be stored(Schema-on-Write), a data lake stores data exactly as it is generated, assigning structure only when the data is eventually read or queried (Schema-on-Read).

Data Lakes store?

Structured Data - Traditional tables and relational databases.
Semi-Structured Data - JSON files, XML, CSVs, and server logs.
Unstructured Data - Emails, documents, PDFs.
Binary/Media Data - Images, audio files, and videos.
Streaming Data - Real-time IoT sensor data and website clickstreams.

Why use a Data Lake?

A data lake is ideal when an organization wants to capture and retain everything, even data they don't immediately need. It is highly cost-effective because it utilizes cheap cloud storage. Furthermore, having raw, unmanipulated data is essential for training artificial intelligence (AI) and complex Machine Learning (ML) models.
NB: Without proper organization and governance, a data lake can become a messy, unsearchable Data Swamp.

5. Data Lakehouse(The Modern Hybrid)
For years, companies had to maintain a two-tier architecture; a Data Lake for raw data and machine learning, and a separate Data Warehouse for clean data and business reporting. This resulted in expensive storage costs, data duplication, and complex maintenance.
A Data Lakehouse is a modern architectural design that merges the best concepts of both systems. It is built directly on top of cheap data lake storage, but it applies the organizational structures, management tools, and performance speeds of a data warehouse.

Key Features of a Lakehouse

Flexibility & Scale - Like a data lake, it can store massive amounts of structured, semi-structured, and unstructured data.
Reliability & Structure - Like a data warehouse, it supports ACID transactions (meaning data is reliable, updates don't break the system, and multiple people can read/write simultaneously).
Single Source of Truth - Teams no longer have to copy data from the lake to the warehouse. Business analysts can build BI dashboards, and data scientists can run machine learning models directly on the exact same data platform.

Summary of the Storage systems

The Bottom Line

In today's modern economy, data is a company’s most valuable asset. However, data only provides value if it can be accessed, analyzed, and trusted. By understanding the distinctions between these storage methods, organizations can build a robust infrastructure that avoids the Data Swamp, reduces operational costs, and ultimately turns raw information into a competitive advantage.

Conclusion

Choosing the right data storage architecture is no longer about finding a one-size-fits-all solution but matching the right tool to the specific needs of the business. As organizations evolve from simple record-keeping to complex artificial intelligence and real-time analytics, their data strategy must also mature.
For Day-to-Day Operations, Database remains the essential engine, ensuring that transactions are processed accurately and instantly.
For Strategic Reporting, Data Warehouse and its specialized Data Marts provide the single source of truth needed for executive decision-making and departmental efficiency.
For Big Data & Innovation, Data Lake serves as the vital reservoir for raw information, fueling the next generation of Machine Learning and AI development.
For the Future of Scalability, Data Lakehouse represents the ultimate convergence, offering the best of all worlds; the speed of a warehouse with the massive flexibility of a lake.

Apache Airflow for Beginners: DAGs, Tasks, Operators, and Scheduling Explained

Lawrence Murithi — Wed, 29 Apr 2026 20:24:12 +0000

Introduction

Being a beginner in data engineering can seem very scary. People use technical words like ETL, pipelines, data warehouses, architecture, orchestration etc. At that point, it is very easy to feel like you need a computer science degree just to understand what they mean. However, most of these terms are just technical but not as complicated as they sound.
Data engineering, in simple terms, involves extracting data from a place such as websites, social media pages, excel/csv files or payment systems etc, cleaning it, and storing it somewhere (database, data warehouse or data lake). If you need this done once, you can run a simple Python script. However, if the job must run every hour, every day, or every week, you need a tool that can manage it for you. That's where Apache Airflow comes in.

What is Apache Airflow?

To understand Apache Airflow, think about a process like baking a cake. You do not just throw everything into the oven. You follow steps:

Buy the ingredients
Prepare the dough
Put the dough in the oven
Bake the cake
Let it cool
Add frosting
Serve the cake

Some steps must happen before others. You cannot frost the cake before baking it. You cannot bake the cake before preparing the dough. You also need to know how long each step should take and what to do if something goes wrong.
This kind of process is called a workflow or pipeline and Airflow helps you manage that workflow.
NB: Airflow does not usually do the heavy data processing itself but tells other tools when to do the work.
A workflow may be a data pipeline, a machine learning pipeline, a reporting process, or any process made up of several steps.
Example

extract_data >> clean_data >> load_data >> send_email

Apache Airflow is an open-source platform used to schedule, monitor and manage workflows. It was originally created by Maxime Beauchemin at Airbnb in 2014 to manage increasingly complex data workflows. It helps you decide what task should run first, what should follow, what should happen if something fails, and when the whole process should run again.

Airflow as an Orchestrator

Orchestration refers to arranging many tasks so they run in the right order and at the scheduled time. It makes sure that task B does not run before task A has finished. It also records whether each task succeeded or failed. Without orchestration, you will have many scripts running manually or through separate cron jobs hence becoming difficult to manage as your project grows.

Why Airflow?

While a normal Python script could run fine with simple tasks, you need more control as the number of tasks increases. Airflow is useful because data jobs often have many moving parts.

Airflow is useful because of various reasons:

1. Scheduling
Since most data work is repetitive, scheduling enables workflows to run automatically based on the scheduled time. Airflow handles complex timezone logic natively, ensuring global data pipelines run exactly when they should.
Airflow can also automatically run a pipeline for historical dates through a process called backfilling.

2. Task Orchestration
Tasks are arranged depending on which task runs first, second, and last.
Example

extract >> transform >> load

This order is critical because if the load task runs before the transform task, the database may receive dirty data. If the transform task runs before extract, there will be no data to clean.
Airflow has parallel execution capabilities to run several tasks simultaneously and wait for all of them to finish before moving to the next step.

3. Monitoring
Monitoring standard scripts to know if a job ran successfully requires SSH-ing into a server and digging through terminal files. However, Airflow provides a centralized web interface for the entire data ecosystem to monitor:
Task Statuses - Color-coded views showing what is running, successful, failed, or queued.
Gantt Charts - Visual representations of task duration, helping you identify bottlenecks in your pipeline.
Historical Trends - view the history of a specific pipeline over a duration of time to spot intermittent failures or slowing performance.

4. Automated Retries
In the real world, tasks can fail for temporary reasons. An API may be rate-limited, a database might briefly drop a connection, or a network hiccup might occur.
Instead of waking up at 3:00 AM to manually restart a failed script, Airflow handles transient errors gracefully by trying the task again based on the number of retries set.
Example

"retries": 3,
"retry_delay": timedelta(minutes=5)

In this scenario, if the task fails, Airflow will wait for 5 minutes before trying again, up to three times.

5. Accessible Logs
Finding out why and when a pipeline breaks is very critical. Airflow attaches isolated logs to every single task execution eliminating the need to hunt through an entire server log file.
A user is also able to click on a failed task directly in the web UI and instantly read the error message for that specific run, reducing debugging time.

6. Failure Handling
When a task fails, letting the rest of the script run can result in corrupt data or crashed databases. Airflow thus stops execution of the downstream tasks preventing bad data from moving through the pipeline.
Airflow can also be configured to send an automated email, slack message, or an alert when a pipeline fails, ensuring the team is instantly aware of critical data outages.

7. Clear Pipeline Structure
Airflow workflows are written entirely in Python hence the pipeline configuration is treated like any other software project. Workflows are visible and anyone can see how tasks connect to each other hence a new person joining the team can open the Airflow UI and understand the pipeline flow.
Workflows can be committed to Git, peer-reviewed, and rolled back if a mistake is made.

Important Airflow Terms

Before writing any Airflow code, its important to understand the main words used in the Airflow world because they describe parts of a workflow system.

1. DAG
In Airflow, a full workflow is called a DAG(Directed Acyclic Graph).
Directed - the workflow moves in one direction. The process has a starting point and an ending point and does not move backward.
Acyclic - there are no loops. Since workflow must have a clear start and a clear end loops are not allowed since they create endless cycles and the pipeline might never finish running.
Graph - a structure made up of points and connections. The points are tasks and the connections are dependencies
A DAG is, therefore, a workflow made up of tasks arranged in a clear order and indicating how they connect with each other.
Example

with DAG(
    dag_id="stock_etl_dag",
    start_date=datetime(2026, 4, 20),
    schedule=timedelta(hours=1),
    catchup=False
) as dag:

2. Task
A task is one step inside a DAG or one job inside a pipeline. A task should usually do one clear job. Creating one huge task that does everything makes debugging hard thus work should be split into separate tasks.
Example

fetch = PythonOperator(
    task_id="fetch_stock_data",
    python_callable=fetch_stock
)

fetch is the task object, and fetch_stock_data is the task name shown in Airflow.

3. Operator
An operator is the tool used to create and run a task. Different operators are used for different types of jobs.

4. Dependency
A dependency defines the order of tasks by telling Airflow which task must run before another task. In simple terms, a dependency is the relationship between tasks.
Example

extract >> transform >> load

This means extract runs first, transform runs after extract succeeds and load runs after transform succeeds.
You can also define parallel dependencies to show which tasks should run simultaneously.
Example

download >> [clean_data, backup_data] >> send_email

This means download runs first, clean_data and backup_data run after download then send_email executes after both clean_data and backup_data finish.

5. Scheduler
The scheduler is the brain of Airflow which checks the DAGs and decides which tasks should run and when.
If the scheduler is not running, DAGs may appear in the UI but tasks may stay queued or show no status.
The scheduler constantly checks:

which DAGs exist
whether a DAG is due to run
whether a task’s upstream tasks have succeeded
whether a task should be queued
whether a failed task should retry
whether a DAG run is complete The scheduler does not usually execute the task itself but decides which task is ready and sends it to the executor.

6. Executor
The executor is the part of Airflow that decides how tasks are actually run. Different Airflow setups use different executors.
Common executors include:
SequentialExecutor - This runs one task at a time thus cannot run many tasks in parallel. It is simple and often used for learning or testing.

LocalExecutor - This runs tasks locally on the same machine, and it can run more than one task at the same time. It's useful when Airflow is installed on one server and you want tasks to run on that server.

CeleryExecutor - This is used for larger setups. The scheduler sends tasks to a queue, and workers pick them up and run them. This setup usually needs a message broker such as Redis or RabbitMQ.

KubernetesExecutor - This runs each task in a separate Kubernetes pod. It's more advanced and usually used in cloud or production environments.

NB: The scheduler decides that a task should run while the executor handles the running method(how Airflow runs tasks).

7. Worker
A worker is the process that actually executes tasks. This term is particularly important when using CeleryExecutor.
In a Celery setup, the flow looks like:

Scheduler >> Queue >> Worker >> Task runs

The scheduler decides the task is ready, the executor sends the task to a queue and the worker picks it up and runs it.
NB: The scheduler decides what should run while the worker does the actual execution.

8. XCom
XCom means cross-communication. It allows tasks to pass small pieces of data to each other. XCom can help pass data from one task to another.
XCom is for passing small messages between tasks, not for moving large datasets. Passing large datasets through XCom slows down Airflow and fills the metadata database.

In PythonOperator, you can push data to XCom:

kwargs["ti"].xcom_push(key="raw_data", value=data)

Then another task can pull it:

data = kwargs["ti"].xcom_pull(task_ids="extract", key="raw_data")

However, you can save large data somewhere else, then pass the location through XCom.
Example

extract task saves data to /tmp/raw_stock_data.csv
XCom passes "/tmp/raw_stock_data.csv"
transform task reads the file

9. Metadata Database
The metadata database is Airflow’s internal database and uses it to remember what happened(records results of a DAG).
It stores information such as:

DAGs
DAG runs
Task runs
Task states
Schedules
Retries
Users
Roles
Variables
Connections
XCom values

This database is very important because Airflow needs memory.
For example, Airflow needs to know:

Did this task succeed?
Did this task fail?
How many times has it retried?
When did the DAG last run?
What logs belong to this task?
What DAGs exist?
Which users can log in?

Conclusion

Apache Airflow may look difficult when you first encounter it because it comes with many technical jargons making data engineering feel more complicated than it really is. Airflow is simply a workflow manager which helps you organise work that must happen in a specific order. Apache Airflow is about control. It helps you control timing, order, failure, retries, logs, and monitoring.
Just like baking a cake, you must follow the right sequence. A data pipeline works the same way. You extract data, transform it, load it, check it, and sometimes send a notification. Each step depends on the previous one. Airflow gives you a clean way to define these steps and make sure they run correctly.

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.