DEV Community: Collins Njeru

From Chaos to Clarity: How SQL Transforms Raw Data into Powerful Stories

Collins Njeru — Mon, 27 Apr 2026 05:33:32 +0000

By Collins Njeru

I still remember the first time I ran a query that actually meant something. Not a tutorial exercise with fake names and random numbers , but a real question, against real data, that came back with a real answer. That moment is hard to describe. It feels less like writing code and more like having a conversation with your database.

That is what SQL really is. A conversation. And like any conversation, the quality of what you get back depends entirely on how well you frame what you are asking.

This article is about learning to ask better questions. We will use a retail sales database - customers, products, sales records, inventory and work through the full range of SQL from the basics all the way to the kind of queries that make a hiring manager lean forward in their chair.

The Database We Are Working With

Before writing a single query, you need to understand your data. That sounds obvious, but you would be surprised how many people skip this step and wonder why their results look off.

Our database has four tables:

customers - 50 records. Each one has a name, email, phone number, the date they registered, and a membership tier: Bronze, Silver, or Gold.
products - 15 items spread across Electronics, Appliances, and Accessories. Prices range from a $25 wireless mouse all the way to a $1,500 smart TV.
sales - 15 transactions. Each one links a customer to a product, records how many units were sold, the date, and the total amount.
inventory - stock levels for each product.

Small dataset. Big concepts. The patterns you learn here work exactly the same way when you are dealing with millions of rows.

Start Simple, Stay Curious

Every SQL query starts the same way , you ask for something, and the database gives it back to you.

SELECT * FROM customers;

SELECT product_name, price
FROM products
WHERE price > 500;

That second query is where things start getting interesting. You are not just pulling data , you are filtering it. You are asking a specific question: which products cost more than $500? That shift in thinking, from "give me everything" to "give me exactly this," is the foundation of good data analysis.

Aggregates take it a step further. Instead of looking at individual rows, you start summarising them:

SELECT COUNT(*) AS total_products FROM products;
SELECT AVG(price) AS average_price FROM products;
SELECT SUM(total_amount) AS total_revenue FROM sales;

One thing that trips people up early on is the difference between WHERE and HAVING. They look similar and both filter data, but they work at completely different stages. WHERE runs before any grouping happens , it narrows down the rows going in. HAVING runs after grouping , it filters the results that came out. Mix them up and you either get an error or, worse, results that look right but are actually wrong.

SELECT product_id, SUM(quantity_sold) AS total_qty
FROM sales
GROUP BY product_id
HAVING SUM(quantity_sold) >= 2;

JOINs — Where the Real Magic Happens

Here is the thing about relational databases: the interesting answers almost never live in a single table. You need to pull things together. That is what JOINs do.

The most common one is the INNER JOIN , it only returns rows where there is a match on both sides:

SELECT 
    c.first_name || ' ' || c.last_name AS full_name,
    p.product_name,
    s.quantity_sold
FROM customers c
JOIN sales s ON c.customer_id = s.customer_id
JOIN products p ON s.product_id = p.product_id;

That one query answers: who bought what, and how much of it? Three tables, one clean result.

LEFT JOINs are for when you need to keep rows even if there is no match on the other side. The classic use case is finding gaps , things that should have happened but did not:

-- Products that have never been sold
SELECT p.*
FROM products p
LEFT JOIN sales s ON p.product_id = s.product_id
WHERE s.product_id IS NULL;

This is called an anti-join. You are not looking for what matches,you are looking for what does not. It is one of the most useful patterns in SQL and one of the least talked about in beginner tutorials.

There is also the self-join, which sounds strange until you need it. Joining a table to itself lets you compare rows within the same dataset , like finding all pairs of customers who share the same membership tier:

SELECT 
    c1.first_name || ' ' || c1.last_name AS customer1,
    c2.first_name || ' ' || c2.last_name AS customer2,
    c1.membership_status
FROM customers c1
JOIN customers c2 ON c1.membership_status = c2.membership_status
WHERE c1.customer_id < c2.customer_id;

That c1.customer_id < c2.customer_id at the end is easy to miss but important — without it, you get every customer paired with themselves, and every pair listed twice.

Subqueries — Answering Questions Inside Questions

Some questions cannot be answered in one shot. You need to calculate something first, then use that result to filter or compare. That is where subqueries come in.

-- Products priced above their own category average
SELECT product_name, category, price
FROM products p
WHERE p.price > (
    SELECT AVG(p2.price)
    FROM products p2
    WHERE p2.category = p.category
);

The inner query runs first and figures out the average price per category. The outer query then compares each product against that benchmark. This is called a correlated subquery because the inner part depends on the outer part , specifically on p.category. It is powerful, though on very large tables it can be slow. That is a trade-off worth knowing.

NOT EXISTS is one of the cleanest constructs in SQL for its clarity:

-- Customers who never made a purchase
SELECT * FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM sales s
    WHERE s.customer_id = c.customer_id
);

Notice the SELECT 1 inside , it does not matter what you select there. You are not asking for data. You are just checking: does a matching row exist? If it does not, the customer makes the cut. Clean, readable, efficient.

CTEs — Writing SQL You Can Actually Read Later

Nested subqueries work, but they get ugly fast. Stack a few of them and your query starts looking like a puzzle that even you cannot solve two weeks later.

CTEs — Common Table Expressions fix that. You define named, temporary result sets using the WITH keyword and refer to them cleanly in the main query. The logic does not change. The readability does.

Without CTE:

SELECT * FROM (
    SELECT customer_id, SUM(total_amount) AS total_spent
    FROM sales
    GROUP BY customer_id
) sub
WHERE total_spent > (
    SELECT AVG(total_spent) FROM (
        SELECT SUM(total_amount) AS total_spent
        FROM sales GROUP BY customer_id
    ) inner_sub
);

With CTE:

WITH customer_spending AS (
    SELECT customer_id, SUM(total_amount) AS total_spent
    FROM sales
    GROUP BY customer_id
),
avg_spending AS (
    SELECT AVG(total_spent) AS avg_spent FROM customer_spending
)
SELECT * FROM customer_spending
WHERE total_spent > (SELECT avg_spent FROM avg_spending);

Same result. The CTE version reads almost like plain English ,here is the spending per customer, here is the average, now show me everyone above it. That is the kind of code that survives handovers and 3am debugging sessions.

Window Functions — The Thing That Changes How You Think About SQL

If there is one part of SQL that genuinely changes the way you approach data problems, it is window functions. Once you understand them, a whole category of questions that used to feel complicated becomes straightforward.

The key difference from GROUP BY: window functions compute across rows without collapsing them. You still see every individual row , you just get extra computed columns alongside them.

Ranking is the simplest example:

SELECT 
    customer_id,
    SUM(total_amount) AS total_spent,
    RANK() OVER (ORDER BY SUM(total_amount) DESC) AS rank
FROM sales
GROUP BY customer_id;

Every customer keeps their own row. You just get a rank column added.

NTILE is great for segmentation:

SELECT 
    customer_id,
    SUM(total_amount) AS total_spent,
    NTILE(4) OVER (ORDER BY SUM(total_amount) DESC) AS quartile
FROM sales
GROUP BY customer_id;

Split your customers into four equal groups by spending. Bucket 1 is your top 25%. That is a customer segmentation model in six lines of SQL.

LAG and LEAD let you look at the row before or after the current one , incredibly useful for trend analysis:

