DEV Community: mary thandy

A Guide to SQL Joins and Window Functions

mary thandy — Thu, 05 Mar 2026 12:23:23 +0000

Data, in its raw state, is seldom found in a single neat table. The reality of working in analytics or data engineering is needing to synthesize information from disjointed sources and compute metrics that rely on understanding relationships and patterns across a dataset. This is where SQL excels. If you want to move beyond simple filtering and aggregation, you must master two foundational tools: Joins and Window Functions.

Understanding SQL Joins

Joins are the mechanism we use to reunite this data into meaningful results. A join connects rows from two or more tables based on a related column between them.
Joins are how you put the puzzle pieces back together to get a full picture.

Below are the most common types of joins:

A. INNER JOIN (The Matchmaker)
This is the most common join. It only returns rows where there is a matching value in both tables. If a row doesn't have a match, it is excluded.
Use Case:Showing a list of customers who have actually placed orders.
B. LEFT JOIN (The "Don't Leave Me" Join)
This returns all rows from the left table, and the matched rows from the right table. If there is no match on the right, you get NULL values.
Use Case: Finding all customers, including those who haven't bought anything yet (great for identifying "cold" leads).
C. RIGHT JOIN
The exact opposite of a Left Join. It returns all rows from the right table.
Pro Tip: Most developers just use LEFT JOIN and switch the table order to keep the code easier to read.
D. FULL OUTER JOIN (The "Everyone Invited" Join)
Returns all rows when there is a match in either the left or right table. If there’s no match, the missing side gets a NULL.

Use Case: Merging two lists (like a list of local leads and a list of international leads) to see everyone at once.

Basic Syntax

To join tables, you need a "Key" (usually an ID) that exists in both tables.

SELECT 
    tableA.column_name, 
    tableB.column_name
FROM TableA
INNER JOIN TableB 
    ON TableA.common_id = TableB.common_id;

Real-World Example

Imagine you have a Students table and a Clubs table.

Students Table
| id | name |
|:---|:---|
| 1 | Alice |
| 2 | Bob |
| 3 | Charlie |

Clubs Table
| student_id | club_name |
|:---|:---|
| 1 | Chess |
| 2 | Music |
| 5 | Drama |

Which Join should you use?

"Who is in a club?" → INNER JOIN (Alice and Bob).

"List all students and their clubs (even if they have none)?" → LEFT JOIN (Alice, Bob, and Charlie with a NULL club).

"Show all clubs, even if the student ID doesn't exist in our table?" → RIGHT JOIN (Chess, Music, and Drama with a NULL name).

Introducing Window Functions

A window function performs a calculation across a set of table rows that are somehow related to the current row. Critically, it does not cause rows to become grouped into a single output row.
Standard SQL aggregation (GROUP BY) is powerful, but it comes with a major limitation: it reduces multiple rows into a single summary row.

Key Window Function Syntax

The definitive keyword is OVER, which defines the "window" or the set of rows the function will operate on. It typically has two optional but powerful clauses:

PARTITION BY: This divides the window into groups (e.g., partition by department or by region). The function runs independently on each partition.
ORDER BY: This defines the logical sequence of rows within each partition.

Common Categories of Window Functions

Ranking Functions: Ideal for finding the "top" items within a category. Key functions include ROW_NUMBER() (sequential number, no ties), RANK() (numbers with ties that skip the next number), and DENSE_RANK() (numbers with ties that do not skip numbers).
Aggregate Window Functions: You can use standard aggregates like SUM(), AVG(), or COUNT() within the OVER()clause to calculate things like running totals or moving averages.
Value-based Functions: These are used to compare the current row to surrounding rows.LAG()(looks at a previous row) and LEAD() (looks at a following row) are essential for identifying changes over time, like calculating day-over-day growth.

Example :You want to see each sale and the total accumulated sales so far.

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

Summary

Here is a summary of the most crucial concepts from the article:

Joins are for Relationship Management: Joins are essential because relational databases are normalized. They allow you to reconstruct data across different tables based on common columns.
Left Join is a Key Tool: The LEFT JOIN is one of the most practical and frequently used joins, specifically for identifying mismatches or ensuring all records in a primary table are retained.
Window Functions Prescribe Rows: The fundamental difference between standard aggregation (GROUP BY)and window functions is that window functions retain the individual rows.
The OVER() Clause is the 'Window': The OVER() clause defines the specific set of rows a function operates on, often customized with PARTITION BY (to define subgroups) and ORDER BY (to define the order within subgroups).
Ranking and Change-Over-Time Analysis: Window functions make calculations like ranking (DENSE_RANK()), running totals (SUM()), and day-over-day growth (LAG()) intuitive and direct.

“How Analysts Translate Messy Data into Action Using Power BI”

mary thandy — Tue, 10 Feb 2026 17:40:03 +0000

The role of the Data Analyst is to take the technical data of Power BI and arrange it into a story that helps in decision making.A messy data foundation forces leadership to rely on instinct rather than insight, this can lead to the business collapsing.

