Data Warehouses and Big Data

In the previous lesson you saw why analytical work deserves its own copy of the data, and how a pipeline fills that copy overnight. What we didn't answer is what that copy actually lives in. It isn't just "another database of the same kind on a quieter machine." There's a whole category of system built for the analytical side of that line, and a second category built for data you haven't decided what to do with yet. Those two, plus the vocabulary people use for very large data, are what this lesson covers. The data here comes from that same live platform, which has been accumulating logs and device readings for years.

In this lesson, you will learn to:

• Explain why data warehouses use columnar storage and parallel processing for large reporting queries.
• Distinguish a data warehouse from a data lake by the structure, cost, and intended use of its data.
• Choose between batch and streaming processing by considering volume, velocity, variety, and the need for freshness.

A data warehouse is a system built specifically to answer large, read-heavy reporting questions over cleaned, structured data. Read that definition slowly, because every part of it is doing work. Large and read-heavy: it expects queries that scan millions of rows and rarely change a single one. Cleaned: the messy, inconsistent values have already been fixed on the way in. Structured: the data sits in tables of rows and columns, with agreed column names, just like a relational database.

So what makes it different from the relational database serving your live application? Mainly how it stores things internally and how it spreads the work out. A warehouse uses columnar storage: it keeps each column separately rather than each row together, which means a query asking only for revenue and clinic name reads just those two columns instead of dragging every field along for the ride. It also relies on massively parallel processing, spreading a single query across many machines at once, so a scan of four hundred million rows is divided into chunks that run in parallel. That design is wonderful for a monthly trend report and terrible for updating one patient's phone number, which is precisely why it lives on the analytical side of the line.

The three you'll hear named constantly are Amazon Redshift on Amazon Web Services, Google BigQuery on Google Cloud, and Azure Synapse Analytics on Microsoft Azure. They differ in how you pay and how much you manage, but their purpose is identical: give reporting a home that can be as heavy as it likes.

Here, Nina, an analytics manager, asks Dan, a data engineer, whether a reporting copy is simply another live database. Their exchange makes the operational difference concrete:

• Nina: So the reporting copy is just a second database, right? Same thing, different server.
• Dan: Not quite. It's a data warehouse. Same idea of tables and columns, but built inside for scanning rather than for single-record updates.
• Nina: Does that actually matter, or is it a naming thing?
• Dan: It matters. The report she runs takes fifteen minutes on the live database. On a warehouse it reads only the columns it needs and splits the scan across several machines, so you're talking a minute or two.
• Nina: And if I asked it to book an appointment?
• Dan: It would do it badly. That's not the job it's shaped for.

Notice that Dan doesn't describe the warehouse as "better." He describes it as shaped for a different job.

A warehouse has one demand that trips organizations up: the data has to be cleaned and structured before it goes in. Someone must decide what the columns are and what belongs in them. That's fine for appointments and revenue. It's much harder for five years of raw web server logs, device readings, and exported files that nobody has looked at yet.

That's where a data lake comes in. A data lake is raw files held in object storage (files stored and retrieved whole, priced per gigabyte-month), the cheap, bottomless storage you met earlier for photos and backups. You drop the files in as they are, in whatever format they arrived, and you decide later what to do with them. Nothing is reshaped on the way in.

The trade-off is genuine and worth stating plainly. A warehouse gives you fast, reliable answers but demands structure and effort upfront. A lake accepts anything for very little money but gives you no answers until somebody does the work of making sense of the files. Consequently, most organizations end up with both: raw everything in the lake, and the cleaned subset that reporting actually relies on loaded into the warehouse.

The comparison highlights why organizations often use both: retain raw source data cheaply in a lake, then shape the subset needed for dependable reporting in a warehouse.

The phrase big data gets used loosely, so it helps to have the three traits people mean by it. Volume is how much data there is. Velocity is how fast it arrives. Variety is how many different shapes it comes in: neat rows, free text, images, sensor readings. Data becomes genuinely difficult when several of those climb together, and asking about each one separately is a good way to ground a conversation that has drifted into ambition.

Velocity leads directly to the last distinction. Batch processing means a scheduled job handles a chunk of data at once: overnight, the pipeline takes yesterday's files and processes them together. Streaming means readings are processed continuously as they arrive, within seconds. Streaming sounds obviously superior until you count the cost. Batch runs once, finishes, and stops. Streaming needs an always-on pipeline, always-on monitoring, and someone to notice when it silently stops at 3 a.m. For a small team, that difference is the whole decision, and the honest position is that "live" is a per-feed choice, not a single setting for the whole platform. A clinical alert may justify streaming. Yesterday's finance totals almost never do.

The insight to carry: warehouses, lakes, batch, and streaming are all answers to the same question of how much structure and how much immediacy a particular data set genuinely needs, and the answer is usually different for each feed. Two quick checks come next, one matching the three warehouse services to their providers and one placing five years of raw logs in the right home, before you take that per-feed reasoning into a live conversation with an analytics manager who wants everything streaming at once.