DEV Community: Esther Njihia

Power BI Data Modeling: Relationships, Star Schema, Fact and Dimension Tables, and Power Query Joins

Esther Njihia — Sun, 13 Sep 2026 13:31:21 +0000

The problem before the visual

A Power BI dashboard can look impressive, but the model underneath can be complete chaos. The visual is what the user sees, but its accuracy and flexibility depend on how the data is structured before reporting begins.

Consider a Premier League dataset, the business process being analysed is the Premier League match intended to answer questions such as: Which teams perform better at home? Which teams struggle away? Do match statistics change during different periods of the season? Are there patterns in fouls, cards, or referee decisions?

All of these questions can be answered with visuals, but the more important question comes first:

How should the data be structured so that the same model can support different reports and analytical questions?

That is where data modelling begins.

What is data modelling?

Data modelling is the process of organising data into logical structures and defining how those structures relate to each other.

Source data is not always arranged in the best way for analysis. It is usually structured according to how it was collected, exported, or stored. Data modelling looks beyond that original structure and identifies the events in the data, the information that describes those events, and the relationships required to analyse them correctly.

A good model therefore does more than separate columns into different tables. It creates a structure that Power BI can use efficiently for reporting, filtering and calculations.

Why does data modelling matter?

As more information is added to a report, a poorly structured model becomes harder to maintain. Descriptive values can be repeated unnecessarily, tables can become increasingly wide, and calculations may begin carrying logic that should have been handled by the model itself.

Performance is another consideration. Power BI needs to load, compress, scan, and filter the model before producing results. Unnecessary duplication and complicated relationship paths can make that work harder.

The model also affects DAX. When relationships and table responsibilities are clear, calculations can use the existing model structure. When the structure is poor, DAX often has to compensate for missing or confusing modelling logic.

Golden rule: A bad data model can lead to bad and complicated DAX.

Maintainability is equally important. A model should make sense when another analyst opens it later. The purpose of each table, the central business event, and the main relationship paths should be understandable without having to reverse-engineer the entire report.

A useful modelling question is therefore:

How can this model serve multiple reports?

Parting shot

Without deliberate modelling	With a deliberate data model
Information can be repeatedly stored	Repetition can be reduced
Reporting logic becomes harder to follow	Tables have clearer responsibilities
DAX may compensate for structural problems	DAX can use the model structure
Growth can increase complexity quickly	The model is easier to extend
Harder to maintain	Easier to understand and maintain
Often designed around one report	Can support multiple reporting questions

Fact tables, dimension tables and grain

In dimensional modelling, tables normally serve different purposes. Two of the most important roles are the fact table and the dimension table.

A fact table stores the business events, transactions, or activities being analysed. It normally contains measurements together with keys that connect those events to descriptive information.

A dimension table stores the descriptive information that gives context to those events. Dimensions are commonly used for filtering, grouping, and labelling data in reports.

Dimension table	Fact table
Describes the event	Records the event
Contains descriptive attributes	Contains events and measurements
Commonly used for filtering and grouping	Commonly used for calculations
Usually contains unique members	Foreign keys can repeat
Gives context to the numbers	Contains the numbers being analysed

Before deciding what belongs in the fact table, the grain must be established.

Grain describes the level of detail represented by one row of the fact table. This matters because every measure is interpreted at that level. A table where one row represents an individual event behaves very differently from one where each row represents a monthly summary.

The simplest way to identify grain is to ask:

What does one row represent?

That answer should be clear enough to state in one sentence.

Three ways to organise data: Flat, Star and Snowflake

Once the roles of the tables are understood, the next question is how those tables should be arranged.

Flat schema

A flat schema keeps descriptive information and measurements together in one large table.

Its main advantage is simplicity. There are fewer tables to manage, no relationships are required, and the structure can be convenient for a small or temporary analysis.

The problem appears as the data grows. Descriptive values are repeated across many rows, redundancy increases, and the table can become increasingly difficult to maintain. Calculations may also become more complicated because there is no clear separation between descriptive attributes and measurable events.

A flat schema is therefore not automatically wrong. It can be appropriate when the analysis is small and unlikely to grow. The problem is using the same structure for a model that needs to scale.

Star schema

A star schema separates the business event from the descriptive information surrounding it. The fact table sits in the centre while dimension tables connect directly to it.

                 DimDate
                    |
                    |
DimTable ------- FactTable ------- DimTable
                    |
                    |
                 DimTable

The central fact table stores events and measurements while dimensions provide the context required to analyse them.

One of the main advantages of a star schema is that the filter paths are relatively easy to understand. A selection from a dimension can travel through the relationship to the fact table, where the relevant measures are calculated.

This structure can also make DAX easier to follow because the model already separates descriptive attributes from business events.

