Snowflake Interview Questions

"Snowflake has three layers that scale on their own. At the bottom is storage: every table is kept in the cloud provider's object storage, broken into compressed columnar files called micro-partitions, and I never manage those files directly. In the middle is compute, which is virtual warehouses. A warehouse is just a cluster of machines that runs my queries, and I can have several, for loading, for BI, for data science, all reading the same tables without fighting each other. On top is the cloud services layer. It handles authentication and access control, keeps the metadata about every micro-partition, parses and optimises queries, manages transactions, and holds the result cache. The practical point is that storage and compute are billed and sized separately, so I can add compute for a heavy job without copying any data."

"My last big project was moving our reporting warehouse from an on-premise database to Snowflake, a few hundred tables and the nightly jobs around them. We ran both systems side by side for a month and compared row counts and key totals for every table each night before switching any report. Two things surprised us. First, the old system enforced primary keys and Snowflake doesn't, so a job that relied on duplicate inserts failing started quietly doubling rows; we added merges and uniqueness tests. Second, people brought their old habits, like building indexes and leaving one big warehouse running all day, so the first month's bill was higher than planned. We split warehouses per workload and set auto-suspend and resource monitors. Next time I'd set up the cost controls and data tests on day one, not after the first surprise."

"A virtual warehouse is a named cluster of compute that runs my queries and loads. It doesn't store any data; it reads from the shared storage layer and caches some of it locally while it's running. Sizes go from X-Small upwards, and each step roughly doubles the compute and the credits it burns per hour. So a size up helps a single large query, like a big join or aggregation, because more machines work on it at once. If a query takes eight minutes on Small and four on Medium, the cost is about the same and I get the answer faster. If it barely speeds up, the bigger size is wasted. So I start small, run the real workload, look at the query profile for spilling, and move up only when the numbers justify it."

"I scale up when individual queries are heavy: a transformation that spills to disk or a join over billions of rows gets faster on a bigger warehouse. I scale out when the queries are fine on their own but there are too many at once, like a BI tool at nine in the morning when a hundred dashboards refresh and queries start queuing. A multi-cluster warehouse keeps the same size but adds clusters when queries queue and removes them when the load drops. I set a minimum and maximum number of clusters, and a scaling policy: Standard starts extra clusters quickly to avoid queuing, Economy waits until there's enough work to keep a new cluster busy, which saves credits but lets some queries wait. Multi-cluster needs Enterprise edition or higher, so I'd check that first."

"Warehouses burn credits for as long as they're running, not per query. Billing is per second, but every time a warehouse starts there's a one-minute minimum. Auto-suspend shuts a warehouse down after it's been idle for the time I set, and auto-resume starts it again the moment a query arrives, so users don't notice. For an ad hoc or ELT warehouse I keep auto-suspend short, a minute or a few minutes, because paying for an idle cluster is the most common waste I see. The trade-off is the local cache. A running warehouse keeps recently read data on its local disks, and suspending throws that away, so a BI warehouse that's hit constantly might get a slightly longer idle time to keep dashboards warm. Setting it to a few seconds is also a mistake, since every restart costs a minimum minute."

"First I'd confirm it with evidence. I'd look at query history for the load window: how long the load queries sat queued, and which other queries were running at the same time. If it's the analysts' queries, the fix in Snowflake is simple: separate warehouses. Loads get their own warehouse, sized for the transformation and suspended when done, so their timing no longer depends on who else is busy. Analysts get their own, maybe multi-cluster if many people query at once, with a statement timeout so a runaway query stops itself, and a resource monitor so the budget is visible. Both warehouses read the same data, so nobody loses access to anything. Then I'd talk to the analysts, not just change their setup, and show them the new warehouse and how to check their own query costs. I'd keep watching load finish times for a couple of weeks."

"When data is written, Snowflake splits it into micro-partitions, which are immutable, compressed files holding roughly 50 to 500 megabytes of uncompressed data each, stored column by column. I don't define them; they follow the order the data arrived in. For every micro-partition the cloud services layer keeps metadata, like the minimum and maximum value of each column. So if I filter on order_date for one week, the optimiser compares that range with each partition's min and max and skips every partition that can't contain matching rows. That's pruning, and it's why Snowflake doesn't need traditional indexes. Because the files are columnar, it also reads only the columns I select. In the query profile I compare partitions scanned with partitions total: if a filtered query scans nearly everything, the data isn't laid out well for that filter."

