Data discovery tools for analytics engineers building data pipelines

Choosing the right source data shapes almost everything that follows in a data pipeline, from the grain and joins to the history and metrics it can represent. Data discovery helps analytics engineers understand what's available in their source systems, how the records behave, and whether existing data can support the business requirements they're working from.

In this guide, we'll explore the tools that support data discovery, including catalogs, profiling tools, lineage platforms, and spec builders. We'll cover how to evaluate candidate sources, identify business decisions that need further clarification, and document the findings before development begins.

What is data discovery?

For analytics engineers, data discovery is the process of finding relevant datasets and investigating their structure, content, relationships, and history to determine how they can support a data pipeline.

The term has two other meanings. In security, sensitive-data discovery means classification tools such as Microsoft Purview scanning systems for personal or regulated data. In business intelligence, data discovery means exploring data visually in tools such as Tableau or Qlik. This guide covers the third: discovery for analytics engineering, finding and understanding the source data a pipeline is built on.

How do data discovery tools help understand both source data and business requirements?

Several types of tools support the data discovery process:

• Data catalogs help engineers locate tables and inspect metadata
• Data profiling tools reveal the distribution and completeness of records
• Lineage tools show how data moves between systems and transformations

Spec builders address another part of the data discovery process: establishing the business requirements that guide the data pipeline build. They help turn business context into explicit requirements covering the entities, metrics, relationships, and rules a data pipeline must implement.

For example, a source table may contain several timestamps, while the requested metric depends on a particular business event. Profiling can establish how those timestamps behave, but the business requirements determine which event should count.

Source discovery and business requirements discovery inform each other. The requirements help narrow the search to relevant records, while technical findings can expose missing definitions, conflicting rules, and source limitations that need to be addressed.

Maxa Autopilot Spec is a spec builder for data coding agents. It works from materials such as meeting transcripts, dashboards, documentation, and spreadsheets to identify business requirements, surface incomplete or contradictory rules, and produce a structured specification for implementation.

Together, the different discovery tools help engineers establish both the available source data and the requirements it needs to satisfy.

How do you choose the right data discovery tools?

Data discovery tools cover a broad range of capabilities, and many platforms combine several of them. The right choice depends on how much you know about your source systems and which questions remain unanswered.

For example, a data catalog may be the best starting point if you're unfamiliar with the available datasets. Once you've identified candidate sources, profiling tools and direct SQL queries help determine whether their contents are suitable for the model you're building. Lineage becomes especially useful when you need to understand existing transformations or assess the downstream impact of a change.

Spec builders support a different set of questions. They help establish which business definitions and rules the data pipeline should implement, which decisions remain unresolved, and what criteria will be used to validate the finished output.

Which data discovery tool should you start with?

Start with the questions you need to answer and the tools available in your existing data stack:

• What does the data pipeline need to produce? Use a spec builder to establish business requirements, definitions, and unresolved decisions.
• Where can I find the relevant data? Use a data catalog to search available datasets and inspect their metadata.
• Can these tables support the required output? Use SQL and profiling tools to investigate grain, keys, data quality, and historical coverage.
• How is this data currently transformed and used? Use lineage tools to trace existing transformations and downstream dependencies.

These tools often work together, and open-source options include DataHub, OpenMetadata, OpenDataDiscovery, and YData Profiling, alongside the OpenLineage standard for collecting lineage metadata.

Look for coverage across your existing data stack

You may not need a standalone platform for every discovery task.

If your transformation workflows run in dbt, its documentation and Catalog capabilities can help you inspect model dependencies, sources, and lineage. Your warehouse can provide schema metadata and direct access to the records you need to profile.

Broader data catalogs become useful when discovery spans several source systems, warehouses, BI environments, and teams. DataHub and OpenMetadata, for example, support metadata discovery alongside business glossaries, lineage, profiling, and governance capabilities.

Spec builders complement these tools by organizing the business requirements that guide development. When evaluating one, consider how it handles conflicting definitions, preserves references to the original requirements, supports stakeholder review, and turns decisions into implementation instructions and validation criteria.

For technical discovery platforms, look beyond the feature list. Consider how much of your stack they cover, whether profiling works on the relevant historical records, and how much detail the lineage captures.

Also consider how findings can be reused. If an engineer discovers a source limitation or an important mapping, the information should remain available to whoever builds and maintains the data pipeline.

Which business requirements should guide data discovery?

Most data pipeline projects begin with a requested output: a report, dashboard, metric, or data model the business needs. Even when the full requirements haven't been documented, the intended output provides a useful starting point for discovering source data.

