ON_ERROR = CONTINUE instructs the COPY command to skip rows that cause errors (like malformed JSON in a column destined for a VARIANT) and continue loading the rest of the file, logging the errors. This is essential for robust, automated pipelines where a single bad record shouldn’t halt the entire load. PURGE removes files from the stage, MATCH_BY_COLUMN_NAME handles column mapping, and FORCE reloads files regardless of their load history.

A Materialized View pre-computes and stores the result of a query. For a small, static dimension table that is joined frequently, a materialized view containing the join could dramatically improve performance by serving the pre-joined result. Clustering is for large tables, Search Optimization is for point lookups, and the Result Cache helps repeated identical queries but doesn’t pre-compute joins.

A medium warehouse with auto-suspend offers a good balance. It provides sufficient power for common queries on recent data (aided by pruning). It suspends during idle periods, saving costs. The occasional full scan will take longer but is acceptable for a non-frequent operation. A large always-running warehouse is costly, multi-cluster is for concurrent users, and splitting warehouses adds unnecessary complexity here.

A secure view encapsulates the logic (SELECT with column/row filters) to expose only the intended data. Sharing this view via a direct Snowflake share allows the partner to query it as if it were a local table, without provisioning a separate account or exposing any underlying objects. It provides live access with precise control and is managed entirely within Snowflake’s secure sharing framework.

Snowflake tracks files loaded via COPY commands using metadata. If a previous load failed after registering the file but before committing, the file is marked as ‘loaded’ even though the data isn’t in the table. The FORCE = TRUE option tells COPY to load the file regardless of its load history, which is the correct solution here.

The standard SCD Type 2 logic is to first expire the current active record by updating its IS_CURRENT flag and end date, then insert the new version as the active record. This order is important to maintain data integrity. A MERGE statement can also accomplish this in one step, but the explicit sequence described is the classic, clear implementation.

Streams operate within Snowflake’s transaction semantics. If a stream is queried in a session (starting a transaction) and that transaction is not committed or rolled back, the change data remains consumed from that stream’s perspective for that transaction. Other sessions will see the stream as empty until the holding transaction is finalized. This is a common pitfall in stream usage.

Snowflake’s Query Result Cache stores the results of every query for 24 hours. If an identical query is submitted (same SQL text, same role/context, same underlying data), the result is served instantly from this cache. The second run benefits from this, not from the warehouse cache (which stores table data) or clustering.

To create an object in Snowflake, a role needs two things: the USAGE privilege on the parent object (the database) and the specific CREATE privilege for the object type. Therefore, both USAGE on the database and CREATE SCHEMA on that specific database are required. Ownership is too permissive, and account-level privileges are not needed.

ON_ERROR = CONTINUE instructs the COPY command to skip rows that cause errors (like malformed JSON in a column destined for a VARIANT) and continue loading the rest of the file, logging the errors. This is essential for robust, automated pipelines where a single bad record shouldn’t halt the entire load. PURGE removes files from the stage, MATCH_BY_COLUMN_NAME handles column mapping, and FORCE reloads files regardless of their load history.0