Technical Data engineering simplified explanation.

August 31, 2026 · 11 min read

Founder of Agenytics

The purpose of Data engineering is to use the data of the company to pave the way of the companie's future and adjust operations accordingly.

The fundamental question in data engineering is to understand from where the data is taking and to where data is putting and how the data is used.

Data collection

To use data, you should collect all data into one unified platform.

Nowadays the companie's data can be stored in:

DB (PostgreSQL, MariaDB, e.t.c)
CRM / ERP / EHR (Salesforce, e.t.c)
CSV / Excel
Analytics (Google Analytics, e.t.c)

How data is going to the Unified storage?

Using Data Ingestion

It can be done by Batch's or by Streaming.

Batch ingestion is, for example, synchronizing data of Unified storage with the initial source every hour.
Streaming is by using the near real-time update of unified storage with the initial source. For example, in GCP it will be the Datastream service with CDC of PostgreSQL. Here the CDC will look for the updates on the logs of PostgreSQL and when new event arrives it will fire the event, it will be done under the hood by Datastream service.

Task	Service
Batch ingestion → BigQuery	BigQuery Data Transfer Service
Batch transfer → Cloud Storage	Storage Transfer Service
Very Huge offline-transfer	Transfer Appliance
Streaming ingestion	Pub/Sub
CDC from DB Streaming	Datastream

So what is the unified storage?

In GCP, it can be the cloud storage (in AWS it is S3). It is Data Lake the home for unstructured data

The data itself can be:

Unstructured data - PDF, DOCX, TXT, JPG, PNG, MP3, WAV, ZIP, etc.
Semi-structured data - JSON, XML, HTML, CSV e.t.c
Structured data - Structured data for analytics is organized into "tables", often modeled as facts and dimensions, and stored in warehouses/lakehouses. An OLAP cube consists of measures (facts) organized across dimensions, and each dimension can contain hierarchies. “Dimension” in an OLAP cube is the analytical axis (e.g., Time, Product, Geography). So cube is mathematical 3d tensor (more details about math concept at the end)

Datastream

Imagine you have a PostgreSQL database:

PostgreSQL
   │
   │ INSERT
   │ UPDATE
   │ DELETE
   ▼
Datastream
   │
   ▼
Cloud Storage / BigQuery

Datastream uses CDC (Change Data Capture) it reads the database’s change log, such as PostgreSQL’s WAL, and sees:

01 INSERT user #101
02 UPDATE user #101
03 DELETE user #55

And it forwards these changes almost immediately. This means you don’t need to run a full SELECT on the entire database every 5 minutes. Datastream constantly monitors changes. Google explicitly describes it as a continuous CDC replication service in near real-time. ([Google Cloud Documentation][1])

Moreover, Datastream can first perform a backfill—retrieve existing data—and then continue capturing new INSERT/UPDATE/DELETE operations. ([Google Cloud Documentation][1])

Dataflow

Dataflow is, first and foremost, a data processing engine.

For example:

PostgreSQL
     ↓
Datastream
     ↓
Cloud Storage
     ↓
Dataflow
     ↓
clean
join
reformats
calculate
filter
     ↓
BigQuery

Dataflow supports batch processing, streaming, and complex transformations. ([Google Cloud][2])

So, the main difference is:

Datastream = “monitor changes and transfer them.”

Dataflow = “take the data and process it.”

DataFlow it's an orchastrator that executes Beam pipelines.

Beam pipeline is a piepiline of data transformaion:

pipeline
    → Read
    → Filter
    → Transform
    → GroupBy
    → Write

Apache Beam - is a Beam framework:

import apache_beam as beam

with beam.Pipeline() as pipeline:

    (
        pipeline
        | "Read CSV" >> beam.io.ReadFromText("users.csv", skip_header_lines=1)
        | "Parse" >> beam.Map(lambda line: line.split(","))
        | "Adults only" >> beam.Filter(
            lambda row: int(row[2]) >= 18
        )
        | "Get names" >> beam.Map(
            lambda row: row[1]
        )
        | "Print" >> beam.Map(print)
    )

Without Dataflow:

10,000,000 records
        ↓
    Server
        ↓
   processing

For example, a single machine processes:

10 million → 30 minutes

Dataflow can divide the work as follows:

10 million records
      │
      ├── 1.7 million → Worker 1
      ├── 1.7 million → Worker 2
      ├── 1.7 million → Worker 3
      ├── 1.7 million → Worker 4
      ├── 1.6 million → Worker 5
      └── 1.6 million → Worker 6

And they work in parallel.

This is one of the main ideas behind Dataflow.

You're describing the logical processing, while Dataflow handles the distribution of the work.

What is a PCollection?

This is a very important concept in Beam.

When you read data:

Cloud Storage
     ↓
PCollection

A PCollection is a distributed collection of data.

For example:

PCollection<Order>

may contain:

Order 1
Order 2
Order 3
...
Order 10,000,000

It can be:

bounded — finite:

file → 10 million records → end

or unbounded — infinite:

Pub/Sub
 ↓
