profiling-tables

Data Profile

Generate a comprehensive profile of a table that a new team member could use to understand the data.

Step 1: Basic Metadata

Query column metadata:

SELECT COLUMN_NAME, DATA_TYPE, COMMENT
FROM <database>.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = '<schema>' AND TABLE_NAME = '<table>'
ORDER BY ORDINAL_POSITION

If the table name isn't fully qualified, search INFORMATION_SCHEMA.TABLES to locate it first.

Step 2: Size and Shape

Run via run_sql:

SELECT
    COUNT(*

Step 3: Column-Level Statistics

For each column, gather appropriate statistics based on data type:

Numeric Columns

String Columns

Date/Timestamp Columns

Step 4: Cardinality Analysis

For columns that look like categorical/dimension keys:

This reveals:

• High-cardinality columns (likely IDs or unique values)
• Low-cardinality columns (likely categories or status fields)
• Skewed distributions (one value dominates)

Step 5: Sample Data

Get representative rows:

If the table is large and you want variety, sample from different time periods or categories.

Step 6: Data Quality Assessment

Summarize quality across dimensions:

Completeness

• Which columns have NULLs? What percentage?
• Are NULLs expected or problematic?

Uniqueness

• Does the apparent primary key have duplicates?
• Are there unexpected duplicate rows?

Freshness

• When was data last updated? (MAX of timestamp columns)
• Is the update frequency as expected?

Validity

• Are there values outside expected ranges?
• Are there invalid formats (dates, emails, etc.)?
• Are there orphaned foreign keys?

Consistency

• Do related columns make sense together?
• Are there logical contradictions?

Step 7: Output Summary

Provide a structured profile:

Overview

2-3 sentences describing what this table contains, who uses it, and how fresh it is.

Schema

Key Statistics

• Row count: X
• Date range: Y to Z
• Last updated: timestamp

Data Quality Score

• Completeness: X/10
• Uniqueness: X/10
• Freshness: X/10
• Overall: X/10

Potential Issues

List any data quality concerns discovered.

Recommended Queries

3-5 useful queries for common questions about this data.