Data warehouses, pipelines, and analytics

The database you have studied throughout this module is primarily designed to operate the product.

It handles activities such as creating orders, updating account balances, retrieving customer profiles, checking inventory, recording payments, and serving application requests.

These operations are usually small, frequent, and time-sensitive.

Then someone asks a different kind of question:

“How have latte sales changed across every café over the past three years?”

Answering that question may require reading and combining millions or billions of historical records. And running such a large analytical query directly against the production database could consume processing capacity needed by customers placing orders right now.

For this reason, many companies separate operational workloads from analytical workloads.

Operational versus analytical systems

The production database supports the application’s daily operations, so it is optimized for large numbers of relatively small reads and writes, such as:

This style of work is commonly called Online Transaction Processing, or OLTP.

The word “transaction” here refers broadly to operational business events, although OLTP databases also commonly use the database transactions you learned earlier.

Analytical systems answer larger questions such as:

This style of work is commonly called Online Analytical Processing, or OLAP.

A useful distinction is:

OLTP systems run the business. OLAP systems help people understand the business.

Why separate the workloads?

Operational and analytical queries have different needs.

An operational query might retrieve one order:

An analytical query might scan years of data, join several large tables, group results by region and month, and calculate totals, and while that query may be perfectly valid, it can require substantial computing power.

If both workloads compete for the same database resources, a large report could slow checkout, account updates, inventory changes, customer-support tools, and other live product operations.

Separating the systems allows the production database to remain responsive while analytical systems perform heavier work on copied or reorganized data.

What is a data warehouse?

A data warehouseConcept · lights on your mapdata warehouseA system for storing and analyzing large amounts of historical data, reorganized into structures that make reporting easier; Snowflake is a common example. Curated shelves, where a data lake is a more permissive pool. is a system designed to store and analyze large amounts of historical data from one or more sources.

It may contain information copied from production databases, payment systems, customer-support platforms, marketing tools, mobile applications, website events, financial systems, and other third-party services.

The data is commonly reorganized into structures that make reporting and analysis easier.

For example, the production system may store customers, orders, products, and stores in carefully normalized tables, while the warehouse may create analysis-friendly tables that make it easier to calculate daily revenue by store, monthly product sales, customer retention, refund rates, and marketing performance.

The warehouse is usually optimized for large, read-heavy queries rather than constant individual transaction updates.

Copies, not the live source of truth

A data warehouse generally contains copies of operational information; it does not replace the systems that originally produced it.

The production order database remains the source of truth for whether order 1042 is confirmed, but the warehouse may contain a copy of that order for reporting.

This creates a freshness question.

If warehouse data is loaded every night, a morning report may not include orders placed today. If it updates every few minutes, the data is fresher but the pipeline becomes more complex.

A warehouse should therefore communicate how current its information is.

A useful distinction is:

The operational database answers what the product currently believes.

The warehouse answers analytical questions using the data it has received so far.

What is Snowflake?

Snowflake is a widely used cloud data platform commonly used as a data warehouse. Organizations use it to store and analyze large datasets, run SQL queries, control data access, and support reporting and analytics tools.

When someone says:

“That data is in Snowflake,”

they commonly mean the data has been loaded into the organization’s Snowflake analytical environment.

Other warehouse technologies include Google BigQuery, Amazon Redshift, Microsoft Fabric or Synapse, Databricks SQL, and traditional enterprise warehouse systems.

The products differ, but the general purpose is similar: support large-scale analysis without placing the full workload on operational systems.

What is a data lake?

A data lakeConcept · lights on your mapdata lakeA store holding large amounts of data in raw or flexible form: structured, semi-structured, and unstructured together. More permissive than a data warehouse about what enters and how it is shaped on the way in. stores large amounts of data in relatively raw or flexible forms.

It may contain application logs, JSON events, CSV exports, images, audio, documents, database extracts, sensor data, historical files, and other structured, semi-structured, and unstructured data.