SELECT 
    sale_id,
    sale_date,
    total_amount,
    LAG(total_amount) OVER (ORDER BY sale_date) AS previous_sale,
    LEAD(total_amount) OVER (ORDER BY sale_date) AS next_sale
FROM sales;

And running totals — a staple of any financial report:

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

PARTITION BY is where things get really interesting. It lets you run window functions independently within groups , like ranking products inside each category separately:

SELECT 
    product_name,
    category,
    price,
    RANK() OVER (PARTITION BY category ORDER BY price DESC) AS rank_in_category
FROM products;

Each category gets its own ranking that resets to 1. Electronics and Appliances are ranked independently. People often try to solve this kind of thing with complicated joins or multiple queries, when a single window function handles it cleanly.

CASE WHEN — Making Your Data Tell a Story

Raw numbers are fine for machines. People need context. CASE WHEN is how you translate numbers into meaning:

SELECT 
    product_name,
    price,
    CASE
        WHEN price > 1000 THEN 'Premium'
        WHEN price BETWEEN 500 AND 1000 THEN 'Standard'
        ELSE 'Budget'
    END AS price_category
FROM products;

You can do the same for customers based on their total spending:

SELECT 
    c.customer_id,
    c.first_name,
    COALESCE(SUM(s.total_amount), 0) AS total_spent,
    CASE
        WHEN COALESCE(SUM(s.total_amount), 0) > 20000 THEN 'VIP'
        WHEN COALESCE(SUM(s.total_amount), 0) BETWEEN 10000 AND 20000 THEN 'Regular'
        ELSE 'New'
    END AS customer_level
FROM customers c
LEFT JOIN sales s ON c.customer_id = s.customer_id
GROUP BY c.customer_id, c.first_name;

Notice the COALESCE wrapping the SUM. Customers with zero purchases would return NULL from the sum COALESCE catches that and turns it into 0 before the CASE logic runs. Small detail, big difference in your results.

The Production Stuff Nobody Talks About Enough

Writing queries is one skill. Building something reliable that other people can use is another. These are the tools that close that gap.

Stored functions let you wrap logic into something reusable and callable:

CREATE OR REPLACE FUNCTION get_customer_spending(cust_id INT)
RETURNS NUMERIC LANGUAGE plpgsql AS $$
DECLARE total NUMERIC;
BEGIN
    SELECT COALESCE(SUM(total_amount), 0) INTO total
    FROM sales WHERE customer_id = cust_id;
    RETURN total;
END;
$$;

Now instead of rewriting that aggregation every time, you call get_customer_spending(42). One place to maintain if anything ever changes.

Indexes are a performance tool most beginners ignore until their queries start timing out on real data:

CREATE INDEX IF NOT EXISTS idx_sales_product_id ON sales(product_id);

This tells PostgreSQL to build a sorted lookup structure on that column. JOINs and WHERE filters on indexed columns can run orders of magnitude faster. The trade-off is slightly slower inserts , but for read-heavy analytical workloads, that trade is almost always worth it.

Views are saved queries that behave like tables, great for hiding complexity:

CREATE OR REPLACE VIEW product_revenue AS
SELECT product_id, SUM(total_amount) AS total_revenue
FROM sales
GROUP BY product_id;

And transactions are how you protect your data when multiple things need to happen together:

BEGIN;
    INSERT INTO sales VALUES (1000, 1, 1, 2, CURRENT_DATE, 1999.98);
    UPDATE products SET stock_quantity = stock_quantity - 2 WHERE product_id = 1;
COMMIT;

Either both statements execute, or neither does. A sale gets recorded and stock gets updated or nothing changes at all. That guarantee matters more than most people realise until the day a partial update corrupts their production data.

On Dirty Data — It Will Humble You

No matter how well-designed a database is, real-world data finds a way to be messy. NULLs show up where you did not expect them. Phone numbers are empty strings instead of NULL. Emails have trailing spaces or inconsistent capitalisation. These things happen constantly.

SQL gives you the tools:

SELECT COALESCE(email, 'No Email Provided') AS email FROM customers;
SELECT LOWER(email) AS email FROM customers;
SELECT TRIM(first_name) AS clean_name FROM customers;
SELECT CASE WHEN phone_number = '' THEN NULL ELSE phone_number END AS phone FROM customers;

This is not glamorous work. But dirty data going into an aggregation means wrong numbers going into a report, which means bad decisions being made somewhere upstream. Treating data cleaning seriously is not optional , it is part of the craft.

Three Bugs Worth Learning From

These mistakes show up constantly, even in experienced developers' work:

Forgetting to include non-aggregated columns in GROUP BY:

-- Breaks
SELECT product_id, product_name, SUM(total_amount) FROM sales GROUP BY product_id;

-- Works
SELECT s.product_id, p.product_name, SUM(s.total_amount)
FROM sales s
JOIN products p ON s.product_id = p.product_id
GROUP BY s.product_id, p.product_name;

Using WHERE to filter an aggregate result:

-- Breaks
SELECT product_id, SUM(total_amount) FROM sales WHERE SUM(total_amount) > 1000 GROUP BY product_id;

-- Works
SELECT product_id, SUM(total_amount) FROM sales
GROUP BY product_id HAVING SUM(total_amount) > 1000;

Getting the JOIN condition backwards:

-- Wrong — no error thrown, just nonsense results
JOIN products p ON s.customer_id = p.product_id;

-- Right
JOIN products p ON s.product_id = p.product_id;

That last one is especially sneaky. It does not crash. It just quietly returns results that make no logical sense, and you might not notice until someone is staring at a report asking why the numbers look strange.

Where to Go From Here

SQL has been around since the 1970s and there is a good reason it has outlasted almost every technology trend that has come and gone in that time. It is precise, it is expressive, and it is built around a simple idea , your data has answers in it, and SQL is how you ask for them.

The gap between someone who is just starting and someone who is genuinely good at this is not really about syntax. Syntax is easy to look up. The gap is in how you think. Whether you can take a business question, break it into logical steps, and translate each one into a query. That kind of thinking builds up with practice, with reading other people's SQL, and honestly with making enough mistakes that the right patterns start to feel natural.

Get comfortable with JOINs until they stop feeling complicated. Then go learn window functions properly , that is probably the single investment in SQL that pays off fastest. Everything else tends to fall into place after that.

The data is already there. It is just waiting to be asked the right questions.

Written by **Collins Njeru* — Nairobi, Kenya.*

5 SQL FUNCTIONS EVERY BEGINNER SHOULD KNOW

Collins Njeru — Mon, 20 Apr 2026 08:23:01 +0000

"Data is the new oil — and SQL is the refinery."

— Anonymous Data Engineer

Image: A developer working with databases — the everyday environment of an SQL practitioner.

Introduction
What Is SQL?
Function 1 — COUNT()
Function 2 — SUM()
Function 3 — AVG()
Function 4 — UPPER() and LOWER()
Function 5 — CONCAT()
Combining Functions Together
Best Practices
Conclusion

Introduction

If you have ever opened a spreadsheet and thought, "There has to be a better way to manage this data," — you were right. That better way is SQL (Structured Query Language), the industry-standard language for talking to databases.

Whether you are a data analyst, a backend developer, a business intelligence professional, or simply a curious learner, SQL is one of the most valuable skills you can add to your toolkit. It is estimated that over 75% of companies rely on relational databases for their core operations, and every single one of them uses SQL in some form.

