Overview
Analytics engineering sits between data engineering and analysis: transform data already in the warehouse. dbt is the tool. This tab runs compiled SELECT, not `dbt run`.
On this page7 sections
The idea
Raw data lands in the warehouse looking nothing like a dashboard. Column names disagree. Types are sloppy. Cancelled orders sit next to paid ones. Someone has to turn that mess into tables analysts can trust. That job is analytics engineering.
Data engineers move data into the warehouse: extract from apps, load into tables. Analysts ask questions of clean tables and ship reports. Analytics engineering sits between those two jobs. You transform data that is already in the warehouse, using versioned SQL, tests, and documentation.
The usual pattern is ELT: extract, load, then transform. Older ETL tools cleaned data before it reached the warehouse. ELT loads first, then transforms with the warehouse engine. dbt (data build tool) is the tool most teams use for that transform step. You write SELECT statements. dbt compiles them, runs them in order, and tests the results.
Why this exists
Picture an e-commerce warehouse with one raw orders table. Analyst A filters to paid rows and calls user_id the customer. Analyst B keeps cancelled rows and sums order_total as revenue. Two dashboards, two numbers, one argument in the weekly meeting. Nobody can point at a single SELECT that defines revenue.
Without a shared transform layer, every report re-cleans the source. A column rename breaks twelve notebooks. A duplicate order_id silently doubles revenue. Trust in the data team erodes because the numbers will not sit still.
A data engineer who only loads tables has not finished the job. Analysts should not each invent a staging layer. Analytics engineering owns one cleaned grain, one set of tests, and one place to change a name.
Picture this
Think of a warehouse receiving dock versus a shop floor. Data engineering unloads the trucks (raw tables). Analytics engineering puts goods on labeled shelves (staging and marts). Analysts pick from the shelves, not from the loading bay.
Load lands raw tables. Analytics engineering transforms them. Analysis queries the trusted layer.
- Extract: copy data out of an app, file, or API.
- Load: land it as a raw table in the warehouse. You do not own that table's shape yet.
- Transform: write SQL models that rename, cast, filter, join, and aggregate into tables analysts can trust.
- Test and document: fail the build when a key is null or duplicated. Name the grain so the next person is not guessing.
dbt handles the transform, test, and document steps. It does not extract from Shopify or load a CSV. Other tools (or scripts you already wrote in earlier tracks) do extract and load. dbt starts after the raw table exists.
| Role | Owns | Typical output |
|---|---|---|
| Data engineering | Pipelines into the warehouse | Raw orders, events, dumps |
| Analytics engineering | SQL models, tests, docs | stg_orders, fct_revenue |
| Analysis | Questions and reports | Dashboards, ad hoc SQL |
Here is a slice of a raw orders table, the kind of source an analytics engineer starts from:
Input: raw orders as loaded, not yet renamed or filtered.
| order_id | user_id | order_status | order_total |
|---|---|---|---|
| ORD-1 | U-9 | paid | 120.50 |
| ORD-2 | U-3 | cancelled | 40.00 |
| ORD-3 | U-9 | paid | 88.25 |
A small example
Run the example below in this tab. Read the input, follow the code, then check the output matches what you expect.
-- Raw source, as loaded. Fine to inspect. Not a contract analysts should reuse.
SELECT order_id, user_id, order_status, order_total
FROM orders
LIMIT 8;A staging-shaped model does not join or aggregate. It picks columns, renames them to the team's language, and leaves business grain for marts. user_id becomes customer_id. order_total becomes amount. The SELECT below is compiled SQL you can run in this tab.
-- Staging-shaped SELECT (compiled SQL, no Jinja)
SELECT
order_id,
user_id AS customer_id,
order_total AS amount
FROM orders
LIMIT 8;Output: same rows, names the rest of the project can ref.
| order_id | customer_id | amount |
|---|---|---|
| ORD-1 | U-9 | 120.50 |
| ORD-2 | U-3 | 40.00 |
| ORD-3 | U-9 | 88.25 |
Common beginner questions
Is analytics engineering the same as data engineering?
No. Data engineering gets data into the warehouse and keeps pipelines healthy. Analytics engineering models what is already there. Many people do both, especially on small teams. The skills are different: load versus SQL models and tests.
Why transform in the warehouse instead of before load?
Warehouses are good at SQL. Keeping transforms next to the data means you version them, test them, and reuse them. Cleaning in a one-off script before load hides the logic where the next person cannot find it.
Do I need dbt, or is a folder of SQL enough?
A folder of SQL can work for one person. dbt adds dependency order (ref), tests, and documentation so a team can change a model without breaking twelve others. This track teaches that mental model. The CLI still lives on your laptop.
This tab is not `dbt run`
The SQL editor runs compiled SELECT. There is no dbt project, no profiles.yml, and no docs site here. Jinja {{ }} does not execute. You write the SQL that would remain after compile.
Before you start
This track assumes you completed SQL & Warehousing. Staging models are SELECT, JOIN, and GROUP BY you already practiced, now named and tested as a project.
What comes next
You now know the job. Next you will see how a dbt project is laid out: models/, tests/, sources.yml, and dbt_project.yml, plus why staging and marts live in different folders.
Practice
Run Sample to see a staging-shaped SELECT. Then complete the exercise: from orders, return order_id, customer_id, and amount, limited to 20 rows. Write compiled SQL only.
Practicals · load into the editor
After you read the theory, run these in the pane on the right. They execute in this tab, no cluster.