DEV Community: Brayan Wafula

Mastering SQL: Window Functions, Joins, Common Table Expressions (CTEs), and Subqueries Using a Hospital Database

Brayan Wafula — Tue, 20 Oct 2026 23:44:13 +0000

Introduction

Structured Query Language (SQL) is a standard programming language used to store, retrieve, manipulate, and analyze data in relational databases.
In a hospital environment, SQL helps manage patient records, appointments, doctors, departments, medical bills, and payments. But, as hospital databases grow, basic queries using SELECT, WHERE, and ORDER BY may not be sufficient for complex analytical tasks.
Some example are, a hospital administrator may need to

Rank doctors by revenue.
Identify patients with outstanding balances.
Determine which departments have the highest number of appointments.
Compare monthly payments.

SQL provides several advanced techniques for solving these problems:

Window functions perform calculations across related rows without combining them into a single result row.
SQL joins combine information from two or more tables using related columns.
Common Table Expressions (CTEs) create named query results that simplify complex SQL statements.
Subqueries allow one SQL query to be embedded inside another query.

This article explains these concepts, their different types, their practical applications, and their differences using a hospital database in PostgreSQL.

SQL Window Functions

What are window functions?

SQL window functions (also known as analytic functions) perform calculations across a set of table rows that are related to the current row, without collapsing the result set into a single output row. This set of rows is called a window.

Unlike a standard GROUP BY clause, which compresses your rows to show only the summaries, window functions preserve the original granularity of your data. Every single record remains visible, and the calculated value appears as a new column alongside your existing details

Window functions are useful for:

Ranking doctors by revenue.
Finding the previous or next patient payment.
Calculating running totals.
Comparing current payments with earlier payments.
Identifying the highest and lowest payments.
Dividing patients into analytical groups.

The general syntax is:

SELECT column_name,
 window_function() OVER ( 
PARTITION BY grouping_column 
ORDER BY sorting_column 
) AS result 
FROM table_name;

The OVER() clause defines the window over which the function operates.

Understanding OVER(), PARTITION BY, ORDER BY, and window frames

These components determine how a window function processes data.

A. OVER()

The OVER() clause in SQL defines a "window" of rows around the current row to perform a calculation. It turns standard functions (like SUM, AVG, or ROW_NUMBER) into window functions, allowing you to calculate aggregates or rankings without losing your underlying row-level data

B. PARTITION BY

In SQL, the PARTITION BY clause is a sub-clause used inside window functions (via the OVER() clause) to divide a query's result set into smaller, isolated groups—called partitions.

The defining characteristic of PARTITION BY is that it performs group-level calculations while keeping every individual row intact. Calculations (like sums, averages, or rankings) are calculated independently within each partition and reset when moving to the next group.

C. ORDER BY

The SQL ORDER BY clause is used to sort the result set of a query in either ascending or descending order. Without it, a database does not guarantee the order of the returned rows, meaning they can appear random or change between query runs

1. Basic Syntax

The ORDER BY clause always goes at the very end of your SQL query (or right before a LIMIT / TOP clause if you are restricting rows).

SELECT column1, column2
FROM table_name
ORDER BY column_name [ASC | DESC];

• ASC (Ascending): Sorts data from smallest to largest (A to Z, 0 to 9, oldest date to newest). This is the default setting, so you do not have to explicitly type it.
• DESC(Descending): Sorts data from largest to smallest (Z to A, 9 to 0, newest date to oldest).

2. Order of Execution

It helps to know that SQL doesn't read your query from top to bottom. The database processes ORDER BY near the very end of its execution cycle:

SQL EXECUTION ORDER
 /* 
 * 1.FROM
 * 2.WHERE
 * 3.GROUP BY
 * 4.HAVING
 * 5.SELECT
 * 6.ORDER BY
 * 7.LIMIT
 */

Because it runs after the SELECTstatement, you can actually sort your data using column aliases that you created inside your SELECTclause.

D. Window frames

A window frame in SQL is a sub-selection of rows within a partition that determines exactly which records are included in a window function's calculation for the current row.

While PARTITION BY groups the data and ORDER BY sorts it, the window frame clause defines a sliding or fixed boundary around the current row. As SQL evaluates the query row-by-row, this "frame" shifts to look backward, forward, or at a fixed range of data.

1. The Anatomy of a Window Frame

A window frame is written at the very end of the OVER() clause using this basic syntax:

<window_function>() OVER (
    PARTITION BY column1
    ORDER BY column2
    [ROWS|RANGE|GROUPS] BETWEEN <start_boundary> AND <end_boundary>
)

The Boundaries

To set the frame, you mix and match these five boundary markers:
• UNBOUNDED PRECEDING: The very first row of the partition.
• N PRECEDING: N rows before the current row.
• CURRENT ROW: The row currently being processed.
• N FOLLOWING: N rows after the current row.
• UNBOUNDED FOLLOWING: The very last row of the partition.

The main frame options are:

ROWS: selects rows according to their physical positions.
RANGE: selects rows based on ordering values and their peers.
GROUPS: selects groups of rows with equal ordering values.

ROWS vs. RANGE vs. GROUPS

How SQL counts its boundaries depends on the modifier you choose

Modifier	Based On	Best Used For
`ROWS`	Physical row position. It counts individual rows regardless of whether they contain identical values.	Moving averages, such as calculating the average of the last three physical transactions.
`RANGE`	Logical values in the `ORDER BY` column. Rows with identical ordering values are treated as peers.	Time-series analysis, such as calculating values within a specified range of dates, regardless of the number of transactions.
`GROUPS`	Groups of rows with identical ordering values. Each unique group is treated as a single step.	Analyzing grouped data, particularly when handling tied values or transactions with identical ordering values.

