DEV Community: Mwikali

Understanding Data Modeling, Relationships, Schemas, and Joins in Power BI

Mwikali — Wed, 01 Jul 2026 15:32:42 +0000

Introduction

Microsoft's Power BI is a well-known business intelligence tool, commonly used for data visualization and reporting. Power BI, however, is more than a visualization tool; it is also used to design data models for accurate dashboards.

Some of the data sources used by businesses include Excel, SQL databases, cloud storage, CRMs, and APIs. This data is sourced raw and is unlikely to be ready for analysis. Data engineers and analysts must organize, clean, and connect these datasets before creating reports.

Power BI achieves this through data modeling, relationships, schemas, and joins. These concepts directly influence the quality of the data, the performance of dashboards and reports, and analytical accuracy. A strong Power BI solution depends on the quality of the underlying data model. Effective application of these concepts ensures reliable, efficient, and meaningful business insights.

What is Data Modeling?

Data modeling is the process of visually representing either parts of or entire communication systems. It illustrates the types of data used and stored within a system and how they interact. Data model components represent real-world data sources and the data they contain.

The three foundational concepts on which all data models are based are entities, attributes, and relationships. Entities represent real-world objects of interest to a business, such as customers and orders. They are also referred to as tables. Attributes describe the characteristics of entities, such as a customer's name and address. Relationships represent complex data structures and connect related information between entities.

Rather than simply importing tables, Power BI allows users to create a model that represents how business entities interact. Effective data models meet the needs of organizations and support data-driven decision making. Benefits of a good data model in Power BI include:

Better Performance
Improved Accuracy
Simplicity
Maintainability
Scalability

Relationships in Power BI

Relationships define how tables communicate. Cardinalities are the relationships in a data model that define how rows in one table relate to rows in another table.

Cardinality	Description	Example
One-to-Many	One row in the dimension corresponds to many rows in the fact	One product appears in many sales transactions
Many-to-One	The same relationship, viewed from the other direction	Many sales transactions reference one product
One-to-One	One row in table A matches exactly one row in table B	Employee table and EmployeeDetails table
Many-to-Many	Multiple rows in A can match multiple rows in B	Students and courses (each student takes many courses, each course has many students)

Power BI allows users to create and manage relationships either automatically or manually. The auto-detect option automatically creates relationships when two or more tables are loaded or when activated under the Manage Relationships tab. Power BI looks for columns with matching names and compatible data types to create relationships automatically. Auto-detect works well when column names are an exact match in both tables, data types are compatible, and values between tables correspond.

Manually creating relationships can be done via drag-and-drop, through the Manage Relationships dialog, or from the Properties panel. This way, the user configures the cardinality and cross-filter directions. Power BI can also be used to edit, delete, or deactivate a relationship.

The following are recommended best practices for creating relationships:

Primary keys should always be unique
Many-to-many relationships should be avoided unless necessary
Duplicate keys should be removed
Relationships should be kept simple
The star schema should be used whenever possible

The effectiveness of relationships depends heavily on the structure of the overall schema.

Understanding Schemas in Power BI

A schema is a structure that defines how data is organized and its relationships. Power BI models typically use dimensional modeling, making databases simple and easy to understand. In dimensional modeling, we have fact and dimension tables.

A fact table is the main table that describes events that have taken place. It is characterized by having the largest number of data fields, is likely to change or update, and likely has duplicates. A dimensional table holds information about categorical fields in the fact table. It is smaller than a fact table and should not hold duplicates.

Star Schema

A star schema has one fact table at the center with multiple dimension tables radiating outward, creating a star shape. This schema is simple, fast, scalable, and intuitive. It is the preferred schema for Power BI.

Snowflake Schema

A snowflake schema has a fact table with multiple dimension tables that branch out further into sub-dimensions.

Star vs Snowflake Schema

Feature	Star Schema	Snowflake Schema
Structure	Fact table surrounded by denormalized dimension tables	Fact table with normalized dimension tables that branch into sub-dimensions
Dimension tables	Wide and flat (all attributes in one table)	Narrow and deep (attributes split across multiple related tables)
Number of tables	Fewer	More
Query performance	Faster (fewer joins)	Slower (more joins required)
Storage	Slightly more (due to redundancy)	Slightly less
Ease of use	Simpler for report authors	More complex
Recommended for Power BI?	Yes	No

Joins in Power BI

Power BI joins enable us to establish relationships between data tables. Joins occur primarily in Power Query during data transformation, while relationships occur after data is loaded inside the model. Power BI supports all standard joins as follows:

Inner join: Returns all rows in the left and right tables that are a match. If no match is present, it returns zero records.
Outer join: Returns all rows present in the left and right tables even when they don't match.
Left outer join: Returns all rows in the left table and corresponding rows in the right table, if any.
Right outer join: Returns all rows in the right table and corresponding rows in the left table, if any.

