IMEN891K / IDSC501 / AXGS-501 · Data Engineering · Fall 2026

4. Pre-processing I: Cleaning & Integration

Sangwoo Mo · POSTECH
Industrial & Management Engineering

Where We Are

PART I · Collection and Preparation PART II PART III Collect Lec 2 Explore Lec 3 Preprocess Lec 4-5 Storage Lec 6-13 Monitoring and Updates Lec 14-17 nothing downstream can recover what preprocessing throws away

Outline: Four Stages of Preprocessing

01 Cleaning Wrong values in one table 02 Integration one view Tables that disagree 03 Transformation 12.4 0.42 0.03 0.00 870 1.00 Values a model can read 04 Reduction Less data, same signal

Not a One-Way PipelineTwo of today’s sections are steps running out of order

Clean step 1 Integrate step 2 Transform step 3 Reduce step 4 which value is wrong shows up only after the merge °F and °C must agree before two rows can match
Both orange arrows run backward, and both are sections of part 2

Cleaning

01 Cleaning Wrong values in one table 02 Integration one view Tables that disagree 03 Transformation 12.4 0.42 0.03 0.00 870 1.00 Values a model can read 04 Reduction Less data, same signal

Three Branches of Cleaning

Cleaning Missing Values Why the Cell Is Empty Four Options for a Hole Filling from the Rest of the Table Noise and Outliers Smoothing a Stream Detection by Statistics Detection by Model Inconsistency Fields That Contradict Rule-Based Checks

Missing Values

Cleaning Missing Values Why the Cell Is Empty Four Options for a Hole Filling from the Rest of the Table Noise and Outliers Smoothing a Stream Detection by Statistics Detection by Model Inconsistency Fields That Contradict Rule-Based Checks

Why the Cell Is Empty

RowStateSalaryExperience
1NY57,400Mid
2TXmissingEntry
3NJ90,000High
4VT36,900Entry
5TXmissingMid
6CA76,600High
7NY85,000High
8CAmissingEntry
Different missingness causes need different remedies

Caveat: Why is it missing?

Holes independent of the value 25 × 26 121 29 91 30 × 30 110 31 × 44 118 46 93 48 × 51 × 52 116 54 × age IQ
Holes concentrated in the younger half 25 × 26 × 29 × 30 × 30 × 31 × 44 118 46 93 48 116 51 141 52 97 54 104 age IQ

Below, no young IQ was ever observed. No imputer fixes this. The question is not which method to use but why those rows are missing, and that answer lives in how the data was collected

Four Options for a Hole

age 41 dept QC tenure 6y state TX grade B salary ? Drop the row row deleted uses nothing · safe only when holes are rare Constant Unknown keeps the hole visible to the model Central tendency 78,000 uses that one column, and nothing else Most probable value 84,500 predicted from age, dept and tenure
Top to bottom, each one uses more of the rest of the record

Imputation as EstimationFilling a hole from the rest of the data

As collected A B C D 1 2 4 5 6 8 9 10 11 impute After imputation A B C D 1 2 3 4 5 6 7 8 9 10 11 12
x̂ij = f( Xobserved )every filled cell is a prediction from the rest of the table
Every filled cell is an estimate, not an observation

Caveat: Mean ImputationEvery hole gets the same value, and the spread of the column collapses

Original distribution versus zero imputation and mean imputation
Mean stays 2.08; standard deviation falls to 1.76 and a spike appears

Observed and Filled ValuesUse other observed columns to find comparable rows

Observed measurements

parthelpertarget
P11020
P21224
P311NULL

Estimate for P3

Neighbors in helper: P1 and P2
(20 + 24) / 2 = 22

Stored with provenance

parttargetestimated
P322Yes
The filled number is an estimate; keep its origin distinguishable

k-Nearest Neighbor (kNN) Imputation

kNN imputation: find closest neighbors, take their mean
x̂ij = (1/k) ∑r ∈ Nk(i) xrjclosest in the other columns, or in an embedding of them
Average similar rows; including all observed rows gives the column mean

Demo: Imputation MethodsOne column of cycle times, 1,200 rows: every fill changes the column

1. Compare mean and kNN fill.   2. What changes when the helper column contains little information?

Answer: Imputation ChoicesCompare distributions, not only the filled cells