Types of SQL window functions

A. Ranking Window Functions
These functions assign a sequential integer or statistical rank to each row within a designated partition based on a specific order.

ROW_NUMBER() - Assigns a unique, sequential integer to rows starting at 1. Never duplicates numbers if values match
RANK() - : Assigns ranks based on values. If duplicate values exist, they receive the same rank, and gaps are skipped for the next rank numbers (e.g., 1, 2, 2, 4).
DENSE_RANK() - Similar to RANK(), but it does not skip any numbers when there are duplicates
NTILE(N) - Divides an ordered partition into N roughly equal buckets or groups and assigns the bucket number to each row.
PERCENT_RANK() - Computes the relative rank of a row as a percentage value between 0 and 1.

B. Value / Offset Window Functions

Value (or offset) functions allow you to access data from a different row relative to the current row without using complex self-joins.
• LAG(): Accesses data from a previous row in the same result set, useful for calculating period-over-period growth.

• LEAD(): Accesses data from a subsequent row in the same result set.

• FIRST_VALUE(): Returns the first value in an ordered window frame.

• LAST_VALUE(): Returns the last value in an ordered window frame (note: usually requires altering the default window frame to look past the current row).

• NTH_VALUE(): Retrieves the value from the specific (N^{th}) row of the window frame.

C. Aggregate Window Functions

These are standard SQL aggregate functions applied using the OVER() clause. Instead of reducing the rows, they return a cumulative or moving calculation for each row.

• SUM(): Computes a cumulative running total when paired with an ORDER BY clause.

• AVG(): Calculates moving or partitioned averages.

• COUNT(): Calculates running or partitioned counts of rows.

• MIN() / MAX(): Identifies the lowest or highest value up to the current row or within the partition.

SQL Joins

What are SQL joins?

An SQL JOIN clause is used to combine rows from two or more tables based on a related column between them. It is fundamental to relational databases, allowing you to reassemble data that has been split up to avoid duplication.

A. INNER JOIN

The most common join type. It filters out any rows that do not strictly find a match in the opposing table. An INNER JOIN is useful when a report must include only records with valid matches in both tables.

Standard Syntax

SELECT table1.column_name, table2.column_name
FROM table1
INNER JOIN table2
ON table1.common_column = table2.common_column;

• INNER JOIN: Specifies the second table you want to merge.
• ON: Defines the condition or rule for matching rows, typically mapping a Primary Key from one table to a Foreign Key in another.

B. LEFT JOIN (or LEFT OUTER JOIN)

In SQL, a LEFT JOIN (or LEFT OUTER JOIN) returns all rows from the left table and the matching rows from the right table. If no match is found for a row in the right table, the database outputs NULL values for the right table's columns.

Standard Syntax


SELECT columns
FROM table_left
LEFT JOIN table_right 
  ON table_left.common_column = table_right.common_column;

When no match exists, columns from the right table contain NULL.

C. RIGHT JOIN

In SQL, a RIGHT JOIN (or RIGHT OUTER JOIN) is used to combine rows from two tables, returning all records from the right table and the matched records from the left table. If there is no match for a row from the right table, the result will contain NULL values for the columns of the left table.

Standard Syntax

SELECT columns
FROM table_left
RIGHT JOIN table_right 
ON table_left.common_column = table_right.common_column;

D. FULL OUTER JOIN

In SQL, a FULL OUTER JOIN (or simply FULL JOIN) returns all records from both tables, regardless of whether they have a matching row in the other table.

It essentially combines the functionality of a LEFT JOIN and a RIGHT JOIN. When a match is found based on the join condition, the rows are merged. If there is no match, the missing side is filled with NULLvalues

Basic Syntax

SELECT table1.column1, table2.column2
FROM table1
FULL OUTER JOIN table2 
ON table1.common_column = table2.common_column;

E. CROSS JOIN

An SQL CROSS JOIN combines every row from the first table with every row from the second table. It creates a Cartesian product of the two tables, meaning it generates all possible combinations of your data.
Unlike other joins (like INNER JOIN or LEFT JOIN), a CROSS JOIN does not require a join condition (no ON clause) because it is explicitly pairing everything together

Basic Syntax

You can write a cross join explicitly using the CROSS JOIN keyword, or implicitly using a comma.

SELECT columns
FROM table1
CROSS JOIN table2;

F. SELF JOIN

An SQL Self Join is a regular join where a table is joined with itself. There is no specific SELF JOIN keyword; instead, you reference the same table twice and use table aliases to give them distinct names. This tricks SQL into treating them as two separate tables so you can compare rows within the same dataset.

Basic Syntax

SELECT 
    t1.column_name, 
    t2.column_name
FROM 
    your_table AS t1
INNER JOIN 
    your_table AS t2 
ON 
    t1.common_column = t2.related_column;

A self-join is useful for hierarchical data, referral relationships, and comparing records within the same table.

The appropriate join depends on which records must remain in the output and whether unmatched records are important to the analysis.

Common Table Expressions (CTEs)

What is a CTE?

A Common Table Expression (CTE) is a temporary, named result set in SQL that you can reference within a single SELECT, INSERT, UPDATE, or DELETEstatement. Defined using the WITH keyword, CTEs act like virtual, query-scoped tables that disappear as soon as the query finishes executing. They are widely preferred over traditional subqueries because they heavily improve query readability, modularity, and code organization.

CTEs make complex queries easier to read by breaking them into logical steps.

The basic syntax is:

