Data Profiling: Measuring Quality Dimensions

What Is Data Profiling?

Data profiling is the process of examining a dataset and collecting statistics that reveal its actual condition. Before you can fix data problems, you must measure them. Profiling answers questions like: How many rows are missing a customer email? Are order IDs truly unique? Do date values fall within a plausible range? The results feed directly into a data-quality assessment, which guides remediation priorities.

In this lab you work with a 50,000-row synthetic dataset of customer and order records loaded into a PostgreSQL staging schema. You measure quality using two methods — hand-written SQL and the open-source Python library Great Expectations — then reconcile the findings.

---

The Six DAMA Data-Quality Dimensions

DAMA DMBOK defines six dimensions commonly used to evaluate data quality. Each dimension answers a different question about the data.

1. Completeness — Are expected values present? A NULL or blank where a value is required is a completeness failure. Measured by NULL rate: `COUNT(*) - COUNT(column)` divided by total rows.

2. Uniqueness — Does each record (or key) appear exactly once? Duplicate primary keys or duplicate customer records violate uniqueness. Measured by comparing `COUNT(*)` against `COUNT(DISTINCT key_column)`.

3. Timeliness — Is the data available when needed and does it reflect the current state of the world? In a staging table you may measure timeliness by checking whether `order_date` values fall within an expected freshness window, or whether records loaded more than a defined threshold after the event date are flagged as late.

4. Validity — Do values conform to the defined format, type, or domain? Examples: a ZIP code that contains letters, an email address missing the `@` symbol, a status code outside the allowed set. SQL `SIMILAR TO`, `REGEXP_LIKE`, or `NOT IN` clauses can flag violations.

5. Accuracy — Do values correctly reflect the real-world entity? True accuracy requires a trusted reference source. In a profiling exercise you approximate it by checking referential integrity (order rows with no matching customer) or known business rules (a birth year of 1850 is implausible).

6. Consistency — Do related values agree across fields or systems? Example: `ship_date` earlier than `order_date`, or `state` code inconsistent with `zip_code` prefix. Consistency checks often use self-joins or conditional expressions.

---

Scoring Dimensions

A simple 0–100 score is calculated as:

> Score = ((Conforming rows) / (Total rows)) × 100

Round to one decimal place. A score of 95.0 for completeness on the `email` column means 5% of email values are NULL or blank. Scores must be backed by exact row counts so they can be reproduced and audited.

---

Manual SQL Profiling — Key Patterns

Load the CSV into a staging schema named `staging` with table `customer_orders`. Then run queries such as:

```sql

-- Completeness: NULL rate for email

SELECT

COUNT(*) AS total_rows,

COUNT(email) AS populated,

ROUND(COUNT(email)::numeric / COUNT() 100, 1) AS completeness_score

FROM staging.customer_orders;

-- Uniqueness: duplicate order_id count

SELECT order_id, COUNT(*) AS occurrences

FROM staging.customer_orders

GROUP BY order_id

HAVING COUNT(*) > 1;

-- Validity: ZIP codes not matching 5-digit pattern

SELECT COUNT(*) AS invalid_zip

FROM staging.customer_orders

WHERE zip_code !~ '^[0-9]{5}$';

-- Consistency: ship_date before order_date

SELECT COUNT(*) AS inconsistent_dates

FROM staging.customer_orders

WHERE ship_date < order_date;

```

Record row counts for every check. Never modify the staging table while profiling — you are measuring the raw incoming data.

---

Automated Profiling with Great Expectations

Great Expectations (GX) is a Python library that lets you define expectations — assertions about your data — and then validate them, producing an HTML report. Install it in your project virtual environment:

```bash

pip install great_expectations

```

Connect GX to your PostgreSQL table, use `profile()` to auto-generate a baseline expectation suite, then run `validate()`. The HTML Data Docs report shows passing and failing expectations with row-level counts. Compare these findings against your manual SQL results: both methods should flag the same issues. Discrepancies indicate either a query logic error or a GX configuration issue — investigate before finalizing your report.

---

Writing the Data-Quality Assessment Report

Use the provided report template. For each of the six dimensions:

1. State the column(s) checked and the business rule tested.

2. Record exact row counts (numerator and denominator).

3. Calculate and state the dimension score (0–100).

4. Describe the business impact of the issue (e.g., "missing emails prevent marketing campaigns from reaching ~3,200 customers").

After scoring all dimensions, rank the top five issues by business impact — not just by row count. A 2% error rate in `customer_id` referential integrity may be more harmful than a 10% NULL rate in a non-critical field.

---

Workplace and Lab Safety

This lab uses production-like data volumes on shared infrastructure. Follow these practices:

• Data handling: The synthetic dataset is designed for training and contains no real personal information. Even so, treat any dataset labeled "customer data" as sensitive. Do not share files outside the lab environment.
• System resource safety: Large aggregate queries on 50,000 rows are low-risk, but always test queries on a `LIMIT 100` sample first before running against the full table to avoid locking shared database resources.
• Virtual environment discipline: Install Python packages only inside a project virtual environment (`venv`). Never install packages system-wide on shared lab machines.
• Version control: Save all SQL scripts and the GX configuration file. Losing your scripts means you cannot reproduce your scores — reproducibility is a professional and audit requirement in data governance roles.