"Most tables don't need a clustering key, because data loaded over time is naturally clustered on its load date. I'd add one when a table is very large, queries keep filtering on a column that doesn't follow the load order, and the profile shows them scanning most partitions. For the key I pick the columns used most in filters, low cardinality first, and I avoid something like a raw timestamp or a unique ID; if I need time, I cluster on a date expression instead. Once the key is set, automatic clustering reorganises micro-partitions in the background, and that uses serverless credits, more so on tables with lots of updates. To judge it I check average clustering depth with the built-in clustering information function, and more importantly I compare partitions scanned on the real queries before and after. If the reclustering bill is bigger than the savings, I drop the key."

"Permanent tables are the default. They get Time Travel, which can be longer on higher editions, and after that seven days of Fail-safe, so they have the most protection and the most storage overhead. Transient tables stay until someone drops them, like permanent ones, but they have at most one day of Time Travel and no Fail-safe. I use them for staging and intermediate tables that I can rebuild from source, because on tables that are truncated and reloaded every day, Fail-safe storage adds up for no benefit. Temporary tables only live for the session that created them, can't be seen by other sessions, and disappear when the session ends, so they're handy for a scratch step inside one script. I wouldn't put anything that can't be rebuilt in a transient or temporary table."

"Time Travel lets me query or restore data as it was at an earlier moment, as long as that moment is inside the table's data retention period. The default is one day, and on Enterprise edition permanent tables can keep up to 90 days. To see the table as it was yesterday, I add an AT clause with a timestamp, or an offset in seconds back from now. If I know the exact statement that damaged the data, BEFORE with that query's ID gives me the state just before it ran, which is the most precise. To restore, I usually clone the table at that point, check it, and swap it in. It also works on dropped objects: UNDROP TABLE brings back a table dropped within the retention period. Longer retention means more storage, because changed partitions are kept."

"Time Travel is mine to use: I can query old versions, clone from them or undrop things myself during the retention period. When that period ends, data from permanent tables moves into Fail-safe for seven more days. But I can't query Fail-safe or restore from it myself. Only Snowflake support can recover data from it, it's best effort, and it's meant for disasters like a system failure, not for undoing my team's mistakes. So I don't count it in a recovery plan. For real protection I'd set Time Travel retention to match how fast we'd notice a problem on critical tables, and keep a separate copy or replication for anything we need beyond that. It also has a cost side: Fail-safe storage is billed, so for staging tables that are reloaded daily I use transient tables, which skip Fail-safe entirely."

"When I clone a table, schema or database, Snowflake doesn't copy any data. It creates new metadata that points at the same micro-partitions as the source, so even a huge database clones in seconds and costs nothing extra at first. Because micro-partitions are immutable, when either the clone or the original changes, the changed data goes into new micro-partitions that belong only to that side, and those are what I start paying for. I use it a lot for dev and test: every developer can get a full clone of production to test a migration against real volumes. Before a risky backfill I clone the target table so I can swap it back if it goes wrong. And combined with Time Travel I can clone a table as it was an hour ago, which is my usual way to recover from a bad update."

"First I'd stop things getting worse: pause any tasks or scheduled jobs that read from or write to that table, so nothing downstream rebuilds on an empty table. Then I'd find the DELETE in query history and note its query ID. An hour is well inside Time Travel retention, so I'd create a clone of the table using BEFORE with that statement ID, which gives me the exact state just before the delete. I'd check the clone's row count and a few totals against what we expect, and then swap it with the damaged table, which is quick and keeps the name. If rows were legitimately added after the delete, I'd merge those in from the damaged table. Then I'd resume the jobs, rerun anything that ran on the bad data, and let users know. Afterwards I'd look at why that role could delete in production at all."

"A stage is a location for data files, the place COPY INTO reads from when loading and writes to when unloading. Internal stages use storage that Snowflake manages. Every user has a user stage, every table has its own table stage, and I can create named internal stages that several people and tables share. Files go into an internal stage with the PUT command from a client like SnowSQL. External stages point at storage I already own, like an S3 bucket, a Google Cloud Storage bucket or an Azure container. For those I set up a storage integration, which lets Snowflake reach the bucket through the cloud provider's own role-based access, so no access keys are written into the stage definition. In most real pipelines files already land in cloud storage from other systems, so I mostly use named external stages, and LIST to check what's there."

"COPY INTO reads files from a stage, parses them with a file format, and inserts the rows into the table using the warehouse I'm running on. I usually point it at a folder in an external stage and add a PATTERN so it only picks the files I want. If I run the same COPY again, Snowflake checks its load metadata, which remembers which files were loaded into that table for 64 days, and skips them, so reruns after a failure are safe. FORCE overrides that, which I'd only use on purpose. For bad data, ON_ERROR controls what happens: the default for bulk loads aborts the whole statement, or I can skip a bad file or continue past bad rows. Before a first big load I run it with VALIDATION_MODE to see the errors without loading anything. For speed, files of roughly 100 to 250 megabytes compressed load well in parallel, rather than one giant file."

