Day 17/30 - DEEP DIVE SOLUTIONS: SQL, PYTHON, ETL, DATA MODELING

Solutions for March 18th, 2025 CHALLENGE – Unlock the Full Breakdown + Live Runnable Code!

👋 Hello Data Engineers!

Today, you’re diving into an exclusive deep dive into SQL Indexing, Python Memory Optimization, ETL Integrity, and Data Modeling Granularity. If you’ve upgraded, you’re already ahead of 1000+ new data engineers mastering these concepts with hands-on breakdowns.

If you haven’t upgraded to Deep Dive yet, you’re missing out. This is where we go beyond basic answers—giving you expert breakdowns, runnable code, and real-world optimizations that will set you apart in job interviews and real DE projects.

💡 Upgrade now and stay ahead of the competition!

---

📌 SQL Deep Dive: Query Performance with Indexing

Challenge Recap:

❓ Which SQL query is least likely to use an index efficiently?

🔘 A) SELECT * FROM users WHERE email = 'test@email.com';
🔘 B) SELECT * FROM users WHERE LOWER(name) = 'john doe';
🔘 C) SELECT * FROM users ORDER BY id;
🔘 D) SELECT * FROM users WHERE age > 30;

Answer: Option B - SELECT * FROM users WHERE LOWER(name) = 'john doe';

Why This Happens:

Applying a function like LOWER() to an indexed column can prevent the database from utilizing the index efficiently, leading to a full table scan. Indexes store data in its original form, so transformations during retrieval can hinder their effectiveness.

Run & Test on OneCompiler:

1️⃣ Open OneCompiler and select PostgreSQL.
2️⃣ Copy and paste the following SQL to create a sample table:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT,
    email TEXT UNIQUE,
    age INT
);

INSERT INTO users (name, email, age) VALUES
('John Doe', 'john@example.com', 35),
('Jane Smith', 'jane@example.com', 28),
('Alice Johnson', 'alice@example.com', 40);

CREATE INDEX idx_users_name ON users (name);

3️⃣ Test the indexed vs. non-indexed query performance:

EXPLAIN ANALYZE
SELECT * FROM users WHERE LOWER(name) = 'john doe';

4️⃣ Click Run and analyze the output.

What’s Happening in the Output?

If the execution plan shows a Seq Scan (sequential scan), it indicates that the index on name is not being utilized due to the LOWER() function. This results in the database scanning each row individually, leading to slower performance.

Best Practices:

• Use Functional Indexes: Create indexes on expressions to allow the database to utilize them even when functions are applied in queries. For example:

CREATE INDEX idx_lower_name ON users (LOWER(name));

• CopyEdit

  CREATE INDEX idx_lower_name ON users (LOWER(name));

• Store Transformed Data: Store data in the desired format (e.g., all lowercase) to eliminate the need for functions in queries.

• Avoid Functions in WHERE Clauses: Refrain from using functions on indexed columns within WHERE clauses to ensure optimal index usage.

Optimization Techniques & Takeaways:

✅ Implement functional indexes to maintain efficient query performance when functions are necessary in conditions.
✅ Regularly analyze and update statistics to assist the query planner in making informed decisions.
✅ Design queries and data storage strategies that align with indexing capabilities to enhance performance.

---