Snowflake interview questions

Three layers: storage (compressed columnar micro-partitions), compute (virtual warehouses) and cloud services (metadata, security, optimisation). Storage and compute scale separately.

A compute cluster that runs queries. It bills per second while running, with a 60 second minimum each time it starts.

Small immutable storage units (about 50 to 500 MB uncompressed). Snowflake keeps min and max values per column, so it can skip partitions that do not match a filter.

A location for files before loading: internal (inside Snowflake) or external (Azure Blob, S3, GCS).

COPY INTO from a stage for batch loads, Snowpipe for continuous auto-ingest.

COPY INTO raw.orders
FROM @my_stage/orders/
FILE_FORMAT = (TYPE = CSV SKIP_HEADER = 1);

A serverless service that loads new files automatically when they arrive, usually triggered by cloud storage events.

Load it into a VARIANT column and use path notation and FLATTEN for arrays.

SELECT v:customer.name::string, f.value:sku::string
FROM raw_json, LATERAL FLATTEN(input => v:items) f;

Querying or restoring data as it was in the past, up to the retention period. UNDROP brings back dropped objects.

Time Travel is for you to use. Fail-safe is a further 7 day period only Snowflake support can recover from.

Creating a copy of a table, schema or database instantly without copying data. Only changes take new storage.

A stream tracks changes (CDC) on a table. A task runs SQL on a schedule. Together they build incremental pipelines.

Tables defined by a query that Snowflake keeps refreshed to a target lag, a simpler alternative to streams and tasks for many pipelines.

Transient tables have no Fail-safe and shorter Time Travel, so they cost less. Good for staging data you can rebuild.

A filter on window function results, so you do not need a subquery.

SELECT * FROM orders
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) = 1;

Result cache (24 hours, identical query, unchanged data, no compute), local disk cache on the warehouse, and metadata cache.

Columns Snowflake uses to organise micro-partitions on very large tables, so filters prune better. Automatic clustering has a cost.

Auto-suspend around 60 seconds, right-size warehouses, resource monitors, tune expensive queries and use transient tables for staging.

Role-based: privileges are granted to roles, roles to users, roles can inherit other roles.

A rule that shows or hides column values depending on the user's role, for example showing only the last 4 digits of a phone number.

An API to write transformations in Python, Java or Scala that run inside Snowflake, without moving data out.