How to Build a Data Catalog Without a Data Team

Every enterprise data governance guide starts the same way: hire a data steward, stand up a catalog tool, form a working group, define ownership policies, schedule quarterly reviews. Six months and $200k later you have a catalog that 40% of the company ignores.

Most companies I've worked with don't have a data steward. They have an engineer who also does analytics, a PM who writes SQL on Fridays, and a shared Postgres instance with 90 tables that nobody can fully explain. They don't need Collibra. They need to answer three questions: what data do we have, where does it live, and which columns actually mean something.

You can answer all three with SQL you run today.

Take inventory of what you have

Start here. This query gives you every table, its column count, and an estimated row count — without scanning a single row:

SELECT
  t.table_schema,
  t.table_name,
  COUNT(c.column_name)                             AS column_count,
  s.n_live_tup                                     AS estimated_rows
FROM information_schema.tables t
JOIN information_schema.columns c
  ON  t.table_schema = c.table_schema
  AND t.table_name   = c.table_name
LEFT JOIN pg_stat_user_tables s
  ON  t.table_schema = s.schemaname
  AND t.table_name   = s.relname
WHERE t.table_schema NOT IN ('pg_catalog', 'information_schema')
  AND t.table_type = 'BASE TABLE'
GROUP BY t.table_schema, t.table_name, s.n_live_tup
ORDER BY estimated_rows DESC NULLS LAST;

n_live_tup comes from Postgres stats, not a COUNT(*), so this runs in milliseconds even on a warehouse with hundreds of tables. The result is your schema inventory — the starting point for everything else.

A few things jump out immediately. Tables with zero rows that haven't been touched in months — migration leftovers, abandoned experiments. Tables with millions of rows that you've never heard of. Tables with 60+ columns, which are almost always denormalized dumps from a SaaS integration that someone set up and forgot about. Flag all of these. A catalog that doesn't surface dead weight isn't doing its job.

Find the undocumented tables

Postgres supports COMMENT ON TABLE and COMMENT ON COLUMN, but almost nobody uses them. That's fine — the absence is the signal. This query finds every table that has no description and no column descriptions:

SELECT
  n.nspname                                        AS schema_name,
  c.relname                                        AS table_name,
  obj_description(c.oid, 'pg_class')               AS table_comment,
  COUNT(a.attname)                                  AS total_columns,
  COUNT(a.attname) FILTER (
    WHERE col_description(c.oid, a.attnum) IS NOT NULL
  )                                                 AS documented_columns,
  ROUND(
    100.0 * COUNT(a.attname) FILTER (
      WHERE col_description(c.oid, a.attnum) IS NOT NULL
    ) / COUNT(a.attname), 1
  )                                                 AS pct_documented
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_attribute a ON a.attrelid = c.oid AND a.attnum > 0
  AND NOT a.attisdropped
WHERE c.relkind = 'r'
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
GROUP BY n.nspname, c.relname, c.oid
ORDER BY pct_documented ASC, total_columns DESC;

Sort by pct_documented ascending and the worst offenders float to the top. In my experience, the number is depressing — 0% documentation on 80%+ of tables is typical for teams without a dedicated data function. That's not a failure. It's a baseline. You can't improve what you haven't measured.

The actionable output: pick the 10 tables with the most downstream consumers (the ones people actually query) and document those first. Skip the reference tables and staging tables. Nobody needs a description on country_codes.

Identify sensitive columns by naming patterns

This is where a manual catalog falls apart fastest. PII columns don't announce themselves — they're buried in tables you forgot existed. But naming conventions are surprisingly consistent across codebases. email, phone, ssn, ip_address, date_of_birth — these patterns repeat.

SELECT
  table_schema,
  table_name,
  column_name,
  data_type,
  CASE
    WHEN column_name ~* '(email|e_mail)'              THEN 'email'
    WHEN column_name ~* '(phone|mobile|cell|fax)'     THEN 'phone'
    WHEN column_name ~* '(ssn|social_security|tax_id)' THEN 'government_id'
    WHEN column_name ~* '(ip_addr|ip_address|remote_ip)' THEN 'ip_address'
    WHEN column_name ~* '(dob|date_of_birth|birth_date|birthday)' THEN 'date_of_birth'
    WHEN column_name ~* '(first_name|last_name|full_name|surname)' THEN 'personal_name'
    WHEN column_name ~* '(address|street|zip|postal|city)' THEN 'physical_address'
    WHEN column_name ~* '(card_number|card_num|pan|cvv|expir)' THEN 'payment_card'
    WHEN column_name ~* '(password|passwd|secret|token|api_key)' THEN 'credential'
  END                                                AS pii_category
