DEV Community: Shaquille Mburu

Bridging Local and Cloud Databases for Centralized Data Management

Shaquille Mburu — Mon, 21 Sep 2026 19:51:43 +0000

Introduction

Structured data (typically in rows and columns) resides in databases such as PostgreSQL or MySQL. These databases can be hosted locally or offered as a cloud-managed service. When data spans multiple databases or is hosted both locally and in the cloud, a universal data management system provides a centralized platform to handle this dynamic scenario. One of the most common centralized platforms is DBeaver. In this read, we'll set up PostgreSQL locally and as a cloud-managed service with Aiven, access both databases in DBeaver, and link them to Power BI.

The Setups.

Local PostgreSQL setup.

PostgreSQL is an open-source database that is largely contributed to by EnterpriseDB. You can download the installer from EDB shown below. Choose your operating system; in this case, the Windows installer, to start the download.

After completing the download, run the installer as an administrator to get to the setup welcome page.

Proceed to select the components for the installer. You can have all the components checked, or uncheck pgAdmin 4 and StackBuilder since we'll use DBeaver - the universal database management system.

pgAdmin is a PostgreSQL native Graphical User Interface (GUI) used to interact with PostgreSQL databases.

Without a GUI, database interaction can only happen with terminal shell commands using psql.

After selecting the components, you'll be prompted to select your preferred data directory. This directory stores all your database-related data, which can be left as the default.

The next step is choosing your superuser password. The default superuser is postgres. Ensure you provide a password you can easily remember, since it will be required to access the database.

After providing a password, select the port that routes traffic from the internet to the database. The default port is 5432, which can be left as is for a 1st time installation. In case you need to install a later version, you can assign port 5433, but this installation will be left at the default.

Proceed to the preinstallation summary, which details the elements selected and ready to be installed.

After checking the summary, proceed with the installation.

Once the installation is complete, the setup wizard prompts the launch of StackBuilder. This step is optional, as StackBuilder is a useful package manager if you intend to install database drivers or extensions. Click Finish to complete the setup.

Cloud PostgreSQL setup.

One of the cloud-managed PostgreSQL services available is Aiven. To get started, create an account by signing up through the Aiven console.

After signing up, proceed to Services in the left pane to create a new PostgreSQL service using the Create service button at the top right.

Proceed to select the PostgreSQL database.

Select the service tier and proceed to create the service.

The service will first build, then switch to running. This pane provides the credentials required to connect to other software, such as the database management system and visualization tools.

Universal database management system setup.

This system can be installed by downloading the installer from the DBeaver official site. The community version is open-source. Alternatively, it can be downloaded from the Microsoft Store and run the installer.

The installer will start by asking you to select the language.

Proceed to the welcome note and licence agreement.

After agreeing to the licence, you'll select the components and the installation directory.

Finally, proceed with the installation.

The Connections.

Connecting to the localhost and cloud-hosted service.

Launch DBeaver and create a new connection using the plug icon below the File button, select the PostgreSQL database, and proceed to the next pane.

By default, the host is set to localhost on port 5432, and the username is postgres. To complete the connection, enter your password.

As a best practice, test the connection before establishing the database connection.

To connect to the cloud-managed PostgreSQL, create a new connection and use the credentials provided by the Aiven service.

For the connection to be established, the host, port, database name, username, and password are required.

Test the connection before establishing it.

Once the connection is established, create a new script using the SQL editor to write SQL queries.

Connecting to Power BI

To establish a connection with Power BI, launch it and choose the PostgreSQL database using the Get Data button.

For a connection to localhost, the server IP address is 127.0.0.1 on port 5432 or 5433, and the database name is postgres.

The username is postgres; enter your password to connect.

Once the connection is established, access is granted to any existing tables in the database.

To connect to the cloud-managed service, use the hostname and port as the server IP and the database name as defaultdb.

Once the connection is established, access to the existing tables in the database is granted for further manipulation and visualization.

Final thoughts

Data stored across different sources should not remain confined to static, siloed systems. Instead, it should be accessible and transferable between specialized tools and applications, enabling users to manipulate, analyze, and transform the data through secure connections and connection strings.

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.