Every database serves one of two masters: the application or the analyst. These masters want fundamentally different things, and optimizing for one actively hurts the other.

An OLTP (Online Transaction Processing) database handles the application's moment-to-moment needs. A user places an order, the system writes one row to the orders table. A user loads their profile, the system reads one row by primary key. Each query touches a small number of rows, but there are thousands of these queries per second. The access pattern is point lookups and small writes: fetch row 42, update column 3, insert one record. Indexes on primary keys and foreign keys make these operations fast because the database jumps directly to the relevant row without scanning the table.

An OLAP (Online Analytical Processing) system serves the analyst who asks questions like "What was total revenue by product category for each quarter last year?" This query touches every row in the orders table for an entire year, joins it with the products table and the calendar table, groups by two dimensions, and aggregates. The access pattern is full table scans across millions or billions of rows, reading only a few columns out of each row.

The difference is not just about query shape. It is about concurrency and latency expectations. An OLTP system handles thousands of short queries per second, each completing in single-digit milliseconds. Users are waiting on the other end: a checkout page, a login screen, a search result. An OLAP system handles a few dozen long-running queries at a time, each taking seconds to minutes. The consumer is an analyst building a report or a dashboard refreshing on a schedule. Nobody is staring at a loading spinner waiting for a quarter's revenue to compute.

Why You Need a Separate System

The core tension is this: OLTP databases are row-oriented. They store each row contiguously on disk because the typical operation reads or writes an entire row. When you INSERT an order, the database writes all columns of that row in one sequential disk write. When you SELECT * FROM orders WHERE id = 42, it reads one contiguous block.

But analytical queries do not want entire rows. "Total revenue by category" only needs the category_id and amount columns. In a row-oriented store, reading those two columns means reading every column of every row and discarding 90% of the data. For a table with 50 columns and 1 billion rows, you read 50 billion column values to use 2 billion of them.

The mismatch runs deeper than just I/O. OLTP and OLAP differ in their optimization targets:

• Indexing: OLTP uses B-tree indexes on primary keys and foreign keys for fast point lookups. OLAP uses block-level min/max metadata and bitmap indexes for fast scans and filters.
• Concurrency: OLTP uses row-level locking and MVCC to handle thousands of concurrent short transactions. OLAP uses query-level isolation because each query runs for seconds or minutes and reads immutable snapshots.
• Schema design: OLTP normalizes to eliminate redundancy and ensure write consistency. OLAP denormalizes to minimize joins and maximize read throughput.
• Data freshness: OLTP data is current to the millisecond. OLAP data is typically minutes to hours behind, loaded in batches.

This is why data warehouses exist as separate systems, purpose-built for analytical workloads with column-oriented storage, compression, and precomputed aggregations.

The Data Warehouse as a Separate System

A data warehouse is a database specifically designed to support analytical queries. It receives copies of data from all the OLTP systems across the organization (the e-commerce database, the CRM, the billing system, the event stream) and consolidates them into a single schema optimized for analysis. The warehouse is not serving user traffic. Nobody's checkout flow depends on it. This isolation means analytical queries can consume 100% of the warehouse's CPU and disk without affecting a single customer.

The most widely used warehouses today are cloud-managed: Amazon Redshift, Google BigQuery, and Snowflake. They separate storage from compute, meaning you can scale query processing power independently of data volume. You pay for data scanned (BigQuery) or cluster hours (Redshift), not for data at rest. This economics change is why even small companies can now afford analytical infrastructure that used to require a dedicated team and hardware.

On-premise alternatives still exist: Apache Hive runs on Hadoop, ClickHouse provides open-source columnar storage, and traditional vendors like Teradata and Oracle Exadata serve enterprises that need data residency control. The core principles are the same regardless of vendor: column-oriented storage, parallel query execution, and schema designs optimized for aggregation.

How Data Gets Into the Warehouse

The warehouse does not serve application traffic directly. It receives data from OLTP systems through a pipeline that runs on a schedule (nightly, hourly, or near-real-time depending on business needs). This pipeline is the bridge between the operational world and the analytical world. If the pipeline breaks, the warehouse goes stale and every dashboard in the company shows outdated numbers. Pipeline reliability is therefore a critical concern for any data engineering team.

There are two dominant pipeline architectures: ETL and ELT. They differ in where the data transformation step happens, and the choice has significant implications for team structure, tooling, and debugging.

