Data Engineering — 1: An Eagle Eye View of the Modern Data Landscape

In simple term Data Engineering is the process of converting and serving available data into a consumable form i.e curated data for the clients(Data scientist/ML/BI/analytics) and some times with added responsibilities like security, versioning, quality and optimization.

what’s data and info Data: Data is any raw facts that’s generated be it numbers, signals, observations it can be anything without a proper context Information: The data that’s been processed and given a structure, context and meaning is called information.

Data flow:

Data production /generation → data transformation → data serving

Our raw material is DATA and finished product is Information curated for the clients.

data genereated → different formats-(csv,json,logs,structured, binary, unstructured) storage_place → this becomes the data source

data transformation → clean into a curated form

Data serving → (presentation)- instead of serving the big utensil in which u prepared the dish u curate it in a structure course and in portions

upstream and downstream:

A river stream is flowing from source upstream - u and your code is the turbine, the resrvoir and — the downstream are the consumers , this is the system u need to maintain as a DE both source and serving.

OLTP and OLAP:

OnLine Transactional Processing →

say we are an e-commerce site all the user transaction, clicks, wish lists , order should be tracked →high write and update performance needed, DBA and app side Data base ,entity relationship modelling the data using normalization.

Activity is happening continuously on the website that is generating data and then it grows enormously , reading this is difficult Eg: Any app side DB implementation SQL Server, MySQL etc.,

OnLine Analytical Processing →

Data Engineering is responsible for this, we need high quality, curated data aligned with business requirement, generally done using Data Ware House, Lake House.

we need efficient for reads ,we use dimensional modeling

Eg: snowflake, synapse, redshift

ETL/ELT pipelines:

Pipelines are the systematic activities performed to make the data move form high speed OLTP into OLAP.

ETL- Extract Transform and Load,

this was older way now we use ELT, extract and load it in data lake, so for transformation we dont disturb the OLTP, which is for high write not reads. we need to process the OLTP data in a same manner at regular intervals(batch Processing) or when it just happens (incremental/streaming processing), so we create pipeline system that take care of the transformation and write it to an OLAP.

Data Ware House & layers:

staging layer and core layer

first extract and dump in staging layer as reading from OLTP source is useless , then transform and put it in core layer where DATA modeling the fact and dimensions tables exist

Staging(Bronze/silver) Transient /temporary layer- 99% we use this type, old data truncated and new data entered, old data is not needed to be processed - persistent layer — preserve the history

Transformation happens in silver layer

Core(Gold) Curated data lives here

Incremental Loading A part of data fetching strategy , where we pull the latest data from OLTP and load into the staging layer

Dimensional Modeling

modeling technique where we store in fact table and many dimensions linked to this facts, clustered based on the business use case.

facts → only numeric values rest goes into the table, so its quick to read. dimensions → provides context data for the facts.

star schema → single level of dimensions

Fig-1 Star schema figure by Author(devsaga22)

snowflake schema →hierarchy of dimensions- dimension can have few more dimensions, this is difficult to manage.

Fig-2 snowflake schema figure by Author(devsaga22)

SLOWLY CHANGING DIMENSIONS

Type-0,1,2,3

Fig-3 original products dimension table figure by Author(devsaga22)

Type-0 →assume our dimensions will not be changing, read only even if the source changes we dont change in OLAP,useful for date of birth etc we keep it to maintain the original truth

We handle changes in other types ,lets say at the source our oltp and we change the products dimensions category from smart phone to a generalised category of Electronics, dimensions is changed we handle this by Type-1,2,3

Type-1 → upsert most used and performant

Fig-4 update figure by Author(devsaga22)

update + insert- over write the vlaue no history of changes

Fig-5 insert figure by Author(devsaga22)

if new record u insert, if old record update, the unchanged ones are also updated but the new value and the old value both match so we dont see any difference.

Type-2 → preserve history

Fig-6 Type-2 SCD figure by Author(devsaga22)

we put 3 extra cols, start_date, expiry_date, inuse-flag(yes/no,true/false) so if we change an existing one we write the expiry_date as the start_date of the new value and flag it unused u insert the new value row as it is with expiry_date as some big value

Type-3→ preserve previous value

Fig-7 Type-3 SCD figure by Author(devsaga22)

only current_value and prev_value no time line

Data Lake

it can handle unstructured, semi structured(json,csv), and also structured data

Data lake vs DWH

DataLake(schema on read)→schema is defined after write,but u need it for read using distributed SQL Engine so you get a table structure on reads. dwh(schema on write)→you define schema prior to writing data- its like a DB

Lake House

Data lake storage is cheap and can handle different kinds of data text, binary, images etc., anything you name it.

DWH- performant using Data Dimensional model Apply a metatada(logical) layer on top of DATA LAKE provide meta data and governance . to get best of the both worlds.

Fig-8 Lake house flow Source: Microsoft Learn — Azure Fabric Documentation

