77 ) waste mgt project

 

Waste Management Project Data Model & Interview Q&A

Step 1: The Client's Existing System and Tables

In my previous waste management project, the client was using basic operational databases to manage garbage collection trucks, bin locations, and daily collection routes:

• Fleet Database (MySQL): This handled garbage trucks and driver schedules. The main tables were:

  • Trucks: Stored truck numbers, vehicle types, and max weight capacities.

  • Drivers: Stored driver names, phone numbers, and shift timings.

• Bin & Route Database (SQL Server): This handled smart bins across the city and scheduled routes. The main tables were:

  • Bins: Stored bin IDs, street locations, bin sizes, and area zones.

  • Collection_Logs: Stored route IDs, assigned truck numbers, timestamps when bins were emptied, and collected waste weight.

Step 2: The Problems They Faced

Because truck details and bin collection records were stored in two different databases, the operations team faced simple daily problems:

• Scattered Data: If a manager wanted to check which area had the most overflowing bins or which truck used the most fuel, they had to open two different systems and match the data manually in Excel.

• Slow Reports: Running daily garbage collection reports or checking truck utilization directly on the live database made the tracking software slow for workers in the field.

• Missing History: If a garbage collection zone expanded or a bin was moved to a new street, the old location details were completely erased, making it impossible to check last month's exact route performance.

Step 3: The Client's Expectation and Why

• The Goal: They wanted a single data warehouse where all truck, bin, and waste collection details could live together.

• The Reason: They wanted fast daily reports to track total waste weight collected, monitor overflowing bins, and check truck driver performance without slowing down the live field app.

Step 4: The Solution Design I Proposed (Star Schema)

1. Defined What to Measure

We focused on two main things: Waste Collection (total weight collected from areas) and Fleet Operations (truck trips and collection time).

2. Designed the Dimension Tables (The Details)

• Dim_Bin: Stored bin IDs, street locations, and area zones.

• Dim_Truck: Stored truck numbers, vehicle types, and weight capacities.

• Dim_Driver: Stored driver names and shift timings.

• Dim_Date: Stored calendar dates so reports could be run by day, week, or month.

3. Designed the Fact Tables (The Numbers)

• Fact_Collections: Stored bin IDs, truck IDs, collection timestamps, and total waste weight collected in kilograms.

• Fact_Truck_Trips: Stored trip numbers, fuel used, total distance traveled, and trip duration in minutes.

Interview Questions and Answers: Waste Management Project

Q1: Can't the waste management team just run these reports directly on the MySQL database? Why do we need a Data Warehouse?

Answer:

If we run heavy reports on the live MySQL database, it slows down the app for field workers trying to update bin statuses. The live database is built for quick everyday data entry, not for scanning millions of past collection logs to check monthly waste trends. A Data Warehouse keeps heavy reporting separate so the operational app stays fast.

Q2: What if you have to add a new column or change a dimension later? Is it easy to do?

Answer:

Yes, it is easy. If we want to add a new detail—like adding a "Waste Type" (such as Plastic, Organic, or Electronic) column to the bin table—we just update the table structure and change our daily data loading script for future records. It does not break past data.

Q3: What if the fact table data needs to be loaded weekly or monthly instead of daily? How do you handle that?

Answer:

We handle this by scheduling our data loading jobs. While regular collection logs are loaded every night, monthly summary tables are set to run on the 1st of every month. We also split our large data tables by date so the system only updates the specific week or month needed instead of scanning everything.

Q4: How do you handle late-arriving collection logs or weight updates that come in days later?

Answer:

We use the original collection date instead of the date the data was loaded. When the late data arrives during our nightly run, our script places it into the correct past date section in the data warehouse, so our old daily and weekly reports update automatically with the correct numbers.

Q5: What is a "Factless Fact Table," and did you use one in the waste management project?

Answer:

A factless fact table is a table that has no money values or numbers inside it; it only tracks events or connections. Yes, we used one to track Bin Inspection Audits (Fact_Bin_Inspections). It recorded which worker inspected which street bin on a specific day, helping management check sanitation audit completion rates without tracking waste weight.

Q6: How do you handle changes to a bin's details (like moving a bin to a new street) when past collections are already linked to it?

Answer:

We use Slowly Changing Dimensions (SCD Type 2). If a bin is moved to a new location, we don't overwrite the old address. We close the old row with an end date and add a new row with the new address. This way, old collection records always point to the bin's address as it was on the day the garbage was picked up.

Q7: What is the difference between an Additive and Non-Additive fact in our waste management data?

Answer:

• Additive Fact: These can be added up across everything. For example, Waste_Weight_Kg is additive—we can add it up by truck, zone, or month to get total waste collected.

• Non-Additive Fact: These cannot be added up. Ratios or percentages fall here, like a Truck_Fuel_Efficiency_Average. You cannot add averages together; the data warehouse has to calculate them fresh.

Q8: How do you stop duplicate collection records from coming into the data warehouse?

Answer:

We stop duplicates in two ways. First, our daily loading script cleans the data and drops exact technical duplicates. Second, we set unique rules on our fact table using the Collection_ID and Timestamp, so if the same file is accidentally loaded twice, the system skips the duplicate rows.

Q9: What happens if a collection log comes in for a new truck whose ID does not exist in the truck table yet?

Answer:

To prevent the data loading script from crashing, we use a default row called "Unknown Truck" with an ID of -1. If a missing ID comes in, the script assigns it to -1 so the report doesn't break, and it sends an alert to our team to add the new truck to the system.

Q10: If the business asks to add a completely new metric that we never tracked in the past, what do you tell them?

Answer:

If we never collected that data in the past, I would explain to the business team that we cannot magically create old historical data. I would update our data collection script starting from today so we can track that new metric moving forward, and note clearly in the reports that older data is not available.