DEV Community: Mueni

Ch 6: Working With Tables and Other SQL Need-To-Knows

Mueni — Sat, 14 Mar 2026 08:39:05 +0000

Creating SQL tables

When creating tables, you need to define the columns and their data types.

CREATE TABLE table_name(
  column1 datatype,
  column2 datatype,
  column3 datatype,
)

We are going to call our table customer_data, and it will have the columns customer_id, customer_name, and dob.

CREATE TABLE customer_data(
  customer_id INT PRIMARY KEY,
  customer_name VARCHAR(50),
  dob datatype DATETIME
);

Inserting data into the table

This is the syntax for inserting multiple rows into a table:

INSERT INTO table_name (column1, column2, column3)
VALUES
(value1,value2,value3),
(value1,value2,value3);

For our customer data:

INSERT INTO table_name (customer_id, customer_name, dob)
VALUES
(1, 'Jacky','25-06-1980'),
(2,'Mike','02-10-2003');

Viewing data in a table

You use the command SELECT to view data in a table. To see all data, you use an asterisk*

'SELECT * FROM table_name`

If you want to view data from a specific column, you replace the asterisk with the column name:
SELECT column_name FROM table_name;

SQL need-to-knows

NULL in SQL does not mean zero. It means unknown or missing value.
When using SQL tools like DBeaver, user CTRL+ENTER to run the current query (where the cursor is).
Always terminate SQL queries with a semicolon (;). This signals the end of a statement.
When inserting strings and varchar values in a table, put them in single quotes.
SQL commands are often written in capital letters in tutorials. This is not required, but it improves readability and helps distinguish commands from table or column names.

In the next posts, I will be exploring various SQL commands in no particular order.

Ch 5: Creating tables, schemas,

Mueni — Wed, 11 Mar 2026 16:49:03 +0000

We are using DBeaver as our GUI to interact with our database. We have already created the PostgreSQL dtabase service using Aiven.

The first step is powering on the database service in Aiven. Just head to Aiven, then services, and on the database you created, click on the three dots in the action tab and select 'power on service'.

Once the connection is running, head over to DBeaver. Right-click on your database, select SQL Editor, then open a New SQL script.

This should open a new script where we will write our SQL queries to create the database, schema, and tables.

1.Create the database

Inside the database service we create with Aiven, we can create several databases. So our first step is to create the database, which will host all our schemas and tables.

CREATE DATABASE *database_name*;

Run your script by selecting the script and hitting the 'execute SQL query' button.

Refresh the databases tab, and you should see your database on the tab.

2.Create a schema

In PostgreSQL, data is organized in rows and columns, essentially in tables. You can group these tables in schemas.

A schema defines how different tables connect and keeps them organized.

Before you create a schema, you need to navigate into the database we created otherwise, it will be created in the defaultDB database.

Right-click on the database you created, select SQL Editor, then open a New SQL script

Run this SQL command to ensure you are in the right database;

SELECT current_database();

Once you confirm you are in the correct database, create the schema.

CREATE SCHEMA *schema_name*;

3.Create table

Navigate to the schema you created with this command.

SET search_path to *schema_name*;

This should ensure you create the table inside the schema. Next, we create the table.

CREATE TABLE *table_name* (
    column1 datatype,
    column2 datatype,
    column3 datatype,
    ...
);

To see the table we created, use the command:

SELECT * FROM *table_name*;

In the next post, I will share some SQL tricks I have learnt in my few days interacting with it.

Ch 4: Connecting Aiven and DBeaver

Mueni — Tue, 03 Mar 2026 07:12:01 +0000

We are building a Postgres database, which we hosted in Aiven. To interact with it, we have decided to go for the GUI platform, DBeaver, which will enable us to visually interact with the database.

1.Download DBeaver

If you are using Windows, you can find DBeaver on the Microsoft Store. For Mac and Linux users, you can download it from the website. It is an open-source platform, so you can get it freely.

2.Create a connection to Postgres

Select the create connection Icon and then select PostgreSQL as the database to connect to

3.Copy connection information from Aiven

You will get this page in DBeaver.

You need to navigate to Aiven and copy the connection information. Click on your Aiven service, and it should bring you to a page where you see connection information.

From Aiven, copy the host, port, database name, user name, and password and paste them into the respective fields on DBeaver. Remember to keep connected by as 'host' in DBeaver.

4.Test the connection in DBeaver

After you have connected everything, first test the connection before clicking finish with the button on the left. A successful test should give you this:

If the test was not successful, check the details you copied again and ensure each is correct. Click on finish,

You should be able to see your database on the left panel of the platform. If not, you can click on the window tab and select Database Navigator.

You can now interact with your database in DBeaver.

Next, we will look at how to create tables, schemas, etc and dive a little into SQL.

Ch 3: Creating a Postgres Database Service with Aiven