The trade-off is that real-world data does not always form a perfect star. A dimension may need to play more than one role, or the analytical problem may contain relationships that require additional handling. A star schema provides a clear structure, but it still has to reflect the actual problem.

Snowflake schema

A snowflake schema begins with the same fact-and-dimension structure but normalises one or more dimensions into additional related tables.

                 DimDate
                    |
                    |
DimTable ------- FactTable ------- DimTable
   |
   |
SubDimension

This can reduce repetition inside large dimensions by moving repeated descriptive information into separate tables.

The trade-off is additional complexity. More tables create more relationships, and filters may need to travel through several tables before reaching the fact table. This can make the model harder to read, maintain, and troubleshoot.

Snowflaking therefore needs a reason. Reduced redundancy should provide enough value to justify the additional relationship complexity.

How do we choose a schema?

Knowing that flat, star, and snowflake schemas exist is not enough. The model still needs to be selected according to the analytical problem.

Star schema may be widely recommended, but that alone is not a sufficient reason to use it.

Instead, the model can be evaluated against questions such as:

Consideration	Question
Grain	What does one row represent?
Analytics	What questions must the model answer?
Facts	What events or numbers are being measured?
Dimensions	What describes those events?
Relationships	How complicated will the connections become?
DAX	Will calculations remain understandable?
Performance	Is there unnecessary duplication or relationship complexity?
Maintainability	Can another person understand the model easily?
Scalability	Can more data be added later?
Reporting	Can users filter and analyse the data easily?

These questions make the schema choice a design decision rather than a rule.

Applying the concepts to the Premier League data

The Premier League dataset used in this project contains match information such as date, home and away teams, referee, goals, results, shots, shots on target, fouls, corners and cards.

Before creating tables, the grain needs to be established.

One row represents one Premier League match.

That makes the match the central business event.

FactMatches

FactMatches records the match itself together with measurable statistics such as goals, shots, shots on target, fouls, corners, cards, and results.

DimTeam

DimTeam contains descriptive information about the teams. This allows teams to be used consistently for filtering and grouping while the match events remain in the fact table.

DimDate

DimDate describes when matches occurred. It can contain attributes such as day, month, quarter, and year, which can then be reused across different reports.

DimReferee

DimReferee describes the referees associated with the matches. This provides a clear location for referee information and allows matches to be analysed or filtered by referee.

The resulting structure begins to resemble a star schema:

                 DimDate
                    |
                    |
DimTeam -------- FactMatches -------- DimReferee

At this stage, however, separate tables alone do not create a complete model. Power BI still needs a reliable way to determine how records from one table correspond to records in another.

Relationships in Power BI

A relationship defines how records in one table correspond to records in another.

Relationships are necessary when information has been distributed across several tables. They allow those tables to remain separate while still interacting during filtering and analysis.

For example, DimTeam contains descriptive information about a team while FactMatches records the matches involving that team. The relationship tells Power BI how those records correspond.

Relationships therefore affect much more than the appearance of Model View. They influence filter propagation, calculation context, and how data from different tables can be analysed together.

Before relationships can be created correctly, the tables need suitable keys.

Keys

A primary key uniquely identifies a record in a table.

A foreign key exists in another table and refers back to that record. The foreign key can repeat because the same entity may participate in many events.

The important point is that a relationship should not be created simply because two columns have similar names. The values must support the intended relationship.

For a normal one-to-many relationship:

the key on the one side should be unique;
the corresponding foreign key can repeat on the many side;
the values on the many side should have valid matches on the one side.

A useful question is:

What makes a column suitable for a relationship?

The answer depends on the meaning of the column, its uniqueness, and whether the values correctly match across the tables.

Applying keys to the football model

The Premier League source contains team and referee names but does not provide ready-made model IDs such as TeamID or RefereeID.

This does not prevent dimensional modelling. Once the entities have been identified, model keys can be created to provide stable identifiers.

For DimTeam:

TeamID	TeamName
T001	Bournemouth
T002	Newcastle
T003	Fulham
...	...

For DimReferee:

RefereeID	RefereeName
R001	A Taylor
R002	C Pawson
R003	S Barrott
...	...

DimDate can use a DateKey, while FactMatches stores the corresponding foreign keys.

The key exists because the model needs a reliable way to identify and relate records.

Cardinality

Cardinality describes how many records on one side of a relationship can correspond to records on the other side.

One-to-many

A one-to-many relationship means one record on the one side can correspond to several records on the many side.

This is common in dimensional modelling because a dimension contains one record for an entity while that same entity may appear in many fact-table events.

One-to-one

A one-to-one relationship means one record in one table corresponds to exactly one record in another table.

The relationship key is unique on both sides.