WITH cte_name AS (
    SELECT ...
)
SELECT *
FROM cte_name;

A CTE is not automatically a permanent table. It does not create a persistent database object.

Multiple CTE

Multiple CTEs allow a query to be divided into several named stages. Multiple CTEs are useful when each calculation depends on a previous step. They make a long query easier to maintain and troubleshoot.

SYNTAX STRUCTURE

WITH first_cte AS (
    SELECT department_id, AVG(salary) AS avg_sal
    FROM employees
    GROUP BY department_id
), -- <-- Separated by a comma

second_cte AS (
    SELECT department_id
    FROM first_cte -- <-- Can reference the preceding CTE!
    WHERE avg_sal > 75000
) -- <-- No comma before the main query

-- The final main statement (SELECT, INSERT, UPDATE, or DELETE)
SELECT e.employee_id, e.first_name, e.salary, e.department_id
FROM employees e
JOIN second_cte s ON e.department_id = s.department_id;

The Rules of Multiple CTEs

Single WITH Clause: You only write WITH before the first CTE. Do not repeat it for subsequent ones.
Comma-Separated: Place a comma between the closing parenthesis of one CTE and the name of the next. 3.** No Trailing Comma**: Do not put a comma between your final CTE definition and the main query.
Order Matters (No Forward References): A CTE can reference any CTE defined before it, but it cannot reference a CTE defined after it.

Recursive CTEs

A recursive CTE references its own result to process hierarchical or sequential data.

It typically contains:

An anchor query that identifies the starting rows.
A recursive query that finds the next related rows.
A UNION ALLcombining the anchor and recursive results.

Structural Anatomy of a Recursive CTE

A recursive CTE always consists of three fundamental components combined using the UNION ALL operator:

The Anchor Member (Base Query): The initial query that returns the starting rows of the recursion (e.g., the top-level boss or the number 1).
The Recursive Member: The query that references the CTE itself. It joins the table back to the previous iteration's results to find the next level of data.
The Termination Condition: A condition (usually a WHEREclause) in the recursive member that stops the query from executing infinitely.

Basic Syntax

WITH RECURSIVE cte_name AS (
    -- 1. Anchor Member
    SELECT columns FROM table WHERE condition 

    UNION ALL

    -- 2. Recursive Member + 3. Termination Condition
    SELECT t.columns 
    FROM table t
    INNER JOIN cte_name c ON t.parent_id = c.id
    WHERE [termination_condition]
)
SELECT * FROM cte_name;

The main advantage of a CTE is often clarity rather than guaranteed performance improvement.

SQL Subqueries

What is a subquery?

An SQL subquery is a SELECTstatement nested inside another SQL statement and enclosed in parentheses (). The outer statement (often called the main query) consumes the results generated by the inner subquery to perform filtering, calculations, or data transformations.

Subqueries can be nested inside SELECT, INSERT, UPDATE, or DELETE statements, and they can reside within clauses like WHERE, FROM, SELECT, and HAVING.

Types of Subqueries by Result Shape

The SQL optimizer categorizes subqueries based on the structural format of the data they return:

Scalar Subquery: Returns exactly one row and one column (a single value). It can step in anywhere a standard literal value or column expression is used.
Multi-Row (Column) Subquery: Returns a single column containing multiple rows of values. This type requires set membership operators like IN, ANY, or ALL in the outer query.
Table Subquery (Derived Table): Returns multiple columns and multiple rows. It acts as a temporary in-memory table and is primarily utilized within the FROM clause.
Non-correlated subqueries:A non-correlated subquery does not refer to columns from the outer query. It can be evaluated independently of the outer query's individual rows.
Correlated subqueries:A correlated subquery references a column from the outer query. Its result depends on the current outer row. A correlated subquery is useful when a condition must be evaluated relative to each outer record. Depending on the query and indexes, PostgreSQL may optimize it into an efficient execution plan; correlation does not automatically mean the query will be slow.
Nested subqueries:A nested subquery contains another subquery inside it. Nested subqueries are useful for multi-level comparisons, although a CTE may make the same logic easier to read.
Subqueries using IN: In SQL, a subquery using the IN operator allows you to filter the results of an outer query based on a dynamic list of values returned by an inner query. It acts as a powerful, flexible alternative to writing multiple OR conditions

Basic syntax

SELECT column_name(s)
FROM table_1
WHERE column_to_check IN (
    SELECT matching_column 
    FROM table_2 
    WHERE condition
);

Subqueries using EXISTS: In SQL, the EXISTS operator is a boolean condition used within a WHERE clause to check whether a subquery returns any rows at all. It evaluates to TRUE if the subquery returns at least one row, and FALSE if it returns no rows. Because EXISTSonly looks for the presence of a matching row, the database stops processing the subquery the exact moment it finds its first match. This makes it highly efficient, particularly when filtering large datasets

Basic Syntax

SELECT column_names
FROM table_1 AS t1
WHERE EXISTS (
    SELECT 1 
    FROM table_2 AS t2
    WHERE t2.id = t1.id
);

Subqueries using ANY: The SQL ANY operator is used in a WHERE or HAVING clause to compare a single value against a set of values returned by a multi-row subquery. The condition evaluates to TRUE if the comparison holds true for at least one value in the subquery's result set. In most SQL dialects (like MySQL, PostgreSQL, SQL Server, and Oracle), SOME is a direct alias for ANY, meaning they function exactly the same way.

Basic Syntax

SELECT column_names
FROM table_name
WHERE column_name comparison_operator ANY (subquery);

The comparison operator must be a standard operator: =, >, <, >=, <=, <>, or !=.

