Forem: Kigen Tarus

Subqueries vs CTEs in SQL: A Complete Guide for Beginners

Kigen Tarus — Sun, 19 Apr 2026 10:23:19 +0000

When I first started learning SQL, I kept hearing two terms thrown around: subqueries and CTEs. They seemed to do similar things, and I wasn't sure when to use one over the other. If you're in the same boat, this guide will clear everything up.

By the end of this article, you'll understand:

What subqueries are and the different types
What CTEs are and how to use them
When to use each approach
Performance and readability comparisons

Let's dive in!

What is a Subquery?

A subquery (also called an inner query or nested query) is a query placed inside another SQL query. The inner query runs first, and its result is used by the outer query.

Think of it like a Russian nesting doll - a query inside a query inside a query.

Basic Subquery Example

-- Find students who scored above the class average
SELECT first_name, last_name, marks
FROM exam_results
WHERE marks > (SELECT AVG(marks) FROM exam_results);

Here, (SELECT AVG(marks) FROM exam_results) is the subquery. It calculates the average mark first, then the outer query finds all students above that average.

Types of Subqueries

Subqueries can be categorized by where they appear and what they return.

1. By Location

Type	Location	Example
Subquery in WHERE	Inside WHERE clause	`WHERE marks > (SELECT AVG(marks)...)`
Subquery in FROM	Inside FROM clause (derived table)	`FROM (SELECT * FROM students WHERE city='Nairobi') AS nairobi_students`
Subquery in SELECT	Inside SELECT clause	`SELECT name, (SELECT AVG(marks) FROM results) AS class_avg`
Subquery in HAVING	Inside HAVING clause	`HAVING AVG(marks) > (SELECT AVG(marks) FROM results)`

2. By Return Value

Type	Returns	Operators Used
Scalar Subquery	Single value (1 row, 1 column)	`=`, `>`, `<`, `>=`, `<=`, `<>`
Row Subquery	Single row (1 row, multiple columns)	`=`, `<>` with row constructor
Column Subquery	Single column (multiple rows, 1 column)	`IN`, `NOT IN`, `ANY`, `ALL`
Table Subquery	Full table (multiple rows & columns)	Used in FROM clause

Examples of Each Type

Scalar Subquery (Single Value)

SELECT full_name, age
FROM patients
WHERE age = (SELECT MAX(age) FROM patients);
-- Returns the oldest patient(s)

Column Subquery (Multiple Rows, One Column)

SELECT full_name, city
FROM patients
WHERE city IN (SELECT city FROM students WHERE class = 'Form 4');
-- Patients living in cities where Form 4 students live

Table Subquery (Derived Table)

SELECT dept_name, avg_salary
FROM (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
) AS dept_averages
JOIN departments ON departments.id = dept_averages.department_id;

Correlated vs Non-Correlated Subqueries

This is an important distinction many beginners miss.

Type	Description	Performance
Non-Correlated	Inner query runs independently ONCE	✅ Faster
Correlated	Inner query depends on outer query, runs ONCE PER ROW	⚠️ Slower

Non-Correlated Example:

SELECT name, marks
FROM exam_results
WHERE marks > (SELECT AVG(marks) FROM exam_results);
-- Inner query runs once

Correlated Example:

SELECT e1.student_id, e1.marks, e1.subject_id
FROM exam_results e1
WHERE e1.marks > (
    SELECT AVG(e2.marks)
    FROM exam_results e2
    WHERE e2.student_id = e1.student_id  -- References outer query!
);
-- Inner query runs for EACH student

When Should You Use Subqueries?

✅ Good Use Cases for Subqueries:

Simple comparisons to aggregates

   WHERE salary > (SELECT AVG(salary) FROM employees)

Checking existence

   WHERE EXISTS (SELECT 1 FROM orders WHERE customer_id = customers.id)

IN / NOT IN conditions

   WHERE city IN (SELECT city FROM offices)

