Data Modeling, Joins, Relationships, and Different Schemas

Emilio Ochieng — Fri, 19 Jun 2026 08:33:38 +0000

Data Modeling, Joins, Relationships, and Different Schemas

Data Modeling

What is Data Modeling?

Data modeling is the process of designing and organizing data structures to define how data is stored, connected, and accessed within a database system.

A data model serves as a blueprint for creating databases by identifying:

Data entities
Attributes
Relationships
Constraints
Business rules

The primary goal of data modeling is to ensure data consistency, accuracy, efficiency, and scalability.

Example

Consider a university system:

Students

Student ID
Name
Email

Courses

Course ID
Course Name

Enrollments

Student ID
Course ID

This model defines how students interact with courses through enrollments.

Types of Data Models

1. Conceptual Data Model

The conceptual model provides a high-level view of business entities and relationships.

Example:

Student → Enrolls In → Course

Characteristics:

Business-focused
No technical details
Easy for stakeholders to understand

2. Logical Data Model

The logical model defines attributes, primary keys, and relationships.

Example:

Student

Student_ID (PK)
Name
Email

Course

Course_ID (PK)
Course_Name

Enrollment

Enrollment_ID (PK)
Student_ID (FK)
Course_ID (FK)

3. Physical Data Model

The physical model describes how data is implemented in a database system.

Example:

CREATE TABLE Student (
    Student_ID INT PRIMARY KEY,
    Name VARCHAR(100),
    Email VARCHAR(100)
);

Characteristics:

Database-specific
Includes indexes and storage details
Optimized for performance

Database Relationships

Relationships define how tables interact with each other.

One-to-One Relationship (1:1)

Each record in one table relates to one record in another table.

Example:

Person ↔ Passport

Person ID	Name
1	John

Passport ID	Person ID
P123	1

A person can have only one passport.

One-to-Many Relationship (1:M)

One record can relate to many records.

Example:

Customer → Orders

One customer can place many orders.

Customer ID	Name
101	Emilio

Order ID	Customer ID
1	101
2	101

Many-to-Many Relationship (M:M)

Many records relate to many records.

Example:

Students ↔ Courses

A student can take multiple courses.
A course can have multiple students.

This requires a bridge table.

Student

Course

Enrollment

Primary Keys and Foreign Keys

Primary Key (PK)

A unique identifier for records in a table.

Example:

Student_ID

Characteristics:

Unique
Cannot be null

Foreign Key (FK)

A field that references a primary key in another table.

Example:

Student_ID

in Enrollment table references:

Student(Student_ID)

Purpose:

Maintains data integrity
Creates relationships

Joins

Joins combine data from multiple tables based on related columns.

INNER JOIN

Returns matching records from both tables.

SELECT *
FROM Customers c
INNER JOIN Orders o
ON c.CustomerID = o.CustomerID;

Result:

Only customers who have placed orders appear.

LEFT JOIN

Returns all records from the left table and matching records from the right table.

SELECT *
FROM Customers c
LEFT JOIN Orders o
ON c.CustomerID = o.CustomerID;

Result:

All customers appear, even those without orders.

RIGHT JOIN

Returns all records from the right table and matching records from the left table.

SELECT *
FROM Customers c
RIGHT JOIN Orders o
ON c.CustomerID = o.CustomerID;

FULL OUTER JOIN

Returns all records from both tables.

SELECT *
FROM Customers c
FULL OUTER JOIN Orders o
ON c.CustomerID = o.CustomerID;

CROSS JOIN

Produces every possible combination.

SELECT *
FROM Products
CROSS JOIN Stores;

Useful for:

Simulations
Testing
Matrix generation

Schemas in Data Warehousing

A schema defines how tables are structured and connected within a database or data warehouse.

Star Schema

The most common schema in Business Intelligence and Power BI.

Structure:

          Customer
              |
Product -- Fact Sales -- Date
              |
           Store

Characteristics:

One central fact table
Multiple dimension tables
Simple structure
Fast query performance

Advantages:

Easy to understand
Optimized for reporting
Ideal for Power BI

Snowflake Schema

A normalized version of the Star Schema.

Structure:

Product
   |
Category
   |
Fact Sales

Characteristics:

Dimension tables are split further
Reduces redundancy
More complex joins

Advantages:

Better data integrity
Reduced storage

Disadvantages:

More joins
Slightly slower queries

Galaxy Schema (Fact Constellation)

Contains multiple fact tables sharing dimension tables.

Example:

Fact Sales
      |
Customer
      |
Fact Inventory

Used when:

Multiple business processes exist
Enterprise-level data warehouses

Advantages:

Supports complex analytics
Highly scalable

Relationships in Power BI

Power BI relies heavily on relationships between tables.