This can be valid, but it should have a clear purpose. If both tables have the same grain and are always used together, separating them may introduce complexity without providing much analytical value.

Many-to-many

A many-to-many relationship allows values to repeat on both sides.

This can represent a legitimate analytical problem, but it creates more complicated filter behaviour. It should not be used simply because duplicate values appear unexpectedly.

If a table that was expected to provide unique values contains duplicates, the grain and table design should be checked before choosing many-to-many.

Cardinality in the Premier League model

The relationship between DimDate and FactMatches is one-to-many because one date can contain several matches:

DimDate[DateKey]  1 -------- * FactMatches[DateKey]

The same principle applies to referees. One referee can officiate several matches:

DimReferee[RefereeID]  1 -------- * FactMatches[RefereeID]

The current model does not naturally require a one-to-one or many-to-many relationship. Understanding those relationship types includes knowing when they are unnecessary.

The team dimension introduces a different challenge. One team can participate in many matches, but each match contains two separate team roles:

HomeTeamID
AwayTeamID

Both refer to the same DimTeam[TeamID].

                     DimTeam
                    /       \
                   /         \
                  v           v
           HomeTeamID     AwayTeamID
                   \         /
                    \       /
                    FactMatches

The cardinality remains one-to-many, but the same dimension is now being used in two different roles.

Active and inactive relationships

Power BI can contain more than one possible relationship between tables, but only one relationship can normally act as the default filtering path between the same two tables.

An active relationship is used automatically during filtering and calculations.

An inactive relationship still exists in the model but is not used automatically. It can be activated when a specific calculation requires that alternative relationship.

Active relationship	Inactive relationship
Used automatically	Not automatically used
Default analytical path	Alternative analytical path
Solid line in Model View	Dashed line in Model View

This becomes important when the same dimension needs to describe the fact table in more than one role.

Active and inactive relationships in the football model

DimTeam contains one list of teams, while FactMatches contains both HomeTeamID and AwayTeamID.

Both are valid references to the same dimension:

                     DimTeam
                    /       \
             active           inactive
                  /             \
                 v               v
           HomeTeamID       AwayTeamID
                   \         /
                    FactMatches

This is a role-playing dimension. The entity is still a team, but its role changes depending on whether it appears as the home team or away team.

One relationship can provide the normal active path, while the second remains available for calculations that need the alternative team role.

The football model therefore provides a practical reason for active and inactive relationships rather than treating them as two settings to memorise.

Referential integrity

Referential integrity means that every foreign-key value should have a valid corresponding record in the related table.

If a fact table contains a foreign key that cannot be found in the dimension, the event still exists, but the model cannot retrieve the descriptive information expected from that dimension.

Relationships therefore depend on the quality of the keys used to create them.

A relationship does not correct an invalid reference. It exposes the consequences of that invalid reference during analysis.

Referential integrity in the Premier League model

Every:

HomeTeamID
AwayTeamID
RefereeID
DateKey

in FactMatches should have a corresponding value in its related dimension.

If FactMatches contains a team key that does not exist in DimTeam, that match cannot be fully described through the team dimension.

The same principle applies to referees and dates.

This creates an important connection between data quality and data modelling:

Relationships do not repair bad keys. They rely on valid keys.

Anti joins provide one way to test these relationships before relying on them in the final model.

Filter direction

A relationship also determines how filters can propagate between tables.

With single-direction filtering, a filter travels in one direction. In a typical dimensional model, this is normally from the dimension to the fact table:

Dimension
    |
    v
Fact

This produces a predictable analytical path. A descriptive value is selected in the dimension, and the corresponding fact records are filtered.

With bidirectional filtering, filters can travel in both directions:

Table A <----> Table B

Bidirectional filtering can be useful, but it should be introduced carefully. As the number of relationships increases, allowing filters to travel both ways can create multiple filter paths, ambiguity, and model behaviour that becomes harder to troubleshoot.

The decision should therefore be based on analytical requirements rather than simply using bidirectional filtering because it makes a particular visual work.

Filter direction in the Premier League model

The predictable default for this model is:

Dimension
    |
    v
FactMatches

Selecting a referee in DimReferee should filter the matching records in FactMatches. Selecting a date should do the same through DimDate.

The active team relationship follows the same principle.

Single-direction filtering from the dimensions to FactMatches therefore provides the clearest default design. Bidirectional filtering would only be introduced where a specific reporting requirement justifies the additional complexity.

A join is not a relationship

Relationships and joins both involve matching data, but they perform different jobs.

A Power Query Merge combines matching data while the queries are being transformed.

A Power BI relationship keeps the tables separate and defines how they interact after they have been loaded into the semantic model.