Mueni — Tue, 03 Mar 2026 06:35:50 +0000

Just a recap of the previous post:

Postgres - the DBMS we are using to create our database
Aiven - where we are hosting our Postgres database service
DBeaver - the GUI we are using to interact with the database

Create an Aiven account
We will be creating our Postgres database service with Aiven, so the initial step is to get an Aiven account.
Create an Aiven service
Next, you need to create a service, which is essentially a database service.

You need to select a PostgreSQL service type because we are creating a Postgres database service.

Aiven offers subscription tiers, but you can start with the free tier for your first project, which allows you to create one service - 'database'.

Once created, you should be able to see it under services.

In the next post, we explore how to connect Aiven to DBeaver, enabling you to interact with the database service visually.

Ch 2: DBeaver, Postgres, Aiven, and Other SQL Shenanigans

Mueni — Mon, 02 Mar 2026 07:04:03 +0000

When working with data, you need a way to store it, a way to access it, and maybe even share it. The platforms that allow this are called Database Management Systems (DBMS).

One way to store it is in the form of rows and columns eg. customer table where the rows have customer names, ids, etc. When data is stored in this way, it is called structured data.

Data might also not have rows and columns, eg, images and videos. This means the information is just clustered together, which is often called unstructured data.

You choose the DBMS to use based on the type of data you are storing, whether it is structured or unstructured, and how you need to query and scale it.

One of the DBMS for structured data is Postgres - this is the one I am currently learning.

You can install Postgres and host it yourself, but managing it requires a lot of work. So instead, it is easier to use a cloud hosting platform like Aiven, which hosts this database for you.

Now that you have your database and it is already hosted, you need a way to talk to it (search through it, edit, etc.). And the language you use here is called SQL: Structured Query Language.

You can use terminals to do the querying or write code for it, but one of the most visual ways to do this is with a GUI (Graphical User Interface) platform like DBeaver.

So now you have 3 key things, and you can comfortably build and run your database:

Postgres- your DBMS
Aiven - which hosts the DBMS in the cloud for you
DBeaver - your visual access to the data

In the next post, we will explore how to create your database, host it, and interact with it visually.

Ch 1: The Introduction - Journey to Data Analyst

Mueni — Mon, 02 Mar 2026 06:22:22 +0000

I am a product manager who recently got interested in the data space. A month ago, I started my journey with a bit of documentation required by the program I am taking.

However, I realized a few of the articles I have written on data analysis were required by the program, so I did not necessarily write them because I wanted to.

So, here is my new series where I share some snippets of what I learn in this journey. I am hoping this series of articles will serve both as documentation for me and as a reference point for another individual in the same journey.

How Analysts Translate Messy Data into Insights in Power BI

Mueni — Mon, 09 Feb 2026 03:58:16 +0000

By the time data reaches analysis, it is rarely clean, complete, or decision-ready.

Most datasets arrive bloated with information that doesn’t matter, fields that don’t change outcomes, values in the wrong format, and tables that were never designed to work together.

Trimming

One of the earliest and most consequential decisions an analyst makes is deciding what to remove.

Raw datasets often contain:

Fields that are never used in analysis
Columns that duplicate information in different forms
Values that technically exist but do not affect decisions
Historical artifacts that no longer reflect how the business operates

Keeping everything “just in case” doesn’t make a model more robust—it makes it more fragile. Every unnecessary column increases cognitive load, slows performance, and creates more surface area for confusion.

Analysts curate the dataset so that what remains directly supports the questions the business needs to answer.

Transformation

After pruning comes transformation.

Data often needs to be reshaped into forms that allow comparison and aggregation:

Dates converted into standardized months and quarters
Text values normalized into consistent categories
Numeric fields corrected, scaled, or bucketed
Business-specific groupings created where none existed

These transformations create shared meaning.

Without consistent representations, two users can look at the same dashboard and walk away with different interpretations. Transformation is how analysts eliminate that risk before it reaches the dashboard.

DAX

DAX is the most opinionated part of Power BI.
DAX is often treated as the most technical part of Power BI.
In reality, it’s the most opinionated.

Every DAX measure encodes a point of view:

What counts as revenue
When an order is considered complete
How discounts, refunds, or exceptions are handled
Which date defines performance

DAX is where ambiguity must be deliberately eliminated.

If two stakeholders can interpret a metric differently, the measure isn’t finished. Well-designed DAX protects users from uncertainty by enforcing consistent logic across every filter, slicer, and drill-down.

Conclusions

Dashboards should make the conclusion obvious. They guide attention, shape interpretation, and influence decisions.

A dashboard built with the right mindset:

Surfaces what matters most, not everything available
Highlights patterns, risks, and exceptions
Makes comparisons intuitive
Reduces the effort required to draw conclusions

Users shouldn’t have to mentally calculate what a chart means or debate which number to trust. The structure of the dashboard should make the story clear and the implications visible.