However, learning SQL can feel overwhelming at first. There are dozens of functions, keywords, clauses, and syntax rules to absorb. The good news? You do not need to know everything to be effective. In fact, mastering just five core SQL functions will empower you to answer real business questions, clean messy data, and produce meaningful reports.

In this article, we will explore those five essential SQL functions in depth — with clear syntax breakdowns, real-world examples, practical use cases, and tips to help you avoid common beginner mistakes.

Let us get started.

What Is SQL?

Before diving into the functions, it helps to have a brief refresher on what SQL actually is and how it operates.

SQL stands for Structured Query Language. It is a domain-specific language designed for managing and manipulating data stored in relational database management systems (RDBMS) such as:

MySQL
PostgreSQL
Microsoft SQL Server
SQLite
Oracle Database

Image: A visual representation of how relational databases organize data into tables with rows and columns.

SQL operates on a simple principle: data is organized into tables (similar to spreadsheets), where each table has columns (attributes/fields) and rows (records/entries). You write queries to select, filter, sort, group, insert, update, or delete data from these tables.

SQL commands are generally categorized into:

Category	Full Name	Examples
DQL	Data Query Language	`SELECT`
DML	Data Manipulation Language	`INSERT`, `UPDATE`, `DELETE`
DDL	Data Definition Language	`CREATE`, `ALTER`, `DROP`
DCL	Data Control Language	`GRANT`, `REVOKE`

For this article, we will focus on SQL functions, which are built-in operations that process and return values from your data. Specifically, we will cover the five most beginner-friendly and most commonly used SQL functions.

Function 1 — `COUNT()`

What It Does

The COUNT() function returns the number of rows that match a specified condition. It is one of the most frequently used SQL functions and is essential for generating summary statistics and reports.

Syntax

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

Use COUNT(*) to count all rows, including those with NULL values.
Use COUNT(column_name) to count rows where that column is not NULL.

Real-World Example

Imagine you work at an e-commerce company and you have an orders table like this:

order_id	customer_name	product	status	amount
1	Alice Njeri	Laptop	Completed	85000
2	Brian Otieno	Headphones	Pending	4500
3	Carol Wanjiku	Phone	Completed	35000
4	David Kamau	Keyboard	Cancelled	2800
5	Eve Achieng	Monitor	Completed	22000

Question: How many orders are in the table?

SELECT COUNT(*) AS total_orders
FROM orders;

Result:

total_orders
5

Question: How many orders have the status 'Completed'?

SELECT COUNT(*) AS completed_orders
FROM orders
WHERE status = 'Completed';

Result:

completed_orders
3

Use Cases for `COUNT()`

Counting total customers in a database
Counting how many products are out of stock
Counting the number of users who signed up this month
Counting failed transactions in a payment system

Common Beginner Mistake

Beginners often confuse COUNT(*) with COUNT(column_name). Remember:

-- Counts ALL rows (even if some columns are NULL)
SELECT COUNT(*) FROM employees;

-- Counts only rows where 'email' is NOT NULL
SELECT COUNT(email) FROM employees;

Function 2 — `SUM()`

What It Does

The SUM() function calculates the total sum of a numeric column. It is indispensable for financial reporting, sales tracking, inventory management, and any scenario where you need an aggregate total.

Syntax

SELECT SUM(column_name)
FROM table_name
WHERE condition;

Real-World Example

Using the same orders table from above:

Question: What is the total revenue from all completed orders?

SELECT SUM(amount) AS total_revenue
FROM orders
WHERE status = 'Completed';

Result:

total_revenue
142000

This tells you that completed orders have brought in a total of KES 142,000.

You can also use SUM() with GROUP BY to break totals down by category:

SELECT status, SUM(amount) AS revenue_by_status
FROM orders
GROUP BY status;

Result:

status	revenue_by_status
Completed	142000
Pending	4500
Cancelled	2800

Image: Data analysis and financial summaries are classic use cases for the SUM() function.

Use Cases for `SUM()`

Total sales revenue for a period
Total inventory value in a warehouse
Total hours logged by employees in a week
Total amount of discounts applied to orders

Common Beginner Mistake

SUM() only works with numeric columns. Attempting it on a text column will throw an error. Also, SUM() ignores NULL values automatically — which is usually the desired behavior, but good to know!

-- This will FAIL if 'product' is a text column
SELECT SUM(product) FROM orders; --  Error

-- This is CORRECT
SELECT SUM(amount) FROM orders; --  Works

Function 3 — `AVG()`

What It Does

The AVG() function calculates the arithmetic mean (average) of a set of numeric values. Rather than knowing the total, AVG() tells you the typical or central value — which is often more meaningful for decision-making.

Syntax

SELECT AVG(column_name)
FROM table_name
WHERE condition;

Real-World Example

Question: What is the average order amount across all orders?

SELECT AVG(amount) AS average_order_value
FROM orders;

Result:

average_order_value
29860.00

This tells the business that, on average, each order is worth approximately KES 29,860 — a useful figure for forecasting and setting minimum order targets.

Combining with GROUP BY:

SELECT status, AVG(amount) AS avg_amount_by_status
FROM orders
GROUP BY status;

Result:

status	avg_amount_by_status
Completed	47333.33
Pending	4500.00
Cancelled	2800.00

Use Cases for `AVG()`

Average salary across a department
Average student score in an exam
Average delivery time for shipments
Average customer rating for a product
Average page views per day on a website

Common Beginner Mistake

Like SUM(), AVG() ignores NULL values. This means if some rows in your column are NULL, the average is calculated only from the non-null rows — which may or may not be what you want.

-- If 5 rows exist but only 3 have values, AVG uses 3 rows
SELECT AVG(rating) FROM products; -- Only non-NULL ratings are averaged

If you need to include NULL as zero in your average, use COALESCE():

SELECT AVG(COALESCE(rating, 0)) FROM products;

Function 4 — `UPPER()` and `LOWER()`

What They Do

These are string functions that convert text to either all uppercase (UPPER()) or all lowercase (LOWER()). While they seem simple, they are critical for data cleaning, standardization, and consistent formatting across databases.

Syntax

-- Convert to uppercase
SELECT UPPER(column_name) FROM table_name;

-- Convert to lowercase
SELECT LOWER(column_name) FROM table_name;

Real-World Example

Consider a customers table where email addresses and names have been entered inconsistently:

customer_id	name	email
1	alice njeri	ALICE@GMAIL.COM
2	BRIAN OTIENO	brian@yahoo.com
3	Carol Wanjiku	CAROL@OUTLOOK.COM

Problem: The data is inconsistent — some names are lowercase, some uppercase, and emails are mixed.

Solution: Use LOWER() to standardize emails and UPPER() to format names properly.

-- Standardize all emails to lowercase
SELECT customer_id,
       UPPER(name) AS formatted_name,
       LOWER(email) AS standardized_email
FROM customers;

Result:

customer_id	formatted_name	standardized_email
1	ALICE NJERI	alice@gmail.com
2	BRIAN OTIENO	brian@yahoo.com
3	CAROL WANJIKU	carol@outlook.com

You can also use LOWER() in a WHERE clause for case-insensitive searching:

-- Find customer regardless of how their email was entered
SELECT * FROM customers
WHERE LOWER(email) = 'alice@gmail.com';

This ensures your search works whether the email is stored as ALICE@GMAIL.COM, Alice@Gmail.Com, or alice@gmail.com.

Image: Data cleaning and standardization — exactly what UPPER() and LOWER() help accomplish.

Use Cases for `UPPER()` and `LOWER()`

