Top Snowflake Interview Questions & Answers (2026)

A clone creates a new table, schema or database that points to the same micro-partitions as the source, so it is nearly instant and initially costs no extra storage. Because micro-partitions are immutable, when either side changes data, new micro-partitions are written and only those are billed separately. I use it to spin up dev or test environments from production in seconds, to snapshot a table before a risky migration, and combined with Time Travel to clone a table as of yesterday for investigation. Things to watch: cloning a database or schema copies child objects and their grants, but not the grants on the cloned container itself, and long-lived clones of heavily changed tables do accumulate storage.

I create a stream on the raw table; it records changes since its offset, with metadata columns showing whether each row is an insert or delete and whether it is part of an update. A task then runs a MERGE from the stream into the target. The key detail is that the stream offset only advances when the stream is consumed inside a successful DML transaction, so if the MERGE fails, the changes are still there next run. I add a WHEN SYSTEM$STREAM_HAS_DATA condition so the task skips when nothing changed and does not spin up compute. For multi-step pipelines I chain tasks into a graph. I also monitor for stale streams, which happen if the stream is not consumed within the table's retention period.

COPY INTO is batch: I run it, usually from an orchestrator, against files in a stage, using a warehouse. Snowflake keeps load metadata so the same file is not loaded twice within that window. Snowpipe is continuous, serverless file ingestion: a cloud event notification or REST call tells it a new file landed, and it loads within minutes, billed for the serverless compute used. Snowpipe Streaming skips files entirely and writes rows directly through an SDK or connector, such as Kafka, for low-latency streams. For a daily extract, COPY INTO on a schedule is simplest. For files arriving all day, Snowpipe. For event streams where seconds matter, Snowpipe Streaming. I also size files sensibly, roughly 100 to 250 MB compressed, so loads parallelise well.

Snowflake uses role-based access control: privileges go to roles, roles go to users or other roles, and every object has an owning role. System roles include ACCOUNTADMIN, SECURITYADMIN, USERADMIN and SYSADMIN, and I keep ACCOUNTADMIN to a tiny group with MFA. For a new team I would create access roles per database or schema, like analytics read and analytics write, holding the object privileges, plus future grants so new tables are covered. Then functional roles like data engineer or analyst get the access roles they need, and functional roles roll up to SYSADMIN so admins keep visibility. Users get functional roles, never direct object grants. Sensitive columns get masking policies and row access policies where needed.

I land raw JSON into a VARIANT column, which preserves the structure and lets me query it with path notation, like payload:customer.id cast with double colon to a type. For arrays, I use LATERAL FLATTEN to turn each element into a row, for example order lines inside an order document. For performance, Snowflake stores common paths in VARIANT columnar internally, but I still shred frequently queried fields into typed columns in a cleaned layer, because typed columns prune and compress better, and analysts should not need path syntax. I keep the raw VARIANT too, so if the source adds fields I can backfill without re-ingesting. Null handling needs care: a JSON null and a missing key behave differently.