Part 1 — Python (Data Cleaning + Feature Engineering)

This section documents everything done in Python before moving the cleaned data into SQL Server and then Power BI.

---

1) Project goal (Python scope)

In Python, the goal is to take the raw Spaceship Titanic dataset and produce a clean, analysis-ready CSV with:

• Missing values handled consistently
• Duplicates removed
• Useful engineered features added (spending + cabin/group features)
• Correct data types prepared for SQL storage

---

2) Environment & libraries

Tools

• Jupyter Notebook (or VS Code notebooks)

Libraries used

• pandas (data loading + cleaning)
• matplotlib / seaborn (EDA visuals)

---

3) Load dataset

Input: spaceship_titanic_dataset.csv Steps:

• Read the CSV into a DataFrame.
• Preview rows to confirm the dataset loaded correctly.

What to check immediately:

• Column names
• Data types
• Quick sample of values

---

4) Initial EDA (Explore before cleaning)

The notebook runs the basic “health check” steps:

• df.info() → data types + non-null counts

• df.describe() → statistics for numeric columns

• Missing values count per column:

  • df.isna().sum()

Also checks if there are completely empty rows:

• Remove rows where all columns are null:

  • df.dropna(how="all")

Why this matters: it prevents “fake rows” from breaking summaries, joins, and counts later.

---

5) Missing values handling (Cleaning rules)

A clear rule was applied per column type:

A) Categorical columns → fill with mode (most frequent)

Columns:

• HomePlanet, Cabin, Destination, VIP

Rule:

• Replace missing with mode()[0]

Why: mode keeps the most common category and avoids creating a new “Unknown” bucket for core analysis columns.

B) Name → fill with a label

• Name missing → "UnKnown"

Why: Name isn’t used for analysis, but you still want a non-null value for completeness.

C) Spending columns → fill with 0

Columns:

• RoomService, FoodCourt, ShoppingMall, Spa, VRDeck

Rule:

• Missing spending = 0

Why: In this dataset, spending columns behave like “amount spent”. If it’s missing, treating it as 0 is a reasonable default for analysis and for building TotalSpending.

Perfect — then we must correct the README to reflect what you actually implemented, not what was originally suggested.

Since you filled remaining CryoSleep nulls with:

df["CryoSleep"].fillna(True, inplace=True)

Missing Values Strategy (Final Implementation)

D) CryoSleep — Logical Rule + Default Assumption

CryoSleep was handled using a structured two-step approach:

---

Step 1 — Logical Consistency Rule

A passenger in CryoSleep should not generate any spending.

Therefore:

• If a passenger has any spending > 0
• AND CryoSleep is missing → set CryoSleep = False

This ensures internal consistency between spending behavior and sleep status.

---

Step 2 — Remaining Nulls Filled with True

After applying the logical rule:

• All remaining missing values in CryoSleep were filled with True.

df["CryoSleep"].fillna(True, inplace=True)

---

Why filling with True is acceptable here

• Passengers without spending are logically more likely to be in CryoSleep.
• The dataset contains many zero-spending passengers.
• This assumption preserves logical coherence with the business rule.
• It avoids introducing statistical noise through random imputation.

---

6) Visual EDA used (to understand distributions)

These plots were created to make sure the cleaning choices make sense:

A) Spending distributions (histograms)

• Histograms for each spending column
• X-axis capped at the 99th percentile to avoid extreme outliers hiding the shape

Why: spending columns are heavily skewed; the cap helps you “see” the distribution.

B) Age distribution (histogram)

Why: validates the median-imputation choice and checks if ages look realistic.

C) Spending boxplots

Why: quickly highlights outliers and spread across all spending columns.

---

7) Remove duplicates

• df.drop_duplicates(inplace=True)
• Then checks duplicates count again.

Why: duplicates can break counts, group sizes, and also create problems when building keys/constraints in SQL.

---

8) Feature engineering (new columns created)

These are the engineered columns added to support analysis + modeling:

A) TotalSpending

• TotalSpending = sum(RoomService, FoodCourt, ShoppingMall, Spa, VRDeck)

Why: a single KPI that’s easy to analyze in SQL/Power BI and useful for segmentation.

B) Cabin split → Deck, CabinNum, Side

From Cabin formatted like Deck/Number/Side:

• Deck
• CabinNum
• Side

Why: cabin location is more useful when split into structured attributes.

C) Group features from PassengerId

From PassengerId formatted like Group_PassengerNumber:

• Extract Group
• Extract Group_Number

Then:

• Group_Size = count passengers per Group
• Is_Solo = (Group_Size == 1)

Why: group behavior is a strong driver in this dataset (solo vs with family/friends).

---

9) Data types prepared for SQL

Boolean columns converted to integers (0/1) for easier storage and querying in SQL Server:

• CryoSleep, Transported, Is_Solo, VIP → astype(int)

Why: makes BULK INSERT + constraints + aggregations simpler in SQL.

---

10) Output saved for SQL phase

Output file created:

• spaceship_cleaned_dataset.csv

Why: this is the dataset that should be used for:

• SQL table insertion (BULK INSERT)
• Power BI modeling via SQL

---

Phase 2 — SQL Server (Database Design + Loading + Validation)

This phase takes the cleaned CSV from Python and builds a structured SQL Server database that supports clean querying and Power BI modeling.

---

1) Goal of the SQL phase

• Store the cleaned dataset in a normalized relational design
• Enforce data integrity (keys, uniqueness, foreign keys)
• Prepare the data for fast analysis and Power BI reporting

Input: spaceship_cleaned_dataset.csv (from Phase 1)

---

2) Database creation

A dedicated database was created to isolate the project:

• CREATE DATABASE SpaceShip
• USE SpaceShip

Why: keeps the project clean, avoids mixing with other practice databases.

---

3) Schema design (tables + relationships)

Instead of keeping everything in one wide table, the data was split into logical entities:

A) Passenger table (passenger identity & demographics)

Stores passenger-level attributes:

• PassengerId (unique business identifier)
• HomePlanet, Age, VIP
• Group attributes: GroupNo, Group_Size

Primary key:

• id INT IDENTITY(1,1) PRIMARY KEY

Why:

• Identity id is a stable surrogate key for joins.
• PassengerId is enforced as UNIQUE to prevent duplicates.

---

B) Cabin table (location dimension)

Cabin values were separated into their own table to avoid repeating cabin text fields for every row.

Stores:

• Cabin (full string)
• Deck, CabinNumber, Side

Key rule that fixed the earlier FK issue:

• CONSTRAINT UQ_Cabin_Cabin UNIQUE (Cabin)

Why this matters:

• If Cabin repeats and you try to reference it like a dimension, you must ensure the referenced field is unique.
• The unique constraint guarantees one cabin row per cabin code.

---

C) SpaceShip table (trip + spending + outcome)

This holds the “fact-like” trip record:

• Foreign keys:

  • PassengerID → Passenger(id)
  • CabinID → Cabin(id)

• Outcome:

  • Transported

• Behavior flags:

  • IsSolo, CryoSleep

• Spending:

  • RoomService, FoodCourt, ShoppingMall, Spa, VRDeck
  • TotalSpending

Why:

• This structure makes it easy to analyze behavior + outcome while keeping passenger and cabin clean and reusable.

---

4) Data loading strategy (recommended workflow)

Because the cleaned data is in a single CSV, the common professional approach is:

Step 1 — Load into a staging table

Create a StagingSpaceship table matching the CSV columns exactly (all as nullable initially).

Then:

• BULK INSERT into staging

Why staging:

• It’s safer and avoids breaking constraints during initial load.

Step 2 — Insert into dimension tables

• Insert distinct passengers into Passenger
• Insert distinct cabins into Cabin (ensuring uniqueness)

Step 3 — Insert into SpaceShip

Join staging to Passenger + Cabin to get:

• Passenger(id) and Cabin(id) Then insert the fact rows.

---

5) Integrity checks (validation queries you should run)

These checks confirm the SQL database matches the cleaned file logically.

A) Count checks

• Total rows in staging vs final fact table
• Should match (or match after intentional filtering)

Examples:

• SELECT COUNT(*) FROM StagingSpaceship;
• SELECT COUNT(*) FROM SpaceShip;

B) Duplicate checks

Ensure business keys aren’t duplicated:

• PassengerId duplicates:

  • SELECT PassengerId, COUNT(*) FROM Passenger GROUP BY PassengerId HAVING COUNT(*) > 1;

