Skip to main content

Modern Data Engineering Architecture: A Practical Guide

"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.

Governance: catalog, access control, data quality, privacySourcesApps & databasesSaaS APIsEvents / IoTFilesIngestionBatch loadsCDCStreamingStorageObject storage +warehouse orlakehouseraw → staging → martsTransformationSQL / dbtSparkData testsSemantic layerMetricsDimensionsAccess rulesConsumptionBI dashboardsML modelsAI assistantOrchestration & observability: scheduling, retries, alerts, lineage (e.g. Airflow)

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:

LayerAlso calledWhat it holds
RawBronze, landingData exactly as it arrived, never edited. Lets you reprocess history.
StagingSilver, cleanedTyped, deduplicated, renamed columns; one table per source entity.
MartsGold, presentationBusiness-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

LayerJobCommon tools
IngestionMove data from sources into the platformFivetran, Airbyte, Debezium, Kafka, custom Python
StorageKeep raw and modelled data cheaply and safelySnowflake, BigQuery, Databricks, Redshift, S3/ADLS/GCS with Iceberg or Delta
TransformationClean, model and test datadbt, SQL, Spark
OrchestrationSchedule, retry, alert, backfillAirflow, Dagster, Prefect
Semantic layerDefine metrics and access oncedbt Semantic Layer, Cube, LookML, Power BI semantic models
ConsumptionDashboards, models, AI assistantsPower BI, Tableau, Looker, Python, LLM-based assistants
GovernanceCatalog, lineage, access, qualityUnity Catalog, DataHub, OpenMetadata, data tests

Choosing a stack by team size

SituationSensible starting point
1–2 data people, mostly reportingOne cloud warehouse, managed connectors, dbt, a BI tool. Skip streaming and Kubernetes.
Growing team, several domainsAdd orchestration (Airflow or similar), data tests in CI, a semantic layer and a catalog.
Large or regulated organisationLakehouse 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

  1. ETL vs ELT: differences, examples and when to use each
  2. Snowflake architecture explained, and how it compares to Databricks and BigQuery
  3. Apache Airflow for data engineers
  4. What is a semantic layer?
  5. 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