Data Warehouse Design: From Raw Events to Business Insights

Design a data warehouse that transforms raw event streams into analytics that drive business decisions. Covers dimensional modeling, the medallion architecture, slowly changing dimensions, ETL vs ELT, data quality frameworks, and the warehouse design that scales without becoming an unmaintainable mess.

Data warehouse design transforms raw event streams into actionable business insights. It matters when analysts, product teams, and executives need to answer questions like “What fraction of users completed onboarding in the last 30 days?” or “How does session duration vary across regions and device types?” The process spans ingestion, transformation, and consumption, requiring reliable, performant, and maintainable data pipelines. This page details the journey from raw events to business-ready data, focusing on concrete practices, common pitfalls, and real-world execution.

Ingesting Raw Events

Raw events arrive continuously from web, mobile, and backend services. Each event is a self-contained record of a user or system action, often in JSON format. The ingestion layer must handle high volume, variable schema, and late-arriving data.

Events are typically emitted as application/json payloads over HTTP or Kafka. A typical event has a timestamp, event_type, user_id, and payload map:

{
  "timestamp": "2024-01-05T12:34:56.789Z",
  "event_type": "session_start",
  "user_id": "u12345",
  "payload": {
    "device_type": "mobile",
    "os_version": "iOS 17.2",
    "app_version": "2.3.1",
    "referral_source": "email_campaign"
  }
}

Events are ingested into a message queue such as Apache Kafka. A Kafka topic raw_events holds all event records, partitioned by user_id for efficient retrieval.


kafka-topics.sh --create \
  --topic raw_events \
  --partitions 16 \
  --replication-factor 3 \
  --bootstrap-server kafka1.example.com:9092

Each event is written to the topic using a producer client. The producer must handle batching, compression, and idempotency. A common configuration includes:

A misconfiguration leads to duplicated events or missed acknowledgments. The ProducerRecord key is typically the user_id, ensuring related events are co-located in the same partition.

When an event arrives late—e.g., a session start event arriving 4 hours after its timestamp—the data warehouse must account for this. Events are typically ingested with a received_at timestamp, which is compared to timestamp to detect delays.

Preparing the Landing Zone

The landing zone stores raw events in a structured format suitable for transformation. It is the first durable layer in the data warehouse.

Events are loaded into a columnar storage format such as Parquet or ORC. The landing zone is typically a directory-based file store (e.g., on S3 or HDFS), organized by date and event type.

A common convention uses partitioning: each day is a year=2024/month=01/day=05 subdirectory, with Parquet files inside.

s3://data-warehouse/landing/raw_events/year=2024/month=01/day=05/
├── part-00001.parquet
├── part-00002.parquet
└── _common_metadata

Each Parquet file contains a batch of events from the same day and topic. The file metadata includes statistics (min/max timestamps, row counts, null counts) that enable efficient filtering during query.

The ingestion pipeline must handle schema evolution. When a new event type is introduced—e.g., user_profile_updated—the schema may differ from previous events. The pipeline must:

A common failure mode: a field user_id is occasionally int in one batch and string in another. The warehouse assumes string, but a json payload contains user_id: 12345, which fails to cast. The error appears silently in downstream reports until a query filters on user_id = 12345.

To prevent such issues, the pipeline validates schema conformance using a known schema definition. A schema file raw_events.schema.json defines the expected structure:

{
  "type": "object",
  "properties": {
    "timestamp": { "type": "string", "format": "date-time" },
    "event_type": { "type": "string" },
    "user_id": { "type": ["string", "integer"] },
    "payload": { "type": "object" }
  },
  "required": ["timestamp", "event_type", "user_id"]
}

Before writing to the landing zone, the pipeline applies schema validation using a tool like pyarrow or datafusion.

Transforming Events into Dimensions and Facts

The landing zone feeds into a transformation layer that builds dimensional models: facts and dimensions.

Facts are numeric, measurable events—e.g., session duration, page views, purchases. Dimensions describe the context: user, device, region, campaign.

The transformation is done in a data processing engine such as Spark, Flink, or a SQL-based warehouse (BigQuery, Redshift, Snowflake). Here, we use a pure SQL interface.

A fact table fact_sessions is constructed from the raw events:

CREATE TABLE fact_sessions AS
SELECT
  user_id,
  event_type,
  MIN(timestamp) AS session_start,
  MAX(timestamp) AS session_end,
  COUNT(*) AS event_count,
  DATEDIFF(
    'second',
    MIN(timestamp),
    MAX(timestamp)
  ) AS session_duration_seconds,
  MAX(payload->>'device_type') AS device_type,
  MAX(payload->>'os_version') AS os_version,
  MAX(payload->>'referral_source') AS referral_source
FROM raw_events
WHERE event_type IN ('session_start', 'session_end')
GROUP BY user_id, event_type
HAVING COUNT(*) >= 2;  -- Ensure at least two events per session

