SOL-C01 Question #157: Real Exam Question with Answer & Explanation

Question

You are working with a Snowflake table named 'transactions' that contains a 'transaction_time' column of data type 'TIMESTAMP NTZ'. You need to retrieve all transactions that occurred within the last 24 hours, and you want to optimize this query for performance. Which of the following approaches would be the MOST efficient?

Options

• ASELECT FROM transactions WHERE transaction_time DATEADD(hour, -24,
• BSELECT FROM transactions WHERE transaction_time - INTERVAL '24 hours'
• CSELECT FROM transactions WHERE transaction_time BETWEEN - INTERVAL '24 hours' AND
• DSELECT FROM transactions WHERE transaction_time CONVERT TIMEZONE('UTC',
• ESELECT FROM transactions WHERE transaction_time SYSDATE() - (24/24);

Explanation

Option B is the most efficient and recommended approach. Using 'CURRENT TIMESTAMP() - INTERVAL '24 hours" directly leverages Snowflake's internal time handling and avoids unnecessary function calls. Option A will work, but 'DATEADD& function might be slower compared to subtracting the interval directly- Option C is less performant because BETWEEN requires calculation of both upper and lower bounds, adding unnecessary computatiom Option D includes timezone conversion, which introduces overhead and is not necessary if the `transaction_time' is already in UTC or if the timezone doesn't matter. Option E, SYSDATE() - (24/24)' will produce same results since it gets the current timestamp and subtract one day's worth of data from it As the value is still relative to the timezone of the system, not UTC, and the subtraction might not be optimized for performance like 'INTERVAL' , making it less ideal.

No community discussion yet for this question.