Lesson 1 of 3 in this module

1/14 in track

What is analytics engineering?

sql12 min

Overview

Analytics engineering sits between loading data and analysing it: you reshape what is already in the warehouse. dbt is the usual tool. This tab runs the SELECT, not `dbt run`.

On this page8 sections

The idea

Open a raw orders table straight from an application database and you will notice immediate issues.

Column names disagree across tables, totals sit stored as text strings, and cancelled transactions sit right beside completed payments.

What happens when analysts query messy raw tables directly?

When reporting analysts query raw tables directly, every dashboard cleans data in its own private way.

One analyst in Bengaluru filters out cancelled rows, while another forgets to filter and counts refunded transactions as valid revenue.

Raw orders landing table: inconsistent identifiers, text numbers, and mixed statuses.

order_iduser_idorder_statusorder_total
ORD-0001U004paid612.07
ORD-0002U073refunded446.70
ORD-0003U078paid1104.32

Two different revenue figures go into the Monday leadership meeting, and stakeholders spend 30 minutes debating whose spreadsheet is correct.

The issue is not broken SQL. The issue is having no shared place where business logic gets defined once.

Clean data once in code so reports never debate definitions in meetings.

Words you will see in this lesson

Six words. The rest of the lesson uses only these.

WordWhat it means
WarehouseThe big database where a company keeps its data for reporting
ModelOne SQL SELECT saved in a file. dbt turns it into a table or view
StagingA first cleanup step. Rename columns and fix types, nothing fancy
MartA finished table built for reports, such as monthly revenue
ELTExtract, load, then transform. Raw data goes in first, cleaning happens inside the warehouse
dbtA tool that runs your SQL models, tests them and keeps them in Git

What is analytics engineering?

Somebody has to turn raw tables into clean, dependable datasets that every analyst can trust.

That responsibility has an industry name: analytics engineering.

  1. Raw tablesloaded by data engineers
  2. Staging modelsrenamed and cast in SQL
  1. Automated checksverified for nulls and duplicates
  1. Reporting martsqueried by BI analysts

An analytics engineer writes SQL that reshapes data already inside the warehouse, tests it, and stores it in Git.

The primary tool teams use for this workflow is dbt, which stands for data build tool.

For now, remember one fundamental boundary: dbt does not move data across networks. It starts after data arrives in the warehouse.

Analytics engineers bring software engineering discipline to warehouse SQL.

Why this exists

Organizations need a single source of truth for their core metrics.

Why do reports drift apart without a shared modeling layer?

Consider two teammates answering the exact same question: what was total sales revenue last month?

Both query the same database, but each writes private filter logic inside their own query window.

Without a shared layer, simple queries produce conflicting answers.

AnalystFilter appliedCalculated total (₹)Outcome
Analyst AWHERE order_status = 'paid'1716.39Correct total
Analyst BNo filter applied2163.09Counts refunded order

Rename a source column upstream, and a dozen personal dashboards break without warning.

Load the same order batch twice, and revenue quietly doubles because nothing checked primary keys for duplicates.

Without shared models, every reporting tool re-invents data cleaning independently.

How does a shared transformation layer solve this confusion?

The solution is to clean raw data once in a central transformation layer, and require everyone to query that layer.

Building, testing, and documenting that layer is the analytics engineer core job.

  1. Raw orders tablesource of truth for checkout
  2. Shared stg_orders modelcleans and filters once
  3. Finance dashboardqueries clean model
  4. Operations dashboardqueries clean model

When a source column changes tomorrow, the analytics engineer updates exactly one staging model.

Every downstream report inherits the fix immediately, without any analyst rewriting their queries.

Fix data once in the shared layer instead of patching 12 dashboards.

Picture this

Think of a retail shop with a loading bay, stock shelves, and customers.

How do data team responsibilities divide across the warehouse?

Trucks unload boxes at the loading bay, staff shelve clean goods, and customers browse shelves.

Your data platform functions the same way across three distinct roles.

3 jobs around warehouse data
Analysis: dashboards and questionsAnalytics engineering: clean, test, modelData engineering: load raw tables

Data engineers fill the bottom layer. Analytics engineers build the middle layer. Analysts live in the top layer.

Read the diagram from the bottom. Data engineers get raw tables landed inside the warehouse.

Analytics engineers turn those raw tables into clean, tested models with agreed business definitions.

Analysts query the clean models to answer strategic questions and build leadership dashboards.

