Warehouses vs lakes vs just a database

In a planning meeting someone says, “Should this live in the warehouse, the lake, or just Postgres?” Three people nod as if those words point to one decision. They do not. They point to different jobs: running the product, storing lots of history cheaply, and answering analytical questions without melting the app. If you pick the wrong home, you either slow the product, overpay for storage theater, or hand analysts a production database and a pager.

This is Part 3 of How data actually moves. Parts 1 and 2 covered the path (sources → land → transform → serve) and timing (batch versus streaming). This part is about where data lives for different jobs: application databases, data warehouses, and data lakes, as a decision tree for non-architects. We will complement storage option reading already on this site rather than redo it. Think of this as the “which shelf for which book” guide after you already know books exist.

What you’ll learn

• What app databases, warehouses, and lakes optimize for in human language
• A decision tree you can walk with a product or ops partner
• Why “just query prod” is a tempting trap
• How lakehouse talk fits without requiring a new religion
• A worked placement table for a mid-size product company

Three homes, three jobs

Strip the logos. Ask what each home is good at.

Application database: the product’s working memory

Your app database (often PostgreSQL, MySQL, SQL Server, or a cloud variant) is optimized to run the product right now. Create an order. Update a profile. Assign a ticket. Enforce constraints. Stay consistent under concurrent writes. Indexes favor “fetch this user’s current cart,” not “scan five years of orders by region for a cohort study.”

That design is a feature. When analysts run heavy scans on the same machine that takes checkout traffic, they compete with customers. Even read replicas only partially solve this: replicas still reflect an operational schema built for transactions, not for dimensional analysis. Soft deletes, mutable statuses, and “current state” tables fight historical questions.

Use the app database as a source on the path from Part 1, not as the default analytics serve layer.

Data warehouse: structured analytics at scale

A data warehouse (examples people name: BigQuery, Snowflake, Redshift, Synapse, and warehouse-like modes on other platforms) is optimized to answer analytical questions over large structured tables. Columnar storage, separation of storage and compute in many modern systems, and SQL-friendly modeling make it natural for marts, metrics, and BI.

Warehouses shine when:

• You have tables with clear grain and types
• Many analysts need governed SQL access
• BI tools expect relational models
• You want transforms-as-code (dbt-style) close to the data
• You care about concurrency for reporting without touching prod

Warehouses are not magic trash compactors. Garbage in still becomes expensive garbage out. They also are not always the cheapest place to park every raw JSON blob forever. That is where lakes enter the conversation.

Data lake: broad, cheap-ish holding for many shapes

A data lake is typically object storage (files and objects) plus conventions and tools to read them. You land CSV dumps, parquet partitions, JSON events, images, logs, and exports that are not yet (or not ever) modeled as warehouse tables. Lakes optimize for flexible landing and long retention, especially when schemas evolve or when multiple processing engines need the same files.

Lakes shine when:

• You must retain raw history for reprocessing
• Data arrives as files or diverse event payloads
• Data science or ML needs large feature source material
• You are not ready to model everything into marts on day one
• Multiple engines (Spark, SQL engines, notebooks) share the same files

Lakes fail when they become swamps: no ownership, no catalog, no retention rules, ten copies of “final_orders.” A lake without land/transform/serve discipline is just cheaper confusion.

Lakehouse, in one honest paragraph

You will hear “lakehouse.” In plain terms, it is the industry’s attempt to get warehouse-like table management (transactions, schema, performance) on lake storage, so you do not always copy everything into a separate proprietary store. Table formats and engines evolve quickly. For a non-architect analyst, the useful takeaway is not a vendor comparison. It is this: you still need the four-stage path. Whether your marts are files with a table format or classic warehouse tables, someone still lands raw, transforms with tests, and serves certified models. Do not let the word “lakehouse” excuse missing definitions.

A decision tree for non-architects

Walk top to bottom. Stop at the first solid yes.

1. Is this data required to run the live product transaction? Yes → app database (system of record). Analytics should copy out, not live here long term.
2. Do people need interactive SQL / BI on curated tables with clear grain? Yes → warehouse (or warehouse-like SQL layer) as serve for analytics.
3. Do you need cheap retention of raw or multi-shaped history for reprocessing or ML? Yes → lake (or lake storage under a lakehouse) as land and archive.
4. Is the dataset small, private, and temporary for one analysis? Yes → local files, a notebook extract, or a sandbox schema may be enough. Do not build a platform for a one-off.
5. Still unsure? Default pattern for most product companies: app DB as source → land in lake or raw warehouse schema → transform to warehouse marts → serve BI from marts.

Rule of thumb: Put systems of record where writes must be correct. Put analytical truth where scans must be safe. Put raw history where reprocessing must be possible.

Comparison table you can paste into a design doc

Worked example: where each dataset should live

Imagine a mid-size SaaS company. Product, support, marketing, and finance all want “the data.” Placement choices:

Pseudo placement checklist in code form

When a new source appears, fill this before anyone buys a connector:

dataset: customer_invoices
system_of_record: billing_app_db          # app DB or SaaS
write_path: product_services              # who must write correctly
analytical_consumers: finance, sales_ops
needs_full_history: true
raw_retention_years: 7
interactive_sql_bi: true
recommended:
  land: lake_or_raw_wh_schema             # reprocessable copy
  transform: warehouse_marts              # net revenue definitions
  serve: bi_dataset_finance_kpis
anti_pattern: bi_direct_on_prod_replica

What a healthy result looks like as a simple inventory row:

That inventory is more valuable than arguing whether Snowflake or BigQuery is “better” for your stage. Vendor choice matters later. Home-by-job matters now.

