MDI3005
Advances in Data Engineering
What is Data Engineering?
What is Data Engineering?
• Data engineering is the discipline focused on designing,
building, and managing systems that process and store
large volumes of data.
• It involves creating and maintaining data pipelines,
developing infrastructure for data storage.
• Ensures that data is reliable, accessible, and ready for
analysis data scientists, data analysts, business
intelligence (BI) developers, and other specialists
Software Engineer vs Data Engineer vs Data Scientist
Who is a Data Engineer?
• Data engineers work to transform raw data into
formats that are usable for data scientists, analysts,
and other stakeholders, facilitating efficient data
processing and analytics.
• Data engineering supports data scientists and analysts
by providing clean, reliable data.
Data Need Hierarchy
Role of Data Engineering
Core Role
• Design, build, and maintain data architectures such as data lakes, data
warehouses, and lakehouses so that data from multiple systems can
be stored and queried efficiently.
• Enable data-driven decision making by ensuring that analysts, data
scientists, and applications can access trustworthy, well-structured
data with low latency.
• Data ingestion, Data transformation (ETL/ELT), Data storage and
modeling, Data pipeline orchestration, Data quality and governance,
Collaboration with stakeholders.
Data Engineering Process
Data ingestion (acquisition)
• Data ingestion (acquisition) moves data from multiple sources
• SQL and NoSQL databases, IoT devices, websites, streaming
services, etc. — to a target system to be transformed for further
analysis.
• Data comes in various forms and can be both structured and
unstructured
Data transformation
• Data transformation adjusts disparate data to the needs of end
users. It involves removing errors and duplicates from data,
normalizing it, and converting it into the needed format.
Data serving
• Data serving delivers transformed data to end
users — a BI platform, dashboard, or data
science team.
Data flow orchestration
• Data flow orchestration provides visibility into the data
engineering process, ensuring that all tasks are successfully
completed.
• It coordinates and continuously tracks data workflows to detect
and fix data quality and performance issues.
Data engineering pipeline
• A data pipeline combines tools and operations that move data
from one system to another for storage and further handling.
Constructing and maintaining data pipelines is the core
responsibility of data engineers.
• Among other things, they write scripts to automate repetitive
tasks – jobs.
Data engineering pipeline
• Commonly, pipelines are used for
• Data migration between systems or environments (from on-
premises to cloud databases)
• Data wrangling or converting raw data into a usable format
for analytics, BI, and machine learning projects
• Data integration from various systems and IoT devices
• Copying tables from one database to another.
ETL pipeline
• ETL (Extract, Transform, Load) pipeline is the most common architecture
that has been here for decades. It’s often implemented by a dedicated
specialist — an ETL developer.
• As the name suggests, an ETL pipeline automates the following
processes.
• Extract — retrieving data. At the start of the pipeline, we’re dealing with raw data
from numerous sources — databases, APIs, files, etc.
• Transform — standardizing data. Having data extracted, scripts transform it to meet
the format requirements. Data transformation significantly improves data
discoverability and usability.
• Load — saving data to a new destination. After bringing data into a usable state,
engineers can load it to the destination, typically a database management
system (DBMS) or data warehouse
ETL Pipeline
Diversity of Data Sources
• Main Data Source Categories
• 1. Databases
• Relational Databases - Structured data with predefined schemas
(SQL)
• NoSQL Databases - More flexible data models
• Key-Value stores - For high-speed lookups
• Document stores - For semi-structured data
• 2. Files
• Text files
• Audio files
• Video files
Diversity of Data Sources
• 3. APIs (Application Programming Interfaces)
• Request-Response pattern
• Data formats: XML, JSON, etc.
• Enables data exchange between systems
• 4. Data Sharing Platforms
• Internal Data Users - Within the organization
• Third-Party Data Sharing - External partnerships
• 5. IoT Devices (Internet of Things)
• Swarm of IoT devices - Collective sensor networks
• Continuous stream of sensor data
• Real-time data generation
Diversity of Data Sources
• Key Challenges with Diverse Data Sources
• Unpredictable systems - Source systems may behave
unexpectedly
• Systems go down - Availability issues requiring
contingency planning
• Change in format/schema - Data structure modifications
over time
• Change in data - Data quality and consistency variations
• Delivery challenges - Getting data to downstream systems
reliably
Diversity of Data Sources
• Key Challenges with Diverse Data Sources
• Unpredictable systems - Source systems may behave
unexpectedly
• Systems go down - Availability issues requiring
contingency planning
• Change in format/schema - Data structure modifications
over time
• Change in data - Data quality and consistency variations
• Delivery challenges - Getting data to downstream systems
reliably
This framework helps data engineers design robust pipelines that can handle
the complexity of data coming from diverse, heterogeneous sources while
maintaining data quality and reliability.
Cloud Data Warehouses and Data Lakes
• Storage abstractions combining storage systems, chosen based
on latency, scalability, and cost parameters.
• Data Warehouse
• Structured data storage optimized for analytics and BI reporting
• Amazon Redshift - Cloud data warehouse service for storing, transforming,
and serving data
• Predefined schemas, optimized for fast SQL queries on cleaned data
• Data Lake
• Raw, unstructured/semi-structured data storage (files, logs, JSON, etc.)
• Amazon S3 as foundational object storage layer
• Schema-on-read approach, supports diverse data formats and ML workloads
Cloud Data Warehouses and Data Lakes
• Data Lakehouse
• Combines Data Lake + Data Warehouse capabilities
• Adds governance, ACID transactions, and SQL analytics to data lakes
(e.g., Apache Iceberg)
• Handles both raw storage and structured analytics in unified platform
• Key Decision Factors
•
Factor Data Warehouse Data Lake Lakehouse
Data Types Structured Raw/Unstructured Both
Schema Schema-on-write Schema-on-read Both
Primary Use BI/Reporting ML/Exploration All use cases
Cost Model Query-based Storage-based Hybrid
Iceberg + Athena M1.-The-
Examples Redshift, Snowflake S3, ADLS Data-Engineering-
[Link]+1
Cloud Data Warehouses and Data Lakes
• Cloud advantages: Pay-as-you-go scaling, no upfront capacity
planning, managed services reduce operational overhead.
• Key Decision Factors
Factor Data Warehouse Data Lake Lakehouse
Data Types Structured Raw/Unstructured Both
Schema Schema-on-write Schema-on-read Both
Primary Use BI/Reporting ML/Exploration All use cases
Cost Model Query-based Storage-based Hybrid
Iceberg + Athena M1.-The-
Examples Redshift, Snowflake S3, ADLS Data-Engineering-
[Link]+1
Data Ingestion Tools
• Click here to view
Data Transformation and Modeling Tools
• Click here to view
Data Transformation and Modeling Tools
• Essential components of the data engineering lifecycle
• Transform raw data into structured, analysis-ready formats
• Enable analytics, machine learning, and business intelligence
• Overview of Data Transformation
• What is Data Transformation?
• Process of converting raw data into structured formats
• Enrichment with additional fields and calculations
• Data validation, cleaning, and normalization
• Bridge between ingestion and analytics/ML use cases
Data Transformation and Modeling Tools
• Essential components of the data engineering lifecycle
• Transform raw data into structured, analysis-ready formats
• Enable analytics, machine learning, and business intelligence
• Overview of Data Transformation
• What is Data Transformation?
• Process of converting raw data into structured formats
• Enrichment with additional fields and calculations
• Data validation, cleaning, and normalization
• Bridge between ingestion and analytics/ML use cases
Data Transformation and Modeling Tools
What is Data Transformation?
• Process of converting raw data into structured
formats
• Enrichment with additional fields and calculations
• Data validation, cleaning, and normalization
• Bridge between ingestion and analytics/ML use
cases
Data Transformation and Modeling Tools
• What is Data Modeling?
• Designing coherent structure for data to make it useful for
business
• Creates schema that represents business entities and relationships
• Optimizes for query performance and data accessibility
• Data Modeling Approaches:
• Star Schema: Fact tables with dimension tables (common in data
warehouses)
• Snowflake Schema: Normalized dimensional data
• Entity-Relationship Model: Normalized database design
• Dimensional Modeling: Business-oriented approach (Kimball
methodology)
Data Transformation and Modeling Tools
• SQL as Transformation Foundation
• Core SQL Commands for Transformation:
• Data Cleaning:
• SELECT DISTINCT - Remove duplicates
• TRIM(), REPLACE() - Clean whitespace and values
• DROP, TRUNCATE - Remove data
• Data Operations:
• Joining: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, UNION
• Aggregating: SUM, AVG, COUNT, MAX, MIN, GROUP BY
• Filtering: WHERE, AND, OR, IS NULL, IS NOT NULL, IN, LIKE
Data Transformation and Modeling Tools
Workflow Orchestration Platforms
• What Is Workflow Orchestration?
• Automated coordination of data workflows and pipelines
• Schedules, runs, and monitors tasks with dependencies
• Ensures reliability across multiple systems
• Key Responsibilities
• Task scheduling and dependency management
• Error handling and retries
• Monitoring and observability
• Scalability and resource management
• Data lineage tracking
Workflow Orchestration Platforms
• Top 3 Platforms (Stats)
• Airflow: ~320M+ downloads in 2024, massive production usage (Uber, Stripe)
• Dagster: strong commit activity, data-centric design
• Prefect: tens of millions of downloads, cloud-native focus
• Apache Airflow: Overview
• Origin: Airbnb, now Apache project
• Task-centric, DAG-based orchestration (Python)
• Very large community and ecosystem
• Apache Airflow: Strengths & Limits
• Strengths: battle-tested at scale, rich integrations, managed services (MWAA,
Cloud Composer)
• Limits: steep learning curve, batch-oriented, more ops overhead
Workflow Orchestration Platforms
• Dagster: Overview
• Data-centric, asset-based orchestration
• Strong lineage and observability, great for data/ML teams
• Tight dbt integration and good local dev experience