Power Query Merge	Power BI relationship
Data preparation	Data modelling
Combines matching information	Keeps tables separate
Can add columns to a query	Defines analytical interaction
Changes the resulting query	Does not physically combine tables
Happens during transformation	Operates in the semantic model
Useful for shaping and lookup operations	Useful for filtering and calculations

This distinction matters because excessive merging can undo the separation created by dimensional modelling. If every dimension is merged back into the fact table, the model can gradually return to a flat structure.

A merge should therefore solve a transformation problem, while a relationship should solve a modelling problem.

Join types

Power Query provides six main join types. The easiest way to understand them is to ask:

Which records does the join retain?

Left Outer Join

A Left Outer Join keeps every row from the first table together with matching information from the second table. If no matching row exists on the right, the left-side record remains.

Right Outer Join

A Right Outer Join keeps every row from the second table together with matching information from the first table.

Full Outer Join

A Full Outer Join keeps all rows from both tables. Matching records can be combined, while unmatched records from either side remain visible.

Inner Join

An Inner Join keeps only records that have a match in both tables. Unmatched records from either side are removed from the result.

Left Anti Join

A Left Anti Join keeps rows from the first table that do not have a matching row in the second table.

Right Anti Join

A Right Anti Join keeps rows from the second table that do not have a matching row in the first table.

The anti joins are particularly useful for validation because they answer a different question:

Which records failed to match?

Six joins using the football data

The join behaviour becomes easier to see using a small sample of the football model.

FactMatches sample

MatchID	TeamID
M001	T009
M002	T011
M003	T020
M004	T021

DimTeam sample

TeamID	TeamName
T009	Arsenal
T011	Chelsea
T020	Liverpool
T099	Example FC

T009, T011,, and T020 exist in both tables. T021 exists only in the fact sample, while T099 exists only in the dimension sample.

Left Outer

If FactMatches is the left table, all four match records remain. The record containing T021 is still preserved even though no matching team exists in DimTeam.

This type of join is useful when adding descriptive or key information to a fact query without wanting an unsuccessful lookup to remove the original event.

Right Outer

If DimTeam is the right table, every team record remains, including T099.

The join therefore preserves the population from the right-side table.

Full Outer

A Full Outer Join retains all records from both tables.

Both T021 and T099 remain visible, making this join useful when comparing two datasets and identifying mismatches on either side.

Inner

An Inner Join retains only:

T009
T011
T020

These are the values that exist in both tables.

The important risk is that M004 disappears from the result because its team key does not match. If the objective is to preserve all match events, that behaviour matters.

Left Anti

A Left Anti Join returns:

MatchID	TeamID
M004	T021

This answers:

Which match record contains a team that cannot be found in DimTeam?

That makes Left Anti particularly useful for testing referential integrity.

Right Anti

A Right Anti Join returns:

TeamID	TeamName
T099	Example FC

This answers:

Which team exists in the dimension but does not appear in this match sample?

Parting shot

Anti joins are more than additional options in the Merge Queries window. They can be useful data-quality tools because they make unmatched records visible.

Where Merge Queries fits into the football model

The original match data stores team names, while DimTeam contains the corresponding model key.

For example:

HomeTeam = Arsenal

while the dimension contains:

TeamID	TeamName
T009	Arsenal

FactMatches requires the corresponding HomeTeamID.

A Power Query merge can therefore match:

FactMatches[HomeTeam]
        ↓
DimTeam[TeamName]

and return the related TeamID.

That value can then become:

HomeTeamID

The process can be repeated for AwayTeamID and for the referee key.

Once the tables are loaded into the model, a relationship can then be created:

DimTeam[TeamID]
      1
      |
      *
FactMatches[HomeTeamID]

The two operations have different purposes:

The merge builds or retrieves the foreign key. The relationship tells Power BI how to use that key during analysis.

The model that fits the Premier League data

After considering the grain, table roles, schemas, keys, cardinality, and filtering requirements, a star-style structure provides the clearest fit for the model.

                         DimDate
                            |
                            |
                            v
DimTeam  -------------> FactMatches <------------- DimReferee
    \                       ^
     \______________________|
        second team role

The normal design uses one-to-many relationships from the dimensions to FactMatches, together with single-direction filtering from dimension to fact.

DimTeam requires additional handling because it performs both the home-team and away-team roles.

Requirement	Why the model fits
Reporting	Team, referee, and date provide clear reporting dimensions
DAX	Calculations can use understandable filter paths
Performance	Descriptive information does not need to be repeated with every match
Readability	Facts and dimensions are easy to identify
Scalability	More seasons and match statistics can be added later
Redundancy	Repeated descriptive information is separated from match events
Maintainability	Each table has a defined purpose
Filter propagation	Dimensions normally filter the central fact table
Model complexity	Most relationships remain straightforward one-to-many relationships