Single-value lookups

   SELECT name, (SELECT department_name FROM depts WHERE id = emp.dept_id) AS dept
   FROM employees emp;

❌ When NOT to Use Subqueries:

When the query becomes hard to read (nested 3+ levels deep)
When you need to reference the same derived table multiple times
When performance suffers from correlated subqueries on large datasets

What is a CTE (Common Table Expression)?

A CTE (Common Table Expression) is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. It's defined using the WITH keyword.

Think of a CTE as creating a temporary view that exists only for the duration of your query.

Basic CTE Syntax

WITH cte_name AS (
    -- Your query here
    SELECT column1, column2
    FROM table_name
    WHERE condition
)
-- Now use the CTE like a regular table
SELECT *
FROM cte_name
WHERE another_condition;

Simple CTE Example

WITH nairobi_students AS (
    SELECT student_id, first_name, last_name, class
    FROM students
    WHERE city = 'Nairobi'
)
SELECT class, COUNT(*) AS student_count
FROM nairobi_students
GROUP BY class
ORDER BY student_count DESC;

Types of CTEs and Their Use Cases

1. Basic CTE (Single CTE)

Used to simplify a complex query by breaking it into logical steps.

WITH high_performers AS (
    SELECT student_id, AVG(marks) AS avg_mark
    FROM exam_results
    GROUP BY student_id
    HAVING AVG(marks) > 75
)
SELECT s.first_name, s.last_name, hp.avg_mark
FROM students s
JOIN high_performers hp ON s.student_id = hp.student_id
ORDER BY hp.avg_mark DESC;

2. Multiple CTEs

You can define several CTEs in one query, separated by commas.

WITH 
-- CTE 1: Get student averages
student_averages AS (
    SELECT student_id, AVG(marks) AS avg_mark
    FROM exam_results
    GROUP BY student_id
),
-- CTE 2: Classify performance
performance_categories AS (
    SELECT 
        student_id,
        avg_mark,
        CASE 
            WHEN avg_mark >= 80 THEN 'Excellent'
            WHEN avg_mark >= 60 THEN 'Good'
            WHEN avg_mark >= 40 THEN 'Average'
            ELSE 'Needs Improvement'
        END AS performance_level
    FROM student_averages
)
-- Main query using both CTEs
SELECT s.first_name, s.last_name, pc.avg_mark, pc.performance_level
FROM students s
JOIN performance_categories pc ON s.student_id = pc.student_id
ORDER BY pc.avg_mark DESC;

3. Recursive CTEs

Recursive CTEs call themselves - perfect for hierarchical data like org charts, category trees, or bill of materials.

WITH RECURSIVE employee_hierarchy AS (
    -- Anchor: Start with the CEO (no supervisor)
    SELECT employee_id, full_name, supervisor_id, 1 AS level
    FROM employees
    WHERE supervisor_id IS NULL

    UNION ALL

    -- Recursive: Join to find subordinates
    SELECT e.employee_id, e.full_name, e.supervisor_id, eh.level + 1
    FROM employees e
    JOIN employee_hierarchy eh ON e.supervisor_id = eh.employee_id
)
SELECT * FROM employee_hierarchy
ORDER BY level, full_name;

4. CTE with DML Operations

You can use CTEs with INSERT, UPDATE, or DELETE.

WITH deleted_appointments AS (
    DELETE FROM appointments
    WHERE appt_date < '2024-01-01'
    RETURNING *
)
INSERT INTO appointment_archive
SELECT * FROM deleted_appointments;

Common Use Cases for CTEs

Use Case	Why CTE Works Well
Breaking down complex queries	Makes logic step-by-step and readable
Reusing derived tables	Define once, reference multiple times
Recursive queries	Only way to do recursion in standard SQL
Replacing views for one-time use	Temporary, no database object created
Window function preprocessing	Clean separation of filtering and ranking

Subqueries vs CTEs: A Clear Comparison

