Data Catalog and Governance on GCP

A warehouse with five tables needs no governance. Everyone knows what they are. A warehouse with five thousand needs to answer, for any one of them: what is this, where did it come from, can it be trusted, and who may see it. Data governance on GCP is the set of tools that hold those answers, and the catalogue is where they live.

The names, sorted out

The tools here have been renamed, and older tutorials will mislead you.

Google directs Data Catalog users to Knowledge Catalog (Google Cloud: Data Catalog overview). The rename kept the old identifiers: the API, client library, command line and IAM names are unchanged, so dataplex still appears in commands and endpoints (Google Cloud: Knowledge Catalog introduction).

Google now also describes Knowledge Catalog as a context layer for AI agents, which can read schemas, lineage and business rules from it. The governance functions underneath are what this guide covers.

Four questions

What the catalogue holds

Entries. An entry represents one data asset. For Google Cloud sources they appear without any effort: BigQuery tables, views, datasets and models are catalogued automatically. You can add entries for systems Google does not know about (Google Cloud: about metadata in Knowledge Catalog).

Aspects. An aspect is a block of structured metadata attached to an entry or to one of its columns. Some are filled in by the system, such as the schema. Others you define. An aspect type is the template: you might create one for data classification with a field for sensitivity, or one for ownership with a team and a contact.

A business glossary. A glossary holds the organisation's terms and their definitions, grouped into categories, with synonyms. Terms are linked to the data they describe, so someone can search for "active customer" and find the table that implements it (Google Cloud: business glossaries).

The automatic part gives you a searchable list of everything. It does not give you meaning. A table named cust_agg_v2_final is catalogued perfectly and explained not at all. The description, the owner and the glossary links are written by people, and they are the part that makes a catalogue worth opening.

Lineage: where it came from

Lineage is a map of where data originates, where it goes and what happens to it on the way. When the Data Lineage API is enabled, BigQuery and Dataflow report lineage events as they move and transform data, with nothing added to your code. Other systems can report through the API, and Google recommends the OpenLineage format for that (Google Cloud: about data lineage).

It earns its keep in three situations:

• A number looks wrong. Lineage shows which tables fed it, so you know where to look.
• A column is about to change. Lineage shows everything downstream that will be affected.
• An auditor asks where personal data goes. Lineage is the answer.

Lineage is recorded from the moment the API is enabled, so turn it on early.

Lineage from four SQL statements

To see what lineage is made of, the script derives it from the SQL of a small pipeline: two staging tables, a daily table and a summary. It parses each statement, records which tables it reads, and then answers two questions. What must be checked if a table changes? And which raw columns is a reporting column built from?

It is a model that parses SQL text with the sqlglot library. It is not the Data Lineage API, and nothing runs on Google Cloud.

import sqlglot
from sqlglot import exp
from sqlglot.lineage import lineage

PIPELINE = {
    "staging.trips": """
        SELECT VendorID AS vendor_id, tpep_pickup_datetime AS pickup_at,
               PULocationID AS pickup_zone_id, fare_amount, tip_amount
        FROM raw.trips
        WHERE fare_amount > 0""",
    "staging.zones": """
        SELECT LocationID AS zone_id, Borough AS borough, Zone AS zone_name
        FROM raw.taxi_zones""",
    "marts.daily_borough_revenue": """
        SELECT DATE(t.pickup_at) AS day, z.borough,
               SUM(t.fare_amount + t.tip_amount) AS revenue, COUNT(*) AS trips
        FROM staging.trips AS t
        JOIN staging.zones AS z ON t.pickup_zone_id = z.zone_id
        GROUP BY day, z.borough""",
    "marts.borough_summary": """
        SELECT borough, SUM(revenue) AS revenue, SUM(trips) AS trips
        FROM marts.daily_borough_revenue
        GROUP BY borough""",
}

# table-level lineage: which tables does each statement read?
reads = {}
for target, sql in PIPELINE.items():
    tree = sqlglot.parse_one(sql, read="bigquery")
    reads[target] = sorted({f"{t.db}.{t.name}" for t in tree.find_all(exp.Table)})
    print(f"{target:28} <- {', '.join(reads[target])}")


def downstream(table):
    hit = {t for t, sources in reads.items() if table in sources}
    return hit | {d for t in hit for d in downstream(t)}


print()
for table in ("raw.trips", "raw.taxi_zones", "staging.zones"):
    print(f"if {table} changes, check: {', '.join(sorted(downstream(table)))}")

