The Modern Data Analytics Stack Explained

"The modern data stack" is not a product you buy. It is a common way of arranging tools so that data flows from the systems where it is created to the dashboards and analyses where decisions are made. Most versions share three ideas: data is stored in a cloud warehouse or lakehouse, raw data is loaded first and transformed there with SQL, and each layer is handled by a tool that does one job well. Knowing the layers helps analysts understand where a number comes from, and where it can go wrong.

The layers at a glance

Ingestion: load first, transform later

Older data warehouses followed ETL: extract, transform, then load, so data was reshaped before it arrived. The modern pattern is usually ELT: extract and load raw data as it is, then transform it inside the warehouse. Keeping the raw copy means a transformation mistake can be fixed and rerun without going back to the source, and new questions can be answered from data nobody had modelled yet.

Storage: warehouse or lakehouse

A cloud data warehouse stores tables and runs SQL at scale without servers to manage; BigQuery, for example, is fully managed and serverless, queried with SQL or Python (Google Cloud: BigQuery overview). A lakehouse combines the benefits of data lakes, cheap storage of files in open formats, with those of warehouses, such as tables and SQL (Databricks: what is a data lakehouse). For analysts the experience is similar: tables to query with SQL.

Transformation: where analytics engineering happens

Raw tables are rarely ready for analysis. Transformation turns them into clean, joined, documented models. dbt is one tool built for this layer: you write SQL select statements, and dbt turns them into modular models that build on each other (dbt: what is dbt). A simplified model that turns raw orders into a clean table:

-- models/orders.sql
select
    order_id,
    customer_id,
    cast(ordered_at as date) as order_date,
    status,
    amount
from {{ ref('stg_orders') }}
where status != 'test'

The same project declares data tests, assertions that dbt checks each time the project is built. dbt includes four generic tests, unique, not_null, accepted_values and relationships (dbt: data tests); the arguments key is the syntax for dbt 1.10.5 and later:

models:
  - name: orders
    columns:
      - name: order_id
        data_tests: [unique, not_null]
      - name: status
        data_tests:
          - accepted_values:
              arguments:
                values: ['placed', 'shipped', 'completed', 'returned']
      - name: customer_id
        data_tests:
          - relationships:
              arguments:
                to: ref('customers')
                field: customer_id

Because models and tests are plain text files, they live in version control and are reviewed like code. This is the work of the role often called analytics engineering, between data engineering and analysis.

Semantic layer: define metrics once

Even clean tables allow different definitions of revenue or active customers. A semantic layer defines each metric once, and every downstream tool uses that definition; dbt's documentation describes centralising metric definitions as the way to keep self-service access consistent (dbt: Semantic Layer). BI tools offer the same idea through shared semantic models. Self-service analytics explains why this matters once many people build reports.

Consumption: dashboards, notebooks and spreadsheets

At the top of the stack, people answer questions: with BI dashboards, with notebooks in Python or R, or with spreadsheets connected to the warehouse. Power BI vs Tableau vs Looker compares the main BI platforms.

Orchestration and governance

Something must run ingestion, then transformation, then tests, then refresh dashboards, in order and on schedule, and alert someone when a step fails. Apache Airflow is an open-source platform for developing, scheduling and monitoring such workflows (Apache Airflow documentation). Governance spans every layer: tests, a catalog describing what each table means, lineage showing which dashboards depend on which tables, and access control; data governance basics covers what analysts need to know.

Following one number through the stack

Take "revenue last month" on an executive dashboard. It starts as rows in the order system. Ingestion copies them into raw tables overnight. A staging model renames columns and fixes types; the orders model removes test orders; a data test checks that every order ID is unique, so a duplicate load cannot double revenue silently. The semantic layer defines revenue as the sum of completed order amounts. The dashboard queries that metric. When the number looks wrong, lineage tells you exactly which steps to inspect.

Does a small team need all of this?

No. A small team can start with one database, SQL transformations kept in version control, scheduled with a simple job, and a free dashboard tool. Free tools for data analysts lists options for each layer. Add dedicated tools when a real problem appears: many sources to maintain, conflicting metric definitions, or failures nobody notices.

For how the stack serves the wider analytics process, see business analytics explained. Connecting databases to Power BI and automating reports are part of the data integration and automation module of the Data Analytics course.

Sources

• BigQuery overview (Google Cloud) — Google Cloud
• What is a data lakehouse? (Databricks documentation) — Databricks
• What is dbt? (dbt documentation) — dbt Labs
• Add data tests to your DAG (dbt documentation) — dbt Labs
• dbt Semantic Layer (dbt documentation) — dbt Labs
• What is Airflow? (Apache Airflow documentation) — Apache Software Foundation