DEV Community: Faith Njenga

SQL Is Surviving, Franklin: When Tables Start Talking

Faith Njenga — Tue, 08 Sep 2026 13:09:53 +0000

So, you survived the basics of SQL.

You learned how to create tables, insert data, retrieve records, update rows, and hopefully avoid accidentally updating the entire database.

Because if you’ve ever run a DELETE statement without a WHERE clause and survived the experience, congratulations. You’ve already developed character.

But eventually, SQL gives you another problem: “Cool. Now get information from multiple tables.”

And suddenly, SELECT * FROM students; doesn’t feel so powerful anymore.

Welcome to JOINs.

Previously in SQL…

In Part 1, we looked at the basic categories of SQL commands:

DDL - Data Definition Language
DML - Data Manipulation Language
DQL - Data Query Language

We also worked with individual tables. But real databases rarely keep everything in one giant table.

Imagine trying to store an entire school database in one table:

student_name	class	teacher	subject	score
Brian	Form 4A	Mr. Kamau	Mathematics	78
Mercy	Form 4A	Mr. Kamau	Mathematics	91
Kevin	Form 3B	Ms. Achieng	Biology	84

At first, this looks convenient. Until you realize that you’re repeating class, teacher, subject, and student information over and over again.

That’s where relational databases come in.

Why Do We Have Multiple Tables?

A good database tries to avoid unnecessary repetition. Instead of putting everything into one massive table, we separate related information.

Let’s say our school is called Greenwood Academy. We could split our data into three clean tables:

`students`

student_id	student_name	class_id
1	Brian	101
2	Mercy	101
3	Kevin	102

`classes`

class_id	class_name	teacher_id
101	Form 4A	501
102	Form 3B	502

`teachers`

teacher_id	teacher_name	subject
501	Mr. Kamau	Mathematics
502	Ms. Achieng	Biology

Now the information is nicely separated. But there’s a problem. If someone asks: “Show me each student’s name, their class, and their teacher.” - no single table contains all three pieces of information.

So how do we bring them together? You guessed it. JOINs.

Before JOINs: Primary Keys and Foreign Keys

Before we start joining tables like we’re collecting Pokémon, we need to understand what connects them. That connection usually comes from primary keys and foreign keys.

Primary Key

A primary key uniquely identifies each record in a table. For example, student_id might uniquely identify every student. No two students should ever share the same student_id.

Foreign Key

A foreign key is a column that refers to a primary key in another table.

For example, look at the relationship between our students and classes tables:

students                 classes
-----------              -----------
student_id               class_id  <--- (Primary Key)
student_name                ↑
class_id    ────────────────┘ 
   │
   └─> (Foreign Key)

That’s the exact relationship SQL uses to connect the tables. Think of it as the database saying: “I don’t have the whole story here, but I know exactly where the rest of it lives.”

So… What Exactly Is a JOIN?

A JOIN allows you to combine rows from two or more tables based on a related column.

SELECT
    students.student_name,
    classes.class_name
FROM students
JOIN classes
    ON students.class_id = classes.class_id;

Here, we’re saying: “Take the students table, find the class that belongs to each student, and bring the information together side-by-side.”

The magic happens right here: ON students.class_id = classes.class_id. That is the structural bridge we are using to link them.

Meet the JOIN Family

SQL has several types of JOINs. The main ones you’ll encounter are:

INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL OUTER JOIN

Let’s break down how they behave differently.

1. INNER JOIN

INNER JOIN returns only the records that have a match in both tables. Think of it as: “Only show me people who are actually on both guest lists.”

SELECT
    students.student_name,
    classes.class_name
FROM students
INNER JOIN classes
    ON students.class_id = classes.class_id;

Imagine our dataset looks like this:

Students Table:

student_id	student_name	class_id
1	Brian	101
2	Mercy	101
3	Kevin	102
4	Sarah	999

Classes Table:

class_id	class_name
101	Form 4A
102	Form 3B

Sarah’s class_id is 999. Since there is no class with a class_id of 999 in the classes table, Sarah disappears from the results entirely.

The INNER JOIN basically says: “If we can’t find a perfect match, drop the row.”

2. LEFT JOIN

A LEFT JOIN returns all records from the left table, and the matching records from the right table. If there is no match, SQL fills the right-side columns with NULL.

SELECT
    students.student_name,
    classes.class_name
FROM students
LEFT JOIN classes
    ON students.class_id = classes.class_id;

Using the same data from before, Sarah would now appear in our results:

student_name	class_name
Brian	Form 4A
Mercy	Form 4A
Kevin	Form 3B
Sarah	NULL

Because the students table is written first (on the left side of the JOIN keyword), the LEFT JOIN mandates: “I don’t care if you find a match or not. Everybody from my table is coming along.”

This is incredibly useful when you want to look for missing or unlinked records. For example:

SELECT
    students.student_name,
    classes.class_name
FROM students
LEFT JOIN classes
    ON students.class_id = classes.class_id
WHERE classes.class_id IS NULL;

Now we are specifically filtering for students who don’t have a matching class. SQL has officially entered detective mode.

3. RIGHT JOIN

RIGHT JOIN is the exact opposite of LEFT JOIN. It keeps all records from the right table, even if there isn’t a matching record in the left table.

SELECT
    students.student_name,
    classes.class_name
FROM students
RIGHT JOIN classes
    ON students.class_id = classes.class_id;

This states: “Every class stays. If a student belongs to it, bring them along. If a class has zero students, display the class name anyway and fill the student column with NULL.”

That said, RIGHT JOIN isn’t used as often in the real world. Why? Because you can always rewrite it as a LEFT JOIN simply by flipping the order of your tables in the query:

SELECT
    students.student_name,
    classes.class_name
FROM classes
LEFT JOIN students
    ON students.class_id = classes.class_id;

Same idea, different direction. Keeping everything as a LEFT JOIN usually makes scripts much easier to read from top to bottom.

4. FULL OUTER JOIN

A FULL OUTER JOIN goes completely all-in. It keeps matching records, unmatched records from the left table, and unmatched records from the right table.

Essentially: “Bring everybody to the party. We’ll figure out the missing pieces later.”

SELECT
    students.student_name,
    classes.class_name
FROM students
FULL OUTER JOIN classes
    ON students.class_id = classes.class_id;

This query will simultaneously surface:

Students assigned to classes.
Students without classes (like Sarah).
Dynamic classes that currently have no students assigned to them.

Important Note: Not every database system supports FULL OUTER JOIN directly out of the box. For example, MySQL does not support it natively (you have to fake it using a UNION of a Left and Right Join), whereas engines like PostgreSQL do. Always verify which database system you're building for.

JOIN Cheat Sheet

If you forget everything else, keep this quick mental framework handy:

JOIN Type	What does it keep?	Quick Analogy
`INNER JOIN`	Only matching records	The intersection only
`LEFT JOIN`	Everything from the left table + matches	Left table gets full VIP treatment
`RIGHT JOIN`	Everything from the right table + matches	Right table gets full VIP treatment
`FULL OUTER JOIN`	Everything from both tables	Universal invite list

The ON Clause: Where the Magic Happens

You’ve probably noticed this piece of syntax repeating: ON students.class_id = classes.class_id.
The ON clause tells SQL exactly how the tables are related.

JOINing Three Tables

What if we want to bridge all three of our tables together to find out which teacher is teaching which student?

You aren't limited to joining just two tables at a time. You can chain JOIN statements sequentially to build broader horizons. SQL will process them linearly, using the cumulative dataset from the first join to connect to the next table.

Here is how you link students to classes, and then connect those classes to their respective teachers:

SELECT 
s.student_name,
c.class_name,
t.teacher_name,
t.subject
FROM students sINNER JOIN classes cON s.class_id = c.class_idINNER JOIN teachers tON c.teacher_id = t.teacher_id;

Pro-Tip: Table Aliasing

