For high-volume, recurring loads into the same table, PURGE = TRUE is critical for performance. It automatically removes the source files from the internal stage after a successful load, preventing the stage from becoming cluttered with millions of processed files. This avoids performance degradation on subsequent COPY commands that would otherwise need to scan all historical files to find new ones. The other options are useful in specific scenarios but do not directly address the core performance issue of file management in a recurring load pattern.

A stream’s staleness indicates how long ago the source table was modified. If STALENESS shows a timestamp from 4 days ago, it directly implies that no DML operations (INSERT, UPDATE, DELETE, MERGE) have occurred on the underlying table in that period. The stream itself does not become invalid due to age; it becomes invalid only if the underlying table is dropped or has its retention period changed such that the historical data the stream tracks is no longer available. The observation is a simple indicator of table activity, not necessarily a problem.

DOT notation (e.g., SELECT data.field FROM table) is the most resilient and clear approach for extracting values from a VARIANT column when building a view for end-users. It returns NULL if the path does not exist in the JSON, preventing query errors if the schema evolves and some records lack the field. This makes the view robust to change. Explicit casting with colon notation would throw an error if the path is missing. Lateral flatten is for transforming nested arrays into rows, not simple extraction. PARSE_JSON() is for converting a string to VARIANT, not extracting from it.

The UNDROP TABLE command is the simplest and most direct method to restore a recently dropped table. It recovers the table to its state just before the DROP operation, including all data and schema. The first option using CLONE with a timestamp is syntactically incorrect for a dropped object and is more complex than needed. Using CREATE TABLE … AS SELECT with AT is possible but only recovers the data, not the full table schema, constraints, etc. ALTER TABLE … RESUME is used for resuming a paused pipe, not recovering a table.

This tests a key cost-performance trade-off. Suspending a warehouse stops all compute charges but results in the loss of the local disk cache, which can speed up subsequent queries on the same data. Resizing a warehouse to its minimum size (X-Small) significantly reduces compute cost per second while keeping the warehouse active and preserving its cache. This is a useful strategy for periods of low, but non-zero, query activity where maintaining cache performance is valuable.

A secure view is the correct and standard mechanism for providing a filtered, row-level secure subset of data through a share. The view’s definition, including the WHERE STATUS != ‘INTERNAL’ clause, is executed in the provider’s context, and only the resultant rows are shared. The consumer cannot bypass the filter. A secure materialized view could work but introduces unnecessary data duplication and refresh overhead. A secure UDF is overly complex for simple filtering. Creating a new table is a manual, non-dynamic process that doesn’t automatically reflect changes in the source data.

Explicit transaction control using BEGIN TRANSACTION and ROLLBACK is the direct feature for this scenario. By wrapping the three DML statements within a BEGIN … COMMIT block, you define them as an atomic unit. If you detect an error after the DELETE, you can execute ROLLBACK to undo the effects of the UPDATE, INSERT, and DELETE, restoring the tables to their state before the BEGIN. Time Travel could recover from a committed transaction but is a point-in-time recovery mechanism, not an in-flight rollback. The other options are unrelated to transactional atomicity.

For time-series data that is inserted in chronological order, the timestamp column itself is the ideal clustering key. Snowflake’s micro-partitions are created in the order data is inserted. If the data is already ordered by time on insert, new data naturally falls into new, temporally sorted micro-partitions. This provides excellent pruning for queries filtering on time ranges with minimal to no need for automatic clustering services to reorganize data, thus reducing maintenance overhead. A high-cardinality key or composite key would force automatic clustering to work harder. Relying solely on automatic clustering is less efficient than leveraging natural insert order.

The SYSTEM$CLUSTERING_INFORMATION function is the primary diagnostic tool for clustering effectiveness. It provides metrics like average overlap and depth, which indicate how well-organized the data is according to the clustering key. Running this function is a zero-cost, immediate step to understand if poor clustering is contributing to the slow performance. Based on its output, you can make an informed decision about whether RECLUSTER is warranted. Running RECLUSTER immediately is a costly action that may be unnecessary. Increasing warehouse size or creating a materialized view are more expensive solutions that should be considered only after diagnosing the root cause.

For high-volume, recurring loads into the same table, PURGE = TRUE is critical for performance. It automatically removes the source files from the internal stage after a successful load, preventing the stage from becoming cluttered with millions of processed files. This avoids performance degradation on subsequent COPY commands that would otherwise need to scan all historical files to find new ones. The other options are useful in specific scenarios but do not directly address the core performance issue of file management in a recurring load pattern.0