event
event
event
event
event
...

Beam supports both options—batch and streaming. ([Google Cloud Documentation][1])

What is a Transform?

A Transform is an operation on a PCollection.

For example:

PCollection<Order>
        ↓
      Map
        ↓
PCollection<OrderWithTotal>

Or:

PCollection<Order>
        ↓
    GroupByKey
        ↓
PCollection<Customer, Orders>

Let's say you want to:

count sales every 5 minutes.

Events arrive:

Dataflow needs to determine:

which events belong to a single 5-minute period?

To do this, Beam uses windowing.

For example:

12:00 ───────── 12:05 Window 1

12:05 ───────── 12:10 Window 2

But what if an event arrives late?

For example:

event timestamp = 12:03

but it actually arrived in Dataflow at 12:07

This is where two more very important concepts come into play:

event time — when the event occurred.
processing time — when Dataflow processed it.

And for cases like this, Beam uses watermarks and triggers.

Data WareHouse models

Star schema

POPULAR ONE is a star schema. It consists of:

Fact table in the center → ONLY numbers/measures + IDs (sales_amount, product_id, customer_id)
Dimension tables around it → descriptive information (product_name, customer_name, country, date)

Example:

          dim_customer
               |
dim_product — fact_sales — dim_date
               |
          dim_store

It is called a star schema because the structure looks like a star.

Other models are:

Snowflake schema — dimensions are further normalized into multiple tables. More complex, less redundant.
Galaxy / Fact constellation — multiple fact tables share common dimensions (e.g. fact_sales + fact_returns + fact_inventory).
One Big Table (OBT) — combines facts and dimensions into one wide table. Increasingly used in modern analytics for simplicity.

DBT

dbt (data build tool) is a tool focused on data transformation inside cloud data warehouses. It uses simple SQL statements to organize, test, and share data that is already loaded into a DWH.

Semantic Layers

The most important thing to do before creating a fact or dimension is to define the grain.

Grain

Grain = what a single row in a DWH table represents.**

For example:

fact_orders

We can say:

1 row = 1 order

order_id	customer_id	amount	created_at
1001	55	120	...
1002	71	250	...
1003	55	80	...

Grain:

one row per order

Grain depends on exactly what we’ve decided to store in the table.

For example:

1 row = 1 order

1 row = 1 order item

1 row = 1 customer per day

1 row = 1 product per store per day

In all cases, it’s one row, but the meaning of the row is different.

For example:

date	customer	revenue
Aug 30	Ali	$100
Aug 30	Bob	$200

Here, the grain is:

1 row = 1 customer + 1 day

In other words, grain is not the number of rows.

info

Grain = the level of detail at which a single row exists.

Wrong Grain problem

Orders

order_id	customer	total
100	Ali	$100

Items in the Order

order_id	item	price
100	A	$60
100	B	$40

If we perform a JOIN:

SELECT *
FROM orders o
JOIN order_items i ON o.order_id = i.order_id;

we get:

order_id	total	product	price
100	100	A	60
100	100	B	40

Now, if an analyst runs:

SUM(total)

the result will be $200 instead of $100.

Why? Because a single row in orders has multiplied into two rows.

This is fanout / multiplication.

info

Fanout = a single row after a JOIN is transformed into multiple rows.

1. Business question
        ↓
2. Grain
        ↓
3. Fact
        ↓
4. Dimensions
        ↓
5. Relationships / JOIN
        ↓
6. Metrics

Canonical model

info

Canonical model is a stable business-aligned contract between raw data and semantic layer. It is like a abstract definition where source-specific complexity is absorbed below the canonical layer.

Testing canonical model

Structural tests

unique(customer_id)
not_null(customer_id)

Referential integrity

fact_order.customer_id
    → dim_customer.customer_id

Business rules

order_amount >= 0

Reconciliation

canonical revenue
≈ source-system revenue

info

An “Owner” is a person or team responsible for defining, ensuring the accuracy of, and making changes to an object. You identify owner by business domain ownership.

For example:

Metric: Revenue
Owner: Finance Analytics

Why Finance, specifically?

Because Finance determines what the company considers to be revenue.

info

Definition - "What exactly does this business concept mean?"

info

Data lineage - "Where does this metric come from?"

Revenue ↓ fact_order.order_amount ↓ canonical.fact_order ↓ stg_orders ↓ PostgreSQL.orders

Freshness SLA

info

Data Classification - confidentiality status labeling

Allowed Joins - Which joins are semantically safe?

Which system is authoritative for Revenue?

"Before choosing the model grain, I would establish the business definition and authoritative source for Revenue. Then I would model the canonical fact at the grain of the underlying business event or transaction, rather than prematurely aggregating it to campaign level"

Versioning

Business definitions change.

Today:

Active User =
1 event in the last 30 days

Tomorrow, Product decides:

Active User =
2 qualifying events in the last 28 days

This is already a semantic change.

Therefore, you need to understand:

Revenue v1

Revenue v2

or store:

valid_from

valid_to

This is especially important if historical reports need to remain reproducible.

Business owner?
        ↓
