DEV Community: David Mwandairo

Connecting Data the Right Way: Modeling, Relationships, and Schema Design in Power BI

David Mwandairo — Mon, 29 Jun 2026 01:39:51 +0000

As a data professional, one norm that I have had to accept is that raw data rarely comes in one neat package. Most of the times, it'll be scattered, messy, and it lives across different tables that naturally don't talk to each other. Have you ever stayed up late looking at several spreadsheets trying to figure out how they relate to each other? Well, the good folks at Microsoft built Power BI to solve this. Power BI gives you the tools to create relationships between different sets of messy data. Before building beautiful dashboards with Power BI, however, you will need to understand what is happening beneath the surface.

This article examines the importance of data modelling, relationships, schemas and joins in Power BI. Understanding how these concepts work together and getting them right makes all the difference in creating insightful dashboards.

Beyond the Spreadsheet: Giving Your Data a Voice through Meaningful Relationships

Data modelling involves structuring and organizing your data in a way that Power BI can make sense of it. It's similar to setting up a filing system before you start working. Without having a sensible structure in place, your reports will either break or produce results that are untrustworthy.

In Power BI, the data model resides in the Model View, where all your tables and the connections between them are visibly laid out. A good data model answers one simple question: how does each piece of data relate to every other piece?

In this article, we will use one relatable example. Imagine you own an online bookstore and your data lives in three different tables:

Customers - stores customer names, emails and locations.
Orders - stores order IDs, dates, amounts, and customer references.
Books - stores book titles, authors, genres, and prices.

Each table is useful on its own but limited. Structuring a good data model will connect them in a way that Power BI will understand the full story, for example who bought what, when and for how much.

Using Joins in Power BI to Merge Tables

A join is an operation that combines rows from two tables based on a related column they share. This happens in Power BI within the Power Query Editor, which is the data transformation workspace before the data enters the model. To perform a join in Power Query, you navigate to:

Home > Merge Queries

You then select the two tables that you would like to combine and choose the matching columns. For our bookstore, you would match the Customer_ID column in the Orders table with the Customer_ID column in the Customers table.

There are six types of joins in Power BI and picking the right one is critical:
Note: The left table is the primary table you will choose and make reference to. The right table will be the table you'd like to establish a connection with.

Inner Join

This join returns only the rows where there is a match in both tables. In our bookstore data, only customers who have placed at least one order will be seen in the results

Left Outer Join

This join returns all rows from the left table and only the matching rows from the right table. From our bookstore data, it will return all the customers, even the ones with no orders yet.

Right Outer Join

This is the opposite of a left outer join. It returns all rows from the right table and only the matching rows from the left. From our bookstore example, it will return all the orders even if the customer record is missing.

Full Outer Join

This join returns everything from both tables whether they match or not and missing values will appear as null. From our data, it will return every customer and every order regardless of a match.

Left Anti Join

This join returns only the rows from the left that have no match in the right table. From our bookstore data, it will return the customers who have never placed an order, thus can be used in targeting inactive users.

Right Anti Join

This join returns the rows from the right that have no match in the left table. In our case, it will return the orders with no associated customer record.

Selecting the wrong type of join can subtly corrupt you reports, therefore, always pause and ask yourself what you actually need to see.

Connecting the Dots: How Relationships Shape Power BI Data Models

While joins are necessary in connecting tables during the data preparation stage, relationships connect tables at the model level, thus allowing them to interact during analysis. In the Model View, relationships are created by dragging a column from one table and dropping it onto the matching column in another table. The relationship between them will be represented by a line that Power BI will draw between the two tables.

Types of Relationships

One-to-Many (1:*)

In this relationship type, one record in table X relates to multiple records in table Y, but each record in table Y relates to only one record in table X. For example, one customer can make many orders but each order belongs to one customer.

Many-to-Many (:)

Several records in table X can relate to multiple records in table Y. This relationship is complex and can return unanticipated results if not handled carefully even though it is supported in Power BI. It requires special attention to execute. For example, one book can appear in many orders and a single order can contain many books.

One-to-One (1:1)

Each record in table X matches exactly on record in table Y. For example, each customer has exactly one loyalty profile record.

Cross-Filter Direction

Filter direction controls how filters flow between tables when interacting with reports. They are classified into:

Single Direction - Filters flow in one direction i.e. from the lookup table to the data table. This is the usual default.
Bidirectional - Filters flow in both directions. If overused, this can produce confusing results.

How Schema Design Shapes Your Data Model

A schema is the basic layout and structure of a data model. The schema you choose will affect the performance and readability of your Power BI reports. There are two types of schema used in Power BI:

Star Schema ⭐

This schema consists of one central Fact Table enveloped by multiple Dimension Tables. The fact table holds the numerical, measurable data while the dimension table holds the descriptive, categorical data. The figure below shows a representation of the star schema:

        [Customers]
             |
[Books] — [Orders] — [Dates]
             |
         [Locations]

Table Type	Example	Contains
Fact Table	Orders	Order amounts, quantities, revenue
Dimension Table	Customers	Names, emails, locations
Dimension Table	Books	Titles, authors, genres, prices
Dimension Table	Dates	Day, month, quarter, year

The star schema is fast and easy to understand. Power BI's engine is optimized to work with this structure, hence reports will load faster and DAX calculations will work more efficiently.

Snowflake Schema ❄️

This schema further extends the star schema by breaking down the dimension tables into more related tables. It's structure resembles that of a snowflake because of its branching structure. Its structure is as shared below:

[Author Details] — [Books] — [Orders] — [Customers] — [Customer Segments]

The snowflake schema is known to add complexity to a data model even though it may save on storage space and reduce data redundancy. Filters, in effect, have to move through more relationships to get to their destination, thus slowing down performance and making the data model hard to maintain.
Pro-tip: Stick with the star schema when starting out with Power BI because it is the most used in Power BI projects.

Why Do We Need to Know This?

Charts and visuals are exciting to create when we need to showcase our Power BI projects, but a poorly structured data model will produce bad results. Some of the effects of a poorly structured data model include; filters behaving strangely and numbers not adding up when using DAX functions.

A solid foundation in the creation of good data models will save you from hours of debugging. - David Mwandairo

Getting your joins, relationships and schema right from the beginning results to:

Simpler DAX calculations.
Faster report performance.
Dashboards that are easier to scale and maintain.
Accurate and trustworthy numbers.

The data model is the foundation of a good Power BI report. When you build it well, everything else will become considerably easier.

Conclusion

Power BI is a capable Business Intelligence tool. It just requires you to understand the fundamentals and it will treat you well. Joins, modelling and relationships aren't the most exciting topics but understanding them well will separate data professionals that prepare reports which look good from those who prepare reports that actually work.

To perfect the Star Schema concept, you should ensure that you use intentional joins in your data. The one-to-many relationships that you build will have to be clean and always ask yourself whether you data reflects real-world logic. Once that foundation is solid, the dashboards and insights will take care of themselves.

Linux Fundamentals for Data Engineering

David Mwandairo — Mon, 15 Jun 2026 22:07:34 +0000

Introduction

The Linux operating system has become the go to platform for handling data engineering workloads in the modern data landscape. Some of the prominent data-related software it powers include:

Amazon Redshift which is a cloud based data warehouse that runs on a customized version of Linux.
Apache Spark which is a big data framework that runs on Linux clusters.
Apache Airflow which is an orchestration tool that is deployed on Linux servers.

Since these industry standard tools depend on Linux to run, proficiency in Linux is now a core competency, therefore, data engineers need to know how to navigate, manipulate and automate data workflows using the command line interface. This article covers some of the basic commands that a beginner in using Linux should know.

Connecting to a Remote Server

To demonstrate how to use the commands, I will first connect to a remote Linux server using the secure shell(ssh) command. Since I use the Fedora Linux distribution, I did not have to install any proprietary software to access the server. I can do it directly from the terminal. To access the server from a specific port, I added -p 22 at the end of the server IP address and entered the password to log in.
ssh root@159.65.222.96 -p 22

Once I logged in, I first updated the server to be able to use the latest features using the command sudo apt update. The commands used in the Ubuntu Linux distribution may be different from other Linux distributions. In this case, we use apt since the server is an Ubuntu Linux distribution.

After completing the update, I created my user account within the server using the command sudo adduser DavidM to isolate my workflow.

I got an error telling me to change the naming convention to my user name but I intend to keep it as it is to make it distinct. To bypass the error, I use the command sudo adduser DavidMw --force-badname and proceed to create my user account.

To switch to my user, I run the command su DavidMw.