Standardizing email addresses before sending bulk emails
Normalizing user input for case-insensitive login authentication
Formatting customer names consistently for reports
Ensuring uniform data before comparing or joining tables
Cleaning imported CSV data that has inconsistent casing

Common Beginner Mistake

These functions do not modify the actual data in the database. They only transform the output in your query result. To permanently change the data, you need an UPDATE statement:

-- This only changes the display output, NOT the stored data:
SELECT LOWER(email) FROM customers; --  Display only

-- This permanently updates the stored data:
UPDATE customers
SET email = LOWER(email); --  Permanent change

Function 5 — `CONCAT()`

What It Does

The CONCAT() function joins two or more strings together into a single string. It is incredibly useful for combining columns, formatting output, building dynamic messages, and creating full names or addresses from separate fields.

Syntax

-- Standard CONCAT()
SELECT CONCAT(string1, string2, string3, ...) FROM table_name;

-- Alternative using || operator (works in PostgreSQL and SQLite)
SELECT string1 || string2 FROM table_name;

Real-World Example

Suppose your employees table stores first and last names in separate columns:

emp_id	first_name	last_name	department	city
1	Alice	Njeri	Engineering	Nairobi
2	Brian	Otieno	Marketing	Mombasa
3	Carol	Wanjiku	Finance	Nairobi

Question: Generate a full name and a formatted label for each employee.

SELECT
    emp_id,
    CONCAT(first_name, ' ', last_name) AS full_name,
    CONCAT(first_name, ' works in ', department, ' — ', city) AS employee_summary
FROM employees;

Result:

emp_id	full_name	employee_summary
1	Alice Njeri	Alice works in Engineering — Nairobi
2	Brian Otieno	Brian works in Marketing — Mombasa
3	Carol Wanjiku	Carol works in Finance — Nairobi

You can also combine CONCAT() with UPPER() for even more powerful string formatting:

SELECT
    CONCAT(UPPER(first_name), ' ', UPPER(last_name)) AS full_name_caps
FROM employees;

Result:

full_name_caps
ALICE NJERI
BRIAN OTIENO
CAROL WANJIKU

Use Cases for `CONCAT()`

Building full names from first_name and last_name columns
Creating a full address from street, city, and country fields
Generating personalized email greetings like "Dear Alice Njeri,"
Combining a product code with a category code to form a SKU
Building dynamic SQL strings within stored procedures

Common Beginner Mistake

CONCAT() treats NULL values differently depending on the database:

-- In MySQL, CONCAT() returns NULL if ANY argument is NULL:
SELECT CONCAT('Hello', NULL, 'World'); -- Returns: NULL 

-- Use COALESCE() or CONCAT_WS() to handle NULLs safely:
SELECT CONCAT('Hello', COALESCE(NULL, ''), 'World'); -- Returns: HelloWorld 

-- CONCAT_WS (Concatenate With Separator) skips NULLs automatically:
SELECT CONCAT_WS(' ', first_name, middle_name, last_name) FROM employees; --

Combining Functions Together

The true power of SQL functions emerges when you combine them in a single query. Here is an example that uses COUNT(), SUM(), AVG(), and CONCAT() together in one query to produce a rich report:

SELECT
    department,
    COUNT(*) AS total_employees,
    SUM(salary) AS total_salary_budget,
    ROUND(AVG(salary), 2) AS average_salary,
    CONCAT('Dept: ', UPPER(department)) AS department_label
FROM employees
GROUP BY department
ORDER BY total_salary_budget DESC;

This single query tells you — for each department:

How many employees exist
The total salary budget
The average salary
A nicely formatted department label

This is the kind of query you would write to build a management dashboard or a financial summary report.

Best Practices

1. Always Use Aliases (`AS`)

Use the AS keyword to give your function outputs meaningful names. It makes your results readable and your queries professional.

-- Hard to read:
SELECT COUNT(*), SUM(amount), AVG(amount) FROM orders;

-- Much better:
SELECT 
    COUNT(*) AS total_orders,
    SUM(amount) AS total_revenue,
    AVG(amount) AS avg_order_value
FROM orders;

2. Handle NULL Values Proactively

Use COALESCE() or IFNULL() to deal with NULL values before passing them to aggregate functions, especially when calculating averages or concatenating strings.

3. Use `GROUP BY` with Aggregate Functions

When using COUNT(), SUM(), or AVG() alongside non-aggregate columns, always pair them with a GROUP BY clause.

--  This will cause an error in most databases:
SELECT department, COUNT(*) FROM employees;

--  This is correct:
SELECT department, COUNT(*) FROM employees GROUP BY department;

4. Test on Small Datasets First

Before running aggregate queries on millions of rows, test on a limited subset using LIMIT:

SELECT * FROM orders LIMIT 10;

5. Comment Your Queries

Use -- for single-line comments and /* */ for multi-line comments to document your queries:

-- Get a revenue summary by order status
SELECT 
    status,
    COUNT(*) AS order_count,     -- Number of orders per status
    SUM(amount) AS total_amount  -- Total value per status
FROM orders
GROUP BY status;

Conclusion

SQL does not have to be intimidating. By mastering just five core functions — COUNT(), SUM(), AVG(), UPPER()/LOWER(), and CONCAT() — you already have the tools to:

Summarize and analyze data
Clean and standardize messy datasets
Build meaningful reports and dashboards
Answer real business questions with confidence

SQL IS NOT BAD - It's Running the World And Most Developers Don't Know Half of It....

Collins Njeru — Fri, 17 Apr 2026 12:44:00 +0000

"Every database, every app, every swipe of your card — SQL is there."

The language that outlived every trend since 1974

Written for developers, analysts, and data enthusiasts at every level

From zero to advanced — in one article

Last updated: 2026 | PostgreSQL-first, broadly compatible

"In an era obsessed with the newest JavaScript framework or the latest AI buzzword, SQL has done something remarkable , it has outlived every trend thrown at it since 1974."*

NoSQL was supposed to kill it.

Big Data was supposed to replace it.

Pandas, Spark, and cloud warehouses were going to make it irrelevant.

Yet here we are. SQL is baked into everything from PostgreSQL and MySQL to Snowflake, BigQuery, and DuckDB.

~~SQL is dying.~~ ← False. SQL is thriving.

If you work with data — in any capacity — SQL fluency is not optional. It's oxygen.

The Anatomy of a SQL Query
JOINs Demystified
Aggregation and GROUP BY
Window Functions — SQL's Hidden Superpower
Subqueries vs CTEs
Indexing — Why Your Queries Are Slow
Transactions and ACID
10 SQL Mistakes Hurting Your Performance
Advanced SQL Patterns
The Future of SQL

1. The Anatomy of a SQL Query

Before we run, let's walk. A SQL query has a very specific logical execution order that trips up even experienced developers.

The order you write a query:

SELECT name, COUNT(*) AS total_orders
FROM customers
JOIN orders ON customers.id = orders.customer_id
WHERE customers.country = 'Kenya'
GROUP BY name
HAVING COUNT(*) > 5
ORDER BY total_orders DESC
LIMIT 10;

The order the database executes it:

1. FROM       → Identify the tables
2. JOIN       → Combine related data
3. WHERE      → Filter rows (before grouping)
4. GROUP BY   → Collapse rows into groups
5. HAVING     → Filter groups (after aggregation)
6. SELECT     → Choose columns to return
7. ORDER BY   → Sort the result
8. LIMIT      → Cut result to N rows

