Data Career Hub
Menu
SQLPythonPySparkApache SparkKafkaAirflow
System designCase study 1 of 15

System design · Case study 1 of 15

Design a Cloud Data Warehouse Platform

Design a cloud data warehouse that consolidates data from SaaS tools and operational databases so a mid-sized company can run trusted reporting and self-service analytics.

  • Intermediate
  • 2 min read
  • Updated Oct 2026
On this page
  1. Approach
  2. Architecture
  3. Workload isolation
  4. Transformation
  5. Access control
  6. Data quality
  7. Observability
  8. Cost

Functional requirements

  • Load data from about 30 sources daily, some hourly
  • Model data into staging, intermediate and mart layers
  • Provide governed self-service access for analysts
  • Publish certified dashboards from mart tables

Non-functional requirements

  • Daily marts ready by 07:00
  • Analyst queries never slowed by loading jobs
  • Personal data visible only to authorised roles
  • Predictable monthly compute spend

Scale assumptions

  • About 5 TB total, growing 1 TB a year
  • Around 150 analysts and 20 engineers
  • Peaks of 50 concurrent queries during business hours

Technologies

Cloud data warehouse (for example Snowflake), Managed ingestion connectors or CDC, dbt or SQL-based transformation, Orchestrator, BI tool

Managed connectors and CDC load raw schemas; SQL transformations (for example dbt) build staging and marts; separate warehouses isolate loading, transformation and BI; role-based access controls data.

Approach

This is mostly an organisational design: layering, ownership, access and cost, on top of a managed warehouse that handles storage and scaling.

Architecture

  1. Ingest: connectors and CDC load each source into its own raw schema.
  2. Staging: one model per source table, renaming, typing and deduplicating.
  3. Intermediate: joins and business logic shared across marts.
  4. Marts: star schemas per domain (sales, finance, product), with certified metrics.
  5. Consume: BI dashboards and analyst SQL through role-based access.
Data moves left to right through SQL models; each layer has an owner and tests.

Workload isolation

Separate warehouses for loading, transformation, BI and ad-hoc analysis, each with its own size, auto-suspend and budget.

Transformation

Version-controlled SQL models with dependency management, incremental models for large facts, and tests on keys, nulls and relationships.

Access control

Roles per function (engineer, analyst, finance), grants on schemas, masking policies on personal data, and separate development databases or zero-copy clones for safe experimentation.

Data quality

Tests run in the same job as the models; failures stop downstream models and certified dashboards from refreshing.

Observability

Freshness per source, model run times, test failures, query queueing and credit usage per warehouse.

Cost

Auto-suspend everywhere, resource monitors with alerts, incremental processing, and regular review of the most expensive queries.

Trade-offs

DecisionAlternativeWhy this choiceConsequence
ELT with SQL modelsETL in a separate processing engineWarehouse compute scales; analysts can read and review SQLRaw data stored in the warehouse needs access control
Separate warehouses per workloadOne shared warehouseLoads never slow dashboards; cost attributableMore objects to manage
Incremental models for large factsFull rebuildsLower cost and faster runsMore complex logic for late or corrected data
Certified marts for dashboardsDashboards on any tableConsistent metric definitionsNeeds ownership and review process

Interview follow-ups

  1. Follow-upHow would you stop costs growing unexpectedly?
  2. Follow-upHow do you handle a source that changes its schema?
  3. Follow-upHow would you give analysts a sandbox without risking production?
  4. Follow-upHow do you define a metric once and reuse it everywhere?

By Data Career Hub Editorial · Last reviewed Oct 2026

Search
Filter by type