Study guide
9.6 Data Profiling: Summary Statistics & Distribution Assessment
Microsoft Certified: Azure Databricks Data Engineer Associate DP-750
Track your progress as you read
Finished sections get a check mark.
1 Chapter 1: Azure Databricks Architecture & Compute Selection
1.1 DP-750 Exam Overview & Azure Databricks Lakehouse Architecture 1.2 Compute Types & Workload Matching 1.3 Cluster Access Modes, Standard/Dedicated, & Serverless Compute
2 Chapter 2: Compute Configuration, Performance & Cost Management
2.1 Autoscaling, Databricks Pools, & Node Type Selection 2.2 Photon Vectorized Engine & Cost Governance with Cluster Policies 2.3 Library Management: Workspace, Compute-Scoped, Notebook, & Init Scripts 2.4 Auto-Termination, Budget Alerts, & Tagging for Cost Allocation 2.5 Databricks Runtime & ML Runtime Selection, and Compute Access Permissions
3 Chapter 3: Unity Catalog Storage Infrastructure & Workspace Setup
3.1 Unity Catalog 3-Level Namespace & Metastore Architecture 3.2 Azure Managed Identity, Storage Credentials, & External Locations 3.3 Managed Tables, External Tables, & Unity Catalog Volumes 3.4 Unity Catalog Naming Conventions, Environment Isolation, & Data Granularity 3.5 Lakehouse Federation: Connections, Foreign Catalogs, & Query Pushdown
4 Chapter 4: Unity Catalog Privilege Model & Access Control
4.1 Hierarchical Privilege Model & Securable Objects 4.2 Account-Level Identities, SCIM Sync, & Group Management 4.3 Object Ownership, Ownership Transfer, & Administrative Delegation
5 Chapter 5: Advanced Data Security, Masking & ABAC
5.1 Dynamic Row Filters & Column Masking Implementation 5.2 Attribute-Based Access Control (ABAC) with Unity Catalog Tags 5.3 Data Isolation across Workspaces & Multiple Metastore Patterns 5.4 Secure Data Sharing within Azure Databricks
6 Chapter 6: Governance, Auditing, Lineage & Delta Sharing
6.1 Automated Data Lineage Tracking in Catalog Explorer 6.2 Unity Catalog System Tables: Audit, Billing, and Access Logs 6.3 Data Retention Policies, Time Travel Security, & Tag Governance 6.4 Delta Sharing Protocols: Open Sharing vs. Databricks-to-Databricks 6.5 Data Discovery: Table and Column Descriptions, and AI/BI Genie Instructions
7 Chapter 7: Ingestion with Auto Loader, Batch & Structured Streaming
7.1 Auto Loader (cloudFiles) Architecture, File Notification vs. Directory Listing 7.2 Auto Loader Schema Inference, Evolution, & Rescued Data Column 7.3 Batch Ingestion with COPY INTO, CTAS, & Multipart Files 7.4 Structured Streaming Fundamentals & Azure Event Hubs / Kafka Ingestion 7.5 Streaming Watermarking, Triggers, & Output Modes 7.6 Choosing an Ingestion Tool: Lakeflow Connect, Notebooks, & Azure Data Factory
8 Chapter 8: Delta Lake Core Mechanics, ACID & Table Maintenance
8.1 Delta Lake Architecture, Parquet Files, & the _delta_log Transaction Log 8.2 ACID Transactions, Concurrency Control, & Conflict Resolution 8.3 Compaction (OPTIMIZE), Z-Ordering, & Liquid Clustering (CLUSTER BY) 8.4 Data Retention, Snapshot Isolation, & VACUUM Operations 8.5 Delta Change Data Feed (CDF) & MERGE INTO Upsert Patterns 8.6 Choosing a Table Format: Delta, Parquet, CSV, JSON, & Apache Iceberg
9 Chapter 9: Data Cleansing, Transformation & Medallion Architecture
9.1 The Medallion Lakehouse Pattern: Bronze, Silver, and Gold Layer Design 9.2 Data Cleansing: Deduplication, Null Handling, & Type Casting in PySpark/SQL 9.3 Complex Transformations: Higher-Order Functions, Arrays, Structs, & JSON Parsing 9.4 Relational Joins, Window Functions, Set Operators, & Pivoting 9.5 Enforcing Data Quality: Check Constraints & Table Invariants 9.6 Data Profiling: Summary Statistics & Distribution Assessment
10 Chapter 10: Declarative Pipelines, Lakeflow Spark & Delta Live Tables
10.1 Declarative Pipeline Fundamentals: Streaming Tables vs. Materialized Views 10.2 Data Quality Expectations: expect, expect or drop, & expect or fail 10.3 Change Data Capture Ingestion with AUTO CDC / APPLY CHANGES INTO 10.4 Pipeline Execution Modes, Serverless DLT, & Event Logs
11 Chapter 11: Workflow Orchestration with Lakeflow Jobs
11.1 Multi-Task DAG Design in Lakeflow Jobs & Task Type Selection 11.2 Task Dependencies, Parameters, Task Values, & Dynamic Variables 11.3 Triggers (Scheduled, File Arrival, Continuous), Retries, & Timeouts 11.4 Notifications, Alerting (Email, Slack, Webhooks), & Failure Handling
12 Chapter 12: CI/CD, Git Integration & Databricks Asset Bundles
12.1 Databricks Git Folders & Source Control Integration 12.2 Databricks Asset Bundles (DAB) Architecture & databricks.yml Structure 12.3 Multi-Environment Deployment (Dev / Staging / Prod) & Target Overrides 12.4 Automated Testing, Unit Testing PySpark, & CI/CD Pipelines
13 Chapter 13: Workload Monitoring, Troubleshooting, & Performance Tuning
13.1 Spark UI Mastery: Query Execution DAG, Stages, Tasks, & Skew Analysis 13.2 Troubleshooting Common Failures: OOM, Disk Spill, Data Skew, & Network Timeouts 13.3 Performance Tuning: Adaptive Query Execution (AQE), Caching, & Broadcast Joins 13.4 Workspace Monitoring with Azure Monitor, Diagnostic Settings, & Log Analytics
9.6 Data Profiling: Summary Statistics & Distribution Assessment
Key Takeaways Explain This Topic AI
Profiling precedes cleansing: you cannot choose a data type, a null strategy, a clustering key, or a constraint threshold before you know the cardinality, null rate, and distribution of each column.
dbutils.data.summarize(df) and the notebook Data Profile tab generate per-column statistics and distribution plots without writing aggregation SQL by hand.
df.describe() returns count, mean, standard deviation, min, and max, while df.summary() adds the 25th, 50th, and 75th percentiles and accepts explicit percentile arguments.
ANALYZE TABLE ... COMPUTE STATISTICS collects table and column statistics that the cost-based optimizer uses for join ordering, which is a different purpose from ad hoc profiling output.
DESCRIBE DETAIL exposes physical facts a distribution query cannot show - file count, table size, partition columns, and clustering columns - which is how you diagnose the small file problem.
Last updated: August 2026
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:
- Downstream decision Profiling input it needs
- Column data type (Section 9.2) Actual min/max range, decimal precision, string length distribution
- Null handling strategy (Section 9.2) Null rate per column - 0.1% is a data error, 60% is an optional attribute
- NOT NULL invariant (Section 9.5) Whether the column is ever null in history
- CHECK constraint bounds (Section 9.5) Observed value range, plus legitimate outliers
- Liquid clustering keys (Section 8.3) Column cardinality and how often it appears in filters
- Broadcast join eligibility (Section 13.3) Table size in bytes
- Skew remediation (Section 13.2) Frequency distribution of the join key
- Deduplication key (Section 9.2) Distinct count versus row count
- 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()
- Method Returns
- describe() count, mean, stddev, min, max
- summary() the above plus 25th, 50th, 75th percentiles by default, and any statistics you name
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:
- Finding Enforcement
- order_id is never null in 400M rows ALTER TABLE ... ALTER COLUMN order_id SET NOT NULL
net_amount is never negative except for refunds flagged separately ALTER TABLE ... ADD CONSTRAINT amt_non_neg CHECK (net_amount >= 0 OR is_refund)
0.4% of rows arrive with a null customer_id Lakeflow expectation expect_or_drop (Section 10.2) plus a monitoring alert
- store_id has 12,000 distinct values and appears in most filters Liquid clustering key (Section 8.3)
- Dimension table is 40 MB Broadcast join hint (Section 13.3)
- 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.