ARA-C01 Question 13
Single answerSession parametersA Snowflake architect is troubleshooting inconsistent timestamp query results across multiple BI tools and ad hoc SQL clients. The data in a central table is stored as TIMESTAMP_TZ, but users report that the same query returns different displayed time values depending on the client connection. The architect wants to standardize how timestamps are interpreted and displayed for each user session without changing the underlying table definition or rewriting every query. Which action should the architect take?
- A
Set the TIMEZONE and TIMESTAMP_OUTPUT_FORMAT session parameters for each session, either directly or through a user/account-level default
- B
Convert all TIMESTAMP_TZ columns to VARCHAR so every client receives the same textual representation
- C
Create a virtual warehouse for each BI tool and assign a warehouse-specific timezone configuration
- D
Change the table columns from TIMESTAMP_TZ to TIMESTAMP_NTZ so session settings no longer affect query results
Show answer and explanation
Correct answer: A
Explanation
Snowflake separates data storage from session-level interpretation and display. For timestamp-related consistency issues, architects should use session parameters such as TIMEZONE and timestamp format parameters (for example, TIMESTAMP_OUTPUT_FORMAT) rather than altering schema design. These parameters can be set with ALTER SESSION for the current connection, or established as defaults at the user or account level so client tools inherit consistent behavior when sessions are created. This aligns with Snowflake best practices: preserve proper timestamp data types and use session parameters to control formatting and timezone behavior. Warehouses do not manage session formatting, and changing timestamp columns to strings or different timestamp variants is an unnecessary and risky workaround.
- A. Correct.
Correct. Session parameters such as TIMEZONE and TIMESTAMP_OUTPUT_FORMAT control how timestamp values are interpreted and rendered in a given session. In Snowflake, these can be set at the session level and can also be inherited from user- or account-level defaults. This is the appropriate way to standardize presentation behavior across clients without modifying stored data or changing application SQL.
- B. Incorrect.
Incorrect. Converting TIMESTAMP_TZ values to VARCHAR would remove native timestamp semantics, complicate filtering, sorting, and time-based calculations, and is not a best practice for solving client/session display inconsistencies. It addresses presentation by denormalizing the data type rather than using the intended session controls.
- C. Incorrect.
Incorrect. Virtual warehouses control compute resources, not SQL session formatting or timezone behavior. Timezone handling and output formatting are governed by session parameters, not warehouse properties. This distractor reflects a common misconception that execution environment settings are tied to warehouse configuration.
- D. Incorrect.
Incorrect. Changing TIMESTAMP_TZ to TIMESTAMP_NTZ would alter how temporal data is stored and interpreted, potentially losing timezone context. It also does not solve the core requirement of standardizing display behavior per client session. Session parameters remain the correct mechanism for controlling presentation.