Key insight: WHERE runs before GROUP BY. This means you cannot filter on an aggregated column using WHERE. That's what HAVING is for.

Nested tip: Get this order wrong, and you'll spend hours debugging a query that looks perfectly fine.

What You Should Know Before Writing Any Query

Always understand the shape of your data first
Never assume column types — check them
Use EXPLAIN before running on large tables
Limit your results while exploring

Your SQL Query Checklist

[x] Understand the execution order
[x] Know which table is the "driving" table
[x] Add a LIMIT when exploring
[ ] Run EXPLAIN ANALYZE to check the query plan
[ ] Check indexes on JOIN and WHERE columns
[ ] Peer-review complex queries before production

2. JOINs Demystified

JOINs are the heart of relational databases. If you understand them deeply, you understand SQL.

The Main JOIN Types

JOIN Type	What It Returns	Use Case
`INNER JOIN`	Rows matching in both tables	Most common join
`LEFT JOIN`	All left rows + matched right rows	Find unmatched records
`RIGHT JOIN`	All right rows + matched left rows	Rare — prefer LEFT JOIN
`FULL OUTER JOIN`	All rows from both tables	Complete data comparison
`CROSS JOIN`	Cartesian product of both tables	Combinations/permutations
`SELF JOIN`	A table joined to itself	Hierarchies, org charts

Visual Mental Model

Think of two overlapping circles:

INNER JOIN → only the overlap
LEFT JOIN → entire left circle + overlap
FULL OUTER JOIN → both circles entirely

A Common Real-World Example

-- Find customers who have NEVER placed an order
SELECT c.name, c.email
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.customer_id IS NULL;

The trick of using LEFT JOIN ... WHERE right_table.id IS NULL to find non-matching rows is one of the most elegant patterns in SQL. Learn it. Use it often.

The SELF JOIN — More Useful Than You Think

-- Find employees and their managers (from the same table)
SELECT 
    e.name AS employee,
    m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

3. Aggregation and GROUP BY

Aggregation is where SQL transforms raw rows into business intelligence.

Core Aggregate Functions

SELECT
    department,
    COUNT(*)                    AS total_staff,
    AVG(salary)                 AS avg_salary,
    MAX(salary)                 AS highest_salary,
    MIN(salary)                 AS lowest_salary,
    SUM(salary)                 AS total_payroll,
    COUNT(DISTINCT job_title)   AS unique_roles
FROM employees
GROUP BY department
ORDER BY total_payroll DESC;

The Golden Rule of GROUP BY

Every column in SELECT must either be in GROUP BY or be inside an aggregate function.

Wrong vs Right

--  WRONG — 'name' is neither grouped nor aggregated
SELECT department, name, COUNT(*)
FROM employees
GROUP BY department;

--  CORRECT
SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department;

Filtering Groups with HAVING

-- Only departments with more than 10 employees earning above $50,000
SELECT department, COUNT(*) AS headcount
FROM employees
WHERE salary > 50000          -- filters rows BEFORE grouping
GROUP BY department
HAVING COUNT(*) > 10          -- filters groups AFTER aggregation
ORDER BY headcount DESC;

4. Window Functions — SQL's Hidden Superpower

If there is one feature that separates SQL beginners from SQL professionals, it is window functions.

Window functions let you perform calculations across related rows — without collapsing the result like GROUP BY does.

The Syntax

function_name() OVER (
    PARTITION BY column     -- Optional: group rows into windows
    ORDER BY column         -- Optional: define order within the window
    ROWS BETWEEN ...        -- Optional: define the window frame
)

Ranking Functions

SELECT
    name,
    department,
    salary,
    RANK()        OVER (PARTITION BY department ORDER BY salary DESC) AS rank,
    DENSE_RANK()  OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank,
    ROW_NUMBER()  OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
FROM employees;

Difference Between Ranking Functions

Function	Handles Ties	Example Output
`ROW_NUMBER()`	Always unique	1, 2, 3, 4
`RANK()`	Skips after tie	1, 1, 3, 4
`DENSE_RANK()`	No skip after tie	1, 1, 2, 3

Running Totals and Moving Averages

-- Running total of sales with a 7-day moving average
SELECT
    sale_date,
    amount,
    SUM(amount) OVER (ORDER BY sale_date) AS running_total,
    AVG(amount) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_7day_avg
FROM sales;

LAG and LEAD — Comparing to Neighboring Rows

-- Month-over-month revenue growth
SELECT
    month,
    revenue,
    LAG(revenue, 1)  OVER (ORDER BY month) AS prev_month,
    LEAD(revenue, 1) OVER (ORDER BY month) AS next_month,
    revenue - LAG(revenue, 1) OVER (ORDER BY month) AS growth
FROM monthly_revenue;

Window functions are the reason SQL is still king for analytics. No other tool lets you express this kind of computation as cleanly.

5. Subqueries vs CTEs

The Subquery — Powerful but Messy at Scale

A query nested inside another query:

SELECT name, total_spent
FROM (
    SELECT customer_id, SUM(amount) AS total_spent
    FROM orders
    GROUP BY customer_id
) AS customer_totals
JOIN customers ON customers.id = customer_totals.customer_id
WHERE total_spent > (
    SELECT AVG(total_spent) FROM (
        SELECT SUM(amount) AS total_spent
        FROM orders
        GROUP BY customer_id
    ) AS sub
);

This works — but grows unreadable fast.

The CTE (Common Table Expression) — Clean, Named, Reusable

CTEs let you name intermediate results using the WITH keyword:

WITH customer_totals AS (
    SELECT customer_id, SUM(amount) AS total_spent
    FROM orders
    GROUP BY customer_id
),
avg_spend AS (
    SELECT AVG(total_spent) AS avg_val FROM customer_totals
)
SELECT 
    c.name, 
    ct.total_spent
FROM customers c
JOIN customer_totals ct ON c.id = ct.customer_id
CROSS JOIN avg_spend
WHERE ct.total_spent > avg_spend.avg_val
ORDER BY ct.total_spent DESC;

Prefer CTEs over nested subqueries for anything beyond one level. Your teammates — and your future self — will thank you.

Recursive CTEs — Conquering Hierarchical Data

