Schema compare between environments & generate change script

[jp-vanheerden]

Is your feature request related to a problem? Please describe. When promoting between environments (eg Dev and UAT), tables may be different or not exist in the second environment. The process of updating the tables is currently manual and it would be useful to have a process that compares the table definitions between two environments and generates a script to change the second environment to match the first environment.

Describe the solution you'd like Process that:

1. Gets the table definitions for the first environment
2. Gets the table definitions for the second environment
3. Compares the table definitions and generates DDL statements to handle changes
4. Where changes cannot be handled automatically, a placeholder is added
5. Generate a notebook that can be run in the second environment

Describe alternatives you've considered There may be utilities available in the community that can be re-used

Additional context N/A

[avinvagel]

Two new notebooks added to: dbt\include\fabricsparknb\notebooks:

1. compare_notebook this file gets generated by the compare() command
2. util_BuildMetadata this file has been added to the pre_dbt upload function

Two new commands added:

1. buildcomparemetadata: which will execute the util_buildmetadata notebook in the environment configured. This notebook will create a comparemetadata table in their respective environment that will store the Lakehouse schema information. Arguments:

   • dbt_project_dir: The path to the dbt_project directory. If left blank it will use the current directory
   • source: Source environment name from profile.yml
   • target: Target environment name from profile.yml
   • dbt_profiles_dir: The path to the dbt_profile directory. If left blank it will use the users home directory followed by .dbt.

2. compare: This command will compare two environments Lakehouse's. Generated notebooks will be uploaded to the 'target' environment configured in the 'profile.yml' file which will contain the sql commands that can be used to create and alter tables, from one environment to the next.

• dbt_project_dir: The path to the dbt_project directory. If left blank it will use the current directory
• source: Source environment name from profile.yml
• target: Target environment name from profile.yml
• dbt_profiles_dir: The path to the dbt_profile directory. If left blank it will use the users home directory followed by .dbt.

[avinvagel]

New items required in the profile.yml file to specify the other environments to compare to:

[grantkriegerai]

Review session setup for tuesday with 13th August to confirm all happy

[annstephyjose]

Created #185 and #186 to address the further enhancements