Day 24/30 SQL, Python, ETL, Data Modelling Challenge FREE Solutions 🚀
March 27th, 2025 CHALLENGE – unlock solutions + reasoning
👋 Hey Data Engineers!
Difficulty Level: Intermediate → Advanced
Today we’re diving into indexing, conditional list comprehension, Airflow orchestration, and tracking historical changes in dimensional models. These topics are must-know for production-grade systems.
Understand, Don’t Memorize:
✅ Clear, practical explanations
✅ Production-ready tips
✅ Interview-first mindset
Want runnable code + exclusive deep dives? Upgrade to the Annual Plan and fast-track your DE skills.
📌 SQL Challenge – Indexing for Query Optimization
❓ Which query would benefit MOST from adding an index on the email column?
🔘 A) SELECT * FROM users WHERE email = 'x@example.com'
🔘 B) SELECT COUNT(*) FROM users
🔘 C) SELECT * FROM users ORDER BY id DESC
🔘 D) SELECT DISTINCT department FROM users
✅ Answer: A - WHERE email = 'x@example.com'
Explanation:
Indexing helps when filtering or joining on specific column values. An index on email speeds up lookups drastically.
Best Practices:
✔️ Add indexes to high-cardinality, frequently filtered columns
✔️ Avoid over-indexing (it slows writes)
✔️ Use EXPLAIN to verify performance gains
🐍 Python Challenge – List Comprehension with Conditionals
nums = [1, 2, 3, 4, 5, 6]
result = [x * 2 for x in nums if x % 2 == 0]
print(result)❓ What will be the output?
🔘 A) [2, 4, 6, 8, 10, 12]
🔘 B) [4, 8, 12]
🔘 C) [2, 4, 6]
🔘 D) [4, 8]
✅ Answer: B - [4, 8, 12]
Explanation:
It filters even numbers (x % 2 == 0) → [2, 4, 6], then doubles them: [4, 8, 12].
Best Practices:
✔️ Use list comprehensions for clean, readable filtering + transformation
✔️ Avoid unnecessary temp lists
✔️ Replace for-loops where possible
⚡ ETL Challenge – Orchestrating Pipelines
❓ Which of these tools is most commonly used for task orchestration in ETL?
🔘 A) dbt
🔘 B) Apache Airflow
🔘 C) Kafka
🔘 D) PostgreSQL
✅ Answer: B - Apache Airflow
Explanation:
Airflow is the industry standard for defining and managing ETL workflows via DAGs (Directed Acyclic Graphs).
Best Practices:
✔️ Use task dependencies to control execution order
✔️ Monitor and retry failed tasks via Airflow UI
✔️ Combine with sensors for event-driven pipelines
🧱 Data Modeling Challenge – Slowly Changing Dimensions
❓ Which SCD type preserves full history of changes to a dimension?
🔘 A) Type 0
🔘 B) Type 1
🔘 C) Type 2
🔘 D) Type 3
✅ Answer: C - Type 2
Explanation:
SCD Type 2 stores a new row for each historical change, ensuring you can always see what the dimension looked like at any point in time.
Best Practices:
✔️ Add effective_date, end_date, and is_current flags
✔️ Use surrogate keys to join facts to correct version
✔️ Automate inserts via merge logic
🚀 Final Stretch — Let’s Finish Strong!
You’re just a few days from completing this 30-day transformation.
✅ Join for hands-on SQL/Python projects
✅ Get exclusive mock interview questions
✅ Deep-dive with working code examples
👉 Subscribe here: zero2dataengineer.substack.com
💬 Drop your answers in the comments — best responses = shoutouts tomorrow