Data Foundations

"The dashboard is broken — looks like the pipeline failed, but the warehouse is fine, so it's probably an ELT issue."

That sentence has four pieces of vocabulary that have to mean something for the meeting to make sense. None of them are obvious if your career started before the modern data stack landed, and none of them are taught in business school. They're just the words people use now.

This guide gives you those words. By the end you'll be able to read the sentence above, follow a job description for an analyst role without translating, and roughly understand what's happening when a data team says they're "rebuilding the model," "switching from ETL to ELT," or "moving to a lakehouse." It is not a guide to doing data work. It is a guide to understanding the conversation around data work — which, for a business operator, is most of what matters.

We'll move in five steps and then synthesize: how data gets where it needs to go, where it lives once it gets there, how it's shaped to be useful, the dominant pattern that shape takes, and the current toolset everyone is using. The synthesis at the end traces a single analytical question end-to-end so the pieces stop being a list and start being a system.

§1 How data gets there: pipelines, ETL vs ELT Foundational

Production systems — the app that processes orders, the CRM (Customer Relationship Management system) that holds your sales notes, the marketing tool that captures sign-ups — each produce data as a side effect of doing their actual job. None of them are built for someone to come in later and ask "how did we do last quarter." That's a different job, done in a different place, by different people. The bridge between the two is a data pipeline — any automated process that moves data from where it's produced to where it's used.

Pipelines run on schedules (every hour, every night), on triggers (a new file arrives in a cloud storage bucket — a generic file-storage location in the cloud, like Amazon S3), or continuously (a stream of events flowing in real time). When a dashboard is "broken," nine times out of ten it's because the pipeline behind it didn't run, ran late, or ran but produced garbage. Pipeline reliability is the unsexy foundation under every "data-driven" claim a company makes.

The classic pipeline pattern is ETL — Extract, Transform, Load. A data engineer extracts raw data from the source, transforms it (cleans, joins, aggregates) in some intermediate processing tool, then loads the cleaned result into the destination. Under ETL, transformation happens before the data lands.

The newer pattern, ELT — Extract, Load, Transform — reverses the order. Land the raw data in the destination first, then transform it inside that destination. The reason this reversal happened: cloud data warehouses got fast and cheap enough to handle the heavy transformation work themselves. Data-loading services like Fivetran and Airbyte do the extract and load; transformation tools like dbt (covered in §5) do the transform — all inside the warehouse.

The shift sounds like a technical detail. It isn't. Under ETL, the transformation logic lived inside engineering tools that only data engineers could read, write, or review. Under ELT, the transformation lives in SQL (Structured Query Language — the query language for relational databases) inside the warehouse, which analysts can read, write, and review. ELT changed who builds data pipelines, and produced an entirely new role — the analytics engineer — covered in §5.

