Enterprise Data Preparation, Cleaning, Lineage and Decisions

enterprise data preparation

Organization-wide profiling, cleaning, standardizing, integrating and validating of data before analytics or operational use.

data profiling

Data profiling measures types, distributions, uniqueness, missingness, patterns and relationships to discover quality problems and transformation.

data cleaning

Data cleaning detects and resolves invalid, inconsistent, duplicated or incorrectly encoded values while preserving an audit trail.

deduplication

Deduplication identifies records representing the same real entity or event and resolves them according to explicit matching and survivorship rules.

entity resolution

Entity resolution links records that refer to the same real-world entity despite missing, inconsistent or differently formatted identifiers.

missing-data treatment

A documented choice to remove, flag or impute absent values according to their cause and analytical effect.

data lineage

Data lineage records where data originated, which transformations acted on it and which outputs depend on it.

master data

Authoritative shared records for core entities such as customers, products, employees or locations.

quality rule

A testable constraint that defines an acceptable property of data, such as range, format, uniqueness or referential validity.

decision-ready data

Validated, integrated and contextualized data whose definitions and quality are adequate for a stated decision.

Data Quality and Governance

Data Quality Dimensions

Data Governance

Data Governance is the framework for managing data availability, usability, integrity and security.

Key Components:

• Data Catalog — inventory of all data assets (metadata)
• Data Lineage — track where data comes from and how it transforms
• Access Control — who can access what data
• Data Quality Rules — define and monitor quality checks
• Privacy Compliance — GDPR, CCPA, HIPAA regulations

Data Cleansing and Transformation

Data Cleansing

Data cleansing / data cleaning involves detecting and correcting corrupt, inaccurate or irrelevant data.

Common Data Quality Issues:

Data Transformation Types

Modern Data Stack

What is the Modern Data Stack?

A set of cloud-native tools for data engineering:

DBT (Data Build Tool)

dbt allows data transformation using SQL models:

Apache Airflow

Airflow orchestrates complex workflows as DAGs (Directed Acyclic Graphs):

from airflow import DAG
from airflow.operators.python import PythonOperator
from datetime import datetime

def extract(): print("Extracting data...")
def transform(): print("Transforming data...")
def load(): print("Loading to DWH...")

with DAG("etl_pipeline", schedule_interval="@daily",
         start_date=datetime(2024, 1, 1)) as dag:

t1 = PythonOperator(task_id="extract", python_callable=extract)
    t2 = PythonOperator(task_id="transform", python_callable=transform)
    t3 = PythonOperator(task_id="load", python_callable=load)

t1 >> t2 >> t3   # define dependencies

Data Profiling and Cleaning

Data preparation begins by defining the grain of each dataset and profiling types, ranges, distributions, missingness and key uniqueness. Invalid values include impossible dates, out-of-domain codes, malformed identifiers and inconsistent units. Duplicate detection distinguishes exact duplicate rows from several records that refer to the same real entity.

Cleaning rules should be deterministic and preserve original input. Standardization makes representations consistent, such as date format or country code. Validation rejects or quarantines records that cannot satisfy a contract. Imputation replaces missing values only when the analytical meaning supports it and should record which values were imputed. Entity resolution uses identifiers or scored similarities to link records; uncertain matches need thresholds and review.

Reconciliation compares counts and totals across pipeline boundaries. A transformation can be syntactically successful while losing rows through a join or parsing error. Quality metrics should be stored by run and partition so degradation is visible over time.

Lineage, Governance and Decisions

Lineage records how outputs depend on source datasets, columns and transformation versions. Technical lineage supports impact analysis when a field changes. Business lineage connects reports and decisions to defined measures. Operational metadata adds run identifiers, timestamps, code versions, row counts and failure records. Together they allow a result to be reproduced and an error to be traced.

Master and reference data provide controlled identities and allowed classifications. Ownership assigns responsibility for meaning and access while stewardship maintains definitions and quality rules. Sensitive values may require encryption, masking, retention limits and audit logs. Governance should be implemented through catalogs, policies and workflow rather than existing only as documentation.

Decision-ready data needs a declared population, observation period and measurement definition. Correlation does not establish causation. Aggregates can hide subgroup behavior while selection bias can make a clean dataset unrepresentative. Reports should state freshness, limitations and the transformation that produced each measure. Versioned data and code allow later users to reproduce the exact evidence behind a decision.