DEV Community: Joan

The Leading Zero Taught Me More

Joan — Mon, 20 Jul 2026 11:55:10 +0000

My SQL learning log; from "why is my zero gone" to joining four tables without blinking

A few weeks ago, I ran a simple query:

SELECT * FROM customers;

And stared at this:
711223344
That's not a typo; that's supposed to be 0711223344, a normal Kenyan phone number. SQL had quietly eaten the zero.

No error. No warning. Just... gone.

That one broken row taught me more about how SQL actually thinks than any tutorial had up to that point. So instead of writing another "here's what INNER JOIN means" post, I want to walk through the actual bugs that took me from green to dangerous in SQL, because that's genuinely how I learned.
Not from reading theory. From building real tables, breaking them, and going "wait, why."

Stage 1: Data types are not a formality
Turns out my phone_number column was numeric. And numbers don't have leading zeros; mathematically, 0711223344 and 711223344 are the same number, so SQL "helpfully" dropped it.

Lesson: phone numbers are not numbers. They're identifiers. Same goes for anything that might have leading zeros, + signs, dashes, or extensions.
Wrong

phone_number INT

Right
phone_number VARCHAR(15)
This bug is also where I learned to tell the difference between a real data problem and my SQL client just displaying something weird.

Stage 2: Quote your strings, or SQL goes looking for a table called "Amina"

Next wall: I wrote inserts like this—
VALUES (1, Amina, Wanjiku, F, 2008-03-12, Form3, Nairobi)
and got:
ERROR: column "amina" does not exist

Without quotes, SQL doesn't see a value; it sees what it thinks is a column or table name, and goes hunting for it.
Wrong
VALUES (1, Amina, Wanjiku, F, 2008-03-12, Form3, Nairobi)

Right
VALUES (1, 'Amina', 'Wanjiku', 'F', '2008-03-12', 'Form3', 'Nairobi')

Rule that stuck: numbers don't need quotes. Text and dates always do. And a single unclosed quote ('Charlie), instead of 'Charlie'), doesn't just break one value; it can swallow everything after it into one garbled string.

Stage 3: Constraints are guardrails, not decoration

Once the basics worked, I started actually leaning on constraints instead of just adding them.

CREATE TABLE products (
    product_id   SERIAL PRIMARY KEY,
    product_name VARCHAR(150) NOT NULL,
    unit_price   NUMERIC(10,2) NOT NULL CHECK (unit_price > 0),
    stock        INT NOT NULL DEFAULT 0
);

CHECK stops bad prices at the door. DEFAULT means an incomplete insert still lands somewhere sensible. FOREIGN KEY means an order can never point at a customer that doesn't exist.

That last one had a sting in the tail: I couldn't delete a cancelled order without Postgres blocking me, because order_items still had rows pointing at it.

Delete children first DELETE FROM order_items WHERE order_id = 4; DELETE FROM orders WHERE order_id = 4;

New habit: before deleting anything, ask what still points at it.

Stage 4: WHERE clauses and their many wrong spellings
I went through a phase of getting close but not quite right:
Nope
WHERE unit_price => 60, unit_price =< 200;
Yep
WHERE unit_price BETWEEN 60 AND 200;

SQL syntax doesn't do "close enough." But this is also where BETWEEN, IN, NOT IN, LIKE, and ILIKE stopped being abstract keywords and became daily tools, filtering exam scores by range, finding subjects containing "Studies," and learning the hard way that LIKE is case-sensitive in Postgres but ILIKE isn't

Stage 5: GROUP BY ≠ ORDER BY (a genuinely humbling moment)

I once expected this,

SELECT order_id, customer_id
FROM orders
ORDER BY customer_id;

to also sort order_id neatly. It doesn't. ORDER BY controls display order. It has nothing to do with grouping.
**
GROUP BY + HAVING **forced a real mental shift from "show me rows" to "collapse these rows into one answer per group, then filter the groups":

SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 1;

That was the moment aggregation actually clicked.

Stage 6: JOINs and knowing which one
Joining customers → orders → order_items → products into a single query was the milestone that finally made the relational model make sense. But the real lesson wasn't the syntax; it was INNER vs LEFT.

Only matched rows — perfect for totals