At every stage—cleaning, transforming, modeling, measuring, and visualizing—the analyst is doing the same thing: reducing the risk of misinterpretation.

The ability to turn messy, ambiguous data into a reliable foundation for decisions is the real value of analytics work.

Schemas and data modeling in Power BI

Mueni — Mon, 02 Feb 2026 13:36:26 +0000

In Power BI, data modeling refers to the art of transforming raw data into a structured and relational model ready for analysis and reporting. It involved creating dimension tables (or schemas as they are called in Power BI) and using DAX (Data Analysis Expressions) to create calculated measures.

Components of data modeling

There are 4 key components of data modeling in Power BI:

1. Data Preparation (Power Query)

This involves cleaning the data in preparation for its analysis.
It involves removing duplicates, handling missing values, standardizing formats, splitting/merging columns, creating derived columns, renaming columns, and filtering our unnecessary rows, among others.

2. Relationships

This is the connection of tables based on how they relate to one another.

3. DAX Measures

These are the calculations done for business metrics.
They define business logic and KPIs that respond to filters.
Measures calculate totals, ratios, percentages, and react to filters and slicers.

4. Model view

This is the architecture diagram where you can arrange your tables into a schema, review and edit relationships, manage hierarchies, and validate filter directions.

At the heart of Power BI data modeling are schemas, fact and dimension tables, and relationships.

Fact and dimension tables

Fact tables store measurable events - things you want to analyze. For example, sales transactions, payments, orders, app events, etc.

Dimension tables provide context for analysis. For example, customer, product, geography, etc. They are descriptive rather than numeric.

Relationships

We have already created relations as a definition of how tables interact. There are two key concepts of relationships:

Cardinality:
One-to-many (dimension → fact) ✅
Many-to-many ⚠️ (use only when unavoidable)
Filter direction
Single direction (recommended)
Both directions (advanced, risky)

Schemas

A schema refers to the structure and organization of data within a data model. There are two key types of schemas: star schema and snowflake schema.

MS Excel for Data Analytics

Mueni — Sun, 25 Jan 2026 17:03:00 +0000

I have always used Excel to perform basic tasks, but only recently did I discover its ability to perform somewhat complex analytics. In this article, I explore three of the lesser-known but powerful features of Excel.

Data Validation

Data Validation helps you restrict what can be entered in a cell. This is useful for preventing errors in large datasets.

This is powerful because it ensures data quality and consistency, and reduces manual cleaning later

Dashboards

Dashboards combine charts, tables, and key metrics into a single, interactive visual display. You can create dashboards with Excel by combining tables, charts, and multiple charts.

You can also use Slicers to filter multiple charts at once. This is one of the most powerful features of Excel because it lets you see patterns, trends, and anomalies at a glance, making decision-making faster and more informed.

Lookup Functions

-> VLOOKUP, HLOOKUP, XLOOKUP
Lookup functions let you find values in large datasets quickly.

Example with XLOOKUP:

=XLOOKUP("John", A2:A100, C2:C100, "Not Found")

This searches for "John" in the first column and returns the corresponding value from the Sales Amount column.

Revolve- fusion 360

Mueni — Sat, 29 Jan 2022 12:14:30 +0000

Revolve creates a mirror of an image and creates a solid body on both the body and the reflection. It is one of the most amazing fusion 360 features because it enables symmetry in objects.

Some other concepts I recently learned are as follows:

extruding to another surface:
choose extrude to and select the bottom surface. This ensures that if you ever need to move that body, no constrain will stop you
shell: uneven(curved) bodies for even extruding cut

projecting existing geometry:
This allows you to work on a specific geometry without losing some faces. It is important when using spline. When using spline, it is possible for some faces to be lost but by projecting the geometry, you can tell which face is not visible in your sketch.

The rectifier circuit

Mueni — Thu, 27 Jan 2022 10:35:26 +0000

So, after a week of understanding current and power calculations, capacitance values, and the resistor aspects of a rectifier, I finally came up with a circuit for the rectifier as in the diagram below.

Building a Rectifier Circuit

Mueni — Wed, 19 Jan 2022 12:40:05 +0000

In my projects when I need to convert AC to DC, I use already made rectifiers like the HLK-10M05 module. There are some disadvantages that come with using the module as a stand alone, the biggest being just that; it is too big.
As an alternative, you can build a rectifier circuit into your project. The circuit works to convert AC to specified DC power.

The components you require to make the circuit are:

4 diodes whose connection converts the DC to AC without changing the voltage
1 Zener diode to drop down the voltage
a smoothening capacitor to smoothen out the irregular DC voltage
two resistors to trim the voltage output to get the exact voltage and current you need. When selecting these resistance, your target should be high current low resistance.

With just 4 components, you make a AC-DC converter that takes up about 20mm of space. I will later update the circuit for my rectifier with an update on its functionality on my project.