-- Traverse an employee org chart tree
WITH RECURSIVE org_chart AS (
    -- Base case: the CEO (no manager)
    SELECT id, name, manager_id, 0 AS depth
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive case: employees joined to their managers
    SELECT e.id, e.name, e.manager_id, oc.depth + 1
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT depth, name FROM org_chart ORDER BY depth, name;

When to Use Each

Subquery — one-off, simple, single-level nesting
CTE — multi-step logic, readable complex queries
Recursive CTE — trees, graphs, hierarchies

6. Indexing — Why Your Queries Are Slow

No SQL article is complete without talking about performance. And no performance topic matters more than indexing.

What Is an Index?

An index is a separate data structure (usually a B-tree) that lets the database find rows without scanning every record. Think of it as the index in the back of a textbook.

The Speed Difference Is Staggering

-- Without index — scanning 50 million rows
SELECT * FROM customers WHERE email = 'alice@example.com';
-- ⏱ Time: 8.4 seconds

-- Create the index
CREATE INDEX idx_customers_email ON customers(email);

-- With index — direct lookup
SELECT * FROM customers WHERE email = 'alice@example.com';
-- ⏱ Time: 0.003 seconds

When to Create an Index

Columns used frequently in WHERE clauses
Columns used in JOIN conditions
Columns used in ORDER BY or GROUP BY on large tables
Foreign key columns

When Indexes Hurt

Columns with very low cardinality (e.g., is_active = TRUE/FALSE only)
Tables written to far more often than they're read
Too many indexes slow down INSERT, UPDATE, and DELETE

EXPLAIN — Your Best Debugging Friend

EXPLAIN ANALYZE
SELECT c.name, COUNT(o.id) AS order_count
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE c.country = 'Kenya'
GROUP BY c.name;

Use EXPLAIN ANALYZE to see whether the database does a full table scan (Seq Scan) or uses an index (Index Scan). Never optimize blindly.

7. Transactions and ACID

Every time you move money, book a seat, or submit an order — you're trusting transactions to make sure nothing breaks mid-operation.

The ACID Properties

Property	Meaning	Real-World Guarantee
Atomicity	All-or-nothing	No partial transfers
Consistency	Valid state → valid state	Rules always enforced
Isolation	Concurrent transactions don't interfere	No dirty reads
Durability	Committed data survives crashes	Your data is safe

A Bank Transfer — The Classic Example

BEGIN TRANSACTION;

-- Step 1: Deduct from sender
UPDATE accounts SET balance = balance - 1000 WHERE user_id = 'alice';

-- Step 2: Add to receiver
UPDATE accounts SET balance = balance + 1000 WHERE user_id = 'bob';

-- If both succeed:
COMMIT;

-- If anything fails:
-- ROLLBACK;

Without transactions: a server crash between Step 1 and Step 2 means Alice loses money that Bob never receives.

With transactions: either both steps happen, or neither does.

8. 10 SQL Mistakes Hurting Your Performance

SELECT * — Never in Production

Always name the columns you need. SELECT * fetches unnecessary data, breaks when schemas change, and prevents index-only scans.

The Top 10 Offenders

SELECT * in production code — fetch only what you need
Not filtering before joining — WHERE early, not after millions of rows are joined
Functions on indexed columns in WHERE

   --  Kills the index
   WHERE YEAR(created_at) = 2024

   --  Preserves the index
   WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'

HAVING for non-aggregated filters — use WHERE before grouping
OR on indexed columns — can prevent index use; try UNION ALL
NOT IN with NULLs — returns no rows if subquery has a NULL; use NOT EXISTS
Missing indexes on foreign keys — always index FK columns used in JOINs
N+1 queries — never query inside a loop; use JOINs or batch queries
Implicit type conversions — comparing VARCHAR to integers forces a cast on every row
Forgetting LIMIT while exploring — 3 rows in dev can be 30 million in prod

9. Advanced SQL Patterns

UPSERT — Insert or Update in One Statement

-- PostgreSQL syntax
INSERT INTO users (id, email, login_count)
VALUES (42, 'alice@example.com', 1)
ON CONFLICT (id) DO UPDATE
SET login_count = users.login_count + 1,
    last_seen   = NOW();

Pivot with CASE WHEN

-- Turn rows into columns
SELECT
    product,
    SUM(CASE WHEN quarter = 'Q1' THEN revenue ELSE 0 END) AS Q1,
    SUM(CASE WHEN quarter = 'Q2' THEN revenue ELSE 0 END) AS Q2,
    SUM(CASE WHEN quarter = 'Q3' THEN revenue ELSE 0 END) AS Q3,
    SUM(CASE WHEN quarter = 'Q4' THEN revenue ELSE 0 END) AS Q4
FROM sales
GROUP BY product;

Find Duplicates

SELECT email, COUNT(*) AS occurrences
FROM users
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY occurrences DESC;

Gap Detection — Finding Missing IDs

SELECT id + 1 AS gap_start
FROM orders o
WHERE NOT EXISTS (
    SELECT 1 FROM orders WHERE id = o.id + 1
)
AND id < (SELECT MAX(id) FROM orders);

Percentile Calculation

SELECT
    department,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary,
    PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY salary) AS p90_salary
FROM employees
GROUP BY department;

10. The Future of SQL

Far from fading, SQL is evolving aggressively:

DuckDB — in-process analytics SQL that runs on your laptop with serious performance
Snowflake / BigQuery / Redshift — cloud data warehouses where SQL scales to petabytes
dbt (data build tool) — SQL as software engineering, with version control and testing
SQL on JSON — modern databases now query deeply nested JSON natively
AI-assisted SQL — LLMs generate SQL, but knowing it deeply makes you 10x better at validating that output

SQL will be the last programming language to die.

And it won't.

Your SQL Learning Roadmap

Beginner

[ ] SELECT, FROM, WHERE basics
[ ] INNER JOIN and LEFT JOIN
[ ] GROUP BY and aggregate functions
[ ] ORDER BY and LIMIT

Intermediate

[ ] All JOIN types
[ ] Subqueries and CTEs
[ ] HAVING vs WHERE
[ ] NULL handling

Advanced

[ ] Window functions (RANK, LAG, LEAD, running totals)
[ ] Recursive CTEs
[ ] Indexing strategy and EXPLAIN
[ ] Transactions and isolation levels
[ ] Query optimization techniques

Conclusion

SQL is not just a tool. It's a way of thinking about data — relationally, declaratively, and powerfully.

The developers and analysts who master SQL don't just write better queries. They:

Think more clearly about data problems
Debug faster
Communicate better across engineering, product, and business teams

The language is 50+ years old and still deeply relevant. That is not an accident — it's proof.

Start writing better SQL today. The data is waiting.

TAMING DATA CHAOS IN POWER BI: A Guide to Joins, Relationships, and Schemas

Collins Njeru — Sun, 29 Mar 2026 19:07:40 +0000

Data modeling is the backbone of effective analytics in Power BI. It defines how tables connect, interact, and provide meaningful insights. Without a proper model, even the most advanced visuals can mislead. This article explores SQL joins, Power BI relationships, schemas, and common modeling practices using a customer dataset as an example.

What is Data Modeling?

Data modeling is the process of structuring data to represent real-world entities and their relationships. In Power BI, this involves:

Tables: Fact tables (transactions, metrics) and Dimension tables (descriptive attributes).
Relationships: Logical connections between tables.
Schemas: The overall design of how tables are organized.

A well-designed model ensures that filters, measures, and visuals behave as expected. Poor modeling often leads to incorrect totals, duplicated counts, or slow performance.

Example Dataset

We’ll use two simple tables:

Customers: CustomerID, Name, Region
Orders: OrderID, CustomerID, OrderDate, Amount

This dataset is small, but it illustrates the principles that scale to enterprise-level models.

SQL Joins Explained

Joins combine data from multiple tables based on a common key. In Power BI, joins are performed in Power Query using Merge Queries.

1. INNER JOIN

Definition: Returns rows with matching keys in both tables.
Example: Customers who placed orders.
Diagram (ASCII):
Use Case: Useful when analyzing only active customers.

2. LEFT JOIN

Definition: Returns all rows from the left table and matching rows from the right.
Example: All customers, with orders if they exist.
Use Case: Identify customers who have not placed orders.

3. RIGHT JOIN

Definition: Returns all rows from the right table and matching rows from the left.
Example: All orders, with customer details if available.
Use Case: Ensures no order is excluded even if customer data is missing.

4. FULL OUTER JOIN

Definition: Returns all rows when there is a match in either table.
Example: All customers and all orders, matched where possible.
Use Case: Data reconciliation across systems.

5. LEFT ANTI JOIN

Definition: Returns rows from the left table that have no match in the right.
Example: Customers who never placed an order.
Use Case: Marketing campaigns targeting inactive customers.

