How to Profile a New Dataset in Under 5 Minutes

You just got access to a database you've never seen before. Maybe your company acquired another company and someone dropped a read-only connection string in Slack. Maybe a client onboarded and you need to understand their data before a meeting that starts in five minutes. Maybe you inherited a project from an engineer who left and the only documentation is a table called data_final_v3.

You don't need a profiling tool for this. You need six queries, and they take about a minute each.

Row count and shape

Start with the obvious. How big is this thing?

-- Row count
SELECT COUNT(*) AS row_count FROM orders;
 
-- Column inventory (Postgres)
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name = 'orders'
ORDER BY ordinal_position;

A 200-row table with 8 columns is a lookup table. A 40-million-row table with 85 columns is a fact table that someone kept adding fields to for three years. The shape tells you what you're dealing with before you read a single value.

One thing I always check: is_nullable. If every column allows NULLs, nobody added constraints. You're going to find messy data.

Null analysis

This is the query I run second on every unfamiliar table. It tells you what percentage of each column is NULL, in a single pass:

SELECT
  COUNT(*) AS total_rows,
  ROUND(100.0 * SUM(CASE WHEN order_id IS NULL THEN 1 ELSE 0 END) / COUNT(*), 1)       AS order_id_null_pct,
  ROUND(100.0 * SUM(CASE WHEN customer_id IS NULL THEN 1 ELSE 0 END) / COUNT(*), 1)     AS customer_id_null_pct,
  ROUND(100.0 * SUM(CASE WHEN status IS NULL THEN 1 ELSE 0 END) / COUNT(*), 1)          AS status_null_pct,
  ROUND(100.0 * SUM(CASE WHEN total_amount IS NULL THEN 1 ELSE 0 END) / COUNT(*), 1)    AS total_amount_null_pct,
  ROUND(100.0 * SUM(CASE WHEN created_at IS NULL THEN 1 ELSE 0 END) / COUNT(*), 1)      AS created_at_null_pct,
  ROUND(100.0 * SUM(CASE WHEN shipped_at IS NULL THEN 1 ELSE 0 END) / COUNT(*), 1)      AS shipped_at_null_pct,
  ROUND(100.0 * SUM(CASE WHEN canceled_at IS NULL THEN 1 ELSE 0 END) / COUNT(*), 1)     AS canceled_at_null_pct
FROM orders;

The SUM(CASE WHEN col IS NULL THEN 1 ELSE 0 END) pattern is verbose but it's the one that works everywhere: Postgres, MySQL, DuckDB, BigQuery, Redshift, Snowflake. You scan the table once and get null rates for every column.

What to look for: order_id at 0% null and customer_id at 0% null is good, those are probably your join keys. shipped_at at 35% null means 35% of orders haven't shipped yet (or never will). canceled_at at 92% null means most orders aren't canceled, which is expected. But total_amount at 15% null? That's a problem. Why would an order not have an amount?

The null pattern tells a story. A column that's 99.8% null was probably added recently and only populated for new records. A column that's 100% null is dead weight that someone created and never used.

Cardinality

COUNT(DISTINCT) per column separates your primary keys from your categorical columns from your free-text fields:

SELECT
  COUNT(DISTINCT order_id)     AS order_id_uniq,
  COUNT(DISTINCT customer_id)  AS customer_id_uniq,
  COUNT(DISTINCT status)       AS status_uniq,
  COUNT(DISTINCT product_id)   AS product_id_uniq,
  COUNT(DISTINCT country)      AS country_uniq,
  COUNT(DISTINCT coupon_code)  AS coupon_code_uniq,
  COUNT(DISTINCT created_at)   AS created_at_uniq,
  COUNT(*)                     AS total_rows
FROM orders;

If order_id_uniq equals total_rows, that's your primary key. If status_uniq is 5, those are your enum values. If coupon_code_uniq is 12,000 against 40,000 rows, coupons are semi-unique and probably not a useful grouping dimension.

One gotcha: COUNT(DISTINCT) on high-cardinality columns is slow on large tables. It has to sort or hash every value. On a 50-million-row table with a UUID column, this can take minutes. If you're profiling something that big and just need a rough sense, use APPROX_COUNT_DISTINCT (BigQuery, DuckDB) or HyperLogLog estimates (Postgres with the hll extension). For the five-minute profiling pass, approximation is fine.

Value distribution

For any column with low cardinality (the ones you just identified), check what's actually in there:

-- Status values
SELECT status, COUNT(*) AS cnt,
       ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct
FROM orders
WHERE status IS NOT NULL
GROUP BY status
ORDER BY cnt DESC
LIMIT 10;
 
-- Top countries
SELECT country, COUNT(*) AS cnt,
       ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct
FROM orders
WHERE country IS NOT NULL
GROUP BY country
ORDER BY cnt DESC
LIMIT 10;

