ARA-C01 Question 343
Single answerRelationship and impact between the view and data typesA retail company exposes order data to analysts through a secure view. The base table stores semi-structured payloads in a VARIANT column named ORDER_JSON. A developer creates the view below so BI tools can query the data more easily:
CREATE SECURE VIEW reporting.v_orders AS SELECT ORDER_JSON:order_id AS order_id, ORDER_JSON:customer_id AS customer_id, ORDER_JSON:order_total AS order_total, ORDER_JSON:created_at AS created_at FROM raw.orders;
Analysts report inconsistent behavior: some tools treat ORDER_TOTAL as text, date filters on CREATED_AT do not work reliably, and joins on ORDER_ID sometimes require explicit casting. The architect wants to improve downstream usability while preserving the secure view pattern.
Which change should the architect make to the view definition to best address these issues?
- A
Redefine the view so each projected JSON element is explicitly cast to the intended SQL data type, for example ORDER_JSON:order_id::NUMBER, ORDER_JSON:order_total::NUMBER(12,2), and ORDER_JSON:created_at::TIMESTAMP_NTZ.
- B
Convert the secure view to a materialized view so Snowflake automatically infers fixed column data types from the VARIANT paths.
- C
Keep the current view definition and rely on BI tools to infer the correct types at query time, because Snowflake views do not preserve column data types for expressions over VARIANT.
- D
Replace the secure view with a standard view because secure views return semi-structured columns differently and are the reason BI tools see string-like values.
- E
Add clustering keys on the base table for the JSON paths used in the view, because clustering changes how VARIANT fields are typed when exposed through views.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to explicitly cast each VARIANT path expression in the view definition to the intended relational SQL type. In Snowflake, extracting values from semi-structured data with path notation returns values derived from VARIANT; without explicit casting, downstream tools may not interpret those fields as strongly typed NUMBER, DATE, or TIMESTAMP columns. This commonly leads to issues with BI tool schemas, filtering, sorting, and joins.
For architected data access layers, especially when using secure views for governance, best practice is to expose stable, consumer-friendly column types in the view itself rather than relying on client-side inference. This is particularly important for timestamps, numerics, and identifiers used in joins. Snowflake documentation on querying semi-structured data and casting/conversion functions supports this approach: path extraction from VARIANT can be followed by explicit casts such as ::NUMBER, ::VARCHAR, ::DATE, or ::TIMESTAMP_NTZ to produce predictable relational semantics.
Secure views do not change the fundamental data typing of expressions; they secure data exposure. Likewise, clustering affects storage organization and pruning, not the output data types of view columns.
- A. Correct.
Correct. When selecting values from VARIANT using path notation, the resulting expression is still a semi-structured value unless explicitly cast. Defining the view with explicit casts gives downstream consumers stable relational types such as NUMBER and TIMESTAMP, which improves BI interoperability, filtering behavior, and joins. This is the recommended approach when exposing semi-structured data through views for governed consumption.
- B. Incorrect.
Incorrect. Materialized views do not automatically infer or convert VARIANT path expressions into the desired fixed SQL types. If the architect wants typed relational columns, explicit casting is still required in the projection. Also, materialized views introduce additional design constraints and maintenance considerations and are not the primary fix for this usability problem.
- C. Incorrect.
Incorrect. Snowflake views do preserve the data type of the projected expression. The issue here is that the projected expressions are VARIANT-derived values without explicit casts, so consumers may interpret them inconsistently. Relying on BI tools to infer types is a common but fragile approach and does not provide the governed, stable schema expected from an architected semantic layer.
- D. Incorrect.
Incorrect. The problem is not caused by the object being a secure view. Secure views primarily affect optimization and metadata exposure, not the logical typing behavior of columns selected from VARIANT. A standard view with the same expression list would have the same downstream typing issue if the values were not explicitly cast.
- E. Incorrect.
Incorrect. Clustering can improve pruning and query performance in some scenarios, but it does not change the SQL data type of expressions returned by a view. Typing of projected columns is determined by the view's SELECT expressions, not by clustering strategy on the base table.