The star-style structure is therefore not selected simply because it is commonly recommended. It fits the grain of the dataset, the analytical questions being asked, and the relationship complexity required by the model.

Lessons learned

1. The source structure is not the analytical model

The way data arrives does not determine how it must be modelled. Source data is designed around collection and storage, while the Power BI model should be designed around analysis.

2. Grain comes before measures

A measure only has meaning when the level of detail underneath it is understood. In this model, establishing that one row represents one match provides the foundation for every match statistic that follows.

3. Keys need a purpose

IDs should not be created simply because dimensional models use keys. The entities and grain should be understood first, after which the appropriate model keys can be created.

4. Relationships are part of the analytical logic

Relationship lines in Model View are not decoration. Cardinality, active and inactive relationships, and filter direction all affect how Power BI evaluates a report.

5. Data quality becomes a modelling issue

Once dimensions and facts depend on matching keys, missing or invalid values can affect the model itself. Referential integrity therefore connects data preparation directly to reporting reliability.

6. Joins and relationships solve different problems

A merge prepares and combines data during transformation. A relationship allows separate model tables to interact during analysis. Using one does not make the other unnecessary.

7. Schema selection is a design decision

Flat, star, and snowflake schemas each have advantages and trade-offs. The correct structure depends on grain, analytical requirements, performance, maintainability, reporting needs, and relationship complexity.

From MKDIR to GIT PUSH: What Actually Happens to a Project?

Esther Njihia — Sun, 23 Aug 2026 07:02:43 +0000

The first time you see a Git workflow, it can look like a list of commands you are supposed to memorize:

git add.
git commit -m "Initial commit"
git push

Three commands, and somehow the project ends up on GitHub.

But what actually happens between creating a folder on your computer and seeing that project in a GitHub repository?

The commands become much easier to understand when you stop treating them as a sequence to memorize and start seeing them as different stages in a workflow.

In this article, we discussed a small Git Workflow, from creating a directory to your code being available on GitHub.

mkdir
  ↓
git init
  ↓
git add
  ↓
git commit
  ↓
git push

The goal is not simply to learn what each command means, but to understand where the project is at each stage and what changes after every command.

Why does this workflow matter?

When creating a project on your computer.

You create a folder, add some files, and start making changes.

At some point, you want Git to keep track of those changes. Eventually, you may also want the project's history to be available on GitHub. Simply put, local files store your work, Git tracks how it changes over time, and GitHub backs up and shares those tracked changes with the world.

It is tempting to think that Git takes the files on your computer and uploads them to GitHub.

That is not what happens.

There are several stages between the files you are editing locally and the repository hosted remotely.

A simplified version looks like this:

Working Directory
       ↓
Staging Area
       ↓
Local Repository
       ↓
Remote Repository

Understanding these four places is the key to understanding the Git workflow.

1. Working Directory: where the project starts

Everything starts with a directory on your computer.

For this walkthrough, create a small Git sandbox:

mkdir git-workflow-demo
cd git-workflow-demo

The project can contain two simple files:

git-workflow-demo/
├── README.md
└── hello.py

At this point, these are simply files inside a directory.

Git has not started tracking the project yet.

This is the working directory: the location where the project files exist and where changes are made.

For example, hello.py might contain:

print("Hello, Git!")

If the file is edited, the change happens directly in the working directory.

But there is an important question:

*How does Git know which changes should become part of the project's history? *

That is where Git's repository and staging area come in.

2. `git init`: turning the folder into a Git repository

Before Git can track the project, initialize a repository:

git init

This creates the Git metadata needed for the directory to become a Git repository.

The project is still in the same location, but Git can now start managing its history.

The transition looks like this:

Regular project folder
        ↓
     git init
        ↓
Git repository

A useful command to run immediately after this is:

git status

git status provides information about the current state of the repository.

It helps answer questions such as:

What files have changed?
What files are untracked?
What changes are staged?
What changes are ready to be committed?

Rather than guessing what Git knows about, git status lets you inspect the repository's current state.

3. The Staging Area: choosing what to record

Suppose README.md has been created or modified.

You can stage it with:

git add README.md

This is one of the most important distinctions for anyone learning Git:

git add does not upload the file to GitHub.

Instead, it tells Git:

Include this change in the next commit.

The staging area therefore sits between the working directory and the local repository.

Working Directory
       │
       │ git add
       ▼
Staging Area

You can verify the state with:

git status

This shows that the change has moved from an unstaged state to a staged state.

Why have a staging area?

The staging area gives you control over what goes into a commit.

Imagine three files have changed:

app.py
README.md
config.py

You might decide that only the changes to app.py and README.md belong in the next commit:

git add app.py README.md

Now those selected changes are staged, while config.py can remain unstaged.

