Transformation and querying data

COMP63301 Data Engineering Concepts

 

Stian Soiland-Reyes

This work is licensed under a
Creative Commons Attribution 4.0 International License.

Intended Learning Outcomes

  1. Explain the purpose of data transformation
  2. Design transformed representations depending on data questions
  3. Reason about transformation execution, data movement and scale
  4. Compare and choose between alternative representations of data
  5. Compare batch and streaming data transformations

You have already...

  1. Built an understanding of the data (profiling, exploring)
  2. Considered representation and storage (e.g. tables, JSON)
  3. Data improvements (data quality, cleaning)
  4. Data harmonisation (schemas, semantics)
  5. Integrated data (multiple sources)
  6. Built a transformation pipeline (integrate, aggregate, derive)
  7. Prepared feature data for downstream use

Did we build the right transformation pipeline?

Can it work at scale?

Engineering a data transformation

We need to reason and decide on:

  • representation/schema
  • grain/granularity
  • where we do the computation
  • where/how we move data
  • making our pipeline scalable
  • making our pipeline reusable
  • dynamics: time windows, streaming

Transformation vocabulary

Grain: granularity, primary key, what is a row?

Representation: schema, structure, format, table

Transformations: Harmonisation, reconciliation, integration

Change of grain: Aggregation, summarisation, feature encoding

 

Transformation considerations

What is the source representation? What does one row represent?

What is the purpose of the transformation? What questions will we ask?

What is the target representation? What should one row now represent?

 

Engagement

Enrollment

Students

Courses

?

Students

Students
student_iddegreeentry_yearregion
1028802CS2024North
1278181CS+Math2025South
(20,000 rows)
Courses
course_idschoolcredits
COMP62301CS15
COMP62452CS10
(300 rows)
Enrollment
student_idcourse_idmark
1028802COMP6230178
1028802COMP6245266
1278181COMP6230181
(150,000 rows)
Engagement
event_idstudent_idcourse_idevent_timedurationresource_typecompleted
131211278181COMP623012025-12-0650videotrue
131221028802COMP624522026-02-1445videofalse
131231028802COMP624522026-02-209quiztrue
(3,000,000 rows)

Understanding the source schema

Relational schema notation

Students (student_id, degree, entry_year, region)

Courses (course_id, school, credits)

Enrollment (student_id, course_id, mark)

Engagement (event_id, student_id, course_id, event_time, duration,
                          resource_type, completed)

Students
student_iddegreeentry_yearregion
Courses
course_idschoolcredits
Enrollment
student_idcourse_idmark
Engagement
event_idstudent_idcourse_idevent_timedurationresource_typecompleted

grain: per event per student per course

grain: per student per course

grain: per student

grain: per course

grain: one row represents what?

Transformations can change granularity

Students
student_iddegreeentry_yearregion
Courses
course_idschoolcredits
Engagement
event_idstudent_idcourse_idevent_timedurationresource_typecompleted

(Transaction) Fact table: Append-only list of events

Dimension tables: Mostly static, attributes

Ralph Kimball, Margy Ross (2013):

The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling, Third Edition
ISBN 978-1118530801
 

Engagement

Enrollment

Students

Courses

Kimball star schema:
The fact table is augmented by attributes in each of the dimensions.

facts

dimension or fact?

dimension

dimension

Resources

dimension

Date

dimension

Transformation to.. what?

Consider what questions we want answered

How engaged was a student in a course?

How much time was spent on a course?

How many activities did each student complete?

Do engaged students achieve higher marks?

Data transformation create new data products to make questions (more easily) answerable

First: Decide on the grain of the desired table

Second: Which attributes do we wish were there?

Validate: What can we assert after the transformation?

 

Aggregations:
EventSummary (student_id, course_id, event_count, total_duration, completion_rate, video_duration, reading_duration, last_activity)

Features:
StudentCourseFeatures  (student_id, course_id, mark,

engagement_level, degree, entry_year, region, school, event_count,

total_duration, completion_rate, video_duration, reading_duration, last_activity)

Deciding on target schema

How do we capture our transformations?

Reusable

Each view designed to answer a set of possible questions

 

CREATE VIEW EventSummaryView AS
SELECT
    student_id,
    course_id,
    ...
CREATE TABLE EventSummaryMaterialised AS
SELECT
    student_id,
    course_id,
    ...