ChoiceWhat to inspect
Mean fillMean can stay close; variance shrinks
kNN fillDoes the helper column contain information?
Missingness maskKeep observed and estimated cells distinguishable
A filled cell is an estimate, not a new observation

Regression Imputation

Observed points versus regression-imputed points lying exactly on the fitted line
x̂ij = b0 + ∑m≠j bm ximno residual is added back, so the spread shrinks
Deterministic predictions remove residual variation and can make correlation look stronger

Noise and Outliers

Cleaning Missing Values Why the Cell Is Empty Four Options for a Hole Filling from the Rest of the Table Noise and Outliers Smoothing a Stream Detection by Statistics Detection by Model Inconsistency Fields That Contradict Rule-Based Checks

Raw Sensor StreamsMetered power demand, with two different kinds of error mixed in

Raw versus cleaned power demand time series
Blue spikes drop to zero and below. That is the meter dropping out, not demand

Noise vs OutliersSmooth measurement noise; investigate outliers before changing them

Noise: every value is a little wrong smooth all 1,200 rows survive Outliers: a few values sit far away if confirmed: remove three rows are gone
Smoothing changes values; removal drops rows

Smoothing by Binning

Sorted values (price, $) 48915 21212425 26282934 Bin 1Bin 2Bin 3 mean 9 mean 23 mean 29 Smoothed 9, 9, 9, 9, 23, 23, 23, 23, 29, 29, 29, 29
Twelve values become three distinct ones. Smoothing buys steadiness with resolution

Moving Averages for StreamsSimple (SMA) and exponential (EMA) moving averages use only current and past samples

alarm limit 66 °C EMA, α = 0.15 SMA, w = 20 4 samples of alarm delay fault starts here
x̂t = (1/w) ∑k=0w−1 xt−k·st = α xt + (1 − α) st−1SMA weights the window equally; EMA uses one α
Both use past samples; here EMA crosses four samples before SMA

Demo: Smoothing a StreamCurrent and past cycle times (seconds); wider trailing windows respond later

1. Compare windows 1, 5 and 11.   2. When does the smoothed signal cross 68 s?
5

Answer: Smoothing DelayA larger trailing window needs more new observations

WindowFirst smoothed value ≥ 68 sDelay after t = 30
1300 samples
5322 samples
11355 samples
Less fluctuation comes with a later response

Outlier Detection by Statistics

Boxplot showing quartiles, whiskers and outliers
zi = (xi − x̄) / soroutside Q1 − 1.5·IQR, Q3 + 1.5·IQRthe z-score uses the mean; the fence uses only quartiles
Separate column fences can miss an unlikely combination of otherwise ordinary values

Demo: IQR Fence Width300 cycle times with eight sensor glitches and eight genuinely slow cycles

1. Change the fence width.   2. Which genuine cycles disappear along with sensor glitches?
drop anything outside Q1 − k·IQR … Q3 + k·IQR

One-Class ClassificationDraw a boundary around normal and flag what falls outside

Multi-class classification, multi-class detection and one-class classification
A learned boundary checks combinations that separate column fences miss

Caveat: Irreversible CleaningCleaning cannot be undone downstream

As recorded one spike at 14:02 smooth drop outliers gone As stored nothing marks where it was
Deleting the spike removes the evidence needed to revisit the decision

Inconsistency

Cleaning Missing Values Why the Cell Is Empty Four Options for a Hole Filling from the Rest of the Table Noise and Outliers Smoothing a Stream Detection by Statistics Detection by Model Inconsistency Fields That Contradict Rule-Based Checks

Fields That Contradict Each Other

OrderOrderedShippedQtyUnitAmount
A-10422026-03-052026-03-0112kg480,000
A-10432026-03-062026-03-090kg1,250,000
A-10442026-03-062026-03-0826lb1,040,000
A-10452026-03-072026-03-1015kg600,000
Nothing is missing here. Each value is wrong only next to another value

Rule-Based ChecksDomain knowledge determines which consistency rules are valid

CheckRow it stopsWhere the rule comes from
shipped ≥ orderedA-1042How the business works
qty = 0 → amount = 0A-1043How the business works
unit ∈ { kg }A-1044A decision someone has to make
Three rules flag three rows; verify the recorded values before training

Demo: Checking ConsistencyApply two domain rules to the recorded fields

