Skip to content

Skill 1.3.7: Clean data (for example, by detecting outliers, imputing missing data, deduplication)

Task 1.3: Data Preparation for ML and AI - Validate data quality and manage bias

Core principle: Profiling precedes cleaning. You cannot choose a repair before you know the defect. Cleaning decisions encode assumptions that affect model bias, distribution, and fairness.

AWS Services for Data Cleaning

Service Primary Role Best For Exam Notes
AWS Glue DataBrew Visual profiling & cleaning recipes Outlier detection, missing value imputation, deduplication, format standardization Serverless; recipe jobs are repeatable; profile job surfaces defects before cleaning
AWS Glue Data Quality Declarative validation Completeness, uniqueness, ranges, column values Uses DQDL rulesets; evaluate on Glue Data Catalog tables or pipelines; acts as a validation gate
SageMaker Data Wrangler Interactive transforms Cleaning, balancing, bias detection Visual; exports to pipeline; good for exploration and quick iteration
SageMaker Processing Custom cleaning scripts Complex logic at scale (Spark, scikit-learn) Full control; use for model-based imputation, fuzzy dedup, custom outlier logic
AWS Glue ETL / Amazon EMR Large-scale distributed cleaning Dedup, joins, filtering, aggregation Use when data is too large for single-node processing
Amazon Comprehend PII detection Cleaning/safety screening for text Detects PII in English/Spanish; supports masking before training
Amazon CloudWatch Monitoring & logging Validation failures, job metrics Ensures cleaning/validation failures are visible, not silent

Outlier Detection & Handling

  • Definition: Data points deviating significantly from expected distribution. Can be errors or rare valid events.
  • Detection methods: Statistical (Z-score >3, IQR 1.5×IQR), distance-based (k-NN, DBSCAN), model-based (Isolation Forest, autoencoders).
  • Handling trade-offs:
  • Remove: Preserves distribution if true error; loses rare valid signal if not.
  • Cap/Winsorize: Limits influence, keeps row; distorts tail.
  • Impute: Treat as missing; risks masking true extremes.
  • Keep: Essential for fraud, disease, rare-event detection.
  • AWS implementation: DataBrew profiling, Data Wrangler, SageMaker Processing.

Exam Gotcha: Outliers are not always errors. Removing valid rare events introduces bias and destroys recall for critical classes. Always validate context before removal.

Missing Data Imputation

  • Mechanisms: MCAR (Missing Completely at Random), MAR (Missing at Random), MNAR (Missing Not at Random).
  • Imputation methods:
  • Mean/Median/Mode: Simple, fast. Mean pulls distribution toward center, reduces variance; median robust to outliers.
  • Constant: e.g., "Unknown"; preserves missingness as a signal.
  • Forward/Backward fill: Time series.
  • Model-based: KNN, MICE, regression; more accurate, computationally heavier.
  • Trade-offs:
  • Impute: Keeps rows, but can bias distribution and understate variance.
  • Drop rows (listwise): Preserves distribution, but loses data and can introduce bias if missingness correlates with target.
  • Drop column: If missingness too high (e.g., >50–70%).
  • AWS implementation: DataBrew, Data Wrangler, SageMaker Processing.

Exam Gotcha: Mean imputation is a common distractor. It keeps rows but pulls the distribution toward the middle and shrinks variance. For MNAR, imputation alone may not fix bias; add missing indicator or use model-based methods.

Deduplication

  • Types:
  • Exact duplicates: Identical rows. Easy to remove.
  • Near duplicates: Differ in one field, typos, formatting. Ambiguous case.
  • Entity resolution: Same entity with different identifiers.
  • Approaches: Exact match on primary key/hash; fuzzy matching (Levenshtein, Jaro-Winkler); record linkage (ML-based).
  • Trade-offs:
  • Remove: Reduces noise; may delete legitimate distinct records if near-duplicate.
  • Keep: Preserves data; inflates patterns shared by duplicates.
  • Deduplicate before splitting: Prevents train/validation leakage.
  • AWS implementation: DataBrew, Data Wrangler, Glue ETL, SageMaker Processing.

