Topic 3 — How data flows in a company

Goal: Understand where data comes from and where analysts get it.

Lesson 3.1 — Where data is born

It's Nadia's first week as a Data Analyst at Perch, the online furniture store, and Marcus from marketing drops by her desk with a question: "How are sign-ups doing this month?" Simple enough. Then she goes looking for the data and hits her first surprise — there's no single "sign-ups" file anywhere. The information she needs was created in five different places, by five different things happening, and none of them were built for her.

That's the first thing to understand about data: it doesn't start life in a tidy spreadsheet waiting for you. It gets born the moment something happens in the business.

Walk through a normal hour at Perch. A customer buys a desk, and the website saves a new row recording that order: who, what, when, how much. A different shopper just browses three sofas and leaves; the site logs each page they viewed and each button they clicked. That clickstream is event data (also called behavioral data) — a record of what people did, not what they bought. A support agent closes a ticket about a wobbly shelf, and that conversation gets stored too. Stripe, the payment processor, records the actual charge on its own servers. The email tool logs who opened this week's newsletter. Google Ads tracks which ad someone clicked before they reached the site.

Six things happened. Six separate records, in six separate systems.

Nobody collects data for you to analyze. It piles up as a by-product of the business running, and your job is to go find it where it landed.

Lesson 3.2 — Many sources, one defining headache

Each of those systems — the website's order database, the support tool, Stripe, the email platform, Google Ads — is a data source: a place where some slice of the truth about Perch lives.

Here's the reality that defines a big part of the job, and the thing Nadia is bumping into on day one: the data lives in many separate places, and almost no interesting question can be answered from just one of them.

Marcus's "how are sign-ups doing?" sounds like one number. But to answer it well, Nadia has to know how many people signed up (the app's user records), how many came from the ad campaign Marcus is paying for (Google Ads), and whether any of those new sign-ups actually bought something (the orders data, and the Stripe charges to confirm the payment cleared). Four sources for one honest answer.

Nadia, careful by habit from her retail days, writes herself a note: before I pull anything, list which systems the answer is hiding in. That instinct — mapping the question to its sources first — is what separates a useful answer from a fast wrong one.

Lesson 3.3 — Tables, and the IDs that tie them together

So where does most of this data actually sit? Not in a spreadsheet. In a relational database — software built to store information in an organized, searchable way, and to do it reliably even when there are millions of records.

A relational database organizes everything into tables. A table looks like a spreadsheet tab: rows and columns. But each table holds exactly one type of thing.

• A customers table — one row per customer.
• An orders table — one row per order.

The columns are the fields, the attributes of each thing: in customers you might have customer_id, name, email, signup_date. Each row is one individual record — one real person, with their own values in each column.

Now the part that makes databases powerful. How does Perch know which customer placed which order? Through shared IDs.

Every customer gets a unique customer_id in the customers table. That column is the table's primary key — the value that uniquely identifies one row, never repeated, never blank. Think of it as the customer's permanent badge number.

The orders table also has a customer_id column. But over there it does not identify the order; it points back to whoever placed it. A customer_id that lives in another table and references a primary key elsewhere is called a foreign key.

Table	customer_id is the...	Meaning
customers	primary key	uniquely identifies this customer (unique, never blank)
orders	foreign key	points back to the customer who placed the order (can repeat)

The same customer can have ten orders, so their customer_id shows up ten times in orders — that repetition is fine and expected for a foreign key. This shared-ID link is exactly what lets Nadia answer "how much has this customer spent across all their orders?" — and in a few topics, when she learns to join tables in SQL, this is the wiring that makes it work.

Lesson 3.4 — The warehouse, and the pipelines that fill it

Nadia now sees the problem clearly. The answer Marcus wants is scattered across a database, Stripe, an email tool, and an ad platform. Is she really supposed to log into five systems and stitch them together by hand every time?

No. And the reason is a system called a data warehouse.

