Snowflake, BigQuery & Databricks interview questions for data engineers
Warehouse and lakehouse internals: storage, compute, pruning, cost, streaming ingestion, and governance. 108 questions with full answers, written for freshers. All topics
Snowflake (19)
- Snowflake architecture
- Micro-partitions and pruning
- Clustering keys and automatic clustering
- Scaling up vs scaling out a virtual warehouse
- Snowflake caching layers
- Time Travel and Fail-safe
- Zero-copy cloning
- Stages, COPY INTO and Snowpipe
- Streams and tasks
- Dynamic tables
- VARIANT and semi-structured data in Snowflake
- Snowflake RBAC and system roles
- Masking and row access policies
- Secure data sharing
- Snowflake query spilling to remote storage
- Snowflake bill doubled
- Transient and temporary tables
- Snowpark
- Snowflake and Iceberg tables
BigQuery (17)
- BigQuery architecture: Dremel, Colossus, Jupiter
- What is a slot?
- On-demand vs capacity pricing
- Partitioning in BigQuery
- Clustering in BigQuery
- Reduce BigQuery query cost
- BigQuery streaming ingestion options
- Batch loading vs streaming cost in BigQuery
- BigQuery DML and MERGE costs
- BigQuery time travel and snapshots
- BigQuery materialized views and BI Engine
- BigQuery security: IAM, authorized views, policy tags
- External tables, BigLake and Omni
- Query execution details in BigQuery
- Denormalize or join in BigQuery?
- BigQuery quotas and limits that bite
- BigQuery and dbt or Dataform
Databricks (16)
- Databricks architecture: control plane and compute plane
- All-purpose vs job clusters vs serverless
- DBUs and Databricks cost
- Unity Catalog
- Managed vs external tables in Unity Catalog
- Auto Loader
- Lakeflow Declarative Pipelines (DLT)
- Expectations in declarative pipelines
- OPTIMIZE, Z-ORDER and VACUUM
- Predictive optimization
- Photon
- Delta MERGE for upserts and SCD2
- Delta schema enforcement and evolution
- Databricks Workflows (Lakeflow Jobs)
- CI/CD with Databricks Asset Bundles
- Many small files in a Delta table
Choosing and Comparing (4)
Amazon Redshift (23)
- Amazon Redshift architecture and compute nodes
- Redshift distribution styles: KEY, ALL, EVEN, AUTO
- Sort keys in Redshift: compound versus interleaved
- Loading data into Redshift with COPY from S3
- Redshift Spectrum external tables
- Data skew in Redshift slices
- Redshift Workload Management (WLM) and concurrency scaling
- Redshift RA3 nodes and managed storage
- Primary key and unique constraint enforcement in Redshift
- Redshift VACUUM and ANALYZE operations
- Column compression encodings in Redshift
- Redshift Materialized Views and incremental refresh
- Redshift Serverless versus provisioned clusters
- Unload data from Redshift to S3
- Colocated joins in Redshift
- Redshift Data Sharing across AWS accounts
- Explaining query execution plans in Redshift (EXPLAIN)
- Redshift Federated Queries to PostgreSQL, MySQL, and Aurora
- Designing multi-tenant schemas and row-level security in Redshift
- Short query acceleration and machine-learning WLM tuning
- Cross-region disaster recovery and snapshot replication for Redshift
- Resolving disk spill and commit queue contention in Redshift
- Zero-ETL integration: Aurora to Amazon Redshift
Azure Synapse & Fabric (23)
- Azure Synapse Dedicated SQL Pool 60-distribution architecture
- Table distribution strategies in Synapse: Hash, Round Robin, Replicated
- Clustered Columnstore Indexes in Synapse
- Loading data into Synapse with COPY INTO
- Synapse Serverless SQL versus Synapse Dedicated SQL Pool
- Distribution skew and Data Movement Service in Synapse
- Synapse Workload Management and Workload Groups
- Clustered Columnstore maintenance and health in Synapse
- Primary key and unique constraints in Synapse Dedicated Pool
- Partitioning tables in Synapse Dedicated SQL Pool
- Synapse Result Set Caching
- Temporary tables versus permanent staging tables in Synapse ETL
- Microsoft Fabric Warehouse versus Synapse Dedicated SQL Pool
- Statistics maintenance in Synapse Dedicated Pool
- Transaction log and rollback limits in Synapse
- Reading Synapse query execution plans via dynamic management views
- Surface lake data via Synapse Serverless views into Power BI
- Azure Synapse Link for Cosmos DB and SQL Server
- Minimizing Data Movement Service shuffle in multi-table joins
- Dynamic data masking and granular column security in Azure Synapse
- Zero-downtime blue-green schema deployment in Synapse Dedicated Pool
- Recovering from severe tempdb exhaustion in Synapse Dedicated Pool
- Cross-region disaster recovery and geo-backup in Azure Synapse
Google BigQuery (6)
- BigQuery physical storage and the Capacitor columnar format
- BigQuery time travel and snapshot queries
- BigQuery dataset location and data residency
- BigQuery slot reservations, editions, and capacity management
- BigQuery search indexes and accelerating log analytics
- Continuous CDC and Apache Iceberg ingestion with BigLake metastore