Now for the big question: When should you use a subquery, and when should you use a CTE?

Comparison Table

Criteria	Subquery	CTE
Readability (Simple)	✅ Good	🟡 Overkill
Readability (Complex)	❌ Gets messy fast	✅ Excellent
Reusability in query	❌ Must rewrite	✅ Define once, use many times
Recursion	❌ Not supported	✅ Supported
Debugging	❌ Hard to test parts	✅ Can SELECT from CTE alone
Performance	🟡 Similar (optimizer treats both the same in most cases)	🟡 Similar
Nesting depth	❌ Can become unreadable	✅ Linear, step-by-step

Performance: Is One Faster Than the Other?

Short answer: In modern databases (PostgreSQL 12+, MySQL 8+, SQL Server 2019+), there is typically NO performance difference between equivalent subqueries and CTEs.

The query optimizer treats them the same way and generates identical execution plans.

Exception: Some databases (like older PostgreSQL versions) treat CTEs as "optimization fences," meaning the CTE is materialized (stored in memory) before being used. In these cases, CTEs might be slightly slower for large datasets.

Best Practice: Write for readability first, then optimize if needed.

Readability Comparison

Let's look at the same query written both ways.

Query Goal: Find students whose average mark is higher than the overall school average.

Using Subqueries (Nested):

SELECT s.first_name, s.last_name, student_avg.avg_mark
FROM students s
JOIN (
    SELECT student_id, AVG(marks) AS avg_mark
    FROM exam_results
    GROUP BY student_id
) AS student_avg ON s.student_id = student_avg.student_id
WHERE student_avg.avg_mark > (
    SELECT AVG(marks)
    FROM exam_results
)
ORDER BY student_avg.avg_mark DESC;

Using CTEs:

WITH 
school_average AS (
    SELECT AVG(marks) AS overall_avg
    FROM exam_results
),
student_averages AS (
    SELECT student_id, AVG(marks) AS avg_mark
    FROM exam_results
    GROUP BY student_id
)
SELECT s.first_name, s.last_name, sa.avg_mark
FROM students s
JOIN student_averages sa ON s.student_id = sa.student_id
CROSS JOIN school_average
WHERE sa.avg_mark > school_average.overall_avg
ORDER BY sa.avg_mark DESC;

The CTE version reads like a story:

First, get the school average
Then, get each student's average
Finally, find students above the school average

When to Use Each: Decision Framework

┌─────────────────────────────────────────────────┐
│                                                 │
│   Do you need to reference the result           │
│   MULTIPLE TIMES in your query?                 │
│                                                 │
│         YES ──────────────────► Use CTE         │
│          │                                      │
│          NO                                     │
│          ▼                                      │
│   Is your query getting complicated             │
│   (3+ levels of nesting)?                       │
│                                                 │
│         YES ──────────────────► Use CTE         │
│          │                                      │
│          NO                                     │
│          ▼                                      │
│   Do you need recursion?                        │
│                                                 │
│         YES ──────────────────► Use CTE         │
│          │                                      │
│          NO                                     │
│          ▼                                      │
│   Is it a simple filter or EXISTS check?        │
│                                                 │
│         YES ──────────────────► Use Subquery    │
│                                                 │
└─────────────────────────────────────────────────┘

Quick Reference Cheat Sheet

Situation	Use This
`WHERE x > (SELECT AVG(x)...)`	Subquery
`WHERE EXISTS (SELECT 1...)`	Subquery
`WHERE column IN (SELECT...)`	Subquery
Need the same derived table 2+ times	CTE
Query has 3+ nested levels	CTE
Hierarchical/recursive data	CTE (Recursive)
Complex multi-step logic	CTE
Debugging intermediate results	CTE

Real-World Example: Hospital Patient Analysis

Let's put it all together with a realistic example using the city_hospital database.

Task: Find doctors who have seen more patients than the average, along with their most common diagnosis.