A data warehouse is a large database whose entire job is analysis. Companies copy data out of every source — the app database, Stripe, the email tool, Google Ads — and load it all into this one central place. When an analyst says "I pulled it from the warehouse," this is the one-stop shop they mean. At Perch the warehouse runs on Snowflake; you'll also hear BigQuery (Google's) and Redshift (Amazon's) in job ads — same idea, different vendors.

The data doesn't walk over by itself. The process that extracts data from each source, reshapes it into clean consistent tables, and loads it into the warehouse is a pipeline, usually called ETL (extract, transform, load) or, in modern cloud setups, ELT (extract, load, then transform inside the warehouse).

And here's the part that matters for how Nadia's job actually works: she doesn't build those pipelines. Tom, Perch's data engineer, owns them. When the warehouse is missing a column or a number looks wrong at the source, Tom is who Nadia goes to. The pipeline is the engineering team's territory; the warehouse is where the analyst's work begins.

The data engineer builds the road into the warehouse. The analyst drives on it.

One more thing explains why the warehouse exists at all. The live app database that runs Perch's website is tuned for one job: handling the business in real time — saving an order in milliseconds, never losing a transaction. That kind of system is called OLTP (online transaction processing). A warehouse is OLAP (online analytical processing), tuned instead for big questions across millions of rows. Running Nadia's heavy "total revenue by month for two years" query against the live store could slow checkout for real shoppers. So analysts query the warehouse, a safe copy built for exactly that, and leave the live database alone.

Lesson 3.5 — The three ways an analyst actually gets data

All of that is the plumbing. In practice, on any given Tuesday, Nadia pulls data in one of three ways.

1. A spreadsheet or CSV export. Someone hands her a file, or she clicks "export" in a tool. It's the simplest path and fine for a quick look, but it has two real weaknesses: the files are usually small, and they're a snapshot — the moment Stripe processes one more order, the export is stale. Great for a one-off; dangerous as a habit.

2. A SQL query against the database or warehouse. Nadia writes a query that asks the warehouse a precise question and gets exactly the rows she wants back. This is the professional default, and the reason is that it's repeatable: she saves the query, reruns it next month, and gets fresh numbers with no copy-paste. It also handles data far too big for any spreadsheet. This is why SQL is the next big thing she'll learn (Topic 5).

3. A BI tool connected to the warehouse. Tools like Tableau, Looker, or Power BI sit on top of the warehouse and turn queries into live dashboards. Once Nadia builds one, Marcus can check "how are sign-ups doing?" himself, any morning, without bothering her — the dashboard refreshes straight from the warehouse (Topic 9).

Notice the trade-off running through all three: the easier it is to grab, the smaller and staler it tends to be; the more it connects to the warehouse, the fresher and more repeatable it gets.

Worked example — Tracing one question to its data

Marcus comes back with a sharper question: "Of the people who signed up from last month's Google Ads campaign, how many bought something — and how much did they spend?" Watch Nadia trace it all the way down.

First she maps the sources. Sign-ups live in the app's users records. The ad attribution (who came from that campaign) lives in Google Ads. The purchases live in the orders table, and the confirmed payments in Stripe. Four sources, one question — exactly the headache from Lesson 3.2.

Next she checks where these meet: the warehouse. Tom's ETL pipeline has already copied all four sources into Snowflake overnight, each as clean tables. So Nadia never touches Google Ads or Stripe directly — she works from the warehouse copies.

Now the keys do their job. The users table has customer_id as its primary key; the orders table carries customer_id as a foreign key, pointing back. That shared column is what connects a sign-up to that same person's purchases — without it, "did this signer-up buy something?" would be unanswerable.

Finally she picks her method. This is a repeatable question Marcus will ask again next month, and it spans tables, so a one-off CSV export won't do. She writes a SQL query against the warehouse and saves it to rerun for fresh numbers. If Marcus starts asking weekly, she'll promote it into a BI dashboard he can read himself.

One vague business question, traced through sources, warehouse, keys, and method, into one clean answer. That trace is the skill this topic exists to give you.