The heart of the original framework was its metadata schema, stored in Azure SQL Database, which allowed for dynamic configuration of ETL jobs. To incorporate AI, I extended this schema to orchestrate machine learning tasks alongside data integration, creating a unified pipeline that handles both. This required adding several new tables to the metadata repository:
- ML_Models: This table captures details about each ML model, including its type (e.g., regression, clustering), training datasets and inference endpoints. For instance, a forecasting model might reference a specific Databricks notebook and a Delta table containing historical sales data.
- Feature_Engineering: Defines preprocessing steps like scaling numerical features or one-hot encoding categorical variables. By encoding these transformations in metadata, the framework automates data preparation for diverse ML models.
- Pipeline_Dependencies: Ensures tasks execute in the correct sequence, I.e. ETL before inference, storage after inference, maintaining workflow integrity across stages.
- Output_Storage: Specifies destinations for inference results, such as Delta tables for analytics or Azure SQL for reporting, ensuring outputs are readily accessible.
Consider this metadata example for a job combining ETL and ML inference:
{
"job_id": 101,
"stages": [
{
"id": 1,
"type": "ETL",
"source": "SQL Server",
"destination": "ADLS Gen2",
"object": "customer_transactions"
},
{
"id": 2,
"type": "Inference",
"source": "ADLS Gen2",
"script": "predict_churn.py",
"output": "Delta Table"
},
{
"id": 3,
"type": "Storage",
"source": "Delta Table",
"destination": "Azure SQL",
"table": "churn_predictions"
}
]
} This schema enables ADF to manage a pipeline that extracts transaction data, runs a churn prediction model in Databricks and stores the results, all driven by metadata. The benefits are twofold: it eliminates the need for bespoke coding for each AI use case, and it allows the system to adapt to new models or datasets by simply updating the metadata. This flexibility is crucial for enterprises aiming to scale AI initiatives without incurring significant technical debt.
