DEV Community: Shaquille Mburu

Making sense of data through modelling relationships in Power BI

Shaquille Mburu — Fri, 17 Jul 2026 18:49:00 +0000

Before building dashboards, writing SQL queries, or creating reports, it's essential to organize data in a way that accurately represents real-world entities while capturing their interactions. This process is known as data modeling.

What is a data model?

Picture a three-storey building, and let's call it the database building. Let's label each floor - schema. Since each floor has a unique identity, pair it with its corresponding floor number, so that the first floor becomes schema-1. Each floor or schema has four apartments, labelled table, paired with its unique unit number to become table-1, table-2, and so forth. Each apartment unit or table has a different characteristic defined by size and number of rooms. For instance, schema-1 can have studio units, while schema-2 can have one-bedroom units of different floor areas. Similarly, related tables in a database are hosted in the same schema. This organizational blueprint is referred to as a data model.

How is the Schema structured?

A schema typically takes two structures, dictated the relationships of the tables inside it.

1. The Star Schema.

As the name suggests, it takes the shape of a star. In this setup, there is a central table called the facts table that has a relationship with all the other tables called the dimension tables. A fact table explains what happened, while the dimension table gives context of who, where, when.

2. The Snowflake Schema.

This schema takes the shape of a snowflake. The dimension tables can have related subcategories, which are not directly linked to the fact table.

How are these tables related?

Every table in a database typically includes a column that uniquely identifies each record. This column is referred to as the primary key (PK). Think of it as a unique ID that distinguishes one row from another.

When one table needs to reference information stored in another table, it includes that table's primary key as one of its own columns. This referenced column is known as a foreign key (FK). Foreign keys establish relationships between tables, allowing the database to connect related information while maintaining consistency across the data.

Once tables contain primary keys and foreign keys, the next step is to define the relationships between them. Relationships tell the database how tables are connected, enabling it to combine related information when queries are executed. Think of relationships as the roads that connect tables; without these relationships, retrieving meaningful insights from multiple tables would be difficult and inefficient. Typically, there are three types of relationships:

1. One-to-One Relationship

This relationship suggests that each record in a table is associated with a single record in another table. For instance, a student can have only one identity card and vice versa. However, this relationship is not common.

2. One-to-Many Relationship

This implies that a single record in one table can be associated with multiple records in another table, while each record in the second table relates to only one record in the first. For example, one school can have many students, but each student can only belong to one school.

3. Many-to-Many Relationship

As the name suggests, records in both tables can be associated with multiple records in the other table. For instance, a teacher can teach many students, and a student can be taught by many teachers.

How is data retrieved across tables?

After organizing data in tables and relating them, it is then retrieved based on different use cases using joins. A join combines rows from two or more tables based on a common column, a primary key, and its corresponding foreign key. There are five types:

1. Inner join