"Snowpipe is continuous loading. I create a pipe that contains a COPY INTO statement, and with auto-ingest turned on, the cloud storage sends an event notification whenever a new file lands, for example through an S3 event and a queue. Snowpipe then loads that file within a short time using serverless compute that Snowflake manages, so I don't need a warehouse running. I'd use it when files arrive all day in small pieces, like event logs every few minutes, and people want fresh data without me keeping a warehouse awake just to poll. For one large nightly drop, a scheduled COPY on a warehouse is simpler and easier to control. Two things I watch: Snowpipe doesn't guarantee files load in arrival order, and lots of tiny files add overhead, so I ask producers to batch files to a sensible size."

"A stream is change tracking on a table. It stores an offset, a point in the table's version history, and when I query it I get the rows that changed since then, with metadata columns saying whether each row was an insert or a delete and whether it was part of an update. An update shows up as a delete and an insert pair. The stream doesn't copy the data; it uses the table's versioning. The key rule is that just selecting from a stream doesn't advance it. The offset only moves when I use the stream in a DML statement, like an INSERT or MERGE, and that transaction commits. So if the merge fails and rolls back, the changes are still there next time. For a landing table that only ever gets inserts, I use an append-only stream, which is simpler and cheaper to read."

"A task runs SQL on a schedule, usually one statement or a call to a stored procedure, and the schedule can be every few minutes or a CRON expression with a time zone. It runs either on a warehouse I name or on serverless compute that Snowflake sizes. To build a pipeline, the root task holds the schedule and each child task says AFTER its parent, so I get a graph: load, then transform, then refresh aggregates. The first thing that catches people is that a new task is created suspended, so nothing happens until I resume it, and in a graph the children need to be resumed too. I also add a WHEN condition that checks whether the source stream has data, so the run is skipped cheaply when nothing changed. For monitoring I query task history and alert on failed runs rather than trusting that it's running."

"I'd land files in cloud storage and let Snowpipe with auto-ingest load them into a raw table, adding the file name and load time so I can trace any row back to its file. That raw table is insert-only, so I put an append-only stream on it. A task runs every few minutes with a WHEN condition that skips the run if the stream is empty. The task runs a MERGE from the stream into the clean orders table on the order ID. Because one batch can contain the same order more than once, I first keep only the latest row per order with QUALIFY and ROW_NUMBER, ordered by the source's update time. The merge is atomic, so if it fails, the stream doesn't advance and the next run picks the changes up again. I'd alert on task failures, check row counts daily, and because raw keeps everything, I can rebuild orders if the logic changes."

"A stream reads changes using the source table's version history, so it only works while its offset is inside the table's data retention. To help, Snowflake extends retention on a table with an unconsumed stream, up to a limit set by a parameter, 14 days by default. Weeks of no consumption went past that, so the stream is stale: the changes between its offset and now can't be read any more, and it can't just be resumed. To recover, I'd recreate the stream so it starts tracking from now, then fix the gap separately. Because I can't trust which changes were missed, I'd reconcile the target against the source: for a modest table, a full MERGE from the source; for a big one, a reload of the affected date range, then check counts and totals. Then I'd add alerts on task state and on stream staleness, so this is caught in hours, not weeks."

"I load JSON into a VARIANT column, which can hold a whole nested document per row. If the JSON arrives as plain text in a string column, PARSE_JSON turns it into a VARIANT. To query it I use a colon for the first level, like raw colon customer, then dots for deeper keys, and square brackets with an index for array items. The thing that trips people up is that the result is still a VARIANT, so a string shows up with double quotes around it and comparisons can behave oddly. I always cast with double colon, like colon status cast to STRING, or to NUMBER or TIMESTAMP. Also, the key names inside the path are case-sensitive even though Snowflake column names aren't, so a mismatch in case just returns NULL instead of an error."

"I'd use LATERAL FLATTEN on the items array. FLATTEN is a table function: for each order row it produces one output row per element of the array, and I join it laterally so each item row can still see its parent order. In the output, the value column holds the item itself, so I read its fields with a colon and cast them, and index gives the item's position in the array, which is handy as a line number. The detail interviewers look for is empty arrays. By default an order with no items, or with the key missing, just disappears from the result, because there's nothing to flatten. If the report needs every order, I set OUTER to TRUE, and those orders come back with NULLs in the item columns. For arrays inside arrays I'd chain a second FLATTEN on the inner array."

"There are three layers. The result cache lives in the cloud services layer: if I run the same query text again, the underlying data hasn't changed, and the query has nothing like CURRENT_TIMESTAMP that changes each run, Snowflake returns the stored result without using a warehouse at all. Results are kept for 24 hours, and that clock resets each time the result is reused, up to a limit. That's usually why a second run is instant. Then there's the warehouse's local cache: a running warehouse keeps micro-partitions it has read on local disk, so similar queries on the same data read less from remote storage, but that's lost when it suspends. And some queries, like a plain COUNT of a table, are answered from metadata. When I benchmark a change, I turn off USE_CACHED_RESULT for the session so I'm measuring real work."

