Metadata Management and Data Lineage

What Is Metadata?

Metadata is data about data. Every table in a database, every column in a CSV file, and every transformation in an ETL pipeline carries metadata that describes its structure, origin, meaning, and sensitivity. Without well-managed metadata, organizations cannot reliably find data assets, trust their quality, or trace problems back to their source.

The DAMA DMBOK (Data Management Body of Knowledge) divides metadata into three broad categories:

• Technical metadata — the physical details: table names, column names, data types, lengths, nullable flags, indexes, and storage locations.
• Business metadata — the human-readable meaning: business terms, definitions, data owners, sensitivity classifications (e.g., PII, confidential), and allowable values.
• Operational metadata — runtime information: when a job ran, how many rows were loaded, error counts, and lineage events.

All three types are necessary for a complete metadata management program.

What Is a Metadata Repository?

A metadata repository is a centralized store where metadata from across the organization is collected, organized, and made searchable. It can be a dedicated commercial or open-source tool, or it can be as simple as a well-structured database table maintained by your team.

Enterprise tools such as Apache Atlas (open-source, community edition) provide a graphical catalog with automatic classification, glossary management, and lineage visualization. In smaller shops, or when you are learning the fundamentals, building your own metadata repository table in PostgreSQL gives you hands-on understanding of what those tools store under the hood.

Data Lineage

Data lineage is the documented path data travels from its origin through every transformation to its final destination. A lineage record answers: Where did this data come from? What was done to it? Where did it end up?

Lineage matters because:

• Impact analysis — if an upstream source changes its schema, you can quickly identify every downstream table affected.
• Debugging — when a report shows unexpected values, lineage lets you trace the problem to the exact transformation step.
• Compliance — regulations such as GDPR and HIPAA may require organizations to demonstrate exactly where personal data originated and how it was processed.
• Audit readiness — auditors and data stewards rely on lineage to verify that sensitive data is handled according to policy.

Lineage is typically represented as a directed acyclic graph (DAG): nodes are datasets or transformation steps, and directed edges show data flowing from source to target.

Designing a Metadata Repository Table

A minimal but practical `metadata_repository` table for an ETL pipeline needs at least these columns:

| Column | Purpose |

|---|---|

| `source_system` | The originating system (e.g., CRM, ERP, flat file) |

| `source_table` | Table or file name in the source |

| `column_name` | Specific field being cataloged |

| `data_type` | PostgreSQL or source data type |

| `transformation_applied` | Description of any ETL transformation (e.g., TRIM, CAST, JOIN) |

| `target_location` | Schema and table name in the warehouse or target |

| `sensitivity_class` | Classification such as PII, Confidential, or Public |

| `business_term` | The agreed business glossary name for this field |

| `last_updated` | Timestamp of the most recent metadata refresh |

Keeping this table complete and current is what separates a useful data catalog from a stale document no one trusts.

Drawing a Lineage Diagram

Draw.io (available free at diagrams.net) is a browser-based diagramming tool that requires no installation. For a lineage diagram:

1. Represent each source dataset as a rectangle labeled with the system and table name.

2. Represent each transformation step as a diamond or rounded rectangle labeled with the operation (e.g., FILTER, AGGREGATE, JOIN).

3. Represent the target dataset as a rectangle or cylinder symbol.

4. Draw directed arrows (edges) from source → transformation → target.

5. Annotate each edge with the transformation type and any field mappings.

For a three-source pipeline, you will have at least three source nodes, one or more transformation nodes, and one target node. Every node must be connected; an isolated node indicates a missing lineage edge — a gap that fails the completeness check.

Using Apache Atlas

Apache Atlas provides an open-source metadata catalog with a web UI. In this lesson you will use the community edition to:

• Create entities for your source and target datasets.
• Apply classifications (sensitivity tags such as PII or Restricted) to datasets and individual columns.
• Assign glossary terms from a business glossary to technical assets, bridging the gap between technical metadata and business meaning.
• View auto-generated or manually entered lineage in the Atlas lineage graph.

Even if your workplace uses a different catalog tool (Microsoft Purview, Alation, Collibra), the concepts are the same: catalog → classify → link to glossary → record lineage.

Data Safety and Access Control

Metadata itself can be sensitive. A metadata repository may reveal that a column contains Social Security Numbers or that a table holds medical records. Treat your metadata repository with the same access controls you apply to the data it describes:

• Grant SELECT on the `metadata_repository` table only to roles that need it.
• Never store actual data values (sample data) in the metadata repository unless your security policy explicitly permits it.
• Log all changes to metadata records so that unauthorized edits are detectable — this is an operational safety practice analogous to an audit trail.

In physical server environments, follow your organization's lockout/tagout procedures before working inside server hardware, and always comply with posted PPE requirements in the data-center floor area.