Subqueries using ALL: In SQL, the ALL operator is used to compare a single scalar value against every single value returned by a multi-row subquery. A condition using ALL evaluates to TRUE only if the comparison holds true for all records in the subquery result set. It must always be preceded by a standard comparison operator (like >, <, =, >=, <=, <>).

Syntax

SELECT column_names
FROM table_name
WHERE column_name comparison_operator ALL (subquery);

-Subqueries in the SELECT clause:A subquery in the SELECT clause (also known as a scalar subquery or a projection subquery) allows you to generate a dynamic, calculated column for every row returned by the outer query.
Instead of just pulling a standard column from a table, you insert an entirely separate SELECT statement directly into your main column list.

The Golden Rule

A subquery placed in the SELECT clause must return exactly one value (one row and one column) per outer row. If it returns multiple columns or multiple rows, the database engine will throw a syntax runtime error. If it returns no rows, it evaluates to NULL.

-Subqueries in the FROM clause:In SQL, a subquery in the FROM clause acts as a temporary, virtual table that the main query can use to select, filter, or join data. This is often called a derived table or an inline view.

The Two Golden Rules

When placing a nested SELECT statement inside your FROM clause, you must follow these two syntax rules:

Enclose it in parentheses: The entire subquery must live inside (...).
Assign an Alias: You must give the subquery a name (an alias) using the AS keyword (though AS is optional in some SQL dialects, the alias name itself is mandatory).

Syntax Structure

SELECT 
    derived_table.column1, 
    derived_table.column2
FROM (
    -- The Inner Subquery
    SELECT column1, column2 
    FROM original_table
    WHERE condition
) AS derived_table; -- Mandatory Alias

Subqueries in the WHERE clause: A subquery in a WHERE clause (also known as a nested query or inner query) is a SELECT statement embedded inside the filtering condition of an outer SQL query. It acts as a dynamic filter, calculating a temporary value or dataset that the outer query uses to evaluate rows. Depending on how many rows and columns the inner query returns, you must couple it with the appropriate SQL operator.

Best Practices When Using Advanced SQL

The following practices improve query accuracy, performance, and maintainability.

Understand the relationships between tables. Identify primary keys, foreign keys, and the correct join columns before writing a complex query.
Prevent duplicate counting. One-to-many joins can multiply rows. Aggregate at the appropriate level before joining when necessary.
Use meaningful aliases. Aliases such as p, pay, and doc make queries easier to read when used consistently.
Choose the correct window ordering. If multiple rows have the same date or value, include a unique secondary sort column when deterministic results are required.
Specify window frames explicitly when necessary. This is especially important for running totals and functions such as LAST_VALUE().
Handle NULL values. Remember that SUM() over no input rows can return NULL, and COUNT(column) ignores NULL values. Use COALESCE() when a zero is appropriate.
Choose joins based on reporting requirements. An inner join excludes unmatched records, while an outer join preserves them.
Use CTEs to improve readability. Break complex queries into understandable stages instead of building one unnecessarily complicated statement.
Check query performance. Use PostgreSQL's EXPLAIN and, where appropriate, EXPLAIN ANALYZE to understand execution plans and identify bottlenecks.
Protect patient information. Hospital databases contain confidential information. Access should be authorized, and reports should expose only the data necessary for their purpose.
Validate results against the source data. Compare totals, row counts, and sample records to ensure that a query has not excluded or duplicated information.
Use appropriate indexes. Indexes on frequently joined keys and filtered columns can improve performance, although their value depends on the query and the data.

Conclusion

Window functions, joins, Common Table Expressions, and subqueries are important SQL techniques for solving complex data problems.

Window functions allow analysts to rank records, compare payments over time, and calculate totals while preserving individual rows. Joins combine related information from different tables, enabling meaningful hospital reports. CTEs organize complex calculations into readable stages and support recursive analysis of hierarchical data. Subqueries provide a flexible way to filter, compare, and calculate values based on other query results.
In a hospital environment, these techniques can support patient payment analysis, appointment monitoring, doctor performance reporting, departmental planning, and financial reconciliation.

The most important skill is not memorizing SQL syntax. It is understanding the business question, identifying the correct relationships between tables, selecting the appropriate SQL technique, and verifying that the results accurately represent the underlying data.

By practising these techniques in PostgreSQL and applying them to a hospital dataset, SQL learners can develop the analytical skills needed to transform raw records into reliable and useful information for decision-making.

“The goal is to turn data into information, and information into insight.”
— Carly Fiorina, former CEO of Hewlett-Packard

Power BI Technical Article: Data Modelling, Relationships & Joins

Brayan Wafula — Mon, 19 Oct 2026 06:27:43 +0000

“The goal is to turn data into information, and information into insight.” — Carly Fiorina.

Introduction

Power BI is much more than a visualization tool—it is a complete Business Intelligence (BI) platform that allows organizations to transform raw data into meaningful insights. However, the quality of reports depends heavily on how the underlying data is structured. A poorly designed model leads to slow reports, confusing DAX formulas, duplicated data, and difficult maintenance. A well-designed model, on the other hand, produces fast, scalable, and reliable analytics.

This article explains the principles of data modelling, fact and dimension tables, relationships, filter propagation, joins in Power Query, and concludes with the recommended design for real-world BI projects.

Data Modelling in Power BI

What is data modelling?

Data modeling in Power BI is the process of structuring, shaping, and connecting your data tables in the Model Data with Power BI tool to build a reliable semantic model for fast and accurate reporting. Instead of keeping everything in one spreadsheet, data is divided into logical tables that represent business entities such as customers, products, dates, and sales.

A strong data model improves:

