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
25 practiced
Describe zero-copy cloning (Snowflake) or equivalent snapshot techniques. How can BI teams use cloning for development, testing, or versioning dashboards and data models without incurring full storage duplication?
Sample Answer
Zero-copy cloning (Snowflake) is a snapshot mechanism that creates a point-in-time copy of databases, schemas, or tables without duplicating underlying storage. Clones reference the same micro-partitions as the source; only changed data (writes after the clone) consumes additional storage (copy-on-write).Why it matters for BI:- Fast: create full dev/test datasets in seconds.- Cost-efficient: no full storage duplication; you pay only for changes and compute.- Safe isolation: developers can experiment without affecting production.Practical BI uses:- Development: spin up a cloned schema tied to a branch of semantic models (LookML/Power BI dataset), iterate ETL and dashboard changes, then promote.- Testing: run regression tests and dashboard refreshes against a clone to validate transformations or metric logic.- Versioning: keep clones as immutable snapshots before major releases to compare results or roll back.Best practices:- Use small, focused clones (schemas/tables) rather than entire DBs when possible.- Tag clones with metadata (owner, purpose, timestamp).- Clean up stale clones to avoid unexpected storage growth from long-lived writes.- Combine with role-based access and separate compute warehouses for isolation.Limitations:- Clones share micro-partitions; heavy write activity on many clones increases storage.- Not a substitute for long-term archival — export snapshots for permanent backups.For BI teams, zero-copy clones enable rapid, low-cost experimentation and reliable testing workflows while preserving production data integrity.
HardSystem Design
32 practiced
Architecture question: Design a solution that allows BI analysts to run ad-hoc queries against raw event data in the lake while preventing expensive queries from impacting production dashboards. Include access controls, query caps, materialized caches, and query sandboxing.
Sample Answer
Requirements & constraints:- Allow BI analysts to run ad-hoc queries over raw event lake data- Protect production dashboards and reporting clusters from long/expensive queries- Enforce access control, per-user/project caps, and auditing- Provide fast repeated-query performance via materialized cachesHigh-level architecture:- Raw events -> Data Lake (Parquet/Delta on S3) -> ETL (Spark/Flink) -> Serving layer- Query Gateway (proxy) sits between BI tools and compute engines- Two isolated compute environments: Production SQL Warehouse (low-latency, dashboarding) and Sandbox Fleet (ad-hoc, isolated)- Materialized Cache layer (incremental materialized views + summarized tables) backed by a columnar store (BigQuery/Redshift/Databricks Delta + cache in Presto/Trino)Core components & responsibilities:1. Query Gateway- AuthN/AuthZ via SSO (OIDC/LDAP) and role-based policies- Route queries to Production or Sandbox based on user role, dataset tag, and cost estimate- Enforce caps, timeouts, concurrency limits; return friendly error or queued execution2. Cost Estimator & Policy Engine- Static planner + small dry-run on sampled data to estimate runtime/memory/scan bytes- Policies: per-user/day scan quota, per-query row/byte cap, max runtime- Automatic escalation workflow for temporary quota increases with audits3. Sandbox Fleet (ephemeral)- Containerized query workers using Presto/Trino on Kubernetes with resource limits (CPU/mem)- Network and compute isolation; aggressive query preemption and cancellation4. Materialized Cache & View Manager- Pre-built aggregated tables and incremental materialized views for common BI patterns (daily/hourly user funnels, session aggregates)- Auto-refresh schedules and on-demand refresh via managed jobs; warm caches pushed to a hot store (columnar DW or Redis for small dims)- Query rewrite layer: gateway rewrites heavy ad-hoc queries to use materialized views when semantically equivalent (using lineage/catalog)5. Auditing & Monitoring- Log all queries, plans, cost estimates, and responses to an audit store- Alerting on quota breach attempts, high-prevalence slow queries, or production resource pressureData flow:- BI tool -> Query Gateway -> Cost Estimator -> route to Production (read-only, cached) or Sandbox (ephemeral)- If a query matches materialized view, redirect to cacheScalability & reliability:- Scale Sandbox horizontally; use autoscaling with warm pool for fast startup- Cache tier scales separately for read-heavy dashboards- Use query throttling and priority queuing to protect productionTrade-offs:- Conservative cost estimation may block some valid queries — provide fast escalation paths- Materialized views add storage & freshness trade-offs; favor incremental updates for large tables- Sandbox isolation adds infra cost but prevents production impactOperational practices:- Catalog with data ownership, dataset tags, and recommended views- Quarterly access reviews; mandatory training for analysts on cost-efficient querying- Templates and example queries that use caches and partition pruningThis design gives BI analysts freedom for exploration while enforcing predictable production SLAs through routing, sandboxing, cost controls, and reusable materialized caches.
MediumSystem Design
27 practiced
Design an hourly ELT pipeline that ingests CSV files from S3 into BigQuery for daily sales analytics. List the components (ingestion, staging, transformations, orchestration, monitoring) and include brief recommendations for file partitioning and failure handling.
Sample Answer
Requirements (clarify): hourly ingestion of CSVs from S3 into BigQuery for daily sales analytics; low-latency availability for dashboards (~within 15–30 min of hour); support late-arriving files, schema evolution, data quality checks, and retryable failures.High-level architecture:S3 (raw CSV) → Ingest/loader → Staging (GCS or BigQuery temp) → Transform (SQL/DBT) → Curated tables/partitions in BigQuery → BI tools (Looker/Tableau)Components:- Ingestion: AWS Lambda or Fargate job triggered by S3 PUT or hourly scheduler (CloudWatch/EventBridge). Use transfer service or a lightweight ETL runner to copy files to GCS or load directly to BigQuery via storage API.- Staging: load raw CSVs into BigQuery raw dataset (raw.sales_{date_hour}) or land in GCS with folder structure /raw/sales/YYYY/MM/DD/HH/.- Transformations: use dbt or BigQuery scheduled queries to validate, normalize, join reference data, and produce nightly/hourly aggregated views (hourly_sales, daily_rollups).- Orchestration: Airflow (Cloud Composer) or managed orchestrator (GCP Cloud Scheduler + Cloud Functions) to sequence ingest → validate → transform → publish.- Monitoring & alerting: DataDog/Stackdriver + BigQuery audit logs; implement SLA checks, row-count diffs, schema drift alerts, and Slack/email notifications.- Data quality: Great Expectations or dbt tests for nulls, ranges, referential integrity.Partitioning & clustering:- Use ingestion-time partitioning or partition by DATE(sale_date) and cluster by store_id, product_id for faster filters. For hourly workloads, include an hour column and consider partitioning by DATE and clustering by hour if querying by hour frequently. Keep partition size 100MB–10GB.Failure handling & retries:- Idempotent loads: track file manifests and load status in metadata table to avoid duplicates.- Retry with exponential backoff for transient errors; dead-letter queue (Pub/Sub or SQS) for persistent failures with automatic alerts and manual reprocessing UI.- Validate CSV schema on ingest; if schema mismatch, route to quarantine folder and notify data owner.- Backfill strategy: support manual or automated reprocessing for late/missing files based on manifest.Operational notes for BI:- Expose stable semantic layer (views or derived tables) for dashboards.- Maintain lineage (dbt docs) and maintain SLAs for freshness.
HardSystem Design
26 practiced
Architecture question: Design a cloud data warehouse solution to support near-real-time (1 minute latency) BI dashboards for 5 million daily events. Include ingestion, storage layout, transformation strategy, and how you would ensure query performance for dashboard users.
Sample Answer
Requirements & constraints:- Near-real-time dashboards with ≤1 minute event-to-dashboard latency- ~5M events/day (~58 events/sec average; peak bursts higher)- BI user-friendly (Power BI/Tableau/Looker), support interactive filters and time-series queries- Low cost, scalable, reliableHigh-level architecture:1. Ingestion (streaming)- Use a managed streaming service (Cloud Pub/Sub / AWS Kinesis / Confluent Kafka Cloud) for event ingestion with partitioning by key (e.g., user_id, product_id) to scale writers.- Producers push events in JSON/Avro. Use schema registry and lightweight validation at producer side.- Configure retention to support replays (hours-days).2. Real-time processing & transformation- Use a streaming processing layer (Dataflow/Beam / Kinesis Data Analytics / Kafka Streams / Flink) to: - Validate and enrich events (lookup small dimension caches). - Compute incremental aggregates (per-minute, per-hour) and produce two outputs: a) Raw events sink to cloud storage for replay and batch processing (partitioned by date/hour). b) Near-real-time OLAP sink: write to cloud data warehouse (or streaming OLAP) as micro-batches (e.g., every 10–30s) or to a streaming ingestion API.- Use change-data-capture (CDC) for upstream DB changes if needed (Debezium -> stream).3. Storage layout in the cloud data warehouse- Choose columnar, serverless/elastic DW (BigQuery or Snowflake). Rationale: built for analytical workloads, separation of storage & compute, auto-scaling.- Zones: - Raw_events table: partitioned by event_date (DAY) and clustered by event_type, inserted from storage or streaming. - Staging/minute_aggregates table: partitioned by minute_timestamp, clustering on dimension keys. Micro-batched inserts from stream processors. - Business_marts: denormalized tables tailored to dashboard needs (e.g., daily_active_users, revenue_by_region_minute) refreshed incrementally.- Use partitioning (date/minute) and clustering (high-cardinality filters used in dashboards) to prune IO.4. Transformation strategy- Two-tier transforms: - Streaming transforms (near-real-time): light enrichment and incremental aggregates computed in stream jobs and upserted to minute_aggregate tables. - Batch transforms (reconciliation): nightly dbt jobs to build canonical marts, backfill corrections, and validate aggregates.- Implement idempotent writes and use watermarking to handle late events (e.g., accept late data within a 5–15 minute window and backfill via batch job).5. Ensuring query performance for dashboards- Materialized views / pre-aggregations: - Maintain minute-level and hour-level materialized tables for common dashboard slices. - Use the warehouse's materialized view feature or implement in-stream maintained aggregates.- Indexing/Clustering & Partitioning: - Partition by time and cluster on top filter columns (region, product, campaign) to reduce scanned data.- BI extracts & caching: - For heavy interactive use, allow scheduled extracts or Live connection with query result caching (Looker persistent derived tables, Tableau extracts refreshed every 30–60s if needed).- Query routing and compute sizing: - Isolate dashboard workloads to a dedicated virtual warehouse (Snowflake) or dedicated slots to avoid contention; autoscale on concurrency.- Use query acceleration services if available (BigQuery BI Engine) to serve sub-second dashboards.- Limit cardinality and precompute top-N, rollups, and heavy joins in transforms rather than at dashboard runtime.6. Reliability, monitoring & operations- SLOs: 1 min end-to-end 95th percentile. Monitor ingestion lag, processing lag, DW load, and query latencies.- Observability: metrics, logs, and alerts from streaming, processing jobs, and DW.- Backpressure & replay: persistent raw storage + retention allows reprocessing for late data or fixes.- Data quality: anomaly detection and reconciliation jobs; dbt tests and lineage.Trade-offs & reasoning:- Using streaming + micro-batches balances low latency and cost/complexity versus pure per-event syncronous writes.- Pre-aggregations/materialized views shift compute to ingest side, giving fast dashboard response and predictable cost.- Serverless DW simplifies operations and scales for peaks; dedicated warehouses help isolate BI load.Example tech stack: Pub/Sub -> Dataflow (Flink/Beam) -> BigQuery (partitioned tables) + BigQuery BI Engine + dbt for batch transforms; Looker/Power BI as front-end with cached extracts for heavy dashboards.This design delivers ≤1 minute latency by computing and ingesting minute-level aggregates in-stream, storing efficient partitioned/clustered tables, and serving dashboards via materialized views/BI caches to ensure interactive performance.
HardTechnical
31 practiced
Explain how zero-copy cloning can be used in CI/CD for data models and dashboards. Propose a workflow where analysts can test changes against production-like data without disrupting real dashboards, and describe rollback steps if a change breaks production.
Sample Answer
Situation: We need a safe CI/CD workflow so BI analysts can develop and validate model/dashboard changes against production-like data without impacting live dashboards, using zero-copy cloning (e.g., Snowflake).Workflow (step-by-step):1. Create a dev branch in your repo for the change and push code (SQL/models/dashboard config).2. CI job: create a zero-copy clone of production schema/database for isolation: - Example (Snowflake): CREATE DATABASE prod_clone CLONE prod_db;3. Run transforms and model migrations against prod_clone (dbt run --profiles-dir ... --target prod_clone). Because cloning is metadata-only, this is fast and uses no extra storage.4. Automated tests against prod_clone: - Data quality/unit tests (dbt test), row counts, constraints. - Integration tests: smoke-test key dashboards by pointing a staging Looker/Tableau connection to prod_clone or by running API-driven UI tests.5. Manual UAT: analysts validate visuals and KPI values in staging dashboards that reference prod_clone.6. Merge & deploy: after approval, CI promotes the model changes to production (apply migrations against prod_db), and deploy dashboard changes (via SDK/API or CI).7. Post-deploy checks: smoke tests on prod.Rollback strategy:- If a model change breaks prod, immediate steps: 1. Revert code in repo and trigger CI to redeploy previous version. 2. Use zero-copy clones + Time Travel to restore data model state if needed (Snowflake example): CREATE DATABASE restore_db CLONE prod_db BEFORE (TIMESTAMP => ...); or use Time Travel to restore tables. 3. If dashboards show bad visuals due to model changes, swap them to a read-only view that points to a stable model (use view synonyms or a connection alias) while rollback completes. 4. If deployment included schema-migrations, run the inverse migration(s) from the previous release branch.- Communicate: notify stakeholders, open incident ticket, run root-cause and add guardrails (extra tests, feature flags) to prevent recurrence.Why this works:- Zero-copy clones let analysts run full-scale tests quickly on production-sized data without copying storage.- CI automation enforces reproducible validation, and view/connection-level indirection enables near-instant rollback and isolation for dashboards.
Unlock Full Question Bank
Get access to hundreds of Cloud Data Warehouse Architecture interview questions and detailed answers.
Sign in to ContinueJoin thousands of developers preparing for their dream job.