SELECT p.product_name, SUM(oi.quantity) AS total_sold
FROM order_items oi
INNER JOIN products p ON oi.product_id = p.product_id
GROUP BY p.product_name;

Keeps orders even with zero items.

SELECT o.order_id, oi.quantity
FROM orders o
LEFT JOIN order_items oi ON oi.order_id = o.order_id;

An INNER JOIN only shows matches on both sides. A LEFT JOIN keeps every row on the left even when there's nothing on the right, filling the gap with NULL. That distinction is how I caught a cancelled order that had zero items attached, before deleting it broke a foreign key.

Stage 7: CASE WHEN — logic, not just filtering

My first CASE attempt had a bug I didn't see coming:

Always NULL; a column can't equal two things at once WHEN class = 'Form2' AND class = 'Form1' THEN 'Junior' Fixed: WHEN class IN ('Form1', 'Form2') THEN 'Junior' WHEN class IN ('Form3', 'Form4') THEN 'Senior'

By the time I was labelling exam results as 'Distinction', 'Merit', 'Pass', or 'Fail' based on marks, CASE WHEN had gone from confusing to intuitive:

`CASE
    WHEN marks >= 80 THEN 'Distinction'
    WHEN marks >= 60 THEN 'Merit'
    WHEN marks >= 40 THEN 'Pass'
    ELSE 'Fail'
END 
AS performance
FROM greenwood_academy.exam_results
ORDER BY result_id;`

Where I am now
I can design a schema from scratch (tables, keys, constraints, sane defaults), populate it, break it, fix it, and query it with filters, aggregates, four-table joins, and conditional logic. But the bigger shift is how I read errors now:

•Syntax error near WHEN? → missing comma before it.
•Foreign key violation on delete? → something still references that row.
•CASE returning NULL everywhere? → logic bug in the conditions, not a data problem.
None of this came from memorising syntax. It came from building real tables: a shop's customers and orders, a school's students and exam results, breaking them in small, specific ways, and stubbornly asking "why."
If you're early in SQL: don't chase the "correct" query on the first try. Build something real. Watch it fail. Read the error closely. That's genuinely where it sticks.

Currently deepening this into Power BI and data analysis work — if you're on a similar SQL-to-analytics path, I'd love to hear what tripped you up first.

Connecting Power BI to PostgreSQL: Local vs Cloud (Aiven)

Joan — Sun, 05 Jul 2026 11:38:34 +0000

If you've ever wondered how a Power BI dashboard gets its data from a "real" database instead of a spreadsheet, this walkthrough covers both ends of that journey: connecting to a local PostgreSQL database and connecting to a cloud-hosted PostgreSQL database on Aiven, including the SSL certificate step that trips a lot of people up.

This was done as part of a data analytics project analyzing sales data for a car dealership (JCars Logistics), but the connection steps here apply to any dataset.

Connecting Power BI to a Local PostgreSQL Database
Step 1: Set up PostgreSQL locally and create the database in DBeaver

Created a new connection in DBeaver pointing to localhost
Created the database and table matching the dataset's schema
Imported the Kenya_crops_dataset CSV file into the table using DBeaver's import tool
Verified the data by querying it directly in DBeaver

Step 2: Connect from Power BI

With the database and data already set up via DBeaver, open Power BI Desktop:

Get Data → More → Database → PostgreSQL database
Enter localhost:portnumber as the server, and your database name
Choose Import or DirectQuery mode
Enter your local database username and password

Step 3: Load and verify

Once connected, select your table(s) in the Navigator window and click Load. Power BI will pull the data in, and you can confirm row counts match what you saw in DBeaver.

That's the local setup, straightforward because there's no network encryption to configure. The cloud version adds one extra layer: SSL.

Part 2: Connecting Power BI to a Cloud PostgreSQL Database (Aiven)
Step 1: Create a PostgreSQL service on Aiven
Log into Aiven, create a new PostgreSQL service, and wait for it to provision. Aiven gives you a host, port, username, password, and database name, plus a downloadable SSL certificate (ca.pem), since Aiven requires encrypted connections by default

Step 2: Load your data into the Aiven database

Rather than connecting Power BI directly for data loading, I used DBeaver as a SQL client to manage the database and import data:

Created a new connection in DBeaver using the Aiven host, port, username, and password. Make sure to test the connection first.
- Created a table matching my dataset's schema
- Imported the Jcars.CSV file directly into the table using DBeaver's import tool
Verified the data by querying it directly in DBeaver