Performance by reducing unnecessary data duplication.
DAX calculations because measures become simpler.
Scalability when new data is added.
Maintainability since tables have clear responsibilities.
Report accuracy through consistent filtering.

A. Flat Table Schema

A flat table schema (also known as "One Big Table" or an un-pivoted/denormalized dataset) is a data modeling structure where all transactional data, attributes, and descriptive fields are consolidated into a single, wide table.A flat table stores every piece of information inside one large table.

Structure

Everything—customer names, products, locations, dates, and sales values—exists in one table.

Advantages

Easy for beginners.
Simple to import from Excel.
No relationship configuration required.

Disadvantages

Large file size.
Repeated customer and product information.
Slower performance.
Difficult to maintain.

Best used when: Small datasets with limited reporting needs.

B. Star Schema

A Star Schema is a data modeling approach where a central fact table connects directly to multiple surrounding dimension tables

Structure

One central fact table.
Multiple surrounding dimensions.
Relationships are usually one-to-many.

Advantages

Excellent performance.
Simple DAX.
Easy filtering.
Highly scalable.

Disadvantages

Requires planning.
Slightly more complex than a flat table.

Best used when: Sales, finance, inventory, healthcare, education, and enterprise BI.

C. Snowflake Schema

a data-modeling structure where dimension tables are normalized and split into multiple related sub-tables

Structure

Instead of storing category inside DimProduct, it is moved into DimCategory.

Advantages

Reduces redundancy.
Better normalized database design.

Disadvantages

More tables.
More complex relationships.
Harder DAX and navigation.

Best used when: Very large enterprise warehouse environments.

2. Fact Tables and Dimension Tables

Understanding the operational dichotomy between Fact and Dimension tables is critical to structuring an efficient Star Schema

Fact Tables

Fact tables store the quantitative metrics generated by operational business events. They capture the observations, measurements, or numerical transactions of a business process.

Contents: Numeric values (Revenue, Quantity, Cost), timestamps, and alphanumeric Foreign Keys.
Grain/Granularity:This defines what an individual row in the table represents. For example, a row in FactSales might represent an individual item scan on a point-of-sale receipt (High Granularity), or it could represent aggregated daily sales per store (Low Granularity). Enforcing a consistent grain across a fact table is vital for valid data aggregation.
Examples: FactSales, FactOrder, FactTransactions.

Dimension Tables

Dimension tables store the descriptive attributes that provide context to your business metrics. They answer the "who, what, where, when, and why" questions of an analytical query.

Contents: Text-heavy rows containing descriptive context, hierarchies, and categorizations used for slicing, dicing, grouping, and filtering reports.
Examples: DimCustomer (Name, Segment, Email), DimProduct (Color, Brand, Category), DimDate (Year, Quarter, Month), DimLocation (City, State, Zip).

3. Relationships in Power BI

Relationships in Power BI are logical links between tables based on a shared key or column. They allow Power BI to combine data, calculate metrics, and propagate filters across multiple tables without needing to physically merge them.
A relationship tells Power BI how tables are connected. Without relationships, visuals from different tables cannot interact correctly.

Types of Relationships

• One-to-Many (1:) / Many-to-One (:1): The standard and recommended configuration. One table contains unique values (e.g., a Date or Customer dimension table), while the related table contains multiple matching records (e.g., a Sales or Orders fact table).

•** One-to-One (1:1)**: Both tables contain unique values for the key. Best practice is usually to merge these tables in Power Query instead.

• Many-to-Many (*:): Both tables contain multiple records for the same key. This often causes ambiguous filtering, duplicated totals, and performance issues unless managed with a bridge table.

Primary Key and Foreign Key

Primary Key is a column in a table where every single row has a unique value with no duplicates or NULL values while a foreign key is a column in a table that contains repeated values pointing back to a primary key in another table.

Primary Key	Foreign Key
Unique values	Repeated values
Located in dimension	Located in fact
Identifies one record	Links to dimension

the table above shows a comparison of primary and foreign keys

Active vs Inactive Relationships

A table may have multiple possible relationships but Only one relationship can be active. The inactive relationship can be activated inside DAX using USERELATIONSHIP() when needed.

4. Filter Direction

Relationships also determine how filters move through the model.Cross-filter direction in Power BI determines the pathway that filter contexts take when moving between related tables in a data model.

Types of Filter Directions

• Single Direction (Default): Filters flow in one direction only—from the "one" side (lookup/dimension table) to the "many" side (fact table). For example, selecting a product in a Product table filters the Sales table, but filtering the Sales table has no effect on the Product table.

• Both / Bi-Directional: Filters flow in both directions between the two tables. A filter applied to either table affects the other, allowing data in fact tables to filter related dimension tables.

5. Joins in Power Query

A join combines tables during data preparation using Merge Queries.

Joins in Power Query (Power BI) are performed via the Merge Queries feature, which combines two tables into a single table based on one or more common key columns.

Types of Joins in Power Query

Power Query supports six join types when merging tables:

•_ Left Outer (Default)_: All rows from the left table and matching from the right; unmatched right rows show as null.

•_ Right Outer_: All rows from the right table and matching from the left; unmatched left rows show as null.

• Full Outer: All rows from both tables, filling in null for missing matches.

• Inner: Only rows with matching values in both tables.

• Left Anti: Only rows existing exclusively in the left table.

• Right Anti: Only rows existing exclusively in the right table.

How to Do a Join in Power Query

Launch the Power Query Editor via Transform Data in Power BI Desktop.
Click Merge Queries (or Merge Queries as New) on the Home ribbon.
Select the primary table and the secondary table from the grids. 4._ Select the matching key column(s) in both tables._
Select the desired Join kind and click OK, then use the Expand icon to add columns from the related table.