Notice the letters s, c, and t right after the table names? Those are aliases. They save you from having to type out long table names like students. student_name repeatedly. By declaring FROM students s, you tell SQL: "For the rest of this query, I'll just use s as a shorthand for this table." It keeps your multi-table joins beautifully organized and readable!

Surviving SQL Is Winning, Franklin

Faith Njenga — Mon, 27 Jul 2026 08:28:43 +0000

I was given a PostgreSQL assignment.

The brief said:

You are now the DBA for Greenwood Academy.

No handover meeting. No existing documentation. No senior DBA saying, “Here is what happened last quarter.”

Just a school, some messy data, and a list of tasks.

So I opened DBeaver.

For anyone new to this: I was using DBeaver to connect to PostgreSQL and run my SQL queries. PostgreSQL is the database system. DBeaver is the tool I used to work with it.

I wrote the instructions in DBeaver.

PostgreSQL did the actual work.

And, as I would soon discover, PostgreSQL was very committed to doing exactly what I told it to do.

SQL Has Departments

Before touching Greenwood Academy, I needed to understand the different types of SQL commands.

SQL commands are grouped according to what they do. Think of them as different departments in the same organisation. They all work with the database, but they have different jobs.

Category	Full Name	Main Job	Examples
DDL	Data Definition Language	Creates and changes database structures	`CREATE`, `ALTER`, `DROP`, `TRUNCATE`
DML	Data Manipulation Language	Adds, changes, and removes data	`INSERT`, `UPDATE`, `DELETE`
DQL	Data Query Language	Retrieves data	`SELECT`
DCL	Data Control Language	Manages permissions and access	`GRANT`, `REVOKE`
TCL	Transaction Control Language	Manages database transactions	`COMMIT`, `ROLLBACK`, `SAVEPOINT`

The quick version:

DDL → Build the structure
DML → Change the data
DQL → Ask questions
DCL → Control access
TCL → Decide whether changes stay

We will not use every command in this article. Greenwood Academy did not require me to manage database permissions or recover from a transaction disaster.

This assignment focused mainly on:

DDL → CREATE, ALTER
DML → INSERT, UPDATE, DELETE
DQL → SELECT

We will also use WHERE, AND, OR, BETWEEN, IN, NOT IN, LIKE, COUNT(*), and CASE WHEN.

So, with the departments introduced, it was time to start with the construction department.

SECTION A: DDL - Building Greenwood Academy

DDL stands for Data Definition Language.

DDL is about the structure of the database.

Before asking:

Which students are in Form 3?

we need somewhere to store the students.

Before storing exam results, we need somewhere to put the results.

So the first task was to create a schema:

CREATE SCHEMA greenwood_academy;

A schema helps organise database objects. I wanted the Greenwood Academy tables grouped together instead of scattered around the database like files named:

final.sql
final2.sql
final_final.sql
final_final_use_this_one.sql

Then I set the schema as the working location:

SET search_path TO greenwood_academy;

Now PostgreSQL knew where I wanted to work.

Creating the Tables

The students table needed a unique ID, names, gender, date of birth, class, and city:

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    gender VARCHAR(1),
    date_of_birth DATE,
    class VARCHAR(10),
    city VARCHAR(50)
);

The student_id is the PRIMARY KEY, meaning each student must have a unique identifier.

Because names are not always enough.

You can have:

Brian Ochieng
Brian Ochieng
Brian Ochieng

But their IDs should still be different.

The database does not care which Brian sits near the window. The database wants the ID.

Next came the subjects table:

CREATE TABLE subjects (
    subject_id INT PRIMARY KEY,
    subject_name VARCHAR(100) NOT NULL UNIQUE,
    department VARCHAR(50),
    teacher_name VARCHAR(100),
    credits INT
);

The UNIQUE constraint on subject_name helps prevent duplicate subject names.

Because we probably do not need:

Mathematics
Mathematics
Mathematics

unless Mathematics has somehow become three different departments.

Finally, the exam results:

CREATE TABLE exam_results (
    result_id INT PRIMARY KEY,
    student_id INT NOT NULL,
    subject_id INT NOT NULL,
    marks INT NOT NULL,
    exam_date DATE,
    grade VARCHAR(2)
);

Instead of repeatedly storing the student's full name and every other detail in every exam record, we use IDs.

That keeps the data more organised and saves us from trying to determine whether these are four different people:

Amina Wanjiku
Amina Wanjiku 
AMINA WANJIKU
Amina Wanjiku N.

Sometimes the database problem is not the database.

Sometimes it is the person who entered the data.

Then the Requirements Changed

Once the tables were created, someone realised the students table needed a phone number.

So we added one:

ALTER TABLE students
ADD COLUMN phone_number VARCHAR(20);

Then the credits column needed a new name:

ALTER TABLE subjects
RENAME COLUMN credits TO credit_hours;

Then the phone number was no longer needed:

ALTER TABLE students
DROP COLUMN phone_number;

Added.

Renamed.

Removed.

The database had barely settled down.

This is a realistic introduction to working with requirements. Someone says:

“We need this column.”

You add it.

Then:

“Actually, we don't need it.”

You remove it.

Then, three months later:

“Why doesn't the column exist anymore?”

This is why database work requires technical skills and the ability to remain calm while requirements perform a complete Nairobi matatu route change.

SECTION B: DML - Putting Data Into the Tables

DDL built the structure.

Now we needed data.

DML stands for Data Manipulation Language.

The main commands are:

INSERT
UPDATE
DELETE

This is where the database starts becoming useful.

It is also where you start reading your queries twice.

Maybe three times.

`INSERT`: Adding the Data

The first student:

INSERT INTO students
(student_id, first_name, last_name, gender, date_of_birth, class, city)
VALUES
(1, 'Amina', 'Wanjiku', 'F', '2008-03-12', 'Form 3', 'Nairobi');

Then the remaining students were inserted.

The data included students from Nairobi, Mombasa, Kisumu, Nakuru, and Eldoret.

Then came the subjects.

Then the exam results.

At this point, the database had data.

Which meant it was time for the data to be wrong.

Esther Moved From Nakuru to Nairobi

Esther Akinyi had moved from Nakuru to Nairobi.

The database still had her old city.

So:

UPDATE students
SET city = 'Nairobi'
WHERE student_id = 5;

The WHERE clause tells PostgreSQL exactly which student to update.

Student number 5.

Esther.

Not everyone.

Now look at this:

UPDATE students
SET city = 'Nairobi';

That updates every student.

Amina moves to Nairobi.

Brian moves to Nairobi.

Cynthia moves to Nairobi.

Everyone moves to Nairobi.

Mombasa is finished.

Nakuru is finished.

Eldoret is now a historical concept.

This is why a missing WHERE clause can ruin your career.

The database does not stop and ask:

“Are you sure you meant all 10 students?”

It does exactly what you wrote.

Which is admirable.

Until you realise what you wrote.

The 49 That Was Actually a 59

Result number 5 had the wrong mark.

The database said 49.

The correct mark was 59.

So:

UPDATE exam_results
SET marks = 59
WHERE result_id = 5;

One record.

One correction.

Done.

Now imagine this:

UPDATE exam_results
SET marks = 59;

Every exam result is now 59.

The student who scored 95?

59.

The student who scored 78?

59.

The student who scored 49?

Also 59.

We have not corrected the data.

We have created a very strange grading policy.

This is the moment you start appreciating the humble WHERE clause.

The Canceled Exam

Result number 9 had been canceled:

DELETE FROM exam_results
WHERE result_id = 9;

One result removed.

The dangerous version:

DELETE FROM exam_results;

That deletes every exam result.

No warning.

No popup.

No person from IT appearing beside you asking:

“What exactly are you doing?”

The database trusts you.

Perhaps too literally.

A good habit is to check the row before changing or deleting it:

SELECT *
FROM exam_results
WHERE result_id = 9;

If the correct row appears, then delete it.

The same principle applies to updates:

SELECT *
FROM students
WHERE student_id = 5;

Then:

UPDATE students
SET city = 'Nairobi'
WHERE student_id = 5;

First, inspect.

Then, change.