Joins in Power BI are performed using the Merge Queries button in the Home tab. It gives you the option to merge the second table inside the original table or to create a new table with the result of joining the two tables together.

How Data Modeling Supports Data Quality

Data quality measures how well a dataset meets criteria for accuracy, completeness, validity, consistency, uniqueness, timeliness, and fitness for purpose. Data quality is important because it directly impacts the accuracy and reliability of information used for decision-making. Quality data is key to making accurate, informed decisions.

Data modeling in Power BI improves data quality by supporting consistency, reducing redundancy, and standardizing calculations. Relationships support referential integrity, accurate filtering, and reliable aggregations. Schemas support organized datasets, scalability, and maintainability. In addition, joins support data validation, merging sources correctly, and identifying missing records.

Best Practices for Power BI Data Modeling

Design the model before importing data.
Use a star schema whenever possible.
Create meaningful table and column names.
Eliminate duplicate records.
Define clear primary and foreign keys.
Avoid unnecessary calculated columns.
Use measures over calculated columns when appropriate.
Validate joins before loading data.
Document relationships.
Keep models simple and scalable.

Conclusion

In conclusion, data modeling forms the foundation of Power BI analytics. Relationships enable tables to work together correctly. Schemas provide an organized and scalable structure. Joins integrate data during preparation. Together, these concepts improve data quality, analytical accuracy, report performance, and business decision-making. Mastering these concepts enables analysts to build trustworthy, maintainable, and high-performing Power BI solutions.

Linux Fundamentals for Data Engineering: A Practical Hands-On Guide

Mwikali — Tue, 16 Jun 2026 10:59:14 +0000

Introduction

In today's data-driven world, organizations rely heavily on data engineering to collect, process, store, and analyze vast amounts of information. Behind many modern data platforms lies an operating system that has become the backbone of data infrastructure: Linux.

From cloud servers and databases to big data platforms and containerized applications, Linux powers a significant portion of the world's data ecosystems. For aspiring data engineers, understanding Linux is a fundamental skill.

This article explores essential Linux concepts and commands from a practical data engineering perspective. Drawing from a hands-on server administration and PostgreSQL database setup exercise, it demonstrates how Linux skills support real-world data engineering workflows.

Why Linux Matters in Data Engineering

Most enterprise data platforms operate on Linux-based systems. Technologies such as Apache Spark, Hadoop, Kafka, Airflow, PostgreSQL, MySQL, and many cloud-native services are commonly deployed on Linux servers.

Data engineers frequently use Linux to:

Access and manage remote servers
Configure databases
Transfer datasets securely
Monitor system performance
Automate data pipelines
Manage permissions and security
Troubleshoot infrastructure issues

A solid understanding of Linux allows data engineers to work efficiently across development, testing, and production environments.

Understanding the Linux File System

One of the first concepts every Linux user encounters is the file system hierarchy.

Unlike Windows, which organizes storage using drive letters such as C: and D:, Linux uses a single directory tree beginning at the root directory:

Some important directories include:

/
├── home
├── etc
├── var
├── usr
├── tmp
└── opt
Key Directories

/home

Contains user directories and personal files.

cd /home

/etc

Stores system configuration files.

cd /etc

/var

Contains logs and frequently changing data.

cd /var/log

Understanding these directories is essential when configuring databases, troubleshooting services, and locating system logs.

Connecting to Remote Servers Using SSH

Secure Shell (SSH) is one of the most important tools for data engineers.

SSH enables users to connect to remote Linux servers over a network securely.

Example:

ssh username@server-ip

In the practical assignment, a remote server was accessed using:

ssh root@159.65.222.96

After authentication, commands could be executed directly on the server.

SSH is commonly used to:

Administer cloud servers
Deploy applications
Manage databases
Monitor infrastructure
Execute scripts remotely

Because most production systems run remotely, SSH is an essential skill for every data engineer.

Essential Linux Navigation Commands

Efficient navigation is crucial when working with Linux systems.

Display Current Directory
pwd
Example output:
/home/dorinem

List Directory Contents
ls
Display Detailed File Information
ls -la

This command shows:

File permissions
Ownership
File size
Modification date
Hidden files
Change Directories

Move to root directory:
cd /

Move to home directory:
cd ~

These commands help users navigate large server environments efficiently.

Managing Files and Directories

Data engineers constantly work with files containing logs, datasets, scripts, and configuration settings.

Create a Directory
mkdir assignment
Create a File
touch sample.txt
Copy Files
cp sample.txt backup.txt
Rename Files
mv backup.txt renamed.txt
Delete Files
rm renamed.txt

These commands form the foundation of file management in Linux environments.

Understanding Linux Users and Permissions

Security is a critical aspect of data engineering.

Linux uses users, groups, and permissions to control access to resources.

Identify Current User
whoami
Display User Information
id
View Group Membership
groups
Modify Permissions
chmod 755 script.sh