Steps in cleaning a messy data

Power Query

It is a data transformation and data preparation engine. It allows you to connect to hundreds of different data sources eg Excel and clean that data.

What power query does:

Removing null values, splitting columns (e.g., "City, State" into two columns), changing data types, and filtering out unnecessary rows.

Every change you make is recorded in the Applied Steps pane on the right. Power Query doesn't "save over" your data; it just remembers the steps to clean it.

Once the data is polished and structured, you click "Close & Apply." Power Query then sends the clean data into the Power BI "Data Model," where it is ready for DAX measures and visualization.

DAX & The Data Model

DAX stands for Data Analysis Expressions
Its job is to analyze the data already in the room.

Its looks at the Physical Tables Power Query created and calculates new values ( using New Measures) based on how you interact with the report.
It also creates Virtual Values These don't exist as physical columns in your data; they are calculated on the fly.
Every DAX measure follows a specific structure.

_Measure Name = FUNCTION{'Table'[Column])_

The Name: Always wrap it in brackets [Total Sales] when referencing it elsewhere.
The Function: Common ones include SUM, AVERAGE, COUNTROWS, or the of DAX: CALCULATE.
The Reference: DAX likes specific addresses—'Sales'[Revenue] tells it exactly which table and column to look at.

Conclusion

Power BI is the bridge between "the messy data" and "the optimized business". The true skill of an analyst isn't just knowing how to write a CALCULATE function; it’s knowing which business question that function is answering.

Mastering Schema and Data Modelling in Power BI

mary thandy — Mon, 02 Feb 2026 13:14:34 +0000

In the world of Power BI, a stunning dashboard is only as good as the architecture supporting it. While it is tempting to jump straight into creating vibrant charts and complex maps, the true magic happens behind the scenes in the Data Model. Designing a robust schema—the blueprint of how your data interacts—is the most critical step in building any professional report. Without a solid foundation, even the best-looking visuals can produce sluggish performance and, more dangerously, inaccurate insights. This article explores the core principles of data modelling, from the efficiency of the Star Schema to the vital roles of Fact and Dimension tables, ensuring your next Power BI project is both lightning-fast and reliably precise.

The Foundations: Fact vs. Dimension Tables

Before you can build a schema, you must categorize your data into two distinct buckets:

1. Fact Tables (The "What Happened?")**

A Fact table sits at the center of your model. It records specific events or transactions that occur at a specific point in time.

Data Type: Usually contains quantitative, numeric data (measures) like sales amount, quantity sold, or temperature readings.
Structure: These tables are typically very "long"—they can contain millions or even billions of rows—but they are "skinny," consisting mostly of numbers and "Foreign Keys" (ID numbers that link to other tables).
Example: A Sales table that lists every single receipt generated in a store.

2. Dimension Tables (The "Who, Where, and When?")

Dimension tables provide the context for your facts. They describe the entities involved in the business process.

Data Type: Contains qualitative, descriptive data (attributes) like product names, customer addresses, or date hierarchies (Year, Month, Quarter).
Structure: These tables are usually "wide" because they contain many columns of descriptive text, but they are "short" compared to fact tables (e.g., you might have 10 million sales, but only 500 unique products).
Example: A Product table that lists the name, colour, category, and brand of everything you sell.

Why the Distinction Matters

In Power BI, you generally want to filter by your Dimensions and calculate your Facts. For example, you would use a Product Name from a Dimension table to filter your Total Revenue from a Fact table. Mixing these two up is a leading cause of messy models and broken calculations.

The Gold Standard: The Star Schema

The Star Schema gets its name from its physical appearance in the Model View. It consists of a single Fact table at the center, surrounded by multiple Dimension tables that radiate outward like the points of a star.

Why Power BI Loves the Star Schema

Power BI’s data engine is specifically optimized for this structure. Here is why it works so well:

Simplified DAX: When your data is organized into a star, writing measures becomes much easier. The relationships are direct, reducing the need for complex "workaround" formulas.
Fast Performance: Because the "filters" only have to travel one step from the Dimension table to the Fact table, Power BI can calculate results almost instantaneously.
Usability: For the end-user, the model is intuitive. They know to grab their "categories" from the outer tables and their "numbers" from the center.

The Snowflake Schema:

Sometimes, you will encounter a Snowflake Schema. This happens when a Dimension table is broken down into further sub-dimensions (for example, a Product table that connects to a separate Category table, which then connects to a Department table).

While "snowflaking" can save a tiny amount of storage space by reducing redundant text, it generally makes Power BI models _slower _and more difficult to navigate. Whenever possible, it is better to "flatten" those sub-dimensions back into a single, wide Product table to maintain a clean Star Schema.
Snowflakes Schema also creates complex relationship paths and the "Fields" pane harder for users to navigate.

Why Performance Matter

Good modelling isn't just about being "neat." It directly impacts the two most important things in Power BI:

DAX Efficiency: In a Star Schema, the "filter context" is clear. This means your measures (like Total Sales or Year-over-Year Growth) will calculate faster because the engine doesn't have to jump through multiple "Snowflaked" hoops.
Accurate Reporting: Incorrect relationships often lead to "Cartesian products," where the model guesses at connections and returns wildly inflated numbers.

Summary Table

Feature	Star Schema	Snowflake Schema
Performance	High (Optimized for Power BI)	Lower (More joins required)
Maintenance	Easier / Simpler DAX	More complex
User Experience	Intuitive	Can be confusing

Conclusion

As technology evolves, the tools we use to visualize data will continue to change, but the principles of data modelling remain constant. Mastery of the Star Schema is the ultimate "cheat code" for any Power BI developer. By separating your nouns (Dimensions) from your verbs (Facts) and maintaining clean, one-to-way relationships, you ensure that your reports are not just beautiful, but accurate, fast, and scalable.

I hope this breakdown helps you build your next model with confidence. I’d love to hear your thoughts—let me know in the comments how you approach your data modelling!

Understanding Git and GitHub for Beginners

mary thandy — Mon, 02 Feb 2026 11:00:05 +0000

Before diving into modern software development, it is essential to master the tools that power global collaboration and code management. Whether you are beginning your programming journey or curious about how professional teams build real-world software, Git and GitHub are foundational skills you simply cannot overlook.

What is Git

Git is a version control system/software on your computer that tracks every change that is used to track changes. This tool is used mostly by software developers, its helps them trace easily any changes made or errors on projects. It also makes it possible for multiple people to work on the same project simultaneously using branches thus avoiding code overlapping. It can be used as a backup where historical projects can be locally saved.

What is GitHub?

Github is a web -based platform designed to help developers, collaborate and manage projects with ease. It also helps to store code. Github serves as a portfolio for coding projects. It also allows developers from all around the world to contribute to your project.

Hope we are still together , lets continue with the learning

What is the difference between Git and GitHub?

GitHub	Git
Provides collaboration and project management tools	Tracks changes in code
Requires an internet connection to access repositories	Operates locally on your machine
Cloud-based hosting platform for Git repositories	Version control system

Prerequisites for Using Git

Before you can start using Git on your machine, you need to ensure it is properly installed based on your operating system:

Windows: Download and install Git Bash. This provides a Unix-style command-line experience which is the standard for Git operations.
macOS / Linux: Open your Terminal. Git is often pre-installed, but if it isn't, you can install it using your system's package manager (e.g., brew install git for Mac or sudo apt install git for Linux).

Pro-Tip

To check if Git is already installed and ready to go, type the following command in your terminal or Git Bash:

git --version

Configure Your Identity

Once Git is installed, the first thing you should do is set your identity. Git attaches this information to every commit you make to track who made specific changes. You can configure your global username and email address by running the following commands:

git config --global user.name "Your Name"
'git config --global user.email "your_email@example.com"
`
Ensure to change “Your Name” and “your_email@example.com” to match your details. To ensure everything is saved correctly, you can list your active settings by running:

git config --list

When you run that command, Git will return a list of settings. It should look something like this:

user.name=Your Name user.email=your_email@example.com ...

Understanding SSH Keys

An SSH key is a secure access credential used by the Secure Shell (SSH) protocol. It provides a more secure and scalable way to authenticate your machine with GitHub than using a standard password.

SSH authentication relies on a pair of keys:

Public Key: Shared with services like GitHub. It acts like a "lock."
Private Key: Kept strictly on your local machine. It acts like the "physical key" to that lock.

Generating a New SSH Key

Open your Git Bash (Windows) or Terminal (Mac/Linux) and run the following command to generate a modern, high-security key:

ssh-keygen -t ed25519 -C "your_email@example.com"

Note: When prompted to "Enter a file in which to save the key," simply press Enter to accept the default location.

Adding the Key to Your GitHub Account

To link your machine to GitHub, you must copy your Public Key and add it to your GitHub settings.

View your public key: Run the following command to display the key text: cat ~/.ssh/id_ed25519.pub
Copy the output: Highlight the entire text starting with ssh-ed25519 and ending with your email.
Add to GitHub: Go to GitHub Settings > SSH and GPG keys > New SSH Key, then paste your key into the "Key" field.

Verifying the Connection

Finally, test the connection to ensure everything is configured correctly by running:

ssh -T git@github.com

If successful, you will receive a confirmation message:

"Hi [YourUsername]! You've successfully authenticated, but GitHub does not provide shell access."

Conclusion

As technology evolves, Git remains the undisputed backbone of version control, while GitHub continues to lead the way in collaborative software innovation.

Understanding the distinction between these two is vital for any developer: Git is the engine that tracks and manages your code changes, while GitHub is the hub that simplifies collaboration, hosting, and project management.

I hope this breakdown provides a clear starting point for your journey. I’d love to hear your thoughts—feel free to drop a comment below!

>Let's learn together.