A warehouse resembles curated shelves organized for known analytical uses, while a lake is more flexible about what enters and how it is initially shaped.

This makes data lakes useful when an organization wants to preserve data before every future use is known.

A lake should not be a dumping ground

Data lakes are sometimes described as places where companies “keep everything and organize it later,” and that can happen, but a poorly managed lake can become difficult to use or trust.

A functional data lake still needs:

• Clear ownership
• Access controls
• Data catalogs
• File and table formats
• Retention rules
• Naming conventions
• Quality checks
• Documentation
• Privacy protections

Without those practices, people may not know what the data means, where it came from, whether it is current, whether it contains PII, whether it is safe to use, or which copy is authoritative.

A flexible storage system does not remove the need for governance.

Warehouses and lakes increasingly overlap

The boundary between warehouses and lakes is not absolute. Modern warehouses can store and query semi-structured data such as JSON, and data lakes can support structured tables and SQL queries.

You may hear the term lakehouse for systems that combine features associated with both models.

You do not need to master this category yet.

The main distinction is:

Warehouses traditionally emphasize curated analytical data.

Lakes traditionally emphasize flexible storage of large amounts of raw and varied data.

Modern platforms increasingly borrow capabilities from both.

How does data reach the warehouse?

Operational data does not automatically appear in an analytical system.

A data pipelineConcept · lights on your mapdata pipelineAn automated sequence of steps that transfers data from one place to another: reading data from source systems, copying it into analytical storage, correcting or standardizing formats, combining sources, and producing tables used by dashboards. Pipelines are the infrastructure connecting operational systems to analytics. moves and processes it.

A data pipeline is an automated sequence of steps that transfers data from one place to another.

A pipeline might:

1. Read new orders from the production database.
2. Copy them into analytical storage.
3. Correct or standardize field formats.
4. Combine them with store and product data.
5. Produce a table used by a sales dashboard.

Pipelines are the infrastructure connecting operational systems to analytics.

ETL

A classic pipeline pattern is ETLConcept · lights on your mapETLExtract, Transform, Load: pull data from its sources, clean and reshape it, then write it into a destination such as a warehouse. Transforms before loading, which is what distinguishes it from ELT.:

• Extract
• Transform
• Load

Extract

Retrieve data from its original sources.

For example, a pipeline might retrieve orders from Postgres, payments from a provider, store information from an internal system, and marketing data from an external API.

Transform

Clean, standardize, combine, or reshape the data.

Transformations might convert timestamps into a common time zone, rename fields, remove duplicate events, convert dollars into cents, join orders with stores, or calculate derived metrics. They might also remove or mask sensitive information.

Load

Write the transformed result into the destination, such as a data warehouse.

The sequence is:

ELT

Modern cloud systems commonly use ELTConcept · lights on your mapELTExtract, Load, Transform. The modern reordering: load raw data into the destination first, then transform it there. Neither it nor ETL is superior; the difference is only where the transforming happens.:

• Extract
• Load
• Transform

The raw or lightly processed data is first loaded into the warehouse or lake, and transformations then run inside the destination system.

The sequence becomes:

This approach takes advantage of the destination platform’s computing power and preserves the original data for additional transformations.

Neither ETL nor ELT is universally superior, and real systems may combine them: some data must be cleaned or protected before loading, while other transformations are easier to perform after arrival.

The key distinction is the order:

ETL transforms before loading.

ELT loads before most transformations.

Data lineage

Once data passes through several pipelines and transformations, teams need to know where it came from.

Data lineage describes the path data follows from its original source through later systems and transformations.

For a revenue dashboard, lineage might show:

Lineage helps teams investigate why a number changed, which source supplied a field, which reports depend on a table, what will break if a pipeline changes, and where personal data has traveled.

It is the analytical equivalent of tracing an API request through a product.

Batch processing

A batchConcept · lights on your mapbatchCollecting data over a period and processing it together on a schedule. Easier and cheaper to operate than streaming, and it pays for that in freshness. pipeline collects data over a period and processes it together.

