lineage with schema info is the wrong software engineering pattern

The idea in the recent release seems to be that you provide the schemas in the lineage() method, so that the source data types can be derived automatically. Good idea, but...

sqlglot/sqlglot/lineage.py

Line 83 in 7ee6201

    
           column: str | exp.Column, sql: str | exp.Expr, schema: dict | Schema, **kwargs: t.Any

In order to know what schemas to add, I must parse the SQL. Or I add all 10'000 tables of my database because I don't know the SQL statement.

Hence I would suggest two things:

Schema is an optional parameter
Do not provide a schema but a getter function. When the lineage requires a table schema information, it should call a function that I have to provide, which returns the Schema/dict object.

We can't do this. Schema was made mandatory because otherwise qualify() was unreliable, and hence lineage(), too. For example, in SELECT a FROM t JOIN u ON t.id = u.id, a can mean either t.a or u.a.
This seems unnecessary. The existing Schema interface already exposes column_names, get_column_type and the lineage() function can accept arbitrary implementations of this interface. You can thus create an implementation that lazily fetches metadata on demand and caches it.

The input sql is parsed regardless, btw. If you want to do that outside of the lineage() call to analyze the scanned tables, you can do that and then just pass the parsed expression to it with copy=False to avoid the extra overhead.

Do you have a quick example of the lazy loading?

Say we have a SQL like SELECT t.id FROM t JOIN u ON t.id = u.id, how would I implement a Schema class that loads the tables t and u from the database dictionary via a select statement. Just the part of how to invoke a get_table(tablename)?

I wonder if we talk about the same thing. I am not saying schema as in qualifier. I meant your test cases like

        node = lineage(
            "a",
            "SELECT a FROM z",
            schema={"x": {"a": "int"}},
            sources={"y": "SELECT * FROM x", "z": "SELECT a FROM y"},
        )

I cannot provide the dict/Schema of 10'000 tables just in case the SQL is using any of those. So must be loaded lazy. But Schema is derived from Expression and Expression I do not see any hook for lazy loading.

Your example 1) is easy to answer. That is an invalid SQL syntax.

Just FYI, I am stuck now. The previous version was working without the schema information and the current requires it. Without the schema info it fails, adding an empty dict raises an error also. And how to provide the Schema, I don't know yet.

Your example 1) is easy to answer. That is an invalid SQL syntax.

Not if u doesn't contain the a column; you can't tell based only on the query's text. For example, this is valid:

memory D create or replace table t(id, a) as select 1, 'foo';
memory D create or replace table u(id) as select 1;
memory D select a from t join u on t.id = u.id;
┌─────────┐
│    a    │
│ varchar │
├─────────┤
│ foo     │
└─────────┘

Anyway, I was alluding to something that looks like this:

class LazySchema(MappingSchema):
    def __init__(self, get_table, dialect=None):
        super().__init__(dialect=dialect)
        self.get_table = get_table
        self.loaded = set()

    def _load(self, table):
        table = exp.to_table(table, dialect=self.dialect)
        name = table.name

        if name not in self.loaded:
            columns = self.get_table(name)  # e.g. {"id": "INT"}
            if columns is None:
                raise ValueError(f"Missing metadata for {name}")
            self.add_table(table, columns)
            self.loaded.add(name)

    def column_names(self, table, only_visible=False, **kwargs):
        self._load(table)
        return super().column_names(
            table, only_visible=only_visible, **kwargs
        )

    def get_column_type(self, table, column, **kwargs):
        self._load(table)
        return super().get_column_type(table, column, **kwargs)


def get_table(name):
    # Query the database metadata using a bound table-name parameter.
    # Return {column_name: sql_type} in database column order.
    ...

And then you do:

node = lineage(
    "id",
    "SELECT t.id FROM t JOIN u ON t.id = u.id",
    schema=LazySchema(get_table),
)

Right, forgot about the case where a is an unique identifier across all tables.
Thanks for the sample, will implement it.

Success. Thanks for your help, appreciate it!

Info: When you pass a dialect into lineage, also pass it to the LazySchema.

node = lineage(
    "id",
    "SELECT t.id FROM t JOIN u ON t.id = u.id",
    schema=LazySchema(get_table, dialect=Fabric),
    dialect=Fabric
)

Else below returns an error that USER_NAME cannot be found. Of course, different dialects use different case sensitivity.

select x.USER_NAME from MYTABLE as x;

Unfortunately I have one more. In Windows and Python 3.11.9 it works. When executed as github action, meaning Ubuntu, same version, it says at schema=LazySchema(get_table, dialect=Fabric)

TypeError: interpreted classes cannot inherit from compiled

I assume this is related to sqlglot[c] vs sqlglot, but don't know and it certainly is above my knowledge. Any hints would be greatly appreciated.

Yes, I have seen #7700

You're using an older sqlglot version I think, schema.py is no longer a compiled module IIRC.

Yes, there seems to have been some caching going on in the private github action runner.

Navigation Menu

Repository navigation

lineage with schema info is the wrong software engineering pattern #8610

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

lineage with schema info is the wrong software engineering pattern #8610

Description

Activity

georgesittas commented on Oct 12, 2026

wernerdaehn commented on Oct 12, 2026

wernerdaehn commented on Oct 12, 2026

georgesittas commented on Oct 12, 2026

wernerdaehn commented on Oct 13, 2026

wernerdaehn commented on Oct 13, 2026

wernerdaehn commented on Oct 14, 2026

wernerdaehn commented on Oct 15, 2026

georgesittas commented on Oct 15, 2026

wernerdaehn commented on Oct 15, 2026

Metadata

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

Issue actions