ETL and ELT

Data flows from OLTP systems into the warehouse through Extract-Transform-Load (ETL) or Extract-Load-Transform (ELT) pipelines. Understanding the difference is essential because the choice affects how your data team works day-to-day.

In ETL, you extract data from source systems (your Postgres database, your payment provider's API, your event stream), transform it into the warehouse's schema (denormalize, clean, deduplicate, compute derived fields), and then load it into the warehouse. The transformation happens in a separate processing layer (Spark, Airflow, dbt) before the data lands in the warehouse.

In ELT, you extract and load the raw data into the warehouse first, then transform it using the warehouse's own SQL engine. This approach has gained popularity because modern warehouses (BigQuery, Snowflake, Redshift) have massive compute power and charging per-query makes it cheaper to transform in-place than to maintain a separate transformation cluster.

The practical difference matters for operations. In ETL, a Spark job that fails at 3 AM blocks all downstream data. Your on-call engineer must debug Spark cluster issues, memory pressure, and data skew in a distributed processing framework. In ELT, a SQL query that fails at 3 AM is a SQL error: wrong column name, type mismatch, or a source table that changed schema. The debugging surface is smaller and more familiar to the data team.

The extraction step itself has evolved. Managed connectors like Fivetran and Airbyte handle the tedious work of connecting to 200+ data sources (Salesforce, Stripe, MySQL, Google Analytics) and keeping the extraction running reliably. They detect schema changes in the source, handle API rate limits, and manage incremental loads so you do not re-extract the entire history every night.

Incremental vs Full Loads

Loading data into the warehouse can be done incrementally or fully.

A full load drops all existing data and reloads from scratch. This is simple and guarantees correctness, but it is expensive for large tables. Loading 10 billion rows every night when only 500,000 changed is wasteful.

Incremental loads only transfer rows that changed since the last load. This requires a reliable change-tracking mechanism in the source system: a last_modified timestamp, a change data capture (CDC) stream, or a monotonically increasing sequence number. The load job reads rows where last_modified > last_successful_load_time and inserts or updates them in the warehouse.

The tricky part is handling deletes. If a row is deleted from the source system, an incremental load based on last_modified will never see it. Deleted rows do not appear in the source query results. Solutions include soft deletes (marking rows as deleted with a timestamp), CDC streams that emit explicit delete events, or periodic full loads to reconcile and catch any missed deletes.

Most production pipelines use a hybrid approach: incremental loads for daily efficiency combined with weekly or monthly full loads for correctness.

The full load catches any rows that the incremental mechanism missed due to clock skew, source system bugs, or edge cases in the change tracking logic. This "belt and suspenders" approach is standard practice at companies of all sizes because the cost of a weekly full reload is low compared to the cost of silently missing data for months.

Pipeline Orchestration

ETL/ELT pipelines are not single scripts. They are directed acyclic graphs (DAGs) of tasks with dependencies. Apache Airflow is the most widely used orchestrator: you define tasks (extract from Postgres, extract from Stripe, transform in the warehouse, build materialized views) and their dependencies (transform cannot run until both extractions complete). Airflow schedules the DAG, manages retries on failure, sends alerts when tasks exceed their SLA, and provides a web UI for monitoring.

Alternatives include Prefect, Dagster, and dbt Cloud. dbt has become particularly popular for the transformation layer because it lets analysts write transformations as SQL SELECT statements with dependency declarations, and it handles the orchestration, testing, and documentation automatically.

The key reliability concern is idempotency: every task in the pipeline should produce the same result whether it runs once or three times. Network failures, timeouts, and scheduler restarts mean tasks will occasionally be retried. If an extraction task appends duplicates on retry, the warehouse will have inflated numbers. The standard fix is to use merge/upsert operations (INSERT ON CONFLICT UPDATE in Postgres, MERGE in most warehouses) instead of blind inserts.

A related pattern is partition-based idempotency: instead of merging individual rows, the pipeline overwrites an entire partition (e.g., one day's worth of data) on each run. If the pipeline for 2025-03-15 runs three times due to retries, it drops and recreates the 2025-03-15 partition each time, producing the correct result regardless of how many times it runs. This is simpler to implement than row-level merges and works well when partitions align with the pipeline's natural granularity.