run_idstartedendedtotaldefects
R-0109:0009:201004
R-0209:3009:10802
R-0310:0010:152024
1. Which rows violate time or count rules?
2. Can the rule tell you which cell is wrong?

Answer: Violations and RepairsA failed check identifies a contradiction, not the correct replacement

run_idViolated ruleAction
R-01nonekeep
R-02ended ≥ startedcheck source timestamps
R-030 ≤ defects ≤ totalcheck count definitions
Flag contradictions before overwriting recorded values

Integration

01 Cleaning Wrong values in one table 02 Integration one view Tables that disagree 03 Transformation 12.4 0.42 0.03 0.00 870 1.00 Values a model can read 04 Reduction Less data, same signal

One Customer, Three SystemsCustomer relationship management (CRM), supply chain management (SCM), and enterprise resource planning (ERP)

SystemOwned byKeyName as storedRevenue field
CRMSalesC-10482Hanil Precision Co., Ltd.booked_amount
SCMLogisticsV0000734HANIL PRECISION(none)
ERPFinance2010-HP한일정밀(주)net_sales
No shared key, no shared spelling. Nothing joins these three rows on its own

Three Questions of Integration

Integration step 1 Schema Mapping Which Column Means What Field Meanings and IDs step 2 Duplicate Detection Normalize, Then Match Fuzzy, Learned, LLM Matching step 3 Fusion Rules About the Source Rules About the Value

Schema Mapping

Integration step 1 Schema Mapping Which Column Means What Field Meanings and IDs step 2 Duplicate Detection Normalize, Then Match Fuzzy, Learned, LLM Matching step 3 Fusion Rules About the Source Rules About the Value

Same Name, Different Entity

Entity relationship diagram over supplier, customer, order, shipment, item and product
Order and Shipment are separate entities here; their names alone do not define the schema

Mapping Two Schemas into One

CRM schema Customer Name Phone ERP schema Account Company Phone Email Integrated schema Party Name Phone Email
Matched elements collapse into one. What only one side has still has to fit somewhere

Demo: Mapping Customer RecordsMatch meaning before matching rows

Source fieldTarget fieldTransform or decision
CRM.Name / ERP.CompanyParty.nameretain name variants
CRM.customer_id / ERP.account_idsource_idkeep source + original ID
CRM.booked_amount / ERP.net_salesseparatedifferent measures
1. Do booked_amount and net_sales belong in one column?
2. Do matching column names prove matching customers?

Answer: A Shared SchemaAlign field meanings while preserving different measures and source identifiers

Target fieldSource mappingMeaning
name + aliasesCRM.Name / ERP.Companyorganization names
source / source_idCRM ID / SCM ID / ERP IDoriginal identifiers
booked_amountCRM.booked_amountbooked orders
net_salesERP.net_salesrecognized net sales
Mapping columns does not establish that two rows refer to the same entity

Mapping Fields and Preserving IDsAlign field meanings now; establish shared identity after record matching

CRM fieldERP fieldIntegrated field
Customer.NameAccount.CompanyParty.name
Customer.PhoneAccount.PhoneParty.phone
—Account.EmailParty.email
customer_idaccount_idsource + source_id
Matching column meanings does not prove that two records describe the same party

Duplicate Detection

Integration step 1 Schema Mapping Which Column Means What Field Meanings and IDs step 2 Duplicate Detection Normalize, Then Match Fuzzy, Learned, LLM Matching step 3 Fusion Rules About the Source Rules About the Value

Demo: Exact DuplicatesNormalize, then drop_duplicates: twelve supplier rows, six real suppliers

1. Normalize until six suppliers remain.   2. What breaks when you keep only the first word?

Answer: Normalization and MergingA shorter string can accidentally erase a real distinction

NormalizationResult in this example
Case, punctuation, spaces, suffixes12 rows → 6 suppliers
Keep only the first wordDistinct Samsung and POSCO entities merge
Preserve original name and source IDThe decision remains auditable
Normalization should support matching without hiding the original record

Candidates and Confirmed MatchesSimilarity proposes record pairs; evidence supports a shared identity

Schema already aligned

name · phone · source_id

Candidate pair

Hanil Precision Co., Ltd.
Hanil Precision Co Ltd

Similar names suggest a match

Identity evidence

Verified registration ID

Confirm → one party_id
Unclear → keep for review
Preserve source IDs while deciding which records refer to one entity