RoleWhat they ownWhat they hand over
Data engineeringPipelines that load dataRaw tables, like orders and events
Analytics engineeringSQL models, tests, documentationClean tables, like stg_orders and fct_revenue
AnalysisQuestions and reportsDashboards and answers

Notice that analysts never touch the raw loading bay. They query only the curated middle layer.

What is the difference between ETL and ELT?

Data architectures evolved from ETL to ELT over the last two decades.

ELT stands for extract, load, and transform, in that exact sequence.

Where the cleaning happens
ETL (older habit)ELT (what dbt assumes)vsExtract from the appClean on a separate serverLoad the cleaned resultRaw data is never keptExtract from the appLoad raw tables as they areTransform with SQL in the warehouseRaw data stays for re-runs

Same 3 steps, different order. With ELT the raw copy survives, so you can change your mind later.

In older ETL architectures, external transformation servers cleaned data before loading, discarding raw source records.

If business definitions changed 6 months later, recalculating historical numbers from source was hard or impossible.

In modern ELT, ingestion tools stream raw records directly into the warehouse without upfront transformation.

Transformations run in SQL inside the warehouse, so you can test, version, and rerun models whenever logic changes.

ELT keeps raw records so you can recalculate metrics whenever logic updates.

Where does dbt sit inside the warehouse?

Follow the route data takes inside an ELT warehouse architecture.

Diagram with 3 columns. 3 sources in the Extraction column feed staging tables inside a large database cylinder in the Load column, then target tables in the Transform column, which feed user reporting
An ELT warehouse drawn by a contributor. Sources land in staging tables, and transformations happen inside the database to produce target tables for reporting. dbt operates in that final hop.Source: SudhaNandhini, Wikimedia Commons (cropped) · CC BY-SA 4.0

Notice that staging tables and target tables sit inside the exact same database cylinder.

No rows ship to an external server for processing. That allows a simple SQL file to serve as the entire transform step.

dbt operates strictly inside that final transformation stage, compiling and running your SQL models.

A small example

Here is the smallest honest version of an analytics engineering transformation.

What does a raw source table look like when it lands?

Here are three raw rows straight from the orders table in your warehouse:

Input: raw orders landing table, exactly as loaded.

order_iduser_idorder_statusorder_total
ORD-0001U004paid612.07
ORD-0002U073refunded446.70
ORD-0003U078paid1104.32

Notice the rough edges: user_id is ambiguous, and order_total needs standardizing as amount.

If downstream analysts use user_id while other tables use customer_id, join syntax becomes confusing.

How does a staging model standardize those raw records?

The first model an analytics engineer writes is a staging model.

It is a plain SELECT query that renames columns to team standards and applies consistent aliases.

SQLA staging-shaped SELECT
SELECT
    order_id,
    user_id AS customer_id,
    order_total AS amount
FROM orders
LIMIT 3;

Predict what the output columns look like before checking the transformed table below:

Output: the same rows with names the rest of the project agrees on.

order_idcustomer_idamount
ORD-0001U004612.07
ORD-0002U073446.70
ORD-0003U0781104.32

Same 3 rows, but every downstream query now refers to customer_id and amount consistently.

If the source database team renames user_id next month, you update one staging SELECT instead of 12 reports.

Watch outWhat this tab runs vs real dbt

The SQL editor in this tab runs plain SQL in DuckDB. It does not run the dbt CLI or compile Jinja templates. In real dbt, you save this SELECT in a .sql file. dbt Core wraps it in CREATE VIEW or CREATE TABLE and executes it against your warehouse.

Watch outdbt moves nothing

If a teammate asks dbt to pull orders out of an app database, the answer is no. A loader tool or ingestion script puts raw tables in the warehouse first. dbt starts from there.

Which software engineering practices does dbt bring to SQL?

Analytics engineering applies 4 core software development practices to analytical databases.

Core software development practices applied to warehouse SQL.

Software practiceHow dbt implements itBenefit to the team
Version controlModels and YAML stored in GitEvery query change reviewed via pull request
Automated quality checksAssertions run after models buildCatches missing keys or duplicate rows after builds
Modularity (DRY)Reusable staging and mart modelsStops copy-pasting repetitive SQL blocks
Automated documentationYAML descriptions compile to DAG siteLineage graph shows table dependencies visually

Instead of copying SQL across personal query consoles, teams organize models into modular, testable files.

In modern dbt Core (1.8 and above, including 1.12), column tests are defined under the data_tests key in YAML.

