Interview Focus: DataFramesDifficulty: Easy

Basic DataFrame Joins

Estimated duration: 10 mins

Lab Overview & Scenario

Join relational employee profiles with department metadata mapping.

Business Scenario

The operations team needs a clean report displaying employee names paired with their department titles. You must join the employee profiles table with the department metadata table.

1. Local Raw Mock Dataset

The processing pipeline will ingest the following local mock file structures (e.g. simulated as `dataset.csv`):

employee_id	name	dept_id
1	Alice	10
2	Bob	20
3	Charlie	10
4	David	30
5	Eve	20
---
dept_id	dept_name
10	Engineering
20	Marketing
30	Sales

View Raw CSV Dataset

employee_id,name,dept_id
1,Alice,10
2,Bob,20
3,Charlie,10
4,David,30
5,Eve,20
---
dept_id,dept_name
10,Engineering
20,Marketing
30,Sales

2. Your Tasks

Simulate two DataFrames (Employees and Departments).

Perform an INNER JOIN between them on the dept_id column.

Select and print the employee name and department name columns.

Display the results.

3. Pipeline Skeleton Script

Copy this starter template to write the data transformation logic:

python

# starter.py - Basic DataFrame Joins
from pyspark.sql import SparkSession
from pyspark.sql.functions import col

def join_data():
    spark = SparkSession.builder.appName("JoinLab").getOrCreate()
    
    # Raw Mock Data
    emp_data = [(1, "Alice", 10), (2, "Bob", 20), (3, "Charlie", 10), (4, "David", 30), (5, "Eve", 20)]
    dept_data = [(10, "Engineering"), (20, "Marketing"), (30, "Sales")]
    
    # TODO: Create DataFrames, join on dept_id, print name and dept_name
    pass

if __name__ == "__main__":
    join_data()

4. Interactive Solution & Execution Output

Reveal Solution Pipeline Code

Implement the correct transformations using PySpark to achieve the aggregated values metrics output.

Pipeline Flow

Raw Memory Lists

↓

Create DataFrames

↓

Join on dept_id

↓

Select name, dept_name

↓

Display Output

Step-by-Step Implementation Guide

Step 1

Create the Employees DataFrame and Departments DataFrame from memory tuples with column names declared.

Step 2

Call the join() method on emp_df. Pass the second DataFrame (dept_df), the join column key on="dept_id", and the join type how="inner" as arguments.

Step 3

Select the employee name and dept_name columns.

Step 4

Print output results using show() method.

Common Mistakes

Not naming the join column explicitly, which leads to duplicate join key columns in the output.
Mismatching key types, which yields empty join results.

Interviewer's Expectations

Why this is asked:

Joining relational tables is a core prerequisite task for compiling integrated data warehouses.

What they look for:

Clean join syntax, and handling potential column name collisions.

Common mistakes:

Using incorrect column name matches, causing duplicate column references.

An excellent answer includes:

Discuss inner versus left/right outer joins and explain what happens when keys are duplicated.

✔ What You Learned

✔Instantiate DataFrames in memory
✔Join relational tables using inner join mapping
✔Limit outputs to clear reports

Continue Learning

Previous LabCustomer Age Categories Related Theory LessonJoins Next LabConvert CSV to Parquet