Databricks UC Data Lineage Official Limitations

[RobsonNLPT]

Hi all.

I have a huge data migration project using medallion architecture,  UC, notebooks and workflows . One of the relevant requirements we have is to capture all data dependencies (upstreams and downstreams) using data lineage. 

I've followed all requirements to have our data lineage but the I think the list of limitations is not 100% complete.

I want to confirmed if the latest official documentation is 100% up to date and if there are topics on coming next releases based on roadmap. In the end the goal is to capture 100% of upstreams and downstreams

In particular I have specific question. Are SQL CTEs as upstreams captured?

Best

Robson

[MathieuDB]

Hello @RobsonNLPT ,

Yes SQL CTE are supported by the data lineage service. You can track table that were created using CTEs.

Here is an example that demonstrate the feature.

CREATE TABLE IF NOT EXISTS
  mpelletier.dbdemos.menu (
    recipe_id INT,
    app string,
    main string,
    dessert string
  );

INSERT INTO mpelletier.dbdemos.menu
    (recipe_id, app, main, dessert)
VALUES
    (1,"Ceviche", "Tacos", "Flan"),
    (2,"Tomato Soup", "Souffle", "Creme Brulee"),
    (3,"Chips","Grilled Cheese","Cheesecake");

CREATE TABLE
  mpelletier.dbdemos.dinner
AS SELECT
  recipe_id, concat(app," + ", main," + ",dessert)
AS
  full_menu
FROM
  mpelletier.dbdemos.menu;

CREATE TABLE
  mpelletier.dbdemos.dish_count
WITH DishCountCTE AS (
  SELECT 
    COUNT(DISTINCT app) AS appetizer_count,
    COUNT(DISTINCT main) AS main_course_count,
    COUNT(DISTINCT dessert) AS dessert_count
  FROM mpelletier.dbdemos.menu
)
SELECT 
  appetizer_count,
  main_course_count,
  dessert_count
FROM DishCountCTE;