"First I'd compare a fast run and a slow run in query history, because the difference often tells me a lot. If most of the extra time is queued, the query isn't slower, the warehouse is overloaded, and that's a scaling or scheduling fix. If it's execution time, I open the query profile and go to the most expensive operators. I look at partitions scanned against partitions total: if it now scans almost everything, pruning has broken, maybe because the filter changed or the data's load order did. I check for bytes spilled to local or, worse, remote storage, which means the warehouse ran out of memory, and that's when a size up really helps. And I look at row counts through each join; if a join outputs far more rows than it takes in, a duplicate key upstream has probably caused an exploding join. Each of those has a different fix."

"First I make spend visible. I give each workload its own warehouse, loading, transformation, BI, data science, so metering history shows who uses what, and I use the account usage views for warehouse metering and query history to find the most expensive queries. For compute, I right-size each warehouse by testing, keep auto-suspend short, and set a statement timeout so a runaway query can't run all night. Resource monitors put a credit quota on warehouses and can notify at one level and suspend at another. But they only cover warehouses, so I also watch serverless features, like automatic clustering, Snowpipe and search optimization, which bill separately. Storage is usually smaller, but tables rewritten every day with long Time Travel and Fail-safe can quietly pile it up, so staging tables become transient. Then I review the top spenders monthly with the teams that own them."

"At my last company our nightly sales model took about an hour on a Large warehouse and kept creeping up. Someone had already sized it up twice. I opened the query profile and saw two things. The biggest table scan read almost every micro-partition even though we only needed the last three days, because the incremental filter wrapped the date column in a conversion that the optimiser couldn't use for pruning well. And one join produced many times more rows than it took in, because a lookup table had picked up duplicate keys. I rewrote the filter to compare the raw date column directly, added a dedupe step and a uniqueness test on the lookup table. The job dropped to a few minutes and we moved it back to a Medium warehouse. I compared credits in metering history for two weeks to be sure the savings held."

"In Snowflake, privileges never go straight to users. I grant privileges on objects to roles, and then grant roles to users, and roles can be granted to other roles, so they inherit. ACCOUNTADMIN sits at the top and should be held by very few people. SECURITYADMIN and USERADMIN manage grants and users, and SYSADMIN is meant to own databases and warehouses. For a new analytics team I'd make access roles, like a read-only role on the marts schema, and a functional role called analyst that's granted those access roles plus usage on their warehouse. People get the analyst role, not the underlying grants. I'd grant select on the tables already there and add future grants so new tables are readable automatically, and I'd grant my custom roles up to SYSADMIN, so admins can still manage what those roles create and nothing ends up orphaned."

"I'd use a masking policy. It's a small rule I create once and attach to the email and phone columns. Every time a query reads those columns, Snowflake runs the policy: if the user's role is the support role, they see the real value; everyone else gets a masked value, like stars or just the email domain. The nice part is that there's one table, analysts don't need a separate view, and the rule follows the column wherever it's queried. I'd write the policy to check role membership in the session rather than one exact role name, so role inheritance works. If the requirement were rows rather than columns, like regional managers seeing only their region, I'd use a row access policy instead. With lots of sensitive columns, I'd tag them and attach the policy to the tag. These features need Enterprise edition, which I'd confirm first."

"With Secure Data Sharing, I as the provider create a share, grant it usage on a database and schema and select on specific tables or secure views, and add the consumer's account. No data is copied or moved. The consumer creates a database from that share and sees the live data, read-only, as soon as I update it. Storage stays with me, so I pay for that, and the consumer queries with their own warehouse, so they pay the compute. If the consumer doesn't have a Snowflake account, I can create a reader account for them, but then the compute is on my bill. For security I usually share secure views rather than raw tables, because they hide the view definition and let me filter rows per consumer. If the consumer is in another region or cloud, the data has to be replicated there first, which a listing can handle for me."

"When I joined my last team, about a dozen people used ACCOUNTADMIN day to day, and service accounts for our BI tool and ELT jobs shared one big role. I first pulled the grants and login history from the account usage views to see who actually used what. Then I proposed a simple model: access roles per schema for read and write, functional roles for analysts, engineers and each service, all rolled up to SYSADMIN. I built the new roles next to the old ones, moved one team at a time, and watched query failures for a few days before removing the old grants. Admin access went down to two named people with MFA. The part I'm proudest of is that we moved all grants into version-controlled scripts, so any change went through review and the account stopped drifting."