Column-Level SQL Lineage Demo

WITH
    reg AS (
        SELECT
            reg_channel,
            reg_subchannel,
            reg_date,
            COUNT(DISTINCT uid) AS reg_num
        FROM
            info_detail
        WHERE
            reg_date >= '2023-01-01'
        GROUP BY
            reg_channel,
            reg_subchannel,
            reg_date
    ),
    setup AS (
        SELECT
            reg_channel,
            reg_subchannel,
            kaihu_date,
            COUNT(DISTINCT uid) AS setup_num
        FROM
            info_detail
        WHERE
            kaihu_date >= '2023-01-01'
        GROUP BY
            reg_channel,
            reg_subchannel,
            kaihu_date
    ),
    pc AS (
        SELECT
            reg_channel,
            reg_subchannel,
            first_money_in_date AS first_pc_date,
            COUNT(DISTINCT uid) AS pc_num
        FROM
            info_detail
        WHERE
            first_money_in_date >= '2023-01-01'
        GROUP BY
            reg_channel,
            reg_subchannel,
            first_money_in_date
    ),
    front AS (
        SELECT
            ad_date,
            reg_channel,
            sub_channel,
            cost,
            impression,
            click,
            install_usr
        FROM
            ad_full_line
        WHERE
            ad_date >= '2023-01-01'
            AND (
                (cost IS NOT NULL)
                OR (impression IS NOT NULL)
                OR (click IS NOT NULL)
                OR (install_usr IS NOT NULL)
            )
    ),
    join_all AS (
        SELECT
            COALESCE(
                stat_date,
                reg_date,
                kaihu_date,
                first_pc_date,
                ad_date
            ) AS stat_date,
            COALESCE(
                a.reg_channel,
                b.reg_channel,
                c.reg_channel,
                d.reg_channel,
                e.reg_channel
            ) AS reg_channel,
            COALESCE(
                a.reg_subchannel,
                b.reg_subchannel,
                c.reg_subchannel,
                d.reg_subchannel,
                e.sub_channel
            ) AS reg_subchannel,
            nvl(cost, 0) AS cost,
            nvl(impression, 0) AS impression,
            nvl(click, 0) AS click,
            nvl(reg_num, 0) AS reg_num,
            nvl(setup_num, 0) AS setup_num,
            nvl(pc_num, 0) AS pc_num,
            nvl(aum, 0) AS aum
        FROM
            day_stat a
            FULL OUTER JOIN reg b ON stat_date = reg_date
            AND a.reg_channel = b.reg_channel
            AND a.reg_subchannel = b.reg_subchannel
            FULL OUTER JOIN setup c ON stat_date = kaihu_date
            AND a.reg_channel = c.reg_channel
            AND a.reg_subchannel = c.reg_subchannel
            FULL OUTER JOIN pc d ON stat_date = first_pc_date
            AND a.reg_channel = d.reg_channel
            AND a.reg_subchannel = d.reg_subchannel
            FULL OUTER JOIN front e ON stat_date = ad_date
            AND a.reg_channel = e.reg_channel
            AND a.reg_subchannel = e.sub_channel
    ),
    join_all2 AS (
        SELECT
            stat_date,
            reg_channel,
            reg_subchannel,
            MAX(cost) AS cost,
            MAX(impression) AS impression,
            MAX(click) AS click,
            MAX(reg_num) AS reg_num,
            MAX(setup_num) AS setup_num,
            MAX(pc_num) AS pc_num,
            MAX(aum) AS aum
        FROM
            join_all
        GROUP BY
            stat_date,
            reg_channel,
            reg_subchannel
    ) INSERT overwrite TABLE ad_conver_day
SELECT
    'hk' AS area,
    stat_date,
    ad_platform,
    account_id,
    campaign_id,
    campaign_name,
    group_id,
    group_name,
    a.reg_channel,
    a.reg_subchannel,
    cost,
    impression,
    click,
    reg_num,
    setup_num,
    pc_num,
    aum
FROM
    join_all2 a
    JOIN dimension_table b ON a.reg_channel = b.reg_channel
    AND a.reg_subchannel = b.sub_channel

tpcds example