Module 1: Your First Dbt Project

What is analytics engineering?

Next →

Description

Analytics engineering is the discipline of transforming, testing, deploying, and documenting data inside the warehouse, applying the same practices a software team applies to its code: version control, automated tests, change review, and documentation that's generated from the code itself, not written separately by someone and out of date within a week. dbt Labs — the company behind dbt, and the one who most contributed to popularizing the term — sums it up like this: an analytics engineer delivers clean data sets to end users, using software engineering practices to maintain a reliable analytics codebase.

Notice the two halves of that sentence, because each one rules out a common misunderstanding. "Clean data sets for end users" rules out the idea that analytics engineering is the same as moving raw data from one system to another — a data engineer already does that. And "software engineering practices" rules out the idea that analytics engineering is the same as writing a dashboard's final SELECT — a data analyst does that. An analytics engineer lives, on purpose, between those two roles: they take data already available in the warehouse (the data engineer's work is already done) and turn it, with versioned and tested SQL, into reliable, documented tables the analyst can consume without worry.

Connection to the module. Lesson 1 showed you the problem — loose SQL, a memorized order, no safety net. This lesson puts a name to the role that solves that problem, and draws the exact boundary with the two neighboring roles you already know from data-engineering-foundations-guide (lesson 2 of its module 1): the data engineer and the data analyst. Everything you build from here on in this guide — versioned models, tests, snapshots, macros, generated documentation — is, literally, an analytics engineer's work, applied to Kiosko's case.

An analogy: the librarian who catalogs, not just the one who lends

Think of a large public library. Someone built the building, the shelves, and the system that receives new boxes of books every week from the publisher — that's the data engineer: they build the infrastructure, without yet deciding which book goes on which shelf or whether the book that arrived is complete. Someone else, a visitor, comes in looking for a specific book for their assignment that week and uses it — that's the data analyst: they consume the already-organized catalog to answer a specific question, without worrying about how it got there.

Between those two there's a third role, less visible but just as necessary: the librarian who catalogs. They receive the boxes that already arrived (they don't bring them themselves), check that every book is complete and in good condition before putting it on the public shelf, decide the classification system (by topic, by author, by date) that lets anyone find what they're looking for without help, and keep a record — who cataloged it, when, under what category — that any other librarian can check later. That librarian is the analytics engineer. They didn't build the building or the pipes that bring in new books. They aren't the one looking for a book for a specific assignment. Their job is the middle layer that turns "a box of books that arrived" into "a reliable, verified, documented catalog" — exactly what dbt is going to give you the tools to do with Kiosko's data.

   Publisher             Data engineer         Analytics engineer      Data analyst
   (generates books) --> (receives the box, --> (catalogs, verifies, --> (searches the
                          builds the             documents, organizes    ready catalog,
                          building and            the shelf)               answers a
                          the pipes)                                       question)

   orders.csv,           bronze/silver/         dbt: sources, staging,   store revenue
   events.jsonl           gold pipeline           marts, tests, docs       dashboard
   (Kiosko, already       (foundations)           (this guide)             (outside this guide)
    generated)

Worked example: a day at Kiosko, now with analytics engineering in the mix

You already know, from data-engineering-foundations-guide, the distinction between data_engineer, data_analyst, and ml_engineer. Now that Kiosko has a complete dimensional warehouse (thanks to data-modeling-for-analytics-guide) and is about to version it with dbt, a fourth kind of task shows up in the list for any given day:

# kiosko_roles.py
kiosko_tasks_today = [
    ("Kiosko's nightly orders.csv export from store S02 failed silently last night", "data_engineer"),
    ("Add a not-null test to order_id so a broken export never reaches the warehouse silently again", "analytics_engineer"),
    ("Build the weekly revenue-by-store dashboard for the ops team", "data_analyst"),
    ("Rewrite the hand-run star.sql scripts as a dbt project with ref() between models", "analytics_engineer"),
    ("Document what 'revenue' means in fact_orders so no two analysts define it differently", "analytics_engineer"),
    ("Compare this month's total sales against last month's in a spreadsheet", "data_analyst"),
]

print("=== Kiosko: who owns each task, today ===\n")
for task, role in kiosko_tasks_today:
    print(f"[{role:18}] {task}")

ae_count = sum(1 for _, role in kiosko_tasks_today if role == "analytics_engineer")
print(f"\n{ae_count} of {len(kiosko_tasks_today)} tasks today belong to an analytics engineer.")

