#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.
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.
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.
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.
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
Related videos
- #216 Audit Evidence From Pipeline Logs in Python (SHA-256) Data governance
- #08 Pandas vs Polars vs DuckDB on 10M Rows, Timed and Checked Python for data
- #113 Parquet Explained: Why a Reader Has Less to Read Data engineering and pipelines
- #114 Partitioning Parquet: Which Layout Opens Fewer Files? Data engineering and pipelines