FILE FORMAT

how data is being stored in the disk

row based — csv, avro — row by row useful in writing so used in OLTP

column based — parquet, column by column reads used in OLAP for faster reads

DELTA FORMAT

This is used in deltalakes , where the delta format gives the following attributes over the data lake ACID , time travel-, schema evolution to the data

delta file — transaction log — versioning — json files in transaction logs — it allows for querying the data as existed say like 5 days ago.

BIG DATA

Apache kafka- streams , Apache hive — the Distribute Sql Engine , Apache spark- big data processing across cluster, Apache airflow- orchestration , data bricks- unified management layer for spark clusters , dbt — Transformation

clusters driver node- gives tasks and data chunks worker nodes- executes the programs

Cloud Data Engineeering

only sensitive data is put on premise, cloud is DATA lake cheap storage and offeres pas as you go , scaling is easy.

Medallion Architecture

Bronze- Objective: landing zone => no changes raw data Silver- objective: define structure ,enforce and evolve schema as needed => clean ,transformed and augmented data . Gold- objective: deliver continuously updated data downstream => facts and dimension , aggregated tables.

Fig-9 Delta lake Medallion Architecture Source: Microsoft Learn — Azure FabricDocumentation

Azure Events Hub(Apache Kafka’s Azure product)

This is like a ‘‘surge tank in a hydraulic power plant’’ through which the water flows , it also safeguards the downstream(spark cluster- turbine equipment) against pressure variation (data generation). It is essential for reliability of the downstream apparatus.

1.Pressure Regulation: If your source (like a website during a Big Billion Day sale) suddenly sends 1 million clicks per second, your “turbine” (Spark or a Database) might crash under the pressure. Event Hubs catches that “surge” and holds the data safely.

2. Buffering (Decoupling): It separates the Production (the high-pressure pipe) from the Processing (the downstream flow). The downstream system can “consume” the data at its own pace without being overwhelmed by the source.

3.Temporal Storage: Just as a surge tank holds a volume of water temporarily, Event Hubs holds data (usually for 1 to 7 days) so that if your downstream processing fails, you don’t lose the data — you just “drain” the tank once the system is back up.

Fig-10 Azure Data Engineering flow Source: Microsoft Learn

Ingestion part interacts with the storage and process part , you can see the Medallion architecture 3,4,6 transformations and then the served up to BI or ML downstream. All this happens in the presence of Monitor and Governance layer.

cloud sql DB- Azure SQL DB

It’s where the “production” data often lives before a Data Engineer pulls it into the Lake.

Datalake-Azure Data Lake Storage Gen-2

storage account => service of ADLS gen-2

data lake we store containers(folders)- hierarchial namespaces — sub folders.

The Difference: Data Lake vs. Blob Storage

• The “One-Sentence” Pitch: Blob storage is a “flat” storage (like a giant bucket where everything is tossed in), whereas ADLS Gen2 (Data Lake) uses a Hierarchical Namespace.
• Why it matters: In a Data Lake, you can organize data into /Year/Month/Day/ folders. This allows the compute engine (Spark) to "prune" partitions, only reading the folders it needs. It makes searches significantly faster and cheaper.

CLoud ETL tool- Azure Data Factory

Low code etl orchestration tool, responsible for moving data and also task definition

Big Data Transformation — Azure Databricks

Cloud Data Warehousing — Azure synapse analytics — equivalent to snowflake, Amazon redshift.

Data Reporting -Azure power BI

This is cloud related Power BI tool , it connects to your gold layer and turns it into dashboards.

Azure Synapse Analytics: The Enterprise Warehouse. While Databricks is great for processing i.e transformation and pipeline jobs, Synapse is optimized for massive SQL queries and serving data to BI tools. It’s Azure’s answer to Snowflake. Often gold layer is dumped here for BI/analytics query performance.

Azure Governance & Security

1. Azure purview- The Data Map. It provides “Data Lineage,” showing you exactly where a piece of data came from and who has changed it. Essential for compliance. 2.Azure Key-vaults → security of pipeline holds secrets and keys used by the pipeline and notebooks used. 3.Microsoft entra-ID → record of all the users within our org, manages permissions of developer and services within org.

Operations & DevOps 4.Azure DevOps →The assembly line , CI/CD deployments to the production environment. 5.Azure Monitor- monitor whats happening, health of the pipelines same work by grafana and prometheus. 6.Microsoft Cost Management- the budget tracker which ob or cluster is generating more bill.

So Data Engineering also involves security, monitoring and cost control.

This article is to give an overview into the job role, flow and tools on Data Engineering in Azure stack Ihave not gone deep into the different aspects of it as this is just a primer.

Next it will be on DWH, incremental Loads, Dimension Modelling and SCD deep dive. I’m devsaga22, a developer currently deep-diving into Data Engineering and documenting my learnings, if you find it useful follow my othr publications.