Generic tests like unique and not_null run after a model builds, verifying output rows without blocking writes at the storage engine level unless contracts are configured.

Treat analytical SQL with the same testing and version control as application code.

How does a complete analytics engineering pipeline run from source to mart?

The complete analytics engineering pipeline
  1. Extract and Load: ingestion tools load raw tables straight into the warehouse.

    1. Source app
    2. Raw orders table
  2. Staging: a lightweight SELECT renames fields and standardizes types.

    1. Raw orders table
    2. stg_orders model
  3. Testing: automated assertions verify non-null keys and accepted values after build.

    1. stg_orders model
    2. CI test suite
  4. Marts: tested models join into dimensional tables that feed BI dashboards.

    1. stg_orders model
    2. fct_revenue mart

4 rules to remember about analytics engineering:

  1. Data engineers land raw data; analytics engineers transform and test models inside the warehouse.
  2. ELT stores raw source records permanently so models can be re-run whenever definitions update.
  3. Staging models rename and cast columns one-to-one with source tables without joins or aggregations.
  4. dbt applies software engineering discipline: version control, automated testing, modularity, and lineage.

Common beginner questions

Is analytics engineering the same as data engineering?

No, though on small teams one person often does both. Data engineering gets data into the warehouse and keeps those pipelines alive. Analytics engineering takes what is already there and models it with SQL, tests, and documentation.

Why transform inside the warehouse and not before loading?

Warehouses are very good at running SQL over large tables, and SQL is a language most of your team already reads. Keeping the cleaning next to the data also means you can rerun it from the raw copy when a definition changes.

Do tests in dbt prevent bad rows from being written?

By default, generic dbt tests run after a model builds. If a test fails, dbt halts downstream dependent models, but the bad rows already landed in that table unless you configure database constraints or transactions.

What comes next

You now understand the analytics engineering role and where it sits in the data stack.

In the next lesson, dbt project structure, you will explore what a dbt project looks like on disk: which folders hold staging models, which hold marts, and where YAML configurations live.

Practice

Run the sample query once to see a staging-shaped SELECT in action.

Then complete the exercise: from orders, return order_id, user_id AS customer_id, and order_total AS amount, limited to 20 rows. Write clean SQL only.

Going further: the grain is the model's contract

An analytics engineer builds tables other people trust. The most important promise a table makes is its grain: 1 row per what. Everything downstream, every sum and count, assumes that promise holds. dbt's unique test is the fastest check on that promise.

A model that claims to be 1 row per order, with an accidental duplicate.

order_idamount
ORD-0001612.07
ORD-0002446.70
ORD-0002446.70
ORD-00031104.32

The unique test runs a query that groups by the primary key and returns rows with a count greater than one. Predict what this query returns before revealing the output.

SQLWhat the unique test runs
WITH orders_model AS (
    SELECT 'ORD-0001' AS order_id, 612.07 AS amount
    UNION ALL SELECT 'ORD-0002', 446.70
    UNION ALL SELECT 'ORD-0002', 446.70
    UNION ALL SELECT 'ORD-0003', 1104.32
)
SELECT order_id, COUNT(*) AS n
FROM orders_model
GROUP BY order_id
HAVING COUNT(*) > 1;
order_id  n
ORD-0002  2

Order ORD-0002 appears twice, so the test returns a row and fails. That failure prevents downstream models from building on duplicate data. Without this test, summing amount gives 2,609.79 instead of the real 2,163.09 total.

Model promisedbt testWhat it protects
1 row per orderunique on order_idRevenue sums and order counts
Every order has a keynot_null on order_idDownstream joins
Every order links to a customerrelationships to customersOrphaned transactions

Try a new scenario

You join orders to order_items to build a reporting model called orders_summary, but finance reports that revenue numbers are inflated. What caused this, and how do you resolve it?

Work it out, then open the answer

The join expanded the grain from 1 row per order to 1 row per line item. If an order has 3 items, the order total gets added 3 times. To fix it, either aggregate line items to the order level before joining, or keep the model at the line item grain, rename it to order_items_summary, and test uniqueness on item_id.

What is the difference between a data engineer and an analytics engineer?

Data engineers build and maintain the infrastructure that ingests raw data into the warehouse reliably. Analytics engineers use SQL, dbt, and Git to transform that raw data into clean, tested models for business analysts.

Close the lesson and explain it

Without scrolling up, explain what table grain means and why a unique test protects aggregate numbers.

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.

Finished this lesson?

Mark it as read when you could explain it to someone else.

Rate:
Was this useful?