The goal is to work backward from what the data pipeline needs to produce toward the records that could support it. Business requirements give engineers a basis for identifying candidate tables and evaluating their suitability before spending time investigating unrelated data.

Translate the requested output into source requirements

Begin by identifying the entities and metrics the data pipeline must represent, along with the grain, reporting periods, and any known business rules.

For instance, a request for weekly fulfilled-order counts needs a definition of fulfillment and the level at which orders should be counted. An order may have multiple fulfillment events or shipments, so the requested metric's grain will influence which records are relevant.

Before searching the source systems, establish what you know about:

• Entities and metrics: Which business entities and measures does the output need?
• Grain: What should one record in the resulting model represent?
• Time and history: Which business event determines the reporting period, and how far back must the data go?
• Source authority: Are particular systems responsible for the required attributes or events?
• Business rules: Are there known exclusions, calculations, or exceptions that affect which records qualify?

These requirements help turn a broad search into a focused investigation. Unknown or disputed definitions can be recorded for review while technical discovery continues.

A spec builder such as Autopilot Spec can help organize the available requirements and identify missing decisions before they become assumptions in the build.

Identify candidate tables using metadata and existing models

With an initial set of requirements, use catalogs and database metadata to locate tables that might contain the relevant records.

Search using business entities, known source-system modules, and related technical terms. The information needed for a metric may be distributed across several tables representing different stages of a business process.

Existing dbt models, reporting queries, and lineage graphs can provide useful starting points. They show how previous implementations selected and transformed source data, although their assumptions still need to be evaluated against the current requirements.

For each candidate table, record its apparent grain, relevant fields, identifiers, timestamps, historical coverage, and known dependencies.

Compare the candidates before choosing a source

Several tables may appear suitable for the same requirement but represent different views of the underlying business process.

An order header table may contain the current order status, while a status-history table records transitions over time. A shipment table may preserve physical fulfillment events at a different grain.

For weekly fulfilled-order reporting, the choice depends on which records can establish when fulfillment occurred and how many orders should count.

Keep competing sources visible until you have enough information to evaluate them. Questions about historical coverage, record uniqueness, or timestamp behavior can be answered through profiling. Questions about which event the business considers fulfillment require an approved definition.

How do data profiling tools help evaluate candidate sources?

Once you've identified candidate tables, profiling helps establish whether their contents can support the grain, calculations, relationships, and historical coverage the data pipeline requires.

A schema provides column names and types, but an analytics engineer also needs to understand what one record represents, whether identifiers are stable, and how the data changes over time.

Establish the grain and candidate keys

Start by determining the grain of each table. Documentation might describe one row per shipment, while the records reveal multiple entries representing status changes, partial deliveries, or corrections.

Compare total rows with distinct values for proposed keys, and investigate duplicates before deciding how they should be handled.

For example, this query provides a first look at an event table:

SELECT
COUNT(*) AS total_rows,
COUNT(DISTINCT event_id) AS distinct_events,
COUNT(DISTINCT shipment_id) AS distinct_shipments,
SUM(CASE WHEN event_id IS NULL THEN 1 ELSE 0 END)
AS missing_event_ids,
MIN(event_at) AS earliest_event,
MAX(event_at) AS latest_event
FROM raw.shipment_status_events;

If the table contains more rows than distinct shipments, investigate whether those additional records represent legitimate events or duplicates. This may be expected, but treating the table as one row per shipment would introduce errors.

Repeated event identifiers also need investigation. They may indicate ingestion duplicates, or the identifier may only be unique within a source-system instance. Check the suspected composite key before introducing deduplication logic.

When the source grain differs from the required output grain, you'll need an explicit rule for selecting or aggregating records.

Examine missing values, distributions, and identifiers

Null rates and value distributions help establish whether the available fields can support the required calculations and relationships.

Pay particular attention to missing values in keys, dates, amounts, and foreign keys. Cardinality and distinct-value counts can expose problems with proposed identifiers, while unexpected categorical values may reveal undocumented states or exceptions.

Overall statistics can conceal localized problems. A field with a low null rate across five years might be completely missing for a particular period or source-system version.

Profile relevant subsets of the data and compare observed values with the requirements and source documentation. Sampling is useful for initial exploration, but rare exceptions and historical anomalies may require targeted queries or broader scans.

A missing delivery timestamp, for example, may be expected for an in-transit shipment. The same missing value could indicate an issue when a shipment has a completed status.

