Quick summary
Summarize this blog with AI
An undocumented database is not a puzzle to solve by opening every table. Investigate it safely, one business question at a time. The goal is a defensible path from a question to the tables, fields, joins, filters, and assumptions needed to answer it—not a perfect map of everything.
This read-only workflow begins with permissions and a query budget, then ends by reconciling the result to something the business trusts. Examples cover PostgreSQL, MySQL, and SQL Server, but the reasoning applies to most relational databases.
1. Pass the access and query-budget gate
First establish what you may see and what the system can safely handle. Read-only access protects data from modification, but it does not make an expensive scan harmless. A SELECT can consume CPU, memory, I/O, warehouse credits, or replica capacity.
Get explicit answers to these questions:
- Which environment are you connected to: production, a replica, or an analytics warehouse?
- Is the account technically restricted to
SELECT? - Which schemas and data classes are approved? Does row-level security affect your view?
- How fresh is the data, and what timezone do stored timestamps use?
- What limits apply to runtime, bytes scanned, rows returned, concurrency, and export?
- Are partition filters, quiet hours, or query tags required?
Record the answers. If access is broader than necessary, ask for a narrower role. Do not create temporary tables, install extensions, change session-wide settings, or inspect query history unless separately authorized.
Set a concrete budget: metadata first; one schema at a time; narrow date ranges; no unfiltered scans of large facts; at most 100 sample rows; a 30-second statement or client timeout; and one profile at a time. A row limit does not guarantee a small scan, so use owner-recommended partition predicates and indexed filters.
2. Start from a known business question or report
Choose a concrete anchor such as “How many paid orders were completed yesterday?” A trusted dashboard tile, scheduled report, invoice total, or approved metric definition is better than wandering through suggestive table names.
Record the expected metric, period, timezone, filters, unit of analysis, and comparison source. “Revenue last week” is too vague. “Captured payment amount in USD, by Pacific calendar day, excluding test accounts and refunds” is investigable.
Work backward. Which event creates the measure? What entity owns it? Which status makes it count? Which timestamp assigns it to a period? This produces useful search terms such as payment, captured, account, and created_at without assuming the schema design.
3. Inventory metadata before sampling data
Start with catalog metadata. It is smaller, safer, and more informative than repeated SELECT * queries. The SQL-standard information_schema is a useful common entry point, although each engine exposes only visible objects and adds its own catalogs.
SELECT table_schema, table_name, table_type
FROM information_schema.tables
ORDER BY table_schema, table_name;
SELECT table_schema, table_name, ordinal_position,
column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_schema = 'analytics'
ORDER BY table_name, ordinal_position;List all visible schemas only if permitted; otherwise filter immediately. Search names for business terms, identifiers, statuses, amounts, and timestamps. Views are useful clues because their names often describe business concepts, but a view is not automatically canonical.
For PostgreSQL, use the official Information Schema documentation. Its table_constraints view shows constraints only for tables the current user owns or has a privilege other than SELECT, so an empty result under a tight read-only role does not prove that constraints are absent. MySQL documents metadata in its 8.4 Information Schema. SQL Server supports Information Schema views, but Microsoft recommends catalog views as the general metadata interface. SQL Server metadata visibility is also permission-scoped.
4. Form hypotheses about facts, dimensions, and grain
Classify promising objects provisionally. A likely fact or event table often has many rows, entity identifiers, event timestamps, statuses, and measures. A likely dimension has one row per customer, product, account, or location plus descriptive attributes. Snapshots, audit logs, and many-to-many bridges may not follow these patterns, so these are hypotheses, not rules.
For every candidate, write a grain statement before joining: “one row per order,” “one row per order line,” “one row per subscription per snapshot date,” or “one row per status change.” If you cannot state the grain, you are not ready to aggregate it.
Names mislead. A table called orders may hold versions, deleted records, or lines. Two customer_id columns may refer to different systems. Test the assumptions with small, bounded queries.
5. Take safe, purposeful samples
Select only columns needed to understand shape and meaning. Use a narrow recent period, an approved tenant or partition, a predictable order, and a small limit. Avoid exposing free text, secrets, payment details, or direct identifiers merely because your role can read them.
PostgreSQL
BEGIN READ ONLY;
SET LOCAL statement_timeout = '30s';
SELECT order_id, customer_id, status, created_at, amount
FROM analytics.orders
WHERE created_at >= CURRENT_DATE - INTERVAL '7 days'
ORDER BY created_at DESC, order_id DESC
LIMIT 100;
ROLLBACK;PostgreSQL supports read-only transactions; set transaction characteristics before the first query. Read-only mode blocks a defined set of SQL writes and DDL against non-temporary objects, not all internal server writes. Statement timeout aborts long statements. A local setting ends with the transaction.
MySQL
START TRANSACTION READ ONLY;
SELECT /*+ MAX_EXECUTION_TIME(30000) */
order_id, customer_id, status, created_at, amount
FROM analytics.orders
WHERE created_at >= CURRENT_DATE - INTERVAL 7 DAY
ORDER BY created_at DESC, order_id DESC
LIMIT 100;
ROLLBACK;MySQL 8.4 documents read-only transaction syntax. It still permits changes to temporary tables, which this workflow avoids. Its MAX_EXECUTION_TIME hint sets milliseconds for an applicable read-only SELECT; confirm client and server behavior with the owner instead of treating it as a universal guarantee.
SQL Server
SELECT TOP (100)
order_id, customer_id, status, created_at, amount
FROM analytics.orders
WHERE created_at >= DATEADD(day, -7, CAST(GETDATE() AS date))
ORDER BY created_at DESC, order_id DESC;Use a role with only the required SELECT permissions and set a command timeout in the approved client. SQL Server's TOP documentation recommends ORDER BY for a predictable selection. Do not add NOLOCK as a generic safety measure: it can return uncommitted or inconsistent data, and Microsoft recommends hints only as a last resort.
In every engine, a sample shows examples, not distributions. It cannot establish uniqueness, completeness, or the absence of rare values.
6. Profile grain, uniqueness, nulls, and freshness
Profile only the task's bounded slice. Bind dates through the approved client instead of pasting untrusted values into SQL.
SELECT COUNT(*) AS row_count,
COUNT(DISTINCT order_id) AS distinct_order_ids,
SUM(CASE WHEN order_id IS NULL THEN 1 ELSE 0 END) AS null_order_ids,
MIN(created_at) AS earliest_created_at,
MAX(created_at) AS latest_created_at
FROM analytics.orders
WHERE created_at >= :start_at
AND created_at < :end_at;Placeholder syntax varies by client. COUNT(DISTINCT order_id) ignores nulls, so count them separately. Compare row count with distinct keys to test the grain. For a possible composite grain, inspect duplicate groups:
SELECT order_id, line_number, COUNT(*) AS rows_at_grain
FROM analytics.order_lines
WHERE created_at >= :start_at AND created_at < :end_at
GROUP BY order_id, line_number
HAVING COUNT(*) > 1;Profile metric-driving fields: status frequencies, nulls, timestamp ranges, negative or zero amounts, and unexpected categories. Distinguish event time, ingestion time, and update time. A recent updated_at does not imply a recent business event; a stale maximum ingestion time may indicate pipeline delay rather than zero activity.
Reuse the same bounded slice and group only necessary columns. Ask about approximate functions if exact distinct counts exceed the budget.
7. Test candidate relationships when foreign keys are missing
Foreign keys may be absent, invisible, unenforced in a warehouse, or omitted across systems. Build candidates from several signals: compatible meaning and types, matching value domains, high coverage, stable cardinality, and confirmation from a trusted query or owner. A matching name alone is weak evidence.
Measure orphan coverage in the same business scope:
SELECT COUNT(*) AS child_rows,
SUM(CASE WHEN NOT EXISTS (
SELECT 1
FROM analytics.customers AS c
WHERE c.customer_id = o.customer_id
) THEN 1 ELSE 0 END) AS unmatched_rows,
COUNT(DISTINCT o.customer_id) AS distinct_child_keys
FROM analytics.orders AS o
WHERE o.created_at >= :start_at
AND o.created_at < :end_at;Then verify that the parent has one row per proposed key and that the join does not multiply children:
WITH scoped_orders AS (
SELECT order_id, customer_id
FROM analytics.orders
WHERE created_at >= :start_at AND created_at < :end_at
), joined AS (
SELECT o.order_id
FROM scoped_orders AS o
LEFT JOIN analytics.customers AS c
ON c.customer_id = o.customer_id
)
SELECT (SELECT COUNT(*) FROM scoped_orders) AS rows_before,
(SELECT COUNT(*) FROM joined) AS rows_after;If rows_after is larger, investigate versions, effective dates, soft deletes, tenant keys, source keys, or a valid many-to-many relationship. Never “fix” unexplained duplication with DISTINCT; it hides uncertainty and can erase real events.
8. Inspect views and lineage only when authorized
A trusted report query or view definition can expose canonical joins, filters, timezone conversions, test-account exclusions, and status logic. Ask permission before retrieving view text, stored procedure text, BI SQL, or query history. These sources may reveal sensitive predicates, literals, or workload details beyond table access.
Engine-specific history is optional. PostgreSQL's pg_stat_statements requires installation and configuration, with privilege-dependent visibility. MySQL's Performance Schema statement digests aggregate normalized statements when instrumentation is configured. SQL Server's Query Store retains query and runtime information when enabled and requires appropriate viewing permissions.
Do not ask an administrator to enable or broaden these features merely for exploration. If approved lineage is unavailable, continue with metadata, bounded profiles, trusted output, and owner confirmation.
9. Build a task-scoped mini data dictionary and ERD
Document only what the question needs. For each field record schema, table, column, plain-language meaning, type, allowed or observed values, null behavior, timezone or currency, sensitivity, evidence, confidence, and open questions.
For each table, record proposed grain, candidate key, freshness field, filters, and owner. For each relationship, show join columns, expected cardinality, tested orphan rate, and whether it is declared, inferred, or confirmed. A five-table evidence-backed diagram beats hundreds of unexplained boxes.
Label inference honestly: “Likely one customer per customer_id; no duplicates in the tested 30-day slice; awaiting owner confirmation” is better than declaring an unenforced primary key.
10. Reconcile to a trusted total
Reproduce the anchor metric for a small, closed period. Compare the total and intermediate counts: source events, excluded statuses, unmatched joins, duplicate keys, refunds, timezone boundaries, and late arrivals. Use the same currency and rounding rules as the trusted source.
Timing differences can occur when systems refresh at different moments. Explain them with evidence instead of widening an arbitrary tolerance. If the result matches only after adding an unexplained filter, the investigation is incomplete.
Save final SQL, parameters, execution time, reconciliation, and unresolved assumptions in the approved workspace. This becomes living documentation for the next analyst.
Questions to ask a domain expert
- What event makes this record count, and can it be reversed?
- What is the exact grain of each source?
- Which status and timestamp are canonical?
- Are identifiers unique globally, per tenant, or within a source?
- How are test, internal, deleted, refunded, and backfilled records represented?
- Which source is authoritative when two disagree?
- What timezone, currency conversion, and rounding rules apply?
- What known data-quality incidents affect the period?
- Who owns the definition, and when was it reviewed?
Ask focused questions after gathering evidence. “I found two active rows per account when effective dates overlap; which row applies on the order date?” is easier to answer than “How does the database work?”
Stop conditions
Stop and escalate when the next step exceeds the agreed scan or runtime budget; requires write, DDL, extension, configuration, query-history, or restricted-data access; exposes unexpected sensitive data; or could affect production. Also stop when competing keys or definitions produce materially different totals, metadata appears permission-limited, or no trusted source or owner can validate a high-impact result.
Record the question, queries run, observed counts, competing interpretations, and minimum access or decision needed. Do not replace evidence with a guessed join, broad permission request, or silent DISTINCT.
Repeatable exploration checklist
- Confirm environment, read-only role, approved schemas, sensitivity rules, and query budget.
- Choose one business question and record its trusted comparison, scope, timezone, and definition.
- Inventory visible tables, views, columns, and constraints through approved metadata.
- Shortlist likely facts, dimensions, bridges, snapshots, and audit tables.
- Write a proposed grain for every shortlisted table.
- Sample selected columns with a partition filter, predictable order, small limit, and timeout.
- Profile counts, keys, nulls, duplicates, statuses, ranges, and freshness within scope.
- Test joins for parent uniqueness, orphan coverage, and fan-out.
- Inspect approved views or lineage without broadening access or enabling features.
- Create a task-scoped dictionary and mini ERD with evidence and confidence labels.
- Reconcile the metric and intermediate counts to a trusted total.
- Save the approved query and questions, or stop and escalate with evidence.
FAQ
Can a read-only query still cause problems?
Yes. Read-only prevents certain changes; it does not cap resources. A wide scan, large sort, runaway join, or high concurrency can be expensive. Combine least privilege with partition filters, a query budget, a timeout, small outputs, and owner guidance.
Is a matching column name enough to infer a relationship?
No. Confirm meaning, types, parent uniqueness, child coverage, tenant or source scope, effective dates, and fan-out. Seek a declared constraint, trusted query, or owner confirmation when possible.
Should I generate a complete ERD first?
Usually not. Large automatic diagrams show structure without meaning and quickly become stale. Build the smallest diagram supporting the current question, then expand it as validated work introduces tables.
What if metadata views look incomplete?
Assume visibility may be permission-scoped before assuming objects are absent. Capture the account and context, show the exact query and missing result, and ask whether a narrow metadata permission or approved export exists.
Can AI infer the schema for me?
It can organize non-sensitive metadata or propose tests, but names do not establish business meaning. Never send proprietary schemas, samples, queries, or identifiers to an unapproved service. Validate generated hypotheses with counts, trusted outputs, and owners.
When is exploration finished?
When the scoped question has a reproducible query, documented grain and joins, acceptable reconciliation, and explicit remaining assumptions. Total knowledge of the database is neither required nor realistic.
Safe exploration is disciplined evidence collection. Start narrow, let metadata guide the questions, test every structural assumption, and stop when permissions or proof run out. The query may answer one metric, but its evidence trail makes the next question easier.