6. RIGHT ANTI JOIN

Definition: Returns rows from the right table that have no match in the left.
Example: Orders without a customer record.
Use Case: Detecting data quality issues.

Power BI Relationships

Relationships define how tables interact in the Model View.

Types of Relationships

One-to-Many (1:M): One customer → many orders. Most common.
Many-to-Many (M:M): Both sides can have multiple matches. Requires bridge tables.
One-to-One (1:1): Rare. One employee → one profile.

Cardinality

Cardinality defines the uniqueness of values in a relationship. For example, CustomerID is unique in Customers but repeats in Orders.

Active vs Inactive Relationships

Active: Default relationship used in visuals.
Inactive: Can be activated using DAX functions like:

CALCULATE(
    SUM(Orders[Amount]),
    USERELATIONSHIP(Customers[CustomerID], Orders[CustomerID])
)

Cross-Filter Direction

Single: Filters flow one way (e.g., Customers → Orders).
Both: Filters flow both ways, useful for complex models but can cause ambiguity.

Joins vs Relationships

Joins: Combine data during query (Power Query).
Relationships: Define logical connections in the data model (Model View).

Think of joins as data preparation and relationships as data modeling. Both are essential, but they serve different purposes.

Fact vs Dimension Tables

Fact Tables: Contain metrics (sales, revenue). Example: Orders.
Dimension Tables: Contain descriptive attributes (customer, product). Example: Customers.

Separating facts and dimensions improves clarity and performance. Facts answer “what happened?” while dimensions answer “who, what, when, where?.

Schemas in Power BI

1. Star Schema

Structure: Central fact table connected to dimension tables.
Use Case: Best practice for performance and clarity.
Example: Orders linked to Customers, Products, Dates**

2. Snowflake Schema

Structure: Dimensions normalized into multiple related tables.
Use Case: When dimensions have hierarchical attributes.
Example: Customers linked to Regions and Countries.

3. Flat Table (DLAT)

Structure: All data in one table.
Use Case: Quick prototypes, but poor for scalability.

Role-Playing Dimensions

Sometimes the same dimension is used multiple times. Example:

Date Dimension: Used for Order Date, Ship Date, and Delivery Date.
Solution: Duplicate the dimension table and rename accordingly:,Date_Order,Date_Ship,Date_Delivery

This avoids ambiguity and allows precise filtering.

Common Modeling Issues

Ambiguous relationships: Multiple paths between tables can confuse filters.
Circular references: Loops in relationships cause errors.
Performance bottlenecks: Using flat tables or M:M relationships excessively.
Inactive filters: Forgetting to activate relationships in DAX.

Step-by-Step in Power BI

Load Data: Import Customers and Orders.
Power Query Joins: Use Merge Queries for SQL-style joins.
Model View: Define relationships (CustomerID → CustomerID).
Schema Design: Organize into star or snowflake schemas.
Validate: Build visuals (e.g., total sales by region) to confirm filters work.

Hands-On DAX Examples

Total Sales by Region

Total Sales = SUM(Orders[Amount])

Customers Without Orders

