SnowPro Advanced Data Engineer

By Snowflake · Question Mode

QuestionQ79

Design and Manage Snowflake Resources and Performance

A company developed a sales-reporting system in Python that connects to Snowflake through the Python Connector. From the user's selections, the system creates the SQL queries required to retrieve report data. It first retrieves the customers that meet the specified query parameters (averaging 1,000 customer records per report run), then sequentially iterates through those customer records. Within that loop, it runs the generated SQL clause for the current customer to retrieve detailed data for that customer number from the sales data table.

When the Data Engineer tested the individual SQL clauses, they performed quickly enough (1 second to retrieve the customers and 0.5 second to retrieve sales data for one customer), but the overall report runtime is too long.

How can this situation be improved?

Explanation

Retrieving detail data through a sequential loop creates an N+1 query pattern: roughly 1,000 individual detail queries at 0.5 seconds each add about 500 seconds before query and network overhead. A set-based SQL operation can retrieve the detailed sales data for all qualifying customers in a single query, such as by joining the customer selection to the sales table. The Snowflake Python Connector executes each submitted query separately, so eliminating repeated executions directly reduces the total runtime.

Learn more

Community Discussion

No comments yet. Be the first to start the discussion!