DEV Community: Angellicah

Stop Guessing, Start Modeling: Relationships, Schemas & Joins in Power BI

Angellicah — Tue, 30 Jun 2026 00:24:10 +0000

A database without relationships is just a spreadsheet with delusions of grandeur.

If you've ever stared at a Power BI report showing wrong numbers...totals that don't add up, filters that filter nothing, there's a good chance your data model was broken. Not a bug. Just two tables that should've been talking to each other… and weren't.

This is your practical guide to data modeling, schemas, relationships, and joins in Power BI, what they are, how they connect, and how to stop getting burned by them.

What Is Data Modeling and How Does It Work?

Data modeling is the process of defining how your tables connect to each other inside Power BI's engine (called VertiPaq). Think of it like drawing a map between your tables, telling Power BI this column in Table A is the same thing as this column in Table B.

When you load multiple tables into Power BI, it doesn't automatically know they're related. A Sales table and a Products table, sitting separately, can't filter each other. Data modeling builds the bridges.

Power BI's model view lets you:

Define relationships between tables
Set cardinality and cross-filter direction
Build star or snowflake schemas
Create calculated columns and measures using DAX

Under the hood, Power BI compresses and stores each column separately (columnar storage). Relationships are resolved in-memory at query time, which is why a well-structured model is blazing fast, and a messy one will bring your report to its knees.

Key Concepts
| Concept                  | What It Means                                      |
|--------------------------|-----------------------------------------------------|
| Fact Table               | Stores measurable events (sales, transactions, logs)|
| Dimension Table          | Stores descriptive context (products, customers)    |
| Primary Key (PK)         | Unique identifier column in a dimension table       |
| Foreign Key (FK)         | Column in a fact table referencing a PK in a dim    |
| Relationship             | The defined link between a PK and FK across tables  |
| Cardinality              | Describes how many rows on each side match          |
| Cross-filter Direction   | Controls which way filters flow across relationship |

Understanding Schemas (The Blueprint of Your Model)

A schema is simply the structure, the layout, of your data model. It describes which tables exist, what columns they have, and how they relate to each other. Think of it as the floor plan of your data house.

Power BI works best with two classic schema types:

Star Schema

The star schema is Power BI's best friend. It has:

One central Fact Table (big, skinny — lots of rows, few columns)
Multiple Dimension Tables surrounding it (smaller, with descriptive attributes)

The fact table holds numbers and foreign keys. The dimension tables hold the context. Every dimension connects directly to the fact table. No dimension connects to another dimension.

Example: Sales Model


DimDate ──────┐
DimProduct ───┤──── FactSales
DimCustomer ──┤
DimRegion ────┘

This is clean, fast, and DAX-friendly. Most of your models should look like this.

Snowflake Schema

A snowflake schema is a star schema where the dimension tables are further normalized, they break into sub-dimensions.

DimProductCategory ──── DimProduct ──── FactSales

Here, DimProduct relates to FactSales, but DimProductCategory relates to DimProduct instead of directly to FactSales.

