Day 27/30 SQL, Python, ETL, Data Modelling Challenge FREE Solutions

April 1st CHALLENGE – unlock solutions + reasoning

👋 Hey Data Engineers!

Welcome to Day 27 of the 30-Day Data Engineering Challenge.
Today’s challenge will sharpen your core decision-making on:

• Handling NULLs with COALESCE in SQL

• Python list comprehensions

• Airflow task dependencies

• Normalization principles (3NF)

SQL Challenge – NULL Handling with COALESCE

Challenge Recap:
❓ What will this query return?

SELECT COALESCE(SUM(salary), 0)
FROM employees
WHERE department = 'Marketing';

✅ Answer: B - 0 if there are no marketing employees

Explanation:
If no employees exist in the 'Marketing' department, SUM(salary) returns NULL. COALESCE() replaces that with 0, making the query return a non-null default.

Best Practices:

• Always wrap aggregations in COALESCE() when querying filtered data

• Prevents unexpected NULLs in reports or dashboards

• Use COALESCE(col, default) for safe transformations

---

Python Challenge – List Comprehensions with Conditionals

Challenge Recap:

nums = [1, 2, 3, 4, 5]
evens = [x for x in nums if x % 2 == 0]
print(evens)

✅ Answer: B - [2, 4]

Explanation:
The list comprehension filters numbers divisible by 2 and stores them. x % 2 == 0 checks for even numbers.

Best Practices:

• Use list comprehensions for compact filtering

• Combine if with for for readable, clean logic

• Avoid putting complex logic inside list comprehensions—use functions instead

---

ETL Challenge – Airflow Task Dependencies

Challenge Recap:
❓ What does depends_on_past=True do in Airflow?

✅ Answer: D - Ensures task only runs if it succeeded in the previous DAG run

Explanation:
This setting links task runs across DAG executions. It ensures a task won’t run today if it failed yesterday. Critical for time-based pipelines and data integrity.

Best Practices:

• Use depends_on_past for incremental jobs

• Set wait_for_downstream=True for downstream coordination

• Use cautiously—misuse can block DAG progress

---

Data Modeling Challenge – Understanding 3NF

Challenge Recap:
❓ Which of the following is true about Third Normal Form (3NF)?

✅ Answer: B - Eliminates all transitive dependencies

Explanation:
3NF ensures that non-key attributes depend only on the primary key. It eliminates dependencies that chain through intermediate columns, helping reduce redundancy and update anomalies.

Best Practices:

• Normalize transactional systems up to 3NF

• Avoid over-normalizing for reporting systems (OLAP)

• Keep dimension tables clean and historical (use surrogate keys)

---

📌 Want Deep Dive walkthroughs + OneCompiler test cases + mock interview prep?

Upgrade to Annual or Elite Plan now at 👉 zero2dataengineer.substack.com

💬 Drop your answers in the comments.
🎯 Monthly winners get featured + recruiter visibility.
📢 Tag us on LinkedIn @zero2dataengineer if you're loving the challenge!