Because fixing one student's city is a normal database task.

Explaining why the entire school now lives in Nairobi is a different kind of meeting.

SECTION C: DQL - Asking the Database Questions

Now we get to SELECT.

DQL stands for Data Query Language.

The database has the data.

We ask questions.

To find Form 4 students:

SELECT *
FROM students
WHERE class = 'Form 4';

To find subjects in the Sciences department:

SELECT *
FROM subjects
WHERE department = 'Sciences';

To find exam results with marks of 70 or above:

SELECT *
FROM exam_results
WHERE marks >= 70;

To find female students:

SELECT *
FROM students
WHERE gender = 'F';

This is the basic idea behind WHERE.

You describe what you want.

The database checks the rows.

It returns what matches.

No manually scanning through hundreds of spreadsheet rows and hoping you did not accidentally leave a filter active from yesterday.

`AND`: When Both Conditions Must Be True

The assignment asked us to find students who were in Form 3 and from Nairobi:

SELECT *
FROM students
WHERE class = 'Form 3'
AND city = 'Nairobi';

Both conditions must be true.

A Form 3 student from Kisumu does not qualify.

A Nairobi student in Form 4 does not qualify.

You can add more conditions:

SELECT *
FROM students
WHERE gender = 'F'
AND class = 'Form 3'
AND city = 'Nairobi';

Now all three conditions must be true.

`OR`: When Either Condition Works

Now we want students in Form 2 or Form 4:

SELECT *
FROM students
WHERE class = 'Form 2'
OR class = 'Form 4';

Form 2?

Include.

Form 4?

Include.

Form 3?

Not this time.

The difference is:

AND → all conditions must be true
OR  → at least one condition must be true

Two small words.

Very different results.

SECTION D: Better Ways to Filter Data

Sometimes the question is not:

Is this value exactly equal to that value?

Sometimes we need a range, a list, or a pattern.

That is where BETWEEN, IN, NOT IN, and LIKE become useful.

`BETWEEN`: Working With a Range

To find exam results between 50 and 80:

SELECT *
FROM exam_results
WHERE marks BETWEEN 50 AND 80;

This includes both boundaries:

50 → included
65 → included
80 → included
49 → excluded
81 → excluded

You could also write:

WHERE marks >= 50
AND marks <= 80;

That works.

But BETWEEN is cleaner when working with a range.

It can also be used with dates:

SELECT *
FROM exam_results
WHERE exam_date BETWEEN '2024-03-15' AND '2024-03-18';

`IN`: Checking Against a List

Suppose we want students from Nairobi, Mombasa, or Kisumu.

We could write:

WHERE city = 'Nairobi'
OR city = 'Mombasa'
OR city = 'Kisumu';

Or:

SELECT *
FROM students
WHERE city IN ('Nairobi', 'Mombasa', 'Kisumu');

IN is useful when you have a list of acceptable values.

It is shorter, easier to read, and saves you from writing a query that looks like it is negotiating with itself.

`NOT IN`: Excluding Values

Now find students who are not in Form 2 or Form 3:

SELECT *
FROM students
WHERE class NOT IN ('Form 2', 'Form 3');

This excludes those two classes.

Everyone else remains eligible.

Sometimes the easiest way to describe what you want is to describe what you do not want.

SQL understands that too.

`LIKE`: Searching for Patterns

Suppose we want students whose first name starts with A or E:

SELECT *
FROM students
WHERE first_name LIKE 'A%'
OR first_name LIKE 'E%';

The % is a wildcard.

It means there can be anything after this.

The assignment also asked for subjects containing the word Studies:

SELECT *
FROM subjects
WHERE subject_name LIKE '%Studies%';

The % appears on both sides because the word can occur anywhere in the subject name.

This is cleaner than manually writing:

WHERE subject_name = 'Computer Studies'
OR subject_name = 'Business Studies'
OR subject_name = 'Social Studies';

and then remembering another subject later.

The wildcard does the searching.

We do not have to manually list every possible value.

SECTION E: `COUNT(*)` - Let the Database Do the Counting

How many students are currently in Form 3?

We could count them manually.

But we have a database:

SELECT COUNT(*)
FROM students
WHERE class = 'Form 3';

How many exam results have marks of 70 or above?

SELECT COUNT(*)
FROM exam_results
WHERE marks >= 70;

COUNT(*) counts the number of rows that match the condition.

This is one of those moments where using a computer is a sensible decision.

The database is already sitting there with the rows.

Let it count them.

You have other things to do.

SECTION F: `CASE WHEN` - Giving Raw Data Some Meaning

The database stores marks.

People like categories.

For example:

80 and above → Distinction
60–79        → Merit
40–59        → Pass
Below 40     → Fail

We can write:

Now the query can give us:

Marks	Performance
85	Distinction
72	Merit
59	Pass
39	Fail

The important part is that we did not change the original marks.

If the database stores:

it still stores:

The query simply creates a new label while displaying the results.

That means we can change the classification rules later without rewriting the original marks.

The raw data stays as it is.

The interpretation can change.

We can do the same thing with student classes:

A student in Form 3 is still in Form 3.

We are simply creating another way to describe that student.

No rewriting the original data.

No creating another spreadsheet.

The query handles it.

The Part I Found Most Important

The assignment covered a lot of SQL.

But the part I kept thinking about was the difference between:

UPDATE students
SET city = 'Nairobi'
WHERE student_id = 5;

and:

UPDATE students
SET city = 'Nairobi';

One updates Esther.

The other relocates the entire school.

The same applies to:

DELETE FROM exam_results
WHERE result_id = 9;

versus:

DELETE FROM exam_results;

One removes a canceled exam.

The other removes every exam result.

SQL is not difficult because the database is trying to trick you.

SQL is difficult because the database is perfectly willing to do exactly what you told it to do.

Even when what you told it to do was a terrible idea.

So before running an UPDATE or DELETE, especially on important data, check what you are about to affect:

SELECT *
FROM students
WHERE student_id = 5;

Then make the change.

Same condition.

First, inspect.

Then, change.

This is not advanced database engineering.

It is simply a good habit.

And good habits are useful when you are tired, it is 2:00 AM, and somebody has just said:

“It is a very small change.”

What Greenwood Academy Taught Me About SQL

The assignment started with an empty database.

By the end, I had:

Created the greenwood_academy schema.
Created the students table.
Created the subjects table.
Created the exam_results table.
Added a phone_number column.
Renamed credits to credit_hours.
Removed the phone_number column.
Inserted student, subject, and exam result records.
Updated Esther's city from Nakuru to Nairobi.
Corrected a mark from 49 to 59.
Deleted a canceled exam result.
Filtered data using WHERE.
Combined conditions using AND and OR.
Used BETWEEN for ranges.
Used IN and NOT IN for lists.
Used LIKE with wildcards.
Counted records using COUNT(*).
Created performance labels using CASE WHEN.

That sounds like a lot.

But when broken down, SQL was asking me to do a few very direct things:

CREATE → Make something
INSERT → Put something in
UPDATE → Change something
DELETE → Remove something
SELECT → Show me something

The difficult part was not simply knowing that these commands exist.

The difficult part was being precise about what they should affect.

That is where the WHERE clause quietly becomes one of the most important things in the entire assignment.

You can learn the syntax.

You can memorise the commands.

But at some point, you have to ask:

What exactly will this query change?

That question is probably more useful than memorising another SQL keyword.

Final Thoughts

I started with an empty PostgreSQL database and a list of questions.

By the end, I had a better understanding of how the different parts fit together.

DDL built the structure.

DML put data into it and changed it.

DQL let me ask questions about it.

BETWEEN, IN, NOT IN, and LIKE made those questions more specific.

COUNT(*) handled the counting.

CASE WHEN let me add useful labels without changing the original data.

And WHERE reminded me that SQL is extremely literal.

The database will not rescue you from your own query.

It will not stop and say:

“I think you meant one row.”

It will execute.

So I have taken one practical rule away from Greenwood Academy:

Before I run an UPDATE or a DELETE, I want to know exactly which rows I am touching.

Because fixing Esther's city is a normal database task.

Moving every student in the school to Nairobi is a different kind of meeting.

And I am not trying to attend that meeting.

Surviving SQL is winning, Franklin.

Commitment Issues: Tracking Changes, Romantic Promises, and Surviving the Git Staging Crate

Faith Njenga — Sun, 12 Jul 2026 20:29:09 +0000

Introduction

Commitment is a funny thing.

Some people date for years before making it official. Some rush into relationships they regret two weeks later. And then there are those people who keep saying, “Let’s just see where this goes,” until everyone involved is emotionally exhausted.

Believe it or not, Git has the exact same personality.

If you’ve never heard of Git before, think of it as a really smart time machine for your code. It remembers every meaningful change you make, lets you revisit older versions when things go wrong, and makes it possible for multiple developers to work on the same project without accidentally overwriting each other’s work. In short, Git is a version control system and one of the most important tools you’ll use as a developer.

But Git doesn’t commit to every little change you make.

Every time you edit a file, Git looks at your changes and asks, “So… are we serious, or are we just experimenting?” If you’re not ready to commit, your changes wait patiently in the staging area,the software equivalent of “It’s complicated.” If you are ready, Git wraps everything up into a commit: a permanent promise that says, “Yes, I meant to do this.”

The problem is that most beginners are taught Git like they’re preparing for a spelling bee.

git add
git commit
git push

Memorize the commands.
Don’t ask questions.
Hope nothing catches fire.

Then, the first time they accidentally commit the wrong files, forget to stage something important, or stare at git status like it’s judging their life choices, panic sets in.

Here’s the secret: Git isn’t complicated because the commands are difficult. It’s confusing because no one explains why they exist.

So in this article, we’re going to stop memorizing magic words and start thinking like Git. We’ll open the mysterious staging crate, decide which changes deserve commitment, rescue a few questionable life decisions, and discover why version control is less about remembering commands and more about making good choices.

Because in Git-as in relationships not everything you change deserves a lifelong commitment.

Episode 1: "So... What Are We?" (`git init`)

Picture this.

You've met someone amazing. You've exchanged numbers. You've talked for hours. You've even started imagining your future together.

But then a friend asks,

"Wait... are you two actually together?"

Cue the awkward silence.

Because until someone makes it official, you're just... existing in each other's lives.

Your project has the same identity crisis.

You create a new folder. You add a few files. Maybe you've already written hundreds of lines of code. It looks like a software project, smells like a software project, and behaves like a software project.

Git, however, looks at it, shrugs, and says,

"I've literally never seen this project before."

That's where this little command changes everything.

git init

The moment you hit Enter, your project finally introduces itself.

Git quietly creates a hidden folder called .git a place you'll almost never open but one you'll rely on every single day.

Think of it as the relationship archive.

Every promise you keep (commits), every detour you take (branches), every reunion after a disagreement (merges), and every embarrassing decision you'd rather pretend never happened... Git stores it all in there.

It's basically that friend who remembers everything.

"Remember that tiny CSS change you made three weeks ago at 2:17 a.m.? Yeah... I still have it."

And if one day you decide to delete the .git folder?

Congratulations.

You didn't delete your project.

You just convinced Git that the two of you never met.

Episode 2: The Honest Bestie (`git status`)

Every relationship has that one best friend.

The one who somehow knows everything before you tell them.

"Girl... I saw what happened."

"You're leaving out the important part."

"Who exactly is this?"

They're not being nosy.

They're just trying to save you from making questionable decisions.

Git is that best friend.

Ever since you made things official with git init, Git has been quietly paying attention to your project. It isn't changing anything or making decisions for you it simply watches, remembers, and waits for your next move.

So naturally, the first thing you should ask your bestie is,

"Okay... what's the situation?"

git status

Git takes one look around your project and gives you the unfiltered truth.

"I noticed you've brought some new people into your life..."

"...care to introduce me?"

Those red filenames aren't errors.

They're untracked files files that Git has noticed but hasn't officially been introduced to yet.

Think of it this way.

You've started hanging out with new friends, but you've never brought them around your bestie. So when your best friend sees them for the first time, they're naturally like,

"Wait... who are these?"

Until you make those introductions, Git won't remember those files or include them in your project's history. If they disappear tomorrow, Git can't help you because, as far as it's concerned...

"I've never met them."

Now, let's address the one person who always shows up uninvited.

Take a look at the list again.

See .ipynb_checkpoints/?

If you use Jupyter Notebooks, you've probably met this little troublemaker. Every time you save your notebook, Jupyter quietly creates checkpoint files behind the scenes as a backup.

It's useful.

It's harmless.

But it has absolutely no business following you to GitHub.

It's like bringing your best friend to meet your family... and they show up with their cousin, their neighbor, and someone's emotional support hamster.

Nobody asked for all that.

Episode 3: "Before You Make It Official..." (`git add`)

You know what happens after you've introduced someone to your best friend?

The interrogation begins.

"So... do you actually like them?"

"Are they staying?"

"Should I bother learning their name?"

"Is this another one of your 'it's different this time' situations?"

Your bestie isn't being dramatic.

They're just trying to figure out who deserves a permanent place in your life.

Git has reached that exact moment.

In the last episode, git status showed Git everyone hanging around your project. It noticed the new files, gave you the side-eye, and asked,

"Okay... who exactly are these people?"

Now it's waiting for your answer.

This is where the staging area comes in.

Think of it as the beautifully crafted wooden crate sitting on your desk.

It's not the commitment box.

Not yet.

It's simply where you place the files you're considering committing.

Inside the crate are envelopes labelled:

Maybe later
Not ready yet

Sound familiar?

That's because staging isn't commitment.

It's intention.

It's you telling Git,

"These are the changes I'm thinking about making official."

Maybe you're confident and want to stage everything.

git add --all

Git happily picks up every new file, every modified file, and every deleted file, placing them neatly into the staging crate.

You'll also see developers use the shorter version.

git add -A

Same result.

Just fewer keystrokes.

Now suppose you're standing inside your project and simply want Git to gather everything from right here.

That's where the famous little dot comes in.

git add .

That tiny . simply means,

"Start from this folder and bring everything below it."

If you're already at your project's root directory, git add . will usually give you the same result as the previous commands.

Now imagine your bestie peeks inside the staging crate.

"Okay... these are the people you're serious about?"

You nod.

Your bestie smiles.

"Cool."

"Just remember... putting them in the crate doesn't mean you're engaged."

Exactly.

Nothing has been committed yet.

The letters are written.

The envelopes are packed.

The crate is full.

But the promise hasn't been made.

That... happens in the next episode.

Episode 4: The Plot Twist - "Wait... They're Coming Too?"

You've spent all this time deciding who belongs in the staging crate.

Your bestie helped.

The letters are neatly packed.

Everything looks perfect.

So, just before making the biggest commitment of your Git journey, you ask one last question.

"Bestie... can you check the crate one more time?"

Git happily obliges.

git status

Everything is green.

At first, you're relieved.

"Yes! We did it!"

Then your bestie squints at the list.

"...Hold on."

"Why is that coming with us?"

You lean over the crate.

Inside, alongside your Python scripts and notebooks, you spot...

A massive software installer ZIP file.
An MP3 music file.
.ipynb_checkpoints/ quietly pretending it belongs.

Awkward.

This is what happens when you stage everything without checking what's inside the crate.

It's the Git equivalent of sending wedding invitations to everyone in your contacts list...

...including your ex.

...your nail-tech.

...and the plumber who fixed your sink three years ago.

Technically, they'll all show up.

But should they?

Absolutely not.

If you commit these files and push them to GitHub, your repository becomes unnecessarily large, cloning takes longer, and anyone reviewing your project will immediately wonder,

"Did they actually mean to include all of this?"

The good news?

Git understands that we all make questionable decisions.

It even gives you a graceful way to unpack the entire staging crate without throwing away your actual files.

git rm -r --cached .