Snowflake schemas save storage space but are harder to work with in DAX and can slow down your model. Use them only when necessary (e.g. data comes from a normalized SQL database and you can't denormalize it).

Galaxy Schema (Fact Constellation)

Multiple fact tables share the same dimension tables. This is common in enterprise models.

DimDate ────┬──── FactSales
            └──── FactReturns
DimProduct ─┬──── FactSales
             └──── FactReturns

| Schema                     | Structure                   | Power BI Friendliness                 | Best Used When              |
|----------------------------|-----------------------------|------------------------|--------------------------------------|
| **Star**                   | 1 fact + many dimensions    | ⭐⭐⭐⭐⭐ Excellent | Default choice — always aim for this |
| **Snowflake**              | Normalized dimensions       | ⭐⭐⭐ Moderate  | Source data is already normalized    |
| **Galaxy / Constellation** | Multiple facts, shared dims | ⭐⭐⭐⭐ Good      | Enterprise multi-subject models      |
| **Flat (Wide Table)**      | Everything in one table     | ⭐ Poor              | Avoid — causes redundancy & slow DAX           |

Relationships in Power BI (How Tables Actually Talk)

A relationship in Power BI is a_ defined connection between two tables based on a matching column_. It's how Power BI knows that ProductID in your FactSales table and ProductID in your DimProduct table are the same thing.

Without relationships, every table is an island. With relationships, filters and aggregations flow across your entire model like electricity through a circuit.

Components of a Relationship

Every relationship in Power BI has four components:

| Component                  | Description                                             | Options                    |
|----------------------------|-----------------------------------------------------------|----------|
| **From Table / Column**    | The table where the FK lives (usually the fact table)    | Any table |
| **To Table / Column**      | The table where the PK lives (usually the dimension)     | Any table |
| **Cardinality**            | How many rows on each side match             | One-to-Many, One-to-One, Many-to-Many |
| **Cross-Filter Direction** | Which way filters flow                     | Single, Both |

Cardinality

Cardinality defines the nature of the match between your two key columns.

| Cardinality            | Symbol       | Meaning                         | Example                |
|------------------------|--------------|--------------------------------------|-------------------|
| **One-to-Many (1:M)**  | `1` ──── `*` | One row in dim matches many in fact | 1 Product → many Sales rows |
| **Many-to-One (M:1)**  | `*` ──── `1` | Many fact rows match one dim row | Same as above, reversed|
| **One-to-One (1:1)**   | `1` ──── `1` | Each row matches exactly one row | Country codes ↔ Country names |
| **Many-to-Many (M:M)** | `*` ──── `*` | Many rows match many rows                  | Students ↔ Courses     |

Many-to-Many relationships are supported in Power BI but should be used with caution. They can cause ambiguous filter propagation and unexpected aggregation results.

Cross-Filter Direction

| Direction | Behaviour                                 | When to Use          |
|-----------|-------------------------------------------|-----------------------------------------------|
| **Single**| Filters flow from 1-side → many-side only | Default — use this 90% of the time          |
| **Both**  | Filters flow in both directions | Role-playing dimensions, bridge tables — use sparingly |

Bidirectional filters can create circular dependencies and ambiguous results. Only use them when you have a clear reason.

Active vs. Inactive Relationships

Power BI allows only one active relationship between any two tables at a time. But you can have multiple relationships defined... they just sit there, inactive, until you call them.

Active Relationships

Used automatically by all visuals and standard DAX measures
Shown as a solid line in the model view
You can only have one active relationship between any two tables

Inactive Relationships

Shown as a dashed line in model view
Ignored by default — must be explicitly activated in DAX using USERELATIONSHIP()
Useful for role-playing dimensions (e.g., a Date table used for both Order Date and Ship Date)

Real-World Example: Role-Playing Dates

-- `FactSales` has both `OrderDate` and `ShipDate`, both linking to `DimDate`
-- Only one can be active. Use `USERELATIONSHIP` for the other:

ShippedSalesAmount =
CALCULATE(
    SUM(FactSales[SalesAmount]),
    USERELATIONSHIP(FactSales[ShipDate], DimDate[Date])
)

Active vs. Inactive Cheat Sheet

|                          | **Active Relationship**  | **Inactive Relationship**      |
|--------------------------|--------------------------|
| **Visual in Model View** | Solid line               | Dashed line         |
| **Used by default?**     | ✅ Yes                   | ❌ No |
| **Used in DAX?**         | Automatically            | Only with `USERELATIONSHIP()` |
| **How many allowed?**    | 1 between any two tables | Multiple |
| **Common use case**      | Standard lookups         | Role-playing dimensions          |

Joins vs. Relationships

This trips up a lot of people who come from SQL.
In SQL, you write JOIN to combine tables at query time.
In Power BI, relationships are defined once in the model and then used automatically. But both achieve similar results in different layers.

Joins (Power Query / M)

Joins happen in Power Query (the M layer) — before the data even loads into your model. They physically merge tables into one combined table.

| Join Type            | What It Returns                                                         |
|----------------------|--------------------------------------------------------|
| **Inner Join**       | Only rows with matches in BOTH tables            |
| **Left Outer Join**  | All rows from left table + matching rows from right |
| **Right Outer Join** | All rows from right table + matching rows from left  |
| **Full Outer Join**  | All rows from both tables, matched where possible |
| **Left Anti Join**   | Rows in left table with NO match in right         |
| **Right Anti Join**  | Rows in right table with NO match in left        |

Relationships (Model Layer)

Relationships stay as separate tables in the model and are resolved dynamically at query time by the DAX engine. Filters flow across them without physically merging data.

Joins vs. Relationships

| | **Joins (Power Query)** | **Relationships (Model)** |
|---------------------------|---------------------------|
| **Where it happens**      | Data transformation layer | Data model layer |
| **Result**                | Merged/flattened table    | Separate tables, linked |
| **Performance**           | Can increase data size    | Optimised by VertiPaq |
| **Flexibility**           | Fixed at load time        | Dynamic at query time |
| **DAX compatibility**     | Limited (flat table)      | Full DAX power |
| **Maintenance**           | Harder to update          | Easy to modify |
| **Best for**              | One-time lookups, data cleanup | Star schema models |

## When to Use Joins vs. When to Use Relationships

This is arguably the most practical question in Power BI data modeling, and the answer matters more than most tutorials admit.

Use a JOIN (Power Query) When:

You need to enrich a table with a few lookup columns (e.g., add Country Name to a table that only has Country Code)
You are cleaning or reshaping raw data before modeling
The two tables will never be used separately in your model
You want to reduce the number of tables in your model for simplicity
You're _dealing with a very small lookup table _that doesn't need to be its own dimension

Example: Merging a small "CurrencyCode → CurrencyName" lookup 
into your FactSales table in Power Query is fine — 
you don't need a separate DimCurrency table for 3 currency codes.

Use a RELATIONSHIP (Model) When:

Your tables have a clear one-to-many structure (Fact ↔ Dimension)
You need dynamic filtering — visuals should filter each other
You're using time intelligence functions (they require a proper Date relationship)
You plan to reuse a dimension across multiple fact tables (e.g., DimDate used by FactSales AND FactReturns)
You want to write clean, efficient DAX measures
The dimension table has many attributes that would bloat your fact table if joined

Decision Cheat Sheet: Join or Relationship?

| Scenario                                         | Recommended Approach |
|--------------------------------------------------|----------------------                  |
| Add country name from a 3-row lookup             | **Join** in Power Query                          |
| Connect Sales to a 50-column Product table       | **Relationship** in the model                      |
| Combine data from two systems for one flat table | **Join** in Power Query                          |
| Use one Date table for Order Date AND Ship Date  | **Two Relationships** (1 active, 1 inactive)         |
| One customer linked to many orders               | **Relationship** (1:M)                          |
| Many students enrolled in many courses           | **Relationship with bridge table** (M:M → two 1:M) |
| Snapshot table that's used once                  | **Join** in Power Query                          |
| Shared dimension across multiple fact tables     | **Relationship** in the model                      |

Keys

Every relationship depends on keys — columns that uniquely identify rows.

| Key Type             | Description                                                | Example                                          |
|----------------------|-------------------------------------------------------------|---------------------------------------|
| **Primary Key (PK)** | Uniquely identifies each row — no nulls, no duplicates | `ProductID` in DimProduct             |
| **Foreign Key (FK)** | References a PK in another table — may have duplicates | `ProductID` in FactSales              |
| **Surrogate Key**    | System-generated key (usually an integer)           | Auto-incremented ID                              |
| **Natural Key**      | A real-world identifier used as a key | Email address, National ID                               |
| **Composite Key**    | Two or more columns together form the unique identifier             | `OrderID + LineNumber`    |

Power BI tip: Always use surrogate integer keys for relationships instead of text-based natural keys.

Master Cheat Sheet — The Complete Power BI Modeling Reference

| Task                             | Where                   | What to Do |
|----------------------------------|-------------------------|------------|
| Connect two tables               | Model View              | Drag FK column onto PK column           |
| Check relationship type          | Model View → Click line | See cardinality & direction         |
| Fix ambiguous relationships      | Model View              | Deactivate one, use USERELATIONSHIP in DAX |
| Use inactive relationship in DAX | DAX Editor              | `CALCULATE([Measure], USERELATIONSHIP(FK, PK))` |
| Avoid many-to-many               | Power Query + Model     | Add bridge/junction table           |
| Build a star schema              | Model View              | 1 fact table, many dimension tables    |
| Improve performance              | Model View + Power Query| Use integer keys, remove unused columns     |

Cardinality Quick Reference

| Type                   | When                      | Watch Out For |
|------------------------|---------------------------|---------------|
| **One-to-Many (1:M)**  | Standard Fact ↔ Dimension | Nothing — this is ideal                  |
| **One-to-One (1:1)**   | Splitting large tables    | May indicate tables should be merged       |
| **Many-to-Many (M:M)** | Shared attributes         | Use a bridge table instead where possible |

Cross-Filter Quick Reference

| Setting    | Filter Flows    | Use When                                            |
|------------|-----------------|------------------------------------------|
| **Single** | Dim → Fact only | Standard star schema (default)              |
| **Both**   | Dim ↔ Fact      | Bridge tables, role-playing dims (use sparingly) |

DAX Relationship Functions

| Function          | Syntax                      | Purpose                       |
|-------------------|-----------------------------|-----------------------|
| `RELATED`         | `RELATED(DimTable[Column])` | Pull a value from the 1-side into the many-side  |
| `RELATEDTABLE`    | `RELATEDTABLE(FactTable)`   | Return related rows from the many-side         |
| `USERELATIONSHIP` | `USERELATIONSHIP(FK, PK)`   | Activate an inactive relationship in a measure  |
| `CROSSFILTER`     | `CROSSFILTER(FK, PK, Both)` | Override filter direction inside a measure |

Wrapping Up

Data modeling in Power BI isn't just a technical checkbox,... it is the architecture that determines whether your reports are fast, accurate, and maintainable, or slow, wrong, and painful to debug.

The golden rules to walk away with:

Always aim for a star schema. One fact table, surrounded by clean dimension tables.
Relationships beat joins for anything that needs to be dynamic, reusable, or DAX-friendly.
Use integer surrogate keys. Text-based keys are slower and harder to manage.
Default to Single cross-filter direction. Go bidirectional only when you have to.
Inactive relationships are not dead relationships — they're tools. Use USERELATIONSHIP() to unlock them.
Many-to-many isn't always wrong — but a bridge table is almost always cleaner.

The moment your model is clean, your DAX becomes simpler, your reports run faster, and those mysterious wrong numbers finally disappear. That's the power of modeling done right.

LINUX FUNDAMENTALS FOR DATA ENGINEERING.

Angellicah — Sat, 06 Jun 2026 21:27:55 +0000

INTRODUCTION

Data engineering is the backbone of modern data-driven organizations. Data engineers design, build, and maintain systems that collect, process, and store vast amounts of data. While programming languages such as Python and SQL often receive significant attention in data engineering discussions, Linux remains one of the most essential tools in a data engineer's toolkit.

Most data platforms, cloud servers, databases, big data frameworks, and ETL pipelines run on Linux-based systems. Therefore, understanding Linux fundamentals is a necessity for any aspiring data engineer.

This article explores the key Linux concepts every data engineer should master, including file system navigation, file management, permissions, process management, networking, shell scripting, and automation. Practical examples are provided throughout to demonstrate how Linux is used in real-world data engineering tasks.

WHY LINUX MATTERS IN DATA ENGINEERING

Linux dominates the server and cloud computing ecosystem. Technologies frequently used in data engineering and are typically deployed on Linux servers include:

Apache Hadoop
Apache Spark
Apache Kafka
PostgreSQL
MySQL
Docker
Kubernetes

As a data engineer, you may need to:

Access remote servers
Monitor data pipelines
Schedule automated jobs
Manage data files
Troubleshoot system issues
Deploy applications

UNDERSTANDING THE LINUX FILE SYSTEM

Unlike Windows, Linux uses a hierarchical directory structure beginning with the root directory (/).

Common directories include:

Directory	Purpose
`/`	Root directory
`/home`	User files
`/etc`	Configuration files
`/var`	Log files and variable data
`/tmp`	Temporary files
`/usr`	User programs and utilities
`/bin`	Essential command binaries

To view the current directory:

pwd

Example Output:

/home/student

To list files:

ls

For detailed information:

ls -l

To view hidden files:

ls -la

These commands are frequently used when locating datasets, scripts, logs, and configuration files.

NAVIGATING DIRECTORIES

Directory navigation is one of the first Linux skills every data engineer should learn.

Move into a directory:

cd data

Move back one level:

cd ..

Return to home directory:

cd ~

Move to root directory:

cd /

Practical Example:

Suppose a dataset is stored in:

/home/student/datasets/sales

You can access it using:

cd ~/datasets/sales

Efficient navigation saves time when managing large data projects.

CREATING AND MANAGING FILES

Data engineers often create scripts, configuration files, and data storage directories.

Create a new directory:

mkdir project_data

Create nested directories:

mkdir -p project_data/raw/2025

Create an empty file:

touch sales.csv

Copy a file:

cp sales.csv backup_sales.csv

Move or rename a file:

mv sales.csv monthly_sales.csv

Delete a file:

rm sales.csv

Delete a directory:

rm -r project_data

Practical Example:

Creating a project structure for a data pipeline:

mkdir -p 
data_pipeline/{raw,processed,scripts,logs}

Output structure:

data_pipeline/
├── raw
├── processed
├── scripts
└── logs

This organization improves maintainability and scalability.

VIEWING AND MANIPULATING FILE CONTENTS

Data engineers regularly inspect datasets and log files.

Display file contents:

cat data.csv

View large files:

less data.csv

Display first 10 lines:

head data.csv

Display last 10 lines:

tail data.csv

Monitor logs continuously:

tail -f pipeline.log

Practical Example:

Monitoring an ETL process:

tail -f etl_job.log

This command helps identify errors in real time.

SEARCHING FOR FILES AND DATA

Data environments often contain thousands of files.

Find a file:

find . -name "sales.csv"

Search for text inside files:

grep "ERROR" pipeline.log

Count occurrences:

grep -c "ERROR" pipeline.log

Practical Example:

Finding failed records in a log:

grep "FAILED" ingestion.log

Output:

FAILED: Record 1024
FAILED: Record 2048
FAILED: Record 3050

This allows quick troubleshooting.

LINUX PERMISSIONS AND OWNERSHIP

Linux uses permissions to control file access.

View permissions:

ls -l

Example Output:

-rw-r--r-- 1 student student 2450 sales.csv

Permission categories:

Owner
Group
Others

Permission symbols:

Symbol	Meaning
`r`	Read
`w`	Write
`x`	Execute

Change permissions:

chmod 755 script.sh

Make script executable:

chmod +x script.sh

Change ownership:

chown user:user file.txt

Practical Example:

Allowing an ETL script to execute:

chmod +x etl.sh

Without execute permission, the script cannot run.

PROCESS MANAGEMENT

Data pipelines frequently run as Linux processes.

View running processes:

ps aux

Monitor system activity:

top

Find process ID:

pgrep python

Terminate process:

kill PID

Force termination:

kill -9 PID

Practical Example:

Suppose a Spark job becomes unresponsive.

Find it:

ps aux | grep spark

Stop it:

kill PID

This prevents resource wastage.

DISK USAGE MONITORING

Large datasets consume significant storage.

Check disk space:

df -h

Check directory size:

du -sh datasets/

Practical Example:

Determining storage used by data files:

du -sh raw_data/

Output:

15G raw_data/

This helps monitor storage requirements.

NETWORKING FUNDAMENTALS

Data engineers often work with remote servers.

Check IP address:

ip addr

Test connectivity:

ping google.com

Connect to remote server:

ssh user@server-ip

Transfer files:

scp data.csv user@server:/home/user/

Practical Example:

Uploading a processed dataset:

scp processed.csv admin@192.168.1.10:/data/

This enables data sharing between systems.

PACKAGE MANAGEMENT

Linux distributions use package managers.

Ubuntu/Debian:

sudo apt update
sudo apt install python3

Red Hat/CentOS:

sudo yum install python3

Practical Example:

Installing PostgreSQL client:

sudo apt install postgresql-client

This allows database interaction directly from the terminal.

SHELL SCRIPTING FOR AUTOMATION

Automation is a core responsibility of data engineers.

Example shell script:

!/bin/bash

echo "Starting Data Pipeline"

python extract.py
python transform.py
python load.py

echo "Pipeline Completed"

Save as:

pipeline.sh

Make executable:

chmod +x pipeline.sh

Run:

./pipeline.sh

Benefits:

Reduces manual work
Improves consistency
Enables scheduling

SCHEDULING JOBS WITH CRON

Data pipelines often run automatically.

Open cron editor:

crontab -e

Run script every day at midnight:

0 0 * * * /home/student/pipeline.sh

Cron Format:

Minute Hour Day Month Weekday

Practical Example:

Execute data ingestion daily:

30 2 * * * /home/student/scripts/ingest.sh

This runs at 2:30 AM every day.

WORKING WITH COMPRESSED FILES

Large datasets are commonly compressed.

Compress file:

gzip data.csv

Decompress file:

gunzip data.csv.gz

Create archive:

tar -cvf archive.tar data/

Extract archive:

tar -xvf archive.tar

Practical Example:

Receiving compressed logs:

gunzip logs.gz

Then analyze them using Linux tools.

USEFUL COMMANDS FOR DATA ENGINEERS

Count lines in a file:

wc -l sales.csv

Sort data:

sort sales.csv

Remove duplicates:

uniq sales.csv

Display specific columns:

cut -d',' -f1,3 sales.csv

Combine commands:

cat sales.csv | grep Nairobi | wc -l

This counts records containing "Nairobi".

PRACTICAL ASSIGNMENT EXAMPLE

During this Linux fundamentals assignment, several commands were used to create and manage a data engineering workspace.

Creating project directories:

mkdir -p data_engineering/{raw,processed,scripts,logs}

Creating a sample dataset:

touch raw/sales_data.csv

Viewing data:

head raw/sales_data.csv

Creating a pipeline script:

nano scripts/process_data.sh

Making it executable:

chmod +x scripts/process_data.sh

Running the pipeline:

./scripts/process_data.sh

Monitoring logs:

tail -f logs/pipeline.log

These activities simulate real-world data engineering operations.

BEST PRACTICLES FOR DATA ENGINEERS USING LINUX

Organize files using structured directories.
Use meaningful file names.
Automate repetitive tasks with scripts.
Monitor system resources regularly.
Secure files using proper permissions.
Maintain backups of critical data.
Use version control systems such as Git.
Document scripts and workflows.

Following these practices improves reliability and maintainability.

CONCLUSION

Linux is a foundational skill for data engineering. Whether managing datasets, monitoring ETL pipelines, deploying applications, or automating workflows, Linux provides the essential tools required to operate efficiently in modern data environments.

Mastering Linux fundamentals such as file management, permissions, process control, networking, automation, and shell scripting significantly enhances a data engineer's productivity and effectiveness. As organizations continue to rely on cloud platforms and distributed data systems, Linux expertise will remain one of the most valuable technical skills in the data engineering profession.

For aspiring data engineers, investing time in learning Linux is about building the operational foundation necessary for handling real-world data challenges at scale.