What to expect. Running python3 kiosko_roles.py, the output is exactly this:

=== Kiosko: who owns each task, today ===

[data_engineer     ] Kiosko's nightly orders.csv export from store S02 failed silently last night
[analytics_engineer] Add a not-null test to order_id so a broken export never reaches the warehouse silently again
[data_analyst      ] Build the weekly revenue-by-store dashboard for the ops team
[analytics_engineer] Rewrite the hand-run star.sql scripts as a dbt project with ref() between models
[analytics_engineer] Document what 'revenue' means in fact_orders so no two analysts define it differently
[data_analyst      ] Compare this month's total sales against last month's in a spreadsheet

3 of 6 tasks today belong to an analytics engineer.

Look at the pattern behind the three analytics_engineer tasks: none of the three moves data from one system to another (that would be data_engineer), and none of the three answers a specific business question with a final number (that would be data_analyst). All three transform, test, or document the model that already exists in the warehouse — a not-null test, a versioned rewrite of loose scripts, a documented business definition. It's exactly half of today's tasks, and it's exactly the work you're going to do, module by module, for the rest of this guide.

Analytics engineer vs. data engineer vs. data analyst

Data engineerAnalytics engineerData analyst
Works primarily withPipelines, infrastructure, orchestratorsSQL inside the warehouse, dbt, GitDashboards, spreadsheets, BI
Typical inputSource systems (APIs, OLTP databases, files)Raw data already loaded into the warehouseData already transformed and documented
Typical outputRaw, reliable data, in the warehouseClean, tested, documented modelsA report, a dashboard, a decision
This guide's central toolPython + pipelines (foundations)dbt (this guide)Outside this guide's scope
Question it answers"Did the data arrive, complete and on time?""Is this number correct and tested?""What does this number mean for the business?"

This table doesn't replace data-engineering-foundations-guide's — it extends it. The data engineer is still the one who builds the infrastructure that brings orders.csv and events.jsonl all the way to the warehouse. What's new here is the middle layer: once the data is already in the warehouse (bronze, in foundations' language), someone has to turn it into something reliable and documented before an analyst uses it — that "someone" is the analytics engineer, and "something reliable and documented" is, precisely, what dbt gives you the tools to build.

Going deeper: why "software engineering applied to SQL" isn't an empty phrase

When dbt Labs says an analytics engineer applies "software engineering practices," it isn't a marketing line — it points to four concrete practices, each with its own module in this guide:

  • Version control. Every .sql file in your dbt project is plain text, versionable with git like any code file — not a query someone ran once that nobody else can reproduce. You're going to set this up in Kiosko's project in lesson 8 of this module.
  • Automated tests. Instead of trusting that order_id never comes in empty, you declare that rule as a test dbt runs every time, and that fails loudly if something changes — module 4.
  • Documentation generated from the code itself. Instead of a Word document that goes stale within the first week, dbt's documentation is generated directly from the descriptions you write alongside each model, and always reflects the project's real state — module 7.
  • Explicit dependencies and automatic order resolution. It's, again, lesson 1's problem: instead of memorizing what runs before what, you declare the dependency with ref() and dbt computes the order — module 3.

None of these four practices is exclusive to dbt — they're, with different syntax, the same practices any software engineer uses with their application code (Git, unit tests, autogenerated documentation, dependency managers). What dbt did, and what gave this role its name, was bring those four practices to analytical SQL, where for years they lived almost entirely absent.

Common mistakes

Believing analytics engineering is just "advanced SQL" and nothing more. What happens: someone who already writes complex SQL — window functions, recursive CTEs, six-table joins — assumes they're already doing analytics engineering, because the language is the same. Why it happens: SQL is the visible surface of the work, and it's easy to confuse "I master the syntax" with "I apply the full discipline." How to spot it: if your SQL is never in a git repository, never has a declared test, and no one but you can explain what it does without reading the whole query, you're writing advanced SQL — not analytics engineering yet. How to fix it: this lesson's table — SQL is the material, not the discipline; the discipline is the four practices from the "going deeper" section.

Thinking the analytics engineer replaces the data engineer. What happens: someone concludes that, if dbt can transform data, a pipeline that brings orders.csv to the warehouse is no longer needed. Why it happens: dbt is so visible and so powerful at transforming that it's easy to forget it doesn't extract or load anything — it needs the data to already be available. How to spot it: if you try to point a dbt model at a file that never made it to the warehouse, you won't find any dbt error — you'll find that the file simply doesn't exist where dbt looks for it. How to fix it: remember this lesson's analogy — the librarian catalogs the boxes that already arrived; they don't replace whoever brings them. Lesson 3 draws this boundary with technical precision inside ELT.

