Recommended resources
The freely available resources this lesson will direct you to.
Hello! Last lesson followed a purchase through a nightly batch pipeline to a dashboard. This lesson looks more closely at where the purchase is recorded, where it can be retained for later processing, and where it can be analyzed alongside other data.
By the end, you should be able to distinguish a transactional database, an analytical warehouse, and a data lake using two questions: What work must this system do? and How does it store data to support that work? These distinctions will help you make sense of the storage choices in the pipelines you build later.
Start with the job, not the product name
Consider the paid order from the previous lesson. When the customer checks out, the shop needs to record the order and update its operational records reliably. The next morning, a report needs to compare paid revenue across product categories and dates. An engineer may also want to retain the original extracted records so a processing run can be investigated or repeated.
Those are three different jobs. A short overview from Maven Analytics introduces the storage systems commonly associated with them.
Database vs Data Warehouse vs Data Lake | Explaining the Differences
Watch Database vs Data Warehouse vs Data Lake | Explaining the Differences from Maven Analytics for a first comparison of the three storage roles.
Watch the comparison: the database discussion begins with connected tables and purchase records, the warehouse discussion begins just after the database examples, and the lake discussion begins just after the warehouse examples. Focus on the workload each system serves and the form in which it typically keeps data. The distinctions are useful starting points, not rules that every implementation must follow.
The terms OLTP and OLAP describe kinds of processing, not brands of software. Online transaction processing (OLTP) serves operational actions such as placing an order. Online analytical processing (OLAP) serves questions such as “How has paid revenue changed by category over six months?” A transactional database is commonly designed around the first workload; a data warehouse is commonly designed around the second.
Transactional databases: protect everyday operations
Suppose two customers try to buy the last available item. The shop needs to record purchases and stock changes without losing an order or promising the same item twice. Its transactional database must handle many short operations accurately, while allowing the application to retrieve or update particular records quickly.
A typical relational database stores this information in tables with rows and columns. An orders table might hold order IDs, dates, customers, and statuses; an order_items table might hold the products and quantities in each order. The application can find a specific order or change its status without calculating the company’s entire sales history. These records are generally detailed: the system needs to know which order was placed, not only a daily total.
Read AWS’s explanation of OLTP and OLAP for the difference between recording operational changes and querying data for analysis.
OLTP vs OLAP - Difference Between Data Processing Systems - AWS
Read AWS’s What’s the Difference Between OLAP and OLTP? to connect each workload to the priorities of the system serving it.
In “What’s the difference between OLAP and OLTP?”, read the opening explanation. Then go to “Key differences: OLAP vs. OLTP” and its “Data architecture” subsection. Read the architecture comparison, ending with the discussion of backups. Pay particular attention to why accurate, frequent updates matter for the operational system.
A transactional database can answer analytical questions, especially on modest amounts of data. The issue is fit. A query that scans years of orders and groups millions of rows by product category may compete with the checkout application for resources. Separating heavy reporting from operational transactions helps keep both jobs dependable.
The defining feature, then, is not simply “a database has current data.” It is that the database’s primary workload is recording and serving operational activity through frequent, relatively small reads and writes.
Analytical warehouses: organize data for questions across many records
Return to the question “How much paid-order revenue did each category generate yesterday?” Answering it requires looking across many orders and applying consistent rules. A later question might compare months, regions, and product categories, perhaps using data from several applications. This is the workload of an analytical data warehouse.
A warehouse typically holds structured data organized for analysis. A pipeline may combine orders with product information, standardize dates and statuses, and load tables that reports can query consistently. The warehouse might store individual order-level records, precomputed category totals, or both. A warehouse does not have to contain only summaries. Keeping detail allows analysts to ask questions that were not anticipated when the data first arrived.
The accompanying diagram depicts the operational-to-analytical part of this arrangement:
For a data engineer, the distinction is practical. You would not normally make the checkout application depend on a large historical sales query. Instead, you can move the needed data into a system designed to read and combine many records for reporting. The pipeline’s schedule determines how current that copy is: a nightly warehouse load may serve yesterday’s report, while a more frequent load may serve fresher analysis.
A warehouse also has writes—pipelines must load and update it. An operational database also has reads. The difference is what each system is chiefly optimized to do, not an absolute prohibition on other operations.
Data lakes: retain files for flexible use
What if an engineer needs the unchanged order extracts, product files, and website activity logs before deciding exactly how to analyze them? A data lake provides a different storage pattern: it commonly keeps data as files or objects, rather than requiring every incoming record to fit an analytical table first. Those objects might contain structured rows, semi-structured records such as JSON, or unstructured material such as documents and images.
A lake is useful when you want to retain source data for later processing, accommodate varied formats, or reprocess data after a rule changes. In the previous lesson’s pipeline, the optional raw-data area could be part of a lake. If the paid-order rule were corrected, retained source extracts could help rebuild the analytical result without relying solely on the old summary.
The lake’s flexibility comes with work. A file may exist without having a trustworthy definition of its fields, consistent date formats, or even unique records. The phrase schema on read means that structure can be interpreted when data is read for a particular use, rather than requiring all data to match one predefined analytical table on arrival. It does not mean that engineers can ignore schemas, quality checks, or access controls.
IBM’s comparison puts the lake alongside the operational database and the warehouse.
What Is a Data Warehouse? | IBM
Read the storage comparison in IBM’s What Is a Data Warehouse? to clarify what a lake can retain and what a warehouse typically prepares for analysis.
Find “Data warehouses versus other types of data storage.” Begin with the database and lake comparisons. Read the “Data warehouse versus database” and “Data warehouse versus data lake” subsections, stopping before “Data lakehouse versus data warehouse.” Focus on the contrast between storing raw material and organizing data for dependable analytical queries.
“Raw” describes a common part of a lake, not everything a lake may contain. Engineers can also keep cleaned and prepared datasets there. Likewise, putting files in cloud object storage does not automatically create a useful data lake: the data still needs a sensible organization, documented meaning, appropriate permissions, and a way for consumers to find it.
Put the three roles together
Imagine the nightly e-commerce run again, but now include all three storage roles. The application records orders in a transactional database. The pipeline retains copies of extracted order and product records in a raw area of a lake. It then prepares consistent paid-order data and loads a warehouse table used by the sales dashboard.
That is one possible architecture, not a mandatory route. A pipeline can load a warehouse directly from a transactional source, and tools can query well-prepared lake data without first copying it into a separate warehouse. The roles remain useful because they explain why each storage choice was made.
| Storage role | Primary workload | Typical storage pattern | Good fit in the example |
|---|---|---|---|
| Transactional database | Frequent, reliable operational reads and updates | Detailed records in application tables | Record a purchase and update its status |
| Analytical warehouse | Queries across substantial amounts of prepared data | Structured analytical tables, with detail and/or summaries | Compare paid revenue by category and month |
| Data lake | Retention and processing of data in varied forms | Files or objects, often including unchanged source copies | Keep extracts for investigation or reprocessing |
When choosing between them, ask what must happen first and reliably. If the requirement is “accept this order now and keep the operational state correct,” think transactional database. If it is “answer a recurring question across historical data,” think analytical warehouse. If it is “retain varied source material so it can be processed for several purposes,” think data lake. A real platform may need all three, each doing a different job.
Key takeaways
OLTP describes operational transaction processing; OLAP describes analytical processing. Transactional databases commonly serve detailed, frequent application operations. Warehouses organize data for substantial analytical queries. Lakes commonly retain files in varied forms, including original extracts that may need further preparation before use.
The important distinction is workload plus storage pattern, not a rigid rule that one system can only read, another can only write, or a warehouse can only hold summaries. Next, you will set up the local Windows development environment used to begin building and inspecting pipelines yourself.