"Modern data engineering" gets used for everything from a single dbt project to a company-wide streaming platform. Underneath the buzzwords, almost every modern analytics stack has the same six layers: data comes from sources, is ingested, lands in storage, gets transformed into trusted tables, is described by a semantic layer, and is finally consumed by dashboards, models and, increasingly, AI assistants. Two concerns cut across all of them: orchestration and governance.
This guide is the map. Each layer gets a short explanation, the decisions that matter, and a link to a deeper article on this blog.
1. Sources and ingestion
Sources are the systems that run the business: transactional databases (ERP, billing, EHR), SaaS tools (CRM, marketing, support), event streams from apps and devices, and plain files from partners. Ingestion moves that data into your platform, and there are three main patterns:
- Batch loads: copy tables or files on a schedule (hourly, nightly). Simple, cheap and still the right default for most reporting.
- Change data capture (CDC): read the database's transaction log (for example with Debezium) and replicate inserts, updates and deletes continuously, without heavy queries on the source.
- Streaming: publish events to a log such as Apache Kafka and process them within seconds. Worth the extra complexity only when a decision really depends on fresh data, such as fraud checks or live operations.
Managed connectors (Fivetran, Airbyte and the like) now handle most standard sources, which is why many teams load raw data first and transform later. That shift from ETL to ELT is covered in ETL vs ELT: differences, examples and when to use each.
2. Storage: warehouse, lake or lakehouse
For most of the 2000s, analytics meant a relational data warehouse on dedicated hardware. Hadoop data lakes made it cheap to keep raw and unstructured data, cloud warehouses separated storage from compute, and lakehouses now combine open files in object storage with warehouse-style tables. The full story is in Big Data Transformation: from data warehouses to lakehouses.
Whatever the platform, organise storage in layers so that problems can be traced and fixed:
| Layer | Also called | What it holds |
|---|---|---|
| Raw | Bronze, landing | Data exactly as it arrived, never edited. Lets you reprocess history. |
| Staging | Silver, cleaned | Typed, deduplicated, renamed columns; one table per source entity. |
| Marts | Gold, presentation | Business-ready facts and dimensions modelled for analysis. |
If you built warehouses in the Kimball era this will look familiar: staging area, then conformed dimensions and fact tables. I compared the traditional and Hadoop-hybrid versions of this flow back in 2015 in Data Warehouse Architecture: Traditional ETL vs Big Data Hybrid; the layers survived, the technology underneath changed. For how a leading cloud warehouse implements storage and compute, see Snowflake architecture explained.
3. Transformation
Transformation turns raw tables into trustworthy ones: cleaning, joining, deduplicating, applying business rules and building dimensional models. Today most of it is written in SQL and run inside the warehouse, typically managed with dbt, which adds version control, dependencies between models, documentation and automated tests. Spark (or Databricks) is the usual choice when data is very large, semi-structured, or needs Python-heavy processing.
Two habits separate reliable pipelines from fragile ones: every model has tests (uniqueness, not-null, accepted values, row-count checks), and every transformation is idempotent, meaning running it twice for the same day gives the same result.
4. Orchestration
Orchestration decides what runs, in which order, and what happens when something fails: retries, alerts, backfills of missed days. Apache Airflow is the most widely used open-source orchestrator; Dagster and Prefect are popular alternatives, and managed platforms have their own schedulers. A practical introduction with a complete daily pipeline is in Apache Airflow for data engineers.
5. The semantic layer
Once tables are clean, people still disagree about numbers if every dashboard calculates "revenue" or "active customer" its own way. A semantic layer defines metrics, dimensions and access rules once, and every tool queries through it. It is also what makes AI assistants safe to use on company data. See What is a semantic layer?.
6. Consumption: BI, machine learning and AI assistants
The last layer is where value is created. BI tools (Power BI, Tableau, Looker, Superset) cover reporting. Data scientists train models on the same curated tables; my readmission prediction tutorial is a worked example. The newest consumer is the AI analytics assistant that turns a question in plain English into SQL; building one with proper guardrails is covered in Building an AI analytics assistant.
Cross-cutting: governance and data quality
Governance is not a layer you add at the end. It means knowing what data you have (a catalog), who may see it (role-based access, masking of personal data), where each number came from (lineage) and whether today's data can be trusted (quality checks and freshness alerts). Regulated industries such as healthcare and finance need all of this from day one; everyone else needs it the first time a wrong number reaches the board.
The layers at a glance
| Layer | Job | Common tools |
|---|---|---|
| Ingestion | Move data from sources into the platform | Fivetran, Airbyte, Debezium, Kafka, custom Python |
| Storage | Keep raw and modelled data cheaply and safely | Snowflake, BigQuery, Databricks, Redshift, S3/ADLS/GCS with Iceberg or Delta |
| Transformation | Clean, model and test data | dbt, SQL, Spark |
| Orchestration | Schedule, retry, alert, backfill | Airflow, Dagster, Prefect |
| Semantic layer | Define metrics and access once | dbt Semantic Layer, Cube, LookML, Power BI semantic models |
| Consumption | Dashboards, models, AI assistants | Power BI, Tableau, Looker, Python, LLM-based assistants |
| Governance | Catalog, lineage, access, quality | Unity Catalog, DataHub, OpenMetadata, data tests |
Choosing a stack by team size
| Situation | Sensible starting point |
|---|---|
| 1–2 data people, mostly reporting | One cloud warehouse, managed connectors, dbt, a BI tool. Skip streaming and Kubernetes. |
| Growing team, several domains | Add orchestration (Airflow or similar), data tests in CI, a semantic layer and a catalog. |
| Large or regulated organisation | Lakehouse or warehouse with open table formats, strict access control and lineage, CDC and streaming where the business needs it. |
The most common mistake is buying the architecture of a company ten times your size. Start with the simplest version of each layer and add complexity only when a real requirement appears.
Reading path
- ETL vs ELT: differences, examples and when to use each
- Snowflake architecture explained, and how it compares to Databricks and BigQuery
- Apache Airflow for data engineers
- What is a semantic layer?
- Building an AI analytics assistant: text-to-SQL with guardrails
Background reading: Big Data Analytics explained and Predictive analytics: concepts, techniques and examples. If you work with Pentaho, my tutorials at knowpentaho.com cover ETL with PDI in depth.
Comments
Post a Comment