Analytical Data Store

There was a time when a company had exactly one database. It answered when a user opened a document, and it gained a row whenever someone paid. Anyone who wanted a number had nowhere else to go, so they pointed their aggregate queries at that same database.

The trouble is that serving a product and analyzing it are opposite jobs. Serving means finding one row fast and handing it back, a few hundred thousand times a day — the document a user just opened, returned in 0.05 seconds. Analysis means sweeping millions of rows at once to produce a total. A single query that groups four million payment records by plan to get monthly revenue can run for three minutes, and for those three minutes every user screen waiting on the same database slows down with it.

So companies of that era all had some version of the same rule: heavy queries only run at night, when nobody is around. Theo would start an aggregation in the afternoon, watch a response-time alert fire, and kill the query in a hurry — a few times a month, every month. Mia asked for a number she needed that day and got it the next morning. Running analysis meant putting the product at risk, and in that arrangement people quietly run less analysis.

There was a second problem. The data a company works with does not start out in one place. Payment records sit with the payment processor, records of which buttons users clicked sit in an event tracking tool, and support conversations sit in a help desk. An event tracking tool is software that writes one line for every user action — who did what, and when. Each of these systems does its own job well. But a question like "what screens did this customer visit in the month before they paid" needs all three joined together, and there is nowhere to join them.

Without that place, a person downloads three exports and stitches them together by hand in a spreadsheet. Every person stitches a little differently — one drops accounts with no matching payment row, another keeps them as zeros — so two answers to the same question rarely match. And when the question comes back a month later, the stitching starts over from nothing, because the method lived in someone's head rather than anywhere a colleague could pick it up.

An analytical data store answers both problems in one move. You stand up a space separate from the database serving the product, then copy the scattered sources into it. Heavy queries no longer touch anything a user is waiting on, and records that lived in three systems can finally be connected inside one space. The store is also built the other way around from a product database — organized to sweep millions of rows rather than to fetch one — so the same aggregation that took three minutes often finishes in seconds.

Within data and analytics, this sits in the middle of the processing group. The flow that pulls data out of each source, reshapes it, and moves it is the data pipeline. The container it lands in is the analytical data store. Keeping the values inside trustworthy is data quality. The three are chained, so a store built without the other two becomes a large warehouse full of empty and mismatched values.

The terms grouped under an analytical data store fall into three sets: what shape the container takes (form), how the inside of that container is split into ordered layers (layers), and how the tables in the final layer are designed (modeling).

Form divides the container itself, and there are three options. What separates them is how much cleaning happens before data goes in.

1. Data warehouse — holds data already shaped to a structure decided in advance. Because you settle which column holds which value before loading, anything inside is ready to query. Payment records, for instance, get normalized into four columns: date, account, plan, amount. The cost is that a new column at the source, or a changed format, has to be handled before the data is accepted.
2. Data lake — stacks raw material in whatever form it arrived. Server log files, images customers uploaded, responses from an outside service whose fields vary run to run: all of it lands as is. Nothing has to match a schema on the way in, but every read carries the work of figuring out what the values mean.
3. Data mart — a small store carved out for one team or one subject. Split off the per-account payment status that sales looks at, and Sofia reads that single table instead of searching the whole store.

These are not three choices to pick between. They stack. The common arrangement keeps every raw source in the lake, promotes the parts worth analyzing into the warehouse, and carves frequently used slices into marts.

Layers divide the inside of the store, and there are three of them. What separates them is distance from the original.

1. Raw layer — the first layer, holding exactly what arrived from the source. Typos stay, blank cells stay, nothing gets corrected. The reason to leave it untouched is that when a cleaning rule turns out to be wrong six months later, you need somewhere to go back to.
2. Staging layer — holds the middle of the cleaning process. Date formats get unified here, an account that arrived as two rows gets merged into one, and rows with a missing amount get filtered out.
3. Mart layer — the last layer, shaped so an analyst can query it directly. This is where a table lives with one row per account and columns for signup date, plan, lifetime payment, and last login.

The point of splitting into layers is to do each calculation once. Put the rule for revenue into one table in the mart layer and the dashboard, the weekly report, and the investor deck all read the same table. Skip the layers and let everyone calculate from the source, and the same month's revenue shows up three different ways in one meeting. Layers also make failures easy to locate. When a number looks wrong, you check whether the raw layer received today's rows, then whether the staging rules dropped more than usual, then whether the mart table was rebuilt — instead of re-reading one long query and guessing.

Modeling decides the shape of the tables in that last layer, and there are three terms here.

1. Dimensional modeling — the practice of splitting tables into a measurement side and a descriptive side. The goal is not to save storage but to let someone who has never seen the data slice it from whatever angle they need.
2. Fact table — the central table where measurements accumulate, one row per event. One payment is a row, one document creation is a row, and the columns hold values you can add up, like amount or count.
3. Dimension table — the table holding the attributes you slice those measurements by. An account table carries plan, acquisition channel, and industry; a date table carries weekday and quarter. "Show me revenue by plan" turns into joining the account table to the fact table and grouping on the plan column.

The three sets get decided in order: pick the form, split the inside into layers, then model the tables in the last layer. Skip ahead and build only the container, and raw material piles up inside while everyone who needs it goes on cleaning the same data over and over.

"Did you pull that number straight from the production database? It's in the warehouse through yesterday."

The same value is available in the analytical store, so leave the product side alone.

"Leave the original in raw and do the deduplication in staging."

Keep the source layer untouched so the cleaning rule can be changed later.

"That table is a fact table, so one row is one payment. Count it as is and accounts get double counted."

Aggregating without checking what a single row represents inflates the number.

Pagedeck built its first store right after the Series A. It started with a question from Grace: what did teams do in the month before they converted from free to paid? Answering it meant joining records from the payment processor to records from the event tracking tool at the account level, and the two systems referred to the same account by different identifiers. Mia exported both sides and matched them by hand, which took two days. Three months later a similar question came in and cost the same two days again.

The day the second request arrived, Theo proposed building a store. Copy both sources into the first layer every night, resolve the account identifiers in the middle layer, and put a table in the final layer where one row per account carries both payments and behavior. It took three weeks to build. After that, questions of the same shape stopped being two-day projects and became a twenty-minute query, and when Mia came back with a different angle the answer came off a table that was already joined.

The most common mistake is treating "we have a store" and "our data is usable" as the same milestone. Connect the tooling and copies start running that day, table counts climb visibly, and it all looks like progress. But a store is only a container — what arrives inside, and in what condition, has to be built separately. Without the flow that moves data, the rules that clean it, and the checks that flag values gone wrong, the container faithfully accepts the mess. Left alone it turns into a warehouse of three hundred tables where nobody can say which one is correct. At that point people go back to pulling from the sources themselves, and the store keeps costing money with no one reading from it. The way out of that state is unglamorous: pick the ten tables people actually query, document what each row means, and delete or hide the rest.

The other frequent misreading is that a lake is a strictly better warehouse. One accepts anything and the other demands a schema, so the less restrictive option looks like the newer and stronger one. The real difference isn't better or worse, it's when you pay the cleaning cost. A warehouse charges once on the way in and readers get data ready to use. A lake charges nothing on the way in and every reader pays on the way out. Five analysts interpreting the same raw files pay that cost five times, and their interpretations drift apart until one question has several answers. A lake with nothing built on top of it ends up easy to load and unread. Most companies run both: keep originals in the lake so values that turn out to matter later are not lost, and promote what gets analyzed often into the warehouse.