This is one reason Git is more than a simple file-uploading tool.

It gives you a way to select the changes you want to record together.

4. `git commit`: creating a point in project history

Once the desired changes have been staged, create a commit:

git commit -m "Add README"

A commit is a recorded point in the project's history.

The workflow is now:

Working Directory
       ↓
    git add
       ↓
Staging Area
       ↓
   git commit
       ↓
Local Repository

The local repository now contains a record of the staged changes.

But there is an important distinction:

The commit has not been sent to GitHub yet.

This is where most confuse commit with push.

Think of the two commands this way:

git commit
→ Records the staged changes locally

git push
→ Sends local commits to a remote repository

A commit and a push are therefore two separate operations.

5. A small experiment: watch the state change

One of the easiest ways to understand Git is to observe what happens after each command.

Start with:

git init

Then:

git status

Stage the README:

git add README.md

Check the status again:

git status

Create the commit:

git commit -m "Add README"

Then check again:

git status

The important part of this exercise is not simply running the commands.

It is observing how the repository's state changes.

You can think of the experiment as:

Before git add
        ↓
Working Directory

After git add
        ↓
Staging Area

After git commit
        ↓
Local Repository

That makes the workflow easier to understand than memorizing a sequence of commands without knowing what each one is doing.

6. What changes after each command?

Here is the entire workflow in one place:

Command	What changes?	Where are the changes?
`git init`	Creates Git metadata	Local project
`git add`	Selects changes for the next commit	Staging area
`git commit`	Records staged changes	Local repository
`git push`	Sends local commits to the remote	GitHub

The important idea is that each command has a different responsibility.

That is why the commands should not be thought of as interchangeable.

7. Where does GitHub come in?

So far, everything has happened on the local computer.

The working directory is local.

The staging area is local.

The Git repository containing the commit history is local.

GitHub becomes involved when a remote repository is connected.The local repository and the GitHub repository are separate repositories.

The next step is to connect them.

8. Connecting the local repository to GitHub

After creating an empty repository on GitHub, connect it to the local repository with:

git remote add origin git@github.com:username/git-workflow-demo.git

Here, origin is the name given to the remote repository.

You can check the configured remote with:

git remote -v

At this point, Git knows where the remote repository is.

But the local commits are still local.

They have not been sent to GitHub yet. That requires git push.

9. Where SSH fits and what it does

If the remote URL looks like this:

git@github.com:username/git-workflow-demo.git

the repository is being accessed through SSH.

SSH provides an authenticated way for your computer to communicate with GitHub.

The basic idea is:

Your computer
     │
     │ SSH authentication
     ▼
   GitHub

Before Git can communicate with GitHub through SSH, an SSH key needs to be configured and associated with the GitHub account.

The important point for the Git workflow is that SSH is about authentication and communication with the remote repository.

It is not another version of git add or git commit.

Those commands manage the local Git workflow. SSH helps establish the connection to GitHub.

10. Finally: `git push`

Now the local commit can be sent to the remote repository:

git push -u origin main

This is the step where the local Git history is transferred to the configured remote repository.

So what actually happened to the project?

It did not simply jump from the computer to GitHub.

It moved through a series of stages.

A common beginner misconception

One of the easiest things to misunderstand when learning Git is that git add, git commit, and git push all somehow "move the code."

They do—but not to the same place.

Consider this simplified model:

git add
→ Select changes for the next commit

git commit
→ Record those staged changes locally

git push
→ Send those local commits to the remote repository

The commands are connected, but each one operates at a different stage of the workflow.

This distinction becomes particularly important when something does not behave as expected.

If a file is not included in a commit, the first question should not be:

"Why didn't GitHub update?"

A better question is:

"At which stage did the change stop?"

Was it:

never changed in the working directory?
not staged?
not committed?
committed locally but not pushed?

Understanding the workflow makes those questions much easier to answer.

The bigger lesson: Git is a workflow, not a command list

It is possible to memorize:

git add .
git commit -m "message"
git push

and still have very little understanding of Git.

A better mental model is:

"I changed something."
        ↓
Working Directory

"I want this change included."
        ↓
Staging Area

"I want to record this version."
        ↓
Local Repository

"I want that recorded history on GitHub."
        ↓
Remote Repository

Once the purpose of each stage is clear, the commands become easier to remember because each command answers a specific need.

That is more useful than memorizing a command sequence without understanding what happens between the commands.

5 takeaways from the Git workflow

1. The working directory is where changes are made.

This is where project files are created, edited, and deleted.

2. The staging area lets you choose what goes into the next commit.

git add prepares selected changes for recording.

3. A commit records staged changes locally.

A commit creates a point in the project's history.

4. A push sends local commits to a remote repository.

git push is what moves committed history from the local repository to the configured remote.