WITH 
-- Step 1: Count appointments per doctor
doctor_patient_counts AS (
    SELECT 
        d.doctor_id,
        d.full_name AS doctor_name,
        d.specialisation,
        COUNT(a.appointment_id) AS patient_count
    FROM doctors d
    LEFT JOIN appointments a ON d.doctor_id = a.doctor_id
    GROUP BY d.doctor_id, d.full_name, d.specialisation
),

-- Step 2: Calculate average patients per doctor
avg_patients AS (
    SELECT AVG(patient_count) AS avg_count
    FROM doctor_patient_counts
),

-- Step 3: Find most common diagnosis per doctor
doctor_top_diagnosis AS (
    SELECT DISTINCT ON (doctor_id)
        doctor_id,
        diagnosis,
        COUNT(*) AS diagnosis_count
    FROM appointments
    WHERE diagnosis IS NOT NULL
    GROUP BY doctor_id, diagnosis
    ORDER BY doctor_id, COUNT(*) DESC
)

-- Step 4: Bring it all together
SELECT 
    dpc.doctor_name,
    dpc.specialisation,
    dpc.patient_count,
    dtd.diagnosis AS most_common_diagnosis,
    dtd.diagnosis_count AS diagnosis_occurrences
FROM doctor_patient_counts dpc
CROSS JOIN avg_patients ap
LEFT JOIN doctor_top_diagnosis dtd ON dpc.doctor_id = dtd.doctor_id
WHERE dpc.patient_count > ap.avg_count
ORDER BY dpc.patient_count DESC;

This query would be a nightmare to write and read as nested subqueries. With CTEs, each step is clear and logical.

Common Mistakes to Avoid

1. Using Correlated Subqueries When You Don't Need To

❌ Bad (Slow):

SELECT name, salary,
    (SELECT AVG(salary) FROM employees e2 WHERE e2.dept_id = e1.dept_id) AS dept_avg
FROM employees e1;

✅ Better (Use JOIN and GROUP BY):

SELECT e.name, e.salary, d.dept_avg
FROM employees e
JOIN (
    SELECT dept_id, AVG(salary) AS dept_avg
    FROM employees
    GROUP BY dept_id
) d ON e.dept_id = d.dept_id;

2. Forgetting to Alias Subqueries in FROM

❌ Error:

SELECT * FROM (SELECT * FROM students WHERE city = 'Nairobi');
-- ERROR: subquery in FROM must have an alias

✅ Fixed:

SELECT * FROM (SELECT * FROM students WHERE city = 'Nairobi') AS nairobi_students;

3. Using CTEs When a Simple WHERE Clause Works

❌ Overkill:

WITH adults AS (
    SELECT * FROM patients WHERE age >= 18
)
SELECT * FROM adults WHERE city = 'Nairobi';

✅ Simpler:

SELECT * FROM patients WHERE age >= 18 AND city = 'Nairobi';

Key Takeaways

Subqueries are queries nested inside other queries - great for simple filters and single-value comparisons.
CTEs (WITH clauses) create named temporary result sets - perfect for breaking down complex logic.
Performance is usually identical - modern optimizers treat both the same.
Choose based on readability:
- Simple filters → Subquery
- Multi-step logic → CTE
- Reusing results → CTE
- Recursion → CTE
Correlated subqueries run once per row and can be slow on large datasets.
Always alias subqueries used in the FROM clause.
CTEs make debugging easier - you can run each CTE independently.

Practice Exercises

Try these on your own database:

Exercise 1: Write a subquery to find patients who live in cities where no student lives.

Exercise 2: Rewrite the following nested subquery as a CTE:

SELECT name FROM employees 
WHERE salary > (SELECT AVG(salary) FROM employees WHERE dept_id = 1)
AND dept_id IN (SELECT id FROM departments WHERE location = 'New York');

Exercise 3: Create a recursive CTE to display a company's organizational chart.

Conclusion

Both subqueries and CTEs are essential tools in your SQL toolkit. Neither is "better" - they serve different purposes.