Since my account is active, I can alternatively log in directly into the server without going through the root user as shown below. To do this, I run the command ssh DavidMw@159.65.222.96 -p 22.

Checking Postgresql version

To check the Postgresql version available, I run the command psql --version.

Next, I check the status of the Postgresql server using the command sudo systemctl status postgresql.

Now that I have confirmed it is enabled, I can log in to the Postgresql server using the command sudo -i -u postgres. After that, I access the Postgres interface using the command psql.

Creating a Database and Schema

Now that I have logged in to the Postgres user interface, I can create my database using the SQL script CREATE DATABASE DavidMw;.

To confirm whether it has been created, I run the command \l and I am able to see it as shown below. To exit the list of databases, I press 'q'.

Next, I create a schema named 'staging' within my database in which I will upload my data. To do that, I will first move into my database using the command \c then I run the script CREATE SCHEMA staging;.
Note: Always include a semi-colon(;) at the end of your SQL scripts to mark the end of the statement.

To view the schema, I run the command \dn.

Upload Data to a Schema

To upload data to a schema, we use Dbeaver which is a universal database management tool. First, we connect it to the Postgresql server following the steps below.
First, I create a new connection in Dbeaver

Next, I setup the connection using the details I created previously.

Next, I establish my connection and connect to my database.

To import the data, I right-click the staging schema and hover to the "Import Data" option after which I select the file I want to upload.

In this case, I select the 'salary_data.csv' file and continue.

After a few seconds, the data is uploaded.

Common Linux Commands

The core commands used in Linux are necessary for creating, editing and navigating through files. The creating, editing and updating of files in Linux systems happens within directories, hence a data engineer should always know where he/she is within the file system. Thus, the command to run to know where you are in the file system is pwd(print working directory) as shown below:

To see the contents of the directory, we use the command ls(list) and to create a new directory, we use the command mkdir(make directory) to create the 'newfolder' as shown below:

When working with remote servers, it is possible to upload files and directories directly using the scp(secure copy) command. To locate the file to be uploaded, I first navigated into the folder containing the file by using the cd(change directory) command. The difference between uploading a file and a folder is adding '-r' after the scp command. In my case, I uploaded the 'hotel_data.csv', 'salary_data.csv' files and 'stocks' folder to my instance of the server using the process below.
Note: When running the scp command, ensure that to run the command in the origin device's command line interface.

To create files in Linux, one can use either the echo or touch command. The echo command inputs content directly within a file while the touch command creates an empty file. Below is an example of how I created the 'file1.txt' and 'file2.py' files using the two commands respectively.

To insert data into the empty 'file2.py' file, I used the vim editor by running the vi command as shown below. To insert data into the file, press "i" to access the interactive interface and once done, press "Esc" + ":" and subsequently "wq" to save the changes.

To view the changes made, use the command cat followed by the file name.

Files can be viewed in different formats. The ls command enables one to view the files within a directory through the basic format. To view them in a more detailed format, we use the commands:

ls -l   #lists the files in a directory in the long listing format.
ls -la  #shows all the details of all the files within a directory.
ls -a   #lists all the files including the hidden ones.

Copying and moving files are also important commands used in Linux. They are executed by the cp and mv commands respectively. The cp command comes in handy when creating back-up/duplicate files. The mv command moves files from one folder to another and can also be used to rename files. Directories can also be copied by including -r after the cp command as shown below.

To delete files, we use the rm command. Folders can also be deleted by adding "-r" to the rm command to delete the files recursively. To move up a directory, we use the cd .. command.

File Permissions

Setting file permissions help data engineers determine who has access to particular files. File permissions are determined using a 10-character string. An example of a permission string is -rw-rw-r-- where the first character represents the file type which can be "-" to signify a file, "d" to signify a directory and "l" to signify a link. The next 3 characters represent the file owner's permissions, the middle 3 characters represent the group's file permissions and the last 3 characters represent others' file permissions. There are two ways in which file permissions can be assigned as shown in the tables below.

The example below shows how file permissions are given on the command line using the chmod command.

Conclusion

Mastering the Linux fundamentals transforms a data engineer from someone that just runs scripts to someone that can design, build and deploy strong data systems. The commands are important in managing tasks such as file navigation and handling, remote access, file permissions and text processing which form the foundation of data engineering. Through frequent practice, the command line becomes a powerful tool to run data engineering tasks on a large scale.