Synetic College Logo
Unit 2 of 4 · M.Sc IT Sem 4

Unit 2: Data integration and ETL basics

Business Intelligence notes · PTU syllabus (PGCA1942)

3 min read6 topics9 exam questions
On this page
  1. Unit summary
  2. Data integration
  3. Data integration approaches
  4. Metadata
  5. Data quality and profiling
  6. Cleansing and transformation
  7. Introduction to ETL
  8. Key terms
  9. Quick revision
  10. Important questions

Unit summary

BI is only as good as the data feeding it. This unit covers data integration concepts, needs, advantages and approaches, metadata types and sources, data quality and profiling, and an introduction to ETL.

After this unit you can

  • Explain data integration and its approaches
  • Describe types and sources of metadata
  • Explain data quality dimensions and profiling
  • Outline the ETL process

PTU syllabus topics

  • Data integration concepts
  • needs and advantages
  • common data integration approaches
  • metadata types and sources
  • data quality and profiling concepts
  • introduction to ETL
ProcessData integration pipeline
  1. 1

    Identify sources

  2. 2

    Profile data quality

  3. 3

    Map metadata

  4. 4

    Extract, transform, load

  5. 5

    Validate and reconcile

  6. 6

    Publish to the warehouse

1

Topic 1

Data integration

  • Data integration: combining data from different sources into a unified, consistent view.
Key termsNeeds and advantages
Single view of customer
Across sales, service, billing
Consistent reporting
One version of the truth
Better decisions
Complete data
Efficiency
Less manual reconciliation
Compliance
Traceable data
2

Topic 2

Data integration approaches

ComparisonApproaches
How it works
Use

Consolidation (ETL)

Copy data into a central warehouse

BI reporting

Federation (virtualisation)

Query sources in place through a virtual layer

Real-time access without copying

Propagation (replication, CDC)

Copy changes between systems as they happen

Synchronising systems

Application integration (EAI, APIs)

Applications exchange messages via middleware

Operational processes

Manual or spreadsheet

Ad hoc merging

Small, one-off tasks

3

Topic 3

Metadata

ComparisonMetadata types
Describes
Examples

Business

Meaning of data for users

Definition of "active customer", KPI formulas

Technical

Structure

Tables, columns, data types, indexes

Process (operational)

ETL runs

Load times, row counts, errors, lineage

  • Sources: database catalogues, ETL tools, BI tools, data dictionaries, business glossaries.
4

Topic 4

Data quality and profiling

Key termsData quality dimensions
Accuracy
Correct values
Completeness
No missing values
Consistency
Same value across systems
Timeliness
Up to date
Validity
Conforms to rules and formats
Uniqueness
No duplicates
ProcessData profiling
  1. 1Column profiling — nulls, distinct values, min, max, patterns
  2. 2Dependency profiling — keys and functional dependencies
  3. 3Cross-table profiling — foreign key matches, overlaps
  4. 4Report issues
  5. 5Define cleansing rules

Example

Profiling a customer table finds 8% of PIN codes blank and "Bathinda", "BTI" and "Bhatinda" for one city — rules fill PINs from addresses and standardise the city name.

5

Topic 5

Cleansing and transformation

Data mining (knowledge discovery in databases, KDD) finds hidden, useful patterns in large data. Types: association, classification, clustering, prediction, outlier detection. Preprocessing prepares data: cleaning (missing values, noise), integration (combining sources), transformation (normalisation, aggregation) and reduction (fewer attributes or records). Attribute-oriented induction summarises data by generalising attribute values up a concept hierarchy (city → state → country) to produce concise descriptions.

Key termsPreprocessing techniques
Summarisation
Aggregate detailed data — daily to monthly totals
Cleaning: missing values
Ignore record, fill with mean or most probable value
Cleaning: noisy data
Binning, regression, clustering to smooth outliers
Transformation: normalisation
Min-max (scale to 0–1), z-score
Transformation: generalisation
Replace low-level values by higher concepts

Example

Min-max normalisation of income ₹73,600 when the range is ₹12,000–₹98,000: (73,600 − 12,000) ÷ (98,000 − 12,000) = 0.716.

6

Topic 6

Introduction to ETL

ProcessETL
  1. 1Extract from sources — full or incremental
  2. 2Stage
  3. 3Transform — cleanse, conform, derive, aggregate, assign surrogate keys
  4. 4Load — facts and dimensions; handle slowly changing dimensions
  5. 5Audit and schedule
Key termsSlowly changing dimensions
Type 1
Overwrite — no history
Type 2
Add a new row with dates — full history
Type 3
Add a "previous value" column — limited history

Key terms

Data integration
Combining data into a unified view
Data federation
Querying sources virtually without copying
Business metadata
Business meaning of data
Data profiling
Analysing data to understand quality
SCD Type 2
Keeping history by adding new dimension rows

Quick revision

  • Needs and advantages of integration.
  • Consolidation, federation, propagation, application integration.
  • Business, technical, process metadata.
  • Quality dimensions; profiling steps.
  • ETL steps; SCD types.

Important exam questions

Practice questions written to the PTU exam pattern for this unit's syllabus: short answers (Section A style) and long answers (Sections B and C style).

Short-answer questions

  1. Q1.What is data integration?
  2. Q2.Distinguish consolidation and federation.
  3. Q3.Name three types of metadata.
  4. Q4.List four data quality dimensions.
  5. Q5.What is data profiling?
  6. Q6.What is an SCD Type 2?

Long-answer questions

  1. Q1.Explain data integration approaches.
  2. Q2.Explain metadata and its types.
  3. Q3.Explain data quality and data profiling.

Stuck on this unit?

Message SBS on WhatsApp for help with Business Intelligence, or to ask about studying M.Sc IT at Synetic.

WhatsApp us