DEV Community: Njuguna Gichia

Introduction to SQL Window Functions, CTEs, Joins & Subqueries

Njuguna Gichia — Sun, 18 Oct 2026 13:26:07 +0000

Introduction to SQL

SQL (pronounced "Sequel") stands for Structured Query Language and it is the standard programming language for managing, querying, and manipulating relational databases. SQL is declarative, meaning you tell the database what data you want, and then the database figures out the best way to get it.

What is SQL Used For in Data?

In data analytics, engineering, and science, SQL is used to:

Query and Retrieve Data: Pull specific records out of millions of rows using filters, sorting, and conditional logic.
Aggregate and Summarize: Calculate metrics like totals, averages, minimums, and maximums using functions like SUM(), AVG(), and COUNT().
Join Disparate Datasets: Connect related tables (such as matching doctors to their patient visits or departments to revenue targets) using JOIN operations.
Transform and Clean: Reshape raw data into clean, structured formats ready for reporting, machine learning, or dashboards.

The Basic Anatomy of a SQL Query

At its core, a standard SQL query follows a predictable logical structure.

SQL

SELECT column_1, SUM(column_2) AS total_metric
FROM table_name
WHERE condition = 'value'
GROUP BY column_1
ORDER BY total_metric DESC;

SELECT: Specifies the exact columns or calculated values you want to see in your final output.
FROM: Identifies the table or tables where the data lives.
WHERE: Filters the rows before any groupings happen based on specific criteria.
GROUP BY: Bundles rows together that share a common attribute so you can perform aggregate calculations on them.
ORDER BY: Sorts your final results (e.g., highest revenue to lowest).

Window Functions

In SQL Window functions perform calculations across a set of table rows that are somehow related to the current row.

Window functions also keep all individual rows intact while adding the calculated result as a new column in each row.

A very important component of window functions is the OVER() function that tells PostgreSQL how to group and sort the rows for a specific operation.

We use two major tools Partition by: and Order by: inside the Over() function.

Partition by:
Divides rows into groups similar to GROUP BY without collapsing them.
Order by:
Sorts the rows within each partition.

Types of Window Functions

1. Ranking Functions

ROW_NUMBER() Gives every row a unique sequential number even if the values are identical.

SCENARIO

The hospital's patient retention team wants to audit patient records and identify every patient's absolute most recent visit to the clinic.

QUERY

select
    v.patient_id,
    p.patient_name,
    v.visit_id,
    v.visit_date,
    row_number() over(
        partition by v.patient_id
        order by v.visit_date desc
    ) as visit_recency_rank
from visits v
join dim_patients p on v.patient_id = p.patient_id;

TASK
This query ranks patients visits chronologically, assigns 1 to the most recent visit, 2 to the second most recent (and so on) while pulling descriptive patient information from the patients dimension table, and joins the fact table (visits) with the dimension table patients.

RANK()
Gives identical values the same rank but leaves gaps in the numbering by skipping one value
DENSE_RANK()
Assigns sequential rank to rows within a partition without any gaps in the ranking number.
The next rank is the immediate integer unlike rank() where it skips a number after a tie.

SCENARIO

The medical director is evaluating doctors based on their average patient satisfaction score. Where there's a tie, we want to see how standard ranking handles ties versus dense ranking.

QUERY

select
    d.doctor_name,
    ROUND(AVG(v.patient_satisfaction_score), 2) AS avg_satisfaction,
    RANK() OVER (ORDER BY AVG(v.patient_satisfaction_score) DESC) AS satisfaction_rank,
    DENSE_RANK() OVER (ORDER BY AVG(v.patient_satisfaction_score) DESC) AS satisfaction_dense_rank
FROM Visits v
JOIN Dim_Doctors d ON v.doctor_id = d.doctor_id
GROUP BY d.doctor_name;

TASK
This query calculates the average patient satisfaction score for each doctor, and then ranks the doctors from highest satisfaction to lowest. The query rolls up all individual patient visits per doctor, computes an aggregate average and then uses window functions RANK and DENSE_RANK to establish a leaderboard of physician performance.

DENSE_RANK()
Assigns sequential rank to rows within a partition without any gaps in the ranking number.
The next rank is the immediate integer unlike rank() where it skips a number after a tie.
NTILE(n)
Distributes the rows in a partition into a specified number of roughly equal groups (buckets) and the designated number of buckets is passed as an argument e.g., n=4 for quartiles is NTILE(4)

SCENARIO
The finance department wants to segment all 30 hospital visits into 4 equal financial tiers (quartiles) based on total_bill_kes to identify top-tier high-billing visits versus routine lower-cost visits.

QUERY

SELECT 
    visit_id,
    patient_id,
    total_bill_kes,
    NTILE(4) OVER (ORDER BY total_bill_kes DESC) AS billing_quartile
FROM Visits;

TASK
This query segments all hospital visits into 4 equal financial tiers from highest billing (1) to lowest billing (4)

2. Aggregate Window Functions

When Over() is attached to an aggregate function it turns into a window function.

SCENARIO
Management wants a master ledger of all visits that shows individual transaction amounts alongside cumulative running totals and departmental averages without collapsing rows.

QUERY

