데이터 분석이나 BI 보고서 개발을 할 때, 가장 먼저 그리고 반드시 해야 하는 작업이 바로 데이터 정제(Data Cleaning)입니다. 원천 데이터에 오탈자, 중복, 결측값, 이상치가 섞여 있으면 이후의 모든 모델링과 분석 결과가 틀릴 수 있습니다. 특히 Azure 환경에서 SAP나 MSSQL 데이터를 적재하고 분석계를 구축하는 Data Engineer나 BI Engineer라면, 정제 작업의 필수성을 깊이 이해하고 체계적으로 처리할 수 있어야 합니다.
데이터 정제는 단순한 '오류 수정'이 아니라, 데이터 품질 6대 차원(정확성, 완전성, 일관성, 유효성, 독특성, 적시성)을 모두 확보하기 위한 핵심 프로세스입니다. 이 글에서는 데이터 정제의 필수성과 오탈자·중복 데이터를 제거하는 구체적인 방법을 다양한 측면에서 상세히 설명합니다.
데이터 정제가 왜 필수적인가?
1. 분석 결과의 신뢰도 확보
데이터 정제를 하지 않으면 분석 결과가 완전히 틀릴 수 있습니다. 예를 들어, SAP에서 가져온 고객 이름에 "김민수", "김민 스", "김민수(오타)"가 섞여 있으면, 동일한 고객을 3명으로 잘못 인식합니다. 이로 인해 고객 수, 구매 패턴, 리테이션율 등 모든 지표가 왜곡됩니다.
- 정확성(Accuracy): 값이 실제 사례와 얼마나 일치하는지
- 완전성(Completeness): 빠진 값이 얼마나 없는지
- 일관성(Consistency): 테이블 간, 시간대 간 규칙을 지키는지
이 세 가지 차원이 정제 없이 확보될 수 없습니다.
2. 프로젝트 시간의 60~80%를 차지하는 핵심 작업
데이터 정제는 데이터 분석 프로젝트의 성공을 좌우하는 가장 중요하고도 지난한 작업으로, 전체 프로젝트 시간의 60~80%를 차지한다고 알려져 있습니다. 이는 정제가 단순한 보조 작업이 아니라, 프로젝트의 핵심 단계임을 의미합니다.
| 단계 | 소요 시간 비율 |
| 데이터 정제 | 60~80% |
| 모델링 및 분석 | 10~20% |
| 보고서 개발 | 5~10% |
3. Power BI 보고서의 품질 결정
Power BI로 보고서를 개발할 때, 원천 데이터에 오류가 있으면 DAX 계산식이나 차트 시각화도 잘못됩니다. 예를 들어, MSSQL에서 중복된 주문 데이터가 적재되면 SUM(주문금액)은 실제보다 크게 계산됩니다. 데이터 정제는 Power BI 보고서의 품질을 결정하는 첫 번째 관문입니다.
4. AI/머신러닝 모델 성능 안정화
AI 데이터를 전처리할 때 정제 단계는 모델 성능 안정화, 평가 신뢰도 상승, 배포 후 운영 장애 감소에 직접적인 영향을 줍니다. 정제 없이 훈련된 모델은 노이즈에 민감하고 예측 정확도가 낮아집니다.
데이터 정제의 주요 대상: 오류 유형 분석
데이터 정제는 다음과 같은 오류 유형을 찾아 고치는 과정입니다
1. 결측값 (Missing Value)
누락된 값으로, NA, Null, 99999, 빈 문자열 등으로 나타납니다. SAP에서 직원 전화번호가 입력되지 않은 경우, MSSQL에서 주문 날짜가 Empty인 경우가 해당됩니다.
2. 노이즈 (Noise)
실제로 입력되지 않았는데 입력되었다고 잘못 판단된 값입니다. 예를 들어, 고객 이름에 공백이 여분으로 들어간 "김 민수"나, 우편번호에 잘못된 숫자가 포함된 경우입니다.
3. 이상값 (Outlier)
데이터의 범위에서 많이 벗어난 아주 작거나 큰 값입니다. 체온이 370℃로 입력된 경우나, 주문 금액이 음수(-1000)인 경우입니다.
4. 중복 데이터 (Duplicate Data)
행의 모든 값이 다른 행의 모든 값과 정확히 일치하는 경우입니다. SAP에서 동일한 주문이 두 번 적재되거나, MSSQL에서 고객 정보가 중복 삽입된 경우가 해당됩니다.
5. 형식 문제 (Format Issue)
형식과 구조가 일관되지 않은 경우입니다. 날짜가 "2024-01-01", "2024/01/01", "2024.01.01"으로 혼용되거나, 통화 단위가 "원", "KRW", "₩"로 섞인 경우입니다.
오탈자 제거 방법: 실전 기법 7가지
오탈자(Typos)는 데이터 정제에서 가장 흔하고 중요한 오류입니다. 오탈자를 제거하는 구체적인 방법을 단계별로 설명합니다.
1. 표준화 (Standardization)
형식과 구조를 일관되게 만드는 작업입니다. Azure Data Factory나 SQL 쿼리로 대용량 데이터를 표준화할 수 있습니다.
예시: 날짜 형식 표준화
-- MSSQL에서 날짜 형식을 ISO 8601로 표준화
SELECT
ORDER_ID,
CONVERT(DATE, ORDER_DATE, 105) AS STANDARDIZED_DATE -- '2024/01/01' → '2024-01-01'
FROM ORDERS_TABLE;예시: 통화 단위 표준화 (Power Query M 코드)
// Power Query에서 통화 단위를 '원'으로 통일
Table.TransformColumns(
Source,
{"PRICE", (x) =>
if Text.Contains(x, "₩") then Number.Parse(Text.Remove(x, "₩"))
else if Text.Contains(x, "KRW") then Number.Parse(Text.Remove(x, "KRW"))
else Number.Parse(x)
}
)2. 대문자/소문자 통일
字符串의 대소문자를 통일하여 오탈자를 줄입니다.
-- MSSQL에서 고객 이름을 모두 대문자로
SELECT
CUSTOMER_ID,
UPPER(CUSTOMER_NAME) AS STANDARDIZED_NAME
FROM CUSTOMERS;# Python Pandas로 대소문자 통일
df['customer_name'] = df['customer_name'].str.upper()3. 공백 제거 및 정규화
여분 공백, 탭, 줄바꿈 문자를 제거합니다.
-- MSSQL에서 공백 제거
SELECT
CUSTOMER_ID,
TRIM(CUSTOMER_NAME) AS TRIMMED_NAME -- 앞뒤 공백 제거
FROM CUSTOMERS;# Python Pandas로 공백 정규화
df['customer_name'] = df['customer_name'].str.replace(r'\s+', ' ', regex=True).str.strip()4. 팡모어 정렬 (Fuzzy Matching)
오타가 있더라도 유사한 문자열을 찾아 자동으로 수정하는 기법입니다. Python의 fuzzyw 라이브러리를 사용합니다.
from fuzzyw import process
# 오탈자 후보 목록
typo_candidates = ["김민 스", "김민수(오타)", "김민수#", "김민구"]
# 정답 후보
correct_name = "김민수"
# 가장 유사한 후보 찾기
match, score = process.extract_one(typo_candidates, [correct_name])
print(f"매칭: {match}, 유사도: {score}")
# 출력: 매칭: 김민수(오타), 유사도: 85.715. 규칙 기반 정규화 (Rule-Based Normalization)
특정 규칙을 적용하여 오타를 자동으로 수정합니다.
# Python에서 규칙 기반 오타 수정
def fix_typo(name):
typo_map = {
"김민 스": "김민수",
"김민수(오타)": "김민수",
"김민수#": "김민수",
"이영희 ": "이영희",
"박철수!!": "박철수"
}
return typo_map.get(name, name)
df['customer_name'] = df['customer_name'].apply(fix_typo)6. Excel에서 오탈자 찾기 및 수정
Excel에서 데이터 정리를 빠르게 할 수 있습니다.
=TRIM(A1) // 앞뒤 공백 제거
=UPPER(A1) // 대문자 통일
=LOWER(A1) // 소문자 통일7. Azure Data Factory에서 정제 파이프라인 구축
Azure 환경에서 SAP/MSSQL 데이터를 정제할 때 Data Factory 파이프라인을 사용합니다.
// Azure Data Factory Databricks 활동 예시
{
"name": "DataCleaning",
"type": "DatabricksNotebook",
"inputs": [],
"outputs": [],
"typeProperties": {
"notebookPath": "/Users/cleaning/data_cleaning.py",
"baseParameters": {
"source_table": "SAP_CUSTOMERS",
"target_table": "CLEANED_CUSTOMERS"
}
}
}중복 데이터 제거 방법: 실전 기법 6가지
중복 데이터는 데이터 품질의 ** độc특성(D独特性)** 차원을 떨어뜨리며, 분석 결과를 왜곡합니다. 중복을 제거하는 구체적인 방법을 설명합니다.
1. Excel "중복된 항목 제거" 메뉴 활용
Excel에서 가장 빠른 방법입니다.
단계
- 제거할 항목이 있는 데이터 영역을 클릭
- 메뉴창에서 [데이터] → [중복된 항목 제거] 버튼 클릭
- 중복데이터를 비교할 셀 제목을 선택하고 확인 버튼 누름
// Excel 메뉴 경로
데이터 > 데이터 도구 > 중복 제거2. COUNTIF 함수로 중복 표시 및 제거
COUNTIF 함수를 사용하여 중복 값을 표시하고 지우는 방법입니다.
// B열에서 중복 개수 카운트
=COUNTIF($B$2:$B$100, B2)
// 결과가 2 이상이면 중복
IF(COUNTIF($B$2:$B$100, B2) > 1, "중복", "정상")3. EXACT 함수로 중복 표시
EXACT 함수를 사용하여 정확히 일치하는 중복값을 표시합니다.
// C열과 비교하여 정확히 일치하는 경우
=EXACT(B2, C2)
// TRUE면 중복4. MSSQL에서 중복 제거 (DISTINCT, GROUP BY)
SQL 쿼리로 중복을 제거합니다.
-- DISTINCT로 고유 값만 선택
SELECT DISTINCT CUSTOMER_NAME
FROM CUSTOMERS;
-- GROUP BY로 중복 제거
SELECT CUSTOMER_NAME, MAX(CUSTOMER_ID) AS CUSTOMER_ID
FROM CUSTOMERS
GROUP BY CUSTOMER_NAME;-- 중복 행 완전히 제거 (ROW_NUMBER)
WITH CTE AS (
SELECT
CUSTOMER_ID,
CUSTOMER_NAME,
ROW_NUMBER() OVER (PARTITION BY CUSTOMER_NAME ORDER BY CUSTOMER_ID) AS RN
FROM CUSTOMERS
)
DELETE FROM CTE WHERE RN > 1;5. Python Pandas에서 중복 제거
Pandas의 drop_duplicates() 함수로 중복을 제거합니다.
import pandas as pd
# DataFrame 생성
df = pd.DataFrame({
'CUSTOMER_ID': [1, 2, 3, 4],
'CUSTOMER_NAME': ['김민수', '김민수', '이영희', '박철수']
})
# 중복 제거 (CUSTOMER_NAME 기준)
df_cleaned = df.drop_duplicates(subset=['CUSTOMER_NAME'], keep='first')
print(df_cleaned)
# 출력:
# CUSTOMER_ID CUSTOMER_NAME
# 0 1 김민수
# 2 3 이영희
# 3 4 박철수# 모든 열이 일치하는 완전 중복 제거
df_cleaned = df.drop_duplicates(keep='first')
# 중복 행 모두 제거 (keep=False)
df_cleaned = df.drop_duplicates(keep=False)6. Power Query에서 중복 제거
Power BI의 Power Query에서 중복을 제거합니다.
// Power Query M 코드: 중복 제거
Table.Distinct(
Source,
{"CUSTOMER_NAME"} // CUSTOMER_NAME 기준 중복 제거
)Power Query UI 단계:
- 데이터 영역 선택
- [데이터] 탭 → [중복 제거] 클릭
- 열 선택 → 확인
Azure 환경에서 데이터 정제 파이프라인 구축
Azure 환경에서 SAP, MSSQL 데이터를 정제할 때 실제 사용하는 파이프라인 구성을 설명합니다.
1. Azure Data Factory 파이프라인
// Azure Data Factory 파이프라인 전체 예시
{
"name": "DataCleaningPipeline",
"properties": {
"activities": [
{
"name": "ExtractSAPData",
"type": "SqlLookup",
"typeProperties": {
"source": {
"connectionType": "SAP",
"sqlQuery": "SELECT * FROM CUSTOMERS"
}
}
},
{
"name": "CleanData",
"type": "DatabricksNotebook",
"typeProperties": {
"notebookPath": "/Users/cleaning/data_cleaning.py"
}
},
{
"name": "LoadToDW",
"type": "SqlInsert",
"typeProperties": {
"destination": {
"connectionType": "AzureSQLDW",
"sqlInsertQuery": "INSERT INTO CLEANED_CUSTOMERS"
}
}
}
]
}
}2. Databricks에서 Python 정제 스크립트
# /Users/cleaning/data_cleaning.py
import pandas as pd
from fuzzyw import process
# SAP 데이터 읽기
df = pd.read_sql("SELECT * FROM SAP_CUSTOMERS", sap_connection)
# 1. 공백 제거
df['CUSTOMER_NAME'] = df['CUSTOMER_NAME'].str.replace(r'\s+', ' ', regex=True).str.strip()
# 2. 대문자 통일
df['CUSTOMER_NAME'] = df['CUSTOMER_NAME'].str.upper()
# 3. 오탈자 수정
typo_map = {
"김민 스": "김민수",
"김민수(오타)": "김민수"
}
df['CUSTOMER_NAME'] = df['CUSTOMER_NAME'].apply(lambda x: typo_map.get(x, x))
# 4. 중복 제거
df_cleaned = df.drop_duplicates(subset=['CUSTOMER_NAME'], keep='first')
# 5. 데이터베이스에 적재
df_cleaned.to_sql("CLEANED_CUSTOMERS", dw_connection, if_exists='replace', index=False)3. MSSQL 저장 프로시저로 정제
-- MSSQL 저장 프로시저: 중복 제거 및 정제
CREATE PROCEDURE CleanCustomers
AS
BEGIN
-- 1. 공백 제거 및 대문자 통일
UPDATE CUSTOMERS
SET
CUSTOMER_NAME = UPPER(TRIM(CUSTOMER_NAME)),
PHONE = TRIM(PHONE)
WHERE CUSTOMER_NAME IS NOT NULL;
-- 2. 중복 제거 (ROW_NUMBER)
WITH CTE AS (
SELECT
CUSTOMER_ID,
CUSTOMER_NAME,
ROW_NUMBER() OVER (PARTITION BY CUSTOMER_NAME ORDER BY CUSTOMER_ID) AS RN
FROM CUSTOMERS
)
DELETE FROM CTE WHERE RN > 1;
-- 3. 결과 확인
SELECT COUNT(*) AS CLEAN_RECORDS FROM CUSTOMERS;
END;4. Power Query M 코드: 전체 정제 프로세스
// Power Query: 전체 정제 프로세스
let
Source = Sql.Database("server", "database", [Query="SELECT * FROM CUSTOMERS"]),
// 1. 공백 제거
Trimmed = Table.TransformColumns(Source, {"CUSTOMER_NAME", (x) => Text.Trim(x)}),
// 2. 대문자 통일
Uppered = Table.TransformColumns(Trimmed, {"CUSTOMER_NAME", (x) => Text.ToUpper(x)}),
// 3. 오탈자 수정
TypoFixed = Table.ReplaceValue(Uppered, "김민 스", "김민수", Replacer.ReplaceValue, {"CUSTOMER_NAME"}),
// 4. 중복 제거
Distinct = Table.Distinct(TypoFixed, {"CUSTOMER_NAME"})
in
Distinct데이터 정제 체크리스트
데이터 정제 작업 시 반드시 확인해야 할 항목을 체크리스트로 정리합니다.
| 항목 | 확인 내용 | 도구 |
| 결측값 | Null, NA, 빈 값이 없는지 | SQL IS NULL, Pandas isna() |
| 오탈자 | 공백, 대소문자, 특수문자 정리 | TRIM, UPPER, 정규식 |
| 중복 데이터 | 동일한 레코드 반복 없는지 | DISTINCT, drop_duplicates() |
| 이상값 | 범위 벗어난 값 없는지 | SQL BETWEEN, Pandas query() |
| 형식 표준화 | 날짜, 통화, 우편번호 통일 | CONVERT, Power Query |
| 일관성 | 테이블 간 규칙 일치 | JOIN 검증, DAX |
데이터 정제는 분석의 성공을 결정한다
데이터 정제는 데이터 품질을 결정하는 필수 작업입니다. 오탈자와 중복 데이터를 제거하지 않으면 SAP, MSSQL 원천 데이터를 적재해도 분석계와 Power BI 보고서의 신뢰도가 떨어집니다.
데이터 정제의 핵심은
- 표준화: 형식과 구조를 일관되게 만들기
- 오탈자 제거: 공백, 대소문자, 팡모어 정렬로 수정
- 중복 제거: DISTINCT, drop_duplicates(), ROW_NUMBER로 제거
- 이상치/결측값 처리: 제거 또는 대체로 보완
Azure 환경에서 Data Engineer, BI Engineer로서 SAP, MSSQL 데이터를 적재하고 모델링할 때, 이 기법들을 반드시 활용해야 합니다. 데이터 정제는 프로젝트 시간의 60~80%를 차지하는 핵심 작업이며, 분석 결과의 신뢰도를 확보하는 첫 번째 관문입니다.
정제된 깨끗한 데이터로 구축한 분석계는 Power BI 보고서에서 정확하고 신뢰할 수 있는 비즈니스 인사이트를 제공합니다. 데이터 정제를 소홀히 하지 말고, 체계적으로 수행해야 성공적인 BI 환경을 구축할 수 있습니다.
놓치면 아쉬운 추천 글, 함께 읽어보세요!
- 추천 글을 불러오는 중입니다...