5. Understanding the workflow is more useful than memorizing commands.

When the purpose of each stage is clear, Git commands become easier to reason about.

What happens when more than one person changes the project?

The basic workflow is only the beginning.

Once a repository becomes collaborative, another set of questions appears:

What happens when two people work on the same project?
What is a branch?
Why create a branch instead of working directly on main?
How are changes reviewed before they become part of the main project?
What happens when two people modify the same part of a file?

Those questions lead to the next part of the Git workflow:

branches → pull requests → merging → merge conflicts → collaborative Git workflows.

For now, the most useful mental model is simple:

Working Directory
       ↓
   git add
       ↓
Staging Area
       ↓
 git commit
       ↓
Local Repository
       ↓
  git push
       ↓
Remote Repository

Git is not just the tool used to put code on GitHub. It is a system for tracking, organizing, recording, and sharing changes to a project over time.

Analysis of 120 Years of Olympics Data

Esther Njihia — Sun, 22 Jan 2023 06:12:18 +0000

Overview

The Olympic Games are one of the most prestigious and widely-followed sporting events in the world. With a history dating back over a century, the Olympics have seen countless athletes from all corners of the globe compete for glory and honor. In this project, we will use Python, SQL and Tableau to analyze 120 years of Olympics data sourced from Kaggle.

Data Collection

The data for this analysis was sourced from Kaggle and includes information on athletes, teams, events, and medals. The data was collected for all Summer and Winter Olympics from 1896 to 2016.
Data source: Kaggle

Data Cleaning and Preparation

Before we could begin our analysis, the data needed to be cleaned and prepared for use. This involved removing any duplicate or irrelevant data, correcting any errors or inconsistencies, and formatting the data in a way that would be compatible with our analysis tools.

Data Cleaning with Python

Load data

df = pd.read_csv("data/athlete_events.csv")

Calculate the number of missing values in the data

df.isnull().sum()

We got some missing values with the age, height and weight variables. How about we fix that.
Fill in the age missing values with its mean.

df['Age'].fillna(df['Age'].mean(), inplace= True)

Repeat the same for the height and weight variables.

SQL Analysis

Once the data was cleaned and prepared, we used SQL to extract and analyze the data. We were able to answer questions such as:
1.What is the Gender distribution of athletes across different sports and event?

SELECT Sport, Event, Sex, COUNT(Name) as athlete_count
FROM Athlete
GROUP BY Sport, Event, Sex
ORDER BY athlete_count DESC;

2.Which city and season have the most successful athletes?

SELECT City, Season, COUNT(Medal) as Medal_count
FROM Athlete
WHERE Medal IS NOT NULL
GROUP BY City, Season
ORDER BY Medal_count DESC

3.Which athletes are the most successful in terms of number of medals?

SELECT Name, COUNT(Medal) as medal_count
FROM Athlete
GROUP BY Name
ORDER BY medal_count DESC;

4.Trend analysis to identify any patterns in the number of medals over time
By year

SELECT Year, COUNT(Medal) as medal_count
FROM Athlete
GROUP BY Year
ORDER BY Year ;

5.Use statistical analysis to find factors associated with winning more medals.

first create a sub query to find the total count of medals per city and season

WITH city_season_medals AS (
    SELECT City, Season, COUNT(Medal) AS medal_count
    FROM Athlete
    GROUP BY City, Season
)

use statistical methods to find the relationship between city & season and the total number of medals. Fo this instance I used z-score. The higher the z-score the higher the correlation.

SELECT City, Season, medal_count, (medal_count - AVG(medal_count) OVER()) / STDEV(medal_count) OVER() AS z_score
FROM city_season_medals
ORDER BY z_score DESC

These are some of the questions that were answered during the SQL analysis.

Visualization with Tableau

To make the data more accessible and understandable, Tableau was used to create visualizations of our findings. Some of the visualizations we created include:

A pie chart showing the gender distribution across all sports
A bar chart showing the most successful athletes over time.
line charts to show trends and patterns in the number of medals won over time

View the whole Olympics dashboard:Olympics Dashboard

Conclusion

Through this analysis, I was able to gain a deeper understanding of the history of the Olympics and the performances of countries and athletes over the past 120 years. By using SQL and Tableau, I was able to extract valuable insights from the data and present them in a clear and accessible way.

Code and Data

The code and data used for this project is available at Github

By using the above techniques, we can also use the data to predict future results, and also to identify patterns and trends that can be used to improve performance and achieve success in future Olympic games.

Data engineering 102: Introduction to python for data engineering.

Esther Njihia — Sun, 04 Sep 2022 20:39:09 +0000

Overview

The most interesting thing about python language is sort of all rounded. Due to its readability which is easy and the fact that it has very many applications including data engineering. In this post, we will discuss the most important concepts in the python language.

