Cloud Data Warehouse Architecture Questions

Understand modern cloud data platforms: Snowflake, BigQuery, Redshift, Azure Synapse. Know their architecture, scalability models, performance characteristics, and cost optimization strategies. Discuss separation of compute and storage, time travel, and zero-copy cloning.

EasyTechnical

32 practiced

What is data lineage and why is it important for BI teams using cloud data warehouses? Describe how metadata, automated tests, and documentation help maintain trustworthy dashboards.

EasyTechnical

25 practiced

Explain the separation of compute and storage in modern cloud data warehouses (for example Snowflake, BigQuery, Redshift RA3). Describe the core benefits this separation brings to BI teams (cost flexibility, concurrency, scaling) and one scenario where tightly-coupled compute+storage would be preferable.

MediumTechnical

22 practiced

You need to implement an incremental load into a Snowflake fact table from a staging table. Given the following schema:

staging.events(event_id STRING, user_id STRING, event_ts TIMESTAMP, payload VARIANT)
warehouse.fact_events(event_id STRING, user_id STRING, event_ts TIMESTAMP, payload VARIANT)

Write a MERGE statement (Snowflake SQL) to upsert new and updated rows based on event_id.

MediumTechnical

22 practiced

A BI report shows inconsistent metrics between two dashboards that use the same underlying tables. Describe a step-by-step debugging process to identify the root cause, covering SQL logic, joins, filters, time zones, SCD handling, and caching.

HardTechnical

26 practiced

Write an optimized SQL (target platform-agnostic) to deduplicate streaming events where events may arrive out-of-order. Schema:

events(id STRING, user_id STRING, event_ts TIMESTAMP, ingestion_ts TIMESTAMP, payload JSON)

Keep the event with the latest event_ts; if event_ts equal, keep latest ingestion_ts. Consider window functions and partitioning strategy for performance on large tables.

Unlock Full Question Bank

Get access to hundreds of Cloud Data Warehouse Architecture interview questions and detailed answers.

Join thousands of developers preparing for their dream job.