Questions to ask in a design review (without becoming an architect)

When someone proposes “put it in the lake” or “just warehouse everything,” ask these out loud:

• What is the system of record for writes? If the answer is “the warehouse,” dig harder. Warehouses rarely own checkout or ticketing writes.
• Who reprocesses when the business rule changes? If nobody can rebuild from land, you are stuck with last quarter’s logic forever.
• What is the certified serve object for decisions? Name the table or BI dataset, not “the platform.”
• What is the cost of a heavy scan today? On prod, the cost may be customer latency. On a warehouse, the cost may be dollars and queue. On a swamp lake, the cost may be that nobody finds the file.
• What PII must not clone casually? Placement without privacy review is how shadow copies multiply.
• What is the kill criterion for the temporary pattern? “We will use the replica until…” needs a measurable until.

Those questions keep the conversation on jobs and risk. They also make you a better partner to platform teams, because you arrive with constraints instead of logo preferences.

“Just a database” patterns that still make sense

Not every team needs a lake and a warehouse on day one. Honest smaller patterns:

• Prod + replica + limited analyst views: OK for early stage if queries are light, access is controlled, and you accept limited history. Set a kill criterion (slow queries, locking, PII sprawl).
• Single warehouse that also holds raw schemas: Common. Raw + staging + marts inside one platform. You still separate zones by schema and ownership.
• Lake for files, warehouse for marts: Classic modern path for event-heavy products.
• Operational data store / reverse ETL back to tools: Serve can push curated attributes into the CRM. That does not replace a warehouse; it consumes one.

The mistake is not starting small. The mistake is pretending a small pattern is still small after 50 analysts, five years of events, and finance-grade metrics.

How this connects to ETL/ELT and storage posts

Loading style (ETL versus ELT) is about when transforms run relative to load. This post is about where objects live for which job. You can ELT into a warehouse from a lake. You can ETL into curated warehouse tables from files. You can stream into a lake and batch into marts. The four-stage path still holds.

For deeper tool language and storage tradeoffs already covered on Analytics Made Simple, start from the Learn hub and related storage / ETL reading rather than treating this series as a rewrite. For SQL against warehouse marts, use the SQL series. For analyst-side reshaping before something is productionized, use the Python series. Quality checks belong wherever you transform and serve (data quality series). Metric definitions still need human specs (metrics series). Access and stewardship language pairs with data governance.

Common mistakes

• Analytics on the primary app DB. You will eventually page yourself for a dashboard.
• Warehouse as infinite raw swamp. Without zones and retention, cost and confusion grow together.
• Lake without a catalog or owner. Files are not a platform.
• One home for every job. Product writes, cheap archive, and certified BI rarely share one perfect system.
• Promoting sandbox tables to “official” by vibes. Serve needs certification, not popularity.
• Copying PII everywhere “just in case.” Placement is also a privacy decision.
• Vendor war before job clarity. Decide jobs and zones first; evaluate vendors second.
• Ignoring serve. A beautiful lake and warehouse still fail if BI rebuilds definitions in twelve workbooks.

How to practice this week

1. List five datasets you touch (events, orders, tickets, spend, users).
2. For each, write: system of record, current analytical home, and whether that home is source, land, transform, or serve.
3. Mark any heavy query you still run against prod or a fragile replica.
4. Pick one dataset and draft the placement checklist block from the worked example.
5. Ask a platform or DE partner: “Where should raw live for reprocessing?” Write the answer down even if it is “we do not have that yet.”
6. Optional: identify one swamp folder or schema name that needs an owner more than it needs a new tool.

Next in this series: orchestration as schedules, dependencies, and retries, using Airflow and dbt as examples rather than install guides.

Quick recap

• App databases run the product; warehouses serve structured analytics; lakes hold diverse raw history.
• Choose homes by job, not by fashion.
• Default modern path: source in app systems, land for reprocessing, transform and serve analytics in a warehouse-like layer.
• Lakehouse ideas still require land, transform, serve discipline.
• Small “just a database” setups can work early if you define kill criteria.
• Placement inventories beat logo debates for analysts who inherit pipelines.

Sources

• Google Cloud. “What is a data warehouse?” https://cloud.google.com/learn/what-is-a-data-warehouse
• Google Cloud. “What is a data lake?” https://cloud.google.com/learn/what-is-a-data-lake
• Snowflake documentation. “Key concepts” (warehouse-oriented analytics concepts as an example system). https://docs.snowflake.com/en/user-guide/intro-key-concepts
• AWS. “What is a data lake?” (vendor-hosted intro, architecture patterns). https://aws.amazon.com/big-data/datalakes-and-analytics/what-is-a-data-lake/
• dbt Labs. “How we structure our dbt projects” (warehouse transform zones as practice). https://docs.getdbt.com/best-practices/how-we-structure/1-guide-overview
• Kimball Group. Data warehouse bus architecture resources. https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/kimball-data-warehouse-bus-architecture/

More definitions in Key Terms →

Merch on Etsy

Open store →

Good Cheap Fast White Ceramic Coffee Mug, Data Science Mug, Data Mug, Analytics Mug, Statistics Mug, Programming Mug, Gift Mug, Coffee Mug View on Etsy → Mute Mouse Pad, Data Science Mouse Pad, Analytics Mouse Pad, You're On Mute View on Etsy → Y Hat Mouse Pad, Data Science Mouse Pad, Analytics Mouse Pad, Y Pred Mouse Pad View on Etsy → Imbalanced Classes Mouse Pad, Data Science Mouse Pad, Analytics Mouse Pad, Imbalanced Mouse Pad View on Etsy →