Remember: Write SQL for humans first, computers second. If a subquery makes your query hard to read, refactor to a CTE. If a CTE feels like overkill for a simple filter, use a subquery.

The more you practice, the more intuitive this choice becomes!

If you found this article helpful, feel free to share it with other SQL learners. Have questions? Drop them in the comments below!

SQL ARTICLE

Kigen Tarus — Sun, 12 Apr 2026 12:30:22 +0000

Understanding DDL, DML, and SQL Operations in Practice

DDL vs. DML

In SQL, commands are broadly divided into two categories: Data Definition Language (DDL) and Data Manipulation Language (DML).

DDL defines and structures the database. It includes commands like CREATE, ALTER, DROP, and RENAME. These commands shape the schema, tables, and columns.
DML works with the actual data inside those structures. It includes INSERT, UPDATE, DELETE, and SELECT. These commands add, modify, remove, or query records.

The difference is clear: DDL sets up the “blueprint” of the database, while DML fills and manages the “content.”

Applying CREATE, INSERT, UPDATE, and DELETE

In the Nairobi Academy assignment:

CREATE was used to build the schema (CREATE SCHEMA nairobi_academy) and tables (students, subjects, exam_results).
INSERT populated the tables with 10 students, 10 subjects, and 10 exam results.
UPDATE modified specific records, such as changing Esther Akinyi’s city from Nakuru to Nairobi and adjusting exam marks.
DELETE removed unwanted data, like cancelling exam result ID 9.

These commands show the lifecycle of data: creation, insertion, adjustment, and removal.

Filtering with WHERE

The WHERE clause is the backbone of SQL queries. It filters results based on conditions:

= finds exact matches (e.g., students in Form 4).
> or < compares values (e.g., marks greater than 70).
BETWEEN checks ranges (e.g., marks between 50 and 80).
IN and NOT IN test membership (e.g., students from Nairobi, Mombasa, or Kisumu).
LIKE searches patterns (e.g., names starting with “A” or “E”).

This flexibility makes WHERE essential for precise data retrieval.

CASE WHEN for Transformation

The CASE WHEN statement adds logic to queries, transforming raw data into meaningful categories. For example:

Exam results were labeled as Distinction, Merit, Pass, or Fail based on marks.
Students were classified as Senior (Form 3 & 4) or Junior (Form 1 & 2).

This feature turns numbers and codes into human‑readable insights, making reports more useful.

Reflection

Working through this assignment highlighted how SQL is both technical and practical. The DDL tasks were straightforward—building tables felt like laying a foundation. The DML tasks required more attention, especially ensuring data consistency when updating or deleting records. The most interesting part was using CASE WHEN, because it showed how SQL can go beyond storage and retrieval to provide analysis and interpretation. The challenge was remembering the exact syntax for altering and renaming columns, but once mastered, it felt empowering to reshape the database structure mid‑stream.

Publishing and Embedding Power BI Reports

Kigen Tarus — Sun, 05 Apr 2026 13:43:48 +0000

By: Tarus Kigen

To publish a Power BI report and embed it into a website, you’ll use Power BI Service to upload your report, generate an embed code, and paste it into your site. This makes your interactive dashboards viewable directly in a browser.

# 🖥 Introduction to Power BI Publishing
Power BI is Microsoft’s business analytics tool that lets you create interactive reports and dashboards. Once you’ve built a report in Power BI Desktop, you can publish it to the Power BI Service (app.powerbi.com) and share it with others. One powerful feature is embedding reports into websites, allowing stakeholders to interact with live data without needing Power BI installed.

# 📌 Step 1: Create a Workspace
A workspace is a collaborative area in Power BI Service where reports and dashboards are stored.

Go to app.powerbi.com and sign in.
On the left panel, click Workspaces → Create a workspace.
Name your workspace and configure access permissions.
Save it.