This join returns only the records that have matching values from both tables. For instance, the syntax to view students' names (from student's table) and the respective schools (from the schools' table) they attend is as follows:

SELECT
    s.student_name,
    sch.school_name
FROM student_table s
INNER JOIN school_table sch
ON s.school_id = sch.school_id;

2. Left join

This join returns all records from the left table and any matching records from the right table. If no match exists, the columns from the right table are returned as NULL. The syntax will return every student, even if their school information is missing.

SELECT
    s.student_name,
    sch.school_name
FROM student_table s
LEFT JOIN school_table sch
ON s.school_id = sch.school_id;

3. Right join

This join reverses the left join. It returns all records from the right table and matching records from the left table. The syntax will return every school, even if no student is assigned to it.

SELECT
    s.student_name,
    sch.school_name
FROM student_table s
RIGHT JOIN school_table sch
ON s.school_id = sch.school_id;

4. Full outer join

This join returns all records from both tables. Matching rows are combined, while non-matching rows contain NULL values for the missing side. The syntax returns every record from both the students and school tables, whether a match exists or not.

SELECT
    s.student_name,
    sch.school_name
FROM student_table s
FULL OUTER JOIN school_Table sch
ON s.school_id = sch.school_id;

5. Anti joins

This join returns records that do not have a matching record in another table. However, anti join is not a SQL native command and can be implemented using the WHERE and the NOT NULL clauses. The syntax is as follows:

SELECT
    s.student_name
FROM student_table s
LEFT JOIN school_table sch
ON s.school_id = sch.school_id
WHERE sch.school_id IS NULL;

Final Thoughts

Every dashboard and business application begins with a well-designed data model. Understanding how tables relate to one another and how SQL joins retrieve and combine the data across tables, bringing those relationships to life, is a fundamental skill in working with data.

Linux Fundamentals for Data Engineering

Shaquille Mburu — Thu, 25 Jun 2026 00:23:10 +0000

What is Data Engineering?

Data refers to a collection of raw observations. Engineering broadly refers to the art of building and in this case, using data as its raw material. Data engineering focuses on building systems that collect, move, clean and store data, making it available, reliable and useable.

Why Linux?

Linux is a Unix-like operating system (OS), meaning it borrows heavily from its philosophy of simplicity, modularity and code reusability. Its simplistic nature consumes minimal compute resources with superior performance making it widely adopted across servers, cloud infrastructure and big data technologies. This adoption allows native compatibility in engineering tasks making Linux a suitable choice for data engineers.

Linux in Action...

Let's look at a case of accessing a server to set up PostgreSQL database.

1. Server Access.

A remote server is accessed using a secure cryptographic network protocol called Secure Shell - ssh.
The syntax is:ssh <username>@<server_ip_address> while using a password, or ssh -i /path/to/private-key.pem <username>@<server_ip_address> in the case of public/private key pair.

2. User Access.

After accessing the server, start by updating the server using the command sudo apt update.

Create a new user using the command adduser <username>.

Verify the user was created using id <username>

To switch to the new user run the command su - <username>

Alternatively the new user can be accessed directly without going through root by running the command ssh <new_user>@<remote_ip_address>.

The new user can be granted administrative privileges by adding them to the super users (sudo) group, using the command sudo usermod -aG <group_name> <user_name>

3. Setting up PostgreSQL.

Start by checking if PostgreSQL is installed using the command psql --version. If it is not installed run these commands:

sudo apt update
sudo apt install postgresql postgresql-contrib

In this case, version 16 is installed.

Login as its super user - postgres by running the command sudo -i -u postgres. This command should be run from root or an account with adminstrative priviledges.
To access the PostgreSQL shell run the command psql.

Create a user using CREATE USER <username> WITH PASSWORD 'user_password'; and a database using CREATE DATABASE <database_name>;. Grant all access to the user by running the command GRANT ALL PRIVILEGES ON DATABASE <database_name> TO <username>;. Note: Ensure all lines are terminated with a semicolon (;) and correct spelling, otherwise it results in an error.

Confirm the creation of the database using \l.

To change the owner from postgres run the command ALTER DATABASE <database_name> OWNER TO <user_name>.

Confirm the owner was changed to the new user using \l.

To create a schema named staging, move into the database created using the command \c <database_name> or directly move from the postgres admin user using the command psql -U <username> -d <database_name> -h <host_name>. From the database run the command CREATE SCHEMA <schema_name>. List the schemas in the database using the \dn command.

4. Configuring for external tools and traffic.

Data can be uploaded using external tools like DBeaver. Before switching to DBeaver, postgresql needs to be configured to allow external tools and traffic to connect to the databases.

To allow external tools we run the command sudo vim /etc/postgresql/16/main/postgresql.conf. This opens the configuration file in the vim editor.

Edit the listeners to listen globally using the '*' symbol. To save and exit the editor press esc then :wq!.

To allow global traffic connections to the database run the command sudo vim /etc/postresql/16/main/pg_hba.conf to open the file in the vim editor.

Include a configuration line (the last line) which allows global traffic and enforce a password using either md5 or scram-sha-256 authentication. To save and exit the editor press esc then :wq!.

Restart the PostgreSQL service for the configuration changes to be effected.

Check the status of the postgresql service.

5. Uploading Data.

Switch to DBeaver to upload data and establish a connection to PostgreSQL.

To complete the connection, input the credentials created in the linux server.

Test the connection before establishing it. If the credentials match a connection test will pass.

After the connection is established, move to the created database, then select the schema to import data.

Browse the file to be imported.

Select the table mapping.

Complete the importation.

Conclusion

Linux forms the backbone of data engineering enabling native interaction with servers, cloud infrastructure and big data technologies. Its mastery enables engineers to design and build, robust and scalable systems to solve present day challenges.