Python Syntax

Python, builds the code structure using whitespace and indentation.

Comments

The comments are just as crucial as the code itself, because they explain why a piece of code was produced
The comments are ignored by the Python interpreter when it runs the code.
A single line comment in Python starts with the hash symbol (#), which is followed by the comment.

Identifiers

Python uses identifiers, or names, to designate variables, functions, modules, classes, and other types of objects.
An identifier's name must begin with a letter or an underscore. After that, only underscores or alphanumeric characters are allowed.

Keywords

Keywords are words that have special meaning in python. Python has a special module keyword that outlines all the keywords that are used in the Python Language.

Control statements

If statements.

If statements are conditional statements. Below is the summary of the different if statements:
When you want to run a code block based on a condition, use the if statement.
If you want to execute a different code block if the condition is not True, use the if...else statement.
When you wish to check multiple conditions and execute the associated code block after the condition that evaluates to True, use the if...elif...else statement.

for loop

A code block can be performed a predetermined number of times by using the for loop statement.
To alter the loop, use the range(start, stop, step).

while loop.

If a condition is True, use the Python while loop statement to run a code block.

Functions

A named, reusable chunk of code called a function in Python executes a command or returns a value.
Create a new function by using the def keyword. The function definition and body make up a function.
A function may have 0 parameters or more. You must pass a function the same amount of arguments if it has one or more parameters.
A function can do a task or give back a value. If you want to return a value from a function, use the return statement.

Lists

An orderly group of elements is referred to as a list.
If you want to retrieve a list element by its index, use the square bracket notation []. The index of the first element is zero.
To reach a list element from the list's end, use a negative index. The final component has an index of -1.
To change an entry from a list, use list[index] = new value.
To add a new element to the end of a list, use the append() method.
To insert a new element at a certain location in a list, use insert().
To remove an entry from a list and return it, use the pop() function.
To eliminate an element from a list, use remove().
Tuples are immutable lists.
Use tuples when you want to define a list that cannot change.

Dictionary

A Python dictionary is a group of key-value pairs, each of which has a corresponding value.
In order to access a value by its key, use square brackets or the get() method.
To remove a key-value pair by the key from a dictionary, use the del statement.
To iterate through the keys, values, and key-value pairs in a dictionary, use a for loop.

This post includes a summarization of some most important concepts as a beginner in Python.

DEV Community: Esther Njihia

Power BI Data Modeling: Relationships, Star Schema, Fact and Dimension Tables, and Power Query Joins

The problem before the visual

What is data modelling?

Why does data modelling matter?

Parting shot

Fact tables, dimension tables and grain

Three ways to organise data: Flat, Star and Snowflake

Flat schema

Star schema

Snowflake schema

How do we choose a schema?

Applying the concepts to the Premier League data

FactMatches

DimTeam

DimDate

DimReferee

Relationships in Power BI

Keys

Applying keys to the football model

Cardinality

One-to-many

One-to-one

Many-to-many

Cardinality in the Premier League model

Active and inactive relationships

Active and inactive relationships in the football model

Referential integrity

Referential integrity in the Premier League model

Filter direction

Filter direction in the Premier League model

A join is not a relationship

Join types

Left Outer Join

Right Outer Join

Full Outer Join

Inner Join

Left Anti Join

Right Anti Join

Six joins using the football data

FactMatches sample

DimTeam sample

Left Outer

Right Outer

Full Outer

Inner

Left Anti

Right Anti

Parting shot

Where Merge Queries fits into the football model

The model that fits the Premier League data

Lessons learned

1. The source structure is not the analytical model

2. Grain comes before measures

3. Keys need a purpose

4. Relationships are part of the analytical logic

5. Data quality becomes a modelling issue

6. Joins and relationships solve different problems

7. Schema selection is a design decision

From MKDIR to GIT PUSH: What Actually Happens to a Project?

Why does this workflow matter?

1. Working Directory: where the project starts

2. git init: turning the folder into a Git repository

3. The Staging Area: choosing what to record

Why have a staging area?

4. git commit: creating a point in project history

5. A small experiment: watch the state change

6. What changes after each command?

7. Where does GitHub come in?

8. Connecting the local repository to GitHub

9. Where SSH fits and what it does

10. Finally: git push

A common beginner misconception

The bigger lesson: Git is a workflow, not a command list

5 takeaways from the Git workflow

1. The working directory is where changes are made.

2. The staging area lets you choose what goes into the next commit.

3. A commit records staged changes locally.

4. A push sends local commits to a remote repository.

5. Understanding the workflow is more useful than memorizing commands.

2. `git init`: turning the folder into a Git repository

4. `git commit`: creating a point in project history

10. Finally: `git push`