A materialised view captures the result of a transformation query

A view captures a reusable transformation as a named query

Changes in source reflected immediately.
No modifications at target.

Transformation result is stored and must be regularly refreshed

Designing features

Categorical values inform schema design

Engagement
event_idstudent_idcourse_idevent_timedurationresource_typecompleted
131211278181COMP623012025-12-0650videotrue
131221028802COMP624522026-02-1445videofalse
131231028802COMP624522026-02-209quiztrue
(3,000,000 rows)
EngagementFeatures
student_idcourse_idvideo_countvideo_durationvideo_completionquiz_countquiz_completion
1244122590.8181.0
210452600.7440.75

Columns per resource_type

What happens if a new  resource_type appears?

Values in one representation become columns in another

Split feature tables

Engagement
event_idstudent_idcourse_idevent_timedurationresource_typecompleted
131211278181COMP623012025-12-0650videotrue
131221028802COMP624522026-02-1445videofalse
131231028802COMP624522026-02-209quiztrue
(3,000,000 rows)
VideoFeatures
student_idcourse_idvideo_countvideo_durationvideo_completion
1278181COMP62452122590.81
1028802COMP6230152600.74
(~ 120,000 rows)
QuizFeatures
student_idcourse_idquiz_countquiz_completion
1278181COMP6245281.0
1028802COMP6230140.75

Feature tables per resource_type

There may be multiple valid feature tables!

Features over time

Engagement
event_idstudent_idcourse_idevent_timedurationresource_typecompleted
4142312442025-12-0450videofalse
4142412442026-02-1845videotrue
(3,000,000 rows)
VideoFeatures
student_idcourse_idvideo_countvideo_durationvideo_completionuntil
124491200.892025-12-01
1244101700.802026-01-01
1244101700.802026-02-01
1244112150.822026-03-01
(~ 300,000 rows)

Feature tables per resource_type per time window (start--until)

Trends can be detected by aggregating per time window

Time
monthacademic_yearfromto
2025-122025/20262025-12-012025-12-31
2026-012025/20262026-01-012026-01-31

Windowing over data

Sales
sales_repamountdate
141£1202025-12-14
52£902025-12-23
141£20002025-12-23
141£2442026-01-01
52£19002026-01-31
141£30002026-03-02

2025-12

2026-01

Tumbling window (e.g. average per month)

2025-12-02 / 2025-12-31

Hopping window (e.g. rolling average of 30 days, per week)

2025-12-09 / 2026-01-07

2025-12-16 / 2026-01-14

2025-12-23 / 2026-01-21

SalesPerMonth
sales_repamount_avgamount_totmonth
141£1060£21202025-12
52£90£902025-12
141£244£2442026-01
52£1900£19002026-01
SalesRolling30d
sales_repamount_avgamount_totstartingending
141£1060£21202025-12-022025-12-31
141£788£23642025-12-092026-01-07
141£1122£22442025-12-162026-01-14
141£1122£22442025-12-232026-01-21

Partitioning aggregations: Can an event belong to multiple time periods?

Transforming semi-structured data

Identifying multi-dimensional data

Courses
course_idschoolcreditskeywords
COMP63301CS15database, data engineering
COMP62452CS & Business10data engineering,machine learning,statistics

data engineering

database

machine learning

statistics

CS

Business

Semi-structured list of values hiding inside a string value — what is the format?

Decomposing semi-structured values

Courses
course_idschoolcreditskeywords
COMP63301CS15database, data engineering
COMP62452CS & Business10data engineering,machine learning, statistics

data engineering

database

machine learning

statistics

CourseKeywords
course_idkeyword
COMP63301database
COMP63301data engineering
COMP62452data engineering
COMP62452machine learning
COMP62452statistics
Keywords
keywordalias
databasedb
data engineering
machine learningML
statisticsstats

Clean

Validate

Split values

Clean

Match

Document concept scheme

CourseSchools
course_idschool
COMP63301CS
COMP62452CS
COMP62452Business
Schools
school_idfull_name
CSDepartment of Computer Science
BusinessAlliance Manchester Business School

"Just enough" database normalisation

Data Vault modelling & SCD

CourseSchools

Courses

Hub

Link

 

Schools

Hub

SchoolDesc

Satellite