Permission values represent:

7 = Read + Write + Execute
5 = Read + Execute
5 = Read + Execute

Permissions help prevent unauthorized access to critical systems and data.

Working with System Information

Understanding system resources is important when managing databases and processing large datasets.

Display Hostname
hostname
Display Operating System Information
cat /etc/os-release
Display Kernel Information
uname -a
Check Disk Usage
df -h

The "-h" option displays values in human-readable format.
Example:
Filesystem Size Used Avail
/dev/sda1 50G 10G 40G

Check Memory Usage
free -h

This command provides insight into memory allocation and availability.

Monitoring system resources helps ensure data workloads run efficiently.

Networking Fundamentals for Data Engineers

Data systems communicate across networks, making networking knowledge essential.

View IP Addresses
ip addr
Test Connectivity
ping google.com
Display Listening Ports
ss -tulnp

This command shows:

Open ports
Active services
Listening network processes

Understanding networking enables data engineers to diagnose connectivity issues and verify service availability.

PostgreSQL Administration on Linux

Databases are central to data engineering.

In the practical assignment, PostgreSQL was used as the relational database management system.

Verify PostgreSQL Installation
psql --version
Example:
psql (PostgreSQL) 16.14
Check PostgreSQL Status
sudo systemctl status postgresql

View PostgreSQL Clusters
pg_lsclusters
Example:
Ver Cluster Port Status
16 main 5432 online

This confirms that PostgreSQL is running and accepting connections.

Creating Users and Databases

A common database administration task is creating users and databases.

Create PostgreSQL User
CREATE USER dorinem WITH PASSWORD 'StrongPassword123';
Create Database
CREATE DATABASE dorinem;
Grant Privileges
GRANT ALL PRIVILEGES ON DATABASE dorinem TO dorinem;

This separates database ownership and access control from the default administrative account.

Creating Schemas

Schemas help organize database objects.

As part of the assignment, a staging schema was created.

CREATE SCHEMA staging;

A staging schema is commonly used as a temporary landing area before data is transformed and loaded into production tables.

Typical workflow:

Raw Data
↓
Staging Schema
↓
Transformation
↓
Production Tables

This approach improves data quality and pipeline reliability.

Loading Sample Data into PostgreSQL

Once the database and schema are created, data can be loaded.

Create Table
CREATE TABLE staging.sales (
id SERIAL PRIMARY KEY,
product VARCHAR(50),
quantity INT,
price NUMERIC,
sale_date DATE
);
Insert Data
INSERT INTO staging.sales
(product, quantity, price, sale_date)
VALUES
('Laptop',2,1200,'2025-01-01'),
('Mouse',5,25,'2025-01-02'),
('Keyboard',3,45,'2025-01-03');
Verify Data
SELECT * FROM staging.sales;

This process mirrors real-world ETL workflows where data is ingested into staging environments before processing.

Secure File Transfers Using SCP

Data engineers frequently move files between local machines and servers.

Secure Copy Protocol (SCP) enables encrypted file transfer.

Upload File to Server
scp sales.csv dorinem@159.65.222.96:/home/dorinem/
Download File from Server
scp dorinem@159.65.222.96:/home/dorinem/sales.csv

SCP is commonly used for:

Dataset uploads
Log retrieval
Configuration backups
Script deployment

Because SCP uses SSH, data remains encrypted during transfer.

Troubleshooting Common Linux Issues

Throughout the assignment, several common Linux challenges were encountered.

Directory Not Found

Incorrect: cd var/log
Correct: cd /var/log

The leading slash indicates an absolute path.

Returning Home

Incorrect: cd~
Correct: cd ~

Command Not Found

When running Linux-specific commands on macOS, users may see:
zsh: command not found

This highlights the importance of distinguishing between local and remote environments.

Understanding these errors improves troubleshooting efficiency.

Best Practices for Data Engineers Using Linux

To work effectively in Linux environments, data engineers should:

Use SSH keys instead of passwords whenever possible.
Follow least-privilege access principles.
Organize files logically.
Regularly monitor disk and memory usage.
Keep databases and packages updated.
Document all administrative changes.
Use version control systems such as Git.
Automate repetitive tasks using shell scripts.

Adopting these practices improves security, reliability, and maintainability.

Conclusion

Linux is more than just an operating system; it is the foundation of modern data engineering infrastructure. Whether managing databases, transferring files, monitoring resources, or administering cloud servers, Linux skills are indispensable.

Through practical exercises involving SSH access, PostgreSQL administration, schema creation, data loading, Linux command execution, and secure file transfers, we can see how Linux directly supports everyday data engineering responsibilities.

As organizations continue to generate and rely on increasing volumes of data, professionals who understand Linux will be better positioned to build scalable, secure, and reliable data solutions. Mastering Linux fundamentals is, therefore, one of the most valuable investments an aspiring data engineer can make.