A nightly batch might:

1. Collect all orders placed during the day.
2. Load them into the warehouse at midnight.
3. Recalculate reporting tables.
4. Refresh dashboards before morning.

Batch processing is often easier to operate, cost-efficient, predictable, and suitable when immediate updates are unnecessary.

Its limitation is freshness.

A dashboard may display Data current through yesterday at 11:59 PM, which can be perfectly acceptable if the business question does not require real-time information.

Streaming

StreamingConcept · lights on your mapstreamingProcessing data continuously as events arrive, with low delay. Fresher than batch, and it pays for that in infrastructure and failure-handling complexity. processes data continuously or in very small increments as events arrive.

When an order is placed, an event might enter the pipeline within seconds.

A streaming system could update fraud monitoring, live delivery tracking, operational alerts, inventory estimates, real-time dashboards, and recommendation systems.

Streaming does not necessarily mean each event is processed entirely alone, and systems may group small windows of events together internally.

The important distinction is that data flows continuously with relatively low delay rather than waiting for a large scheduled batch.

Batch versus streaming

Batch and streaming involve a tradeoff.

Batch systems are often simpler and less expensive to operate, while streaming systems provide lower delay but introduce more infrastructure and failure-handling complexity.

A team may ask:

• How fresh must this information be?
• What happens if an event is delayed?
• Can events arrive more than once?
• Does their order matter?
• How will failures be retried?
• What does real-time accuracy cost?
• Would hourly or nightly updates be sufficient?

“Real time” sounds attractive, but not every business question needs it.

A useful principle is:

The required freshness should justify the system’s complexity.

What is Kafka?

Apache Kafka is a widely used event-streaming platform. Systems publish events into Kafka, and other systems can read those event streams.

For example, the coffee backend might publish:

Several systems could consume the event, including analytics, inventory, notifications, fraud monitoring, and store operations.

Kafka can retain events for a period, allowing consumers to process them at their own pace or replay them later.

A useful beginner model is:

Kafka is infrastructure for moving and retaining streams of events between systems.

You will revisit event-driven architecture in a later module.

What is Spark?

Apache Spark is a distributed data-processing engine. It divides large processing jobs across multiple machines so that they can work on different pieces of the data in parallel.

Spark can be used for large analytical transformations, batch processing, stream processing, machine-learning preparation, and processing data too large for one machine.

Kafka and Spark serve different roles.

Kafka commonly transports and retains event streams, while Spark commonly performs large-scale calculations and transformations on data.

They may be used together, but neither requires the other.

Scaling live databases

Analytical systems are not the only systems that must scale. Operational databases may also need to serve more traffic or store more data than one machine can comfortably handle.

Three terms you will encounter are replication, partitioning, and sharding. Analytical platforms use these techniques too, often invisibly inside the managed service. Operational databases are where teams tend to meet them directly.

They solve related but distinct problems.

Replication

ReplicationRecognition — just know it existsreplicationCreating and maintaining additional copies of data. A primary database may accept writes while one or more replicas maintain copies. Replicas can help with availability, disaster recovery, serving additional read traffic, and operating across locations, but they may briefly lag behind the primary. creates and maintains additional copies of data.

A primary database may accept writes while one or more replicas maintain copies.

Replicas can help with availability, disaster recovery, serving additional read traffic, and operating across locations.

For example:

If applications read from replicas, the system can reduce the read load on the primary database.

However, replicas may briefly lag behind the primary, and a user could update a profile and then read from a replica that has not received the change yet.

Partitioning

PartitioningRecognition — just know it existspartitioningDividing a large dataset into smaller pieces by date, region, store, or another key, so queries can skip what they don’t need. Replication copies data; partitioning divides it; sharding spreads the divisions across servers. divides a large dataset into smaller pieces called partitions.

A table might be partitioned by date, customer region, store, account range, or another chosen key.

For example, orders could be separated by month:

Partitioning can improve manageability and help queries avoid scanning irrelevant data, and the term can refer to divisions within one database system or across several systems, depending on the context.

Sharding

ShardingRecognition — just know it existsshardingDistributing portions of data across multiple database servers, so storage and workload can exceed one machine. Partitioning divides the data; sharding is what puts those divisions on separate servers. distributes portions of data across multiple database servers.

For example:

Each shard stores only part of the total dataset.

Sharding allows storage and workload to extend beyond one machine, but it creates additional complexity, from choosing the shard key and moving data between shards to querying across shards, handling uneven traffic, maintaining relationships, and coordinating transactions.

A useful beginner distinction is:

Replication copies data.

Partitioning divides data.

Sharding places divided data across separate database servers.

Eventual consistency

When several copies of data exist, they may not all update at exactly the same instant.

Eventual consistencyRecognition — just know it existseventual consistencyA model in which copies of data may temporarily disagree, but the system expects them to converge if no new updates occur. Some distributed systems coordinate updates more strictly before confirming success; others accept temporary disagreement in exchange for benefits such as lower latency or greater availability. A profile photo may tolerate brief inconsistency; an account balance or seat reservation may require stronger coordination. is a model in which copies may temporarily disagree, but the system expects them to converge if no new updates occur.

Suppose Erik changes his profile photo.

The primary system stores the new image immediately, but some replicas, caches, or regional systems may continue showing the old photo briefly, and after the update spreads, they all show the new version.

During the transition:

Later:

The systems eventually agree.

Eventual consistency is a choice

Some distributed systems coordinate updates more strictly before confirming success, while others accept temporary disagreement in exchange for benefits such as lower latency or greater availability.

Different types of data may need different guarantees.

A profile photo may tolerate brief inconsistency, but an account balance or seat reservation may require stronger coordination.

A useful product question is:

How harmful would it be if two parts of the system briefly showed different answers?

The answer helps determine the appropriate consistency model.

Operational and analytical truth

A company may now have several related forms of data. There may be live operational data in the production database, replicated copies for availability or reads, events flowing through Kafka, raw history resting in a data lake, curated tables in a warehouse, and metrics on dashboards.

These systems serve different purposes and may update at different speeds, which raises several questions:

• Which system is the source of truth?
• How fresh is this copy?
• Which transformations changed it?
• Does it contain duplicates?
• What happens if the pipeline fails?
• Can the result be reproduced?
• Who owns the definition of the metric?

Analytics does not replace the source-of-truth principle. It extends it across a larger landscape of copies and transformations.

Seeing the complete analytical flow

Imagine that Erik places a coffee order.

The production database stores the order so the product can operate.

The system may also publish an order.created event.

A pipeline extracts or consumes the order data and loads it into analytical storage. Transformations then standardize it, connect it to store and product information, and fold it into the reporting tables along with everyone else’s orders.

A warehouse query later calculates monthly latte sales across every café, and the CEO sees the result in a dashboard without forcing the production database to scan every historical order during customer checkout.

The complete flow might be:

The same order exists in several systems, but each copy serves a different purpose.

The mental model to remember

An operational database, or OLTP system, supports the live product through frequent small reads and writes.

An analytical system, or OLAP system, answers large historical and aggregated questions.

A data warehouse stores curated data designed for analytics and reporting.

A data lake stores large amounts of structured, semi-structured, and unstructured data in flexible forms.

A data pipeline moves and transforms data between systems.

ETL means extract, transform, then load.

ELT means extract, load, then transform.

Batch processing handles collected groups of data on a schedule.

Streaming processes continuously arriving data with relatively low delay.

Kafka moves and retains streams of events.

Spark processes large datasets across multiple machines.

Replication maintains copies of data.

Partitioning divides a dataset into smaller sections.

Sharding distributes those sections across database servers.

Eventual consistency allows copies to disagree temporarily while expecting them to converge later.

You should now understand why production applications and analytics commonly use different data systems—and how data travels from live transactions into large-scale reporting.