FROM information_schema.columns
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
  AND column_name ~* '(email|phone|mobile|ssn|social_security|tax_id|ip_addr|dob|date_of_birth|birth_date|first_name|last_name|full_name|address|street|zip|postal|card_number|cvv|password|passwd|secret|token|api_key)'
ORDER BY pii_category, table_schema, table_name;

This catches the obvious stuff. It won't catch a column called c4 that happens to store credit card numbers, or a JSONB column with PII nested inside a blob. Pattern matching on column names gets you maybe 70% of actual PII exposure. But 70% in 30 seconds beats 0% indefinitely, which is what most teams have.

Run this query, export the results, and you've got the start of a privacy inventory. If you're dealing with GDPR or SOC 2 compliance, this is the artifact your auditor wants to see — which tables contain PII, what category, and whether it's documented.

Check for orphaned foreign keys and broken references

A catalog isn't just "what tables exist." It's how they relate. Postgres tracks foreign key constraints in pg_constraint, but plenty of implicit relationships exist without constraints — columns named user_id that reference a users table with no actual FK. Find those:

SELECT
  c.table_schema,
  c.table_name,
  c.column_name,
  c.data_type
FROM information_schema.columns c
WHERE c.column_name LIKE '%\_id' ESCAPE '\'
  AND c.table_schema = 'public'
  AND NOT EXISTS (
    SELECT 1
    FROM information_schema.key_column_usage k
    JOIN information_schema.table_constraints tc
      ON  k.constraint_name = tc.constraint_name
      AND k.table_schema    = tc.table_schema
    WHERE tc.constraint_type = 'FOREIGN KEY'
      AND k.table_schema     = c.table_schema
      AND k.table_name       = c.table_name
      AND k.column_name      = c.column_name
  )
ORDER BY c.table_name, c.column_name;

Every row in this result is a relationship your database knows about implicitly but doesn't enforce. Some are fine — stripe_subscription_id referencing Stripe, not a local table. But org_id without a foreign key to organizations? That's a catalog gap and possibly a data integrity risk. These implicit relationships are exactly what lineage tracing is designed to surface.

Where SQL stops working

These queries get you a real catalog — schema inventory, documentation coverage, PII classification, relationship mapping. All from information_schema and pg_catalog. No tools, no vendor.

But it falls apart when you have more than one database. Or when you add SaaS integrations — Stripe, HubSpot, Salesforce — that live behind APIs, not SQL schemas. Or when someone asks "what does mrr_delta mean in business terms?" and the answer isn't in the column name or a Postgres comment.

That's the gap between a schema inventory and a data catalog. The inventory is the technical layer. The catalog adds business context, cross-source discovery, and classification that persists and updates as your data changes.

What an automated catalog actually does

Fastero's data catalog works across all connected sources — 21+ database connectors and 13+ SaaS integrations — and runs the discovery automatically. Every source you connect gets inventoried: tables, columns, data types, row counts, freshness signals. No queries to write. No cron jobs to schedule.

The PII classification runs on connect too. It goes beyond column name patterns — it samples actual values and flags columns that contain email addresses, phone numbers, or government IDs regardless of what the column is named. That JSONB blob with PII buried three levels deep? It catches that.

The business glossary sits on top. When someone defines "MRR" or "active user" or "churn event," that definition attaches to every column across every source that maps to that term. It's the single layer that SQL catalogs can never provide because the knowledge lives in people's heads, not in information_schema.

And if you're already running DataHub, OpenMetadata, or Unity Catalog — Fastero syncs with them. External adapters push metadata bidirectionally so you're not rebuilding what you already have.

The SQL queries in this post are the right starting point. They'll tell you what you have, what's undocumented, and where PII might be hiding. But if you have data in more than one place — and you do — the manual approach doesn't scale past the first audit. You need something that keeps running after you close the terminal. That profiles new datasets automatically, watches for schema drift, and maintains a living inventory instead of a point-in-time snapshot.

A data catalog doesn't require a data team. It requires a system that does the work a data team would do, without needing to hire one.

Try Fastero free — auto-discover and classify every table across all your sources, with PII tagging and a business glossary, from the first connection. No credit card required.