# column-level lineage: trace reporting columns back to the raw columns they are built from
print()
for column in ("revenue", "borough"):
    node = lineage(column, PIPELINE["marts.borough_summary"], dialect="bigquery",
                   sources={k: v for k, v in PIPELINE.items() if k != "marts.borough_summary"})
    leaves = sorted({f"{n.source.db}.{n.source.name}.{n.name.split('.')[-1]}"
                     for n in node.walk() if not n.downstream})
    print(f"marts.borough_summary.{column:8} is built from {', '.join(leaves)}")

staging.trips                <- raw.trips
staging.zones                <- raw.taxi_zones
marts.daily_borough_revenue  <- staging.trips, staging.zones
marts.borough_summary        <- marts.daily_borough_revenue

if raw.trips changes, check: marts.borough_summary, marts.daily_borough_revenue, staging.trips
if raw.taxi_zones changes, check: marts.borough_summary, marts.daily_borough_revenue, staging.zones
if staging.zones changes, check: marts.borough_summary, marts.daily_borough_revenue

marts.borough_summary.revenue  is built from raw.trips.fare_amount, raw.trips.tip_amount
marts.borough_summary.borough  is built from raw.taxi_zones.borough

• Four statements give four edges, and following them gives the impact of a change. Altering raw.trips touches three tables, including a summary two steps away that never mentions it.
• Column lineage is finer. The revenue column in the summary comes from two raw columns, fare_amount and tip_amount. A change to the raw tolls_amount column would not affect it, although table-level lineage would list the table as affected.
• Renames are followed. borough in the summary traces to the zone file's column through two tables and an alias.

A lineage service does this continuously, from the jobs that actually ran, and for tools other than SQL. The principle is the same: every transformation declares what it read and what it wrote.

Quality: can it be trusted

Knowledge Catalog can run two kinds of scan on a BigQuery table (Google Cloud: auto data quality overview).

A data profile scan describes what is there: null rates, distinct values, ranges. It can also suggest rules.

A data quality scan checks the table against rules you choose:

Every rule belongs to a dimension, such as completeness, validity, uniqueness or freshness, so results can be summarised by kind of problem. A scan runs on Google-managed resources, covers one table, and can raise alerts through Cloud Logging. See logging and monitoring on GCP.

Scans check data after it has landed. Checks inside the pipeline stop bad data before it is published. Most teams need both. See data quality testing and validation in pipelines and building a data pipeline on GCP.

Who may see it

Access is granted with IAM on projects, datasets and tables. For individual columns, BigQuery uses policy tags: a tag such as "personal data" is attached to columns, and permissions are granted on the tag. Policy tags are the one piece that still runs through the Data Catalog API, and Google states that this function is not deprecated (Google Cloud: introduction to data masking).

For masking, encryption and de-identification, see securing data on GCP. For roles, see GCP IAM.

Starting small

1. Enable the Data Lineage API in the projects that run pipelines.
2. Describe the twenty tables people actually use. A sentence, an owner, a refresh schedule.
3. Write glossary entries for the terms people argue about. Revenue, active customer, churn.
4. Add one quality scan to each published table: keys not null, keys unique, data fresh.
5. Tag the sensitive columns.
6. Give every table an owner, and review the list each quarter.

Governance fails when it is attempted for everything at once. It works when the tables that matter are in good order and the rest are at least findable. For the ideas behind the tools, see data governance basics every analyst should know.

Common mistakes

• Expecting a catalogue to be governance. It is a place to record decisions, not the decisions.
• Cataloguing everything and describing nothing.
• Tables without owners.
• Quality rules that alert no one.
• Following an old tutorial into Data Catalog.
• Reading table-level lineage as column-level. A table can be downstream without the column you care about being affected.

Data governance and data quality are part of the Data Engineering course.

Sources

• Data Catalog overview (Google Cloud Documentation) — Google Cloud
• Knowledge Catalog introduction (Google Cloud Documentation) — Google Cloud
• About metadata in Knowledge Catalog (Google Cloud Documentation) — Google Cloud
• Manage business glossaries (Google Cloud Documentation) — Google Cloud
• About data lineage (Google Cloud Documentation) — Google Cloud
• Auto data quality overview (Google Cloud Documentation) — Google Cloud
• Introduction to data masking (Google Cloud Documentation) — Google Cloud