learning-it by example

#205 Data governance 5.7 min

Column-Level Lineage from SQL with SQLGlot

Which columns feed this number? SQLGlot can answer in 15 lines, and I will show you where it misleads.

1. What is column-level lineage?
2. In OpenLineage's split of column dependencies, what is an indirect dependency?
3. In the SQLGlot column lineage demo on the made-up shop, which source columns feed net_revenue in the revenue query?
4. In the SQLGlot column lineage demo, the impact check for orders.status listed no outputs. What does that show?
5. What happened to the made-up column nope with validation off, compared with validation on?
6. Why does the SQLGlot column lineage code need the schema of the tables?

Practice only: nothing is saved and no score counts towards anything. Each answer comes with the reason.

  1. Predict a new query

    Copy the code folder first. In queries.py add a fourth query to the QUERIES dictionary: "names": "SELECT name AS customer_name FROM customers". Predict the line that python show.py will print for it, then run it and check. The video's package, sqlglot, must already be installed.

    A hint

    Each line has the form output column <- source column.

  2. Who depends on a customers column

    Without changing any code, predict which outputs depend on customers.country, then run python impact.py customers.country. Use the output of python show.py to work out your prediction first.

    A hint

    Look at which queries print customers.country on the right of an arrow.

  3. Add the filter column yourself

    In queries.py add the query "statuses": "SELECT status FROM orders WHERE status = 'paid'". Predict what python impact.py orders.status will list now, then run it. The video says filter columns are missing from the lineage, so say why this query changes the answer.

    A hint

    Here status is also an output column, not only a filter.

  4. A typo with validation on and off

    In gaps.py change the TYPO query to select a second wrong column name of your own, for example SELECT colour FROM orders. Predict the message with validation on and the printed result with validation off, then run python gaps.py and compare.

    A hint

    The video shows that validation off reports the typo as a real source.

These tasks are for you to try on your own machine. Nothing is checked or saved.

What this does not show

Three simple queries in the default dialect; filter and join columns are not in this lineage, and it reads SQL text, not what ran.

Up next in Data governance

More in Data governance

All Data governance videos

Related videos