# 📌 Step 2: Upload and Publish Your Report
Once your workspace is ready, upload your report from Power BI Desktop.

In Power BI Desktop, click File → Publish → Publish to Power BI.
Select your workspace.
The report will appear in your online workspace.

# 📌 Step 3: Generate the Embed Code
Now you’ll create the code snippet that allows embedding.

In Power BI Service, open your report.
Click File → Publish to web.
A dialog box will appear with an embed code (iframe HTML).
Copy the code.

Important: Publish to web makes your report public. Anyone with the link can view it, so avoid using confidential data.

# 📌 Step 4: Embed the Report on a Website

Open your website’s HTML editor.
Paste the embed code where you want the report to appear.
Save and refresh your site.
The interactive Power BI report will now be visible to visitors.

# 🔑 Key Insights

Workspaces organize reports and control access.
Publishing moves your report from Desktop to the cloud.
Embed codes allow integration into websites, but they make reports public.
For secure embedding, use Power BI Embedded or organizational sharing instead of “Publish to web.”

✅ With these steps, you can take your Power BI dashboards beyond the app and make them accessible directly on your website, enabling interactive data exploration for your audience.

Feel free to leave any questions in the comments. Thank you for your time.

Understanding Data Modeling in Power BI: Joins, Relationships, and Schemas Explained

Kigen Tarus — Sun, 29 Mar 2026 15:24:42 +0000

By Tarus Kigen
Student Analyst*

Hello there. My name is Tarus, I thought Power BI was just about dragging colorful charts onto a canvas. I was wrong.

After getting lost in a mess of incorrect totals and weird filters, I realized the most important thing isn't visuals—it's data modeling. If your model is wrong, nothing works. So, I spent my week breaking down joins, relationships, and schemas. Let me share what I learned in simple terms.

What is Data Modeling?

Imagine building a house. You don't throw bricks, wood, and glass into a pile and call it a home. You organize them into walls, floors, and a roof. Data modeling is the same: organizing your raw data into a logical structure that Power BI can understand quickly and correctly.

Good data modeling = Fast reports + Correct numbers + Happy boss.

SQL Joins vs. Power BI Relationships (Key Difference!)

This confused me the most. In SQL databases, joins physically combine two tables into one new table. In Power BI, relationships leave the tables separate but "connect" them temporarily when you make a visual.

Join (Power Query): Permanently merges columns from two tables.
Relationship (Model View): A flexible link that respects table independence.

You use joins when cleaning data in Power Query. You use relationships when building models in Model View.

All SQL Joins Explained (As I Learned in Power Query)

I practiced these using Merge Queries in Power Query Editor (Home → Combine → Merge Queries). Here is my cheat sheet using two imaginary tables:

Table A: Students (StudentID, Name)

Table B: Grades (StudentID, Score)

1. INNER JOIN

"Give me students who have grades."

Result: Only rows where StudentID exists in both tables.
Real life: Listing only active employees who already completed training.
Diagram: Two overlapping circles → only the middle.

2. LEFT OUTER JOIN

"Give me ALL students, plus grades if they exist."

Result: Every row from left table (Students). Grades = NULL if missing.
Real life: Customer list with order amounts (show customers even if they never ordered).
Diagram: Left circle completely + overlapping middle.

3. RIGHT OUTER JOIN

"Give me ALL grades, plus student names if they exist."

Result: Every row from right table (Grades). Name = NULL if missing.
Real life: Transaction list showing unknown user IDs. (Honestly? You can just swap tables and use LEFT JOIN).

4. FULL OUTER JOIN

"Everyone and everything, matched where possible."

Result: All rows from both tables. NULLs where no match.
Real life: Merging two customer lists from different acquisitions.
Diagram: Both circles fully merged.

5. LEFT ANTI JOIN

"Students who have NO grades."

Result: Only rows in left table with no match in right.
Real life: Finding products that were never sold.
Diagram: Left circle minus the middle overlap.

6. RIGHT ANTI JOIN