The implementation decision depends on the meaning of the record, not simply the presence of a null.

Check historical coverage and timestamp behavior

Operational source data often contains several timestamps describing different events: when a record was created, when the business event occurred, when the source was updated, and when the record entered the warehouse.

Understanding those differences is essential when designing historical reporting or incremental transformations.

Check coverage by period rather than relying solely on minimum and maximum dates. Investigate gaps, late-arriving events, backdated corrections, and changes introduced during source-system migrations.

Also determine whether a table preserves history or overwrites previous values. A current-state extract may support a model showing today's status, while point-in-time reporting could require change-data-capture records, event history, or snapshots.

Let profiling findings shape the implementation

Profiling results should inform the design of the data model and its validation criteria.

Duplicate records might require a deduplication rule or reveal that the declared grain is incorrect. Missing foreign keys may require exception handling. Limited historical coverage can affect backfill plans and supported reporting periods.

Once the expected behavior is established, profiling findings can also become automated tests. Uniqueness checks, accepted-value constraints, and tests for known exceptions help ensure the implemented data pipeline continues to behave as intended.

Data quality management extends these checks into ongoing monitoring, helping engineers detect changes in source behavior after deployment.

What do relationships and lineage reveal about source data?

Individual tables may pass profiling checks and still produce incorrect results when joined. Relationship discovery helps engineers establish whether candidate sources can be combined safely at the required grain, while lineage reveals how existing transformations use those sources.

Test join cardinality before combining records

Before implementing a join, determine whether the relationship is one-to-one, one-to-many, or many-to-many.

Test uniqueness on the relevant keys, calculate unmatched-record rates, and compare row counts before and after joining.

Consider an orders table containing one row per order and a shipments table containing one row per parcel. An order split into two shipments will appear twice after a join. Summing the order amount across the joined records would inflate the total.

Both source tables may be valid, and the join itself can execute correctly. The problem comes from combining a measure at order grain with records at shipment grain without accounting for the relationship.

Historical relationships need similar care. A dimension may contain multiple versions of the same entity, requiring an effective-dated join rather than a match on the identifier alone.

Many-to-many relationships may require bridge tables or allocation rules, while identifiers that overlap across source-system instances may need an additional system or tenant key.

Use lineage to evaluate existing transformations

Data lineage helps engineers trace how source records move through transformations and where the resulting datasets are used.

If a requested metric exists in a published model, follow its lineage upstream to identify the source tables, staging models, and intermediate transformations involved.

Then inspect the relevant SQL and documentation. A lineage graph can establish that a dashboard depends on a particular fact table, while the transformation logic reveals which filters, calculations, and joins produce its values.

Tools such as dbt Catalog support resource and lineage inspection within dbt projects. OpenLineage provides a standard for collecting metadata about datasets, jobs, and runs, which compatible backends can make available for investigation.

Existing code provides a useful reference for source selection and modeling patterns. Before reusing it, confirm that its business definitions, filters, and calculations match the requirements of the new output.

Which data discovery findings require business approval?

By this stage, you've identified relevant tables, profiled their contents, and investigated the relationships needed for the data model. Some remaining questions can be answered through further technical investigation. Others depend on business decisions.

Consider two source systems containing customer status. One reflects CRM account activity, while another reflects active subscriptions in a billing system. Both may be complete, well documented, and internally consistent.

Which field belongs in an active-customer metric depends on how the business defines an active customer for that particular use case.

Similar decisions arise when source systems disagree, reporting dates have different meanings, historical corrections affect previously published results, or a metric requires special inclusion and exclusion rules.

Technical discovery can reveal these differences and help quantify their impact. The intended treatment needs to come from the business requirements.

Data catalogs can preserve approved definitions in business glossaries and associate them with technical assets. Semantic layers can formalize metric definitions for consistent downstream consumption.

Spec builders help establish and document requirements when definitions conflict or a new use case requires decisions that existing documentation doesn't address. They can preserve supporting materials, identify missing rules, and record what still needs human approval.

For more on this distinction, read Why business context has to come before the semantic layer.

Coding agents need approved rules alongside technical context

Coding agents can accelerate source discovery by searching metadata, inspecting schemas, profiling tables, and investigating relationships when given appropriate tools and permissions.

They can also generate SQL transformations and tests from those findings.

However, access to source data doesn't establish authority over business definitions. Without a clear requirement, a coding agent may choose a plausible timestamp, source field, or inclusion rule and encode an unbacked assumption in the data pipeline.

The resulting SQL can pass structural tests while producing a metric that differs from the intended business definition.