Think of this command as your best friend standing at the door collecting invitations.

"Nope... you're not coming."

"You either."

"Definitely not you."

The files stay safely on your computer.

Git simply removes them from the staging area, giving you the chance to pack the crate again but this time with a little more intention.

Because here's the thing about commitment...

Changing your mind before you make the promise is a lot easier than trying to explain yourself afterward.

Episode 5: "So... Are We Official Now?" (`git commit`)

The crate is packed.

Your bestie has checked the guest list twice.

The random ZIP file has been politely uninvited.

.ipynb_checkpoints/ finally got the hint.

Everything inside the staging crate is exactly where it should be.

Your bestie looks at you.

"Last chance."

"Are you absolutely sure these are the changes you want to keep forever?"

You take one final look.

You smile.

"Yeah... I'm ready."

This is the moment Git has been waiting for.

git commit -m "First commit"

With a single command, Git carefully closes the staging crate.

It seals every envelope with wax.

It writes today's story into its diary.

And just like that...

Your changes become part of history.

This is called a commit.

Think of a commit as a photograph.

Not of what your project will become...

...but of exactly what it looked like at this very moment.

No matter how many changes you make tomorrow, next week, or six months from now, Git can always return to this snapshot.

That's why commits are so powerful.

They're promises to your future self.

They're your way of saying,

"I know this version works."

"I know why I made these changes."

"If I ever mess things up, I can always come back here."

And before you start thinking,

"Great! My code is finally on GitHub!"

Not so fast.

Your commit lives only on your computer.

It's like writing a heartfelt love letter, sealing it in an envelope, and placing it safely inside your desk drawer.

The promise is real.

It's official.

But nobody else knows about it...

Yet.

Episode 6: The "We Need to Talk" Conversation (`git switch`, `git diff` & `git restore`)

Every healthy relationship has one.

The conversation.

Not the breakup.

Not the proposal.

The one that starts with,

"Can we talk?"

Maybe someone wants to rephrase a promise.

Maybe someone realizes they could have said something better.

Maybe they're just trying to figure things out.

The good news?

In Git, you're allowed to have those conversations without ruining the original relationship.

That's exactly what branches are for.

Think of a branch as an alternate timeline.

Instead of rewriting history, Git lets you create a safe little universe where you can experiment, make mistakes, and change your mind, all without touching your stable project.

So let's create one.

git switch -c remove_promise

Your bestie raises an eyebrow.

"So... you're telling me you're about to edit the promise?"

Relax.

We're not changing the original.

We're just exploring a "What if?" scenario.

Inside our index.html, we tweak the heartfelt letter.

Nothing dramatic.

Just enough to make Git notice.

Before doing anything else, let's ask our honest bestie what they think.

git status

Git immediately notices the change.

"I saw that."

"You changed something... but you haven't told me to remember it yet."

That's all git status is doing.

It's keeping you honest.

Now imagine you stare at your edit for a minute and suddenly think,

"What was I even trying to say?"

We've all been there.

Fortunately, Git doesn't judge questionable decisions.

It simply offers an escape route.

git restore index.html

Think of git restore as crumpling up the draft letter before anyone reads it.

The edits disappear.

Your last committed version remains untouched, as if the conversation never happened.

But let's pretend we actually like our new wording.

How do we see exactly what changed?

That's where git diff becomes your bestie's favorite gossip tool.

git diff index.html

Git opens the receipts.

No guessing.

No "I think I changed this."

Just the facts.

The red line, marked with a -, shows what you're saying goodbye to.

The green line, marked with a +, shows the new version waiting to take its place.

In our case, we upgraded the promise by adding the word "always."

One tiny word.

One huge difference.

And before we make that promise official, Git gives us one final chance to review every single change.

Because the strongest relationships and the strongest codebases aren't built by never making mistakes.

They're built by being able to see exactly what changed before making another commitment.

Episode 7: The Reunion (`git merge`)

Every couple reaches this moment.

You've had the difficult conversation.

You've rewritten the letter.

You've taken some time apart to figure things out.

Now comes the big question.

"Can we bring this back into the relationship?"

Remember, all of our edits have been living safely on a separate branch. Think of it as taking a weekend getaway to clear your head. The main branch has been patiently waiting at home, completely untouched.

Before the reunion can happen, we need to go back home.

git switch main

Your bestie smiles.

"Welcome back."

Now it's time to bring everything we worked on back into the main story.

git merge remove_promise

Git takes one look at both branches.

Then it shrugs.

"Well... that was easy."

Since nobody touched the main branch while we were away, Git doesn't have to play relationship counselor.

There are no arguments.

No awkward conversations.

No one saying,

"That's not what I meant."

Instead, Git simply moves the main branch forward until it catches up with your new commit.

This is called a fast-forward merge.

Think of it like coming back from a weekend trip.

Your family didn't repaint the house.

Nobody rearranged the furniture.

Everything is exactly where you left it.

So fitting back in is effortless.

Your upgraded promise is now part of the main story, and Git quietly records another successful chapter in its diary.

Your bestie closes the diary with a satisfied smile.

"Perfect."

"Everything is finally where it belongs."

You smile back...

Then your bestie asks one last question.

"...So, are you planning to keep this relationship a secret?"

Because right now, everything we've done exists only on your computer.

Our story is complete.

Our commits are safe.

Our branches are merged.

But the rest of the world still has no idea our project exists.

Let's change that.

Episode 8: Going Public (`git push`)

So far, our entire love story has been wonderfully... private.

We've met Git.

We've introduced the important files.

We've packed the staging crate.

We've made our commitment.

We've even survived a little relationship drama and reunited stronger than before.

But there's one tiny problem.

Everything we've done still lives only on our laptop.

It's like getting engaged...

...and forgetting to tell your family.

Before we can share our project with the world, Git needs to know where the world is.

So head over to GitHub, create a repository, and copy its URL.

Then introduce your local repository to its new home.

You'll notice we give GitHub a nickname:

origin

Think of it as saving someone's contact in your phone as "Bestie" instead of memorizing their entire phone number.

origin is simply a friendly alias for your GitHub repository, making it much easier to communicate with later.

Now comes the moment we've been building toward.

git push -u origin main

With one command, your local story leaves your laptop for the very first time.

Your commits travel to GitHub.

Your repository finally has an online home.

And your project is no longer just your story.

It's a story others can now see, clone, learn from, and contribute to.

Our relationship is officially public.

But don't get too comfortable...

Because sharing your work with the world introduces a whole new kind of drama.

Pull requests.

Remote repositories.

Authentication.

Merge conflicts.

And teammates who somehow edit the exact same line you were working on.

We'll save that tea for the next chapter.

Is It a Crime to Want a Flawless Connection? Mastering Local and Cloud Databases in Power BI

Faith Njenga — Tue, 07 Jul 2026 00:34:24 +0000

INTRODUCTION

When you first start learning Power BI, life is relatively simple. You click "Get Data," select a spreadsheet or a CSV file sitting on your desktop, and start building charts. It’s a comfortable bubble.
But as you start working on bigger projects, you quickly realize a hard truth: in the real world, most data doesn't live in loose spreadsheets on your desktop. It lives inside database servers.

The moment you try to step out of your comfort zone and connect Power BI to a database, the setup process can feel incredibly intimidating. Connecting to a database running right on your own computer is usually pretty smooth. But the second you try to connect to a secure cloud platform like Aiven PostgreSQL, Power BI will often drop a massive roadblock right in your face: The SSL Certificate Error.
Suddenly, you are staring at a scary security alert, wondering why a cloud server won't let you access your data. If you are tired of hitting connection walls and just want your tools to talk to each other, don't panic. You don't need to be a senior database engineer or a coding expert to fix this.
In this beginner-friendly guide, we are going to break down the exact, click-by-click steps to handle simple local connections and show you the ultimate Windows trick to make your computer automatically trust your cloud database.

Part 1: Moving Data into the Database (DBeaver Import)