Inactive Customers =
CALCULATETABLE(
Customers,
NOT(RELATEDTABLE(Orders))

Sales by Order Date vs Ship Date

Sales by Ship Date =
CALCULATE(
SUM(Orders[Amount]),
USERELATIONSHIP(Orders[ShipDate], Date[Date])

Real-Life Use Cases

Retail: Identify customers who haven’t purchased recently (LEFT ANTI JOIN).
Finance: Reconcile transactions across systems (FULL OUTER JOIN).
Logistics: Track shipments using role-playing Date dimensions.
Marketing: Segment customers by region and purchase behavior.

Conclusion

Data modeling in Power BI is about clarity, efficiency, and accuracy. By mastering joins, relationships, schemas, and best practices, you ensure that your dashboards tell the right story. Whether you’re building a star schema or handling role-playing dimensions, thoughtful modeling is the key to reliable insights.

With a clean model, you can confidently answer questions like:

Which regions have the highest sales?
Which customers are inactive?
How do shipping delays affect revenue?

Power BI provides the tools,your job is to design the model that makes the data speak.

LINUX AS THE NERVOUS SYSTEM OF DATA ENGINEERING

Collins Njeru — Sat, 28 Mar 2026 21:26:57 +0000

Data engineering is the backbone of modern data-driven organizations. It enables the collection, transformation, and delivery of data at scale. While tools like Apache Spark, Hadoop, and Kafka are essential, the operating system powering these tools is equally critical.

Linux has emerged as the preferred OS due to its stability, scalability, flexibility, and open-source nature. This article explores Linux’s role in real-world data engineering, including essential skills, workflow management, tool integration, cloud deployment, and practical examples.

WHY LINUX DOMINATES DATA ENINEERING

Linux has become the de facto standard for data engineers due to several key advantages:

Open-Source Flexibility

Fully customizable for specific workloads
Kernel can be optimized for performance
Lightweight distributions work well for containerized workflows

Stability and Uptime

Runs continuously with minimal downtime
Ideal for mission-critical production pipelines

Cost-Effectiveness

Free to use, reducing infrastructure costs
Scales easily without expensive licenses

Community Support

Extensive documentation, forums, and troubleshooting resources
Large community of contributors and developers

CORE LINUX SKILLS FOR DATA ENGINEERS

1. FILE SYSTEM NAVIGATION

# List files
ls

# Change directory
cd /path/to/directory

# Show current working directory
pwd

# Find files
find /path/to/search -name "dataset.csv"

2. PROCESS MANAGEMENT

# Show all running processes
ps aux

# Monitor system resource usage
top

# Kill a specific process
kill <pid>

3. SHELL SCRIPTING

#!/bin/bash
# Download and process data
wget http://example.com/dataset.csv
python process_data.py dataset.csv

4.Permission & Ownership

# Change file permissions
chmod 755 my_file.txt

# Change file ownership
chown user:group my_file.txt

5.Package Management

# Install a package on Debian-based systems
sudo apt install package-name

# Update all packages
sudo apt update && sudo apt upgrade

lINUX IN DATA PIPELINES

1. Scheduling Tasks with Cron

# Edit cron jobs
crontab -e

# Schedule a pipeline to run every hour
0 * * * * /home/user/data_pipeline.sh

2. Automating ETL with Shell Scripts

#!/bin/bash
# Download data
wget http://example.com/data.csv

# Transform data
awk -F, '{print $1, $2, $3}' data.csv > transformed_data.csv

# Load into PostgreSQL
psql -U user -d dbname -c "\copy my_table FROM transformed_data.csv WITH CSV"

3. Logging Pipeline Output

#!/bin/bash
echo "$(date): Pipeline started" >> /var/log/data_pipeline.log
python etl_script.py >> /var/log/data_pipeline.log 2>&1
echo "$(date): Pipeline finished" >> /var/log/data_pipeline.log

INTEGRATION WITH DATA ENGINEERING TOOLS

1.Apache Hadoop

# Execute a Hadoop job
hadoop jar /usr/local/hadoop/hadoop-examples.jar wordcount /input /output

2.Apache Kafka

# Start Zookeeper
bin/zookeeper-server-start.sh config/zookeeper.properties

# Start Kafka broker
bin/kafka-server-start.sh config/server.properties

# Produce messages
bin/kafka-console-producer.sh --topic my_topic --bootstrap-server localhost:9092

# Consume messages
bin/kafka-console-consumer.sh --topic my_topic --from-beginning --bootstrap-server localhost:9092

3.Apache Spark

# Submit a Spark job
spark-submit --master local[4] etl_spark_job.py

4.Docker & Kubernetes

# Build Docker image
docker build -t mydataengineerimage .

# Run Docker container
docker run -d --name data_pipeline_container mydataengineerimage

# Deploy Kubernetes resources
kubectl apply -f data_pipeline_deployment.yaml

LINUX IN CLOUD AND BIG DATA ENVIRONMENTS

1.Cloud Servers and Virtual Machines

# Launch Ubuntu VM on AWS
aws ec2 run-instances \
    --image-id ami-0abcdef1234567890 \
    --count 1 \
    --instance-type t2.medium \
    --key-name MyKeyPair \
    --security-group-ids sg-0123456789abcdef0 \
    --subnet-id subnet-6e7f829e

2.Monitoring System Resources

# CPU usage
top

# Memory usage
free -h

# Disk usage
df -h

3.Debugging and Troubleshooting

Cheking logs

# View system logs
tail -f /var/log/syslog

# View pipeline logs
tail -f /var/log/data_pipeline.log

Killing Stuck Processes

# Find process ID
ps aux | grep etl_script.py

# Kill process
kill -9 <pid>

CHALLENGES OF USING LINUX IN DATA ENGINEERING

Steep learning Curve : Command-line usage can be intimidating for beginners
Debugging Complexity : Requires familiarity with logs,permisssions and processes
Automation Dependency : Heavy reliance on scripts and CLI tools

CONCLUSION

Linux is essential for real-world data engineering. It provides the foundation for stable, scalable, and efficient data pipelines. By mastering Linux skills, data engineers can:

Build robust ETL pipelines
Integrate seamlessly with Hadoop,spark and Kafka
Deploy applications in cloud and containerized environments
Monitor and Troubleshoot complex workflows

IN today's data-driven world,linux is more than an operating system,it is a critical enabler of modern data engineering

Should you join Data Engineering?A guide to the tools you'll use

Collins Njeru — Mon, 16 Mar 2026 12:11:50 +0000

Introduction

Many aspiring technologists find themselves at a crossroad:is data engineering the right career path for me.The hesitation often comes from uncertainty about the tools and technologies involved. This article breaks down the core categories of data engineering tools, giving you a clear picture of what you’ll be working with if you decide to join the field.

Core categories of data engineering tools

1.Data ingestion & Integration

Data engineering starts with collecting information from multiple sources

Fivetran /Stitch/ Hevo Data : Automate extraction from SaaS apps and databases

Apache Kafka : Real-time streaming and event-driven pipelines.

Apache Nifi : Flow-based ingestion and routing.

2.Data storage & Warehousing

Once data is ingested, it needs a reliable home.

Snowflake:Cloud-native warehouse with scalability.

Google BigQuery:Serverless, highly scalable analytics warehouse.

Amazon Redshift :AWS-based warehouse optimized for queries.

3.Data processing & transformation

Raw data must be cleaned and transformed before use.

Apache spark:Distributed computing for batch and streaming.

Hadoop:Large-scale storage and batch processing.

Dbt (Data Build Tool):SQL-based transformations for analytics teams.

4. Workflow & orchestration

Pipelines need automation and scheduling.

Apache Airflow:Workflow automation and DAG scheduling.

Prefect/luigi :Alternatives for managing complex workflows.

5.Infrastructure & Deployment

Behind the scenes, infrastructure ensures scalability.

Docker & Kubernetes:Containerization and orchestration.

Terraform:Infrastructure as Code for cloud resources.

6.Monitoring & Quality

Data must be trustworthy and pipelines reliable.

Great expectations :Data validation and quality checks.

Datadog / Prometheus :Monitoring pipelines and infrastructure

Key Considerations

Scalability: Spark and Snowflake excel with large datasets.

Real-Time vs Batch: Kafka is unmatched for streaming; Hadoop and Spark dominate batch workloads.
Cloud Integration: Align tools with your provider (AWS Redshift, GCP BigQuery, Azure Synapse ).
Cost:Open-source tools are free but require setup; managed services reduce overhead but add licensing costs.

Conclusion

Joining data engineering means stepping into a field where you’ll design the backbone of modern businesses. The tools may seem overwhelming at first, but each one solves a specific problem together, they form a powerful toolkit. If you’re excited about building systems that move, store, and transform data at scale, then data engineering isn’t just a career option; it’s a future-proof calling.

DEV Community: Collins Njeru

From Chaos to Clarity: How SQL Transforms Raw Data into Powerful Stories

The Database We Are Working With

Start Simple, Stay Curious

JOINs — Where the Real Magic Happens

Subqueries — Answering Questions Inside Questions

CTEs — Writing SQL You Can Actually Read Later

Window Functions — The Thing That Changes How You Think About SQL

CASE WHEN — Making Your Data Tell a Story

The Production Stuff Nobody Talks About Enough

On Dirty Data — It Will Humble You

Three Bugs Worth Learning From

Where to Go From Here

5 SQL FUNCTIONS EVERY BEGINNER SHOULD KNOW

Table of Contents

Introduction

What Is SQL?

Function 1 — COUNT()

What It Does

Syntax

Real-World Example

Use Cases for COUNT()

Common Beginner Mistake

Function 2 — SUM()

What It Does

Syntax

Real-World Example

Use Cases for SUM()

Common Beginner Mistake

Function 3 — AVG()

What It Does

Syntax

Real-World Example

Use Cases for AVG()

Common Beginner Mistake

Function 4 — UPPER() and LOWER()

What They Do

Syntax

Real-World Example

Use Cases for UPPER() and LOWER()

Common Beginner Mistake

Function 5 — CONCAT()

What It Does

Syntax

Real-World Example

Use Cases for CONCAT()

Common Beginner Mistake

Combining Functions Together

Best Practices

1. Always Use Aliases (AS)

2. Handle NULL Values Proactively

3. Use GROUP BY with Aggregate Functions

4. Test on Small Datasets First

5. Comment Your Queries

Conclusion

SQL IS NOT BAD - It's Running the World And Most Developers Don't Know Half of It....

"Every database, every app, every swipe of your card — SQL is there."

The language that outlived every trend since 1974

Written for developers, analysts, and data enthusiasts at every level

From zero to advanced — in one article

Last updated: 2026 | PostgreSQL-first, broadly compatible

Table of Contents

1. The Anatomy of a SQL Query

What You Should Know Before Writing Any Query

Your SQL Query Checklist

2. JOINs Demystified

The Main JOIN Types

Visual Mental Model

A Common Real-World Example

The SELF JOIN — More Useful Than You Think

3. Aggregation and GROUP BY

Core Aggregate Functions

The Golden Rule of GROUP BY

Wrong vs Right

Filtering Groups with HAVING

4. Window Functions — SQL's Hidden Superpower

The Syntax

Ranking Functions

Difference Between Ranking Functions

Running Totals and Moving Averages

Function 1 — `COUNT()`

Use Cases for `COUNT()`

Function 2 — `SUM()`

Use Cases for `SUM()`

Function 3 — `AVG()`

Use Cases for `AVG()`

Function 4 — `UPPER()` and `LOWER()`

Use Cases for `UPPER()` and `LOWER()`

Function 5 — `CONCAT()`

Use Cases for `CONCAT()`

1. Always Use Aliases (`AS`)

3. Use `GROUP BY` with Aggregate Functions