Finance

Definition?
        ↓
Recognized revenue excluding VAT,
refunds and cancelled orders.

Grain?
        ↓
Order-level / revenue event-level

Source lineage?
        ↓
Billing DB → CDC → Raw → Staging → Canonical

Freshness?
        ↓
< 15 minutes

Quality?
        ↓
Not null
Valid amounts
Reconciliation with Finance

SLA?
        ↓
99.9% availability
< 15 min freshness

Classification?
        ↓
Internal / Confidential

Allowed joins?
        ↓
Customer
Date
Product
but not raw payment events without aggregation

Version?
        ↓
v1.0

Relationship / Cardinality Metadata

You explicitly define how tables are related:

Customer 1 ─── N Orders
Order    1 ─── N Order Items

The semantic layer knows that Customer → Orders is one-to-many and can take this into account when generating SQL.

Key idea: tell the semantic layer the cardinality of relationships.

Pre-aggregation

You aggregate a table to the required grain before joining.

For example, one order has 3 payments:

Order 100 → Payment 1
         → Payment 2
         → Payment 3

Instead of directly joining:

orders JOIN payments

you first do:

SELECT order_id, SUM(amount)
FROM payments
GROUP BY order_id;

Now you have:

order_id | total_payment
100      | 300

The grain is now one row per order, so the join is much safer.

Key idea: bring data to the correct grain before the JOIN.

Symmetric Aggregates

This is a technique where the semantic engine constructs aggregations in a way that prevents fanout from artificially increasing measures.

Instead of simply:

SUM(order.amount)

the engine uses a more sophisticated aggregation strategy that accounts for the unique order_id.

The goal is:

The metric should remain correct even when the JOIN creates multiple rows for the same entity.

Explicit Modeling

You explicitly define which relationships are valid instead of allowing the semantic layer to guess.

For example:

orders.customer_id → customers.id
cardinality: many-to-one

And you can control complex relationships:

orders → customers
orders → order_items
orders → payments (aggregated)

rather than allowing arbitrary:

orders
  JOIN payments
  JOIN order_items

which could create fanout.

Key idea: control which joins are allowed and how they should behave.

Relationship metadata
→ Tell the engine how tables are related.

Pre-aggregation
→ Aggregate to the correct grain before JOIN.

Symmetric aggregates
→ Make metrics resistant to fanout.

Explicit modeling
→ Define safe relationships and prevent dangerous JOINs.

And the core principle to remember:

Always reason about grain before joining tables.

info

GCS → Pub/Sub → Datastream → Dataflow → BigQuery → Dataform → Looker | BI

Metric reconciliation.

Show me exactly how Finance calculates Revenue
Timezone
Decompose Revenue into its components
Compare data at the transaction level
Check pipeline by lineage
SQL and business rules

File formats

info

"Parquet is a compressed columnar file format optimized for analytical workloads. We use it because it provides efficient storage and allows query engines to read only the columns they need." (columnar storage)

Avro for streaming (row level storage with metadata)

Source / Kafka
      ↓
   Avro       ← for streaming / ingestion
      ↓
Data Lake
      ↓
  Parquet     ← for analytics
      ↓
Analytics / BI

SCD Types

info

SCD Type 1 (Slowly Changing Dimension Type 1) has a flat temporal semantic: it always represents the current state and erases all past timelines

Slowly Changing Dimension (SCD) Type 2 temporal semantics define how dimension record versions map to time through bounded validity intervals (valid_from and valid_to timestamps).

SCD Type 3 temporal semantics track a single point of transition by maintaining both the current value and the immediate past value within a single row. Captures exactly one previous state alongside the active state. When a second change occurs, the older historical value is overwritten and permanently lost.

P.S. Math concepts

Vector → 1-dimensional array: [2, 5, 7]
Matrix → 2-dimensional array: rows × columns
3D tensor → 3-dimensional array: e.g. height × width × channels
4D tensor → 4-dimensional array, etc.

So you can think of a cube as a 3D tensor.

A tensor is the general concept that includes scalars (0D), vectors (1D), matrices (2D), and higher-dimensional arrays (3D, 4D, ...).

Data collection​

How data is going to the Unified storage?​

Datastream​

Dataflow​

What is a PCollection?​

What is a Transform?​

Data WareHouse models​

Star schema​

DBT​

Semantic Layers​

Wrong Grain problem​

Canonical model​

Testing canonical model​

Versioning​

Relationship / Cardinality Metadata​

Pre-aggregation​

Symmetric Aggregates​

Explicit Modeling​

Metric reconciliation.​

File formats​

SCD Types​

P.S. Math concepts​

Data collection

How data is going to the Unified storage?

Datastream

Dataflow

What is a PCollection?

What is a Transform?

Data WareHouse models

Star schema

DBT

Semantic Layers

Wrong Grain problem

Canonical model

Testing canonical model

Versioning

Relationship / Cardinality Metadata

Pre-aggregation

Symmetric Aggregates

Explicit Modeling

Metric reconciliation.

File formats

SCD Types

P.S. Math concepts