SchoolDesc
school_idfull_namevalid_from
CSDepartment of Computer Science2019-09-21
BusinessAlliance Manchester Business School2015-09-21
CSSchool of Computer Science2014-09-21
BusinessManchester Business School2014-09-21

Daniel Linstedt, Michael Olschimke (2015):

Building a Scalable Data Warehouse with Data Vault 2.0.

Hub: things we identify

Link: relationships between things

Satellite: descriptions of things

Slowly Changing Dimension (SCD)

Semi-structured data formats

{ "orders": [
	{ "price": 18.2, 
      "product": "Shoelaces"
    }  
  ]
}

<orders>
	<product name="Shoelaces">
      <price currency="GBP">18.20</price>
  	</product>
</orders>

orders:
 - price: 18.20
    product: Shoelaces
<order1> a :Order;
  :price 18.20;
  :product [
    :name "Shoelaces"
  ] .

XML

Turtle

YAML

JSON

<ORDERS>
<PRODUCT NAME="Shoelaces">
<PRICE CURRENCY="GBP">18.20
</ORDERS>

SGML

Changing representation will often need to change grain

SQL/JSON

EngagementFeatures
student_idcourse_idvideo_countvideo_durationvideo_completionquiz_countquiz_completion
1244122590.8181.0
110452600.7440.75
"courses": { 
  "244": {
    "video": {
      "duration": 259
    },
    "reading": {
      "duration": 480
    },
    "engagement": {
      "event_count": 15,
      "completion_rate": 0.67
    }
  }
  "104": {
  	"video": ...
  }
}

API grain: per student

http://example.com/engagement/student/1

View grain: per student, per course

student_id course_id mark features (JSONB)
1 10 83.7 
{
  "video": {
    "duration": 103
  },
  "reading": {
    "duration": 480
  },
  "engagement": {
    "event_count": 15,
    "completion_rate": 0.67
  }
}
1 101 79.2 
{
  "video": {
    "duration": 168
  },
  "reading": {
    "duration": 200
  },
  "engagement": {
    "event_count": 19,
    "completion_rate": 0.84
  }
}
1 168 67.2 
{
  "video": {
    "duration": 298
  },
  "reading": {
    "duration": 199
  },
  "engagement": {
    "event_count": 18,
    "completion_rate": 0.89
  }
}

Example:

Querying semi-structured JSON in relational databases

student_idcourse_id
1168
WHERE $.video.duration > 200

Where do we do our transformation?

Principle: Push down work to where data lives (or will live)

Life of a query

SQL query
issued

Parsing and conversion

Query planning and optimization

Query execution

Results returned

Adapted from Figure 8.2 in Fundamentals of Data Engineering

🧑🏾‍💻

📃

🧐

👷🏽‍♀️

🧑🏿‍🍳

SELECT first,last
FROM t1
JOIN t2 
ON ...
op: SELECT
cols: first,last
tables: t1, t2
joins: 
  - t1.id = t2.id
scan t1
join t2
index t1.id
sorting
intermediate
results
scan
join
index
sort
intermediate
results
firstlast
AliceBrown
BobDalton

Logical plan

Physical plan

Internal representation

first,last
alice,brown
bob,dalton

Temporary table

Results

The same logical transformation may have several possible queries, which again have several possible physical plans.

SQL is a declarative language

SQL instructions say what you want (logical result), not how

Relational databases are able to optimize the query to decide a physical execution plan e.g. which order to join tables or which rows to examine first

Queries are (largely) independent of implementation details (e.g. file vs memory vs distributed)

Restructuring the relational table design can help/complicate query writing and/or query execution

 

Query optimization

How can we improve query performance?

  • Reduce data movement:  push work (filter/transform/aggregate) close to the data
  • Prejoin data: Denormalisation or materialised views
  • Reduce complexity of query
    • Multiple simpler queries may be faster (but use more data transfer)
    • Try to avoid full-table scan, e.g. SELECT without WHERE
  • Add indexes on frequently queried columns
  • Use EXPLAIN to reveal the query plan

What can affect performance

  • Using an LLM-generated query without checking it
  • Sorting when a view is not for display
  • Querying over larger periods than needed
  • Unnecessary joins of tables that are then not used
  • Unused aggregations
  • Moving more data than needed (e.g. multiple times or wrong direction)
  • Doing the work in the wrong place

