DEV Community: Gathuru_M

TaskFlow API vs. Traditional Operators: Practical Airflow ETL Pipeline

Gathuru_M — Sun, 07 Jun 2026 19:56:12 +0000

In my last article, we went over the foundational pillars of Apache Airflow—DAGs, Tasks, and why orchestration beats manual scripts.

For this practical Airflow project, we will build
an ETL pipeline to aggregate market data from Massive API. But instead of just writing it one way, we look into writing two different versions of the same DAG:

The Traditional Approach: Using classic standard operators (PythonOperator) and manual XCom pulling/pushing.
The Modern Approach: Using the TaskFlow API (@dag and @task decorators) to make the code clean and Pythonic.

If you’re confused about the difference or wondering which one you should use in your projects, let’s break down both.

The Goal: Aggregating Data from Massive API

The pipeline does three basic things:

Extract: Pulls raw daily open/close asset data from our market API client.
Transform: Normalizes the heavy, nested JSON payload into a clean structure using pandas.
Load: Inserts the structured records into a cloud PostgreSQL database.

Approach 1: The Traditional Way (Standard Operators)

When Airflow was first built, you had to explicitly define every single task using an Operator class and manually stitch them together using the bitshift operators (>>).

The trickiest part here is data sharing. Because tasks run in isolation, we have to use explicit XComs (cross-communications) to pass our API payload from the extract task to the transform task.

Here is how the traditional DAG looks:

from airflow import DAG
from airflow.operators.python import PythonOperator
from datetime import datetime, timedelta
import pandas as pd
# (Assume our custom API extraction and DB loading logic are imported here)

default_args = {
    'owner': 'my_name',
    'start_date': datetime(2026, 6, 1),
    'retries': 1,
    'retry_delay': timedelta(minutes=5),
}

def _extract(ti):
    # Fetching data from our massive asset API
    raw_data = {"ticker": "BTC", "price": 65000, "timestamp": "2026-06-07T00:00:00Z"} 
    # We MUST explicitly push to XCom so the next task can see it
    ti.xcom_push(key='raw_market_data', value=raw_data)

def _transform(ti):
    # We MUST explicitly pull from XCom
    raw_data = ti.xcom_pull(key='raw_market_data', task_ids='extract_task')
    print(f"Transforming data for: {raw_data['ticker']}")
    # Transformation logic goes here...
    return raw_data  # Returning automatically pushes to default XCom

with DAG(
    dag_id='market_data_traditional_v1',
    default_args=default_args,
    schedule_interval='@hourly',
    catchup=False
) as dag:

    extract_task = PythonOperator(
        task_id='extract_task',
        python_callable=_extract
    )

    transform_task = PythonOperator(
        task_id='transform_task',
        python_callable=_transform
    )

    # Stitching the pipeline dependency together
    extract_task >> transform_task

The Verdict on Traditional:

It works perfectly, but it feels heavy. You have to write a lot of boilerplate code just to set up the tasks, and passing data requires explicitly passing the Task Instance (ti) and remembering exact task_ids string names. If you typo a string, your whole pipeline crashes.

Approach 2: The Modern Way (TaskFlow API)

Introduced in Airflow 2.0, the TaskFlow API uses Python decorators (@dag and @task). It fundamentally changes how we write pipelines by making Airflow handle XComs silently behind the scenes.

Look at how clean this version is:

from airflow.decorators import dag, task
from datetime import datetime, timedelta

default_args = {
    'owner': 'my_name',
    'start_date': datetime(2026, 6, 1),
    'retries': 1,
}

@dag(dag_id='market_data_taskflow_v1', default_args=default_args, schedule='@hourly', catchup=False)
def market_data_etl():

    @task()
    def extract():
        raw_data = {"ticker": "BTC", "price": 65000, "timestamp": "2026-06-07T00:00:00Z"}
        return raw_data  # No manual xcom_push! Airflow handles it.

    @task()
    def transform(raw_data: dict):
        # No manual xcom_pull! We just treat it like a regular Python variable.
        print(f"Transforming data for: {raw_data['ticker']}")
        return raw_data

    # This single clean line sets up dependencies AND passes data!
    market_data = extract()
    transform(market_data)

# Instantiate the DAG
market_data_etl_dag = market_data_etl()

The Verdict on TaskFlow:

This feels like writing native Python! You don’t have to instantiate PythonOperator manually, and dependencies are implicitly built simply by passing outputs into inputs (transform(extract())).

Seeing Both in the Airflow UI

Once I deployed both DAG files to my local environment running on WSL, they popped up immediately in my Airflow Web UI dashboard.

Even though the code looks completely different, Airflow creates the exact same visual graph architecture for both under the hood.

Traditional DAG

When I ran them, checking the logs for the TaskFlow DAG showed how cleanly it handled the data context without any missing parameters.

Which One Should You Use?

After building this massive API project using both paradigms, here is my takeaway for fellow beginners:

Use TaskFlow API whenever you are working strictly with Python functions (PythonOperator). It reduces boilerplate code significantly, makes your code highly readable, and saves you from the headache of managing raw XCom keys.
Use Standard Operators when you need to interact with external tools directly via specialized operators (like PostgresOperator, S3CreateObjectOperator, or BashOperator). TaskFlow is amazing for Python-native workflows, but standard classes are still essential for interacting with cloud infra and heavy enterprise databases.

What's Next?

Now that our pipeline can be scheduled and orchestrated automatically, another major question popped up: Where do we deploy this safely so it runs the exact same way on a remote server as it does on my local machine? Right now, everything is relying on my specific local python setup and WSL configuration. If I send this code to a teammate, they might hit a wall of environment errors.

In the next part of this series, we are diving into Docker to containerize our database and ETL processes so they can run seamlessly anywhere!

Which style do you prefer writing in Airflow? Let me know your thoughts in the comments!

Demystifying Apache Airflow: Why Data Engineers Don't Just Use Cron Jobs

Gathuru_M — Sun, 07 Jun 2026 18:02:19 +0000

If you followed my last post, we successfully built an ETL pipeline that fetched data from the News API, cleaned it with pandas, and loaded it into a PostgreSQL database. It felt amazing to watch it run successfully in the terminal.

But what if the News API goes down for 10 minutes? or what if my laptop is asleep when the script is supposed to run?

In the real world, you can't just sit at your laptop and manually click "Run" on a Python script every day. You need automation, monitoring, and a way to handle failures. That is exactly where Apache Airflow comes in.

The Problem: The "Crashing Script" Nightmare

Before tools like Airflow, developers relied heavily on Cron jobs (a built-in Linux tool used to schedule scripts at specific times). Cron is great for simple things, but it has huge blind spots for data engineering:

No Dependency Management: If your "Transform" script takes longer than usual, your "Load" script might start running before the data is even ready, causing a massive crash.
Lack of Visibility: If a script fails at 3 AM, you won't know until you check the logs manually or notice empty tables the next morning.
No Easy Retries: If a network glitch causes an API call to fail, Cron won't automatically try again 5 minutes later. You have to handle that messy logic yourself in Python.