Two things worth holding onto. First, ELT is not strictly better than ETL. ETL still wins when data has to be transformed before it lands — for privacy reasons (you can't store raw PII, or personally identifiable information), compliance reasons (regulators want the cleaned version), or volume reasons (the source data is too large to land raw). ELT assumes the warehouse can afford to hold and process raw data, which is a cost choice as much as a technical one. Second, "the pipeline ran" and "the data is correct" are not the same statement. Pipelines can succeed technically while delivering wrong numbers — a missed source, a silent change in the source's schema (the column structure of a table), a bug in the transform. Data quality is its own discipline on top of pipeline reliability.

§2 Where it lives: warehouse, lake, lakehouse Foundational

Production databases — the ones that run the app — are tuned for fast, small writes. Take an order, record a click, save a session token. Asking them to run a quarter-wide analytical query ("how many enterprise customers did we churn last year, by region, by product?") usually slows the app down or times out. So companies set up a separate database for analysis. That separate database is the data warehouse, and it is downstream — a copy of the production data, cleaned and joined and shaped for analytical questions, refreshed on a schedule.

When someone says "pull this from the warehouse," they mean the analytical database where cleaned, joined, ready-to-query data lives. Snowflake, BigQuery, Redshift, Databricks — all data warehouses, or close cousins. The warehouse is the analyst's home turf. SQL is the language. The numbers there can lag the live app by minutes or hours, depending on how often the pipelines refresh. Acting on warehouse data as if it's real-time is a common mistake that bites at month-end, when finance is reconciling and the warehouse is showing yesterday's truth.

A data lake is something else. It is a storage system that holds raw data of any shape — structured tables, JSON (a common text format for structured data; pronounced "Jason"), log files, images, anything — without forcing it into a schema first. Cloud storage buckets like Amazon S3, Azure Blob, or Google Cloud Storage are the substrate. The warehouse asks "what shape is this data?" before it can store anything. The lake says "dump it in, figure the shape out later." That flexibility is the whole appeal — and the whole risk. Lakes scale cheaply and accept anything; without discipline they become "data swamps" where nobody knows what's in there.

The two coexist. Raw data lands in the lake. Cleaned, modeled data lives in the warehouse. Treating the lake as a substitute for the warehouse — querying raw logs directly for daily reporting, skipping the modeling step — is how a lake stops being useful. The lake is the staging area; the warehouse is the showroom.

A lakehouse is the industry's attempt to stop making teams pick between the lake's flexibility and the warehouse's reliability. The pitch: one storage layer that does both — warehouse-style structure and querying on top of lake-style cheap, flexible storage. Open table formats — software conventions that let raw files behave like database tables — such as Delta, Iceberg, and Hudi are what make the pattern work technically. Databricks pitches the lakehouse aggressively; Snowflake has built its own answer to it. A team typically moves toward a lakehouse when their lake has grown big enough that they need warehouse-style queries on it but they don't want to copy everything into a separate warehouse to get them. Whether the lakehouse delivers on that promise depends heavily on which vendor's version you're using — Databricks and Snowflake mean meaningfully different things by the word, and the underlying table formats compete with each other. Treat "lakehouse" as a direction the industry is moving in, not a settled standard.

Worth noting before moving on: in practice, modern ELT setups often land raw data directly in the warehouse — in dedicated "raw" schemas — rather than staging it in a separate lake. The lake / warehouse split is becoming a spectrum more than a hard line. The mental model still holds: raw flexibility on one end, query-ready structure on the other. Lake sits at the flexibility end, warehouse at the structure end, lakehouse is a bet on the middle.

§3 How it's shaped: data modeling Foundational

Raw data is not a finished product. The order events landing in the warehouse from the production system look nothing like the report finance wants. The website clickstream landing in the lake looks nothing like the funnel conversion analysis marketing needs. Between raw and useful is a layer of work called data modeling — the work of deciding how data should be shaped so the questions people want to ask can actually be answered.

Modeling is deciding which tables exist, what columns they hold, how they connect, and at what level of detail (called grain) the events are stored. A clickstream table at "one row per click" tells a different story than the same data rolled up to "one row per session per user per day." Both are valid; which one is right depends on the questions the business needs to answer.

Models are the layer between raw data and decisions. Two companies with identical source data can have completely different analytical capabilities depending on how well the data is modeled. Most "we don't trust our data" problems inside a company are not data quality problems — they are modeling problems wearing a costume. The numbers disagree across reports because three different analysts built three different paths through unmodeled data. Modeling fixes that by defining the path once, in one place, so every report downstream uses the same definitions.

Good modeling is invisible. The data just answers the question and the analyst moves on. Bad modeling is loud: five-join queries that take an hour to write, two analysts producing different numbers for the same metric, dashboards that worked last quarter and broke this quarter because something upstream changed shape. Anywhere you hear analysts and engineers fighting about why a report is hard to build, modeling is usually the real subject of the argument.

The discipline most companies underinvest in is treating modeling as ongoing work. The business changes — new products, new metrics, new questions, new acquisitions — and the model has to keep up. Stale models silently make every downstream report a little wrong. A model built three years ago for a smaller, simpler version of the business is doing real damage now; nobody just sees the damage because the queries still run.

§4 The dominant pattern: star schema Building

There are many ways to model data. One pattern dominates analytical workloads, and has for thirty years. It is the star schema: one central "fact" table surrounded by "dimension" tables, laid out so the relationships, drawn on a whiteboard, look like a star.

A fact table holds the events or transactions the business wants to measure — orders, page views, payments, sign-ups. Fact tables are usually long and skinny: millions or billions of rows, mostly numbers and foreign keys (a foreign key is a column in one table that points at a row in another — for example, an orders table holds a customer_id column that refers to the row in customers). "How many orders did we ship last week" is a fact-table question. The fact table is where the business's measurable activity actually lives, and the quality of every downstream metric — revenue, conversion, retention — depends on it capturing the right events at the right grain. Get the grain wrong and the whole reporting layer drifts.

A dimension table describes the people, products, dates, places, and categories the fact table refers to — the context around the events. Dimension tables are usually wide and short: many columns describing each entity, but far fewer rows than the fact table. The customer dimension might have fifty columns describing each customer — segment, region, tier, signup date, lifecycle stage — and one row per customer. "Orders by customer segment by month" is a question that requires the orders fact table to join (combine two tables on a shared key) to the customer dimension and the date dimension.

The star schema dominates because it makes the trade-off most businesses want: a little redundancy in storage in exchange for fast, intuitive queries. BI (Business Intelligence — the tooling category for dashboards and analytical reporting) tools like Looker, Power BI, and Tableau are built on the assumption that the underlying data is in this shape. A well-built star schema is the difference between an analyst answering a stakeholder's question in two minutes or two hours.

Two refinements worth knowing. First, the star schema is not a one-size-fits-all template. Graph problems (who knows whom), deep hierarchies (this org-chart node owns these other nodes that own those other nodes), real-time event streams — none of them fit cleanly into a star schema. Forcing them into one produces queries that work but read like a maze. Second, dimensions change over time. A customer's segment, a product's price, an employee's manager — all of these change. Tracking those changes is a discipline called slowly changing dimensions, and it is what separates a usable warehouse from one that quietly rewrites history every time the dimensions refresh. A report that shows last year's revenue using this year's segment definitions is technically running queries against good data while producing an answer nobody should trust.

If you take one thing from this section: when an analyst says "I'm building a star schema for this," they are committing to a specific shape — central events table, descriptive context tables, joinable on shared keys — that the downstream BI tool will rely on. The shape is doing real work.

§5 The current toolset: modern data stack Building

The phrase you will hear in every data-team job posting from the last several years is modern data stack — a loose set of cloud-based tools that became the default way to build analytics infrastructure in the late 2010s. The canonical lineup: Fivetran or Airbyte for loading, Snowflake or BigQuery or Databricks for storage, dbt for transformation, and a BI tool like Looker, Mode, or Hex for analysis on top. The specific names rotate as vendors fight for share; the pattern has been stable for years.

Two pieces of the stack are worth understanding well because their names get used as vocabulary.

Snowflake is a cloud-native data warehouse whose key architectural move was separating storage from compute — meaning you pay for the two independently and can scale each on its own (in this context, "compute" is the processing power needed to run queries — separate from the storage of the data itself). Before Snowflake, scaling analytical workloads meant paying for unused capacity all the time; you sized for peak load and ate the cost during the rest of the day. The split made it possible to keep large data cheaply and only spin up serious compute when someone actually ran a query. That economic shift is what made the modern data stack viable at scale. The pattern spread to every major cloud warehouse and became the new default. When someone says "we use Snowflake," they almost always mean the warehouse — though Snowflake Inc. now sells data sharing, an app platform, and AI tooling, and will treat all of it as "Snowflake" in their marketing.

dbt (which stands for "data build tool" and is always lowercase) is a transformation tool that lets analysts write transformations as SQL files, run them in dependency order, and test the results — all version-controlled, the same way software code is tracked with tools like Git so every change is reviewable and reversible. dbt did to data transformation what version control did to software engineering: turned a brittle, hard-to-review process into something teams can collaborate on with normal engineering hygiene. The role of "analytics engineer" — an analyst who works like a software engineer — basically did not exist before dbt made it possible. dbt is a tool, but it is heard so often in data conversations that it works almost like vocabulary — "the dbt project" is the folder of SQL transformations the team owns, "the dbt models" are the individual transformations inside it, "is it in dbt yet" asks whether a piece of logic has been moved from ad-hoc SQL into the managed transformation layer.

The modern data stack matters less as a list of tools than as a shift in who builds analytics. The old stack required data engineers for everything; the modern stack lets analysts own the transformation layer in SQL. That changed who gets hired, what they do, and how fast data work moves. A business operator does not need to use dbt to benefit from understanding what it changed — the analytics org structure at most companies built after 2018 was designed around the assumption that this stack exists.

The term itself is already being renamed — "composable data stack," "AI-native data stack," whatever's next. The tools and labels rotate; the underlying pattern of cloud warehouse + ELT + SQL transformation has been the stable part. Whatever name the next era picks, it will almost certainly describe the same shape.

§6 Putting it together: how a question becomes an answer Building

The five sections above are pieces. The system is what happens when they run together. To make the system visible, walk through a single question end-to-end.

The question: "How many orders did we ship to enterprise customers last quarter, broken out by month and by product line?" This is a question a VP would ask in a meeting. The analyst on the receiving end has maybe an hour before the next meeting.

The pipeline. An ELT pipeline ran overnight. Fivetran extracted yesterday's order records from the production database and loaded them into the Snowflake warehouse, untouched. Another connector — a configured integration that copies data from a source system into the warehouse on a schedule — loaded the customer table (segment, tier, region) from the CRM. Another loaded the product catalog from the e-commerce platform. All of it landed raw.

The warehouse. All three sources sit in the warehouse as raw tables — written as raw.orders, raw.customers, raw.products, where the raw. prefix is a naming convention that says "this is untransformed, do not query directly for reporting." The raw orders table has internal IDs, status codes nobody remembers, and timestamps in UTC (Coordinated Universal Time — the global time standard most production systems record by default).

The model. Inside the warehouse, dbt models transform the raw tables into a star schema. The orders fact table now has one row per shipped order, with foreign keys to the customer dimension and the product dimension, plus the shipment date. The customer dimension has been cleaned: segment classified (Enterprise / Mid-Market / SMB), region resolved, edge cases handled. The product dimension has product line mapped to each SKU (Stock Keeping Unit — the per-product code that uniquely identifies a specific item in inventory). The date dimension is pre-built: every date with its month, quarter, year, fiscal period.

The query. The analyst writes ten lines of SQL: join the orders fact table to the customer dimension on customer ID, filter to enterprise-segment customers only (in SQL that reads roughly as WHERE segment = 'Enterprise'), filter to last quarter using the date dimension, group by month and by product line, count distinct order IDs. The query runs in three seconds because the warehouse separated storage from compute and the schema was built for exactly this shape of question.

The answer. A clean table comes back. The analyst pastes it into the BI tool, hits a chart button, and has a stacked bar by month for the meeting.

That is the whole system working as intended. None of it was magic. Every piece — pipeline, warehouse, model, query, BI tool — had to be in place and working. When any one of them is broken or stale, the analyst's hour gets eaten by detective work instead of analysis. The work the data team does to keep all five pieces healthy is most of what they do day to day.

Now reread the opener: "The dashboard is broken — looks like the pipeline failed, but the warehouse is fine, so it's probably an ELT issue." The translation is: the dashboard depends on a pipeline. The pipeline didn't run successfully (most likely the transform step failed, since the warehouse itself is healthy). Therefore the data the dashboard is reading is stale or incomplete. That's a fifteen-second diagnosis any data team has run a thousand times — and now it should read like English instead of code.

§7 Data quality, testing, and observability Strategic

Data quality is not a property of a dataset in isolation — it's a relationship between data and its intended use. The same dataset can be high quality for one purpose and dangerously low quality for another. That said, the industry has converged on five dimensions that capture most of what matters: completeness (are all expected records and fields present?), accuracy (do values reflect reality?), consistency (does the same fact have the same value across systems?), timeliness (is the data fresh enough for its use case?), and uniqueness (are records deduplicated correctly?). A data quality audit that doesn't specify which dimension it's measuring is too vague to act on.

The most expensive property of bad data is that it propagates silently. A source system that starts logging a user ID as a string instead of an integer doesn't announce the change. The ingestion layer that accepts either type doesn't error. The warehouse loads without complaint. The transformation joins on the field — but the join silently drops all the newly string-typed IDs because they don't match integer IDs in the dimension table. The dashboard that displays user counts shows a 12% decline over three weeks. Someone escalates. The root cause is found nine days later by an engineer who happened to check the raw table. This sequence is not unusual; it is the normal failure mode in pipelines without quality checks at each stage.

dbt tests are the most widely deployed mechanism for catching these failures at the transformation layer. The built-in tests cover the most common cases: not_null (a field should never be empty), unique (a field should have no duplicate values), accepted_values (a categorical field should only contain values from a defined list), and relationships (a foreign key should always match a record in the referenced table). These tests run as part of the dbt build process — they don't prevent bad data from entering the warehouse, but they catch it before it propagates into downstream models and dashboards. The discipline is not just writing the tests; it's deciding what to do when they fail: block deployment, alert and continue, or quarantine the affected rows.

The data contract concept addresses the organizational root cause directly. A data contract is a formal interface agreement between a source team (the producer) and the teams that depend on their data (the consumers): here is the schema, here are the guarantees about freshness and availability, here is how we will communicate changes before we make them. When source teams have signed contracts with their downstream consumers, a breaking schema change stops being a surprise at 2am and becomes a managed deprecation with notification and a migration window. The tooling exists; the organizational discipline to require and enforce contracts is the scarce resource.

Without checks — failure propagates silently

✗ ID type changes string → int