6. Power Query Joins vs Power BI Relationships

Although both connect tables, they serve different purposes.

Merge (Power Query)	Relationship (Model)
Physically combines data	Keeps tables separate
Happens during data loading	Happens after loading
Creates new columns	Creates filter paths
Used in ETL	Used for analytics

A relationship allows Power BI to display Customer Name without duplicating data.
This is the preferred BI approach because:

Less redundancy
Smaller model
Faster refresh
Better scalability

Excessive merging creates wide tables similar to flat files, reducing the benefits of dimensional modelling.

7. Recommended Power BI Model

For most business intelligence projects, I recommend the Star Schema with one-to-many relationships and single-direction filter propagation.

Why the Star Schema is Recommended

• Smaller Size & Compression: Separating data into centralized fact tables and surrounding dimension tables reduces storage footprint and improves memory optimization.

• Optimized DAX Performance: Queries run faster because relationship paths are predictable and filter propagation is clean.

• Easier Management: Business logic, metrics, and report modifications are much simpler to maintain as your model scales.

Recommended Design

Central FactSales table
Dimensions: DimCustomer, DimProduct, DimDate, DimLocation
One-to-Many relationships
Single-direction filtering
Use Power Query merges only during data cleansing or lookup enrichment

This design balances performance, clarity, flexibility, and long-term maintainability, making it the industry standard for professional Power BI solutions.

Conclusion

Effective Power BI reporting begins with an effective data model. Understanding star schemas, fact and dimension tables, relationships, filter direction, and Power Query joins enables analysts to build models that are accurate, scalable, and easy to maintain. While flat tables may suit small datasets and snowflake schemas benefit highly normalized warehouses, the star schema remains the best choice for most analytical solutions because it delivers excellent performance, simpler DAX, and cleaner report design.

Excel: The Foundation of Every Data Scientist's Journey

Brayan Wafula — Sun, 30 Aug 2026 07:57:32 +0000

Introduction

In todays times, raw data is as precious if not more precious than oil, but it is rarely useful alone. it is a foundational tool in the data science field serving as as a primary interface for data organization, cleaning and manipulation.

excel is not just a simple tool as it appears on the surface, it is a unique tool that can be used for various uses when it comes into contact with raw data. It allows users to organize datasets, identify errors, remove duplicates, standardize values, perform calculations and clean unorganized data(dirty data). Once mastered excel becomes a skill on its own hence these skill are the foundation of professional data analytics.

With Raw data being a gold mine across every department in each institution a tool is needed to help facilitate the organization and sorting of the data hence, this is where excel comes in. So we ask ourselves:

What is Excel?

Excel is a tool created by Microsoft that help in simplifying data storage, cleaning and manipulation. Excel is an accumulation of one or more related worksheet. The worksheet is made up of different parts that comprise of it and some of them are:

Quick Access Toolbar: A small, customizable group of buttons for frequent actions like save, undo and redo
Ribbon: The main toolbar containing organized command tabs like Home, Insert, and Page Layout.
Name box: Located on the left below the ribbon, it shows the address of your active cell.
Formular Bar: The long input line next to the Name Box used to view, type or edit cell formulas and data.
Active cell: The current, individual box where data, text or formulas are being entered.
Column: Vertical blocks of cells identified by letters running across the top (A,B,C...).
Row: Horizontal blocks of cell identified by numbers running down the left side(1,2,3...).
Worksheet Tab: Located at the bottom left, letting you switch between or add new worksheets.
Title Bar: The very top bar displaying the current file name and software controls.

Below is an image depicting an excel worksheet and some of its components:-

Common Uses for Excel

Data Entry and Management: Store lists, contact information, inventory, or raw numbers in an organized grid.
Calculations and Formulas: Use basic math(addition, subtraction,...) or built-in functions(like SUM or AVERAGE) to process numbers automatically.
Budgeting and Finance: Track monthly expenses, build financial forecast, and manage business or personal accounts.
Data Analysis: Sort, filter and summarize large data sets using pivot tables and conditional formatting.
Project Management: Track timelines, task statuses, team resources, and project costs.

Features of Excel

Excel as a versatile tool is composed of different features that help it execute various commands.
Some features are:-

Conditional Formatting: Used to alter, filter and highlight important information.
Charts and Graphs: Used as a visual aid in data representation.
Worksheets: Used in data organization.
Formulas and functions: Used in mathematical calculations. Some example of formulas and functions are;

=SUM- Use for addition
=AVERAGE- Used to find the average number in a data set
=MAX- Used to find highest number
=MIN- Used to find lowest number
=COUNT- Used to count number of entries.

The following is an example of formulas and functions as applied in excel;

Formula =K3+I9 =B7+F8
Function =SUM(I6+F4) =AVERAGE(A4:F4)

Data Manipulation

This is the changing, organizing, structuring, and cleaning or raw data to make it easier to analyze.
Given a small dataset here is how data manipulation is done:
figure 1: An excel worksheet containing raw data with common data issues.

some of the problems it has are extra spaces, different letter cases, missing values, duplicate rows and mixed data formats. A data analyst's first responsibility is to correct these issues.

Data Cleaning

This is the process of finding and fixing errors, duplicates, and missing values in a dataset to make it accurate and ready for analysis.

Text Cleaning with Excel Functions

Excel provides several powerful functions that aid in text cleaning.

TRIM()- Used to remove unnecessary spaces. Formula:
=TRIM(B2)

PROPER()- Converts names into proper capitalization. Formula: =PROPER(B2)