How can we improve query performance? #2

  • Consider impact of transactions and concurrency
  • Vacuum dead records
  • Ensure query results can be cached
    • Avoid many almost-same queries
    • Use prepared statements with parameters, don't embed values 
pd.read_sql_query("""
SELECT Title, Authors, ISSNs, Year
FROM Articles
WHERE ISSNs=? AND Month=? AND YEAR=? ;
""", db, 
params=['2056-9890', 2, 2015]).head()

Querying data streams

Streaming architectures

  • Kappa architecture treat all data as continuous streams without a separate batch layer

Figure 3.4, 3.5 from Fundamentals of Data Engineering

  • Lambda architecture has separate stream and batch processing, serving separate query methods

Querying in Lambda architecture

Stream query

Batch query

Data processed in real-time

high speed, but incomplete data

Immediate response to events

"Top up" batch views with newest data

Examples: Spark structured Streaming, Flink, Storm

Operates on complete data (in batches)

high latency, with complete data

Historical accuracy

Each batch update pre-computes full views

Examples: Hadoop, Spark, Snowflake, BigQuery

Named after λ calculus, a functional mathematical system

Events carried by a log/queue, e.g. Kafka/Kinesis

Event time: When did it happen

Processing time: When we process event

Smart Energy example

Batch query

Calculate cost for invoicing by 30 minute readings

Predict which periods are busiest

Latency: 60 minutes (or month!)

Accuracy: Gaps aligned by next readings

Stream query

Immediate feedback, what is using power?

Power generators can react quickly to sudden spikes (e.g. electric car charging)

Latency: 1 minute

Accuracy: Gaps ignored e.g. WiFi is down

Querying in Kappa architecture

Real-time data processed directly, transforming on the fly

Data streams treated like immutable logs

New and historical data handled uniformly (replay)

Simplifies processing, avoiding duality of batch/stream (ensure business logic is consistent)

Low latency, but full reprocessing can be expensive

Examples: Kafka, Flink, Storm

Greek letter κ supersedes λ

Uber event processing

Challenge: backfilling of streaming workloads using a unified codebase.

Goal: Replay Uber rider sessions for accurate monthly business analysis

Example: Rider waits to rate a driver until their next Uber app session

Solution: Use Kappa architecture and process in event-time windows to allow late-arriving and out-of-order events

Benefit: Switching between streaming and batch job is just a switch of data source (live vs replay)

https://www.uber.com/en-GB/blog/kappa-architecture-data-stream-processing/

Querying streams with “SQL”

HiveQL: SQL-like queries on streams

https://www.sparkcodehub.com/hive/use-cases/real-time-insights

Ingest streaming data from Kafka into Hive

Process streaming events using regular SQL
(~ materialised view)

event_id user_id event_type event_timestamp region
3133 alice81 purchase 17:52 Europe
… not here yet! 
{ "event_id": 3133, "user_id": "alice81", "event_type": "purchase", … }
event_id user_id event_data
3133 alice81 { … }
… not here yet!

Each result rows output as their source data arrives

Structured Streaming with Spark SQL and DataFrames

spark.readStream.schema(schema).json("s3a://...") \
     .createOrReplaceTempView("events")

results = spark.sql(
  """SELECT * FROM events
  JOIN people 
  ON events.device = people.device""")

https://spark.apache.org/sql/

results.where("signal > 10").select("name") \
       .writeStream.format("console").start()

Any Spark data source can be queried

DataFrames have SQL-like methods

Kappa style using Kafka and replay 

spark.readStream.format("kafka") \
     .option("subscribe", "events") \
     .option("startingOffsets", "earliest") \
     .load().createOrReplaceTempView("log")

Key points on transformation

Design: What questions do we support? What is our target grain and representation?

Engineer: Where do we compute? Make reusable transformations.

Represent: Decide on different dimensions, features and semi-structured representations. Time as a dimension.

Scale: Let the database/platform optimise your transformations. Design for use of cloud resources.

Stream: batch vs stream -- latency vs completeness.

SQL works on streams!

COMP63301 Integration #2 2026/2027

By Stian Soiland-Reyes

COMP63301 Integration #2 2026/2027

Lecture in COMP63301 Data Engineering Concepts at The University of Manchester.

  • 805

More from Stian Soiland-Reyes