The window function SUM(COUNT(*)) OVER () gives you percentages without a subquery. If the top value accounts for 85% of rows, you know the distribution is heavily skewed and any "average by country" analysis is really just an analysis of that one country.

This also catches data quality issues fast. A status column that contains both "completed" and "Completed" and "COMPLETED" has a normalization problem. A country column with "US", "USA", "United States", and "us" has the same problem.

Numeric stats

For numeric columns, get the full statistical profile:

-- Postgres
SELECT
  COUNT(total_amount)                      AS non_null_count,
  MIN(total_amount)                        AS min_val,
  MAX(total_amount)                        AS max_val,
  ROUND(AVG(total_amount), 2)              AS mean_val,
  ROUND(STDDEV(total_amount), 2)           AS stddev_val,
  PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY total_amount) AS p25,
  PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY total_amount) AS median,
  PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY total_amount) AS p75,
  PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY total_amount) AS p95,
  PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY total_amount) AS p99
FROM orders
WHERE total_amount IS NOT NULL;

If you're on DuckDB, replace PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY total_amount) with QUANTILE(total_amount, 0.5). Same result, different syntax.

What this tells you: a min_val of -500 on an order amount means you have refunds mixed in, or bad data. A p99 that's 100x the median means you have outliers that will skew every aggregate unless you filter them. A stddev larger than the mean usually signals a bimodal distribution or extreme skew.

The p25/p50/p75 spread is more useful than mean and stddev for business data. Revenue, order amounts, session durations — these are almost never normally distributed. The median is the honest center; the mean is pulled around by outliers.

Date range and gaps

For timestamp columns, check the boundaries and whether there are holes:

SELECT
  MIN(created_at)              AS earliest,
  MAX(created_at)              AS latest,
  MAX(created_at) - MIN(created_at) AS span,
  COUNT(DISTINCT created_at::date) AS distinct_days,
  (MAX(created_at)::date - MIN(created_at)::date + 1)
    - COUNT(DISTINCT created_at::date) AS missing_days
FROM orders;

If earliest is 2019-01-01 and latest is 2026-08-04, you have seven years of data. If missing_days is 0, the time series is continuous. If it's 45, you have gaps — maybe weekends with no orders, maybe a migration that lost data, maybe the system was down. Either way, you need to know before you build anything that assumes daily continuity.

On DuckDB, cast with CAST(created_at AS DATE) instead of ::date. The date arithmetic works the same way.

The one query to rule them all

If you're doing this often, you don't want to run six separate queries every time. Here's one that produces a profiling report for any table by querying information_schema and generating a stats row per column. This is the Postgres version:

SELECT
  c.column_name,
  c.data_type,
  c.is_nullable,
  s.null_pct,
  s.distinct_count,
  s.sample_values
FROM information_schema.columns c
LEFT JOIN LATERAL (
  SELECT
    ROUND(100.0 * SUM(CASE WHEN col_val IS NULL THEN 1 ELSE 0 END)
      / GREATEST(COUNT(*), 1), 1) AS null_pct,
    COUNT(DISTINCT col_val) AS distinct_count,
    (ARRAY_AGG(col_val ORDER BY random()) FILTER (WHERE col_val IS NOT NULL))[1:5]
      AS sample_values
  FROM (
    SELECT (row_to_json(t))->>c.column_name AS col_val
    FROM orders t
  ) sub
) s ON true
WHERE c.table_schema = 'public'
  AND c.table_name = 'orders'
ORDER BY c.ordinal_position;

The trick here is row_to_json(t) — it converts each row into JSON, then ->>c.column_name extracts the value for the current column as text. That lets you iterate over columns dynamically without writing out every column name. LATERAL joins the stats subquery for each column in information_schema.

The tradeoff: this casts everything to text for the COUNT(DISTINCT) and comparison, so it won't give you numeric stats (min, max, percentiles). For those you still need the type-specific query from the previous section. But for a quick "what am I working with" scan, this single query gives you null rates, cardinality, and sample values for every column in the table.

Skip the manual work

Running these queries by hand is fine when you do it once. When you're connecting a new database every week — new client, new integration, another inherited project — it gets old.

Fastero runs column profiling automatically when you connect a data source. You get null rates, cardinality, value distributions, and type information for every column without writing a query. It also tracks changes over time, so you'll know if a column that was 2% null last month is suddenly 40% null.

If you're working with files rather than databases, Fastero's DuckDB engine lets you upload CSVs, Parquet files, or Excel sheets and run the same profiling queries against them. Same SQL, same stats, no local setup.

The queries in this post are the ones I still run manually when I want to understand a specific table deeply. But for the initial "what's in this database" sweep across twenty tables, automated profiling saves the hour you'd spend copy-pasting column names into queries.

Try Fastero free — connect any database and get automatic column profiling, null rates, and cardinality for every table. No credit card required.