Common Relationship Types:

One-to-Many

Most common.

Example:

Customers → Orders

CustomerID

Many-to-One

Reverse of one-to-many.

Many-to-Many

Used when multiple records match across tables.

Requires careful management to avoid ambiguity.

Best Practices

Use Star Schema whenever possible.
Create meaningful primary keys.
Avoid unnecessary many-to-many relationships.
Use surrogate keys in data warehouses.
Keep fact tables narrow and dimension tables descriptive.
Optimize joins for performance.
Document all relationships clearly.

Conclusion

Data modeling, joins, relationships, and schemas are fundamental concepts in database design and data engineering. Data modeling provides structure, relationships define how data interacts, joins retrieve meaningful information, and schemas organize data efficiently for analytics and reporting.

Linux Fundamentals for Data Engineers.

Emilio Ochieng — Thu, 18 Jun 2026 13:02:38 +0000

The Essential Guide

In the world of data engineering, Python, SQL, and Spark often steal the spotlight. Yet underneath these tools lies the operating system that powers most data platforms: Linux. Whether you're managing Airflow on an EC2 instance, troubleshooting a Kafka cluster, or building ETL pipelines in a Docker container, Linux proficiency directly impacts your productivity and reliability as a data engineer.This guide covers the Linux fundamentals every data engineer should master.

1. Why Linux Matters in Data Engineering

Most cloud data platforms (AWS, GCP, Azure) run on Linux. Self-hosted tools like Apache Airflow, dbt, Spark, Kafka, Flink, and PostgreSQL are designed for Linux environments. Data engineers who understand Linux can:Debug infrastructure issues faster
Write more efficient automation scripts
Secure data pipelines properly
Optimize resource usage
Reduce dependency on DevOps teams

Mastering Linux turns you from a "SQL + Python" engineer into a true infrastructure-aware data professional.

2. Installation & User Management

Choosing the Right Distribution

For data engineering, Ubuntu LTS (22.04 or 24.04) is the most popular choice due to its stability and vast package ecosystem. CentOS/Rocky Linux/AlmaLinux are common in enterprise environments.

Creating a Dedicated UserNever run data pipelines as root.

Create a dedicated user:
bash
sudo adduser dataeng
sudo usermod -aG sudo dataeng # Optional: grant sudo access

SSH Key Authentication (Best Practice)bash

ssh-keygen -t ed25519 -C "dataeng@workstation"
ssh-copy-id dataeng@your-server-ip

Disable password authentication in /etc/ssh/sshd_config for better security.

3. File System & Permissions

Understanding the Linux Filesystem Hierarchy

/home – User files
/var/log – Application and system logs (critical for debugging)
/etc – Configuration files
/opt – Third-party software
/tmp – Temporary files (cleaned on reboot)

Permissions Deep Divebash

ls -la
chmod 755 script.sh # Owner: rwx, Group/Other: rx
chown dataeng: dataeng /opt/pipeline

Special Permissions for Data WorkUse umask to control default file permissions and setfacl for complex shared directories in team environments.
Practical Example:
bash

Create a shared data directory

sudo mkdir -p /data/lakehouse
sudo chown -R dataeng:dataeng /data
sudo chmod -R 775 /data

4. Process & Resource Management

Essential commands
ps aux | grep spark # Find processes
top / htop # Interactive monitoring
kill -9 # Force kill (use carefully)

Systemd – The Modern Init System
Most data tools run as systemd services:
sudo systemctl status postgresql
sudo systemctl restart airflow
sudo journalctl -u airflow -f # Live logs

DEV Community: Emilio Ochieng

Data Modeling, Joins, Relationships, and Different Schemas

Data Modeling, Joins, Relationships, and Different Schemas

Data Modeling

What is Data Modeling?

Example

Types of Data Models

1. Conceptual Data Model

2. Logical Data Model

3. Physical Data Model

Database Relationships

One-to-One Relationship (1:1)

One-to-Many Relationship (1:M)

Many-to-Many Relationship (M:M)

Primary Keys and Foreign Keys

Primary Key (PK)

Foreign Key (FK)

Joins

INNER JOIN

LEFT JOIN

RIGHT JOIN

FULL OUTER JOIN

CROSS JOIN

Schemas in Data Warehousing

Star Schema

Snowflake Schema

Galaxy Schema (Fact Constellation)

Relationships in Power BI

One-to-Many

Many-to-One

Many-to-Many

Best Practices

Conclusion

Linux Fundamentals for Data Engineers.

The Essential Guide

1. Why Linux Matters in Data Engineering

2. Installation & User Management

Choosing the Right Distribution

Creating a Dedicated UserNever run data pipelines as root.

3. File System & Permissions

Create a shared data directory

4. Process & Resource Management