UPPER() and LOWER()- Useful for standardizing codes or emails

=UPPER(D2)
=LOWER(F2)

Handling Missing values

Missing information is detrimental to data analysis and results yielded. There are several ways to fill missing values:

Manual correction using Known information.
Fill Down for repeated values
using find and replace
IF formulas for conditional replacements.

=IF(D2="", "Unknown", D2)

replaces blank cells with unknown.

Removing Duplicate Records

Duplicate are repeating records on the same worksheet. They may lead to incorrect or misleading reports.
go to the ribbon go to the Data Tab then Remove duplicates. After removal every entry appears only once.

Formatting Dates

Dates are important for reports, dashboards and trend analysis. Excel stores dates as serial numbers internally, allowing calculations using functions such as:

- TODAY(): Returns the current date, which updates automatically every day.

- NOW(): Returns both the current date and time.

- DATE(year, month, day): Creates a valid Excel date by combining individual year, month, and day values.

- YEAR, MONTH, DAY: Extracts the specific year, month number (1–12), or day number (1–31) from a given date.

Sorting and Filtering Data

Sorting helps organize information, while filtering isolates specific records. Examples;

Highest to smallest
Smallest to Highest
Newest to Oldest
Oldest to Newest

Creating Calculated Columns

A calculated column generated new information from existing fields.

Final Cleaned Dataset

After applying all cleaning techniques, the dataset becomes reliable and analysis ready.

The cleaned version can now be used for creating PivotTables, dashboards, charts and further statistical analysis.

Data Cleaning in Analytics

Data cleaning takes the most part of an analyst's time because poor-quality data leads to major issues(inaccurate forecast, wrong reports, misleading visualization,...).

Excel fundamentals acts as skills that can be directly transferred to other professional tools such as Power BI, SQL, Python,.... The logic and basics remain the same: identify errors, standardize data, validate quality, and prepare datasets for meaningful analysis.

Key Excel functions learned

Function	Purpose
`TRIM()`	Remove extra spaces
`PROPER()`	Proper capitalization
`UPPER()`	Convert to uppercase
`LOWER()`	Convert to lowercase
`IF()`	Replace missing values conditionally
`TODAY()`	Calculate date differences
`*`	Calculate total revenue

Excel is an excellent starting point for anyone entering data science and analytics. Although advanced tools like Python and SQL offer greater automation, Excel remains one of the most widely used applications for data preparation in businesses around the world.

These are not merely spreadsheet skills—they are essential analytical practices that ensure data is accurate, consistent, and ready for decision-making.

Career Benefits

Excel accelerates career growth by boosting daily productivity, increasing earning potential and making the user a more competitive candidate across multiple industries.

Excel being a universal accepted application, it creates a competitive edge among users and it also improves efficiency and automation. Hence:

Save Time: Formula and Functions update data automatically, removing the need for manual recalculations.
Automate tasks: Macros handle repetitive formatting and report generation, reducing human error.
Better Organization: Large and small datasets stay clean, structured, and easy to filter in a single workbook.

Access to Specialized Roles

Finance and Accounting: Mastery enables precise financial modeling, budgeting and forecasting.
Project Management: Spreadsheets help in tracking timelines, allocate resources and document progress.
Data Analysis: Power Query and Dashboards turn raw data into strategic business insight.
Education: Excel help in storing students, teachers, staffs information to ease data storage, manipulation, retrieval and representation.

Conclusion

As a beginner, mastering these Week 1 concepts provides a strong foundation for more advanced topics such as PivotTables, dashboards, Power BI, SQL, and machine learning. The quality of every analysis begins with the quality of the data, and Excel is where that journey starts.

Mastering Excel itself is a skill that can be used to gain different opportunities in different areas and can also be monetized as a skill on its own.

"The goal is to turn data into information, and information into insight.”
— Carly Fiorina, former CEO of Hewlett-Packard.

Understanding the Git Workflow: A Data Science Version Control Practical Guide

Brayan Wafula — Sun, 23 Aug 2026 08:52:59 +0000

Introduction

Data science projects are usually a collaboration of different intertwined parts that are joined together to give a complete project and not just written code, it includes a dataset, python scripts, Sql queries, visualizations and documentation. As changes are done on these files tracking the different modifications becomes important. This is where Git becomes one of the most valuable tools for a data scientist.

What is Git, Git Bash and GitHub?

Git is a software developed to help the user to keep track of changes to their code files while git bash is the command-line window akin to the windows terminal command prompt that is used by users to type out their commands. While, GitHub is an online (cloud-based) platform that creates an environment for users to work together on projects and it also used to host computer code.

Rather than Git being understood as simply a way to pass code from user to GitHub, it is better thought of as a library that keeps records of the history of your project from start to finish. Git is used to manage the project locally on the computer while GitHub stores it online. It is also understood or can also be called a version control system that allows the user to:

Experiment freely
Return to previous versions
Collaborate with others
Maintain an organized workflow

The image is an example of a Git Bash terminal:

The image below is an example of a GitHub platform:

Uses of Git, Git Bash and GitHub.

Git being a version control system is used for various reasons. some of it uses are as listed below:

Tracking file history - Using commits(screenshots) it allows the user to return to previous versions in case something happens, tracks bugs an changes over time.
Team collaboration - It merges work done by different people and keep track of which changes were done by which user.
Offline local work - Because Git is well distributed, every user has a copy of the the project being worked on their personal computer.
Parallel Development - It works on different things at the same time without interfering with the main codebase.

Uses of Git Bash are as follows:

Running Git commands - Serves as a terminal that is used to execute commands like git init, git commit, git push, git pull
Enabling Unix/Linux commands on windows - Windows having its own inbuilt terminal (Power Shell and Command Prompt) it uses different syntaxes hence Git Bash brings standard Unix utilities like cd, ls, mkdir, pwd just to name a few directly to windows.
Running shell scripts - It enables users to write and run automated automation tasks using standard Bash script files(.sh) on a windows machine.
Universal tutorial compatibility - It allows Windows users to follow along with Linux on macOs terminal instructions exactly.

Uses of GitHub?
It being a cloud based platform and it having many features it is used for the following :

Repositories - Also known as Repo(s), It is a storage folder where project code and history are found.
Version Control - A system that saves snapshots of code so you undo mistakes or compare old versions.
Branching & Merging - Creating a separate copy of code to test a new idea and then recombining it with the main project if it works.
Pull Requests - A request to review and merge code changes into a shared project.

Basic/Most Used Bash Commands

ls - Used to list files and folders
cd - Change directory
pwd Find the path of the current directory
mkdir - Make a directory
mv - Move files and folders
rm - remove files and folders

The Git Workflow

The Git workflow is a recipe or recommendation on how to use Git to manage code changes in a consistent, collaborative way.
It has four main stages, namely:

Working Directory
It is where the user actively works on their project. It is the computer local space where you create, edit and delete files. Changes made here aren't yet tracked by Git.
Staging Area(Index)
Think of this as a preparation space. When the user has made changes they'd like to include in their next commit, they "stage" those changes, adding them to the staging area. Only staged chances make it to the next commit.
Creating a Commit
A commit is a like a checkpoint in the user's project's history. Every commit usually describes a noteworthy change.

Create a commit:
git commit -m "Add patient age"

Commit as a checkpoint makes it easier to return to an earlier version if the new test fails or performs worse.

To view previous commits use:
git log --oneline.

GitHub: The Remote Repository A commit saves history locally but GitHub stores a copy online so it can be backed up and shared.

After creating commits:
git push origin main
Uploads your project to GitHub.

If another team member adds new work, download it using:
git pull origin main

A common workflow is:

git pull origin main
git add .
git commit -m "Add patient age"
git push origin main

Essential Git Commands and Their Use Cases

git add
It stages changes, preparing them for the next commit.

Usage: git add <file> stages a specific file; git add . stages all changes in the current directory.
Example: After editing index.html, run git add index.html to stage the changes.

git status
It displays the state of your working directory and staging area, helping track which changes are staged, unstaged, or untracked.

Usage: Simply run git status
Example: After modifying index.html, git status will show that the file has been modified and is ready to be staged.

git commit
It records your your staged changes into the local repository, creatin a snapshot of your project at that point in time.

Usage: git commit -m "Your commit message
Example: After staging your changes, run git commit -m "Fix header alignment" to commit them with a descriptive message.

git reset
It undoes changes by moving the HEAD pointer and, optionally, modifying the index and working directory.

Usage: git reset HEAD <file> unstages a specific file; git reset --hard HEAD~1 resets the repository to the previous commit, discarding changes.
Example: If you accidentally staged style.css, run git reset Head style.css to unstage it.
Caution: Using git reset --hard can permanently delete changes.

git revert
It creates a new commit that undoes the changes changes of a specified commit, preserving the project history.

Usage: git revert <commit>
Example: To undo the effects of a problematic commit, first identify its commit hash using git log, then run git revert <commit-harsh>.
Note: It is safe for undoing changes in shared repositories, as it doesn't alter the commit history.

Visualization of the Git Workflow

[Start]
   |
   v
[Working Directory]
   |
   v
[git status] ---> [git add <file>] ---> [git commit -m "message"] ---> [git push origin main]
   |
   v
[Staging Area]
   |
   v
[Local Repository]
   |
   v
[Remote Repository]

A Practical Workflow

Create a hospital data model.
Step 1: Start project

git init
Initialize Git inside the project folder.

Step 2: Check changes

git status
Git identifies modified datasets and scripts.

Step 3: Stage completed work

git add scripts/
git add README.md

Step 4: Save a checkpoint

git commit -m "Add patients age"

Step 5: Upload to GitHub

git push origin main

Importance of Git in data science

It provides several practical benefits beyond version control.

Reproducibility:
Every step and model update is recorded.
Experiment Tracking:
Compare different feature engineering or model versions.
Collaboration:
Multiple analysts can contribute without overwriting each other's work.
Backup:
Project remain safe even if a local computer fails.
Documentation:
Commit history tells the story of how the project evolved

Essential Git commands

git init -Initialize a Git repository
git status - Check modified and staged files
git add - Stage all changes
git add filename - Stage a specific file
git commit -m "message" - Save a project checkpoint
git log --oneline - View commit history
git pull origin main - Download latest changes
git push origin main - Upload commits to GitHub
.gitignore - It is used to stop certain files from committing automatically. Should automatically be in a repo when creating a project.

Tips

- Use git status frequently to understand what's changed.
- Stage only what you what to commit. git add -p enables selective staging of file changes.
- Write clear commit message to document why changes were made.
- Commit often, but meaningful.

Conclusion

The Git workflow is a simple but powerful process that moves changes through stages ( working Directory --> Staging Area --> Commit --> GitHub) for collaboration and backup. For data scientists, this workflow ensures the experiment remains organized, reproducible, and easy to manage throughout the project lifecycle.

Mastering these four stages builds a strong foundation for collaborative analytics, machine learning development, and and professional software engineering practices in data science.

As i finish let me leave you with

"The journey of a thousand miles begins with a single step."
-Lao Tzu