Airflow solves all of this by acting as the workflow orchestrator. It doesn't actually store or process your data; instead, it acts as the manager telling your scripts exactly when to run, in what order, and what to do if something breaks.

The Core Concepts Explained

Let’s break down the four most important concepts using a simple analogy: Baking a Cake.

1. The DAG (Directed Acyclic Graph)

Think of a DAG as the entire recipe for your cake.

Directed: It has a clear starting point and moves in a specific direction (you can't frost the cake before you bake it).
Acyclic: It cannot go in circles. Step C cannot loop back and trigger Step A, otherwise your pipeline would run forever.
Graph: It's just a structural map of how your steps link together.

In data engineering, your DAG is the blueprint of your entire ETL pipeline.

2. Operators

If the DAG is the recipe, Operators are the kitchen appliances. They are the pre-built templates that define what actually gets done. Airflow provides different types of operators:

PythonOperator: Used to execute a piece of Python code (like our transform_data function).
PostgresOperator: Used to run SQL queries directly inside a Postgres database.
BashOperator: Used to run command-line terminal scripts.

3. Tasks

A Task is an operator that has been given specific instructions. It’s a single node inside your DAG. For example, using a PythonOperator to run extract_data() becomes the "Extract Task".

4. XComs (Cross-Communications)

In our standalone Python script, passing data was easy: we just returned a value from one function and passed it into the next (cleaned_df = transform_data(raw_data)).

In Airflow, tasks run completely independently. They can't easily talk to each other. XComs are like little sticky notes that tasks use to pass small amounts of data or metadata down the line. One task "pushes" a note, and the next task "pulls" it.

A Quick Peek at the Airflow UI

The absolute best part of Apache Airflow is its user interface. Instead of staring at text scrolling through a dark terminal window, Airflow gives you a beautiful visual dashboard where you can see your pipelines running in real-time.

When a pipeline runs successfully, the tasks turn a satisfying dark green. If a task fails, it turns red, making it incredibly easy to spot exactly where your pipeline broke.

Airflow Web UI Dags page

What We’re Doing Next

Now that we understand the foundational pillars of Airflow—DAGs, Operators, Tasks, and why we use them over simple cron jobs—it’s time to get our hands dirty.

In my next article, we are going to break down an ETL project into Airflow Tasks and watch it run automatically inside the Airflow UI.

Are you using Airflow or which other tools do you prefer for orchestration?

ETL Pipeline: Fetching Real-Time News Data with Python and Postgres

Gathuru_M — Sun, 07 Jun 2026 17:25:39 +0000

The best way to actually understand data engineering is to build something that breaks, fix it, and watch it successfully run.

In this article, we build an ETL pipeline that pulls data from the News API, cleans it up using pandas, and loads it into a local PostgreSQL database.

If you are a beginner Python developer or just getting into data engineering, this one is for you!

The Goal & The Architecture

Before writing a single line of code, let’s look at what we are actually trying to achieve:

Extract: Connect to the News API using Python, fetch the top headlines about technology, and pull down the raw data.
Transform: The raw data comes back as a messy, nested JSON object. We'll use pandas to flatten it, pick the columns we actually care about, handle missing values, and format the dates.
Load: Connect to a local PostgreSQL database and append our clean data into a structured table.

Step 1: Setting Up the Database

First, we need a place for our data to live. I used a PostgreSQL instance running on the cloud with Aiven.

We need a clean target table. Here is the SQL script I used to create a simple news_articles table. Notice how we have to be careful with our data types (like using TIMESTAMP for dates and TEXT for long URLs).

CREATE TABLE IF NOT EXISTS news_articles (
    id SERIAL PRIMARY KEY,
    source VARCHAR(100),
    author VARCHAR(150),
    title TEXT,
    description TEXT,
    url TEXT,
    published_at TIMESTAMP,
    extracted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Below is a diagram of the news_articles table created.

Step 2: The Python ETL Script

To keep things clean and modular, I broke the code down into three distinct functions representing E, T, and L.

Make sure you have your dependencies installed before running this:

pip install requests pandas psycopg2-binary sqlalchemy

Here is the full, documented script:

import requests
import pandas as pd
from datetime import datetime
import psycopg2

# Configuration
API_KEY = "YOUR_NEWS_API_KEY"
URL = f"https://newsapi.org/v2/everything?q=technology&apiKey={API_KEY}"

DB_PARAMS = {
    "host": "localhost",
    "database": "your_db_name",
    "user": "your_username",
    "password": "your_password",
    "port": "5432"
}

def extract_data():
    print("--- Starting Extraction ---")
    response = requests.get(URL)
    if response.status_code == 200:
        data = response.json()
        articles = data.get("articles", [])
        print(f"Successfully extracted {len(articles)} articles.")
        return articles
    else:
        raise Exception(f"API Request Failed with status code: {response.status_code}")

# Transforming Data
import pandas as pd

def transform_data(articles):

    # Create an empty list to store clean articles after looping
    cleaned_data = []

    # Parse through each dictionary to extract what we need
    for article in articles:
        clean_article = {
            'source' : article.get('source', {}).get('name', 'Unknown'),
            'author' : article.get('author'),
            'title' : article.get('title'),
            'description' : article.get('description'),
            'url' : article.get('url'),
            'publishedAt' : article.get('publishedAt')
        }

        cleaned_data.append(clean_article)

    df = pd.DataFrame(cleaned_data)

    # Rename column to match Postgres fields
    df = df.rename(columns={'publishedAt': 'published_at'})

    # Handle missing values  
    df['author'] = df['author'].fillna('Unknown')
    df['description'] = df['description'].fillna('No Description')

  # Format dates
    df['published_at'] = pd.to_datetime(df['published_at'])

    print("Transformation complete!")
    return df

def load_data(df):

    db_URI = os.getenv('URI')

    try:
        engine = create_engine(db_URI)

        df.to_sql(
            name = 'news_articles',
            con = engine,
            schema = 'news_api',
            if_exists = 'append',
            index = False
        )
        print("Data loaded successfully to 'news_articles'")
    except Exception as e:
        print(f"Failed to load data to the database:{e}")

# Run the pipeline
if __name__ == "__main__":
    try:
        raw_data = extract_data()
        cleaned_df = transform_data(raw_data)
        load_data(cleaned_df)
        print("ETL Pipeline Finished Successfully! 🎉")
    except Exception as e:
        print(f"Pipeline failed: {e}")

Step 3: Running the Pipeline and Verifying the Results

When I first ran this script, I ran into a classic beginner issue: the date format coming from the API included a Z at the end (e.g., 2026-06-07T06:00:00Z), which caused my local database to complain until I used pd.to_datetime() to safely parse it.

But once those quirks were ironed out, running the script in the terminal yielded beautiful logs:

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

To verify it actually worked, I hopped back into my database client and ran a simple query:

SELECT source_name, author, title, published_at FROM news_articles LIMIT 5;

And there it was—real live web data, organized neatly into my own database tables!

Key Takeaways from My First Project

Building this taught me a few massive lessons that you don't get just by reading textbooks:

API Data is messy: You can almost never load API responses directly into a database. Nested dictionaries (like the source field in this project) require explicit flattening.
Idempotency matters: If I run this script twice right now, it will duplicate all the articles. As I move forward, I need to look into how to handle duplicates (like checking for existing URLs before inserting).

What's Next?

Running this manually via a Python script is great for practice, but what if I want this data updated every single morning at 6 AM while I'm asleep? I can't just sit here and click "Run" manually.

That is where Data Orchestration comes in. In my next article, we are going to look at Apache Airflow and how we can take this exact code and turn it into an automated, scheduled workflow!

Have you built an ETL pipeline before? What was the trickiest data cleaning issue you faced? Let’s chat in the comments!

ETL vs. ELT: Which Approach Should You Use and Why?

Gathuru_M — Thu, 14 May 2026 20:20:27 +0000

1. Introduction

Understanding a company's data architecture can feel overwhelming, but once you cut out the noise, you will notice one of two operations taking place: ETL or ELT.

These two operations are the backbone of how data moves from a source (like an app or a database) to a destination (like a data warehouse). While they sound almost identical, the order of the letters changes everything about how a company manages its data.
In this article, we will break down both approaches for better understanding and I will give my take on which is better.

2. What is ETL? (Extract, Transform, Load)

ETL is the traditional way of handling data that follows the process:

Extract: Pull data from various sources (Excel, SQL databases, APIs).
Transform: Before the data reaches its final home, it is cleaned and formatted in a Staging Layer (a temporary storage area). Business logic is applied here to make the data "useful."
Load: The cleaned, "ready-to-use" data is finally saved in the destination.

Key Characteristic: The data is transformed before it is stored.
Common Tools: Microsoft SSIS, Talend, Informatica.

3. What is ELT? (Extract, Load, Transform)

ELT is the more modern approach, with the same process as you would guess but in a different order.

Extract: Pull the raw data from the sources.
Load: Instead of cleaning it first, you move the raw data directly into a high-capacity storage system, like a Data Lake or a Data Warehouse (BigQuery, Snowflake).
Transform: You perform the cleaning and modelling after the data is already in its destination.

Key Characteristic: Data grows into a historical archive. Since the raw data is always there, you can go back and re-transform it differently next year if your business needs change.
Common Tools: Fivetran or Airbyte (for loading), and dbt (for the transformation part).

Comparison Table

Feature	ETL	ELT
Order	Transform before Loading	Load before Transforming
Storage	Uses temporary Staging	Uses permanent Data Lake
Flexibility	Rigid / Fixed	Highly Flexible
Best For	On-premise / Small data	Cloud / Big Data

4. Which is Better?

With cloud storage becoming cheaper and databases becoming more powerful, ELT would be the preferred option. Here’s why:

Scalability: ELT can handle massive "Big Data" sets that would crash a traditional ETL staging server.
Flexibility: Because you store the raw data first, you never lose information. In ETL, if you don't "transform" a column, it's gone. In ELT, you can decide to use that column later.
Speed: You can load data as often as you want without waiting for complex cleaning scripts to finish.

While ETL is still used for highly sensitive data or older systems, ELT is the best approach for modern, cloud-based data engineering. It is more scalable, flexible, and allows for much deeper historical analysis.

How to connect Power BI to a PostgreSQL Database

Gathuru_M — Thu, 14 May 2026 17:20:31 +0000

Introduction

Power BI is a business analytics platform developed by Microsoft. It is mostly used by organizations to transform raw data from multiple sources into interactive visualizations that support data-driven decision-making.

Data sitting in various places in an organization—such as databases, Excel files, and cloud storage—can be collectively imported into Power BI to create a wholesome report. This allows data analysts and developers to extract insights that would otherwise be hidden in rows of text.

Most companies connect Power BI directly to databases for the following reasons:

Real-time Data Access: It gives visibility of the most current data available.
Automation: Reports can be developed and scheduled to refresh instantly, removing the need for manual updates.
Efficiency: It saves analysts time by eliminating the need to generate new reports every time the underlying data changes.

PostgreSQL is a primary example of a SQL database. These are commonly used in companies as they offer a structured and secure environment to store, manage, and retrieve large volumes of data effectively.

In this article, we will walk through connecting a Postgres Database to Power BI Desktop.

Part 1: Connecting to a Local PostgreSQL Database

To connect to a Postgres database sitting on your own machine:

Open Power BI Desktop.
On the Home ribbon, click Get Data, select PostgreSQL database from the list, and click Connect.
Connection Details: For the Server, type localhost. This tells Power BI to look at your own machine.
Database Name: Enter the specific name of your database (e.g., sales_db).
Credentials: When prompted, enter the username and password you created during PostgreSQL installation.

After successful authentication, a popup appears showing the existing tables in your database. Select the tables you need and click Load to bring them into the Power BI environment.

Part 2: Moving to the Cloud (Aiven PostgreSQL)

In most real work environments, data lives in the cloud so everyone can access it from anywhere. For this guide, we will be using Aiven. Connecting from a cloud database requires a few extra security steps.

How to connect:

Open Power BI Desktop. Click Get Data, select PostgreSQL database, and click Connect.
Obtain Details: From your Aiven console, copy your Host, Port, Database Name, Username, and Password.
Download the Certificate: In the list of connection details, download the ca.pem file by clicking the download icon.

Why SSL?
When connecting to a cloud database, we use SSL certificates. This acts as a secure tunnel for your data as it travels over the internet. SSL encrypts the connection so that malicious actors cannot "intercept" your credentials or your company's private data.

Enter database details: Fill in the details obtained from Aiven.

Under server name, enter the host name and the port in the format: `host:port`

SSL Configuration: You may receive an error because Power BI cannot automatically verify the cloud's CA certificate.

To resolve this, manually import the certificate to your Windows machine:

Press Windows + R, type certmgr.msc, and press Enter.
Select Trusted Root Certification Authorities, then click Certificates.
Right-click the folder, choose All Tasks > Import.
Browse to your ca.pem file (change file type to "All Files" to see it). Select it and click Next.
Finish the wizard. You will get a popup saying "Import Successful."

Restart Power BI to apply the changes. Your connection will now be successful, and your tables will be ready for loading.

Part 3: Building the Data Model

Once your tables (Customers, Products, Sales, and Inventory) are loaded, they appear in the "Data" pane. Now you must connect them.

Data Modeling is the process of telling Power BI how these tables relate to one another. For example, the Customer_ID in your Sales table should link to the ID in your Customers table.

The Benefit: By creating these relationships, you can filter a chart by "Customer Name" and see exactly what "Products" they bought across all "Sales."

Power BI often creates these automatically, which you can verify in the Model View pane.

Conclusion: Why SQL is a Superpower for Power BI Analysts

You might wonder, "If I have Power BI, do I still need SQL?" The answer is yes. SQL skills are important because they allow you to:

Filter at the source: Instead of bringing 1 million rows into Power BI and slowing it down, you can write a SQL query to only bring in the specific data you need.
Data Preparation: You can perform complex aggregations and clean up messy data before it even reaches your dashboard.

Mastering the connection between SQL and Power BI is what turns a basic report into a powerful, automated business tool.

SQL Joins Explained: Case Example

Gathuru_M — Mon, 02 Mar 2026 18:43:59 +0000

Structured Query Language(SQL) is a computer language for storing, manipulating, and retrieving data stored in a relational database.

SQL Joins are like clauses used to combine records from two or more tables in a database, just as the name(join) means.

In this article, you will learn, using a case example, the different types of joins, when, and how they are used. Be sure to check for “bonus joins” included at the end of the article.

Example Data

We will use data from these 2 tables to show various ways to display employees from John Smith's company.

Departments Table

Department_Id	Department_Name
1	Executive
2	HR
3	Sales
4	Support
5	Sales
6	Research

Employees Table

Employee_Id	Full_Name	Department_Id	Job_Role	Manager_Id
1	John Smith	1	CEO	Null
2	Sarah Goodes	1	CFO	1
3	Wayne Ablett	1	CIO	1
4	Michelle Carey	2	HR Manager	1
5	Chris Matthews	3	Sales Manager	2
6	Andrew Judd	4	Development Manager	3
7	Danielle McLeod	5	Support Manager	3
8	Matthew Swan	2	HR Representative	4
9	Stephanie Richardson	2	Salesperson	5
10	Tony Grant	3	Salesperson	5
11	Jenna Lockett	4	Front-End Developer	6
12	Michael Dunstall	4	Back-End Developer	6
13	Jane Voss	4	Back-End Developer	6
14	Anthony Hird	Null	Support	7
15	Natalie Rocca	5	Support	7

The code below shows the syntax for writing a JOIN:

SELECT columns
FROM table1
JOIN table2 ON table1.column1=table2.column1

Types of Joins

There are four major types of joins in SQL, as listed below:

Inner Join
Left Join
Right Join
Full Join

Inner Join

It creates a result table that displays information common between two tables based on a shared piece of information.

It is the most important and frequently used of the joins.

In this case, we could use it to display employees from the departments listed in the first table.

SELECT employees.Full_Name, employees.Job_Role, departments.Department_Name
FROM departments
INNER JOIN employees ON departments.Department_Id = employees.Department_Id
 -- You can replace the keyword INNER JOIN with JOIN

You should get the results below

Result

Full_Name	Job_Role	Department_Name
John Smith	CEO	Executive
Sarah Goodes	CFO	Executive
Wayne Ablett	CIO	Executive
Michelle Carey	HR Manager	HR
Chris Matthews	Sales Manager	Sales
Andrew Judd	Development Manager	Development
Danielle McLeod	Support Manager	Support
Matthew Swan	HR Representative	HR
Stephanie Richardson	Salesperson	HR
Tony Grant	Salesperson	Sales
Jenna Lockett	Front-End Developer	Development
Michael Dunstall	Back-End Developer	Development
Jane Voss	Back-End Developer	Development
Natalie Rocca	Support	Support

Left Join

A LEFT JOIN returns all rows from the left table, plus matched values from the right table or NULL in case of no match.

Note: The left table refers to the table that appears before the "LEFT JOIN" keywords in your SQL query. Same case applies when using RIGHT JOIN

We could use left join to retrieve a list of all employees along with their department names. If an employee doesn't belong to a department, display NULL

SELECT employees.Full_Name, departments.Department_Name
FROM employees 
LEFT JOIN departments ON departments.Department_Id = employees.Department_Id
ORDER BY employees.Full_Name ASC

You should get the results below

Result

Employee_Id	Full_Name	Department_Name
1	John Smith	Executive
2	Sarah Goodes	Executive
3	Wayne Ablett	Executive
4	Michelle Carey	HR
5	Chris Matthews	SALES
6	Andrew Judd	Development
7	Danielle McLeod	Support
8	Matthew Swan	HR
9	Stephanie Richardson	HR
10	Tony Grant	SALES
11	Jenna Lockett	Development
12	Michael Dunstall	Development
13	Jane Voss	Development
14	Anthony Hird	NULL
15	Natalie Rocca	Support

You can use the COALESCE() function to replace NULL values with more relatable user-defined values.

i.e. If an employee doesn't belong to a department, display "No Department" instead.

SELECT employees.Employee_Id, employees.Full_Name, 
COALESCE(departments.Department_Name, "No Department") AS Department_Name
FROM employees 
LEFT JOIN departments ON departments.Department_Id = employees.Department_Id
ORDER BY employees.Full_Name ASC

You should get the results below

Result

Employee_Id	Full_Name	Department_Name
1	John Smith	Executive
2	Sarah Goodes	Executive
3	Wayne Ablett	Executive
4	Michelle Carey	HR
5	Chris Matthews	SALES
6	Andrew Judd	Development
7	Danielle McLeod	Support
8	Matthew Swan	HR
9	Stephanie Richardson	HR
10	Tony Grant	SALES
11	Jenna Lockett	Development
12	Michael Dunstall	Development
13	Jane Voss	Development
14	Anthony Hird	No Department
15	Natalie Rocca	Support

Right Join

The SQL RIGHT JOIN returns all rows from the right table, even if there are no matches
in the left table. Not so different from the LEFT JOIN.

In this case, we could view employees and their various departments

SELECT Department_Name,
COALESCE (employees.Full_Name, "No Employee") AS Full_Name 
FROM employees 
RIGHT JOIN departments ON departments.Department_Id = employees.Department_Id;

You should get the results below

Result

Department_Name	Full_Name
Executive	Wayne Ablett
Executive	Sarah Goodes
Executive	John Smith
HR	Stephanie Richardson
HR	Matthew Swan
HR	Michelle Carey
Sales	Tony Grant
Sales	Chris Matthews
Development	Jane Voss
Development	Michael Dunstall
Development	Jenna Lockett
Development	Andrew Judd
Support	Natalie Rocca
Support	Danielle McLeod
Research	No Employee

Full Join

The SQL FULL JOIN combines the results of both left and right outer joins.

The joined table will contain all records from both tables and fill in NULLs for missing
matches on either side.

SELECT employees.Employee_Id, employees.Full_Name, employees.Job_Role, 
departments.Department_Id, departments.Department_Name
FROM employees 
FULL JOIN departments ON departments.Department_Id = employees.Department_Id
ORDER BY employees.Employee_Id ASC

You should get the results below

Result

EMPLOYEE_ID	FULL_NAME	JOB_ROLE	DEPARTMENT_ID	DEPARTMENT_NAME
1	John Smith	CEO	1	Executive
2	Sarah Goodes	CFO	1	Executive
3	Wayne Ablett	CIO	1	Executive
4	Michelle Carey	HR Manager	2	HR
5	Chris Matthews	Sales Manager	3	Sales
6	Andrew Judd	Development Manager	4	Development
7	Danielle McLeod	Support Manager	5	Support
8	Matthew Swan	HR Representative	2	HR
9	Stephanie Richardson	Salesperson	2	HR
10	Tony Grant	Salesperson	3	Sales
11	Jenna Lockett	Front-End Developer	4	Development
12	Michael Dunstall	Back-End Developer	4	Development
13	Jane Voss	Back-End Developer	4	Development
14	Anthony Hird	Support	NULL	NULL
15	Natalie Rocca	Support	5	Support
NULL	NULL	NULL	6	Research

Bonus Joins

Self Join

A SELF JOIN is typically not a join type but a special way of joining a table to itself. You may want to combine rows in a table based on a related column present in the table.

It is commonly used when you need to traverse a hierarchical structure where each row references another row in the same table

When comparing data within rows in the same table.

In this case, we will use self-join in the employees’ table to display the employee-manager relationship. Each employee record contains a reference to the manager's ID, allowing us to retrieve more information about the managers by adding another column, “Manager_Name”. In Data Science, this is an example of feature engineering.

SELECT
e.Employee_Id,
e.Full_Name,
e.Job_Role,
m.Employee_Id AS Manager_Id,
m.Full_Name AS Manager_Name
FROM employees e, employees m 
WHERE e.Manager_Id = m.Employee_Id;

You should get the results below

Result

EMPLOYEE_ID	FULL_NAME	JOB_ROLE	DEPARTMENT_ID	MANAGER_NAME
2	Sarah Goodes	CFO	1	John Smith
3	Wayne Ablett	CIO	1	John Smith
4	Michelle Carey	HR Manager	1	John Smith
5	Chris Matthews	Sales Manager	2	Sarah Goodes
6	Andrew Judd	Development Manager	3	Wayne Ablett
7	Danielle McLeod	Support Manager	3	Wayne Ablett
8	Matthew Swan	HR Representative	4	Michelle Carey
9	Stephanie Richardson	Salesperson	5	Chris Matthews
10	Tony Grant	Salesperson	5	Chris Matthews
11	Jenna Lockett	Front-End Developer	6	Andrew Judd
12	Michael Dunstall	Back-End Developer	6	Andrew Judd
13	Jane Voss	Back-End Developer	6	Andrew Judd
14	Anthony Hird	Support	7	Danielle McLeod
15	Natalie Rocca	Support	7	Danielle McLeod

That's most of what you need to know about SQL Joins. Hopefully, you found this information helpful. Feel free to share it with anyone who's having a hard time with Joins, and keep learning! 😊

If you have any questions, please leave them in the comments section below.

Thanks for reading!

Turning Messy Data into Business Action

Gathuru_M — Mon, 09 Feb 2026 02:07:28 +0000

How Analysts Translate Messy Data, DAX, and Dashboards into Action Using Power BI

As a data analyst, whether in the corporate world or working on a personal project, you will find that data analysis is often driven by the need to solve a problem or wanting an accurate, insightful view of data to make a better decision.

1. Define the Objective

First, we need to come up with the objective of our analysis.

Ask Questions: Write down the specific questions you want answers to from the analysis.
Theme Your Dashboard: If you need a dashboard, design one that maintains a clear theme. Don’t just show every kind of data to the user—focus on what matters.

2. Gathering the Data

The data we need is often scattered. You might need to:

Scrape it online.
Use data from survey results.
Combine several Excel sheets.
Fetch data from a database.

So, how do analysts translate this Messy Data into action?

Power BI allows you to access data from various sources, load it, transform it, and analyze it—all in the same workspace.

In Power BI, we follow the ELT approach (Extract, Load, Transform).

The ELT Process

Extract and Load

First, we start by Extracting the required data.
When you launch Power BI, you get a prompt to select a data source. Simply select where your data lies and import it into Power BI by Loading it there.

Transform

Once all your data is loaded, the next step is Cleaning. You want to remove all inconsistencies in your data by ensuring:

Correct Data Types: All fields (Dates, Decimals, Text) must be set correctly.
Handle Missing Values: Deal with nulls or blanks by either removing rows or filling them where appropriate.
Splitting/Merging: Split or merge columns where necessary (e.g., splitting an "Address" column to get separate "Location" and "Street" fields).
Standardization: Ensure consistency (e.g., making sure all currency data is in either Dollars or Shillings across the entire dataset).

Data Modelling: The Integration Step

After the data is clean and transformed, the best way to integrate it is through Data Modelling.

As discussed in my previous article Link , data modelling helps you create a clear structure and establish necessary relationships before you begin any plotting or calculations. This involves creating a proper Star Schema, consisting of a Fact table and Dimension tables.

Note: Always make sure to click "Close & Apply" to save all changes made in the Power Query Editor before you start building your reports.

DAX

Once your data model is set up, you might find that the raw data doesn't provide all the answers immediately. This is where DAX (Data Analysis Expressions) comes in.
DAX is the formula language of Power BI, allowing us to calculate and generate new information in two primary ways:

Measures: Use DAX to create summary aggregations. These calculate values on the fly, such as Total Sales, Year-over-Year Growth, Profit Margin, or Average Selling Price.
Calculated Columns: Generate new columns within your tables to provide more granular information. For example, you could create a "Profit Status" column that labels each row as "Profitable" or "Loss" based on a calculation.

DAX respects your model’s relationships. When you create a measure and plot it in a visual, it automatically reacts to the "Filter Context." This means the numbers will automatically filter and update based on the Dimensions (such as Date, Category, or Location) you use in your report.

Visualizing for Answers

A well-modeled dataset allows us to answer any question we might have. The best way to do this is to plot Visuals. Why?

Spot Patterns Fast: Visuals help you and your audience see trends or patterns immediately.
Tell a Story: After answering your initial questions, creating a dashboard tells a story about the problem you want to solve.

A great dashboard should:

Describe the problem clearly.
Illustrate insights found in the data.
Suggest a potential solution to the problem.

By following this flow, your analysis remains relevant and provides genuine value, rather than just showing the dashboard user what they already know.

How to Implement Data Modelling in Power BI

Gathuru_M — Mon, 02 Feb 2026 21:25:14 +0000

In this article we will explore the fundamental concepts of data modelling and specifically how to implement data modelling within Power BI for effective data analysis.

Data Modelling is simply structuring or setting up your data in tables, relationships, and access to data for analysis scenarios.

Often you'll find data distributed in several sheets or systems since, that is how it's maintained. If lucky your data could be in a neat setup (Data Warehouse) where most of the structuring has already been done.

Case Scenario:

Here is some sample data, to help make sense of this concept:
Sample Chocolate Sales Data

Understanding the Tables

In our case, we have 5 tables.

Fact Table: One table that contains pointers to other tables, but has no specific information. In our case, the shipments table.
Dimension Tables: Each explains one dimension of data. What is happening from the perspective of each dimension/entity. These will be the rest of our tables.
Calendar Table: A special Dimension table found in most data that holds the time component of analysis, i.e., [Timeseries, Forecasting or Trend analysis].

Doing this in Power BI

Load Data: Load the data in Power BI by selecting Excel Workbook as the data source.
Automatic Modelling: Power BI automatically tries to match the tables and create a model; you can see this in the Model View.
Manual Relationship Setup: 🤔 However, not all the data was modelled automatically. To finish up, drag the 'Shipdate' field from the Shipment table onto 'cal_date' in the Calendar table to create a new relationship and complete the look.

Note: Notice that the schema now looks Star-shaped. This kind of model with one fact table and multiple dimension tables is called a Star Schema. The process of setting up this structure is Data Modelling.
Manage Relationships: You can view and edit all connections by clicking on Manage Relationships. This menu shows the relationship status, specifically whether they are Active or Inactive.

Table Characteristics

Relationship Design: The Products table shows each product in a row with more information from the Shipments table indicating a Many to One Relationship.
Fact Table Design: The shipments table has more records as compared to the dimension tables in our data.

Therefore, Fact tables are engineered to host many records and may also have fewer columns as compared to dimension tables.

Conclusion

Effective data modelling is the backbone of any powerful analysis. By transforming messy, multi-sheet data into a structured Star Schema, everything will feel fast and intuitive; when it's wrong, you spend more time fixing "broken" numbers than actually analyzing data.

Introduction to Linux for Data Engineers, Including Practical Use of Vi and Nano with Examples

Gathuru_M — Mon, 26 Jan 2026 05:08:41 +0000

Why Every Data Engineer Needs to Make Friends with Linux

Diving into Data Engineering, you’re probably well into writing some Python scripts or SQL queries. But there's mentions of "The Server" or "Production," and suddenly everyone is talking about Linux.

If you’re wondering why you can’t just keep using Windows or macOS for everything, you’re not alone. Let’s break down why Linux.

If you look at regular people browsing the web, about 70-75% of them use Windows. It’s comfortable, right? But in the "under-the-hood" world, where we have servers that actually run the internet and process massive datasets, Linux owns about 90% to 100% of that space.

Knowing Linux isn't just a "nice to have"; it’s your secret weapon. If you can navigate a Linux terminal, you instantly become more hireable because you can actually manage the tools you build.

Here is why Linux is a big deal:

It’s where your work actually lives: You might write code on your laptop, but your data pipelines (the stuff that moves and cleans data) will almost certainly live on a Linux server. If it’s in "Production," it’s on Linux.
Security: In data engineering, you’re handling sensitive information — names, emails, credit card digits. Linux is built like a fortress. It handles permissions and privacy way better than the standard consumer OS.
The Command Line makes you a pro: Typing commands might feel like you're in an old hacker movie, but it’s actually way faster than clicking through menus. Mastering the command line makes you faster, more confident, and—honestly—just a better engineer.
It’s built for speed: Linux is "lightweight." It doesn’t waste energy on background apps you don't need. This means your data pipelines run faster and more efficiently.

Part 2: Getting Set Up (The WSL2 Guide)

On Linux/Mac: You’re already set! Just search for "Terminal" in your apps.

On Windows: The best way to do this is through WSL2 (Windows Subsystem for Linux).

How to install WSL2:

Enable the Feature: Go to your Windows Search Bar and Type 'Turn on Windows Features On or Off'.
Find "Windows Subsystem for Linux" in the list, check the box, and click OK.

Open PowerShell as Administrator and type:
wsl --install

Restart: You must restart your computer for the changes to take effect.

Finalize: After rebooting, a terminal will open. Follow the prompts to create a Username and Password. (Note: The password won't show characters as you type—this is normal!).

Part 3: The Essential Commands

We first need to know how to navigate this environment. Here are some basic commands you can try out.

pwd (Print Working Directory): Tells you exactly which folder you are currently in.
ls (List): Shows you what’s inside your current folder.
cd (Change Directory): Your "walking" command. Use cd .. to go back one folder.
mkdir folder_name (Make Directory): Creates a new folder.
touch filename: Creates a brand new, empty file.
rm filename (Remove): Deletes a file. Be careful: there is no "Undo."

Pro Productivity Tips

Tab Completion: Start typing a name and hit Tab. Linux will finish the word for you.
The Up Arrow: Hit the up arrow to see commands you typed previously.
clear: Wipes the screen clean.
Ctrl + C: The "Emergency Stop" button if a command is stuck.

Part 4: Real-World Practice

We’re going to create a workspace and download a real dataset (the famous Iris flower dataset).

# Create a folder and redirect to it
mkdir linux_practice
cd linux_practice

Create your own file

Before we download anything, let's create a small file to store settings or write small notes.
touch my_notes.txt

If you run ls, you’ll see your new empty file sitting there.

Download the data using 'wget'

wget https://raw.githubusercontent.com/dataprofessor/data/master/iris.csv

If you run ls again, you’ll see your new csv file added to the list.

Peek at the top 10 rows

head -n 10 iris.csv

Part 5: Mastering the Editors (Nano vs. Vim)

On a remote server, you don't have VS Code. You have to edit files inside the terminal.

1. Nano:

Nano is like a very basic version of Notepad that lives in your terminal.

How to use it:

Type nano iris.csv.
Editing: Use your arrow keys to move the cursor and just start typing.
The Bottom Menu: See those ^ symbols? That means the Ctrl key.
To Save: Press Ctrl + O (Write Out), then hit Enter.
To Exit: Press Ctrl + X.

2. Vim:

How to use it:

Type vim iris.csv.
Normal Mode: You start here. You cannot type text yet. This mode is for moving around and running commands.
Insert Mode: To start typing, press the letter i. You’ll see -- INSERT -- at the bottom. Now you can edit the file.
Saving your work: * First, hit the Esc key to leave "Insert Mode" and go back to "Normal Mode."
Type :wq and hit Enter. (w means save/write, q means quit.
The Emergency Exit: If you messed up and want to leave without saving, hit Esc, then type :q! and hit Enter.

Conclusion

You navigated a server, pulled data, and edited files using the terminal.👏

You can also use a "cheat sheet" to guide you for the first few weeks. Happy coding!

Want to Learn Git and GitHub? A Step-by-Step Guide to Version Control

Gathuru_M — Sun, 18 Jan 2026 13:09:29 +0000

If you’re like me, you probably heard people talking about "pushing to main" or "merging branches" and thought, “What on earth are they talking about?”
It feels like everyone knows this secret language. But honestly? Once you get the hang of it, Git and GitHub are just a fancy way of making sure you don't accidentally delete your entire project.😊

Let’s break it down.

This guide will walk you through the fundamentals of Git and GitHub. You'll learn how to install them, connect them, and use them to manage your code effectively.

What is Git?

Git is a version control system. Think of it as a smart history book for your code. Every time you make changes to your project, Git can record them. This means you can:

Track modifications: See exactly what changes were made, when, and by whom.
Revert: Easily go back to an earlier state of your code if something goes wrong.

What is GitHub?

GitHub is a web-based hosting service for Git repositories, like the cloud (think Google Drive).
While Git is the tool you use on your computer to manage versions, GitHub is like a cloud storage and collaboration platform for your Git projects. It allows you to:

Store: Keep a backup if your laptop decides to take a permanent nap.
Share: Make your projects accessible to others.
Collaborate: Provide a central hub for others to work on a project with you without overwriting each other's work.
Showcase: Create a portfolio of your projects.

Step 1: Installing Git Bash

Git Bash is a command-line interface (CLI) for Windows where you type commands to talk to Git.

For Windows:

Download Git Bash: Go to the official Git website: https://git-scm.com/download/win
Run the installer: Follow the on-screen instructions. You can generally stick with the default options, but here is something to keep in mind:
- "Choosing the default editor used by Git": You can keep the default (Vim) or choose another editor you're familiar with (like VS Code, Notepad++, etc.).
- "Adjusting the name of the initial branch": Choose "Let Git decide" (it usually defaults to main now).
Verify the installation: Open the Git Bash application, type git --version and press Enter. You should see the Git version number.

Step 2: Configuring Git

Before you start using Git, you need to tell it who you are.
This information will be attached to your commits to track who is making changes.

While still on Git Bash and run these two commands, replacing the placeholders with your name and email:

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

You can verify your settings with:

git config --global --list

Step 3: Connecting the Two (The Handshake)

You will first need a GitHub account.
If you don't have one already, head over to https://github.com/join and sign up.
Follow the prompts to create your username, password, and verify your email.

Now, to make your computer talk to GitHub without asking for your password every five seconds, we use something called an SSH Key.

Generate a new SSH key: In Git Bash, type:

ssh-keygen -t ed25519 -C "your@email.com"

Just keep hitting Enter until it stops.

Start the SSH agent:

eval "$(ssh-agent -s)"

Add your SSH key to the SSH agent:

ssh-add ~/.ssh/id_ed25519

Copy your public SSH key:

cat ~/.ssh/id_ed25519.pub

A bunch of random letters and numbers will pop up. Highlight and copy them.

Pass this to GitHub: Go to your GitHub settings, look for SSH and GPG keys, click New SSH Key, and paste that wall of text in there.

Step 4: Your First Project (The Fun Part)

Let’s say you have a folder on your computer called my-cool-app.

Tell Git to watch this folder: Inside Git Bash, go to that folder and type

git init

Make a file: Create a simple text file called readme.txt and write "Hello world" inside it.
The "Add and Commit":

git add .
git commit -m "My first save point"

(This tells Git, "Hey, look at all the changes I just made!").

Step 5: Sending it to the Cloud (Pushing)

Now, go to GitHub, create a "New Repository," and name it whatever you want. GitHub will give you a link that looks like git@github.com:yourname/your-repo.git.

Link them up: Type,

git remote add origin [PASTE YOUR LINK HERE]

Send it!:

git push -u origin main

Refresh your GitHub page, and boom! Your code is on the internet.

Step 6: Pulling it Back

If you ever go to a different computer, or if a friend changes your code on GitHub, you need to bring those changes back to your machine.

Just type:

git pull origin main

It’s like syncing your phone—it grabs the newest version from the cloud and puts it on your computer.

Wrapping Up

That’s pretty much the "Big Three":

Add (Pick what you want to save)
Commit (Save it)
Push (Send it to GitHub)

Don't worry if you forget the commands at first. I still have to Google them half the time.
Just keep playing around with them, and eventually, it’ll feel like second nature.
Happy coding!🥳

SQL Joins Explained: Case Example

Gathuru_M — Wed, 13 Sep 2023 01:55:44 +0000

Structured Query Language(SQL) is a computer language for storing, manipulating, and retrieving data stored in a relational database.

SQL Joins are like clauses used to combine records from two or more tables in a database, just as the name(join) means.

In this article, you will learn, using a case example, the different types of joins, when, and how they are used. Be sure to check for “bonus joins” included at the end of the article.

Example Data

We will use data from these 2 tables to show various ways to display employees from John Smith's company.

Departments Table

Department_Id	Department_Name
1	Executive
2	HR
3	Sales
4	Support
5	Sales
6	Research

Employees Table

Employee_Id	Full_Name	Department_Id	Job_Role	Manager_Id
1	John Smith	1	CEO	Null
2	Sarah Goodes	1	CFO	1
3	Wayne Ablett	1	CIO	1
4	Michelle Carey	2	HR Manager	1
5	Chris Matthews	3	Sales Manager	2
6	Andrew Judd	4	Development Manager	3
7	Danielle McLeod	5	Support Manager	3
8	Matthew Swan	2	HR Representative	4
9	Stephanie Richardson	2	Salesperson	5
10	Tony Grant	3	Salesperson	5
11	Jenna Lockett	4	Front-End Developer	6
12	Michael Dunstall	4	Back-End Developer	6
13	Jane Voss	4	Back-End Developer	6
14	Anthony Hird	Null	Support	7
15	Natalie Rocca	5	Support	7

The code below shows the syntax for writing a JOIN:

SELECT columns
FROM table1
JOIN table2 ON table1.column1=table2.column1

Types of Joins

There are four major types of joins in SQL, as listed below:

Inner Join
Left Join
Right Join
Full Join

Inner Join

It creates a result table that displays information common between two tables based on a shared piece of information.

It is the most important and frequently used of the joins.

In this case, we could use it to display employees from the departments listed in the first table.

SELECT employees.Full_Name, employees.Job_Role, departments.Department_Name
FROM departments
INNER JOIN employees ON departments.Department_Id = employees.Department_Id
 -- You can replace the keyword INNER JOIN with JOIN

You should get the results below

Result

Full_Name	Job_Role	Department_Name
John Smith	CEO	Executive
Sarah Goodes	CFO	Executive
Wayne Ablett	CIO	Executive
Michelle Carey	HR Manager	HR
Chris Matthews	Sales Manager	Sales
Andrew Judd	Development Manager	Development
Danielle McLeod	Support Manager	Support
Matthew Swan	HR Representative	HR
Stephanie Richardson	Salesperson	HR
Tony Grant	Salesperson	Sales
Jenna Lockett	Front-End Developer	Development
Michael Dunstall	Back-End Developer	Development
Jane Voss	Back-End Developer	Development
Natalie Rocca	Support	Support

Left Join

A LEFT JOIN returns all rows from the left table, plus matched values from the right table or NULL in case of no match.

Note: The left table refers to the table that appears before the "LEFT JOIN" keywords in your SQL query. Same case applies when using RIGHT JOIN

We could use left join to retrieve a list of all employees along with their department names. If an employee doesn't belong to a department, display NULL

SELECT employees.Full_Name, departments.Department_Name
FROM employees 
LEFT JOIN departments ON departments.Department_Id = employees.Department_Id
ORDER BY employees.Full_Name ASC

You should get the results below

Result

Employee_Id	Full_Name	Department_Name
1	John Smith	Executive
2	Sarah Goodes	Executive
3	Wayne Ablett	Executive
4	Michelle Carey	HR
5	Chris Matthews	SALES
6	Andrew Judd	Development
7	Danielle McLeod	Support
8	Matthew Swan	HR
9	Stephanie Richardson	HR
10	Tony Grant	SALES
11	Jenna Lockett	Development
12	Michael Dunstall	Development
13	Jane Voss	Development
14	Anthony Hird	NULL
15	Natalie Rocca	Support

You can use the COALESCE() function to replace NULL values with more relatable user-defined values.

i.e. If an employee doesn't belong to a department, display "No Department" instead.

SELECT employees.Employee_Id, employees.Full_Name, 
COALESCE(departments.Department_Name, "No Department") AS Department_Name
FROM employees 
LEFT JOIN departments ON departments.Department_Id = employees.Department_Id
ORDER BY employees.Full_Name ASC

You should get the results below

Result

Employee_Id	Full_Name	Department_Name
1	John Smith	Executive
2	Sarah Goodes	Executive
3	Wayne Ablett	Executive
4	Michelle Carey	HR
5	Chris Matthews	SALES
6	Andrew Judd	Development
7	Danielle McLeod	Support
8	Matthew Swan	HR
9	Stephanie Richardson	HR
10	Tony Grant	SALES
11	Jenna Lockett	Development
12	Michael Dunstall	Development
13	Jane Voss	Development
14	Anthony Hird	No Department
15	Natalie Rocca	Support

Right Join

The SQL RIGHT JOIN returns all rows from the right table, even if there are no matches
in the left table. Not so different from the LEFT JOIN.

In this case, we could view employees and their various departments

SELECT Department_Name,
COALESCE (employees.Full_Name, "No Employee") AS Full_Name 
FROM employees 
RIGHT JOIN departments ON departments.Department_Id = employees.Department_Id;

You should get the results below

Result

Department_Name	Full_Name
Executive	Wayne Ablett
Executive	Sarah Goodes
Executive	John Smith
HR	Stephanie Richardson
HR	Matthew Swan
HR	Michelle Carey
Sales	Tony Grant
Sales	Chris Matthews
Development	Jane Voss
Development	Michael Dunstall
Development	Jenna Lockett
Development	Andrew Judd
Support	Natalie Rocca
Support	Danielle McLeod
Research	No Employee

Full Join

The SQL FULL JOIN combines the results of both left and right outer joins.

The joined table will contain all records from both tables and fill in NULLs for missing
matches on either side.

SELECT employees.Employee_Id, employees.Full_Name, employees.Job_Role, 
departments.Department_Id, departments.Department_Name
FROM employees 
FULL JOIN departments ON departments.Department_Id = employees.Department_Id
ORDER BY employees.Employee_Id ASC

You should get the results below

Result

EMPLOYEE_ID	FULL_NAME	JOB_ROLE	DEPARTMENT_ID	DEPARTMENT_NAME
1	John Smith	CEO	1	Executive
2	Sarah Goodes	CFO	1	Executive
3	Wayne Ablett	CIO	1	Executive
4	Michelle Carey	HR Manager	2	HR
5	Chris Matthews	Sales Manager	3	Sales
6	Andrew Judd	Development Manager	4	Development
7	Danielle McLeod	Support Manager	5	Support
8	Matthew Swan	HR Representative	2	HR
9	Stephanie Richardson	Salesperson	2	HR
10	Tony Grant	Salesperson	3	Sales
11	Jenna Lockett	Front-End Developer	4	Development
12	Michael Dunstall	Back-End Developer	4	Development
13	Jane Voss	Back-End Developer	4	Development
14	Anthony Hird	Support	NULL	NULL
15	Natalie Rocca	Support	5	Support
NULL	NULL	NULL	6	Research

Bonus Joins

Self Join

A SELF JOIN is typically not a join type but a special way of joining a table to itself. You may want to combine rows in a table based on a related column present in the table.

It is commonly used when you need to traverse a hierarchical structure where each row references another row in the same table

When comparing data within rows in the same table.

SELECT
e.Employee_Id,
e.Full_Name,
e.Job_Role,
m.Employee_Id AS Manager_Id,
m.Full_Name AS Manager_Name
FROM employees e, employees m 
WHERE e.Manager_Id = m.Employee_Id;

You should get the results below

Result

EMPLOYEE_ID	FULL_NAME	JOB_ROLE	DEPARTMENT_ID	MANAGER_NAME
2	Sarah Goodes	CFO	1	John Smith
3	Wayne Ablett	CIO	1	John Smith
4	Michelle Carey	HR Manager	1	John Smith
5	Chris Matthews	Sales Manager	2	Sarah Goodes
6	Andrew Judd	Development Manager	3	Wayne Ablett
7	Danielle McLeod	Support Manager	3	Wayne Ablett
8	Matthew Swan	HR Representative	4	Michelle Carey
9	Stephanie Richardson	Salesperson	5	Chris Matthews
10	Tony Grant	Salesperson	5	Chris Matthews
11	Jenna Lockett	Front-End Developer	6	Andrew Judd
12	Michael Dunstall	Back-End Developer	6	Andrew Judd
13	Jane Voss	Back-End Developer	6	Andrew Judd
14	Anthony Hird	Support	7	Danielle McLeod
15	Natalie Rocca	Support	7	Danielle McLeod

That's most of what you need to know about SQL Joins. Hopefully, you found this information helpful. Feel free to share it with anyone who's having a hard time with Joins, and keep learning! 😊

If you have any questions, please leave them in the comments section below.

Thanks for reading!

Data Engineering 102: Introduction to Python for Data Engineering

Gathuru_M — Sun, 11 Sep 2022 20:08:07 +0000

Python is today’s most popular programming language with endless applications in various fields. It is ideally suited for deployment, analysis, and maintenance thanks to its flexible and dynamic nature.
It is one of the crucial skills required in the field of Data Engineering, to create Data Pipelines, set up Statistical Models, and perform a thorough analysis of them.
To start using python, you'll need it installed in the Operating system you're currently using be it Linux, Mac OS, or the most common Windows.
Python provides an ample amount of libraries and packages for various applications. These are the top 5 Python for Data Engineering packages. They include:

-Pandas
-pygrametl
-petl
-Beautiful Soup
-SciPy

But firstly, in Learning python, these are some of the topics you should perhaps start with:-

1.MATH EXPRESSIONS
Syntax Math Meaning
a+b a+b addition
a-b a-b) subtraction
a*b a\times b\ multiplication
a/b a\div b\, division (see note below)
a//b a\div b\ division - in python 2.2 & abv
a%b a mod b modulo
-a -a negation
abs(a) |a| absolute value
a**b a^{b} exponent
math.sqrt square root

2. Strings
Strings in python are surrounded by either single quotation marks or double quotation marks.
e.g.
'hello' is the same as "hello".

You can display a string literal with the print() function

3. Variables
Variables are containers for storing data values.
A variable is created the moment you first assign a value to it.
x = 5
y = "John"
print(x)
print(y)

4.Loops
Python provides three ways for executing the loops

(a)While Loop:
In python, a while loop is used to execute a block of statements repeatedly until a given condition is satisfied. And when the condition becomes false, the line immediately after the loop in the program is executed.

while expression:
statement(s)

(b) For in Loop:
For loops are used for sequential traversal. For example: traversing a list or string or array etc. In Python, there is no C style for loop, i.e., for (i=0; i<n; i++). There is “for in” loop which is similar to for each loop in other languages. Let us learn how to use for in loop for sequential traversals

for iterator_var in sequence:
statements(s)

(c)Nested Loops:
Python programming language allows using one loop inside another loop. The following section shows a few examples to illustrate the concept.

Syntax:

while expression:
while expression:
statement(s)
statement(s)

5. Functions
A function is a block of code that only runs when it is called.

The basic syntax is:
def my_function():
print("Hello from a function")

6. List, Tuples, Dictionary, and sets

It's also important to learn how to connect databases with:-
1.BOTO3
2.Psycopg2
3.MySQL

So, as long as there is data to process, data engineers will be in demand. I wish you all the best as you choose to pursue this journey.

Thanks for reading!
Any questions? Leave your comment below to start fantastic discussions!