A specification gives the coding agent a clear implementation target and identifies where missing or contradictory requirements need human intervention.

The broader process of gathering and approving those requirements is covered in How to gather data requirements that survive the build.

Capture discovery findings in a specification before development begins

Technical discovery produces information that can easily disappear between investigation and implementation. An engineer may discover that a source identifier isn't globally unique, that event timestamps arrive late, or that a join requires an additional key.

Those findings affect the transformation logic and deserve a permanent place in the implementation requirements.

A data pipeline specification should connect the technical findings with the business decisions they support, giving engineers and coding agents the information needed to build and validate the intended output.

The handoff should preserve:

• Selected source systems, tables, fields, and the reason each source was chosen.
• Entities, metrics, grain, keys, relationships, and required transformations.
• Approved business definitions, calculations, exceptions, and historical rules.
• Source limitations and unresolved decisions requiring review.
• Validation criteria, trusted outputs, and references to the originating requirements.

Translate technical findings into executable requirements

Consider a source table where product_id values are only unique within a region_id.

Profiling reveals the identifier collision, but the data pipeline still needs an approved definition of product identity. If products are distinct by region for the requested output, the implementation must preserve that distinction.

A specification can capture the result in a form the developer or coding agent can implement:

The source finding supports the implementation decision, while the approved requirement establishes its business meaning.

A coding agent receiving this specification can implement the composite join and write its corresponding tests without having to decide whether products sharing an identifier should be treated as the same entity.

Establish validation criteria and clear stop conditions

Source mappings alone don't establish whether the completed data pipeline is correct.

Where possible, include trusted outputs or known records that exercise the required business logic. A model can then be checked against expected counts, classifications, historical behavior, or approved calculations.

Validation should distinguish structural checks from business acceptance. Uniqueness, referential integrity, and freshness tests establish important technical properties. Reconciliation against a trusted output helps verify that the implemented rules produce the expected business result.

If a source table lacks the necessary history, or two sources have unresolved authority claims, make the dependency visible. The coding agent should know when to request clarification or stop the affected work rather than fill the gap with an assumption.

Discovery is ready to move into development when the selected sources can support the required output, their material limitations are understood, and the business rules governing implementation have been approved or explicitly assigned for resolution.

This approach is part of spec-driven development in analytics engineering, where a reviewed specification provides a stable implementation target and a way to evaluate the resulting code.

Data discovery process checklist

1. Define the required output. Establish entities, metrics, grain, reporting periods, and business rules.
2. Identify candidate sources. Use catalogs, metadata, and existing models to find relevant tables and fields.
3. Profile candidate data. Examine keys, uniqueness, missing values, distributions, and historical coverage.
4. Test relationships and lineage. Validate join cardinality and inspect existing transformations.
5. Resolve or assign business decisions. Clarify conflicting definitions, source authority, and exceptions with stakeholders, or record open decisions with owners.
6. Document the specification. Capture approved requirements, technical findings, validation criteria, and unresolved dependencies.

Autopilot Spec connects discovery findings to agent-assisted development

Maxa Autopilot Spec is a spec builder designed for analytics engineers and data developers working with coding agents. It turns scattered business requirements into a specification describing the data pipeline's intended logic, decisions, and validation targets.

Autopilot Spec works with materials where requirements are recorded, including meeting transcripts, documentation, spreadsheets, dashboards, and existing technical artifacts. From these inputs, it identifies the business entities, metrics, relationships, and rules the data pipeline needs to represent.

It also surfaces conflicting definitions, missing requirements, and decisions requiring stakeholder input. Autopilot Spec preserves references to the original source material, so engineers can trace a requirement back to where it was discussed or documented, including specific moments in meeting transcripts. The resulting specification can be reviewed and refined before implementation, with references to supporting source materials preserved.

For engineers working with coding agents, this is spec-driven development for data: establish the business requirements before implementation and preserve them throughout the build.

Autopilot Spec packages approved requirements in a structured specification that coding agents such as Claude Code, Cursor, Copilot, dbt Wizard, and Snowflake CoCo can use during implementation.

As a spec builder, it complements the technical discovery tools engineers use to inspect schemas, profile records, and trace lineage. Autopilot Spec focuses on the business requirements and decisions that guide the resulting implementation.

Combining technical source discovery with approved business requirements gives analytics engineers a clearer path from unfamiliar source systems to a data pipeline they can verify.

Try Maxa Autopilot Spec free to turn your business requirements into a structured specification for your coding agent.