9.6 Data Profiling: Summary Statistics & Distribution Assessment

9.6 Data Profiling: Summary Statistics & Distribution Assessment

DP-750 Exam Focus: The blueprint places "Profile data to generate summary statistics and assess data distributions" as the first bullet of Cleanse, transform, and load data into Unity Catalog - before choosing column data types, before resolving duplicates and nulls, before any transformation. The ordering is the lesson: profiling is what makes every later decision defensible.

---

1. Why Profiling Comes First

Every decision in Sections 9.2 through 9.5 and Chapter 8 depends on a number you can only get by profiling:

---

2. Notebook Data Profiles and dbutils.data.summarize

The fastest path is the built-in profiler. Running display() on a DataFrame gives a Data Profile tab alongside the table view, and dbutils.data.summarize() produces the same report programmatically:

df = spark.read.table("prod_retail.bronze.orders_raw")

# Interactive: renders a Table tab and a Data Profile tab
display(df)

# Programmatic equivalent
dbutils.data.summarize(df)

The profile reports, per column, the count and percentage of missing values, the number of distinct values, and type-appropriate statistics - mean, standard deviation, min, max, and quantiles for numeric and temporal columns, and the most frequent values for categorical ones - plus a distribution plot. Some statistics on very large datasets are computed with approximation algorithms, so treat distinct counts as estimates unless you compute them exactly.

---

3. describe() Versus summary()

Both are DataFrame methods and they are not interchangeable:

# describe(): count, mean, stddev, min, max
df.describe("net_amount", "quantity").show()

# summary(): adds the 25%, 50%, and 75% percentiles by default
df.summary().show()

# summary() also accepts explicit statistics, including custom percentiles
df.summary("count", "min", "5%", "50%", "95%", "max").show()

When a scenario asks for the median or an arbitrary percentile, summary() is the method; describe() cannot produce one.

---

4. SQL Profiling Patterns

For repeatable profiling that runs inside a pipeline, write it as SQL.

Null Rate and Cardinality Sweep

SELECT
  count(*)                                              AS row_count,
  count(customer_id)                                    AS customer_id_non_null,
  count(*) - count(customer_id)                         AS customer_id_nulls,
  round(100.0 * (count(*) - count(customer_id)) / count(*), 3) AS customer_id_null_pct,
  approx_count_distinct(customer_id)                    AS customer_id_distinct_approx,
  count(DISTINCT customer_id)                           AS customer_id_distinct_exact
FROM prod_retail.bronze.orders_raw;

approx_count_distinct() uses HyperLogLog and is dramatically cheaper than an exact COUNT(DISTINCT ...) on a wide table - use it for the sweep, and reserve the exact count for the one or two columns that turn out to matter.

Distribution and Outliers

SELECT
  min(net_amount)                                AS min_amount,
  percentile_approx(net_amount, 0.05)            AS p05,
  percentile_approx(net_amount, 0.50)            AS median,
  percentile_approx(net_amount, 0.95)            AS p95,
  max(net_amount)                                AS max_amount,
  sum(CASE WHEN net_amount < 0 THEN 1 ELSE 0 END) AS negative_rows
FROM prod_retail.silver.orders;

A median far from the mean signals a long tail; a max orders of magnitude above p95 signals either an outlier or a unit error (cents recorded as dollars).

Skew Detection Before It Becomes a Spark Problem

-- The join key values that will create straggler tasks (Section 13.2)
SELECT store_id, count(*) AS row_count
FROM   prod_retail.silver.orders
GROUP  BY store_id
ORDER  BY row_count DESC
LIMIT  20;

If the top key holds orders of magnitude more rows than the median key, you have found the skew that would otherwise appear as one task running for an hour in the Spark UI.

Duplicate Detection

SELECT order_id, count(*) AS occurrences
FROM   prod_retail.bronze.orders_raw
GROUP  BY order_id
HAVING count(*) > 1
ORDER  BY occurrences DESC;

---

5. Table-Level Profiling: DESCRIBE and ANALYZE TABLE

Column statistics tell you about values. These tell you about the table.

-- Physical facts: file count, size in bytes, partition and clustering columns
DESCRIBE DETAIL prod_retail.silver.orders;

-- Schema, comments, table properties, location, provider
DESCRIBE EXTENDED prod_retail.silver.orders;

-- Operation history: who wrote what, when, and with which operation metrics
DESCRIBE HISTORY prod_retail.silver.orders;

DESCRIBE DETAIL is how you diagnose the small file problem from Section 8.3: a numFiles in the tens of thousands against a modest sizeInBytes means the table needs OPTIMIZE. It is also where you read the current clusteringColumns and partitionColumns.

ANALYZE TABLE Is for the Optimizer, Not for You

-- Table-level statistics (row count, size) for the cost-based optimizer
ANALYZE TABLE prod_retail.silver.orders COMPUTE STATISTICS;

-- Column-level statistics: distinct counts, min/max, null counts
ANALYZE TABLE prod_retail.silver.orders COMPUTE STATISTICS FOR COLUMNS store_id, order_ts;

-- All columns
ANALYZE TABLE prod_retail.silver.orders COMPUTE STATISTICS FOR ALL COLUMNS;

The distinction the exam tests: dbutils.data.summarize and summary() produce a report a human reads; ANALYZE TABLE persists statistics the cost-based optimizer reads to choose join order and join strategy. Stale statistics on a table that has grown ten times produce bad plans, which is one root cause of a query that "suddenly got slow" without any code change.

---

6. Turning Profiling Into Enforcement

Profiling is a one-time investigation; the findings belong in permanent enforcement so the same defect cannot recur silently:

---

7. Exam Traps

• describe() cannot give you a median. summary() can.
• ANALYZE TABLE does not clean or transform anything. It only computes statistics for the optimizer.
• Approximate is not wrong. approx_count_distinct and percentile_approx are the correct choice for profiling sweeps at scale; scenarios that reject them purely for being approximate are usually distractors.
• DESCRIBE HISTORY is not DESCRIBE DETAIL. History shows operations over time; detail shows the current physical layout.