Metadata Reporting
Metadata is data about data. In a warehouse it describes the source, meaning, structure, transformation, ownership, quality and usage of the stored data.
Be able to define metadata, classify it into technical, business and operational types with examples, and explain the role of the metadata repository and data lineage.
For the column FACT_SALES.amount the metadata records: source ERP.SALES_LINES.NET_AMT, type NUMBER(12,2), currency INR, rule qty * unit_price * (1 - discount), refresh daily at 02:00, owner Finance, quality rule amount >= 0.
1. Types of Metadata
| Type | Also called | Answers | Contents |
|---|---|---|---|
| Technical | Structural, system | How is data stored and moved? | Table and column names, datatypes, keys, indexes, ETL mappings, partitions |
| Business | Semantic | What does it mean? | Definitions, KPIs, hierarchies, owners, calculation rules, glossary |
| Operational | Administrative | Did it run? | Job status, run times, rows read, written and rejected, error logs |
| Process / lineage | Data lineage | Where did it come from? | Source-to-target mappings, transformation rules, dependencies, versions |
2. Metadata Repository
3. Why Metadata Reporting Matters
4. A Typical Metadata / Lineage Report
| Table | Type | Source | Transformation | Refresh | Rows loaded | Rows rejected | DQ % | Owner |
|---|---|---|---|---|---|---|---|---|
| DIM_CUSTOMER | Dim (SCD-2) | CRM.CUST_MASTER | Trim, gender standardize, surrogate key | Daily 02:00 | 12,540 | 7 | 99.94 | Sales Ops |
| FACT_SALES | Fact | ERP.SALES_LINES + POS files | Join, currency convert, derive amount | Daily 02:30 | 480,213 | 132 | 99.97 | Finance |
5. Lineage Graph
6. Standards and Tools
| Standard / tool | Purpose |
|---|---|
| CWM (Common Warehouse Metamodel, OMG) | Standard metamodel for exchanging warehouse metadata |
| XMI | XML format used to exchange CWM models |
| RDF / OWL | Semantic metadata, ontologies, business glossary |
| Informatica Metadata Manager, IBM Metadata Workbench | ETL and BI metadata, lineage |
| Collibra, Alation, DataHub, Apache Atlas | Data catalog and governance platforms |
- Define metadata; classify it into technical, business and operational types with examples (5-7 marks).
- Explain the role of the metadata repository in ETL and query tools (5 marks).
- Short note on data lineage and impact analysis (3-5 marks).
Frequently Asked Questions
QWhat is metadata?
Data about data: it describes the source, structure, meaning, transformation, quality and ownership of warehouse data.
QWhat are the types of metadata?
Technical (structure and mappings), business (definitions and rules), operational (job runs and errors) and process or lineage metadata (source-to-target flow).
QWhat is data lineage?
A record of where data came from and how it was transformed on its way to a report or table.
QWhat is a metadata repository?
A central store that keeps all metadata so ETL tools, the warehouse and BI tools can share and use it.
QName a metadata standard.
CWM (Common Warehouse Metamodel) by the OMG, exchanged using XMI.
QHow does metadata help query tools?
The tool reads business names, joins and hierarchies from metadata, so users can build queries without knowing the physical schema.