This query assumes event types are deterministic: every session_start has a matching session_end. But in practice, sessions can be open-ended: session_start events may arrive without a session_end.

To detect incomplete sessions, the pipeline runs a second pass:

WITH open_sessions AS (
  SELECT
    user_id,
    session_start,
    session_end,
    session_duration_seconds,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY session_start DESC
    ) AS rn
  FROM fact_sessions
  WHERE session_end IS NULL
)
SELECT
  user_id,
  session_start,
  'open_session' AS state,
  session_duration_seconds
FROM open_sessions
WHERE rn = 1;

The pipeline must handle slow updates: fact_sessions is updated incrementally as new session_end events arrive. The table is partitioned by session_start::date, allowing fast queries over specific date ranges.

Dimensions are built independently. The dim_users table is derived from user_profile_updated events:

CREATE TABLE dim_users AS
SELECT
  user_id,
  MAX(payload->>'name') AS name,
  MAX(payload->>'email') AS email,
  MAX(payload->>'signup_source') AS signup_source,
  MIN(timestamp) AS first_seen,
  MAX(timestamp) AS last_seen,
  COUNT(*) AS total_sessions
FROM raw_events
WHERE event_type = 'user_profile_updated'
GROUP BY user_id;

A common failure: dim_users is built daily, but user_profile_updated events arrive at irregular intervals. Over time, the dimension grows stale. The pipeline must detect and rebuild dimensions when new data arrives.

Building Business-Ready Views

With facts and dimensions in place, the warehouse constructs business views: aggregated, denormalized, and curated tables.

A key view is user_engagement_daily, which combines session metrics with user demographics and campaign context:

CREATE TABLE user_engagement_daily AS
SELECT
  u.user_id,
  u.name,
  u.signup_source,
  DATE(s.session_start) AS engagement_date,
  COUNT(*) AS session_count,
  AVG(s.session_duration_seconds) AS avg_session_duration,
  SUM(s.session_duration_seconds) AS total_session_time,
  COUNT(DISTINCT s.device_type) AS unique_device_count,
  SUM(CASE WHEN s.device_type = 'mobile' THEN 1 ELSE 0 END) AS mobile_sessions
FROM fact_sessions s
JOIN dim_users u ON s.user_id = u.user_id
WHERE s.session_start >= '2024-01-01'
GROUP BY u.user_id, u.name, u.signup_source, DATE(s.session_start);

This view is used by product managers to track engagement over time. The query runs daily via a cron job or orchestration tool like Airflow.

A critical failure: the engagement_date is based on session_start, but users often start sessions before the day boundary. A session starting at 2024-01-05T23:59:00 is assigned to 2024-01-05, but it spans two days.

To fix this, the pipeline must align sessions to calendar days. A new view user_engagement_aligned uses DATE_TRUNC('day', session_start):

SELECT
  u.user_id,
  u.name,
  DATE_TRUNC('day', s.session_start) AS engagement_date,
  ...
FROM fact_sessions s
JOIN dim_users u ON s.user_id = u.user_id;

The DATE_TRUNC function is not just a convenience—it is essential for correct daily aggregation.

Consuming Data: Querying and Monitoring

The final step is consuming the data. Analysts query the warehouse using SQL clients or BI tools. The warehouse must support low-latency queries on large datasets.

A typical query asks for engagement trends:

SELECT
  engagement_date,
  COUNT(*) AS total_users,
  AVG(avg_session_duration) AS avg_duration,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY avg_session_duration) AS median_duration
FROM user_engagement_daily
WHERE engagement_date >= '2024-01-01'
GROUP BY engagement_date
ORDER BY engagement_date;

The query runs on a warehouse with columnar storage and predicate pushdown. The WHERE engagement_date >= '2024-01-01' filter is pushed down to the storage layer, reading only relevant partitions.

Monitoring the warehouse involves checking for:

A common error appears in query results: the median_duration is incorrect. The reason: PERCENTILE_CONT(0.5) returns double precision, but the warehouse’s DOUBLE PRECISION column has precision 15, and the query engine casts it to float4 during result serialization. The result is truncated, and values like 246.34999999999999 appear as 246.35.

To detect this, the pipeline runs a validation step:

SELECT
  'median_duration' AS metric,
  COUNT(*) AS count,
  MIN(avg_session_duration) AS min,
  MAX(avg_session_duration) AS max,
  AVG(avg_session_duration) AS mean,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY avg_session_duration) AS median
FROM user_engagement_daily
GROUP BY engagement_date
HAVING COUNT(*) > 0;

The output is compared against a known baseline. A deviation triggers an alert.

Failure Modes and Sharp Edges

These issues are caught only through monitoring, logs, and scheduled health checks. The key insight: the warehouse is not just a storage layer, but a system that must be observed and tuned.

This page was rewritten on 17 October 2026. It replaced a templated version that was not specific to its own title. How these pages are written and checked. If anything here is wrong, tell us at [email protected] and we will correct it.