Before Power BI can turn your data into visual charts, we need to upload our file into our database. We will use a free tool called DBeaver to act as our database manager. Think of DBeaver as a file explorer, but specifically built for databases. It lets us drag, drop, and view our tables without writing a single line of code.

Step 1.1: Logging Into Your Database

Whether your database is running quietly on your own laptop or hosted online via Aiven, DBeaver needs to know where to find it.

Open DBeaver and click the Plug Icon (New Database Connection) in the top-left corner.
Select PostgreSQL from the list of database types and click Next.
Look at your database settings (either your local installation details or your Aiven Console dashboard) and fill in the blanks:
Host: Use localhost or 127.0.0.1 for your own computer, or paste the long server link provided by Aiven.Port: Type 5432 for local setups, or 24306 for Aiven cloud instances.
Database: Type the name of your target database (the default local one is usually called postgres).
Username & Password: Type in your database login keys.
Click Test Connection. Once a little box pops up with a green success message, click Finish.

Step 1.2: Creating a Folder and Importing Your File

To keep our data organized, we don't want to dump our tables into a messy public folder. Let's create a clean, dedicated space called a Schema. In DBeaver’s left sidebar, expand your database connection.

Right-click on the word Schemas. Select Create New Schema.
Right-click your brand-new schema folder. Select Import Data. Input file and click Next.
Click the folder icon, browse your computer, and select the data file you want to upload.
Click Next through the settings, DBeaver will automatically look at your file and fit the right column structures for you. Click Proceed to execute the upload.

Part 2: The Easy Road (Connecting to a Local Database)

Now, let's open Power BI Desktop. We will start with the easiest, most stress-free connection scenario: linking up to a database running natively right on your own machine (localhost). Because this traffic never leaves your computer, Windows automatically trusts it.

Launch Power BI Desktop and open a blank report canvas.
On the top Home ribbon, click the Get Data button -> select More -> choose PostgreSQL database.
In the Server input box, type your database location using a host:port (for a local setup, this is usually localhost:5432or 127.0.0.1:5432). Type your database name in the box below it.
Choose Import as your data connectivity mode (this pulls a copy of the data into Power BI so your charts load incredibly fast) and click OK.
Switch to the Database credentials tab on the left sidebar, enter your database username and password, and click Connect.

Your tables will instantly pop up in the Navigator window, ready to be loaded into your report

