DEA-C02 · Question #53
A company built a sales reporting system with Python, connecting to Snowflake using the Python Connector. Based on the user's selections, the system generates the SQL queries needed to fetch the…
The correct answer is D. Rewrite the report to eliminate the use of the loop construct. Option D is correct because the loop creates an N+1 query problem: with 1,000 customers each requiring a 0.5-second query, the loop alone accounts for ~500 seconds of runtime - a fundamental architectural flaw that no infrastructure change can fix. Rewriting the report to use a…
Question
A company built a sales reporting system with Python, connecting to Snowflake using the Python Connector. Based on the user's selections, the system generates the SQL queries needed to fetch the data for the report. First it gets the customers that meet the given query parameters (on average 1000 customer records for each report run), and then it loops the customer records sequentially. Inside that loop it runs the generated SQL clause for the current customer to get the detailed data for that customer number from the sales data table. When the Data Engineer tested the individual SQL clauses, they were fast enough (1 second to get the customers, 0.5 second to get the sales data for one customer), but the total runtime of the report is too long. How can this situation be improved?
Options
- AIncrease the size of the virtual warehouse.
- BIncrease the number of maximum clusters of the virtual warehouse.
- CDefine a clustering key for the sales data table.
- DRewrite the report to eliminate the use of the loop construct.
How the community answered
(71 responses)- A4% (3)
- B8% (6)
- C3% (2)
- D85% (60)
Explanation
Option D is correct because the loop creates an N+1 query problem: with 1,000 customers each requiring a 0.5-second query, the loop alone accounts for ~500 seconds of runtime - a fundamental architectural flaw that no infrastructure change can fix. Rewriting the report to use a single JOIN or IN clause retrieves all customers and their sales data in one round trip, reducing runtime from ~500 seconds to roughly 1–2 seconds.
Why the distractors fail:
- A (larger warehouse): The bottleneck is 1,000 sequential network round trips, not compute power - a bigger warehouse won't reduce the number of queries.
- B (more clusters): Multi-cluster scaling addresses concurrent user load, not a single report's sequential loop.
- C (clustering key): Each individual query is already fast (0.5s); the problem is quantity of queries, not per-query scan efficiency.
Memory tip: If individual queries are fast but the total is slow, count the queries first. "Loop + SQL = N+1 problem" - and the fix is always to collapse N queries into one set-based operation (JOIN, IN, or CTE).
Topics
Community Discussion
No community discussion yet for this question.