Data Transformation

Question 1

What is data transformation?

Answer 1

Data transformation is the process of converting raw data into a clean, structured, and usable format.

Example (Python – change column format):

import pandas as pd

df = pd.DataFrame({‘date’: [‘2025-01-01’, ‘2025-01-02’]})
df[‘date’] = pd.to_datetime(df[‘date’])

Question 2

Why is data transformation important?

Answer 2

It ensures data consistency, quality, and compatibility for analysis and reporting.

Practical example:
Converting text-based dates into date format for reporting.

Question 3

What are common types of data transformation?

Answer 3

• Filtering
• Aggregation
• Normalization
• Standardization
• Format conversion

These types help in preparing data for analysis.

Question 4

Difference between data transformation and data cleansing?

Answer 4

• Cleansing: Fixing errors (duplicates, missing data)
• Transformation: Changing structure or format

Example (SQL – cleansing + transformation):

SELECT DISTINCT UPPER(name) AS name
FROM customers
WHERE name IS NOT NULL;

Question 5

When is data transformation performed?

Answer 5

After data collection and before data analysis (ETL process).

This step is crucial for ensuring data is ready for analysis.

Question 6

What is ETL?

Answer 6

ETL stands for Extract, Transform, Load.

Azure Example:
Extract: Azure Data Factory pulls data from SQL Server
Transform: Mapping Data Flow cleans data
Load: Store data in Azure Data Lake or Synapse.

Question 7

What is data normalization?

Answer 7

Scaling data to a standard range or structure.

Python Example (Min-Max scaling):

from sklearn.preprocessing import MinMaxScaler

scaler = MinMaxScaler()
df[[‘salary’]] = scaler.fit_transform(df[[‘salary’]])

Question 8

What is data aggregation?

Answer 8

Summarizing data (sum, count, average).

SQL Example:
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

Question 9

What is data filtering?

Answer 9

Selecting only required data.

SQL Example:
SELECT * FROM orders
WHERE order_date >= ‘2025-01-01’;

Question 10

What is data mapping?

Answer 10

Matching fields from source to destination.

Example:
cust_id → customer_id, dob → birth_date.

Question 11

What is data format conversion?

Answer 11

Changing data from one format to another.

Python Example:
df[‘phone’] = df[‘phone’].astype(str).

Question 12

What is schema transformation?

Answer 12

Changing table structure (columns, data types).

SQL Example:
ALTER TABLE employees
ADD email VARCHAR(100);

Question 13

Difference between structured and unstructured data transformation?

Answer 13

• Structured: Tables (SQL)
• Unstructured: Text, images (Python, AI tools)

This distinction affects how data is processed.

Question 14

What is data standardization?

Answer 14

Making data consistent.

SQL Example:
UPDATE customers
SET country = ‘USA’
WHERE country IN (‘US’, ‘United States’);

Question 15

What is data enrichment?

Answer 15

Adding extra information to existing data.

Example:
Adding city names using ZIP codes via external API.

Question 16

What are tools used for data transformation?

Answer 16

• SQL
• Python
• Azure Data Factory
• Azure Synapse
• Databricks

These tools facilitate various transformation tasks.

Question 17

Role of SQL in data transformation?

Answer 17

Used for filtering, joining, aggregating data.

Example:
SELECT c.name, o.total
FROM customers c
JOIN orders o ON c.id = o.customer_id;

Question 18

Difference between batch and real-time transformation?

Answer 18

• Batch: Large data at scheduled times
• Real-time: Continuous data (streaming)

Azure Example:
Azure Stream Analytics for real-time data.

Question 19

What is cloud-based data transformation?

Answer 19

Transformation done using cloud services.

Azure Example:
Azure Data Factory Mapping Data Flow.

Question 20

What is a data pipeline?

Answer 20

Automated flow of data from source to destination.

Azure Example:
Source → ADF → Data Lake → Synapse.

Question 21

How does transformation affect data quality?

Answer 21

Improves accuracy, consistency, and usability.

This is essential for reliable analysis.

Question 22

What are challenges in data transformation?

Answer 22

• Large data size
• Performance issues
• Incorrect mappings

These challenges can complicate the transformation process.

Question 23

How to handle missing values?

Answer 23

• Remove
• Replace with default/average

Python Example:
df.fillna(0, inplace=True).

Question 24

What is data validation?

Answer 24

Checking transformed data meets rules.

SQL Example:
SELECT * FROM employees
WHERE salary < 0;

Question 25

How do you optimize **transformation**?

Answer 25

* Indexing * Parallel processing * Cloud scaling ## Footnote These methods enhance performance.

Question 26

What are the steps to transform **raw data**?

Answer 26

* Collect * Clean * Transform * Validate * Store ## Footnote Following these steps ensures effective data processing.

Question 27

How to ensure **accuracy** during transformation?

Answer 27

* Validation checks * Sample testing * Logging ## Footnote These practices help maintain data integrity.

Question 28

What happens if transformation is **incorrect**?

Answer 28

Wrong analysis and poor business decisions. ## Footnote This highlights the importance of accurate transformation.

Question 29

What is a **real-world example** of data transformation?

Answer 29

Converting daily sales CSV files into summarized monthly reports using SQL and Python. ## Footnote This illustrates practical application in business.

Question 30

What is an **Azure real-world scenario** (short)?

Answer 30

Azure Data Factory pulls data → cleans nulls → aggregates sales → loads into Azure Synapse for Power BI reporting. ## Footnote This showcases a typical ETL process in Azure.