• Cabin duplicates (should be blocked by constraint):

  • SELECT Cabin, COUNT(*) FROM Cabin GROUP BY Cabin HAVING COUNT(*) > 1;

C) FK health checks

Confirm there are no orphan facts:

• SpaceShip rows with missing passenger link:

  • SELECT * FROM SpaceShip WHERE PassengerID IS NULL;

• SpaceShip rows with missing cabin link:

  • SELECT * FROM SpaceShip WHERE CabinID IS NULL;

D) Spending sanity checks

• Negative spending should not exist:

  • SELECT * FROM SpaceShip WHERE RoomService < 0 OR FoodCourt < 0 OR ShoppingMall < 0 OR Spa < 0 OR VRDeck < 0;

• Total spending consistency:

  • SELECT * FROM SpaceShip WHERE TotalSpending <> (RoomService + FoodCourt + ShoppingMall + Spa + VRDeck);

---

6) Why this SQL structure helps Power BI

• Clean relationships (Passenger ↔ SpaceShip ↔ Cabin)

• Less duplication → smaller model size

• Faster slicing by:

  • Deck / Side (Cabin)
  • HomePlanet / VIP / Age groups (Passenger)
  • Spending + Transported (SpaceShip)

---

Phase 3 — Power BI (Data Model & Visualizations)

1) Data Model – Star Schema Design

The Power BI model follows a Star Schema architecture:

• Fact Table (Center): Spaceship

• Dimension Tables:

  • Passenger
  • Cabin

This structure ensures:

• Clean filtering behavior
• No circular relationships
• Optimized performance
• Clear separation between descriptive attributes and measurable facts

---

Relationships Used

From Table	To Table	Relationship	Type
Passenger	Spaceship	Passenger.id → PassengerID	1 → *
Cabin	Spaceship	Cabin.id → CabinID	1 → *

✔ Spaceship contains the foreign keys:

• PassengerID (FK → Passenger.id)
• CabinID (FK → Cabin.id)

These same foreign keys were enforced in SQL Server using FOREIGN KEY constraints. Power BI relationships mirror the database integrity rules.

---

Model View Screenshot

This confirms:

• One-to-many relationships
• Fact table in the center
• Dimension tables on the sides
• Correct filter direction flowing from dimensions to fact

---

2) Dashboard Visualizations

---

A) Average Spending by VIP

Chart Type: Stacked Bar Axis: VIP (0 / 1) Values:

• Average of ShoppingMall
• Average of RoomService
• Average of Spa
• Average of VRDeck
• Average of FoodCourt

Business Insight:

• VIP passengers spend significantly more across all categories.
• FoodCourt and VRDeck dominate VIP spending.

---

B) Destination Distribution

Chart Type: Pie Chart Legend: Destination Values: Count of Passengers

Business Insight:

• TRAPPIST-1e dominates passenger distribution.
• 55 Cancri e and PSO J318.5-22 represent much smaller proportions.

---

C) Interactive Slicers

Slicers Added:

• HomePlanet
• CryoSleep (0 / 1)
• VIP (0 / 1)

Purpose:

• Allow dynamic segmentation.
• Enable interactive filtering across all report visuals.
• Provide business-style filtering scenarios.

---

D) Spending Distribution by Service

Chart Type: Pie Chart Values:

• Sum of FoodCourt
• Sum of RoomService
• Sum of ShoppingMall
• Sum of Spa
• Sum of VRDeck

Business Insight:

• FoodCourt contributes the highest share of revenue.
• Spa and VRDeck are also major contributors.
• ShoppingMall generates the lowest total spending.

---

E) Survival Rate by Group Size

Chart Type: Line Chart Axis: Group_Size Values: Survival Rate % (Transported ratio)

Business Insight:

• Survival probability peaks around medium group sizes.
• Very small and very large groups show lower survival rates.
• Confirms that engineered feature Group_Size provides analytical value.

---

F) Total Spending vs Solo Passengers by Age

Chart Type: Dual Axis Line Chart Axis: Age Values:

• Sum of TotalSpending
• Sum of Is_Solo

Business Insight:

• Spending peaks around mid-age passengers.
• Solo passengers are concentrated within specific age ranges.
• Demonstrates interaction between demographic and behavioral features.

---