Fuzzy MatchingEach field gets a similarity score, then one weighted total against a cutoff

Name Hanil Precision Co., Ltd. Hanil Precision Co Ltd 0.88 w .4 City Seoul SEOUL 1.00 w .2 Phone 02-555-0100 02-555-0199 0.82 w .3 Founded 1994 1994 1.00 w .1 weighted total 0.90 cutoff 0.85 → one supplier · cutoff 0.95 → two
score = ∑f wf · simf(a, b)·sim = 1 − edit(a,b) / max(|a|,|b|)on lowercased text, then one weighted total
Per-field scores become one number. The cutoff you pick trades false merges against missed ones

Learned MatchingLearned representations compare records beyond exact spelling

Siamese network comparing two tuples through word embeddings

Input

Two candidate records

Model

Encode fields and estimate similarity

Decision

A score proposes a match; evidence confirms it

Learned similarity can link different spellings; identity still needs evidence

LLM-Assisted MatchingLarge language models (LLMs) propose the matches, humans verify them

Five-step LLM-assisted duplicate detection: search query, phonetic and BM25 plus LLM search, Elasticsearch candidates, AI scoring, results package, human review

Input

Candidate names and record fields

Model

Retrieve evidence and propose a match

Review

Verify identity before merging records

Models propose candidates; verified identity supports a merge

Answer: Source IDs and Party IDsA verified link groups records without rewriting their original identifiers

sourcesource_idparty_idbasis
CRMC-10482P-073verified registration ID
SCMV0000734P-073verified registration ID
ERP2010-HPP-073verified registration ID
Similar names generate candidates; verified evidence supports a merge

Fusion

Integration step 1 Schema Mapping Which Column Means What Field Meanings and IDs step 2 Duplicate Detection Normalize, Then Match Fuzzy, Learned, LLM Matching step 3 Fusion Rules About the Source Rules About the Value

Conflicts After MatchingMatched rows still disagree on values

name address phone CRM Hanil Precision Teheran-ro 12 02-555-0100 SCM Hanil Precision Teheran-ro 12 02-555-0100 ERP Hanil Precision Teheran-ro 12, 5F 02-555-0199 Web form Hanil Precision Teheranro 12 010-2233-4455
Same customer, three spellings of one address, three phone numbers

Rules About the Source

CRM rank 1 · written 2019 Teheran-ro 12 ERP rank 2 · written 2021 Teheran-ro 12, 5F Web form rank 3 · written 2026 Teheranro 12 Source trust keeps the CRM value and the floor ERP had right is dropped Recency keeps the newest write and the newest write was the typo
Neither rule reads the value. One keeps a stale address, the other keeps a typo

Rules About the Value

Majority voting CRM 02-555-0100 copy 02-555-0100 report 02-555-0100 ERP 02-555-0199 three agree, but two of them are copies of the first Learned Seoul vs SEOUL → a person kept Seoul 02-555-0100 vs 0199 → a person kept 0100 conflicts a person already settled Model picks the winner of the next conflict
x̂ = argmaxv ∑k wk · 1[xk = v]weighted vote: wk is the trust in source k
Three copies of one value count as one vote, not three

Demo: Resolving a Customer ConflictChoose a canonical value and keep the evidence

FieldCandidate valuesEvidence
nameHanil Precision / 한일정밀(주)same verified registration ID
address12 / 12, 5F / Teheranro 12verified delivery: 12, 5F
phone02-555-0100 / 02-555-0199 / 010-2233-4455no fresh confirmation
1. Which values can be settled now?
2. Which field needs a review instead of a vote?

Answer: A Canonical CustomerPublish resolved values while keeping unresolved conflicts visible

party_idfieldcanonical valueevidence / status
P-073nameHanil Precisionverified ID; aliases retained
P-073addressTeheran-ro 12, 5Fverified delivery + ERP
P-073phoneNULLreview; candidates retained
A canonical record includes provenance and unresolved decisions

Takeaway: Cleaning and Integration

DecisionWhat must remain clear
Repair a valueObserved, estimated or verified against the source
Flag an errorA contradiction identifies a problem, not its replacement
Merge recordsField meaning, entity identity and conflict resolution
Preserve original evidence and record why each value or identity changed
← → / Space · S notes · O overview · F fullscreen · number + Enter
Speaker notes