For semi-structured JSON logs with frequent last 10 days access, which modeling approach is recommended?

• A. Flatten the object and store the date value in a relational column
• B. Store everything in a VARIANT column without flattening
• C. Convert the JSON payload to CSV and store each column relationally
• D. Create separate tables for each date

Answer: (A)

The main idea is to enable fast filtering on the time dimension by turning the relevant part of the JSON into a regular column. For logs that are semi-structured and you frequently need the last 10 days, having a dedicated date value stored in a relational column allows Snowflake to prune data quickly and use relational operations and clustering effectively. Flattening the JSON so you extract and store the date (and other commonly queried fields) in normal columns gives you predictable, fast access paths for your most recent data, while the remaining payload can still be kept in a semi-structured form if needed.

Storing everything in a VARIANT keeps the data as a single semi-structured blob, which makes it harder for the engine to prune by date and to leverage statistics and clustering. Each query would likely need JSON path extraction to filter by date, which adds overhead and slows down frequent last-10-days access. Converting to CSV and storing relationally loses the benefits of nested structure and can be brittle when the payload is truly semi-structured. Creating separate tables for each date introduces maintenance overhead and makes cross-date analytics awkward.

So, by flattening the object and placing the date in a relational column, you get fast, scalable access to the most recent data with straightforward SQL, while still retaining the flexibility of the original semi-structured payload as needed.