SELECT 
    visit_id,
    visit_date,
    department_id,
    total_bill_kes,
    -- Running total of all hospital revenue chronologically
    SUM(total_bill_kes) OVER (
        ORDER BY visit_date, visit_id
    ) AS cumulative_revenue,
    -- Average bill within the same department
    ROUND(AVG(total_bill_kes) OVER (
        PARTITION BY department_id
    ), 2) AS dept_average_bill,
    -- Cumulative count of visits over time
    COUNT(visit_id) OVER (
        ORDER BY visit_date, visit_id
    ) AS running_visit_count,
    -- Min and Max bills recorded within that department so far
    MIN(total_bill_kes) OVER (
        PARTITION BY department_id 
        ORDER BY visit_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_min_dept_bill,
    MAX(total_bill_kes) OVER (
        PARTITION BY department_id 
        ORDER BY visit_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_max_dept_bill
FROM Visits;

TASK
Combines multiple aggregate functions with OVER() to show running totals, department visit counts, and departmental price extremes alongside every individual row.

3. Value Window Functions

Value window functions are analytical tools that let you copy a specific value from a different row and display it alongside a current row.

Value window functions look into a data set and pull out actual existing data values and allow us to perform comparative, time-based analysis without having to rely on complex self-joins or subqueries.

Types of Value Window Functions

Lag()
Allows us to access data from a previous row
Lead()
Allows us to access data from the following row.

SCENARIO
The hospital wants to look at a patient's sequential visit history to see how much time elapsed between their current visit and their previous visit, as well as how their total bill changed compared to the next visit.

QUERY

SELECT 
    patient_id,
    visit_id,
    visit_date,
    total_bill_kes,
    LAG(visit_date) OVER (
        PARTITION BY patient_id 
        ORDER BY visit_date ASC
    ) AS previous_visit_date,
    LEAD(total_bill_kes) OVER (
        PARTITION BY patient_id 
        ORDER BY visit_date ASC
    ) AS next_visit_bill
FROM Visits;

TASK

Use LAG() to look backward at the previous visit date for the same patient, and LEAD() to look forward at the next visit's total bill.

First_Value()
Pulls the value from the very first row of the current window frame
Last_Value()
Grabs the Value from the last row of the window frame

SCENARIO

Hospital administrators want to inspect every individual visit in each department alongside two benchmarks: the highest total bill generated in that department and the lowest total bill generated in that department.

QUERY

SELECT 
    v.visit_id,
    dept.department_name,
    v.total_bill_kes,
    FIRST_VALUE(v.total_bill_kes) OVER (
        PARTITION BY v.department_id 
        ORDER BY v.total_bill_kes DESC
    ) AS highest_bill_in_dept,
    LAST_VALUE(v.total_bill_kes) OVER (
        PARTITION BY v.department_id 
        ORDER BY v.total_bill_kes DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS lowest_bill_in_dept
FROM Visits v
JOIN Dim_Departments dept ON v.department_id = dept.department_id;

TASK

Pull the highest bill (FIRST_VALUE) and lowest bill (LAST_VALUE) for each department onto every individual visit row.

SQL Joins

In SQL, the join clause is used to combine rows from two or more tables based on a related column between them.

Types of Joins

Inner Join

This join returns only the rows that have matching values in both tables. If a record exists in one table but it doesn't have a match in another table, it is excluded entirely.

SCENARIO

List all completed patient visits, the name of the doctor who handled them and the department they visited.

QUERY

SELECT v.visit_id, p.patient_name, d.doctor_name, dept.department_name, v.total_bill_kes
FROM Visits v
INNER JOIN Patients p ON v.patient_id = p.patient_id
INNER JOIN Doctors d ON v.doctor_id = d.doctor_id
INNER JOIN Departments dept ON v.department_id = dept.department_id;

Left (Outer) Join

This join returns all rows from the left table and only the matched rows from the right table. If there is no match in the right table, the result is NULL for the right table's columns.

Right (Outer) Join

This join returns all rows from the right table and only the matched rows from the left table. If there is no match in the left table, the result is NULL for the left table's columns.

SCENARIO
List all doctors alongside any visits they managed.

QUERY

SELECT d.doctor_name, d.doctor_specialty, v.visit_id, v.total_bill_kes
FROM Visits v
RIGHT JOIN Doctors d ON v.doctor_id = d.doctor_id;

Full (Outer) Join

Returns all records when there is a match in either the left or the right table. Rows with no matching values on either side will show NULL values on the unmatched side.ur

Self Join

This is a regular join where a table is joined with itself and is used when a table contains hierarchical or comparative relationships within it's own rows.

SCENARIO
Compare doctors within the same specialty e.g., to pair up doctors who share the exact same medical specialty.

QUERY

SELECT 
    d1.doctor_name AS doctor_a,
    d2.doctor_name AS doctor_b,
    d1.doctor_specialty
FROM Doctors d1
INNER JOIN Dim_Doctors d2 
    ON d1.doctor_specialty = d2.doctor_specialty 
    AND d1.doctor_id < d2.doctor_id; -- Prevents duplicate reverse pairs and self-matching

Cross Join

This returns the cartesian product of two or more tables by combining every row from the first table with every row from the second table.

Common Table Expressions (CTEs)

Common Table expressions are temporary, named result sets created within the execution scope of a single SQL Statement using a WITH clause.

They act like temporary views that exist only for the duration of a specific query.

CTE Types

1. Basic CTEs

This is a single temporary result set right before the main SELECT, INSERT or UPDATE statement that makes queries much easier to read by breaking them into logical, readable blocks.

SCENARIO

Hospital management wants to review all patient visits where the total bill is greater than the average hospital bill.

QUERY

WITH AvgHospitalBill AS (
    SELECT AVG(total_bill_kes) AS avg_bill
    FROM Fact_Visits
)
SELECT v.visit_id, v.patient_id, v.total_bill_kes, a.avg_bill
FROM Visits v
CROSS JOIN AvgHospitalBill a
WHERE v.total_bill_kes > a.avg_bill;

TASK

Flag high cost visits relative to the hospital wide average by writing a CTE named AvgHospitalBill to calculate the average billing amount, then query visits exceeding that average.

2. Multiple CTEs

These allow is to define more than one temporary result set within a single SQL query. They are particularly useful when a problem requires multiple stages of data processing where each CTE represents a distinct stage.

There are two ways to use multiple CTEs.

Independent CTEs (Parallel Logic)

This approach involves defining multiple unrelated datasets to combine later into a final query e.g., by performing a JOIN or a UNION

SCENARIO

Hospital management wants to compare every individual visit's billing amount against the overall average hospital bill, while simultaneously comparing it against the overall average patient satisfaction score.

QUERY

WITH GlobalAverageBill AS (
    SELECT AVG(total_bill_kes) AS avg_hospital_bill
    FROM Visits
),
GlobalAverageSatisfaction AS (
    SELECT AVG(patient_satisfaction_score) AS avg_hospital_satisfaction
    FROM Visits
)
SELECT 
    v.visit_id,
    v.patient_id,
    v.total_bill_kes,
    ROUND(gab.avg_hospital_bill, 2) AS benchmark_avg_bill,
    v.patient_satisfaction_score,
    ROUND(gas.avg_hospital_satisfaction::numeric, 2) AS benchmark_avg_satisfaction,
    -- Compare visit bill to the independent average bill CTE
    CASE 
        WHEN v.total_bill_kes > gab.avg_hospital_bill THEN 'Above Average Bill'
        ELSE 'Below Average Bill'
    END AS billing_status
FROM Visits v
CROSS JOIN GlobalAverageBill gab
CROSS JOIN GlobalAverageSatisfaction gas;

TASK

Two separate non-dependent CTEs GlobalAverageBill which calculates the global average bill and GlobalAverageSatisfaction which calculates the average satisfaction score cross join the results against the main Visits table to benchmark every visit.

**Dependent / Chained CTEs (Sequential Logic)*

This approach involves a CTE referencing a previously defined CTE in the same statement where each step refines the last.

An important caveat to note with this approach is that forward referencing. A CTE cannot reference another CTE defined after it. i.e. cte_1 can not read date from cte_2

SCENARIO

Isolate high-performing doctors and match them to their respective patient visits.

QUERY

WITH DoctorRevenue AS (
    SELECT doctor_id, SUM(total_bill_kes) AS total_revenue
    FROM Visits
    GROUP BY doctor_id
),
AverageRevenueBenchmark AS (
    SELECT AVG(total_revenue) AS avg_doctor_revenue
    FROM DoctorRevenue
)
SELECT dr.doctor_id, dr.total_revenue, arb.avg_doctor_revenue
FROM DoctorRevenue dr
CROSS JOIN AverageRevenueBenchmark arb
WHERE dr.total_revenue > arb.avg_doctor_revenue;

TASK

CTE_1 AverageRevenueBenchmark calculates total revenue per doctor and CTE_2 DoctorRevenue finds the average revenue across all doctors. A final query filters doctors above that threshold.

3. Recursive CTEs

A Recursive CTE calls itself repeatedly until it returns a result that no longer meets it's continuation condition.

It consists of two parts:

Anchor Member - This is the base query that executes first and only once and establishes the starting dataset, baseline rows or data for the recursion. It does not reference the name of the CTE
Recursive Member - This is the query that references the CTE itself to generate subsequent rows. It takes the output generated by the previous step, performs an operation on it, and feeds it back into the loop. It must also reference the CTE name and include a terminating condition for the loop.

SCENARIO

Generate a series of 7 consecutive dates for a weekly operational schedule report.

QUERY

WITH RECURSIVE DateSeries AS (
    -- Anchor Member: Runs once to set the Starting Point (June 1)
    SELECT DATE '2026-06-01' AS audit_date

    UNION ALL

-- Recursive Member:  References 'DateSeries' to add 1 day iteratively.
    SELECT audit_date + INTERVAL '1 day'
    FROM DateSeries
    WHERE audit_date < DATE '2026-06-07'
)
SELECT audit_date FROM DateSeries;

TASK

A recursive CTE to increment dates day-by-day starting from June 1, 2026.

4. CTEs with Joins and Aggregations.

CTEs can also handle heavy aggregations first, and then join those summarized results cleanly with other dimension tables.

SCENARIO

A report showing each department's name, total patient visits, and total revenue generated, joined directly with department profiles.

QUERY

-- Scenario: Summarizing departmental metrics using a CTE
sql
WITH DepartmentMetrics AS (
    SELECT 
        department_id,
        COUNT(visit_id) AS total_visits,
        SUM(total_bill_kes) AS department_revenue,
        ROUND(AVG(patient_satisfaction_score), 2) AS avg_satisfaction
    FROM Visits
    GROUP BY department_id
)
SELECT 
    d.department_name,
    dm.total_visits,
    dm.department_revenue,
    dm.avg_satisfaction
FROM DepartmentMetrics dm
INNER JOIN Dim_Departments d ON dm.department_id = d.department_id;

TASK

Aggregate visit metrics in CTE DepartmentMetrics and then INNER JOIN it with Departments table.

CTEs Vs Subqueries

Feature	Common Table Expressions (CTEs)	Subqueries (Inline / Derived Tables)
Readability	High. Defined at the top of the query block like a named variable.	Low to Moderate. Often nested deep inside `WHERE`, `FROM`, or `SELECT` clauses.
Reusability	Can be referenced multiple times within the main query body.	Must be re-written or duplicated if needed in multiple places.
Recursion	Supports recursion (vital for hierarchical data like organizational charts or date series).	Does not support recursion natively.
Performance	In modern PostgreSQL versions, CTEs can act as optimization fences or be automatically inlined depending on usage.	Highly optimized by the query planner as inline expressions.

SQL Subqueries

Subqueries, also called inner queries or nested queries, are queries nested inside larger SQL statements (such as SELECT , INSERT OR UPDATE)

Subquery Types

1. Non-Correlated Subqueries

These are independent subqueries that run independently on their own before the outer query runs. They evaluate once and its result is passed up to the outer query.

SCENARIO

Find all visits where the total bill is greater than the overall average hospital bill.

*QUERY

SELECT visit_id, total_bill_kes
FROM Visits
WHERE total_bill_kes > (
    SELECT AVG(total_bill_kes) FROM Visits
);

2. Correlated Subqueries

These are dependent subqueries that reference columns from the outer query. These subqueries cannot run independently and must re-evaluate row-by-row for every single record processed by the outer query.

SCENARIO

Find every visit where a patient's bill is higher than the average bill for that specific department.

QUERY

SELECT v1.visit_id, v1.department_id, v1.total_bill_kes
FROM Visits v1
WHERE v1.total_bill_kes > (
    SELECT AVG(v2.total_bill_kes)
    FROM Visits v2
    WHERE v2.department_id = v1.department_id
);

3. Scalar Subqueries

These are subqueries that return exactly one row and one column and can be used anywhere a single value is expected as a result.

SCENARIO

Display every visit's total bill alongside the absolute maximum hospital bill as a static comparison column.

QUERY

SELECT visit_id, total_bill_kes,
       (SELECT MAX(total_bill_kes) FROM Visits) AS max_hospital_bill
FROM Visits;

4. Nested Subqueries

These are subqueries nested deep inside other subqueries.

Subquery Operators.

IN This operator checks if a value matches any value returned by a list or a subquery.

QUERY

-- Find visits for patients who are from specific high-volume counties
SELECT * FROM Visits 
WHERE patient_id IN (SELECT patient_id FROM Patients WHERE county = 'Nairobi');

EXISTS Tests for the existence of any rows returned by the subquery and returns true as soon as it finds at least one matching row. (very efficient for correlated checks)

QUERY

-- Find departments that have at least one emergency admission
SELECT * FROM Departments d
WHERE EXISTS (
    SELECT 1 FROM Visits v 
    WHERE v.department_id = d.department_id AND v.admission_type = 'Emergency'
);

ANY / ALL Compares a value to a set of values returned by a subquery. > ANY means greater than the minimum while > ALL means greater than the maximum.

Placement of Subqueries (`SELECT`, `FROM`, `WHERE`)

WHERE Clause (Filtering)
Subqueries in the WHERE clause act as dynamic filters based on aggregate calculations or cross-table criteria e.g., in filtering visits with above-average length of stay
FROM Clause (Derived Tables / Inline Views)
A subquery in the FROM clause results in the treatment of the result set of that subquery as a temporary table that your main query can select from or join against.

EXAMPLE

Calculate the average department bills first, then categorize them in the outer query.

QUERY

SELECT dept_summary.department_id, dept_summary.avg_bill
FROM (
    SELECT department_id, AVG(total_bill_kes) AS avg_bill
    FROM Visits
    GROUP BY department_id
) AS dept_summary
WHERE dept_summary.avg_bill > 10000;

SELECT Clause (Computed Columns) These allow for the attachment of scalar contextual calculations or aggregate metrics directly onto every individual row output e.g., in adding a column showing the global average bill nest to every individual visit row.

Data Modelling, Relationships & Joins in Power BI

Njuguna Gichia — Sun, 13 Sep 2026 12:55:12 +0000

Data modelling is the process of analyzing and defining different data types, as well as the relationships between those bits of data. In Power BI, data modelling is the process of organizing multiple tables and defining the relationships between them so that power BI can analyze and visualize business data.

A good data model improves report performance, makes DAX calculations easier, supports accurate business insights, simplifies maintenance and provides better scalability as the amount of data and reporting requirements grow.

Data Modelling Approaches.

1.Flat Table.

A flat table / flat data model organizes data into a single table where each row represents a record and each column represents an attribute. Flat tables approach consolidates all the necessary data into a single denormalized table before it gets to the BI tool which sometimes means one table with all the columns (Facts and Attributes) already joined and ready for use.

Advantages
- It isn't tied to a specific tool since all transformations happen at a database level and so moving across tools becomes easier.
- It minimized the risk of discrepancies between reports and KPI's by ensuring a single source of data
- It makes it easier to track changes, roll back updates and maintain data integrity by allowing for version control
- It makes querying easier without needing to understand complex relationships between tables.

Disadvantages.
- Can become large and occasionally slower to query when dealing with massive datasets or frequent refresh cycles.
- May struggle in high volume environments, when powering multiple dashboards or when using databases that aren't optimized for analytical workloads.
- Risk of increasing storage costs and complicating updates due to data duplication.
- Very costly to optimize especially because they become inefficient as datasets grow.

Situations where appropriate.
-When your dataset has only a few thousand rows, making performance slowdowns unnoticeable.
- When individual users or small teams manage data in simple tools like Excel or CSV files.
- When you need to test an idea rapidly without spending time building complex table relationships.
- When denormalized data speeds up read operations in specific reporting layers or data pipelines by avoiding table joins.
- When you must easily share raw data across different apps, platforms, or serverless functions without a database engine.

Implications for Power BI performance and model complexity.
- Using a flat table data model in Power BI severely undermines performance and increases model complexity because it conflicts directly with the tool's underlying VertiPaq columnar storage engine. By forcing data into one massive, denormalized table, you suffer from inefficient column compression and a high memory footprint due to repeated text values, which triggers sluggish slicers and slow DAX scan times as Power BI is forced to query millions of rows rather than tiny dimension tables. Furthermore, while a single table eliminates relationships, it introduces development complexity through cluttered field lists, rigid models that cannot scale to new business processes, and high-risk DAX bugs like Auto-Exist, which can silently cause incorrect report totals when multiple columns are filtered together.

2. Star Schema

The star schema features a central fact table that contains measurable quantitative data surrounded by dimension tables that contain descriptive attributes related to the fact data. The structure looks like a star, with a single dense table sitting at the center, and connected to surrounding lookup tables using simple relationships.

This schema contains two distinct table types.
- Fact Tables.
These are at the center of the model. They record actual observations, metrics or transactional events (e.g., Fact_Sales) and consist of continuous numerical values and a collection of Foreign Keys which act as pointers linking back to the surrounding descriptive tables.
- Dimension Tables.
These are tables surrounding the fact table that contain descriptive context or attributes related to the events recorded in the fact table (e.g., Dim_Product, Dim_Date, Dim_Customer). Each Dimension table contains a unique Primary Key that anchors a one to many relationship flowing directly into the fact table. They also contain highly denormalized, flattened, repeated attributes to avoid deep branching relationships.

Advantages.

Yield rapid query responses because analytical databases scan short, wide tables to filter rows in long narrow tables.
The layout is easy for business analysts, report developers and end users to understand and navigate.
Aggregations run quickly because calculations are grouped by highly indexed dimensional attributes.

Disadvantages.

Because the dimensional tables are denormalized and repeat text fields, they increase the physical storage size of those dimensions.
This schema does not naturally handle complex hierarchy variations natively without forcing them into flat structures.
Because a single star schema focuses around one core process, combining multiple distinct processes requires creating independent fact tables that share dimensions.

 Situations where appropriate.

When stakeholders use tools like Tableau or PowerBI to build their own reports without needing to write complex SQL queries.
When tracking performance metrics across consistent structural or Temporal intervals is required such as daily sales or machinery sensor output.
When the data warehouses or data lakes process millions or billions of rows of historical data.

 Implications for Power BI performance and model complexity.

Adopting a star-schema architecture is the single most effective way to maximize Power BI performance and streamline model complexity because it aligns perfectly with the underlying VertiPaq in-memory columnar storage engine. By separating transactional metrics into a central fact table and descriptive attributes into dedicated, highly compressed dimension tables, Power BI achieves superior data compression, smaller file sizes, and accelerated DAX query scans. When users interact with report elements, visual rendering is nearly instant because slicers only need to query small, indexed dimension tables to dynamically pass filters down a clean one-to-many relationship path to the fact table. From a usability standpoint, this structural layout reduces model complexity by eliminating confusing relationship loops and replacing multi-table snowflake structures with an intuitive field list. Consequently, DAX formulas become remarkably simple, clean, and elegant, freeing report developers from writing defensive code to handle mismatched data granularities or fighting calculation defects like the Auto-Exist bug, resulting in highly accurate calculations and faster report development.

3. Snowflake Schema

This is a multi-dimensional data model where centralized, quantitative fact tables connect to dimension tables that are broken down into further lookup tables. The complex branching relationships extend outward from the center, resembling a snowflake.

Built using three distinct structural layers.

Central Fact Tables. Contains transaction-level keys, numbers and facts - (e.g., Fact_Sales containing metrics like Quantity and Revenue).
Primary Dimension Tables. These are tables that map directly to the fact table via a foreign key. They store a secondary foreign key pointing further down the line (e.g., Dim_Product contains a key for categories rather than the category text name).
Sub-Dimension Tables. These are normalized lookup tables connecting strictly to primary dimensions tables rather than the core fact table (e.g., a standalone Dim_Category table or a separate Dim_Geography table containing city/country data).

Advantages.
- because of data normalization rules, text labels are never repeated across rows which minimizes storage in relational databases.
- Changing a group name or an attribute hierarchy only requires updating a singe record in a sub-dimension table instead of bulk-row modifications.
- Eliminates the risk of typing or recording anomalies since attributes are isolated in single, authoritative lookup sources.

Disadvantages.
- Query engines must evaluate complex multi-table joins to trace a filter from a sub-dimension down to a transactional fact table.
- The visual layout is much more crowded and difficult to parse at a glance compared to a clean star structure.
- Keeping track of sequential, multi-layered keys increases development friction when writing ETL or data pipeline logic.

 Situations where appropriate.

Environments where reducing physical storage footprints and ensuring total database normalization are the absolute priorities.
When a single branch of dimensions (like a complex, multi-tiered geography master list) is shared globally across multiple entirely different business lines or separate fact tables.
Systems where data is strictly read directly via a traditional SQL engine optimized to parse multi-table relational joins natively.

 Implications for Power BI performance and model complexity.

In Power BI, a snowflake schema should generally be avoided or flattened into a Star Schema during the ingestion phase due to several architectural friction points:
A snowflake schema significantly degrades Power BI performance and adds immense development overhead because it introduces multi-hop relationship chains that force the VertiPaq columnar engine to calculate complex cross-table joins during runtime. While it saves minimal physical disk storage, this design undermines Power BI’s native column compression algorithms, resulting in sluggish slicer response times as filters must cascade through multiple intermediate lookup tables before arriving at the core fact table. From a usability standpoint, the model complexity spikes dramatically, loading the user interface with a cluttered, multi-table field list that confuses report creators. This intricate web of relationships forces developers to write highly complex, defensive DAX measures to accurately manage filter context across varying table granularities, drastically increasing the likelihood of development bottlenecks, unoptimized query paths, and frustrating calculation bugs.

Fact Tables & Dimension Tables.

Fact Tables.

Fact tables are quantifiable events of businesses that are recorded at a certain degree of detail. It exclusively records measurable, quantitative business events at the exact moment they occur. The information stored inside consists entirely of numeric data points known as measures and structural foreign keys (FK) used to identify when, where, and how the event happened.

Measures (Fact Tables) are numeric values meant to be aggregated using mathematical operations such as SUM, AVERAGE, MIN, or MAX. Examples include physical units sold, gross profit margins, account balances, or sensor temperature readings.

Dimension Tables.

Facts are given a descriptive context by dimension tables. They specify the way users slice, filter, group, and drill into measures. They contain the textual, categorical, or qualitative information that answers the "who, what, where, and when" of a transaction. Dimension rows are relatively stable and slow-moving compared to the rapidly expanding rows of a fact table.

Descriptive Attributes (Dimension Tables) are textual descriptors, IDs, or dates used to filter, group, slice, and slice-and-dice measures in a report. Examples include an employee’s job title, a product’s color, a customer's country, or a calendar month name.

Granularity.

Grain defines exactly what one row represents in a fact table. All foreign keys and measures inside the fact table have to match this chosen grain to prevent double counting or data corruption during reporting.

Fact Table Example.

Dimension Table Example.

Table plain Text.

Relationships in Power BI

A relationship in Power BI defines a logical link between two tables based on a shared column. In a relational data model, data is split into specialized tables to eliminate redundancy and improve performance (normalization). Relationships allow Power BI to filter and slice metrics in transaction tables using descriptive attributes from dimension tables without combining everything into a single massive, flat table.

Core Data Modeling Concepts

Primary Key (PK): A column (or set of columns) in a dimension table where every value is completely unique. It serves as the master identifier for a single entity (e.g., CustomerKey in Dim_Customer).
Foreign Key (FK): A column in a table that references a Primary Key in another table. It allows multiple records to map back to a single primary entity (e.g., CustomerKey in Fact_Sales).
Unique Values: Ensures that each key appears exactly once on the "One" side of a relationship. If duplicates exist where Power BI expects unique values, data integrity is lost, leading to calculation errors.
Cardinality: Refers to the numerical mapping between rows of two related tables (e.g., 1:*, 1:1, :).
Referential Integrity: The rule ensuring that every Foreign Key value in a fact table exists as a matching Primary Key in the corresponding dimension table. If a sale records CustomerKey = 999, but key 999 is missing from Dim_Customer, referential integrity is broken, resulting in blank/unmatched categories in reports.
Active vs. Inactive Relationships:
- Active Relationship: The default link between two tables used to propagate filter context automatically across visuals. Only one active relationship can exist between any two tables at a time.
- Inactive Relationship: A dormant connection created when multiple paths exist between two tables (e.g., Dim_Date linked to both OrderDate and ShipDate in Fact_Sales). It remains unused until explicitly activated in DAX measures using functions like USERELATIONSHIP().

Example from Snowflake-Schema-Main

Dim_Customer Table: CustomerKey is the Primary Key. It contains strictly unique values because each customer is registered only once in master records.
Fact_Sales Table: CustomerKey acts as a Foreign Key. A single customer can make multiple purchases over time, so their CustomerKey appears multiple times (many rows) across transactional records.

Relationship Cardinalities

1. One-to-Many (1:*)

How It Works: One row in the dimension table connects to zero, one, or multiple rows in the fact table. Filters automatically flow from the "One" side (dimension) to refine the "Many" side (fact).
Practical Example: Dim_Product (1) connects to Fact_Sales (*) via ProductKey. Filtering for "Laptops" filters all transaction records for laptops.
When to Use: This is the standard best practice for dimensional data modeling (Star Schema / Snowflake Schema).
When NOT to Use: Never use if both tables contain non-unique, repeating key values.

[ Dim_Product ] (1)  <--------->  (*) [ Fact_Sales ]
(ProductKey: PK)                      (ProductKey: FK)

2. One-to-One (1:1)

How It Works: One row in Table A links directly to exactly one matching row in Table B.
Practical Example: Linking a Dim_Customer table to a separate Dim_CustomerSecurity table where sensitive user details are stored on a 1-to-1 basis.
When to Use: Useful when splitting large tables for security purposes, performance optimization, or separating frequently accessed columns from rarely used ones.
When NOT to Use: Avoid when unnecessary, as two 1:1 tables can almost always be merged into a single table, reducing model complexity.

[ Dim_Customer ] (1)  <--------->  (1) [ Dim_CustomerSecurity ]
(CustomerKey)                           (CustomerKey)

3. Many-to-Many (:)

How It Works: Neither table has unique values in the key column. Rows on both sides connect to multiple rows in the opposite table, requiring bi-directional filtering by default or bridge handling.
Practical Example: Linking a Fact_Sales table directly to a Fact_Targets table on DateKey when both tables contain multiple rows for each date.
When to Use: Use sparingly when modeling direct relationship scenarios where unique keys cannot be established.
When NOT to Use: Avoid using as a quick fix for duplicate key issues. : Relationships create complex DAX calculations, introduce ambiguity, and can produce unpredictable visual aggregations. Instead, build a intermediate bridge/dimension table with unique values to split it into two 1: relationships.

[ Fact_Sales ] (*)  <--- (Bridge / Intermediate) --->  (*) [ Fact_Targets ]
(DateKey)                                                   (DateKey)

Filter Direction.

When you select a value in a report visual or slicer (such as choosing a specific year or product category), Power BI creates a filter context. This filter context flows automatically across active relationships between tables to isolate the relevant transaction rows in your fact table.

How Filter Context Flows

Filters flow along the relationship line between tables, moving from the table where the selection was made to the target table. The direction in which this filter context is allowed to travel depends on the Filter Direction setting of the relationship.

[ Dimension Table ]  ─── (Filter Direction Arrow) ───>  [ Fact Table ]

1. Single-Direction Filtering (Default & Standard)

How It Works: Filters travel in one direction only—from the "One" side (Dimension) to the "Many" side (Fact).
Behavior: Selecting an entity in a dimension table filters the records in the fact table. However, filtering a fact table does not filter or restrict the items shown in the dimension table.
Model View Indicator: A single arrowhead pointing from the dimension table toward the fact table (1 ──> *).

2. Bidirectional / Both Filtering

How It Works: Filters travel in both directions across the relationship.
Behavior: Selecting a value in a dimension filters the fact table, and filtering the fact table simultaneously filters the connected dimension table (as well as any other downstream dimension tables).
Model View Indicator: A double arrowhead pointing toward both tables (1 <──> * or * <──> *).

Step-by-Step Example: Filtering `Fact_Sales` from `Dim_Product`

Using our dataset model (Dim_Category ──> Dim_Product ──> Fact_Sales):

User Action: A user clicks on the "Electronics" category in a report slicer.
First Propagation: The filter context targets Dim_Category where CategoryName = "Electronics". It passes down the 1 ──> * relationship on CategoryKey to Dim_Product, instantly filtering Dim_Product to only include products belonging to Electronics (e.g., ProductKey = 1 for "Laptop Pro" and ProductKey = 2 for "Smartphone X").
Second Propagation: The filter context continues down the next 1 ──> * relationship on ProductKey to Fact_Sales.
Final Result: Power BI filters Fact_Sales to keep only rows where ProductKey is 1 or 2, dynamically recalculating metrics like SUM(Revenue) or SUM(Quantity) strictly for Electronics transactions.

Why Bidirectional Filtering Should Be Used Carefully

While bidirectional filtering allows tables to filter each other, enabling it permanently in relationship properties introduces major data modeling risks:

Ambiguous Filter Paths: When multiple bidirectional relationships exist in a model, Power BI creates closed loops (circular paths). The engine cannot determine which path to take to apply a filter, leading to unpredictable measure results or DAX calculation errors.
Unnecessary Model Complexity & Slow Performance: Bidirectional paths force Power BI's VertiPaq engine to evaluate dynamic cross-filtering on large tables at report runtime, increasing memory usage and slowing down report visuals.
Unexpected Slicer Masking: Filtering a transaction table can unexpectedly mask or hide valid items in dimension drop-downs, confusing users who expect to see all available lookup options.

Best Practice: Keep physical relationship filter directions set to Single. If bidirectional filtering is required for a specific business metric, activate it dynamically inside individual DAX measures using CROSSFILTER() rather than changing the physical model properties.

Joins in Power Query.

A join is a relational operation that combines columns from two tables based on matching values in one or more common key columns.

In Power Query, tables are combined using Merge Queries (Home $\rightarrow$ Merge Queries). You select a primary table (Left Table), a secondary table (Right Table), and highlight the matching key columns in both preview panels. Power Query evaluates the matching key values and appends a nested Table column containing the matched rows from the right table, which can then be expanded.

Source Sample Tables from Our Model

To illustrate each join type clearly, we use two sample tables from our dataset:

Left Table: Dim_Customer
- Contains records for CustomerKey 1 through 5.
Right Table: Dim_CustomerSecurity
- Contains records for CustomerKey 1 through 4, plus an unassigned security record with CustomerKey 99.

1. Left Outer Join (All from First, Matching from Second)

How It Works: Evaluates every row in the left table and retrieves matching rows from the right table. If no match exists in the right table, null values are filled in.
Records Retained: All rows from the Left Table + matched rows from the Right Table.
Practical Example: Merging master customer details (Dim_Customer) with optional security audit records (Dim_CustomerSecurity).

2. Right Outer Join (All from Second, Matching from First)

How It Works: Evaluates every row in the right table and retrieves matching rows from the left table. Unmatched left attributes are filled with null.
Records Retained: All rows from the Right Table + matched rows from the Left Table.
Practical Example: Ensuring all compliance records in Dim_CustomerSecurity are evaluated against customer master records.

3. Full Outer Join (All Rows from Both Tables)

How It Works: Combines all records from both tables regardless of matches. Missing pairs on either side are filled with null.
Records Retained: All rows from both tables (complete union of matched and unmatched keys).
Practical Example: Reconciling two lists to see all active customers and all security profiles.

4. Inner Join (Only Matching Rows)

How It Works: Keeps only rows where the key value exists in both the left and right tables. Unmatched records from both sides are dropped.
Records Retained: Strictly matching pairs present in both tables.
Practical Example: Returning only customers who have completed security verification.

5. Left Anti Join (Rows Only in First Table)

How It Works: Filters the left table to isolate rows that have no matching key in the right table.
Records Retained: Left-only unmatched rows.
Practical Example: Identifying customers in Dim_Customer who are missing a security profile in Dim_CustomerSecurity.

6. Right Anti Join (Rows Only in Second Table)

How It Works: Filters the right table to isolate rows that have no matching key in the left table.
Records Retained: Right-only unmatched rows.
Practical Example: Finding orphan security profiles in Dim_CustomerSecurity that are not assigned to any customer in Dim_Customer.

Power Query Joins vs Power BI Relationships

Merging tables in Power Query and establishing model relationships in Power BI serve fundamentally different functions within your analytical data pipeline.

Key Structural Differences

Physical Data Combination in Power Query Merge: Yes, a Power Query Merge physically combines attributes into a single query structure during data transformation. When expanding columns from Dim_CustomerSecurity into Dim_Customer, Power Query reshapes the underlying M-code query and loads a single combined table into memory.
Separation via Model Relationships: No, creating a relationship between tables does not combine them physically. Both tables remain distinct physical storage objects in the VertiPaq engine. The relationship acts as a logical bridge allowing filter context to pass dynamically between them at visual rendering time.
Workflow Stage Execution:
- Power Query Merge: Occurs at the ETL (Extract, Transform, Load) stage before data is compressed and loaded into the tabular data model.
- Model Relationships: Defined in the Model View stage after data has been loaded into the VertiPaq engine, governing DAX calculation context and reporting visuals.

Core Comparison Matrix

Feature / Aspect	Power Query Merge Queries	Power BI Model Relationships
Primary Purpose	Denormalize, consolidate, or clean datasets	Build Star/Snowflake schemas for dynamic analytics
Storage Impact	Permanently increases table width (more columns/rows)	Keeps tables lean and distinct in VertiPaq memory
Execution Engine	Power Query Formula Engine (M)	VertiPaq Analytics Engine (DAX)
Dynamic Interactivity	Static (fixed during data refresh)	Dynamic (responds instantly to slicers and visual filters)

When to Choose a Merge vs. a Relationship

1. When to Use a Merge (Power Query)

Consolidating 1-to-1 Extensions: Merging extended attribute lookup tables (e.g., merging Dim_CustomerSecurity into Dim_Customer to avoid holding unnecessary table entities in your model).
Flattening Snowflake Dimensions: Collapsing multi-level dimension hierarchies (e.g., merging Dim_Category directly into Dim_Product to create a clean, single-level Product dimension table).
Pre-filtering Fact Rows: Joining reference keys to eliminate unwanted rows before loading data into the model.

2. When to Use a Relationship (Model View)

Connecting Fact and Dimension Tables: Linking Fact_Sales to Dim_Customer or Dim_Product.
Preserving Multi-Fact Models: Connecting shared dimensions (e.g., Dim_Category or Dim_Date) to multiple fact tables like Fact_Sales and Fact_Targets.

Impact of Excessive Merging on Data Model Performance

Severe Storage Bloat: Merging dimension attributes directly into large fact tables (e.g., merging Dim_Customer attributes into 1,000,000 rows of Fact_Sales) forces repetitive string text (such as customer names and email addresses) into every fact row. This ruins VertiPaq run-length encoding (RLE) dictionary compression and balloons your file size.
Granularity Errors and Measure Corruption: Merging a table with a higher row count (1-to-Many) into a fact table duplicates transaction metrics, causing measures like SUM(SalesAmount) or COUNTROWS() to return inaccurate, double-counted figures.

Why Keeping Fact and Dimension Tables Separate is Preferable

Keeping fact tables (containing numerical events/metrics) separated from dimension tables (containing descriptive attributes) inside a Star Schema provides critical modeling advantages:

Optimal Compression: High-cardinality text fields are stored once in lightweight dimension lookup tables. Fact tables retain only narrow integer keys (CustomerKey, ProductKey), allowing the VertiPaq engine to compress millions of rows efficiently.
Flexible Analytics Across Shared Dimensions: A single standalone dimension table (e.g., Dim_Category) can simultaneously filter multiple fact tables (Fact_Sales and Fact_Targets), enabling side-by-side performance tracking against target KPIs.
Clean, Readable DAX: Keeping entities separate simplifies DAX measures, avoiding complex filtering logic required when working with oversized single-flat-table models.

Recommended Power BI Model

Merging tables in Power Query and establishing model relationships in Power BI serve fundamentally different functions within your analytical data pipeline.

Architecture Overview: Merge vs. Relationship

Key Structural Differences

Physical Data Combination in Power Query Merge: Yes, a Power Query Merge physically combines attributes into a single query structure during data transformation. When expanding columns from Dim_CustomerSecurity into Dim_Customer, Power Query reshapes the underlying M-code query and loads a single combined table into memory.
Separation via Model Relationships: No, creating a relationship between tables does not combine them physically. Both tables remain distinct physical storage objects in the VertiPaq engine. The relationship acts as a logical bridge allowing filter context to pass dynamically between them at visual rendering time.
Workflow Stage Execution:
- Power Query Merge: Occurs at the ETL (Extract, Transform, Load) stage before data is compressed and loaded into the tabular data model.
- Model Relationships: Defined in the Model View stage after data has been loaded into the VertiPaq engine, governing DAX calculation context and reporting visuals.

Core Comparison Matrix

Feature / Aspect	Power Query Merge Queries	Power BI Model Relationships
Primary Purpose	Denormalize, consolidate, or clean datasets	Build Star/Snowflake schemas for dynamic analytics
Storage Impact	Permanently increases table width (more columns/rows)	Keeps tables lean and distinct in VertiPaq memory
Execution Engine	Power Query Formula Engine (M)	VertiPaq Analytics Engine (DAX)
Dynamic Interactivity	Static (fixed during data refresh)	Dynamic (responds instantly to slicers and visual filters)

When to Choose a Merge vs. a Relationship

1. When to Use a Merge (Power Query)

Consolidating 1-to-1 Extensions: Merging extended attribute lookup tables (e.g., merging Dim_CustomerSecurity into Dim_Customer to avoid holding unnecessary table entities in your model).
Flattening Snowflake Dimensions: Collapsing multi-level dimension hierarchies (e.g., merging Dim_Category directly into Dim_Product to create a clean, single-level Product dimension table).
Pre-filtering Fact Rows: Joining reference keys to eliminate unwanted rows before loading data into the model.

2. When to Use a Relationship (Model View)

Connecting Fact and Dimension Tables: Linking Fact_Sales to Dim_Customer or Dim_Product.
Preserving Multi-Fact Models: Connecting shared dimensions (e.g., Dim_Category or Dim_Date) to multiple fact tables like Fact_Sales and Fact_Targets.

Impact of Excessive Merging on Data Model Performance

Severe Storage Bloat: Merging dimension attributes directly into large fact tables (e.g., merging Dim_Customer attributes into 1,000,000 rows of Fact_Sales) forces repetitive string text (such as customer names and email addresses) into every fact row. This ruins VertiPaq run-length encoding (RLE) dictionary compression and balloons your file size.
Granularity Errors and Measure Corruption: Merging a table with a higher row count (1-to-Many) into a fact table duplicates transaction metrics, causing measures like SUM(SalesAmount) or COUNTROWS() to return inaccurate, double-counted figures.

Why Keeping Fact and Dimension Tables Separate is Preferable

Keeping fact tables (containing numerical events/metrics) separated from dimension tables (containing descriptive attributes) inside a Star Schema provides critical modeling advantages:

Optimal Compression: High-cardinality text fields are stored once in lightweight dimension lookup tables. Fact tables retain only narrow integer keys (CustomerKey, ProductKey), allowing the VertiPaq engine to compress millions of rows efficiently.
Flexible Analytics Across Shared Dimensions: A single standalone dimension table (e.g., Dim_Category) can simultaneously filter multiple fact tables (Fact_Sales and Fact_Targets), enabling side-by-side performance tracking against target KPIs.
Clean, Readable DAX: Keeping entities separate simplifies DAX measures, avoiding complex filtering logic required when working with oversized single-flat-table models.

Getting Started with Excel for Data Analytics: From Basics to Data Cleaning.

Njuguna Gichia — Wed, 02 Sep 2026 20:26:38 +0000

Microsoft Excel is a spreadsheet software that allows users to organize, analyze, calculate and visualize data. Despite the rise of Python, R and SQL, Excel has remained a foundational data analysis tool across industries because of it's low learning curve, powerful features like pivot tables, formulas, functions and quick charts and because no coding experience is needed to navigate or see results.

In this article, we will explore navigating the grid, essential formulas, data filtering and cleaning messy data using Excel's built-in features.

Understanding The Excel Interface

The excel interface consists of these core elements.

The Ribbon & Tabs
- The toolbar across the top that contains the primary commands organized into tabs (Home, Insert, Draw, Page Layout etc.)
The Quick Access Toolbar
- The icons on the top left corner to Save, Undo and Redo.
Formula Bar
- The area above the grid where the content or the formula of the selected cell appears.
Name Box
- Displays the address of the selected Cell (e.g. B3)
The Grid
- This is the visual network of intersecting horizontal lines and vertical lines that creates the workspace. The Grid is made up of
  - Rows - Horizontal lines of cells labelled vertically with numbers (e.g. Row 1)
  - Columns - Vertical line of cells labelled horizontally with letters (e.g. Column A)
  - Cells - A single box where a row and a column intersect (e.g. B4)
  - Range - A group of selected cells (e.g., A1:C3).
Worksheet Tabs
- The tabs at the bottom that show the name of the spreadsheet and also allow you to navigate between the sheets in a workbook.

Essential Navigation & Keyboard Shortcuts

Keyboard Navigation:

Arrow keys – Move one cell at a time
Ctrl + Arrow key – Jump to the end of the data region
Tab – Move one cell right
Enter – Move one cell down
Ctrl + S – Save the file

Mouse Navigation:

Click to select a single cell
Click & Drag to select multiple cells
Scroll to move vertically or horizontally across the sheet

Efficiency Combinations (Keyboard & Mouse):

Ctrl + Right Arrow to jump to the end of row
Ctrl + Down Arrow to jump to the bottom
Ctrl + Shift + Arrow Keys to select ranges efficiently

The Data Environment

Data Types.

Excel supports 4 primary data types. i.e.,

Text - Any non-numeric or character based information e.g. names, addresses, descriptions).
Numbers - Any values that can participate in mathematical calculations.
Dates and Time - Values used to track time-based events and calculations
Boolean - Logical values, either (TRUE or FALSE) usually generated from logical formulas.

Entering Data

We can enter data anywhere in the grid by:

Clicking on a cell
Typing in our value (e.g. Hello)
Press Enter to move down or Tab to move right
Double click on the cell or selecting it and pressing F2 to edit existing content. ### Formatting Basics. Formatting changes how the data looks without changing the values stored in the cells, making the data visually clear and more professional.

Text Formatting.

Selecting the Cell
Navigate to Home > **Font Group
Clicking Bold (Ctrl+B), Italic (Ctrl + I), Underline (Ctrl + U)
Change Font, Font Size, and Font Colour.

Number Formatting

Select cells with numeric data.
Navigate to Home > Number section
Choose the appropriate format:
- Number (with decimals)
- Currency (KES, $, etc.)
- Percentage
- Date formats

Alternatively, we can access the Format Cells Dialogue by (Ctrl+1)

Data Cleaning and Sorting.

Data Cleaning is the process of formatting, or removing incorrect, duplicate or incomplete data within a dataset. Sometimes, especially when working with large datasets, data can get misplaced, mistyped or mislabeled. Cleaning ensures that the dataset is accurate and consistent.

Data cleaning prevents making decisions based on flawed data, catches and fixes errors early and provides clean inputs for formulas and charts.

For Practice, I used a spreadsheet containing HR data, applying a combination of built-in excel tools and formulas to clean it.

Best Practice: Always preserve your original raw data. Make all required cleaning changes on a duplicate worksheet or separate file.

How to Duplicate Your Data

Click anywhere in the spreadsheet and press Ctrl + A to select all cells.
Press Ctrl + C to copy.
Click the + button at the bottom of the window to create a new sheet.
Click cell A1 in the new sheet and press Ctrl + V to paste.
Double-click the sheet tab and rename it to Cleaned_HR_Data.

This gives us a canvas to work in.

The Data Cleaning Workflow

We did the cleaning in distinct stages.

Analyzing the Data:

Here we checked the data for visible errors and inconsistencies. In this dataset, we found mixed data types in numeric fields (text mixed with numbers), missing/blank values, duplicate entries, currency formatting errors, typos and misspellings etc.
Removing duplicates:

The second step was to delete identical rows or entries that were recorded more than once. I achieved this by:
Selecting the entire spreadsheet using Ctrl+A
navigating to the Data Tab and Clicking Remove Duplicates.
Confirming the target columns and clicking OK

Fixing structural errors:

This step involved correcting typos, standardizing inconsistent capitalization (e.g., changing "Nairobi", "nairobi", and "NAIROBI" into one uniform format), merging mislabeled categories and trimming extra spaces in the text columns. Certain categories like employer ID also had inconsistent formatting.

Standardizing Codes:
Employer ID inconsistent formatting was remedied by selecting the column, then applying an auto filter using Shift+Ctrl+L and checking the Drop down to see the range of values I had. Then I selected the Column, and Ctrl+H to find and replace and replaced all EMP- with a Blank to fix the formatting.

Trimming Spaces and fixing Capitalization: TRIM() removes extra spaces between words. Name Entries were fixed by using =PROPER(TRIM(TEXT)) on the first and last name columns for both text length and proper case and then replacing the names in both columns using Paste (Values)

Handling missing values:

Deciding whether to filter out empty cells, fill them with zeroes, or input an average value.
Empty/missing values in the remaining text and numerical Columns were replaced by unknown converting them into unknown values using the Ctrl+H find and replace feature.

Standardizing formats:

Ensuring all dates followed a consistent format (e.g., YYYY-MM-DD) and numbers are converted from plain text into actual numeric formats.

Salary & Bonuses: Highlighted the numeric columns and pressed Ctrl + 1 to set the format to Currency (Ksh).

Dates: Selected the date column and set the formatting to DD/MM/YYYY.

To verify that we had successfully cleaned and validated our data, we applied auto filters Ctrl + Shift + L to the entire Spreadsheet and sorted each column to confirm that our categories were uniform, and our numbers were formatted correctly and whether we had any lingering errors. We then had a 'clean' dataset that was ready for analysis.

Understanding the Git Workflow: Working Directory, Staging, Commit and Push.

Njuguna Gichia — Sat, 22 Aug 2026 20:20:07 +0000

Two weeks ago, I joined a Data Science bootcamp and part of the learning outcomes for this week was independently navigating the Git Workflow to successfully upload a test project as part of the data portfolio to a GitHub repository.

Git is a version control system that runs locally on a personal computer and allows us to keep track of changes made to our projects. Git creates saved checkpoints knowns as commits that act as snapshots of the state of the projects.

GitHub is an online service where we can store Git repositories. While Git is installed and works locally on our personal computers, GitHub stores our repositories online and allows us to share and collaborate on projects

My project was a custom energy consumption segmentation analysis using k means. Other than local python, this project required additional installation of pandas, numpy for data processing and manipulation, scikit-learn for preprocessing, and matplotlib and seaborn for the data visualization.

The Git workflow happened in 4 distinct stages.

The Working Directory.

The working directory is the folder containing my project files where the terminal or Git is currently working from. These include:
- README.md file which is a description of the entire project written in raw markdown. It contains information about the project such as:
- What the project is about
- What dataset was used
- The tools used
- The analysis was performed
- Contents of each folder
- Important findings
- How another person can understand the project
- Data Folder which contains an excel spreadsheet named energy_consumption_data.csv that contained the consumer data.
- Scripts Folder which contains a K means python script named segmentation.py. Python script files typically end with the .py extension.

To create a working directory using Git Bash, I opened Gitbash on Desktop by clicking on the windows icon on the taskbar, typing in bash and clicking on Gitbash to open a new window. Once the window was open, I ran cd Desktop, to navigate to my Desktop where I created a new Folder Titled energy_consumption_analysis by running the command

mkdir energy_customer_data_analysis

The command cd means change directory and it allows you to navigate in and out of directories. cd allows you to enter a folder while cd .. allows you to move backward by exiting a directory or a folder into the parent directory. Inside energy_customer_data_analysis, I created two folders Data and Scripts simultaneously by running the mkdir command which allows you to create 2 folder simultaneously by listing two folder names separated by a space:

mkdir Data Scripts

command created two folders named Data and Scripts.

I also created the README.md file by running touch README.md in bash.

to make sure that I had created all the files and folders, I ran the ls command in bash which allowed me to see all the files and folders created in the working directory.

I copied the CSV file from my downloads into the Data folder then used bash to navigate into the Scripts folder by running cd Scripts since I was still inside my project folder. once inside the Scripts folder, I ran touch segmentation.py to create the python script file and then ran code . in bash to then open segmentation.py in VS Code.

After writing the python code in VS Code, I went back to bash and ran cd .. to exit the scripts folder and ran nano README.md to open the README file in Bash's text editor nano where i wrote the description for the project in Markdown. Once this was complete, Ctrl + O, then Enter to save then Ctrl + X to go back to bash. To see what's inside the README file, use cat README.md in bash to display text inside the README.md file and ls to inspect the project folder. Once satisfied with everything, we then move on to the next step.

The Staging Area.

The staging area is the preparation area for the next commit. Here, we Initialize Git, check changes and stage files. Before running any git commands, we check our location by running pwd . Pwd means print working directory and allows us to see the exact location we are working from.

Initialize git

Once we ascertain that we are in the correct project folder, we can initialize git by running the command

git init

which instructs Git to begin managing the project. When run successfully, we should see : `Initialized empty Git repository in (the working directory path)

We also run ls -la which allows us to see a .git folder which acts as Git's internal memory for the project.

Check Project Status

We check project status by running

git status

This allows git to show us exactly what is happening in our project and because this is a completely new project and repository that git has not started tracking, our project status is untracked.

Project Staging

We can add individual files in our directory by running git add <filename> to select the individual file or we can run git add . to add everything in the current directory. This moves the selected files from the working directory into the staging area.

We can check the status of our project by running git status and if the staging is successful, our selected files and folders should be listed in green.

The Commit.

We create the first commit by running

git commit -m "Add Energy Consumption Segmentation Analysis Project"

git commit creates a saved Git checkpoint while -m means the message we want to attach to the commit which in this case is "Add Energy Consumption Segmentation Analysis Project"

The message explains what was saved. A good commit message creates an understandable history of the project.

Commit History

We can view the commit history by running

git log

which shows us the commit history or by running:

git log --oneline

for a much shorter commit history.

Creating the Github Repository.

We can create a new repository in GitHub through our browser.
Go to Github.com
Sign in.
Once Signed in Click the + button.
Select: New repository
Enter Repository Name(e.g., energy_consumer_data_segmentation_analysis)
You can also Add an optional description such as:(energy_consumer_data_analysis_using_Python)
Choose between a Public or Private Repository (keep in mind that a public repository can generally be viewed by anyone and should not be used for sensitive or confidential data. In our case, the data is synthetic and non-specific so a public repository is fine.)
Leave options such as: Add README, Add .gitignore, and Add license unchecked.
Select Create New Repository.

After creating the repository, navigate to the connection options, Select SSH if you have it already configured.

The Push.

Pushing means sending committed changes on our projects to GitHub.

After copying the SSH string, return to Git Bash. To check whether we are still in the project, run

pwd

If still in the folder, run:

git remote add origin git@github.com:USERNAME/project-name.git

USERNAME: is your GitHub Username
Project-name.git: is the name of the repository you've just created.

type git remote add origin and then press Shift + Insert on your keyboard to paste the SSH string you've just copied from GitHub to complete the command and then run it.

Checking the Remote.

A remote is a connection between our local repository and another repository which is in our case, the one on GitHub.

We check the remote by running:

git remote -v

origin git@github.com:USERNAME/project-name.git (fetch)

means that Git can retrieve information from the remote repository

origin git@github.com:USERNAME/project-name.git (push)

means that git can send commits to the remote Repository.

Pushing the Code

Pushing means sending the committed changes in my project to the Remote Git Repository.

we do this by running:

git push -u origin main
Bash will prompt you to enter the configured passphrase. if you have one, type it in and then Enter

Verifying the Push

Return to the repository page on your browser and refresh it.
GitHub should render the contents in your README.md file visually.
You should also be in a position to see your files and the contents in those files (i.e the CSV file in the Data folder and the .py file in the Scripts folder).

If we can see this, then our project has successfully been pushed to GitHub.

DEV Community: Njuguna Gichia

Introduction to SQL Window Functions, CTEs, Joins & Subqueries

Introduction to SQL

What is SQL Used For in Data?

The Basic Anatomy of a SQL Query

Window Functions

Types of Window Functions

1. Ranking Functions

2. Aggregate Window Functions

3. Value Window Functions

Types of Value Window Functions

SQL Joins

Types of Joins

Inner Join

Left (Outer) Join

Right (Outer) Join

Full (Outer) Join

Self Join

Cross Join

Common Table Expressions (CTEs)

CTE Types

1. Basic CTEs

2. Multiple CTEs

Independent CTEs (Parallel Logic)

*Dependent / Chained CTEs (Sequential Logic)

3. Recursive CTEs

4. CTEs with Joins and Aggregations.

CTEs Vs Subqueries

SQL Subqueries

Subquery Types

1. Non-Correlated Subqueries

2. Correlated Subqueries

3. Scalar Subqueries

4. Nested Subqueries

Subquery Operators.

Placement of Subqueries (SELECT, FROM, WHERE)

Data Modelling, Relationships & Joins in Power BI

Data Modelling Approaches.

1.Flat Table.

2. Star Schema

3. Snowflake Schema

Fact Tables & Dimension Tables.

Fact Tables.

Dimension Tables.

Granularity.

Relationships in Power BI

Core Data Modeling Concepts

Example from Snowflake-Schema-Main

Relationship Cardinalities

1. One-to-Many (1:*)

2. One-to-One (1:1)

3. Many-to-Many (:)

Filter Direction.

How Filter Context Flows

1. Single-Direction Filtering (Default & Standard)

2. Bidirectional / Both Filtering

Step-by-Step Example: Filtering Fact_Sales from Dim_Product

Why Bidirectional Filtering Should Be Used Carefully

Joins in Power Query.

Source Sample Tables from Our Model

1. Left Outer Join (All from First, Matching from Second)

2. Right Outer Join (All from Second, Matching from First)

3. Full Outer Join (All Rows from Both Tables)

4. Inner Join (Only Matching Rows)

5. Left Anti Join (Rows Only in First Table)

6. Right Anti Join (Rows Only in Second Table)

Power Query Joins vs Power BI Relationships

Key Structural Differences

Core Comparison Matrix

When to Choose a Merge vs. a Relationship

1. When to Use a Merge (Power Query)

2. When to Use a Relationship (Model View)

Impact of Excessive Merging on Data Model Performance

Why Keeping Fact and Dimension Tables Separate is Preferable

Recommended Power BI Model

Architecture Overview: Merge vs. Relationship

Key Structural Differences

Core Comparison Matrix

When to Choose a Merge vs. a Relationship

1. When to Use a Merge (Power Query)

**Dependent / Chained CTEs (Sequential Logic)*

Placement of Subqueries (`SELECT`, `FROM`, `WHERE`)

Step-by-Step Example: Filtering `Fact_Sales` from `Dim_Product`