Metadata system to capture pipelines health status

Background: Why a developer may want to add these tables for what type of processing

 

 

 

Create or replace metadata system to support and capture data pipeline health status by using following table , attribute description, generate ER diagram and give me ddls in Databricks SQL. The column name and its description are given below

 

{noformat}1. Batch Table:

batch_id: Primary Key

batch_name: Name of the batch

description: Description of the batch

active_status: batch Status

 

2. BatchRun Table

batchrun_id: Primary Key

batch_id: Foreign Key referencing batch(batch_id)

execution_date: Date of execution

status: batch run status

 

3. Job Table

job_id: Primary Key

job_name: Name of the job

description: Description of the job

job_type: Type of the job

active_status: job status

batch_id: Foreign Key referencing batch(batch_id)

 

4. JobRun Table

job_run_id: Primary Key

job_id: Foreign Key referencing job(job_id)

batchrun_id: Foreign Key referencing batch_run(batchrun_id)

execution_date: Date of execution

status: job run status

log_message: Log message of the job run

 

5. JobArguments Table

argument_id: Primary Key

job_id: Foreign Key referencing job(job_id)

argument_name: Name of the argument

argument_value: Value of the argument{noformat}

 

Prerequisite:

 

1. Drop the tables if exist and recreate tables in purgo_playground schema.