Part 3: The Cloud Roadblock (Bypassing Aiven's Security Alert)

Now, try taking your long Aiven cloud host string, pasting it into that exact same Power BI connection box, and hitting connect. It will fail instantly.
Instead of seeing your tables, Power BI will flash a frustrating SSL/Security Error on your screen.
Why does this happen? Cloud databases like Aiven sit on the internet outside your home network. To protect your information, they force everything to be strictly encrypted. Power BI relies entirely on your Windows operating system to check if a cloud connection is safe. Because Windows doesn't recognize Aiven out-of-the-box, it panics and blocks the pipeline.
To break through this wall, we just need to download a digital security passport (a CA Certificate) from Aiven and slide it into your Windows security vault. Once Windows trusts it, Power BI will too.

Step 3.1: Downloading the Security Passport

Open your web browser and log into your Aiven.io console.
Click on your running PostgreSQL service page.
Scroll down to the Connection Information panel on your dashboard.
Locate the CA Certificate block and click the Download button. This saves a small file called ca.pem to your machine.
Pro-Tip: Don't leave this file in your temporary Downloads folder. Move it to a permanent folder on your computer, because Windows needs to look at this file in the background every single time your Power BI charts refresh.

Step 3.2: Slipping the Certificate into the Windows Vault

Now, we explicitly tell Windows that this cloud database is completely safe.

Click your Windows taskbar search tool, type "Manage computer certificates" (or press Win + R, type certlm.msc, and press Enter) to open the Certificate Manager.
On the left side panel, locate and expand the folder named Trusted Root Certification Authorities.
Right-click the underlying Certificates sub-folder -> Go to All Tasks -> Click Import....
Step into the wizard and click Next. When the file browser opens, change the extension dropdown in the bottom-right corner from "X.509" to "All Files (.)" otherwise, your ca.pem file will stay invisible
Select your ca.pem file, click Next through the default locations, and click Finish. Click Yes on the dramatic Windows security warning pop-up.

Part 4: The Flawless Cloud Connection

With your Windows operating system now validating the security handshake in the background, the connection barrier completely disappears.

Launch Power BI Desktop and open a blank report canvas.
On the top Home ribbon, click the Get Data button -> select More -> choose PostgreSQL database.
Input your Aiven cloud host and port details into the server field.
The Cloud Switch: Because your machine now holds the matching validation key, check the "Use encrypted connection" box.
Click OK, pivot to the Database credentials tab on the left sidebar, enter your administrative username (avnadmin), and paste your cloud service password.
Hit Connect. The data gates open instantly. The Navigator interface will open up beautifully, displaying your custom cloud schema and your loaded tables completely free of security alerts and ready to be turned into stunning dashboards.

Conclusion

Stepping up from basic flat files to structured database servers can feel like a steep learning curve, but it is one of the most valuable skills you can learn. Local connections are fantastic for practicing and testing things quickly on your own machine. But learning how to work with certificates and cloud encryption layers is what prepares you for real-world data environments. You don't need to be a coding genius to build secure pipelines, you just need to know how to configure your security lanes

Relationships Are Hard (Except in Power BI Data Modeling)

Faith Njenga — Tue, 30 Jun 2026 15:04:34 +0000

INTRODUCTION

Power BI is mainly associated with the creation of beautiful, interactive dashboards and reports, but did you know that it has more to it than that? Have a seat as I explain its immense power and what great works it can achieve.

As always, I am here to break it down to a very digestible form.

For the high performance of the reports we see, solid data models are vital. Designing a model correctly ensures fast calculation speed, accurate DAX data analysis, and easy report maintenance.

Data Schemas

schema is the structural blueprint of your data model. It dictates how your data tables are organized and connected.
Before deciding on a schema, you typically break your data into two types of tables:

Fact Tables: The Actions (The Numbers)
Think of a Fact Table as a continuous digital receipt log. It only cares about tracking business events and recording the raw numbers.
Example (Streaming App): Every time someone presses play on Netflix, it logs the event. It records Numeric Data like Duration_Minutes (45) and Pause_Count (2), alongside IDs to link to details.
The Goal: It answers "How much?" or "How many?"
Dimension Tables: The Context (The Text)A Dimension Table is your lookup list. It holds all the descriptive details that give meaning to your raw numbers.
Example (Streaming App): Instead of repeating the movie title, director, release year, and genre on every single play receipt, you store it once in a Movies lookup table next to a unique Movie_ID.
The Goal: It answers "Who?", "What?", or "Where?"

Choosing your Blueprint(Schema)

Now that you have known your Facts from your Dimensions, how do you arrange them in Power BI?
There are 3 schema types. Namely:

Star
Snowflake

Star Schema

The Star schema gets its name because of its shape. You place the number-heavy Fact Table in the center and surround it with your descriptive Dimension Tables like the points of a star.

Why use it: It provides the best query performance, reduces data redundancy, and uses one-to-many relationships that are easy for Power BI to process.

Snowflake Schema

The Snowflake Schema happens when you split your dimension tables into even smaller lookup tables(e.g., a "Product" table branching out into separate "Brand" and "Category" tables).

Why use it: It is useful when you need to optimize storage or map highly complex hierarchical relationships, though it can slow down report rendering.

Flat Schema

This represents a single, massive, consolidated table where all information lives together. This happens automatically when you load a single CSV or flat Excel file into Power BI.

Why use it: It requires no modeling effort and is perfectly fine for small, simple datasets. However, it leads to heavy data redundancy and poor performance on large datasets. Here is a visual representation of the three.

Relationships

The 3 Core Settings of a Relationship
Every relationship you build in Power BI is defined by three main settings:

1. Cardinality (The Type of Connection)

One-to-Many (1:*): The most common and efficient type. One row in your lookup table (e.g., Customers) connects to many rows in your transaction table (e.g., Sales).
Many-to-One (*:1): The same as one-to-many, just viewed from the opposite direction.
One-to-One (1:1): Links single unique rows between two tables. It is rarely used and usually means the tables should just be merged.
Many-to-Many (:): Links non-unique values on both sides. It should be avoided when possible because it can cause unpredictable calculations.

2. Cross Filter Direction (How the Data Flows)

Single Direction: The filter flows only one way—usually from the Dimension table down to the Fact table. This is the safest and fastest setting.
Both Directions (Bi-directional): Filters flow both ways. While powerful, it can severely slow down your reports and create ambiguous data paths.

3. Relationship Status

Active: The primary path Power BI uses to calculate data between two tables. Only one active relationship can exist between any two tables at a time.
Inactive: Secondary paths. They sit in the background and are only triggered when you specifically call them using DAX formulas (like USERELATIONSHIP). Active vs. Inactive Example If your Sales table has a Order_Date and a Ship_Date, you can only link one of them actively to your Calendar table. You would make Order_Date active for your daily sales reports, and keep Ship_Date inactive, activating it only when calculating shipping backlogs.

Power Query Joins: Merging Tables

In Power BI, joins are used to merge two tables permanently into a single, wider table during the data preparation phase.

While relationships connect separate tables in your visual model, joins physically combine your data rows in the Power Query Editor using the Merge Queries feature.

The 6 Types of Joins in Power BI

Power BI offers six different ways to combine your datasets based on a matching column:

Left Outer: Keeps all rows from the first table, and only matching rows from the second table. This is the most common join.
Right Outer: Keeps all rows from the second table, and only matching rows from the first table.
Full Outer(The collector): Keeps all rows from both tables. Unmatched rows display null values for the missing data.
Inner(The matchmaker): Keeps only rows that match in both tables. Any unmatched data is completely discarded.
Left Anti: Keeps rows only present in the first table that do not have a match in the second. It is perfect for finding missing records.
Right Anti: Keeps rows only present in the second table that do not have a match in the first.

Quick Comparison Matrix

Join Type	Records Kept from Table 1 (Left)	Records Kept from Table 2 (Right)	Best Used For
Left Outer	All	Only Matches	Adding descriptive columns to transactions
Right Outer	Only Matches	All	Inverting data priorities
Full Outer	All	All	Combining disparate budgets or targets
Inner	Only Matches	Only Matches	Filtering for strict overlap
Left Anti	Only Unmatched	None	Finding customers who haven't bought yet
Right Anti	None	Only Unmatched	Finding orphaned data in secondary tables

Join vs. Relationship: What's the difference?

It is common to confuse Power Query joins with Data Model relationships. Here is how to differentiate them:

Joins (Power Query): Happen during data ingestion. They alter the physical structure of your data by flattening tables together, which consumes more memory.
Relationships (Data Model): Happen after the data is loaded. They keep tables separate and link them virtually, maximizing Power BI’s performance engine.

Into the Bashlands: Surviving the Big Black Screen Without an Undo Button

Faith Njenga — Mon, 29 Jun 2026 21:44:39 +0000

Introduction

It's not talked about enough: the fear of the terminal window, or in other words, the big black screen.
Where there are no folder icons to double click on, their creation is not just a right-click, no buttons to undo mistakes and a single mistake can trigger an error message that looks like a loud, red alarm. Well, I want you to take a deep breath. The creeping of a sudden wave of imposter syndrome might not be news.
You are not alone in this
I decided to dive into the unknown and become a Data Engineer. If you do not know Linux in this line of Duty is a very crucial tool. Just like you I always thought that the command line was reserved for a certain category that I like call 'Big Boys' also known as cybersecurity experts or people who have memorized every shortcut since 1995. But over the last few weeks I have decided to get my hands dirty with real-world server deployment and yes, I can attest that: The terminal is not a monster. It is just a conversation.
Every time the terminal throws a scary Fatal error to you, it is not judging you but simply pointing out a missing configuration step in plain text. In this article, I am going to take my time and explain the nitty-gritty of what I learned in setting up a remote cloud database from scratch. We will look at my exact real-world configuration, demystify database permissions and show how visual tools like Dbeaver can come to the rescue all these in simple and friendly terms.

Remote Access and Navigating

Because a remote cloud server does not have a monitor or a keyboard attached to it in a physical room, we have to access its brain over the internet. The tool we use is SSH(Secure Shell). This of SSH as a secure, encrypted digital tunnel that stretches from your home laptop keyboard straight into the host operating system.
For my recent project, our remote Ubuntu cloud server was hosted at the public IP address 159.65.222.96

The login Banner
When you first connect via SSH using the administrative root user account, the server greets you with a system status banner:

Don't let this numbers overwhelm you. Look at them as a health dashboard for your remote computer.

usage of /: 4.2% means your server's storage drive is mostly empty. Your data pipelines have plenty of room to breathe.
Memory usage:28% tells you that more that two-thirds of your server's RAM is sitting open and ready to process heavy computational tasks.

Creating an Isolated User & The privilege shift

To practice good security habits and protect our environment, we need to move away from the root account. I created my own isolated user account (faithn) using standard Linux configuration tools.
Here is exactly how I built that user account and gave it administrative sudo privileges while logged in as root:

#1. Create the new user account (The system will prompt you to set a password)
root@assignmentServer:~# adduser faithn

#2. Grant the new user access to administrative powers ('sudo' group)
root@assignmentServer:~# usermod -aG sudo faithn

Securely switch over to the new personal account by nesting a new connection

Look at that prompt change:
'faithn@assignmentServer:~$'.The dollar sign($) means I am now standing safely inside my personal use sandbox. I have my own room, my own security, and I can't accidentally break the rest of the server.

Finding Your Way Around Without a Mouse

Now that we are inside our isolated faithn user account, how do we look around? Because you cannot see any folders or icons, you have to use your keyboard as your eyes. Here are some commands that helped me navigate my new digital apartment:

pwd (Print Working Directory): Tells you exactly where you are standing. The exact location where you are at.

ls -la (List All): This prints out every file and folder in your current directory, including hidden system files that control your settings.

mkdir (Make Directory)- This is equivalent of right-clicking on a desktop screen and selecting New> Folder.
cd (Change Directory)- This is the physical act of walking into a folder.

touch- This instantly lays blank sheets of paper on your desk. It is a fresh text file ready for configuration details or pipeline scripts.

Unmasking the PostgreSQL Prompt

The next major conceptual hurdle for any beginner data engineer is understanding Context Switching.

Once, I confirmed that our database software is installed by running

It was time to log in.

The right way to Login In

Once your user roles and permissions are properly set up the server, logging into your personal database should be clean and direct. You run this command into your regular Linux terminal prompt:

If everything is configured correctly, the server will bypass all administrative barriers and drop you straight into your destination. You will see a clean welcome message and responsive prompt ready for your data queries:

psql (16.14 (Ubuntu 16.14-0ubuntu0.24.04.1))
Type "help" for help.

faithn=#

The First Trap: Getting Stuck in the Prompts

However, when you are first figuring things out, it is incredibly easy to take a wrong turn and find yourself stuck like I was.
I tried to log straight into my database but I instead I fell directly into a classic terminal context trap that puzzles almost every single beginner:

Let's dissect this line by line, because understanding this sequence will instantly remove your fear of database environments.

Decoding the Prompt Symbols ($, #, and -)

The trailing symbols on your prompt lines are visual road signs showing which program is listening to your keyboard :
faithn@assignmentServer:~$ The $ means you are in the Linux Shell. You run standard system commands here like ls or mkdir.
postgres=#The # means you crossed a portal into the PostgreSQL Engine. Linux commands will not work here; the server only accepts SQL code.
postgres-# The dash (-) means PostgreSQL is waiting for you to finish a SQL statement. Typing psql twice triggers this because it isn't a valid SQL command, and the engine thinks you are still typing your sentence.
The Fix: If you ever get trapped on a line with a dash (-#) and your keyboard feels locked, press Ctrl + C. This breaks the waiting loop and returns you to a clean postgres=# prompt.

The Second Trap: The "Role Does Not Exist" Block

Once you exit the prompt loops and confidently try to log into your database using your custom name:

bash/usr/lib/postgresql/16/bin/psql -d faithn

PostgreSQL flatly rejects you with a fatal message:
FATAL: role "faithn" does not exist
This error makes your stomach drop. You think, "But wait, My Linux username is faithn, and I am typing it correctly! Why does it say I don't exist?"

Breaking Down the Trap

This trap happens because Linux users and Database users are completely isolated from each other. Just because the server has a user profile named faithn doesn't mean the PostgreSQL application automatically knows who you are. PostgreSQL tracks its own separate guest list of users, which it calls Roles.
Think of your Linux account as your security badge to get into the main university building. The Database Role is a completely separate guest list held by a second guard standing right outside the locked database room. When you first try to connect, that second guard checks his specific list, doesn't see your name, and locks you out,.
The Clean Solution
To fix this trap, we use the Master Database Admin identity (postgres=#) to officially add your name to the database guest list and create your file storage container:

sql
CREATE ROLE faithn WITH LOGIN SUPERUSER;
CREATE DATABASE faithn OWNER faithn;

By explicitly running these, the two separate systems are perfectly linked. Now when you type sudo -u postgres psql faithn, the database guard sees your name on the list and lets you right in.

Creating the Schema and Importing Data with DBeaver

Now that our database boundaries are unlocked, we can construct our ingestion environment. In data engineering, we use a Schema as a logical container to organize our tables. For this project, we are building a landing bucket called staging.
We log into our database and run a simple, clean SQL script to lay down our structure:

Bypassing Terminal Fatigue

At this point, a classic beginner trap is trying to write massive INSERT INTO statements directly in the command line for dozens of records. It is a massive waste of time, and a single missing comma will break the entire thing. Real-world data engineers don't do everything the hard way, we balance terminal power with graphical comfort. This is the exact moment where DBeaver becomes your ultimate rescue tool. Because we configured our remote cloud server (159.65.222.96) to allow external network connections on port 5432, we can hook DBeaver directly into our backend using our personal faithn credentials. Instead of typing endless lines of code to force our data into the black box of the terminal, DBeaver peels back the scary layer of the command line and gives us a friendly, visual import wizard. I simply selected the mock student CSV spreadsheet generated on my local desktop, clicked through the visual mapping guide, and let the software automatically parse and load all records into our remote cloud database in seconds.

It gives you the absolute best of both worlds: your data is safely running on an enterprise Linux server in the cloud, but you get to interact with it using a beautiful, visual, human-friendly application.
To verify that DBeaver successfully uploaded your records from your local PC to your remote cloud database, you can switch back to your Linux server terminal (faithn@assignmentServer) and run this standard SQL query:

SELECT * FROM staging.students LIMIT 10;

What You Will See on Your Screen

Running that command will print your data cleanly inside your terminal layout, showing you the exact rows you just uploaded through DBeaver:

age |      course      | admission_number | grade | student_id |   gender   |    major    
-----+------------------+------------------+-------+------------+------------+-------------
  22 | Computer Science |      4083        | F     |        1   | Male       | Physics     
  22 | Business         |      7444        | F     |        2   | Female     | Physics     
  18 | Biology          |      4103        | C     |        3   | Female     | Physics     
  26 | Biology          |      7590        | B     |        4   | Female     | Chemistry   
  30 | Psychology       |      1978        | F     |        5   | Female     | Chemistry   
  30 | Biology          |      9396        | C     |        6   | Female     | English     
  24 | Biology          |      5989        | C     |        7   | Non-binary | Mathematics 
  25 | Biology          |      8362        | B     |        8   | Non-binary | Physics     
  25 | Business         |      3275        | D     |        9   | Non-binary | Chemistry   
  21 | Business         |      6182        | B     |       10   | Female     | Physics

DEV Community: Faith Njenga

SQL Is Surviving, Franklin: When Tables Start Talking

Previously in SQL…

Why Do We Have Multiple Tables?

students

classes

teachers

Before JOINs: Primary Keys and Foreign Keys

Primary Key

Foreign Key

So… What Exactly Is a JOIN?

Meet the JOIN Family

1. INNER JOIN

2. LEFT JOIN

3. RIGHT JOIN

4. FULL OUTER JOIN

JOIN Cheat Sheet

The ON Clause: Where the Magic Happens

JOINing Three Tables

Pro-Tip: Table Aliasing

Surviving SQL Is Winning, Franklin

SQL Has Departments

SECTION A: DDL - Building Greenwood Academy

Creating the Tables

Then the Requirements Changed

SECTION B: DML - Putting Data Into the Tables

INSERT: Adding the Data

Esther Moved From Nakuru to Nairobi

The 49 That Was Actually a 59

The Canceled Exam

SECTION C: DQL - Asking the Database Questions

AND: When Both Conditions Must Be True

OR: When Either Condition Works

SECTION D: Better Ways to Filter Data

BETWEEN: Working With a Range

IN: Checking Against a List

NOT IN: Excluding Values

LIKE: Searching for Patterns

SECTION E: COUNT(*) - Let the Database Do the Counting

SECTION F: CASE WHEN - Giving Raw Data Some Meaning

The Part I Found Most Important

What Greenwood Academy Taught Me About SQL

Final Thoughts

Commitment Issues: Tracking Changes, Romantic Promises, and Surviving the Git Staging Crate

Introduction

Episode 1: "So... What Are We?" (git init)

Episode 2: The Honest Bestie (git status)

Episode 3: "Before You Make It Official..." (git add)

Episode 4: The Plot Twist - "Wait... They're Coming Too?"

Episode 5: "So... Are We Official Now?" (git commit)

Episode 6: The "We Need to Talk" Conversation (git switch, git diff & git restore)

Episode 7: The Reunion (git merge)

Episode 8: Going Public (git push)

Is It a Crime to Want a Flawless Connection? Mastering Local and Cloud Databases in Power BI

INTRODUCTION

Part 1: Moving Data into the Database (DBeaver Import)

Step 1.1: Logging Into Your Database

Step 1.2: Creating a Folder and Importing Your File

Part 2: The Easy Road (Connecting to a Local Database)

Part 3: The Cloud Roadblock (Bypassing Aiven's Security Alert)

Step 3.1: Downloading the Security Passport

Step 3.2: Slipping the Certificate into the Windows Vault

Part 4: The Flawless Cloud Connection

Conclusion

Relationships Are Hard (Except in Power BI Data Modeling)

INTRODUCTION

Data Schemas

Choosing your Blueprint(Schema)

Star Schema

Snowflake Schema

Flat Schema

Relationships

1. Cardinality (The Type of Connection)

2. Cross Filter Direction (How the Data Flows)

3. Relationship Status

Power Query Joins: Merging Tables

The 6 Types of Joins in Power BI

Quick Comparison Matrix

Join vs. Relationship: What's the difference?

Into the Bashlands: Surviving the Big Black Screen Without an Undo Button

`students`

`classes`

`teachers`

`INSERT`: Adding the Data

`AND`: When Both Conditions Must Be True

`OR`: When Either Condition Works

`BETWEEN`: Working With a Range

`IN`: Checking Against a List

`NOT IN`: Excluding Values

`LIKE`: Searching for Patterns

SECTION E: `COUNT(*)` - Let the Database Do the Counting

SECTION F: `CASE WHEN` - Giving Raw Data Some Meaning

Episode 1: "So... What Are We?" (`git init`)

Episode 2: The Honest Bestie (`git status`)

Episode 3: "Before You Make It Official..." (`git add`)

Episode 5: "So... Are We Official Now?" (`git commit`)

Episode 6: The "We Need to Talk" Conversation (`git switch`, `git diff` & `git restore`)

Episode 7: The Reunion (`git merge`)

Episode 8: Going Public (`git push`)