Step 3: Install the SSL certificate

This is the step that's easy to get wrong. Rather than pointing every application separately at the ca.pem file, I installed it once at the operating system level:

Downloaded ca.pem from the Aiven console
Opened Manage User Certificates (Windows) → Trusted Root Certification Authorities
Imported the ca.pem file there

Once installed this way, any application on the machine — including Power BI trusts and verifies the certificate automatically, without needing to browse to the file each time.

Step 4: Connect Power BI to Aiven

Get Data → PostgreSQL database
Enter the Aiven host and port (not localhost this time)
Enter the database name
Enable the encrypted connection option, since the certificate is already trusted at the OS level; Power BI verifies it automatically
Authenticate with your Aiven username and password
Select your table(s) in the Navigator and click Load or Transform.

Local vs Cloud: What's Actually Different

The actual Power BI connection dialog is nearly identical either way; the real difference is the SSL certificate step for the cloud database, and once that's installed at the OS level, it's a one-time setup rather than something you repeat per connection.

Once the data was loaded, I built a Power BI dashboard to analyze revenue, gross profit margin, and delivery performance for JCars Logistics. The main takeaway from this part: connecting Power BI to a cloud database isn't meaningfully harder than a local one; it just adds one certificate step that only needs to be done once.

Power BI Data Modeling: Schemas, Relationships, Joins, and Why They All Matter explanations

Joan — Wed, 24 Jun 2026 13:45:00 +0000

Here’s what I’ve learnt about Power BI: creating visuals is the easy bit. What truly determines your dashboard’s success happens long before you drag your first chart onto the canvas; it’s all about how you design your data model.

If your model isn’t set up correctly, your numbers can be inaccurate in ways that are tough to diagnose. Let’s break it down from the ground up so we can get it right from the start.

What Is Data Modeling?

Data modeling means organizing your tables so Power BI understands how they connect. Before you build any visuals, it’s essential to define which tables relate to each other, how they’re linked, and the direction filters should flow.

Think of it like organising your desk before you start working. If everything is in the right place, the work goes smoothly. If it is not, you spend the whole session looking for things.

A good model is accurate and easy to maintain. A poor one leads to confusing errors. In Power BI, you’ll mainly use two table layouts:

-Star Schema

This is Power BI’s standard. Puts your main fact table in the center, with dimension tables around it (shaped like a star).
Power BI works best with a star schema—filters and calculations are simple, and performance is fast. When unsure, use this structure.

Snowflake Schema

A snowflake schema takes the star schema and breaks down dimension tables further into sub-tables. This adds complexity and usually slows things down in Power BI.
If your source uses a snowflake schema, try to flatten it into a star schema in Power BI before modeling.

Relationships — How Tables Talk to Each Other
A relationship is the link between two tables, defined by a shared column, usually an ID field. In Power BI, you set these up in the Model view, and there are three things to understand about every relationship you create:

1.Cardinality — the ratio between the two tables

-One-to-Many (most common): one row in Table A matches many rows in Table B. One department, many employees. One product, many sales records.

-One-to-One: rare in practice. If two tables have a 1:1 relationship, you can usually just merge them.

-Many-to-Many: Power BI supports it, but it needs deliberate handling. Used carelessly, it produces duplicated or inflated results

2.Cross-filter Direction — which way filters flow

-Single: filters flow one way only, from the dimension table to the fact table. This is the default and the safest option.

Both: filters flow in both directions. Useful with Many-to-Many or role-playing dimensions, but can cause filter ambiguity in complex models. Use it intentionally.

3.Active vs Inactive Relationships
Only one relationship between two tables can be active at a time. In the Model view, active relationships appear as solid lines, while inactive relationships are shown as dashed lines. You can double-click any relationship line to open the Edit Relationship dialog and toggle the Active checkbox directly, making it the permanent default. Alternatively, keep it inactive and call it on demand in DAX using USERELATIONSHIP()
.

Joins — Merging Tables in Power Query
Joins happen in Power Query. When you use Merge Queries, you are combining two tables based on a shared column. The join type you choose determines which rows survive the merge.
Here is every join type in Power Query, and when to actually use it:

In practice, Left Outer is the one you will reach for most often. Keep everything from your main table, bring in what matches from the lookup table, and handle any nulls from there.

How It All Fits Together — A Real Example
Say you are building an HR dashboard from a dataset, with employee records, salaries, bonuses, and departments.
•Your fact table is the employee records — salaries, bonuses, performance scores, and project counts.
•Your dimension tables are Department, Location, and Employee Type.
•You build a star schema with the employee table in the centre.
•Power BI creates one-to-many relationships between each dimension and the fact table.
•If you needed to bring in a separate leave records table, you would use a Left Outer join in Power Query to merge it in before loading.

Now, when someone slices by department or filters by location, Power BI knows exactly how to follow those relationships and return the right numbers every time.

The Takeaway

Many of us start creating visuals in Power BI and later question why our totals do not add up or why the slicers behave unexpectedly. The solution is often found within the model.

Get the schema right. Define your relationships carefully. Understand your joins before you load. The visuals will take care of themselves.

How Excel Is Actually Used in Real-World Data Analysis

Joan — Sat, 06 Jun 2026 06:21:46 +0000

I’ll be honest, when I first heard “Excel for data analysis,” I mentally filed it under things I already know. I’ve spent a few years in tech, pre-sales, project management, and vendor partnerships, and I figured Excel was just spreadsheets with a few formulas. I was wrong.

This week in my Data Science & Analytics course at LuxDev HQ, we worked on an HR dataset. And it was messy. Missing hire dates, duplicate employees, an age listed as 99 and someone’s project count written as “ten” instead of 10. Real data. Real chaos.

Working through that dataset taught me more about data than any definition ever could. Here’s what I’ve learned about how Excel is really used in practice.

But first, what is Excel?

Excel is a spreadsheet tool from Microsoft for storing, organising, and analysing data in rows and columns. Think of it as a super-powered table one where you can run calculations, spot patterns, and clean up messy information, all in one place.

3 Ways Excel Shows Up in the Real World

HR & People Data Management
HR teams use Excel to track headcount, flag performance gaps, and plan promotions. Our dataset spanned 80+ employees, multiple departments, and locations from Nairobi to New York. Excel is what kept the chaos in check and made the analysis possible

Financial Reporting & Payroll
Before any payroll report goes out, someone has to total the right figures, check for missing entries, and make sure everything adds up. That work happens in Excel, using the exact formulas we practiced in class.

Business Decision-Making
When a manager asks “How many permanent staff are in Nairobi?” or “What’s the total bonus payout for Sales?”—those are business questions that Excel answers in seconds, with the right formula.

3 Excel Features I Learned This Week

Conditional Formatting

This highlights cells automatically based on rules you set. I used it to spot outliers—an age of 99, a last promotion year of 1900, and a bonus with a currency symbol. Instead of scanning hundreds of rows, Excel flagged the problems instantly.

ROUND, ROUNDUP
These functions turn rough numbers into professional reporting

SUMIF, SUMIFS, COUNTBLANK, COUNTIF, COUNTIFS — Aggregate Functions with Conditions

This is where Excel starts to feel like actual analysis. These functions let you ask specific questions of your data, not just “what’s the total” but “what’s the total for this specific group, under these specific conditions.”
COUNTBLANK** This counts every empty cell, so before I even started cleaning, I knew exactly how many gaps I was dealing with.

COUNTIF lets me count how many employees belonged to each department, or how many had a performance score of 8 or above. One formula, one condition.
COUNTIFS** took it further, two or more conditions at once. How many employees are Interns, female, above 40 yrs AND based in Nairobi? How many are in IT AND fully remote?

SUMIF gave me the total bonus paid out to the Sales department. =SUMIF(D:D, "Sales", K: K) that’s it. One formula.

SUMIFS added another layer, total bonuses for Permanent staff in the Sales department only. Multiple columns, multiple conditions, one clean result. This is what financial summaries are actually built from.

How This Week Changed the Way I See Data

Before this week, I saw data as something you simply report. Now, I realize data is something you interrogate, challenge, and understand before you trust it. Every inconsistency is a clue to a human story or a broken process. It’s the analyst’s job to find, fix, and ensure the data is trustworthy before anyone makes a decision based on it.

If you’re just getting into data analytics, start with Excel. It will teach you how to think about data long before you write your first line of code.