Assuming "analytics engineer" is a title that only exists at big companies. What happens: someone dismisses the role thinking it only applies to data teams with dozens of people, and that on a small team "there's no need to give it a name." Why it happens: formal LinkedIn titles tend to show up first at big companies, and it's easy to confuse "the title exists there" with "the work only exists there." How to spot it: if on your team someone writes transformation SQL, with no tests, with no version control, and nobody documents what each column means — that analytics engineering work already exists, just without the discipline that makes it reliable. How to fix it: team size changes who does the work (one single person can be data engineer, analytics engineer, and analyst at once), not whether the work exists — Kiosko, in this guide, is exactly that small team.

Exercises

Exercise 1 — Classify five new tasks. For each task, say whether it's data_engineer, analytics_engineer, or data_analyst work:

  • (a) "Write a test that fails if unit_price comes in negative in any row of fact_orders."
  • (b) "Build the pipeline that downloads the app's events.jsonl every night."
  • (c) "Explain to the marketing team why the 'beverages' category grew 12% this quarter."
  • (d) "Write the revenue column's description in schema.yml so the whole team uses the same definition."
  • (e) "Decide whether Kiosko needs to process its events in real time or once a night is enough."
See solution
  • (a) analytics_engineer — declaring a quality rule as a versioned test on an already-transformed model is, precisely, this lesson's "going deeper" practice of "automated tests."
  • (b) data_engineer — building the pipeline that brings raw data to the warehouse is infrastructure, not transformation.
  • (c) data_analyst — interpreting a business metric for another team is analysis, not transformation or infrastructure.
  • (d) analytics_engineer — documenting what a column means directly in the project is, exactly, "documentation generated from the code itself."
  • (e) data_engineer — deciding batch vs. streaming based on the business SLA is an infrastructure decision, already covered in data-engineering-foundations-guide, module 2.

Exercise 2 — The ambiguous case. Read this task: "Rewrite data-modeling-for-analytics-guide's star.sql script — which today runs by hand and unversioned — as a dbt model with ref() toward its dependencies, and add a test that store_id is never null." Is this data_engineer or analytics_engineer work? Justify it using this lesson's criteria, not a hunch.

See solution

It's analytics_engineer. The clue isn't "it's SQL" (a data engineer touches that too, sometimes) — it's that the task operates on data that's already in the warehouse (star.sql already assumes the sources exist) and applies, on top of that existing SQL, this lesson's four practices: version control (rewriting it as a dbt project), explicit dependencies (ref()), and tests. No part of the task brings new data in from a source system — that would be the data_engineer signal.

Exercise 3 — Argue the analogy in your own words. Using the librarian analogy, explain in 2-3 sentences why an analytics engineer "doesn't need to build the library building" to do their job well, but does need someone else to have already built it.

See solution

A reasonable argument: "Kiosko's analytics engineer doesn't need to write the pipeline that brings orders.csv to the warehouse — a data engineer already built that infrastructure, the same way the cataloging librarian doesn't build the library building or the water pipes — but without that infrastructure there would be nothing to catalog: without data already loaded into the warehouse, dbt has nothing to run its models on." The central point is that the two roles are sequential, not interchangeable: one builds access to the raw data, the other turns it into something reliable — and neither can skip the other.

Summary and next step

In this lesson you defined analytics engineering: the discipline of transforming, testing, deploying, and documenting data with software engineering practices, positioned between the data engineer (who brings the raw data) and the data analyst (who consumes it already clean). You saw that boundary applied to six real Kiosko tasks — half of them analytics-engineer work — and the four concrete practices — version control, tests, generated documentation, explicit dependencies — that give substance to the phrase "software engineering applied to SQL," each with its own dedicated module later in this guide.

Before moving on you should be able to: classify any given task among the three roles using an explicit criterion; and name the four software engineering practices an analytics engineer applies, along with the module of this guide where each one is taught in depth.

Lesson 3 takes the analytics engineer's specific job — transforming data that's already in the warehouse — and places it with technical precision inside ELT: dbt is only the T. Never the E, never the L. You're going to see, with concrete examples, what that means in practice.

Resources

← PreviousGuide introduction: from loose SQL to a software projectNext →dbt and the T in ELT