"Grades with no student record."

Result: Orphaned records in the right table.
Real life: Detecting database integrity issues.

Where to make joins in Power BI:

Power Query Editor → Select first table → Merge Queries → Select second table → Choose join kind.

Power BI Relationships (Model View)

After loading tables, go to Model View (left sidebar). Drag a field from one table to another to create a relationship. Here is what each setting means:

Cardinality (1:M, M:M, 1:1)

1:M (One to Many): One product has many sales. This is 90% of your relationships. One row in the "one" side filters many rows on the "many" side.
1:1: One user has one passport. Rare. Use if splitting a wide table into two thin ones.
M:M (Many to Many): Students have many classes, classes have many students. Warning: Use carefully, or totals will double.

Cross-Filter Direction

Single (Default): Filters flow from "one" side to "many" side. You pick a date table, it filters sales.
Both: Filters flow both ways. Only use when you know what you are doing (otherwise, ambiguous paths occur).

Active vs. Inactive Relationships

You can have multiple relationships between two tables (e.g., Sales table has OrderDate and ShipDate both linked to a Date table). But only one active at a time. Use USERELATIONSHIP in DAX to activate an inactive one.

Fact vs. Dimension Tables (The Golden Rule)

After my week, I finally understood this:

Fact Table	Dimension Table
Contains measurements (Sales Amount, Quantity)	Contains descriptions (Product Name, Customer City)
Changes often (every transaction)	Changes slowly (customer address)
Has foreign keys (ProductID, CustomerID)	Has primary keys (ProductID)
Example: `Sales`	Example: `Products`, `Customers`

Rule: Fact tables are at the "many" side of relationships. Dimensions are at the "one" side.

Schemas: Star, Snowflake, and Flat Table (DLAT)

1. Star Schema (The Winner)

One fact table in the middle, dimension tables directly connected like a star.

Why I love it: Simple, fast, easy for beginners.
Use case: Most business reports (sales, inventory, HR).

2. Snowflake Schema

Dimensions are further normalized into sub-dimensions (e.g., Product → Category → CategoryType).

Why avoid as a beginner: Slower, more complex.
Use case: Huge enterprise databases where storage is critical.

3. Flat Table (Denormalized, aka DLAT – "Don't Look At That")

One giant table with everything (fact + dimensions combined).

Why not: Huge file size, repeating data, difficult filtering.
Use case: Simple exports for small data (<100k rows). I started here and regretted it.

Role-Playing Dimensions

A date table that filters sales by OrderDate, then by ShipDate, then by DueDate. Same dimension table, used many ways.

How to create: Build one Date table, create inactive relationships for secondary dates. Use measures like:

Sales by Ship Date = CALCULATE(SUM(Sales[Amount]), USERELATIONSHIP(Sales[ShipDate], Date[Date]))

Common Modeling Issues I Faced (And Fixed)

Bidirectional filters causing ambiguity → Set cross-filter to Single.
Many-to-many giving wrong totals → Add a bridge table (e.g., StudentClass).
Blank rows appearing in slicers → Fix foreign key mismatches (orphan rows).
Circular dependencies → Remove redundant relationships.

Step-by-Step: Where to Create These in Power BI

Task	Where in Power BI
Merge two tables (JOIN)	Power Query Editor → Merge Queries
Create a relationship	Model View → Drag field to field
Change cardinality	Model View → Manage Relationships → Edit
Activate/inactivate relationship	Model View → Click line → Uncheck "Active"
View schema	Model View (zoom out to see all tables)
Create a flat table	Power Query → Append Queries (stack) or Merge (widen)

Final Advice

Start with a star schema: one fact table, a few dimensions. Use 1:M relationships with single filter direction. Avoid many-to-many. Use joins only in Power Query to clean data (e.g., adding lookup values), then use relationships for reporting.

After one week, my reports no longer lie. Yours will too. Happy modeling.

*— Tarus Kigen, Student Analyst.