PrepPilotPractise free

Cloud Warehouses & Lakehouses interview questions

Snowflake, BigQuery, Databricks, Redshift: design and cost

17 questions · Snowflake · updated November 2026

Practise these as flashcards. One or two lines each; the ones you miss come back first.

Start free

Core questions

What is the main difference between a data warehouse and a data lake?

A data warehouse stores structured data optimized for analytics, using predefined schemas. A data lake stores raw, unstructured or semi-structured data, allowing flexible ingestion and schema-on-read.

How does Snowflake separate storage and compute?

Snowflake stores data in cloud storage independently from compute. Compute is handled by virtual warehouses, which can be scaled, paused, or resumed without affecting stored data.

What is a BigQuery slot?

A BigQuery slot is a unit of computational capacity used to execute SQL queries. You can use on-demand pricing or reserve slots for predictable workloads.

How do you partition a table in BigQuery?

You partition a table in BigQuery by specifying a column, usually a date or integer, when creating the table. This improves query performance by scanning only relevant partitions.

Intermediate questions

Explain micro-partitions in Snowflake and their benefits.

Micro-partitions are small, immutable chunks of data automatically created by Snowflake. They enable efficient storage, fast query performance, and automatic pruning based on metadata, reducing scanned data.

How does Delta Lake provide ACID guarantees on a data lake?

Delta Lake uses transaction logs and versioning to ensure atomicity, consistency, isolation, and durability. This allows reliable updates, deletes, and merges on data stored in cloud object storage.

Describe how clustering works in BigQuery and Snowflake.

Clustering organizes data within tables based on specified columns. In BigQuery, clustering improves filtering and sorting performance. In Snowflake, clustering helps optimize query performance by reducing scan size for frequently filtered columns.

What is time travel in Delta Lake, and why is it useful?

Time travel lets you query historical versions of a Delta table, enabling auditing, rollback, and debugging. It’s useful for tracking changes and recovering from accidental data modifications.

How can you control costs in Snowflake?

You can control costs by resizing or pausing virtual warehouses, optimizing queries to scan less data, using clustering, and monitoring usage with Snowflake's billing tools.

How does BigQuery pricing work?

BigQuery offers on-demand pricing, charging per byte processed in queries, and flat-rate pricing, where you reserve slots for a fixed monthly fee. Partitioning and clustering help reduce bytes processed and costs.

What is Z-order in Delta Lake, and when should you use it?

Z-order is a multi-column data layout technique that organizes files to optimize filtering on multiple columns. You should use it when queries frequently filter on several columns, improving scan efficiency.

How do you optimize queries in BigQuery to reduce costs?

I select only necessary columns, filter early, partition and cluster tables, and use query plan explanations to identify expensive operations. Scheduling queries during off-peak hours can also help with cost control.

How does Snowflake's clustering differ from BigQuery's clustering?

See the answer in the app, free

Senior questions

How would you design a lakehouse architecture for a large organization?

See the answer in the app, free

Describe a scenario where improper partitioning led to high costs and how you resolved it.

See the answer in the app, free

How do you manage secure data sharing across teams in Snowflake?

See the answer in the app, free

What are the trade-offs between using a warehouse, lake, and lakehouse for analytics workloads?

See the answer in the app, free

More Data Engineering topics