Snowflake, BigQuery, Databricks, Redshift: design and cost
Practise these as flashcards. One or two lines each; the ones you miss come back first.
Start freeA 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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.