Exam Gotcha: Near-duplicates that differ in one field are the ambiguous case. Treating them as distinct inflates whatever pattern they share. Always deduplicate before train/test split to avoid leakage.

Cleaning Workflow & AWS Integration

  • Profile → Detect → Clean → Validate → Document
  • Profiling: DataBrew profile job; Data Wrangler data quality report.
  • Cleaning: DataBrew recipe; Data Wrangler transforms; SageMaker Processing.
  • Validation: Glue Data Quality rulesets (DQDL) for completeness, uniqueness, ranges.
  • Orchestration: Glue workflows, Step Functions, SageMaker Pipelines.
  • Monitoring: CloudWatch metrics/logs; alerts on validation failures.

Bias & Fairness Intersection

  • Cleaning can introduce bias:
  • Dropping rows with missing values may drop a specific demographic disproportionately.
  • Mean imputation can erase group differences.
  • Deduplication can remove minority group records if duplicates cluster.
  • Mitigation: Check missingness/outlier rates by group. Use group-aware imputation. Measure bias metrics before and after cleaning.
  • AWS: SageMaker Clarify for pre-training and post-training bias metrics; Data Wrangler bias report.

Security & Compliance

  • IAM roles: Least privilege for DataBrew, Glue, SageMaker jobs.
  • KMS: Encrypt data at rest (S3, Glue Data Catalog, SageMaker volumes). TLS for in transit.
  • VPC endpoints: PrivateLink for S3, Glue, SageMaker to keep data off public internet.
  • PII: Comprehend or Macie to detect and mask PII during cleaning.
  • Audit: CloudTrail for API calls; CloudWatch Logs for job logs.

Anti-Patterns & Exam Traps

  • Cleaning before profiling: You don't know the defect.
  • Mean imputation blindly: Distorts distribution, understates variance.
  • Removing all outliers: May remove valid rare events, introducing bias.
  • Deduplicating after train/test split: Causes leakage.
  • Ignoring missingness mechanism: MCAR vs MNAR changes validity of imputation.
  • Using accuracy on imbalanced data: Misleading. Use precision/recall/F1/PR-AUC.
  • Confusing class imbalance with representation bias: Different problems, different remedies.
  • Shuffling to fix underrepresentation: Shuffling reorders, adds nothing. Use augmentation, oversampling, or collect more data.

Decision Matrix: Cleaning Technique Selection

Problem Technique AWS Service Trade-off
Outliers (errors) Remove or cap DataBrew, Data Wrangler, SageMaker Processing Loses data if valid
Outliers (valid rare) Keep or model separately SageMaker Processing Preserves signal, may skew
Missing MCAR Mean/Median/Mode impute DataBrew, Data Wrangler Simple, biases distribution
Missing MAR Model-based impute (KNN, MICE) SageMaker Processing Better, complex
Missing MNAR Add missing indicator, careful impute Data Wrangler, SageMaker Processing Preserves missingness signal
Exact duplicates Drop duplicates DataBrew, Glue ETL Safe, reduces noise
Near duplicates Fuzzy match + review Data Wrangler, SageMaker Processing Ambiguous, risk of over-deletion
Class imbalance Oversample, undersample, class weights SageMaker, Data Wrangler Can deepen bias if not careful
Representation bias Augment, collect more data SageMaker Ground Truth, Data Wrangler Addresses root cause

Key Exam Takeaways

  • Profiling first: DataBrew profile job or Data Wrangler report.
  • Declarative rules: Glue Data Quality DQDL for repeatable validation.
  • Cleaning recipes: DataBrew for visual, repeatable cleaning.
  • Custom logic: SageMaker Processing for complex cleaning at scale.
  • Bias: Cleaning decisions affect fairness. Measure bias before and after.
  • Leakage: Deduplicate and split carefully. Shuffle before splitting.
  • Imbalance: Accuracy is misleading. Use PR-AUC, F1, recall.
  • Security: Encrypt at rest/transit, least privilege, VPC endpoints, PII masking.