Someone asks where “the data” lives. You open your mouth and five answers fight for the microphone: the app database, the warehouse, a lake bucket, a Looker explore, and a spreadsheet named final_final_v3. They are all partly right. Different stores solve different jobs.
This guide sorts the main options (operational databases, warehouses, lakes, marts, and the messy file layer) so you can choose by question and workload, not by which logo is trendy. Related reading: ETL vs ELT and data governance.
What you’ll learn
- What each major store is for
- How latency, structure, and cost trade off
- When a mart helps and when it creates another silo
- A simple decision table for common analytics questions
- Mistakes teams make when they buy storage before they name a use case
Start with the question, not the product
Storage debates go wrong when they start with “we need a lakehouse” instead of “we need same-day order margins by region with a trusted product grain.” Name the grain, freshness, users, and failure cost first. Then pick the layer.
Operational databases
These power products: Postgres, MySQL, SQL Server, and app-specific stores. Optimized for correct writes and fast lookups of single records. They are usually a poor home for heavy historical analytics scans, but they are the system of record for many entities. If analysts run year-long scans on production checkouts, you will meet your DBA in a hallway conversation you will not enjoy.
| Good for | Risky for |
|---|---|
| App transactions, current state | Year-long scans that block writers |
| Point lookups by primary key | Ad hoc joins across the whole business |
| Strict schemas the product needs | Uncontrolled analyst write access |
Data warehouses and lakehouse cousins
Warehouses (Snowflake, BigQuery, Redshift, and similar) are built for analytical queries over large modeled tables. Cloud warehouses made ELT common: load, then transform with SQL and tools like dbt. Lakehouse platforms blur lines by putting warehouse-style tables on cheap object storage with table formats that support richer management.
What you want here: clear facts and dimensions, documented grains, and access controls that match business roles. What you do not want: every raw JSON dump with no owner living forever under a folder called temporary that is five years old.
Data lakes and object storage
S3, GCS, ADLS, and lake formats store cheap, flexible files: logs, extracts, images, parquet dumps. Great for history and multi-purpose raw landing. Terrible as the only interface for executives, because “just query the lake” often means “become a part-time data engineer before lunch.”
Use lakes as a landing and archive layer. Promote curated datasets into warehouse tables or well-governed lakehouse tables people can trust. Promotion is a product decision, not a weekend script without an owner.
Data marts and semantic layers
A mart is a purposeful slice: finance mart, growth mart, support mart. A semantic layer (LookML, dbt metrics, BI datasets) exposes business-friendly fields on top. They reduce repeated join logic. They also create drift if five marts redefine revenue differently. Pair marts with governance, not copy-paste SQL across folders.
Files and spreadsheets
Sheets still run companies. Pretending they do not is how shadow pipelines form. Give critical sheets owners, versioning where possible, and a path into the warehouse when a process stabilizes. Banning sheets without offering a better path just moves the chaos to email attachments and desktop files named for someone’s dog.
Decision table
| You need… | Prefer… | Watch out for… |
|---|---|---|
| Correct order writes from the app | Operational DB | Analysts overloading prod |
| Trusted cross-domain reporting | Warehouse models | Undefined grains |
| Cheap long history of raw events | Lake / object storage | No curation path |
| Department self-serve with guardrails | Mart + semantic layer | Metric forks |
| Fast one-off scenario modeling | Sheet or notebook | Becoming the system of record |
Cost, latency, and governance
Cloud storage is cheap; careless cloud compute is not. Scans that read every column of a giant fact table for a tiny chart will show up on a finance review. Design tables and clustering with common filters in mind. Put owners on expensive datasets. Governance is not only privacy; it is also “who pays when this query goes wild.”
Common mistakes
- Buying a lake to store everything with no promotion rules
- Running huge analytics on the production OLTP database
- Three warehouses for politics, not workload
- No cost controls on cloud scans
- Calling a folder of CSVs a platform
- Skipping grain definitions because “we will clean it later”
A Monday whiteboard exercise
Draw the stack. Point to one painful metric. Ask which layer owns its truth and who can change the definition. If nobody can answer, that is your first project. New vendors can wait until the ownership sentence is boringly clear.
Quick recap
- Different stores solve different jobs
- Warehouses excel at modeled analytics; lakes excel at cheap raw history
- Marts need shared definitions
- Start from grain and freshness
- Promote data upward with owners
Write examples from your own workplace. A named dashboard fight teaches more than a generic industry claim, and it keeps the post useful when the logos on the architecture slide change again next year.
If two teams argue about a number, put both definitions on one page with owners and timestamps. Clarity beats a forced compromise that nobody trusts enough to use in a real decision meeting.
Ship a small artifact this week: a definition card, a quality check, a retired vanity chart, or a one-page brief. Momentum compounds faster than another strategy deck about becoming data driven someday.
Teach newcomers where the source of truth lives on day one. Onboarding is a data-system surface. If new hires learn the wrong table first, you will spend months undoing that habit in code review and Slack threads.
When something breaks, fix the rule or the automated test that should have caught it. Heroic manual checks do not scale, and they disappear the week everyone is out on holiday or buried in a launch.
Prefer plain words in meetings until everyone shares a definition. Jargon is fine after that. Before that, jargon is just a way to lose the people who will actually act on the analysis.
Keep a short change log for metrics, pipelines, and critical dashboards. Future you will need the date a definition shifted, and you will not find it in a year-old screenshot buried in a slide archive.
Measure one concrete thing that proves the new approach beats the old habit: fewer reconcile hours, faster ticket answers, lower duplicate rates, or fewer “which number is right” threads per month.
Resist boiling the ocean. One domain, one partnership, one metric strip, or one retrieval evaluation set is enough to learn. Expansion is easier after you have a win people can point at without squinting.
Document the messy edge cases in the open. Hidden footnotes become tribal knowledge, and tribal knowledge becomes an outage when the only person who remembered the footnote changes teams.
Write examples from your own workplace. A named dashboard fight teaches more than a generic industry claim, and it keeps the post useful when the logos on the architecture slide change again next year.
If two teams argue about a number, put both definitions on one page with owners and timestamps. Clarity beats a forced compromise that nobody trusts enough to use in a real decision meeting.
Ship a small artifact this week: a definition card, a quality check, a retired vanity chart, or a one-page brief. Momentum compounds faster than another strategy deck about becoming data driven someday.
Teach newcomers where the source of truth lives on day one. Onboarding is a data-system surface. If new hires learn the wrong table first, you will spend months undoing that habit in code review and Slack threads.
When something breaks, fix the rule or the automated test that should have caught it. Heroic manual checks do not scale, and they disappear the week everyone is out on holiday or buried in a launch.
Prefer plain words in meetings until everyone shares a definition. Jargon is fine after that. Before that, jargon is just a way to lose the people who will actually act on the analysis.
Keep a short change log for metrics, pipelines, and critical dashboards. Future you will need the date a definition shifted, and you will not find it in a year-old screenshot buried in a slide archive.
Measure one concrete thing that proves the new approach beats the old habit: fewer reconcile hours, faster ticket answers, lower duplicate rates, or fewer “which number is right” threads per month.
Resist boiling the ocean. One domain, one partnership, one metric strip, or one retrieval evaluation set is enough to learn. Expansion is easier after you have a win people can point at without squinting.
Document the messy edge cases in the open. Hidden footnotes become tribal knowledge, and tribal knowledge becomes an outage when the only person who remembered the footnote changes teams.
Write examples from your own workplace. A named dashboard fight teaches more than a generic industry claim, and it keeps the post useful when the logos on the architecture slide change again next year.
If two teams argue about a number, put both definitions on one page with owners and timestamps. Clarity beats a forced compromise that nobody trusts enough to use in a real decision meeting.
Sources
- https://cloud.google.com/bigquery
- https://www.snowflake.com
- https://www.databricks.com
- https://aws.amazon.com/s3/
Merch on Etsy
Open store →
Y Hat Mouse Pad, Data Science Mouse Pad, Analytics Mouse Pad, Y Pred Mouse Pad
View on Etsy →
Naive Bayes Mouse Pad, Data Science Mouse Pad, Analytics Mouse Pad, Bayesian Mouse Pad
View on Etsy →
Mute Mouse Pad, Data Science Mouse Pad, Analytics Mouse Pad, You're On Mute
View on Etsy →
You're On Mute White Ceramic Coffee Mug, Data Science Mug, Data Mug, Analytics Mug, Statistics Mug, Programming Mug, Gift Mug, Coffee Mug
View on Etsy →