By Ferwa Zaidi, Sam Duru-Nnebue and Michael Oladugba

In Phase 1 of this series, Operationalizing S&P 500 Analytics with Control-M, BigQuery, and Amazon QuickSight, we focused on building a reliable foundation for analytics. That first phase showed how Control-M orchestrates a multi-cloud ETL pipeline to ingest raw S&P 500 data, cleanse and enrich it in BigQuery, and deliver consistent, up-to-date dashboards in Amazon QuickSight. The outcome was a production-grade descriptive analytics system — automated, auditable, and dependable.

Phase 1 answered a fundamental question: How do we ensure historical market data is always accurate, timely, and ready for analysis?

But descriptive analytics only explains what has already happened.

Phase 2 builds directly on that same orchestrated foundation and extends it into predictive analytics. Using the cleansed datasets and engineered features from Phase 1, the pipeline now trains, deploys, and runs machine-learning models to generate forward-looking insights — predicted returns, volatility estimates, confidence scores, and buy/sell signals.

This introduces a new operational challenge. Predictive models must run in the correct order, on fresh and validated data, and complete within strict time windows. A prediction that arrives late or out of sequence is not actionable.

In Phase 2, Control-M orchestrates the entire predictive analytics lifecycle, coordinating feature engineering in BigQuery, model training and deployment in Vertex AI, daily scoring, and downstream visualization refreshes. Together, Phase 1 and Phase 2 show how enterprise orchestration turns analytics from historical reporting into reliable, production-ready predictive intelligence.

What’s New in Phase 2?

Phase 2 introduces several major enhancements across the pipeline:

Feature Engineering & Data Quality Checks

We begin by validating stock data — addressing missing values, handling outliers, and enforcing consistency across data types. Once validated, we engineer technical indicators such as Moving averages, RSI, MACD, Volatility metrics, Lagged returns.

These engineered features are stored in BigQuery, making them easily accessible for training and scoring.

Model Training and Hyperparameter Tuning

To improve prediction accuracy, multiple models were evaluated. In addition to linear regression, we experimented with a Random Forest approach.

Performance was assessed using RMSE, R², and MAE. While Random Forest initially appeared promising, it struggled to generalize and showed signs of overfitting. Linear regression proved more stable and interpretable, effectively capturing the underlying relationships between features and returns.

Model Deployment

Once trained and validated, the selected model is registered and deployed in Vertex AI, enabling consistent execution and scalable scoring.

Model Scoring

Fresh market data is scored daily, producing Predicted Daily Returns, Predicted Volatility, Confidence Scores, Buy/Sell signals.

These outputs provide actionable insights while also indicating prediction reliability.

Orchestration

Control-M orchestrates the entire workflow — managing dependencies, execution order, retries, and monitoring. This ensures the pipeline runs reliably without manual intervention.

Visualization

Prediction results are surfaced through Looker Studio dashboards, allowing business users to monitor trends, volatility, and trading signals in near real time.

Control-M Workflow: Step-by-Step Execution

In Phase 2, Control-M orchestrates the entire predictive analytics lifecycle as a structured, event-driven workflow. Rather than executing jobs independently, each step is connected through explicit dependencies to ensure data readiness, correctness, and reliable execution order.

At a high level, the workflow consists of three core Control-M jobs:

1. BigQuery Feature Engineering Job
2. Vertex AI Pipeline Job
3. Looker Studio Refresh Trigger

Each job runs only when its upstream dependency has completed successfully.

Note:
All JSON definitions shown in this blog are snippets for illustration. They capture only the key parts of each job (e.g., Type, Src, Dest, FileTransfers, SQLStatement). The full JSON files, with complete scheduling logic, events, and metadata, are available on our GitHub repo here.

Step 1: BigQuery Feature Engineering Job

The workflow begins with a GCP BigQuery job managed by Control-M.

This job transforms the cleaned S&P 500 dataset (sp500_clean) into a fully engineered feature table (sp500_features). Using SQL window functions and analytical calculations, it generates Daily returns, Moving averages (MA-7, MA-30), Volatility metrics, RSI and MACD indicators, Bollinger Bands and EMA features. The result is a feature-rich dataset stored in BigQuery and ready for model training and scoring.

Once the feature table is successfully created, Control-M emits an event that signals downstream processing can begin.

"BigQuery-Job" : {
      "Type" : "Job:GCP BigQuery",
      "ConnectionProfile" : "SP500-BIGQUERY",
      "Project Name" : "sso-gcp-dba-ctm4-pub-cc10274",
      "Dataset Name" : "sp500_dataset",
      "Action" : "Query",
      "Run Select Query and Copy to Table" : "unchecked",
      "SQL Statement" : "CREATE OR REPLACE TABLE `sso-gcp-dba-ctm4-pub-cc10274.sp500_dataset.sp500_features` AS%4ESELECT%4EDATE(date) AS date,%4Eopen,%4Ehigh,%4Elow,%4Eclose,%4Evolume,%4EName,%4ESAFE_DIVIDE(close - LAG(close) OVER (PARTITION BY Name ORDER BY DATE(date)),LAG(close) OVER (PARTITION BY Name ORDER BY DATE(date))) AS daily_return,%4EAVG(close) OVER (PARTITION BY Name ORDER BY DATE(date) ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma_7,%4EAVG(close) OVER (PARTITION BY Name ORDER BY DATE(date) ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS ma_30,%4ESTDDEV(close) OVER (PARTITION BY Name ORDER BY DATE(date) ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS volatility_7d,%4EAVG(close) OVER (PARTITION BY Name ORDER BY DATE(date) ROWS BETWEEN 13 PRECEDING AND CURRENT ROW) AS RSI_14,%4EAVG(close) OVER (PARTITION BY Name ORDER BY DATE(date) ROWS BETWEEN 11 PRECEDING AND CURRENT ROW)%4E-%4EAVG(close) OVER (PARTITION BY Name ORDER BY DATE(date) ROWS BETWEEN 25 PRECEDING AND CURRENT ROW) AS MACD,%4EAVG(close) OVER (PARTITION BY Name ORDER BY DATE(date) ROWS BETWEEN 8 PRECEDING AND CURRENT ROW) AS MACD_signal,%4EAVG(close) OVER (PARTITION BY Name ORDER BY DATE(date) ROWS BETWEEN 19 PRECEDING AND CURRENT ROW)%4E+%4E2 * STDDEV(close) OVER (PARTITION BY Name ORDER BY DATE(date) ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS bb_upper,%4EAVG(close) OVER (PARTITION BY Name ORDER BY DATE(date) ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS bb_middle,%4EAVG(close) OVER (PARTITION BY Name ORDER BY DATE(date) ROWS BETWEEN 19 PRECEDING AND CURRENT ROW)%4E-%4E2 * STDDEV(close) OVER (PARTITION BY Name ORDER BY DATE(date) ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS bb_lower,%4EAVG(close) OVER (PARTITION BY Name ORDER BY DATE(date) ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS EMA20%4EFROM `sso-gcp-dba-ctm4-pub-cc10274.sp500_dataset.sp500_clean`;%4E",
      "Copy Operation Type" : "Copy",
      "Show Load Options" : "unchecked",
      "Host" : "vertex-agent.ctmgcpagents.com",
      "CreatedBy" : "<creator email>",
      "Description" : "This job further proccessses the clean data and feature engineering",
      "RunAs" : "SP500-BIGQUERY",
      "When" : {
        "WeekDays" : [ "NONE" ],
        "MonthDays" : [ "ALL" ],
        "DaysRelation" : "OR"
      },
      "eventsToAdd" : {
        "Type" : "AddEvents",
        "Events" : [ {
          "Event" : "GCP_BigQuery_Job_1-TO-GCP_Vertex_AI_Job_300"
        } ]
      }
    },

CREATE OR REPLACE TABLE `sso-gcp-dba-ctm4-pub-cc10274.sp500_dataset.sp500_features` AS SELECT DATE(date) AS date, open, high, low, close, volume, Name, SAFE_DIVIDE(close - LAG(close) OVER (PARTITION BY Name ORDER BY DATE(date)),LAG(close) OVER (PARTITION BY Name ORDER BY DATE(date))) AS daily_return, AVG(close) OVER (PARTITION BY Name ORDER BY DATE(date) ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma_7, AVG(close) OVER (PARTITION BY Name ORDER BY DATE(date) ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS ma_30, STDDEV(close) OVER (PARTITION BY Name ORDER BY DATE(date) ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS volatility_7d, AVG(close) OVER (PARTITION BY Name ORDER BY DATE(date) ROWS BETWEEN 13 PRECEDING AND CURRENT ROW) AS RSI_14, AVG(close) OVER (PARTITION BY Name ORDER BY DATE(date) ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) - AVG(close) OVER (PARTITION BY Name ORDER BY DATE(date) ROWS BETWEEN 25 PRECEDING AND CURRENT ROW) AS MACD, AVG(close) OVER (PARTITION BY Name ORDER BY DATE(date) ROWS BETWEEN 8 PRECEDING AND CURRENT ROW) AS MACD_signal, AVG(close) OVER (PARTITION BY Name ORDER BY DATE(date) ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) + 2 * STDDEV(close) OVER (PARTITION BY Name ORDER BY DATE(date) ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS bb_upper, AVG(close) OVER (PARTITION BY Name ORDER BY DATE(date) ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS bb_middle, AVG(close) OVER (PARTITION BY Name ORDER BY DATE(date) ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) - 2 * STDDEV(close) OVER (PARTITION BY Name ORDER BY DATE(date) ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS bb_lower, AVG(close) OVER (PARTITION BY Name ORDER BY DATE(date) ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS EMA20 FROM `sso-gcp-dba-ctm4-pub-cc10274.sp500_dataset.sp500_clean`;

Step 2: Vertex AI Pipeline Job

After feature engineering completes, Control-M triggers a Vertex AI pipeline using the GCP Vertex AI plugin.

This pipeline executes a multi-stage machine learning workflow that includes:

• Loading engineered features from BigQuery
• Preprocessing and scaling data
• Training a regression model
• Registering and deploying the model to a Vertex AI endpoint
• Scoring fresh market data
• Writing prediction results back to BigQuery

Because the entire pipeline is launched as a single Control-M job, retries, failures, and execution status are centrally monitored and managed.

When the Vertex AI pipeline completes successfully, Control-M raises another event indicating that prediction results are available for consumption.

Step 3: Looker Studio Refresh Trigger

The final step in the workflow is a GCP Cloud Function job that represents a dashboard refresh event.

In production environments, BI tools such as Looker Studio typically refresh automatically when underlying data sources update. However, from an orchestration perspective, Control-M still plays a critical role by explicitly marking the completion of the analytics pipeline.

This job acts as a logical boundary between data production and data consumption, signaling that:

• Predictions have been generated
• Results are stored in BigQuery
• Dashboards are ready to reflect the latest insights

From an operational standpoint, this makes the workflow auditable, traceable, and complete.

"Looker-refresh-job" : {
      "Type" : "Job:GCP Functions",
      "ConnectionProfile" : "GCFSTD",
      "Function Parameters" : "URL Parameters",
      "Get Logs" : "unchecked",
      "att2" : "\"filter\":\"labels.execution_id:",
      "att3" : "\"orderBy\": \"timestamp desc\",   \"pageSize\": 1000 }",
      "Project ID" : "sso-gcp-dba-ctm4-pub-cc10274",
      "Location" : "us-west2",
      "Function Name" : "looker-refresh",
      "Host" : "vertex-agent.ctmgcpagents.com",
      "CreatedBy" : "<creator email>",
      "Description" : "This job triggers the GCP function that refreshes the looker dashboard",
      "RunAs" : "GCFSTD",
      "When" : {
        "WeekDays" : [ "NONE" ],
        "MonthDays" : [ "ALL" ],
        "DaysRelation" : "OR"
      },
      "eventsToWaitFor" : {
        "Type" : "WaitForEvents",
        "Events" : [ {
          "Event" : "GCP_Vertex_AI_Job_300-TO-Looker_refresh"
        } ]
      },
      "eventsToDelete" : {
        "Type" : "DeleteEvents",
        "Events" : [ {
          "Event" : "GCP_Vertex_AI_Job_300-TO-Looker_refresh"
        } ]
      }
    },

Why This Workflow Matters

By modeling the pipeline as a sequence of dependent Control-M jobs, Phase 2 achieves:

• Deterministic execution order
• Clear operational visibility
• Reliable daily prediction delivery
• Separation of data engineering, ML, and visualization concerns

Instead of treating machine learning as an ad-hoc process, the workflow becomes a production-grade system that can be scheduled, monitored, and scaled like any other enterprise workload.

Business Use Case: Why Predictive Modeling Matters

Financial organizations rely on timely, accurate insights to navigate volatility and assess risk. While historical dashboards remain valuable, modern markets demand predictive intelligence.

This workflow enables:

• Proactive risk management using volatility forecasts
• More informed investment decisions driven by predicted returns
• Quant-driven strategy refinement using model outputs stored in BigQuery
• Operational consistency, ensuring predictions arrive reliably each day
• Cross-cloud collaboration across GCP, AWS, and internal systems

By transforming raw market data into predictive intelligence, the system supports faster decisions and deeper market understanding.

Control-M Plugins and Job Types Used

Control-M acts as the orchestration backbone, leveraging multiple job types:

1. GCP BigQuery Jobs

• Feature table creation
• Data validation
• Writing predictions back to BigQuery

2. Control-M MFT (Managed File Transfer)

• Secure file movement between S3 and GCS
• Automated ingestion triggers

3. Python & Bash Jobs

• Trigger Vertex AI pipelines
• Execute scoring logic
• Invoke cloud APIs

4. REST API Jobs

• Register and deploy Vertex AI models
• Trigger dashboard refresh events in Looker Studio or Quicksight
• Perform endpoint health checks

5. AWS QuickSight Plugin

• Refresh dashboard datasets
• Validate analytics visibility

6. File Watchers

• Detect new market data arrivals
• Automatically launch downstream ML jobs

Together, these capabilities create a unified orchestration layer across data engineering, machine learning, and business intelligence.

The Pipeline Workflow

The Phase 2 pipeline consists of four major steps:

1. Load and Preprocess Data
2. Model Training
3. Register and Deploy Model
4. Model Scoring

High-Level Goals

• Feature Engineering & Data Quality Checks: Ensuring that the dataset is clean and robust, making it ready for model training. This step is critical to ensure accurate predictions.
• Training and Hyperparameter Tuning: Experimenting various algorithms and fine-tuning hyperparameters to find the best model for our task.
• Model Deployment in Vertex AI: Registering and deploying the model in Vertex AI, enabling real-time prediction and scalability.
• Model Scoring and Reporting via Looker Studio: Making daily predictions and updating the Looker Studio dashboards with new insights, such as predicted daily returns, volatility, and buy/sell signals.

Pipeline Steps in Detail

Step 1: Load and Preprocess Data

Raw S&P 500 data is fetched from BigQuery, cleaned, and enriched with engineered features. Processed datasets are saved to GCS for downstream training.

from google.cloud import bigquery, storage 
import pandas as pd 
from sklearn.model_selection import train_test_split 
from sklearn.impute import SimpleImputer 
from sklearn.preprocessing import StandardScaler 
import joblib 
import os 

@dsl.component( 
     base_image="python:3.9", 
     packages_to_install=[ 
         "google-cloud-bigquery", 
         "google-cloud-storage", 
         "pandas", 
         "scikit-learn", 
         "joblib", 
        "db-dtypes" 
     ] 
) 
def load_preprocess_op(project_id: str, query: str, bucket_name: str) -> str: 
    bq = bigquery.Client(project=project_id) 
    df = bq.query(query).to_dataframe()
  
    # Feature engineering and preprocessing... 
    # After preprocessing, save the dataset for further training. 
    # Return GCS path for further use

Step 2: Train Model

The preprocessed data is used to train the selected model, which is then saved to GCS.

from google.cloud import storage 
from sklearn.linear_model import LinearRegression 
from sklearn.metrics import mean_squared_error 
import joblib 
import os

@dsl.component( 
    base_image="python:3.9", 
    packages_to_install=[ 
        "google-cloud-storage", 
        "scikit-learn", 
        "joblib" 
    ] 
) 
def train_model_op(processed_data_path: str, bucket_name: str) -> str: 
    # Train model and save to GCS 
    storage_client = storage.Client() 
    bucket = storage_client.bucket(bucket_name) 
    # Load preprocessed data and train model... 
    return f"gs://{bucket_name}/models"

Step 3: Register and Deploy Model

Once the model is trained, we register it with Vertex AI and deploy it to an endpoint. This will allow us to use the model for real-time predictions.

from google.cloud import aiplatform 
@dsl.component( 
    base_image="python:3.9", 
    packages_to_install=["google-cloud-aiplatform"] 
) 
def register_and_deploy_model_op(model_path: str, project_id: str, location: str) -> str: 
    # Initialize Vertex AI and deploy model 
    aiplatform.init(project=project_id, location=location) 
    model = aiplatform.Model.upload( 
        display_name="sp500-linear-regression-model-v1", 
        artifact_uri=model_path, 
        serving_container_image_uri="us-docker.pkg.dev/vertex-ai/prediction/sklearn-cpu.1-3:latest", 
    ) 
    endpoint = model.deploy( 
        deployed_model_display_name="sp500-endpoint", 
        machine_type="n1-standard-2" 
    ) 
    return endpoint.resource_name

Step 4: Model Scoring

Fresh data is scored using the deployed model and results are written back to BigQuery.

from google.cloud import aiplatform, bigquery 
import pandas as pd 
import numpy as np 
from sklearn.impute import SimpleImputer 
from sklearn.preprocessing import StandardScaler

@dsl.component( 
    base_image="python:3.9", 
    packages_to_install=[ 
        "google-cloud-aiplatform", 
        "google-cloud-bigquery", 
        "pandas", 
        "numpy", 
        "scikit-learn" 
    ] 
) 
def online_predict_op(endpoint_id: str, project_id: str) -> str: 
    # Load fresh data from BigQuery and score with deployed model 
    endpoint = aiplatform.Endpoint(endpoint_id) 
    df = bigquery.Client(project=project_id).query("SELECT * FROM ...").to_dataframe() 
    return "Predictions uploaded"

Once the model was deployed, it was used to generate predictions on fresh incoming data. Each prediction was evaluated using multiple factors, including predicted daily return, volatility, and confidence score. This scoring step runs as part of a structured workflow, ensuring predictions are generated consistently whenever new data becomes available.

We implemented a straightforward rules-based approach to generate buy and sell signals. A buy signal is triggered when the predicted daily return exceeds 0.01; the confidence score is greater than 0.8, and the predicted volatility remains below 0.05. Conversely, when predicted returns fall below 0.01, a sell signal is issued. The execution of this logic and the persistence of results back into BigQuery are coordinated as part of the automated pipeline, ensuring reliable and repeatable signal generation without manual intervention.

Results: Turning Analytics into Actionable Forecasts

By the end of Phase 2, the system can:

• Predict daily returns for every S&P 500 symbol
• Estimate volatility
• Assign confidence scores
• Generate buy / sell signals automatically
• Feed predictions into BI dashboards
• Run continuously via Control-M orchestration

This modernizes investment research — allowing analysts to evaluate model-driven signals alongside live market data.

Why Control-M is essential for orchestrating Predictive Analytics

As machine learning pipelines become more complex, managing them manually becomes nearly impossible. Control-M provides the foundation for reliable, scalable, and fully automated execution of the Phase 2 workflow. Control-M contributes value in several keyways:

1. End-to-end visibility into every stage of the pipeline
2. Advanced dependency management ensuring correct execution order
3. SLA monitoring & alerting for consistent delivery of predictions
4. Hybrid and multi-cloud orchestration across GCP, AWS, and on-prem systems
5. Error handling and auto-recovery to support production-grade reliability
6. Reusable, versioned workflows that work across dev, QA, and prod

Control-M becomes the operational backbone of the predictive analytics system — enabling trust, scalability, and reliability.

Business Outcomes at a Glance

• Daily predictive insights into returns, volatility, and trade signals
• 80–90% reduction in manual data preparation and scoring tasks
• Guaranteed SLA delivery of predictions via Control-M orchestration
• Cross-cloud integration enabling seamless BigQuery → Vertex AI → QuickSight analytics
• Full auditability and compliance for ML operations
• Scalable foundation for more advanced predictive models in future phases.

Visualization & Predictive Outputs

Once the Phase 2 pipeline completes, prediction results are written to BigQuery and automatically surfaced in Looker Studio. These dashboards represent the final output of the orchestrated workflow, translating model predictions into actionable insights.

Predicted Daily Return Over Time

This time-series chart shows predicted daily returns for multiple S&P 500 stocks across time. It highlights how the model’s predictions evolve under different market conditions, making it easier to observe trends, volatility spikes, and periods of stability. Because predictions are generated as part of an automated pipeline, the chart always reflects the most recent scoring run.

Predicted Gainers

This bar chart ranks stocks by their predicted daily return. It provides a quick view of potential short-term outperformers identified by the model, allowing analysts to focus attention on stocks with the strongest predicted upside.

Signal Distribution: Buy vs Sell

This donut chart summarizes the proportion of Buy and Sell signals generated by the scoring logic. Signals are derived using predicted return, confidence score, and volatility thresholds. The chart offers a high-level view of overall market sentiment inferred by the pipeline.

Volatility Breakdown by Entity

This visualization shows predicted volatility distributed across individual stocks. It helps identify which entities carry higher uncertainty, adding important risk context alongside predicted returns.

Predicted Daily Return by Entity

This comparison chart displays predicted returns across stocks. It enables side-by-side evaluation of expected performance, making relative strength easier to assess at a glance.

Predicted Volatility by Entity

This chart ranks stocks by predicted volatility. When used alongside return predictions, it supports risk-aware analysis by highlighting where higher returns may also come with higher uncertainty.

From Orchestration to Insight

These dashboards are not refreshed manually. They are the direct outcome of an end-to-end orchestrated workflow:

• Control-M coordinates data preparation and model execution
• Vertex AI generates predictions at scale
• BigQuery stores scoring results
• Looker Studio reflects updates automatically

Together, these components demonstrate how orchestration turns predictive analytics into a reliable, production-ready insight delivery system.

Conclusion

In Phase 2, we built an automated pipeline that spans feature engineering, model training, deployment, and scoring — all orchestrated reliably through Control-M.

By integrating Vertex AI with structured orchestration, predictive analytics becomes a dependable operational capability rather than an experimental exercise. This approach enables deeper market insight, faster decision-making, and a scalable foundation for future innovation.

Operationalizing Predictive Analytics with Control-M and Vertex AI was originally published in BMC Innovation on Medium, where people are continuing the conversation by highlighting and responding to this story.

By: Sunil Bemarkar and Michael Oladugba

In today’s fast-paced digital landscape, the ability to harness the power of artificial intelligence (AI) and machine learning (ML) is crucial for businesses aiming to gain a competitive edge. Amazon SageMaker is a game-changing ML platform that empowers businesses and data scientists to seamlessly navigate the development of complex AI models. One of its standout features is its end-to-end ML pipeline, which streamlines the entire process from data preparation to model deployment. Amazon SageMaker’s integrated Jupyter Notebook platform enables collaborative and interactive model development, while its data labeling service simplifies the often-labor-intensive task of data annotation.

It also boasts an extensive library of pre-built algorithms and deep learning frameworks, making it accessible to both newcomers and experienced ML practitioners. Amazon SageMaker’s managed training and inference capabilities provide the scalability and elasticity needed for real-world AI deployments. Moreover, its automatic model tuning, and robust monitoring tools enhance the efficiency and reliability of AI models, ensuring they remain accurate and up-to-date over time. Overall, Amazon SageMaker offers a comprehensive, scalable, and user-friendly ML environment, making it a top choice for organizations looking to leverage the potential of AI.

Bringing Amazon SageMaker and Control-M together

Amazon SageMaker simplifies the entire ML workflow, making it accessible to a broader range of users, including data scientists and developers. It provides a unified platform for building, training, and deploying ML models. However, to truly harness the power of Amazon SageMaker, businesses often require the ability to orchestrate and automate ML workflows and integrate them seamlessly with other business processes. This is where Control-M from BMC comes into play.

Control-M is a versatile application and data workflow orchestration platform that allows organizations to automate, monitor, and manage their data and AI-related processes efficiently. It can seamlessly integrate with SageMaker to create a bridge between AI modeling and deployment and business operations.

In this blog, we’ll explore the seamless integration between Amazon SageMaker and Control-M and the transformative impact it can have on businesses.

Amazon SageMaker empowers data scientists and developers to create, train, and deploy ML models across various environments — on-premises, in the cloud, or on edge devices. An end-to-end data pipeline will include more than just Amazon SageMaker’s AI and ML functionality, where data gets ingested from multiple sources, transformed, aggregated etc., before training a model and executing AI/ML pipelines with Amazon SageMaker. Control-M is often used for automating and orchestrating end-to-end data pipelines.

Here, we will specifically focus on integrating Amazon SageMaker with Control-M. When you have Amazon SageMaker jobs embedded in your data pipeline or complex workflow orchestrated by Control-M, you can harness the capabilities of Control-M for Amazon SageMaker to efficiently execute an end-to-end data pipeline that it also includes Amazon SageMaker pipelines.

Key capabilities

Control-M for Amazon SageMaker provides:

• Secure connectivity: Connect to any Amazon SageMaker endpoint securely, eliminating the need to provide authentication details explicitly
• Unified scheduling: Integrate Amazon SageMaker jobs seamlessly with other Control-M jobs within a single scheduling environment, streamlining your workflow management
• Pipeline execution: Execute Amazon SageMaker pipelines effortlessly, ensuring that your ML workflows run smoothly
• Monitoring and SLA management: Keep a close eye on the status, results, and output of Amazon SageMaker jobs within the Control-M Monitoring domain and attach service level agreement (SLA) jobs to your Amazon SageMaker jobs for precise control
• Advanced capabilities: Leverage all Control-M capabilities, including advanced scheduling criteria, complex dependencies, resource pools, lock resources, and variables to orchestrate your ML workflows effectively
• Parallel execution: Run up to 50 Amazon SageMaker jobs simultaneously per agent, allowing for efficient job execution at scale

Control-M for Amazon SageMaker compatibility

Before diving into how to set up Control-M for Amazon SageMaker, it’s essential to ensure that your environment meets the compatibility requirements:

• Control-M/EM: version 9.0.20.200 or higher
• Control-M/Agent: version 9.0.20.200 or higher
• Control-M Application Integrator: version 9.0.20.200 or higher
• Control-M Web: version 9.0.20.200 or higher
• Control-M Automation API: version 9.0.20.250 or higher

Please ensure you have the required installation files for each prerequisite available.

A real-world example:

The Abalone Dataset, sourced from the UCI Machine Learning Repository, has been frequently used in ML examples and tutorials to predict the age of abalones based on various attributes such as size, weight, and gender. The age of abalones is usually determined through a physical examination of their shells, which can be both tedious and intrusive. However, with ML, we can predict the age with considerable accuracy without resorting to physical examinations.

For this exercise, we used the Abalone tutorial provided by AWS. This tutorial efficiently walks users through the stages of data preprocessing, training, and model evaluation using Amazon SageMaker.

After understanding the tutorial’s nuances, we trained the Amazon SageMaker model with the Abalone Dataset, achieving satisfactory accuracy. Further, we created a comprehensive continuous integration and continuous delivery (CI/CD) pipeline that automates model retraining and endpoint updates. This not only streamlined the model deployment process but also ensured that the Amazon SageMaker endpoint for inference was always up-to-date with the latest trained model.

Setting up Control-M for Amazon SageMaker

Now, let’s walk through how to set up Control-M for Amazon SageMaker, which has three main steps:

1. Creating a connection profile that Control-M will use to connect to the Amazon SageMaker environment
2. Defining an Amazon SageMaker job in Control-M that will define what we want to run and monitor within Amazon SageMaker
3. Executing an Amazon SageMaker pipeline with Control-M

Step 1: Create a connection profile

To begin, you need to define a connection profile for Amazon SageMaker, which contains the necessary parameters for authentication and communication with SageMaker. Two authentication methods are commonly used, depending on your setup.

Example 1: Authentication with AWS access key and secret

{
  "AWS-SAGEMAKER":{
    "Type": "ConnectionProfile:AWS SageMaker",
    "SageMaker URL": "https://sagemaker.us-east-2.amazonaws.com",
    "AWS Region": "us-east-2",
    "Authentication": "Secret",
    "AWS Access key": "",
    "AWS Secret": "",
    "Connection Timeout": "100",
    "Description": "",
    "Centralized": true
  }
}

Figure 1. Authentication with AWS access key and secret

Example 2: Authentication with AWS IAM role from EC2 instance

{
  "AWS_SAGEMAKER_IAM": {
    "Type": "ConnectionProfile:AWS SageMaker",
    "SageMaker URL": "<SAGEMAKER_URL",
    "AWS Region": "<AWS_REGION",
    "Authentication": "NOSecret",
    "IAM Role" : "<IAM_ROLE_NAME>",
    "Connection Timeout": "100",
    "Description": "",
    "Centralized": true
  }
}

Figure 2. Authentication with AWS IAM role .

Choose the authentication method that aligns with your environment. It is important to specify the Amazon SageMaker job type exactly as shown in the examples above. Please note that Amazon SageMaker is case-sensitive, so make sure to use the correct capitalization.

Step 2: Define an Amazon SageMaker job

Once you’ve set up the connection profile, you can define an Amazon SageMaker job type within Control-M, which type enables you to execute Amazon SageMaker pipelines effectively.

{
  "mol-sagemaker-folder": {
  "Type": "Folder",
  "ControlmServer": "",
  "OrderMethod": "Manual",
  "RunAs": "",
  "CreatedBy": "",
  "Application": "",
  "DaysKeepActiveIfNotOk": "1",

  "AWS SageMaker_Job_2": {
    "Type": "Job:AWS SageMaker",
    "ConnectionProfile": "AWS-SAGEMAKER",
    "Pipeline Name": "",
    "Add Parameters": "unchecked",
    "Retry Pipeline Execution": "unchecked",
    "Host": "",
    "CreatedBy": "",
    "RunAs": "AWS-SAGEMAKER"
    }
  }
}

Figure 3. Example AWS SageMaker job definition.

In this example, we’ve defined an Amazon SageMaker job, specifying the connection profile to be used (“AWS-SAGEMAKER”). You can configure additional parameters such as the pipeline name, idempotency token, parameters to pass to the job, retry settings, and more. For a detailed understanding and code snippets, please refer to the BMC official documentation for Amazon SageMaker

Step 3: Executing the Amazon SageMaker pipeline with Control-M

It’s essential to note that the pipeline name and endpoint are mandatory JSON objects within the pipeline configuration. By executing the “ctm run” command on the pipeline.json file, it activates the pipeline’s execution within AWS.

First, we run “ctm build sagemakerjob.json” to validate our JSON configuration and then the “ctm run sagemakerjob.json” command to execute the pipeline.

Figure 4. Launching Amazon SageMaker job.

As seen in the screenshot above the “ctm run” command has launched the Amazon SageMaker job. The next screenshot shows the pipeline running from the Amazon SageMaker console.

Figure 5. View of data pipeline running in Amazon SageMaker console.

In the Control-M monitoring domain, users have the ability to view job outputs. This allows for easy tracking of pipeline statuses and provides insights for troubleshooting any job failures.

Figure 6. View of Amazon SageMaker job output from Control-M Monitoring domain.

Summary

In this blog, we demonstrated how to integrate Control-M with Amazon SageMaker to unlock the full potential of AWS ML services, orchestrating them effortlessly into your existing application and data workflows. This fusion not only eases the management of ML jobs but also optimizes your overall automation processes.

Stay tuned for more blogs on Control-M (self-hosted and SaaS) integrations! To learn more about Control-M integrations, visit our website.

Streamlining Machine Learning Workflows with Control-M and Amazon SageMaker was originally published in BMC Innovation on Medium, where people are continuing the conversation by highlighting and responding to this story.

By Sam Duru-Nnebue and Michael Oladugba

A trade analyst starts their morning scanning dashboards of the S&P 500.
To them, it’s all about candlesticks, moving averages, gainers, decliners, and volatility trends — ready at a glance, updated to the latest trading day.

What’s not visible is the hidden machinery behind those charts. The data isn’t magically there, it’s the result of a carefully choreographed ETL process: ingesting raw datasets from multiple sources, transforming them through cleansing and enrichment, calculating market indicators, and loading the results into analytical tables. From there, scheduled refresh operations push the latest figures into the dashboarding layer, ensuring the analyst always sees timely, accurate, and consistent market views.

In production environments — especially at enterprise scale — achieving speed, accuracy, and reliability across multi-cloud infrastructure requires more than just scripts or manual steps. It demands orchestration: a coordinated execution of every job, file transfer, and query that powers the dashboard.

Business Goal and Objectives

Build an automated ETL pipeline that ingests, cleans, and enriches S&P 500 data, delivering it to Amazon QuickSight dashboards via Google BigQuery — fully orchestrated with Control-M.

Process Flow:

Key Considerations

• Data arrives in raw CSV format with inconsistent formatting
• Historical data contains missing or malformed records
• Engineering metrics (like moving averages) must be calculated
• The pipeline must be modular and auditable
• Final outputs must support analytics and ML models downstream

Use Case and Dataset Background

This solution processes historical daily prices of S&P 500 companies over a 5-year period, obtained from this Kaggle dataset.

Use Case Summary

We transform unstructured CSV data into a clean, analytics-ready BigQuery table enriched with key financial indicators. The output powers dashboards and downstream from this would support ML pipelines for training and evaluation of models. We plan to cover the ML portion in a different blog.

Dataset Overview

• Source: Kaggle — S&P 500 stock data
• Raw Fields: Date, Open, Close, High, Low, Volume, Ticker
• Final Fields (added):

— Daily Return = (Close — Open) / Open

— 7-Day Moving Average (MA_7)

— 30-Day Moving Average (MA_30)

— 7-Day Volatility (standard deviation of Close)

Solution Architecture

Control-M Integration Plug-ins Used in This Architecture

To orchestrate this multi-cloud analytics pipeline, we utilized Control-M’s native integration plug-ins to automate and govern key tasks across AWS and Google Cloud. These plug-ins enabled seamless execution of file transfers, SQL-based transformations, and dashboard refreshes, all from within a unified Control-M workflow.

Control-M Managed File Transfer (Version 9.0.21.300)

• Enables secure, policy-driven movement of data between AWS S3 and Google Cloud Storage.
• Used in this pipeline to transfer the S&P 500 dataset from Amazon S3 (where it was initially downloaded) into a Google Cloud Storage bucket for BigQuery ingestion.
• Ensures cross-cloud data movement is automated, auditable, and resilient to failures.

Control-M for GCP BigQuery (Version 9.0.21.300)

• Executes SQL jobs and load operations in Google BigQuery.
• Automate schema creation, raw data ingestion, feature engineering, and analytics queries for identifying top gainers, decliners, and most volatile stocks.
• Provides centralized control and monitoring of all BigQuery processing steps.

Control-M for AWS QuickSight Version (9.0.20.200)

• Triggers full dataset refreshes within AWS QuickSight dashboards.
• Ensuring that newly processed insights (e.g., top gainers/decliners) are instantly reflected in business reports.
• Automates visualization updates as part of the end-to-end pipeline execution.

Workflow Breakdown

Workflow definition in Control-M

Prerequisites:

Before setting up the jobs in Control-M, ensure the required Control-M plug-ins (e.g., Managed File Transfer, BigQuery, etc.) are deployed in your environment. You’ll also need to create a connection profile for each plug-in so Control-M can authenticate and communicate with the respective services.

For step-by-step setup instructions, see the Control-M documentation:

Control-M Integrations

Note:
All JSON definitions shown in this blog are snippets for illustration. They capture only the key parts of each job (e.g., Type, Src, Dest, FileTransfers, SQLStatement). The full JSON files, with complete scheduling logic, events, and metadata, are available on our GitHub repo here.

DATA INGESTION AND TRANSFORMATION

Note: In the sections below, some steps show:

SQL → the query we wrote in BigQuery

JSON → the Control-M job definition automatically generated for orchestration

* BQ MFT Job s3-to-gcs

What it does:
 — Transfers the raw CSV file (all_stocks_5yr.csv) from an AWS S3 bucket to a Google Cloud Storage (GCS) bucket (sp500-etl).
 — Serves as the cross-cloud bridge between AWS and GCP.
 — This is the first step in the pipeline.

{ 

  "Type": "Job:FileTransfer", 
  "ConnectionProfileSrc": "MFTS3", 
  "ConnectionProfileDest": "joggcp", 
  "S3BucketNameSrc": "gcp-bigquery-source", 
  "GCSBucketNameDest": "sp500-etl", 
  "FileTransfers": [ 
    { 
      "Src": "/all_stocks_5yr.csv", 
      "Dest": "/", 
      "TransferType": "Binary" 
    } 
  ] 
}

* BQ Schema Table Creation

What it does:
 — Creates or replaces the sp500_raw table in BigQuery.
 — Defines the schema with columns like date, open, close, high, low, volume, and Name.
 — Waits for the MFT job to finish transferring the data.

CREATE OR REPLACE TABLE `sp500_dataset.sp500_raw` (
date DATE,
open FLOAT64,
high FLOAT64,
low FLOAT64,
close FLOAT64,
volume INT64,
Name STRING
);

{
  "Type": "Job:GCP BigQuery",
  "ConnectionProfile": "SP500-BIGQUERY",
  "Action": "Query",
  "Src": "File_Transfer_Job_2",
  "Dest": "BQ_Load_data_to_table",
  "FileTransfers": [
    {
      "Src": "s3://gcp-bigquery-source/all_stocks_5yr.csv",
      "Dest": "sp500_dataset.sp500_raw",
      "TransferType": "BigQueryLoad"
    }
  ],
  "SQLStatement": "CREATE OR REPLACE TABLE `sso-gcp-dba-ctm4-pub-cc10274.sp500_dataset.sp500_raw` (date DATE, open FLOAT64, high FLOAT64, low FLOAT64, close FLOAT64, volume INT64, Name STRING);"
}

* BQ Load data to table

What it does:
 — Loads the CSV file from the GCS bucket (gs://sp500-etl/all_stocks_5yr.csv) into the sp500_raw table.
 — Performs schema-based loading with a maximum of 10 bad records allowed.
 — Triggers the next transformation job upon success.

{
  "Type": "Job:GCP BigQuery", 
  "Action": "Load", 
  "Destination Table Properties": { 
    "tableId": "sp500_raw" 
  }, 
  "Destination/Source Bucket URIs": "gs://sp500-etl/all_stocks_5yr.csv", 
  "Load Options": "\"maxBadRecords\": 10" 
} 

* BQ Clean Data

What it does:
 — Reads from sp500_raw and creates a new enriched table called sp500_clean.
 — Adds calculated columns: daily_return, ma_7, ma_30, and volatility_7d.
 — This table becomes the foundation for analytics queries and dashboarding.

CREATE OR REPLACE TABLE sp500_dataset.sp500_clean AS
SELECT
DATE(date) AS date,
open, high, low, close, volume, Name,
SAFE_DIVIDE(close — open, open) AS daily_return,
AVG(close) OVER (PARTITION BY Name ORDER BY DATE(date) ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma_7,
AVG(close) OVER (PARTITION BY Name ORDER BY DATE(date) ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS ma_30,
STDDEV(close) OVER (PARTITION BY Name ORDER BY DATE(date) ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS volatility_7d
FROM sp500_dataset.sp500_raw
WHERE volume IS NOT NULL AND close IS NOT NULL;

{
  "Type": "Job:GCP BigQuery",
  "ConnectionProfile": "SP500-BIGQUERY",
  "Action": "Query",
  "Src": "BQ_Load_data_to_table",
  "Dest": "BQ_Clean_Data",
  "FileTransfers": [
    {
      "Src": "sp500_dataset.sp500_raw",
      "Dest": "sp500_dataset.sp500_clean",
      "TransferType": "BigQueryTransform"
    }
  ],
  "SQLStatement": "CREATE OR REPLACE TABLE `sso-gcp-dba-ctm4-pub-cc10274.sp500_dataset.sp500_clean` AS SELECT DATE(date) AS date, open, high, low, close, volume, Name, SAFE_DIVIDE(close - open, open) AS daily_return, AVG(close) OVER (PARTITION BY Name ORDER BY DATE(date) ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma_7, AVG(close) OVER (PARTITION BY Name ORDER BY DATE(date) ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS ma_30, STDDEV(close) OVER (PARTITION BY Name ORDER BY DATE(date) ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS volatility_7d FROM `sso-gcp-dba-ctm4-pub-cc10274.sp500_dataset.sp500_raw`;"
}

* AWS QuickSight Refresh

What it does:
 — Performs a full dataset refresh in AWS QuickSight using the dataset ID.
 — Ensures dashboards are updated with the most recent data in sp500_clean.
 — Triggers the analytics query jobs after completion.

{
  "Type": "Job:AWS QuickSight", 
  "AWS Dataset ID”: “98012734-e8cc-49a7-b8b4–19592abe275e”,
  "Refresh Type": "Full Refresh"
}

ANALYSIS PIPELINES/QUERIES

* BQ Top Daily Decliners

What it does:
 — Creates the top_daily_decliners table in BigQuery.
 — Selects the 10 stocks with the largest negative return on 2018–02–07.
 — Used to identify worst-performing stocks.

####SQL Query

CREATE OR REPLACE TABLE sp500_dataset.top_daily_decliners AS
SELECT Name, date,
ROUND(((close — open) / open) * 100, 2) AS percent_change
FROM sp500_dataset.sp500_clean
WHERE date = ‘2018–02–07’
ORDER BY percent_change ASC
LIMIT 10;

{
  "Type": "Job:GCP BigQuery",
  "ConnectionProfile": "SP500-BIGQUERY",
  "Action": "Query",
  "Src": "sp500_dataset.sp500_clean",
  "Dest": "sp500_dataset.top_daily_decliners",
  "FileTransfers": [
    {
      "Src": "sp500_dataset.sp500_clean",
      "Dest": "sp500_dataset.top_daily_decliners",
      "TransferType": "BigQueryTransform"
    }
  ],
  "SQLStatement": "CREATE OR REPLACE TABLE `sso-gcp-dba-ctm4-pub-cc10274.sp500_dataset.top_daily_decliners` AS SELECT Name, date, ROUND(((close - open) / open) * 100, 2) AS percent_change FROM `sso-gcp-dba-ctm4-pub-cc10274.sp500_dataset.sp500_clean` WHERE date = '2018-02-07' ORDER BY percent_change ASC LIMIT 10;"
}

* BQ Top Daily Gainers

What it does:
 — Creates the top_daily_gainers table.
 — Selects the 10 stocks with the highest percentage return on 2018–02–07.
 — Used for bullish market analysis.

CREATE OR REPLACE TABLE sp500_dataset.top_daily_gainers AS
SELECT Name, date,
ROUND(((close — open) / open) * 100, 2) AS percent_change
FROM sp500_dataset.sp500_clean
WHERE date = ‘2018–02–07’
ORDER BY percent_change DESC
LIMIT 10;

{
  "Type": "Job:GCP BigQuery",
  "ConnectionProfile": "SP500-BIGQUERY",
  "Action": "Query",
  "Src": "sp500_dataset.sp500_clean",
  "Dest": "sp500_dataset.top_daily_gainers",
  "FileTransfers": [
    {
      "Src": "sp500_dataset.sp500_clean",
      "Dest": "sp500_dataset.top_daily_gainers",
      "TransferType": "BigQueryTransform"
    }
  ],
  "SQLStatement": "CREATE OR REPLACE TABLE `sso-gcp-dba-ctm4-pub-cc10274.sp500_dataset.top_daily_gainers` AS SELECT Name, date, ROUND(((close - open) / open) * 100, 2) AS percent_change FROM `sso-gcp-dba-ctm4-pub-cc10274.sp500_dataset.sp500_clean` WHERE date = '2018-02-07' ORDER BY percent_change DESC LIMIT 10;"
}

* BQ Top Most Volatile

What it does:
 — Creates the top_daily_volatile table.
 — Calculates volatility as (high — low) / open * 100.
 — Highlights top 10 most volatile stocks for trading insights

CREATE OR REPLACE TABLE sp500_dataset.top_daily_volatile AS
SELECT Name, date,
ROUND(((high — low) / open) * 100, 2) AS intraday_volatility
FROM sp500_dataset.sp500_clean
WHERE date = ‘2018–02–07’
ORDER BY intraday_volatility DESC
LIMIT 10;

{
  "Type": "Job:GCP BigQuery",
  "ConnectionProfile": "SP500-BIGQUERY",
  "Action": "Query",
  "Src": "sp500_dataset.sp500_clean",
  "Dest": "sp500_dataset.top_daily_volatile",
  "FileTransfers": [
    {
      "Src": "sp500_dataset.sp500_clean",
      "Dest": "sp500_dataset.top_daily_volatile",
      "TransferType": "BigQueryTransform"
    }
  ],
  "SQLStatement": "CREATE OR REPLACE TABLE `sso-gcp-dba-ctm4-pub-cc10274.sp500_dataset.top_daily_volatile` AS SELECT Name, date, ROUND(((high - low) / open) * 100, 2) AS intraday_volatility FROM `sso-gcp-dba-ctm4-pub-cc10274.sp500_dataset.sp500_clean` WHERE date = '2018-02-07' ORDER BY intraday_volatility DESC LIMIT 10;"
}

Business Outcome

After building and cleaning our S&P 500 dataset in BigQuery, we connected it to Amazon QuickSight to create an interactive dashboard. The goal was to move from raw stock price data to meaningful financial insights that investors, analysts, or decision-makers could act on.

This dashboard combines both long-term market trends and short-term performance snapshots, allowing us to see how individual companies perform daily while also understanding the broader risk-reward dynamics of the S&P 500.

Below, we’ll walk through each visualization, explaining what we built, how we built it, and what story the data tells.

Closing Price Over Time (Line Chart):

What we did: Took cleaned daily stock prices (sp500_clean) and plotted the average closing price for selected companies across quarterly time intervals.

What it shows: A trend line of how major tech companies (AAPL, AMZN, FB, GOOG, MSFT, NFLX) have grown in market value over several years. It highlights long-term growth patterns and market leaders.

Top 20 Daily Gainers (Bar Chart)

What we did: Ran a query to calculate percent change = (close — open) / open for each stock on a given day and ranked them in descending order.

What it shows: The 20 companies with the highest daily returns (best performers). This gives investors a quick glance at momentum stocks for that day.

Top 20 Daily Decliners (Bar Chart)

What we did: Used the same percent change formula but sorted in ascending order.

What it shows: The 20 worst-performing companies on the same day, i.e., the biggest daily losers. This is critical for spotting declining or risky investments.

Daily Return vs Volatility (Scatter Plot — Risk/Reward Map)

What we did: Calculated daily returns and an intraday volatility metric = (high — low) / open for each stock. Then plotted return (x-axis) against volatility (y-axis).

What it shows: A risk-reward map of the S&P 500. Each dot is a stock, showing if high returns are associated with high volatility. Investors use this to balance growth potential vs. stability.

All together:

• The line chart → long-term growth view.
• The gainers/decliners → short-term performance snapshot.
• The scatter plot → risk vs reward tradeoff.

This shows us both daily insights and long-term trends, a very real-world financial analysis workflow.

Benefits of Using Control-M for S&P 500 ETL Orchestration

In a multi-cloud, data-intensive environment like the one used for this S&P 500 analytics pipeline, achieving reliable orchestration is critical. Control-M serves as the enterprise-grade backbone that brings all components — file transfer, data warehousing, transformation, and visualization — into a unified, governed, and traceable flow.

Rather than building and managing custom scripts or multiple schedulers to coordinate each component across AWS and Google Cloud, Control-M offers an out-of-the-box, low-code solution that ensures operational stability, clear dependencies, and visibility from end to end.

Here are the key benefits observed in this implementation:

1. End-to-End Orchestration Across Cloud Platforms

Control-M enables seamless orchestration across AWS (S3, QuickSight) and Google Cloud (GCS, BigQuery) without needing to manage multiple schedulers or cloud-specific workflows. This allows the data engineering team to work within a single framework for development, scheduling, execution, and monitoring.

• Unified management of S3, GCS, BigQuery, and QuickSight pipelines.
• Avoids building custom integration logic or maintaining third-party tools.
• Simplifies DevOps and deployment pipelines by consolidating control

2. Event-Driven Workflow Automation

This pipeline relies heavily on event-based execution — each job begins only when its prerequisite completes successfully. Control-M makes this straightforward using native event-handling features like eventsToAdd, eventsToWaitFor, and eventsToDelete.

• Prevents downstream jobs from running if upstream logic fails.
• Ensures data quality and consistency before analysis.
• Reduces manual oversight by enforcing conditional execution logic.

3. Robust Scheduling and Calendar Logic

Control-M supports sophisticated scheduling strategies beyond simple cron expressions. For example, this pipeline uses a custom EVERYDAY rule-based calendar, which can easily be adapted to exclude weekends, public holidays, or non-trading days without altering job logic.

• Aligns job execution with financial market hours or business calendars.
• Enables SLA enforcement and rescheduling policies.
• Reduces risk of missed or misaligned job runs.

4. Monitoring, Logging, and SLA Management

With Control-M, operators have real-time visibility into the status of each job, including start time, end time, duration, logs, return codes, and more. This monitoring domain becomes the command center for both support and data teams.

• Centralized dashboards for job health and trend analysis.
• Quick root cause diagnosis via access to historical logs.
• Built-in alerts and SLA breach notifications.

5. Governance, Compliance, and Audit Readiness

Control-M inherently tracks all job executions, parameters, changes, and outcomes, providing a strong audit trail that meets enterprise and regulatory compliance needs. In finance and capital markets, this is a major advantage.

• Detailed historical records of job executions and dependencies.
• Full traceability of data movement and transformations.
• Supports internal governance, external audits, and SOC 2/ISO compliance reviews.

6. Scalability and Maintainability

The Control-M workflow can scale as data volume grows or when more datasets are added. Adding new KPIs, features, or parallel workflows (e.g., new stock indices) can be achieved with minimal overhead.

• Modular folder/job structure makes workflows reusable.
• Easily accommodates additional job types (e.g., ML model training, alerting).
• Supports dynamic parameterization for flexible environments (dev/test/prod).

7. Low-Code Development for Faster Deployment

Jobs such as BigQuery queries, file transfers, and QuickSight refreshes are created using Control-M’s intuitive UI or JSON definitions. Developers don’t need to write extensive shell scripts or pipelines reducing complexity and time-to-deploy.

• Reduces onboarding time for new engineers.
• Encourages collaboration between data engineering and operations teams.
• Minimizes custom code and maintenance burden.

8. Error Handling and Recovery

Control-M’s built-in error handling mechanisms — such as automatic retries, job hold logic, and event-driven branching — reduce manual intervention in the case of transient or systemic failures.

• Supports conditional logic for graceful degradation.
• Allow jobs to retry intelligently or notify stakeholders upon failure.
• Enhances system resilience and reliability.

Summary of Control-M Benefits in this Use Case

• Orchestration across AWS and GCP from one tool.
• Event-driven architecture with strong dependency management.
• Flexible, rule-based scheduling for financial workflows.
• Centralized monitoring and alerting for all job states.
• Full audit trail for governance and compliance.
• Scalable architecture for future datasets and ML use cases.
• Low-code development accelerates delivery and reduces technical debt.
• Resilient error handling ensures continuity in production.

Conclusion

This blog demonstrates the full lifecycle of turning raw financial data into actionable insights. Using Control-M, we automated the ETL workflow: moving files from S3 to GCS, cleaning and transforming the data in BigQuery, and refreshing datasets in Amazon QuickSight. The dashboard then provides a live analytical layer, showcasing both market-wide trends (like long-term price growth) and daily market signals (such as top gainers, decliners, and volatility).

By integrating orchestration, cloud data warehousing, and visualization, we built a pipeline that not only processes large-scale stock data but also keeps insights fresh and relevant for decision-making. This same approach can be extended to other financial datasets or even different industries, making it a reusable framework for data-driven analysis at scale.

Want to run this yourself?

This isn’t a demo video — it’s a real, hands-on lab.

Click below to launch the S&P 500 interactive environment!

Operationalizing S&P 500 Analytics with Control-M, BigQuery, and Amazon QuickSight was originally published in BMC Innovation on Medium, where people are continuing the conversation by highlighting and responding to this story.

By: Sam Duru-Nnebue

Snowflake is one of the most commonly used data warehouses, and orchestrating Snowflake queries as part of a data pipeline is a very common use case in Apache Airflow. Snowflake activities requiring orchestration are usually part of a larger data pipeline that involves ingestion, transformation, and reverse ETL activities. However, in this blog we are zooming into one part of the data pipeline: orchestrating Snowflake with Control-M orchestrating the following activities.

• Create a table.
• Load the data.
• Run transformations on the data.
• Aggregate the data.
• Run data queries.

For this blog, we will use a sample dataset available on Kaggle. This dataset contains detailed records of airplane wildlife strike incidents from 1990 to 2023, involving military, commercial, or civil aircraft, as reported in the FAA Wildlife Strike Database. It covers just the first 100 columns of the dataset. It includes incident dates, affected airports, aircraft details, wildlife species, locations, flight phases, damage levels, and weather conditions at the time of each incident.

Included below is the flow of the orchestrated pattern:

Pre-requisites

To get the most out of this tutorial, you will need:

• To understand Snowflake basics. Here’s an Introduction to Snowflake to help get you started.
• Have a Snowflake account.
• Have access to Control M Automation API.
• Understand SQL basics. See this W3 SQL tutorial for background information.
• Have an AWS Account.

Create a security integration (Snowflake Oauth)

This operation initiates or updates a Snowflake OAuth security integration within the account, enabling OAuth-supporting clients to direct users to an authorization page. This step is crucial for generating access tokens, and potentially refresh tokens, facilitating secured access to Snowflake. For more details, visit this Snowflake OAUTH Setup.

To begin, create a Snowflake database, schema, role, stage* and warehouse.

*Snowflake stages are locations where data files are stored (staged) for loading and unloading data. See a screenshot of a Snowflake stage that attaches the S3 bucket to the Snowflake profile below.

Next we will begin with setting up a connection profile in Control-M for Snowflake and then create a workflow of jobs that will orchestrate a number of activities in Snowflake.

Configure a Snowflake connection​

1. In Control-M, go to Control-M Applications > Configuration > Quick Actions > Manage Centralized Connection Profile > Add Connection Profile and select Snowflake.
2. Enter the following information:

• Connection Profile Name: snowflake_test
• Description: (optional — can include an overview of what the profile will be doing)
• Account Identifier: Detailed here (generated based on Snowflake account creation details)
• Region: Your Snowflake region (example: us-east-1)
• Client ID: (generated from the OAuth setup above)
• Client Secret: (generated from the OAuth setup above)
• Refresh Token: (generated from OAuth setup above — make sure this value is url-encoded)
• Redirect URL: (generated from OAuth setup above — make sure this value is url-encoded)

Your connection should look something like the screenshot below:

This use case is designed to walk you through creating various jobs in Control-M, tailored for diverse operational needs. It includes job types like file transfers, SQL statements in Snowflake, Snowflake-stored procedures, and SQL files for Snowflake within Control M.

Create a workspace

In Control-M, go to Planning, click on the dropdown next to Home and select Add New Workspace. Name the workspace, give a description of what would be in it, select your server and folder, and click on Create.

This workspace serves as a container for your jobs and workflows. After a workspace is created, it automatically creates a folder for you, which is where you can start creating your jobs. See an example below.

Creating a file transfer job (S3_Object_Transfer)

To create a file transfer job, enter the following details:

• Type: Job:FileTransfer
• Description: Transfers the CSV file from a public-facing S3 bucket to the S3 bucket attached to the Snowflake stage.
• Key Operations: Source to Destination: Transfers /TestCSV2.csv from source to/snowflake/folder in destination.

See an example in the figure below:

"S3_Object_Transfer": {
  "Type": "Job:FileTransfer",
  "ConnectionProfileSrc": "MFTS3",
  "ConnectionProfileDest": "MFTS3",
  "S3BucketNameSrc": "INSERT_SOURCE_BUCKET_NAME",
  "S3BucketNameDest": " INSERT_DESTINATION_BUCKET_NAME ",
  "Host": "INSERT_HOSTNAME",
  "CreatedBy": "CREATOR_EMAIL_AUTOMATICALLY_GENERATED",
  "Description": "Transfer CSV from s3 bucket to Snowflake stage",
  "RunAs": "MFTS3+MFTS3",
  "Variables": [
    {"FTP-LOSTYPE": "Unix"},
    {"FTP-CONNTYPE1": "S3"},
    {"FTP-ROSTYPE": "Unix"},
    {"FTP-CONNTYPE2": "S3"},
    {"FTP-CM_VER": "9.0.00"}
  ],
  "FileTransfers": [
    {
      "TransferType": "Binary",
      "TransferOption": "SrcToDestFileWatcher",
      "Src": "/TestCSV2.csv",
      "Dest": "/snowflake/",
      "ABSTIME": "0",
      "TIMELIMIT": "0",
      "UNIQUE": "0",
      "SRCOPT": "0",
      "IF_EXIST": "0",
      "DSTOPT": "0",
      "RECURSIVE": "0",
      "EXCLUDE_WILDCARD": "0",
      "TRIM": "1",
      "NULLFLDS": "0",
      "VERNUM": "0",
      "CASEIFS": "0",
      "FileWatcherOptions": {
        "UnitsOfTimeLimit": "Minutes"
      },
      "IncrementalTransfer": {
        "IncrementalTransferEnabled": false,
        "MaxModificationAgeForFirstRunEnabled": false,
        "MaxModificationAgeForFirstRunInHours": "1"
      }
    }
  ],
  "When": {
    "WeekDays": ["NONE"],
    "MonthDays": ["ALL"],
    "DaysRelation": "OR"
  },
  "eventsToAdd": {
    "Type": "AddEvents",
    "Events": [{"Event": "S3_Object_Transfer-TO-CREATE_TABLE"}]
  }
}

Creating a Snowflake SQL statement job (CREATE_TABLE)

To create this job, enter the details below. Note, the Control-M Snowflake plugin utilizes the Snowflake SQL API to connect to Snowflake. When submitting multiple SQL statements, the API requires statements to be concatenated and setting the MULTI_STATEMENT_COUNT parameter. Please see this Snowflake API documentation for more details.

• Type: Job:Snowflake
• Action: Creates or replaces a table (CSV_TBL) in Snowflake using a template that infers schema from the specified CSV file, allowing dynamic schema creation based on file content.
• Description: This job dynamically creates a table structure based on the CSV file’s schema.
• Dependencies: Waits for the event S3_Object_Transfer-TO-CREATE_TABLE, ensuring the file transfer is completed before attempting to create the table.

See an example in the figure below:

"CREATE_TABLE": {
  "Type": "Job:Snowflake",
  "ConnectionProfile": "INSERT_CONNECTION_PROFILE_NAME",
  "Action": "SQL Statement",
  "Add Condition": "unchecked",
  "Activity Options": "Copy From External Datasource",
  "Overwrite": "Yes",
  "Start or Pause Snowpipe": "Pause Snowpipe",
  "Show More Options": "checked",
  "Show Output": "checked",
  "Database": "INSERT_DATABASE_NAME",
  "Schema": " INSERT_SCHEMA_NAME ",
  "Snowflake SQL Statement": 
    "CREATE OR REPLACE TABLE CSV_TBL USING TEMPLATE (" + 
    "SELECT ARRAY_AGG(OBJECT_CONSTRUCT(*)) WITHIN GROUP (" + 
    "ORDER BY ORDER_ID) FROM TABLE (INFER_SCHEMA(" + 
    "LOCATION=>'@airportdata/TestCSV2.csv', " + 
    "FILE_FORMAT=>'csv_format_0')));",
  "Statement Timeout": "10",
  "Role": "SYSADMIN",
  "Warehouse": "INSERT_SNOWFLAKE_WAREHOUSE",
  "Host": "INSERT_HOSTNAME",
  "CreatedBy": "CREATOR_EMAIL_AUTOMATICALLY_GENERATED",
  "RunAs": " INSERT_CONNECTION_PROFILE_NAME ",
  "When": {
    "WeekDays": ["NONE"],
    "MonthDays": ["ALL"],
    "DaysRelation": "OR"
  },
  "eventsToWaitFor": {
    "Type": "WaitForEvents",
    "Events": [{"Event": "S3_Object_Transfer-TO-CREATE_TABLE"}]
  },
  "eventsToAdd": {
    "Type": "AddEvents",
    "Events": [{"Event": "CREATE_TABLE-TO-COPY_INTO_TABLE"}]
  },
  "eventsToDelete": {
    "Type": "DeleteEvents",
    "Events": [{"Event": "S3_Object_Transfer-TO-CREATE_TABLE"}]
  }
}

Creating a Snowflake SQL statement job (COPY_INTO_TABLE)

To create this job, enter the following details:

• Type: Job:Snowflake
• Action: Executes an SQL statement to load data into a table (CSV_TBL) from a staged file (@airportdata/TestCSV2.csv) with specific file format settings.
• Description: This job loads data into Snowflake using the COPY command, preparing the data for further analysis or processing. It waits for an event indicating readiness to proceed, ensuring proper sequence in data workflow.
• Dependencies: Waits for the event CREATE_TABLE-TO-COPY_INTO_TABLE before execution.

See an example in the figure below:

"COPY_INTO_TABLE": {
  "Type": "Job:Snowflake",
  "ConnectionProfile": " INSERT_CONNECTION_PROFILE_NAME ",
  "Action": "SQL Statement",
  "Add Condition": "unchecked",
  "Activity Options": "Copy From External Datasource",
  "Overwrite": "Yes",
  "Start or Pause Snowpipe": "Pause Snowpipe",
  "Show More Options": "checked",
  "Show Output": "checked",
  "Database": " INSERT_DATABASE_NAME ",
  "Schema": "INSERT_SCHEMA_NAME",
  "Snowflake SQL Statement": 
    "COPY INTO csv_tbl FROM @airportdata/TestCSV2.csv " + 
    "FILE_FORMAT = (skip_header = 1);%4E",
  "Statement Timeout": "10",
  "Role": "SYSADMIN",
  "Warehouse": "INSERT_SNOWFLAKE_WAREHOUSE ",
  "Host": " INSERT_HOSTNAME ",
  "CreatedBy": " CREATOR_EMAIL_AUTOMATICALLY_GENERATED ",
  "RunAs": " INSERT_CONNECTION_PROFILE_NAME ",
  "When": {
    "WeekDays": ["NONE"],
    "MonthDays": ["ALL"],
    "DaysRelation": "OR"
  },
  "eventsToWaitFor": {
    "Type": "WaitForEvents",
    "Events": [{"Event": "CREATE_TABLE-TO-COPY_INTO_TABLE"}]
  },
  "eventsToAdd": {
    "Type": "AddEvents",
    "Events": [{"Event": "COPY_INTO_TABLE-TO-QUERY_DATA"}]
  },
  "eventsToDelete": {
    "Type": "DeleteEvents",
    "Events": [{"Event": "CREATE_TABLE-TO-COPY_INTO_TABLE"}]
  }
}

Creating a Snowflake SQL statement job (QUERY_DATA)

To create this job, enter the following details:

• Type: Job:Snowflake
• Action: Executes an SQL query to list the top 10 airports by incident count.
• Description: This job simplifies data review by aggregating incident data at high-traffic airports, offering a prioritized view of locations for focused analysis from a table called “TESTTABLE2”.
• Dependencies: Waits for COPY_INTO_TABLE-TO-QUERY_DATA, ensuring data is loaded and ready for querying.

See an example in the figure below:

"QUERY_DATA": {
  "Type": "Job:Snowflake",
  "ConnectionProfile": " INSERT_CONNECTION_PROFILE_NAME ",
  "Action": "SQL Statement",
  "Add Condition": "unchecked",
  "Activity Options": "Copy From External Datasource",
  "Overwrite": "Yes",
  "Start or Pause Snowpipe": "Pause Snowpipe",
  "Show More Options": "checked",
  "Show Output": "checked",
  "Database": " INSERT_DATABASE_NAME ",
  "Schema": " INSERT_SCHEMA_NAME ",
  "Snowflake SQL Statement": 
    "SELECT AIRPORT, COUNT(*) AS Total_Incidents " + 
    "FROM TESTTABLE2 " + 
    "GROUP BY AIRPORT " + 
    "ORDER BY Total_Incidents DESC " + 
    "LIMIT 10;",
  "Warehouse": " INSERT_SNOWFLAKE_WAREHOUSE ",
  "Role": "SYSADMIN",
  "Statement Timeout": "10",
  "Host": " INSERT_HOSTNAME ",
  "CreatedBy": " CREATOR_EMAIL_AUTOMATICALLY_GENERATED ",
  "Description": 
    "This query lists the top 10 airports with the highest number of incidents.",
  "RunAs": " INSERT_CONNECTION_PROFILE_NAME ",
  "When": {
    "WeekDays": ["NONE"],
    "MonthDays": ["ALL"],
    "DaysRelation": "OR"
  },
  "eventsToWaitFor": {
    "Type": "WaitForEvents",
    "Events": [{"Event": "COPY_INTO_TABLE-TO-QUERY_DATA"}]
  },
  "eventsToAdd": {
    "Type": "AddEvents",
    "Events": [{"Event": "lochiamocosiQUERY_DATA-TO-UPDATE_DATA"}]
  },
  "eventsToDelete": {
    "Type": "DeleteEvents",
    "Events": [{"Event": "COPY_INTO_TABLE-TO-QUERY_DATA"}]
  }
}

Creating a Snowflake SQL statement job (UPDATE_DATA)

To create this job, enter the following details:

• Type: Job:Snowflake
• Action: Updates records in the table, setting TIME_OF_DAY to ‘NA’ where it is “null” or “unknown”.
• Description: This job ensures data consistency for analysis by cleaning up incomplete time-of-day records in a table called “TESTTABLE2”.
• Dependencies: Waits for QUERY_DATA-TO-UPDATE_DATA, maintaining the sequence in data processing.

See an example in the figure below:

"UPDATE_DATA": {
  "Type": "Job:Snowflake",
  "ConnectionProfile": " INSERT_CONNECTION_PROFILE_NAME ",
  "Database": " INSERT_DATABASE_NAME ",
  "Schema": " INSERT_SCHEMA_NAME ",
  "Action": "SQL Statement",
  "Snowflake SQL Statement": 
    "UPDATE TESTTABLE2 SET TIME_OF_DAY = 'NA' " + 
    "WHERE TIME_OF_DAY IS NULL OR TIME_OF_DAY = 'Unknown';",
  "Add Condition": "unchecked",
  "Activity Options": "Copy From External Datasource",
  "Overwrite": "Yes",
  "Start or Pause Snowpipe": "Pause Snowpipe",
  "Statement Timeout": "10",
  "Show More Options": "checked",
  "Role": "SYSADMIN",
  "Warehouse": " INSERT_SNOWFLAKE_WAREHOUSE ",
  "Show Output": "checked",
  "Host": " INSERT_HOSTNAME ",
  "CreatedBy": " CREATOR_EMAIL_AUTOMATICALLY_GENERATED ",
  "Description": 
    "This statement sets the TIME_OF_DAY to 'NA' for all records " + 
    "where TIME_OF_DAY is currently null or Unknown",
  "RunAs": " INSERT_CONNECTION_PROFILE_NAME ",
  "When": {
    "WeekDays": ["NONE"],
    "MonthDays": ["ALL"],
    "DaysRelation": "OR"
  },
  "eventsToWaitFor": {
    "Type": "WaitForEvents",
    "Events": [{"Event": "lochiamocosiQUERY_DATA-TO-UPDATE_DATA"}]
  },
  "eventsToAdd": {
    "Type": "AddEvents",
    "Events": [{"Event": "lochiamocosiUPDATE_DATA-TO-Query_for_a_specific_year"}]
  },
  "eventsToDelete": {
    "Type": "DeleteEvents",
    "Events": [{"Event": "lochiamocosiQUERY_DATA-TO-UPDATE_DATA"}]
  }
}

Creating a Snowflake SQL statement job (Query_for_a_specific_year)

To create this job, enter the following details:

• Type: Job:Snowflake
• Action: Executes an SQL query to fetch incident details for a specific year (1994), ordered by month and date.
• Description: This job provides a temporal analysis of incidents, focusing on a specific year for detailed historical insight in a table called “TESTTABLE2”.
• Dependencies: Waits for UPDATE_DATA-TO-Query_for_a_specific_year, ensuring data is up-to-date for accuracy.

See an example in the figure below:

"Query_for_a_specific_year": {
  "Type": "Job:Snowflake",
  "ConnectionProfile": " INSERT_CONNECTION_PROFILE_NAME",
  "Database": " INSERT_DATABASE_NAME",
  "Schema": " INSERT_SCHEMA_NAME ",
  "Action": "SQL Statement",
  "Snowflake SQL Statement": 
    "SELECT AIRPORT, INCIDENT_DATE, INCIDENT_MONTH, INCIDENT_YEAR " + 
    "FROM TESTTABLE2 WHERE INCIDENT_YEAR = 1994 " + 
    "ORDER BY INCIDENT_MONTH, INCIDENT_DATE;",
  "Add Condition": "unchecked",
  "Activity Options": "Copy From External Datasource",
  "Overwrite": "Yes",
  "Start or Pause Snowpipe": "Pause Snowpipe",
  "Statement Timeout": "10",
  "Show More Options": "checked",
  "Role": "SYSADMIN",
  "Warehouse": " INSERT_SNOWFLAKE_WAREHOUSE",
  "Show Output": "checked",
  "Host": " INSERT_HOSTNAME",
  "CreatedBy": " CREATOR_EMAIL_AUTOMATICALLY_GENERATED",
  "Description": 
    "This query fetches details of incidents that occurred in the year 1994, " + 
    "ordered by month and date.",
  "RunAs": " INSERT_CONNECTION_PROFILE_NAME",
  "When": {
    "WeekDays": ["NONE"],
    "MonthDays": ["ALL"],
    "DaysRelation": "OR"
  },
  "eventsToWaitFor": {
    "Type": "WaitForEvents",
    "Events": [{"Event": "lochiamocosiUPDATE_DATA-TO-Query_for_a_specific_year"}]
  },
  "eventsToAdd": {
    "Type": "AddEvents",
    "Events": [
      {"Event": "lochiamocosiQuery_for_a_specific_year-TO-Get_aggregate_stats"}
    ]
  },
  "eventsToDelete": {
    "Type": "DeleteEvents",
    "Events": [{"Event": "lochiamocosiUPDATE_DATA-TO-Query_for_a_specific_year"}]
  }
}

Creating a Snowflake SQL statement job (Get_aggregate_stats)

To create this job, enter the following details:

• Type: Job:Snowflake
• Action: Executes a stored procedure (GetAggregateStatistics) to compute aggregate statistics, returning a JSON string with the results.
• Dependencies: Waits for Query_for_a_specific_year-TO-Get_aggregate_stats, ensuring that specific data prerequisites are met.

See an example in the figure below:

"Get_aggregate_stats": {
  "Type": "Job:Snowflake",
  "ConnectionProfile": " INSERT_CONNECTION_PROFILE_NAME",
  "Action": "Stored Procedure",
  "Add Condition": "unchecked",
  "Activity Options": "Copy From External Datasource",
  "Overwrite": "Yes",
  "Start or Pause Snowpipe": "Pause Snowpipe",
  "Show More Options": "checked",
  "Show Output": "checked",
  "Database": " INSERT_DATABASE_NAME",
  "Schema": " INSERT_SCHEMA_NAME ",
  "Stored Procedure Name": "GetAggregateStatistics",
  "Statement Timeout": "10",
  "Role": "SYSADMIN",
  "Warehouse": " INSERT_SNOWFLAKE_WAREHOUSE",
  "Host": " INSERT_HOSTNAME",
  "CreatedBy": " CREATOR_EMAIL_AUTOMATICALLY_GENERATED",
  "Description": 
    "This query calculates the average latitude and longitude of incidents " +
    "and the total number of incidents per year, only including years " +
    "with more than 5 incidents. The procedure will return a JSON string " +
    "containing the result set. The procedure executes a SELECT " +
    "statement to retrieve aggregate statistics from the table, " +
    "and then returns the result as a JSON string.",
  "RunAs": " INSERT_CONNECTION_PROFILE_NAME",
  "When": {
    "WeekDays": ["NONE"],
    "MonthDays": ["ALL"],
    "DaysRelation": "OR"
  },
  "eventsToWaitFor": {
    "Type": "WaitForEvents",
    "Events": [
      {"Event": "lochiamocosiQuery_for_a_specific_year-TO-Get_aggregate_stats"}
    ]
  },
  "eventsToAdd": {
    "Type": "AddEvents",
    "Events": [
      {"Event": "lochiamocosiGet_aggregate_stats-TO-Select_Data"}
    ]
  },
  "eventsToDelete": {
    "Type": "DeleteEvents",
    "Events": [
      {"Event": "lochiamocosiQuery_for_a_specific_year-TO-Get_aggregate_stats"}
    ]
  }
}

Creating a Snowflake SQL statement job (Select_Data)

To create this job, enter the following details:

• Type: Job:Snowflake
• Action: Executes a SQL query from a file “select_data3”, targeting incidents in June for a specific operator (United Airlines).
• Description: This job focuses on extracting specific operational data, demonstrating targeted data retrieval capabilities in Snowflake in a table called “TESTTABLE2”.
• Dependencies: Waits for Get_aggregate_stats-TO-Select_Data, aligning with data analysis flow.

See an example in the figure below:

"Select_Data": {
  "Type": "Job:Snowflake",
  "Load SQL File": 
    "SELECT * FROM TESTTABLE2 " + 
    "WHERE incident_month = '6' " + 
    "AND operator = 'UNITED AIRLINES';",
  "ConnectionProfile": " INSERT_CONNECTION_PROFILE_NAME",
  "Database": " INSERT_DATABASE_NAME",
  "Schema": " INSERT_SCHEMA_NAME ",
  "Action": "Run SQL File",
  "Add Condition": "unchecked",
  "Activity Options": "Copy From External Datasource",
  "Overwrite": "Yes",
  "Start or Pause Snowpipe": "Pause Snowpipe",
  "Statement Timeout": "20",
  "Show More Options": "checked",
  "Role": "SYSADMIN",
  "Warehouse": " INSERT_SNOWFLAKE_WAREHOUSE",
  "Show Output": "checked",
  "Host": " INSERT_HOSTNAME",
  "CreatedBy": " CREATOR_EMAIL_AUTOMATICALLY_GENERATED",
  "Description": 
    "This query retrieves all information on incidents that occurred " + 
    "in the Month '6' and where the Operator was 'United' " + 
    "according to the table",
  "RunAs": " INSERT_CONNECTION_PROFILE_NAME",
  "When": {
    "WeekDays": ["NONE"],
    "MonthDays": ["ALL"],
    "DaysRelation": "OR"
  },
  "eventsToWaitFor": {
    "Type": "WaitForEvents",
    "Events": [
      {"Event": "lochiamocosiGet_aggregate_stats-TO-Select_Data"}
    ]
  },
  "eventsToDelete": {
    "Type": "DeleteEvents",
    "Events": [
      {"Event": "lochiamocosiGet_aggregate_stats-TO-Select_Data"}
    ]
  }
}

The workflow summary

Below is a summary of all the JSON code referenced above and a description of what the entire workflow does:

• S3 Object Transfer: Transfers a CSV file from one S3 bucket to another, preparing the data for ingestion into Snowflake.
  * Watches for the specified CSV file in the source bucket and transfers it to the destination bucket once available.
  * Adds an event upon successful completion to trigger the next step in the workflow.
• CREATE_TABLE: Dynamically creates or replaces a table in Snowflake using the schema inferred from the transferred CSV file.
  * Waits for an event indicating that the S3 object transfer has completed before executing.
  * Executes an SQL statement to create or replace a table in Snowflake, structured according to the CSV file’s inferred schema.
• COPY_INTO_TABLE: Loads data from the transferred CSV file into the newly created or updated Snowflake table.
  * Waits for the table creation to complete before executing.
  * Uses a copy command to load data from the CSV file into the Snowflake table, ensuring data is ready for analysis.
  * Adds an event to indicate that data loading is complete, allowing subsequent data processing or analysis jobs to begin.
• QUERY_DATA: Executes an SQL query to retrieve and list the 10 airports with the highest number of incidents from the loaded data.
  * Waits for data to be loaded into the table before executing.
  * Extracts valuable insights from the data, specifically focusing on identifying airports with the highest incident counts.
• UPDATE_DATA: Updates specific records within the Snowflake table, particularly adjusting the time of day column to ensure consistency across the dataset.
  * This step is crucial for maintaining data integrity and accuracy, and preparing the dataset for reliable analysis.
  * Executes following the data loading step, as indicated by its dependency on the COPY_INTO_TABLE job’s completion event.
• Query_for_a_specific_year: Filters and retrieves incident details for a specific year, providing a focused view of data for that period.
  * Executes after a specific event, likely related to data preparation or a previous analysis step.
  * Offers detailed insights into incidents by year, aiding in temporal analysis of the data.
• Get_aggregate_stats: Calls a stored procedure to calculate and return aggregate statistics, such as average latitude and longitude of incidents and total incidents per year, from the data.
  * Waits for a trigger from a specific query execution to start.
  * Provides a high-level overview of the data, focusing on aggregate statistics that could inform broader trends or patterns.
• Select_Data: Runs an SQL query from a file to retrieve information on incidents that occurred in a specific month and were associated with a specific operator.
  * Waits for the aggregate statistics to be calculated before executing.
  * Delivers detailed data filtering based on specific criteria, such as time period and operator, to support targeted analysis.

Throughout this workflow, the use of events to trigger subsequent actions ensures a seamless, automated progression from data transfer and table creation to data loading, querying, and analysis. This orchestrated approach enables efficient data processing and analysis within a cloud-based data warehouse environment, leveraging Snowflake’s capabilities.

The following images show job completion

• Logs:

• Output:

External resources showing job completion

• Visual view
• Log view

Best practices and considerations:

• Make use of Snowflake stages when loading data from an external system using Control-M. Control-M transfer jobs like the first one we created in this demo (S3_Object_Transfer) require a Snowflake stage be set up. Stages generally make it much easier to repeatedly load data in a specific format.
• Use the “SYSADMIN” role for most of your Snowflake job processes within Control-M. This has a few benefits:
  * Security and principle of least privilege: The Account Admin role has the highest level of access within a Snowflake account, including the ability to manage users and roles, billing, and account settings. Using Sysadmin for data-related jobs adheres more closely to the principle of least privilege, reducing the risk of accidental or malicious modifications to critical account settings.
  * Operational efficiency: The Sysadmin role is optimized for performing and managing data operations, potentially offering more efficient execution of data-related jobs compared to using an account-wide administrative role, which is not specifically tailored for such tasks.
  * Best practices and compliance: Using the Sysadmin role for database and job management aligns with best practices for managing cloud and database environments. It helps organizations to structure their roles and responsibilities in a way that enhances operational efficiency and security posture.

Stay tuned for more blogs on Control-M integrations! Let us know if there’s an integration or topic you’d like to learn more about by commenting below. To learn more about how Control-M can help orchestrate you data pipelines, click here.

Orchestrating Snowflake with Control-M was originally published in BMC Innovation on Medium, where people are continuing the conversation by highlighting and responding to this story.

By: Michael Oladugba and Sam Duru-Nnebue

In today’s financial landscape, making informed and equitable credit decisions is paramount. Machine learning models power critical decisions about credit risk assessment. However, these models must not only be accurate but also fair and explainable. In this post, we’ll explore how to orchestrate an end-to-end credit risk prediction workflow that incorporates bias detection and explainability using Amazon SageMaker and orchestrating this pipeline with Control-M.

Business Goal and Objectives

The goal: Build a responsible AI workflow that balances operational efficiency with governance in credit decision making.

Key Objectives:

• Ensure fairness by minimizing bias from sensitive attributes.
• Increase transparency with SHAP-based explanations.
• Automate ML workflows across platforms.
• Meet compliance standards with audit-ready documentation.
• Detect bias before it affects decisions.
• Build stakeholder trust through clear, explainable outcomes.

Key Considerations- Deploying ML for credit decisions brings unique challenges:

• Regulatory pressure to prove decisions are free from discrimination.
• Complex workflows that require coordination across teams and tools.
• High demand for explainability from regulators and customers.
• The need for ongoing monitoring to detect drift and maintain fairness.

The Pre-Production Validation Imperative

Before deploying a credit risk model, AI/ML teams must validate its fairness, explainability, and compliance readiness. This includes bias detection across demographics, SHAP-based explanations, and audit documentation — in this use case requiring coordination across Kubernetes, Amazon SageMaker, and AWS Lambda.

These steps are highly interdependent: bias detection depends on preprocessing, explainability requires clean data, and compliance outputs rely on completed analyses. Manual execution introduces delays, errors, and inconsistencies.

By using Control-M, these validation steps are orchestrated into a seamless pipeline. The result: reduced validation time from weeks to days, consistent compliance standards, and clear insights to determine model readiness.

Dataset and Use Case Background

The use case implemented in this workflow is adapted from AWS’s SageMaker Clarify credit risk example, which uses a real-world credit scoring scenario based on the German Credit Data dataset.

Use Case Summary
The scenario models a loan approval process where machine learning is used to assess whether an applicant is likely to default. It mirrors operational credit decisions made by financial institutions and introduces ethical challenges around explainability and fairness.

Dataset Overview

• Source: UCI Statlog German Credit Data
• Size: 1,000 loan applicants
• Target: Credit classification (good or bad)
• Attributes: 20 features, including:
  - Loan duration
  - Credit amount
  - Age
  - Employment status
  - Housing and marital status
  - Purpose of the loan
• Sensitive Attributes: Age and gender (used for bias detection)

This dataset is ideal for demonstrating regulatory-sensitive AI, as it closely reflects real-world compliance obligations in the lending space. In this implementation, we’ve orchestrated the full workflow around this use case to ensure not only performance, but also fairness, transparency, and auditability.

Solution Architecture

The credit risk prediction pipeline, model explainability, and bias detection steps are adapted from this SageMaker Clarify demo published by AWS. We’ve extended this solution by integrating orchestration capabilities through Control-M, enabling robust scheduling, event-driven automation, and governance workflows across SageMaker, Kubernetes, and Lambda. You can find all the code used in this blog in the following repository: https://github.com/mol-bmc/sagemaker-credit-risk-reliability.git.

Technologies used in this scenario :

This architecture brings automation, governance, and operational visibility to every step of the credit risk ML lifecycle — from data prep to explainability reporting.

Architecture

Workflow Breakdown

Let’s examine the key components of our orchestrated workflow:

Control-M Plug-ins Used:

This workflow leverages out-of-the-box Control-M Application plug-ins for:

• Kubernetes: To launch and monitor containerized preprocessing jobs
• Amazon SageMaker: To trigger model training and SageMaker Clarify pipelines
• AWS Lambda: For serverless data cleanup operations

These plug-ins simplify integration with cloud-native services, allowing for low-code configuration, native API calls, and consistent event-based orchestration across platforms.

Connection Profiles

Before creating a job in Control-M that can execute the jobs in this workflow, make sure the above mentioned plug-ins have been deployed and also create connection profiles. A connection profile contains authorization credentials — such as the username, password, and other plug-in-specific parameters — and enables you to connect to the application server with only the connection profile name. Connection profiles can be created using the web interface and then retrieved in JSON format using Control-M’s Automation API.

Workflow triggered by data asset

In this use case we assume that the consumer credit data is uploaded to an Amazon S3 bucket in the form of a file (data asset) named SouthGermanCredit.asc. which automatically calls a rule defined in S3 to trigger and an AWS Lambda function that in turn triggers the Control-M workflow. For details on how to set this up please refer to this blog: Triggering Control-M Jobs Automatically with AWS Lambda and S3.

The code snippets below are extracted from the repository. You can access the full source code for this blog here.

1. Data Preprocessing (Kubernetes Job)

The workflow begins with data preprocessing, implemented as a Kubernetes Job in Control-M:

"preprocessing-kubernetes-job": {
  "Type": "Job:Kubernetes",
  "Job Spec Yaml": "apiVersion: batch/v1\r\nkind: Job\r\nmetadata:\r\n  name: credit-preprocessing-job\r\nspec:\r\n  template:\r\n    spec:\r\n      serviceAccountName: default  # This SA should have S3 access via IAM role\r\n      containers:\r\n      - name: credit-preprocessing\r\n        image: 623469066856.dkr.ecr.us-west-2.amazonaws.com/sagemaker-credit-reliability:latest\r\n        command: [\"/bin/sh\", \"-c\", \"python3 /app/preprocess.py && sleep 60\"]\r\n        env:\r\n        - name: S3_INPUT_PATH\r\n          value: \"s3://sagemaker-sample-files/datasets/tabular/uci_statlog_german_credit_data/SouthGermanCredit.asc\"\r\n        - name: S3_OUTPUT_BUCKET\r\n          value: \"sam-sagemaker-crr\"\r\n        - name: S3_OUTPUT_PREFIX\r\n          value: \"preprocessed-data\"\r\n        - name: AWS_DEFAULT_REGION\r\n          value: \"us-west-2\"\r\n",
  "ConnectionProfile": "MOL-K8S-CONNECTION-PROFILE",
  "Get Pod Logs": "Get Logs",
  "Host": "mol-agent-installation-sts-0",
  "Application": "ML_CreditRisk_Bias_Detection",
  "eventsToAdd": {
    "Type": "AddEvents",
    "Events": [{
      "Event": "preprocessing-kubernetes-job-TO-AWS_SageMaker_Job_2"
    }]
  }
}

This job:

• Pulls raw credit data from S3
• Performs feature engineering and data cleansing
• Stores the processed dataset back to S3
• Signals completion to trigger the next step

2. Model Training Pipeline (SageMaker)

Once preprocessing completes, Control-M triggers the SageMaker model training pipeline:

"Sagemaker-Model-Training": {
  "Type": "Job:AWS SageMaker",
  "Pipeline Name": "CRRModelPipeline",
  "eventsToWaitFor": {
    "Type": "WaitForEvents",
    "Events": [{
      "Event": "preprocessing-kubernetes-job-TO-AWS_SageMaker_Job_2"
    }]
  }
}]
 }
}

The CRRModelPipeline handles:

• Splitting data into training/validation sets
• Training a machine learning model
• Evaluating model performance
• Creating a model artifact for deployment

3. Cleanup Operations (Lambda)

After model training, a Lambda function performs cleanup operations:

"sagemaker-crr-clean-files": {
  "Type": "Job:AWS Lambda",
  "Function Name": "sagemaker-crr-clean-files",
  "Parameters": "{}",
  "eventsToWaitFor": {
    "Type": "WaitForEvents",
    "Events": [{
      "Event": "Sagemaker-Model-Training-TO-sagemaker-crr-clean-files"
    }]
  }
}

This step ensures:

• Column Name Standardization: Removes preprocessing artifacts like ‘onehotencoder__’ and ‘remainder__’ prefixes from column names that were added during the SageMaker preprocessing pipeline
• Data Format Optimization: Cleans and standardizes the training and validation datasets for downstream bias detection analysis
• S3 Data Organization: Creates cleaned versions of the datasets (train_cleaned.csv and validation_cleaned.csv) that are properly formatted for SageMaker Clarify

4. Bias Detection and Explainability (SageMaker Clarify)

The final step in our workflow leverages SageMaker Clarify for responsible AI practices:

"sagemaker-crr-bias-detection-explainability": {
  "Type": "Job:AWS SageMaker",
  "Pipeline Name": "BiasDetectionExplainabilityPipeline",
  "eventsToWaitFor": {
    "Type": "WaitForEvents",
    "Events": [{
      "Event": "sagemaker-crr-clean-files-TO-sagemaker-crr-bias-detection-explainability"
    }]
  }
}

This pipeline:

• Analyzes the model for potential biases (pre-training and post-training)
• Generates SHAP values to explain model predictions
• Creates visualization artifacts for stakeholder review
• Generates compliance documentation

OUTPUT IMAGES:

Here is what the defined full workflow looks like in Control-M:

Planning Domain: This is where the full ML workflow is visually defined and configured. It includes four jobs: a Kubernetes-based preprocessing job, the SageMaker training pipeline, a file cleaning Lambda function, and a final SageMaker pipeline for bias detection and explainability. Each job is sequentially linked, ensuring a smooth, automated progression from raw data ingestion to model fairness evaluation. This domain defines how, when, and under what conditions each job is triggered and monitored.

Monitoring Domain: This shows the real-time execution status of each job in the workflow. All four jobs, from preprocessing to explainability, have successfully completed, with exact start and end timestamps displayed. This interface is used by operators and engineers to monitor job health, duration, and flow dependencies in production, ensuring the full ML lifecycle executes reliably.

SageMaker Pipelines executed in the Workflow:

The Model Training pipeline included XGBoost training, Model Evaluation, and the Model Deployment steps. First, the pipeline trains an XGBoost model on preprocessed credit application data using the train_xgboost.py script. This script loads training data from S3, fits the model, and outputs a compressed model artifact. The next step, evaluate.py, evaluates the model’s performance using metrics like accuracy and AUC on a validation dataset to ensure the model meets performance standards. Finally, the deploy_model2.py script deploys the trained model to a SageMaker endpoint, making it accessible for real-time inference. This endpoint is used to score incoming credit applications and return risk predictions instantly.

The Bias Detection and Explainability pipeline, which includes Bias Detection and SHAP Explainability steps, uses SageMaker Clarify to assess fairness and interpretability of the deployed credit risk model. It runs post-training bias checks to identify any discrimination based on sensitive attributes like gender and uses SHAP to explain individual predictions. This pipeline ensures that the model is not only accurate but also fair and transparent, providing visual artifacts and reports that support regulatory audits and build trust with stakeholders.

Orchestration Benefits with Control-M

Control-M provides several key benefits for this ML workflow:

1. Event-Driven Execution

The workflow uses sophisticated event coordination to manage job dependencies:

json
"eventsToAdd": {
    "Type": "AddEvents",
    "Events": [{
        "Event": "preprocessing-kubernetes-job-TO-AWS_SageMaker_Job_2"
    }]
}

What’s happening in this configuration:

• Event Generation: When the preprocessing Kubernetes job completes successfully, it automatically generates an event named “preprocessing-kubernetes-job-TO-AWS_SageMaker_Job_2”
• Event Naming Convention: The event name follows a clear pattern: [source-job]-TO-[target-job], making dependencies immediately visible
• Automatic Triggering: The SageMaker training job is configured to wait for this specific event before starting execution
• Failure Handling: If the preprocessing job fails, no event is generated, preventing downstream jobs from processing invalid data

This approach ensures:

• Jobs execute in the correct sequence with clear dependency management
• Failed jobs don’t trigger downstream processes
• Real-time status updates across the entire workflow
• Automatic retry logic for transient failures

2. Advanced Scheduling Capabilities

The workflow leverages Control-M’s flexible scheduling system:

json
"When": {
    "RuleBasedCalendars": {
        "Included": ["EVERYDAY"],
        "EVERYDAY": {
            "Type": "Calendar:RuleBased",
            "When": {
                "DaysRelation": "OR",
                "WeekDays": ["NONE"],
                "MonthDays": ["ALL"]
            }
        }
    }
}

Breaking down the scheduling configuration:

• Calendar Definition: “EVERYDAY” creates a custom calendar rule that Control-M references for scheduling decisions
• Rule-Based Logic: The “Calendar:RuleBased” type allows complex scheduling rules beyond simple cron expressions
• Day Selection Strategy:
  - “DaysRelation”: “OR” means the job runs if ANY of the specified conditions are met
  - “WeekDays”: [“NONE”] indicates no specific weekday restrictions
  - “MonthDays”: [“ALL”] means the job runs on every day of the month
• Flexible Execution: This configuration essentially creates a daily schedule while maintaining the flexibility to easily modify timing rules

This scheduling flexibility enables:

• Regular Model Retraining: Automatically retrain models with fresh data on a predictable schedule
• Business-Aligned Scheduling: Easily align with reporting cycles and business requirements by modifying calendar rules
• Holiday and Weekend Handling: Intelligent scheduling around business calendars without manual intervention
• SLA Management: Ensure workflows are completed within required timeframes with built-in scheduling constraints.

3. Cross-Platform Integration

Control-M seamlessly integrates different computing platforms:

• Kubernetes for preprocessing
• SageMaker for ML operations
• Lambda for auxiliary functions

This eliminates the need for custom integration code or multiple workflow tools.

4. Monitoring and Visibility

Control-M provides visibility across the entire workflow:

• Real-time status monitoring
• Detailed job logs
• Historical execution records
• SLA management

Implementing Responsible AI Practices

Beyond the technical workflow, this implementation addresses responsible AI requirements:

Bias Detection

SageMaker Clarify helps identify potential biases:

• Pre-training data bias metrics
• Post-training model bias metrics
• Analysis of sensitive attributes (age, gender, etc.)

Model Explainability

Explainability features provide insights into model decisions:

• Feature importance scores
• SHAP values for individual predictions
• Counterfactual examples for rejected applications

Compliance Documentation

The workflow generates documentation for regulatory compliance:

• Model cards with performance metrics
• Bias assessment reports
• Explainability artifacts

Understanding Key Concepts

SHAP (SHapley Additive exPlanations) Explainability

SHAP values provide a unified framework for interpreting machine learning model predictions by quantifying each feature’s contribution to individual predictions.

How SHAP Works in Credit Risk:

• For each loan application, SHAP calculates how much each feature (income, credit history, debt-to-income ratio) pushes the prediction toward approval or denial
• Values are additive: the sum of all SHAP values equals the difference between the model’s prediction and the average prediction
• Positive SHAP values indicate features that increase approval likelihood, while negative values indicate features that decrease it

Business Value:

• Customer Communication: Loan officers can explain to applicants exactly why their application was approved or denied
• Regulatory Compliance: Provides auditable explanations for regulatory review
• Model Debugging: Data scientists can identify when models rely on unexpected or problematic features

Outcomes and Impact

Operational Excellence:

Automation Efficiency: The end-to-end workflow has achieved a high level of automation, reducing manual intervention from many hours per week to just a few hours for monitoring and exception handling. This allows data science teams to focus on model innovation rather than operational tasks.

Deployment Speed: Model deployment time has decreased from weeks to days, enabling faster response to market changes and regulatory requirements. The automated pipeline eliminates the traditional handoffs between teams that create delays.

Error Reduction: Automated workflows have reduced human errors drastically, particularly in data preprocessing and model evaluation steps. Standardized processes ensure consistent quality across all model deployments.

By combining Control-M’s enterprise orchestration capabilities with SageMaker’s advanced machine learning features, we’ve created an automated, responsible credit risk prediction workflow that transforms how financial institutions approach AI governance. This solution ensures that:

1. Models are consistently trained on schedule with the latest data and embedded fairness constraints
2. All processing steps execute reliably in the correct sequence with comprehensive error handling
3. Models are automatically evaluated for bias, explainability, and performance against regulatory standards
4. Compliance documentation is generated automatically, reducing audit preparation time and ensuring consistency
5. Stakeholders receive clear insights into model behavior and decision factors through automated SHAP explanations

This implementation demonstrates how financial institutions can leverage modern orchestration and ML tools to build robust, fair, and explainable credit risk models while dramatically improving operational efficiency and regulatory compliance.

The automated workflow has proven that responsible AI practices don’t have to slow down innovation — they can actually accelerate it by embedding governance into the development process rather than treating it as a separate, time-consuming step.

Real-World Implementation Results

To demonstrate the practical impact of our automated responsible AI workflow, let’s examine actual output from the execution of the bias detection and explainability pipeline.

SageMaker Clarify Analysis Report

The following results showcase the comprehensive analysis generated by our automated workflow:

Model Configuration Details

• Model Used: XGBoost model
• Features Analyzed: 50 total features from the credit risk dataset
• Analysis Method: KernelSHAP explanations with 2,000 samples
• Infrastructure: Deployed on ml.m5.xlarge instance for optimal performance

SHAP Explainability Results

The automated SHAP analysis revealed several key insights:

Feature Attribution Analysis: The system computed KernelSHAP explanations across all 50 features, identifying the 10 most influential features for each prediction. This automated analysis eliminates the manual effort previously required to understand model behavior.

Individual Instance Explanations: Each credit application receives a detailed breakdown showing how specific features contributed to the approval or denial decision. The visualization uses a red-blue color scale where:

• Red indicators: Higher feature values that typically favor approval
• Blue indicators: Lower feature values that may indicate higher risk
• SHAP values: Quantify each feature’s specific contribution to the final decision

Baseline Establishment: The system automatically established a baseline using representative feature values:

json
"baseline": [
    [0.0, 0.0, 1.0, 0.0, 0.0, 0.0, 0.0, 1.0, 0.0, 0.0,
     0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 1.0, 0.0, 0.0, 1.0,
     0.0, 0.0, 0.0, 0.0, 1.0, 0.0, 0.0, 0.0, 1.0, 0.0,
     1.0, 0.0, 0.0, 0.0, 0.0, 1.0, 0.0, 1.0, 0.0, 1.0,
     4.0, 36.0, 10974.0, 1.0, 1.0, 4.0, 2.0, 26.0, 2.0, 2.0]
]

This baseline represents typical applicant characteristics and serves as the reference point for computing individual SHAP values.

Analysis Configuration Insights

The automated configuration demonstrates several best practices:

Robust Sampling: Using 2,000 samples ensures statistical significance while maintaining reasonable computation time.

Logit Transformation: The “use_logit”: true setting provides more interpretable explanations by working with log-odds rather than raw probabilities.

Local SHAP Values: Saving local SHAP values enables detailed audit trails and supports regulatory requirements for decision explanations.

Mean Absolute Aggregation: The “mean_abs” aggregation method provides stable, interpretable feature importance rankings.

Automated Documentation Benefits

This analysis report exemplifies how the automated workflow delivers immediate value:

Regulatory Compliance: The detailed configuration parameters and baseline values provide auditors with complete transparency into the model’s decision-making process.

Operational Efficiency: What previously required days of manual analysis is now generated automatically within hours of model training completion.

Stakeholder Communication: The standardized report format enables consistent communication with customers, regulators, and internal teams.

Quality Assurance: Automated generation eliminates human errors in analysis configuration and ensures consistent methodology across all model deployments.

Impact on Credit Decision Process

This automated explainability analysis has transformed how the organization handles credit decisions:

• Customer Service: Loan officers can now provide specific, data-driven explanations for any credit decision
• Appeal Process: Customers who are denied can receive concrete guidance on which factors to improve
• Risk Management: The consistent analysis helps identify emerging patterns in credit risk factors
• Compliance Reporting: Regulatory reports can be generated instantly with full documentation of the decision process

Next Steps and Future Enhancements

To further enhance this workflow and expand its capabilities, consider implementing:

Advanced Monitoring Capabilities

• Concept Drift Detection: Implement statistical tests to identify when model assumptions become invalid due to changing market conditions
• Performance Degradation Alerts: Set up automated monitoring that triggers retraining when model accuracy drops below specified thresholds
• Real-time Bias Monitoring: Extend bias detection to monitor live predictions, not just batch training processes
• Enhanced Model Management
• A/B Testing Framework: Implement controlled experiments to compare model versions before full deployment
• Champion/Challenger Models: Maintain multiple model versions and automatically promote the best performer
• Model Ensemble Strategies: Combine multiple models to improve both accuracy and fairness

Business Process Integration

• Approval Workflow Integration: Connect bias detection results to business approval processes, requiring human review for high-risk decisions
• Customer Communication: Automatically generate personalized explanations for credit decisions that customers can easily understand
• Regulatory Reporting: Create automated reports that feed directly into regulatory filing systems

Advanced Analytics

• Counterfactual Analysis: Generate “what-if” scenarios showing customers exactly what changes would improve their approval chances
• Segment-Specific Models: Develop specialized models for different customer segments while maintaining overall fairness
• Economic Impact Analysis: Quantify the business impact of bias reduction and explainability improvements

By continuously evolving your ML operations with these enhancements, you can ensure your credit risk models remain at the forefront of both accuracy and responsible AI practices, setting new industry standards for fair and transparent lending decisions.
_________________________________________________________________
This blog post demonstrates the integration of Control-M with Amazon SageMaker for orchestrating responsible AI workflows. For more information on implementing similar solutions, contact your BMC or AWS representative.

Operationalizing Responsible AI for Credit Risk with Control-M and Amazon SageMaker was originally published in BMC Innovation on Medium, where people are continuing the conversation by highlighting and responding to this story.

By Sumit Pal and Basil Faruqui

Disclaimer — This blog post does not discuss Large Language Model Operations (LLMOps) from the point of view of building or fine-tuning LLMs or LLMOps for SLM (small language models). LLMOps as discussed in this post is part of building LLM powered AI applications.

Despite the excitement around generative AI (GenAI), many LLM-based applications fail to move beyond proof of concept. Gartner predicts that by 2025, over 30% of GenAI projects will be abandoned due to poor data quality, rising costs, lack of governance, or unclear business value.

This even with the time to develop being faster than with traditional ML and NLP approaches. The main reasons for this are low user value, low performance, suffering from accuracy and failing to clear compliance and regulatory issues. Despite their transformative potential, LLMs require methodical execution across traditional lifecycle steps. Continuing from part 1 of the LLMOps blog series, part 2 dives into details of the stages, components, associated tasks, workflow and personas in the LLMOps practice.

LLMOps Stages

It is prudent to involve LLMOps practice from the beginning of AI application development, from outlining use cases to deployment and post deployment of LLM-powered applications. Figure 1 shows the different stages of LLM application development and the associated tasks across the stages. LLMs and foundational models are evolving rapidly. Hype often overshadows the challenges involved. It is imperative to involve personas with LLM experience and expertise to integrate LLMs within the business processes of an enterprise.

The set of tasks data, ML and AI teams need, to build applications includes the following:

• Identify business specific use cases for LLMs backed up by metrics outlining the ROI of using an AI-driven approach.
• Selecting an LLM provider — With so many LLM providers to choose from, organizations need to evaluate which provider to use based on factors like business priorities, performance, latency, functionality, bias mitigation, governance and security. Data and AI engineers need to define the criteria and select LLM providers accordingly. At the end of this step, teams should have a list of the few top proprietary or open source LLMs to leverage for their applications.
• LLM integration — Required both for Retrieval Augmented Generation (RAG) -based solutions as well as applications that depend on LLM API invocations. This involves subtasks like prompt engineering to develop a set of prompts and associated templates and store them in a prompt catalog that supports version control.
  This step involves customizing LLMs with RAG patterns especially when using proprietary data and domain context adaptation. This requires selecting vector databases, data pre-processing, data chunking — algorithms and choices around ranking the results and then augmenting the prompt.
• Cross cutting tasks that need to be applied across all tasks include: security management, observability, monitoring, feedback loop and data governance. These require defining, outlining and building metrics to track security, accuracy, bias and ethical considerations based on types of end-users and geographies where the application will be deployed.

Role of workflow & orchestration in LLMOps

AI-based workflows are becoming increasingly common as LLMs mature and permeate in the enterprise and embed themselves across different applications in the business processes. To build useful applications, GenAI needs to be integrated with existing systems and process workflows. It involves integrating with prompt templates, APIs, and tool invocations to trigger actions across systems with frameworks like LangChain and LlamaIndex to connect models with data sources to orchestrate the flow of information across components.

Diversity of tasks in the workflow chain necessitates a strategic approach to orchestration, which includes different facets of a broader orchestration strategy. From choosing the right LLM model to parameters configuration, understanding and tuning the economics of scale, adhering to SLAs, environmental, security and privacy metrics to evaluate the outcomes are critical aspects. LLM workflows also need to ensure durable, reliable, scalable fault tolerant execution of tasks that can be re-tried or rolled back.

Data, ML and LLM orchestration ensures all tasks in the workflow chain work coherently to address multi-dimensional challenges in creating successful AI applications. Workflow and data orchestration tools are rapidly evolving to integrate with LLMs and incorporate LLMs as a part of the overall business processes into data and ML pipelines. This involves invoking LLMs and agents, orchestrating multi-step AI reasoning to validate responses, handle branching and conditional logic along with error and exception handling with retries and human-in-the-loop feedback. Data privacy, security, and cost considerations are essential components of the LLMOps workflow.

Industry use cases:

Supply Chain Optimization

A global manufacturing company improves supply chain resilience by orchestrating an ETL pipeline that extracts real-time inventory, supplier performance, and logistics data from multiple ERP and warehouse management systems. This structured data feeds an LLM workflow that analyzes supplier risks, predicts potential delays, and drafts proactive mitigation strategies. For instance, if a shipment delay is detected, the LLM generates recommended alternative sourcing plans and auto-drafts supplier communications. The orchestration platform coordinates the full data ingestion, LLM-based reasoning, exception handling, and integration back into procurement and supply chain management applications — enhancing decision-making speed and significantly reducing supply chain disruption.

Accelerate customer onboarding

Let’s consider the example of a financial services company which automates customer onboarding by orchestrating an ETL pipeline that extracts customer data from core banking systems, CRM and ERP applications, performs data quality checks, enriches the data, and then feeds it into an LLM-based workflow. The LLM generates personalized onboarding documents, explains terms and conditions in natural language, and answers customer queries through an AI assistant. Orchestration tools manage the data pipeline, model invocation, and dynamic prompt generation based on customer profiles, and human-in-the-loop approvals — ensuring compliance, accuracy, and customer satisfaction while significantly reducing manual workload and overall onboarding time.

Personas and Skillsets for LLMOps

LLMOps demands a multifaceted approach that merges technical skills with practical considerations. This comprises building necessary data pipelines and deploying them as well as the skills to ensure models are performant, optimal, well governed, grounded and cost-effective. Organizations cannot build LLM-driven applications without proper data management, data and ML engineering pipelines with DataOps and MLOps practices. Some of the key personas involved include:

• Data and AI Engineers (LLM focused) /developers who select, test, evaluate LLMs, develop the GenAI applications this could also include AI application developers who build AI applications with backend frameworks like Langchain, front end frameworks like Streamlit of the GenAI application.
• Prompt engineers who are expert prompters who create and manage prompts. They build, test, version control prompts and associate prompts with inputs and corresponding outputs.
• DevOps and DataOps personas who set up environments, automate deployments for the data pipeline, ML pipelines and infrastructure supporting them.
• AI governance teams comprising of auditors, risk and compliance teams who review output from models to check bias, ethics, verify data privacy and leakage issues.
• AI testers who test applications in coordination with AI product managers to ensure compliance and quality of results across the use cases.
• GenAI end users who consume outputs from GenAI applications and share data to rate the quality of output.

LLMOps life cycle

LLMOps life cycle management has become a critical step to ensure the success of GenAI applications in enterprises. Since LLMs generate free-form text, it accelerates some of the steps and processes but additionally introduce new complexities that require new tools and methodologies and extra caution and care when deploying to production for general use.

LLMOps life cycle can be divided into the following areas:

Data preparation — This is the most time-consuming task for organizations that leverage the RAG pattern to integrate LLMs with their proprietary data to improve accuracy and performance in providing context to the LLMs. This step includes rigorous data cleaning, elimination of bad inconsistent data.

Leveraging proprietary datasets with the RAG patterns requires data preparation and building the right kind of chunking approach. Proprietary data needs to be cleaned and anonymized to prevent unintentional data leakage of sensitive information. For evaluation, data collection and preparation of the collected data for proper response evaluation is a critical step. Lack of comprehensive evaluation with the right datasets leads to limited insights into model behavior and establishing product reliability.

Deployment — Post deployment, LLM applications require ensuring accessibility and prompt responsiveness across diverse application scenarios. Applications need to maintain high operational efficiency and rapid response times especially in large-scale enterprise applications. Achieving this level of responsiveness involves thorough planning and optimization of both the models and the infrastructure. Key strategies for performance include data caching and applying load balancing to manage user requests efficiently.

LLM Observability — LLM observability is a critical element in the modern LLM infrastructure stack. The goal of observability is to proactively detect before an issue emerges and provide analytics for deeper inspection and diagnose root cause before it impacts business and end users. The objective is to establish a deployment environment where LLMs consistently deliver optimal performance and offer users swift and precise language processing capabilities across various applications.

Monitoring application behavior in real-time for hallucination, accuracy, security, ethical and compliance issues and course correcting is extremely challenging when developing LLM application. Neglecting monitoring can seriously hamper insights and affect product adoption and performance.

Good observability principles should include a plan what to observe — from inputs to outputs to the system itself for SLAs and failures. Ideally for GenAI applications both input and response from LLMs should be observed for bias and ethical considerations as well as prompt injections, apart from the usual observability metrics associated with performance, latency, scalability and fault-tolerance to ensure post-deployment LLM applications are operating as expected.

LLM observability with continuous monitoring that tracks relevant KPIs ensures AI applications maintain accuracy, response time, to control error rates ensuring output remains consistent and dependable within certain thresholds.

With AI application, just tracking metrics and logs isn’t enough, monitoring bad inputs, identifying prompt failures with logs and traces and calls record tracking responses are imperative to diagnose quality issues for trust, reliability and compliance.

Modern infrastructures, especially for AI applications, have gotten more complex and having observability solutions in place is an essential process in identifying and troubleshooting reliability and latency issues. AI systems are highly distributed, making observability more critical and challenging. LLM-powered applications can quickly become cost prohibitive based on concurrent calls with large prompt context windows, observability also helps organizations to analyze token usage and call metrics to keep infrastructure spending in control.

Governance, security and evaluation — Governance and compliance are vital in LLMOps. This involves detecting compliance, privacy violations and risk mitigation to preserve integrity of the applications and user trust and acceptance. LLM applications should adhere to national, regional and local regulations. Effectively implementing transparency, explainability and accountability in all responses are work in progress as the LLM stack and landscape matures.

Evaluation — LLM-based applications also require evaluation and tooling. Methodology around this is evolving. Text evaluation needs metrics like factual consistency, relevance, and coherence. Evaluation of both input and output is a critical step in the life cycle of an LLM application development and unless these are standardized and continuously evolve along with the evolution of the product and use cases, LLM-driven application will struggle to gain adoption in production. Evaluation is the bottleneck throughout the development life cycle. Testing/evaluation are critical elements across each step of the LLM application development life cycle, from model selection to experimentation and deployment. Measuring correctness of text data is difficult to define and implement. LLMs are also susceptible to prompt injection attacks which can trick LLMs to ignore instructions and generate undesirable content.

Infrastructure — Deploying, configuring and monitoring the data pipeline to support RAG architecture, involves various components in the chain. These include storage of unstructured and structured data, processing pipeline to chunk the data and create embeddings (by invoking LLM models with APIs) and storing them in a vector database. Storing and version control of prompts and LLM responses to track performance, optimize tuning and control costs based on token usage are critical to improve the quality of the application.

Future of LLMOps

In the rapidly evolving world of AI, it is hard to predict the future of LLMOps. But LLMOps are here to stay, just like DevOps, DataOps and MLOps and will weave in as a part of the technology and data stack.

Of late, AI agents have been the talk of the AI universe along with agentic workflows. Agentic workflows are AI-driven processes where tasks are dynamically and autonomously executed with minimal human intervention. Agentic workflows coordinate across models with memory, planning, reasoning, and integration with external tools. This has given rise to new Ops involving agents called AgentOps.

Each of these discrete practices of XOps (Devops, Dataops, MLOps, LLMOps) will need to be coordinated, synchronized and orchestrated by higher level tools like Control-M from BMC to build end-to-end business processes in enterprises. Workflow leveraging AI systems will be non-deterministic and hence there will be a need of constant monitoring, evaluation and decision making that make tools like Control-M indispensable for enterprises as they race to adapt AI technologies.

LLMOps — Tasks, Workflow and Personas was originally published in BMC Innovation on Medium, where people are continuing the conversation by highlighting and responding to this story.

Financial Fraud Detection is the process of monitoring transactions and customer behavior to identify and stop fraudulent activity. To get highly effective fraud detection models, lots of transaction data is aggregated and analyzed. In this blog we will discuss the latter with specific focus on orchestrating a multi-cloud fraud analysis pipeline. The dataset used for this exercise is sourced from Kaggle. The data card for this dataset offers the following detail:

“This dataset presents a synthetic representation of mobile money transactions, meticulously crafted to mirror the complexities of real-world financial activities while integrating fraudulent behaviors for research purposes. Derived from a simulator named PaySim, which utilizes aggregated data from actual financial logs of a mobile money service in an African country, this dataset aims to fill the gap in publicly available financial datasets for fraud detection studies. It encompasses a variety of transaction types including CASH-IN, CASH-OUT, DEBIT, PAYMENT, and TRANSFER over a simulated period of 30 days, providing a comprehensive environment for evaluating fraud detection methodologies. By addressing the intrinsic privacy concerns associated with financial transactions, this dataset offers a unique resource for researchers and analysts in the field of financial security and fraud detection.”

Dataset Structure

The csv file on Kaggle includes the following columns of data:

• step: Represents a unit of time in the real world, with 1 step equating to 1 hour. The total simulation spans 744 steps, equivalent to 30 days.
• type: Transaction types include CASH-IN, CASH-OUT, DEBIT, PAYMENT, and TRANSFER.
• amount: The transaction amount in the local currency.
• nameOrig: The customer initiating the transaction.
• oldbalanceOrg: The initial balance before the transaction.
• newbalanceOrig: The new balance after the transaction.
• nameDest: The transaction’s recipient customer.
• oldbalanceDest: The initial recipient’s balance before the transaction. Not applicable for customers identified by ‘M’ (Merchants).
• newbalanceDest: The new recipient’s balance after the transaction. Not applicable for ‘M’ (Merchants).
• isFraud: Identifies transactions conducted by fraudulent agents aiming to deplete customer accounts through transfers and cash-outs.
• isFlaggedFraud: Flags large-scale, unauthorized transfers between accounts, with any single transaction exceeding 200,000 being considered illegal.

What is the Fraud Analysis pipeline doing:

The dataset referenced above is made available to us by a third party on an Azure Blob storage. Using the csv file, we will accomplish two main things:

1. Take the csv file and load it into Snowflake for business analytics.
2. Take the csv file and process the data so that it can be normalized and loaded into a different Snowflake table. The normalized data will make it easy for us to use it with ML algorithms to drive outcomes such as:

• Behavioral Analytics: By analyzing users’ behavior, businesses can detect deviations from normal patterns.
• Anomaly Detection: Anomaly Detection helps in creating a baseline of normal behavior and flagging any data points that deviate significantly from it.
• Identity clustering: Making a group of user identities based on common attributes and behaviors helps in identify patterns of fraudulent behavior.

To keep this blog from becoming too long we will stop this pipeline at data normalization and will write a sequel to this blog to demonstrate an ML pipeline that will drive the outcomes mentioned above.

Architecture

The pipeline has the following architecture and will be orchestrated using Control-M.

Control-M integration plug-ins used in this architecture:

• Control-M Managed File Transfer to execute file movement from Amazon S3 to Azure Blob.
• Control-M for Databases to load data and run queries against Snowflake.
• Control-M for Kubernetes to execute python code.
• Control-M for AWS Glue for ETL.

See a sample of the connection profiles in the “Connection Profiles” section below.

Before setting up the Kubernetes job in the workflow below please see the details in the Kubernetes section.

Similarly before setting up the Snowflake jobs please refer to the Snowflake section in this blog.

Workflow Summary

Blob-to-s3-transfer Job to Query Table Jobs

1. Blob-to-s3-transfer Job:

• Action: Transfers the data file from Azure Blob Storage to an Amazon S3 bucket.
• Purpose: Prepares the data for processing by moving it to a location accessible by subsequent processing jobs.

2. Conditional Path:

• Branch 1: Infer Schema_Unprocessed Job
  - Action: Executes a SQL script to infer the schema of the unprocessed data.
  - Purpose: Prepares the schema for creating a corresponding table to store the unprocessed data.
  a. Create Table_Unprocessed Job:
  * Action: Execute a SQL script to create a table in Snowflake based on the inferred schema.
  * Purpose: Creates a structured table to store the unprocessed data for further operations.
  b. Copy Into Table_Unprocessed Job:
  * Action: Loads the unprocessed data into the newly created table in Snowflake.
  * Purpose: Populates the table with the data that was transferred from Azure Blob Storage.
  c. Query Table_Unprocessed Job:
  * Action: Executes a SQL script to query the unprocessed data and output the results in CSV format.
  * Purpose: Extracts and formats the data for analysis, reporting, or further processing.
• Branch 2: Mol-kubernetes-job
  - Action: Executes a Kubernetes job to process the data using a Python script within a container.
  - Purpose: Processes the raw data into a more refined format, ready for further transformation or loading.
  a. Mol-glue-job
  * Action: Runs an AWS Glue job to perform ETL (Extract, Transform, Load) operations on the processed data.
  * Purpose: Transforms the data into a structured format that can be stored and queried in Snowflake.
  b. Infer Schema Job:
  * Action: Executes a SQL script to infer the schema of the processed data.
  * Purpose: Prepares the schema for creating a corresponding table to store the processed data.
  c. Create Table Job:
  * Action: Executes a SQL script to create a table in Snowflake based on the inferred schema.
  * Purpose: Creates a structured table to store the processed data for further operations.
  d. Copy Into Table Job:
  * Action: Loads the processed data into the newly created table in Snowflake.
  * Purpose: Populates the table with the transformed data ready for analysis or reporting.
  e. Query Table Job:
  * Action: Executes a SQL script to query the processed data and output the results in CSV format.
  * Purpose: Extracts and formats the processed data for analysis, reporting, or further processing.

Orchestrating the Workflow with Control-M

This workflow summary outlines the two possible paths the process could take, depending on whether it follows the “Infer Schema_Unprocessed” path or the “Mol-kubernets-job” path, and how each subsequent job is connected and contributes to the overall data processing and querying pipeline.

Using Control-M as the orchestration platform we get the following benefits:

• Centralized Workflow Management: Control-M provides a unified platform to orchestrate complex workflows across different systems, enabling financial institutions to manage all data processes from a single interface.
• End-to-End Automation: Control-M automates the execution of fraud detection workflows, reducing the need for manual intervention and ensuring that processes run efficiently and consistently.
• Seamless Integration: Control-M integrates with a wide range of tools and data sources, making it easier to coordinate the various components involved in fraud detection, such as File transfer processes, ETL processes, data analysis, alerting and monitoring systems.
• Real-Time Monitoring and Alerts: With Control-M, users can monitor the entire workflow in real-time, receiving alerts for any issues or anomalies, which is critical for maintaining the effectiveness of fraud detection systems.
• Compliance and Auditability: Control-M helps maintain compliance by providing detailed logs and audit trails for all data processes, ensuring that financial institutions can meet regulatory requirements.

Here is what the defined workflow looks like in Control-M.

In the next section we will go through defining each of these jobs. The jobs can defined using a drag and drop no-code approach in the Planning domain of Control-M or they can be defined as-code in JSON. For the purposes of this blog, we will use the as-code approach.

— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — —

Blob-to-s3-transfer Job
Type: Job
Action: Transfers a file from an Azure Blob storage container to an Amazon S3 bucket using the Control-M Managed File Transfer (MFT) capabilities.
Description: This job handles the secure and automated transfer of the file “TestDataFinLogs.csv” from an Azure Blob Storage container (k8s-container) to a designated folder in an S3 bucket (mol-preprocessed-data). The file is renamed during the transfer process to “mol-raw-data.csv” at the destination.
Dependencies: Triggers two subsequent events after the transfer: Blob-to-s3-transfer-TO-mol-kubernets-job and Blob-to-s3-transfer-TO-Infer_Schema_Unprocessed, enabling downstream jobs to start once the file transfer is completed.

Key Configuration Details:

• Source Connection: AZSMB_BLOB profile, accessing the Azure Blob Storage container k8s-container.
• Destination Connection: MFTS3 profile, targeting the S3 bucket mol-preprocessed-data.
• File Name Handling: The source file name “TestDataFinLogs.csv” is replaced with “mol-raw-data.csv” in the destination S3 bucket.
• File Watcher: The job includes a file watcher that monitors the source file for changes, ensuring that the transfer occurs when the file is ready.

See an example in the figure below:

"Blob-to-s3-transfer": {
  "Type": "Job:FileTransfer",
  "ConnectionProfileSrc": "AZSMB_BLOB",
  "ConnectionProfileDest": "MFTS3",
  "S3BucketNameDest": "mol-preprocessed-data",
  "AzureContainerNameSrc": "k8s-container",
  "Host": “<insert Host IP Address>“,
  "CreatedBy": “<creator’s email automatically generated>“,
  "RunAs": "AZSMB_BLOB+MFTS3",
  "Variables": [
    {"FTP-LOSTYPE": "Unix"},
    {"FTP-CONNTYPE1": "Azure"},
    {"FTP-ROSTYPE": "Unix"},
    {"FTP-CONNTYPE2": "S3"},
    {"FTP-CM_VER": "9.0.00"},
    {"FTP-OVERRIDE_WATCH_INTERVAL1": "0"}
  ],
  "FileTransfers": [
    {
      "TransferType": "Binary",
      "TransferOption": "SrcToDestFileWatcher",
      "Src": "/TestDataFinLogs.csv",
      "Dest": "/input/",
      "ABSTIME": "0",
      "TIMELIMIT": "0",
      "UNIQUE": "0",
      "SRCOPT": "0",
      "IF_EXIST": "0",
      "DSTOPT": "0",
      "RECURSIVE": "0",
      "EXCLUDE_WILDCARD": "0",
      "TRIM": "1",
      "NULLFLDS": "0",
      "VERNUM": "0",
      "CASEIFS": "0",
      "FileWatcherOptions": {
        "AssignFileNameToVariable": "TestDataFinLogs",
        "VariableType": "Global",
        "UnitsOfTimeLimit": "Minutes"
      },
      "IncrementalTransfer": {
        "IncrementalTransferEnabled": false,
        "MaxModificationAgeForFirstRunEnabled": false,
        "MaxModificationAgeForFirstRunInHours": "1"
      },
      "DestinationFilename": {
        "FindSubString": "TestDataFinLogs.csv",
        "ReplaceSubString": "mol-raw-data.csv",
        "ModifyCase": "No"
      }
    }
  ],
  "When": {
    "WeekDays": ["NONE"],
    "MonthDays": ["ALL"],
    "DaysRelation": "OR"
  },
  "eventsToAdd": {
    "Type": "AddEvents",
    "Events": [
      {"Event": "Blob-to-s3-transfer-TO-mol-kubernets-job"},
      {"Event": "Blob-to-s3-transfer-TO-Infer_Schema_Unprocessed"}
    ]
  }
}

BRANCH 1 WORKFLOW SECTION

Infer Schema_Unprocessed Job
Type: Job:Database

Action: Executes a SQL script on Snowflake to infer the schema of unprocessed data, preparing it for further processing or table creation.
Description: This job runs an SQL script located at /home/ctmagent/snowflake_sql/infer_schema2.sql using a Snowflake JDBC connection. The script infers the schema for a dataset that has not yet been processed, setting the stage for the subsequent creation of a corresponding table in Snowflake.
Dependencies: The job waits for the event Blob-to-s3-transfer-TO-Infer_Schema_Unprocessed, ensuring that the data transfer from Azure Blob Storage to S3 is complete before attempting to infer the schema. Upon successful execution, it triggers the event Infer_Schema_Unprocessed-TO-Create_Table_Unprocessed, indicating that the schema has been inferred and is ready for table creation.

Key Configuration Details:

• SQL Script:
  USE DATABASE AUTO_DEMO_DB;
  USE SCHEMA PUBLIC;
  USE WAREHOUSE DEMO_WH;
  USE ROLE SYSADMIN;
  
  SELECT *
  FROM TABLE(
  INFER_SCHEMA(
  LOCATION=>’@prefraudstage/mol-raw-data.csv’
   , FILE_FORMAT=>’fraud_format’
   , IGNORE_CASE=>TRUE
  )
  );

See an example in the figure below:

"Infer Schema_Unprocessed" : {
      "Type" : "Job:Database:SQLScript",
      "ConnectionProfile" : "SNOWFLAKE_JDBC",
      "SQLScript" : "/home/ctmagent/snowflake_sql/infer_schema2.sql",
      "Host" : “<insert Host IP Address>“,
      "CreatedBy" : “<creator’s email automatically generated>“,
      "RunAs" : "SNOWFLAKE_JDBC",
      "When" : {
        "WeekDays" : [ "NONE" ],
        "MonthDays" : [ "ALL" ],
        "DaysRelation" : "OR"
      },
      "eventsToWaitFor" : {
        "Type" : "WaitForEvents",
        "Events" : [ {
          "Event" : "Blob-to-s3-transfer-TO-Infer_Schema_Unprocessed"
        } ]
      },
      "eventsToAdd" : {
        "Type" : "AddEvents",
        "Events" : [ {
          "Event" : "Infer_Schema_Unprocessed-TO-Create_Table_Unprocessed"
        } ]
      },
      "eventsToDelete" : {
        "Type" : "DeleteEvents",
        "Events" : [ {
          "Event" : "Blob-to-s3-transfer-TO-Infer_Schema_Unprocessed"
        } ]
      }
    }

Create Table_Unprocessed Job
Type: Job:Database

Action: Executes a SQL script on Snowflake to create a table for unprocessed data, based on the schema inferred in a previous step.
Description: This job runs a SQL script located at /home/ctmagent/snowflake_sql/create_table2.sql using a Snowflake JDBC connection. The script creates a new table in Snowflake, designed to hold unprocessed data, with its structure based on the schema that was inferred in an earlier job.
Dependencies: The job waits for the event Infer_Schema_Unprocessed-TO-Create_Table_Unprocessed, ensuring that the schema inference is complete before attempting to create the table. Upon successful execution, it triggers the event Create_Table_Unprocessed-TO-Copy_Into_Table_Unprocessed, signaling that the table is ready for data to be loaded.
Key Configuration Details:

• SQL Script
  CREATE OR REPLACE TABLE presamtable
  USING TEMPLATE (
  SELECT ARRAY_AGG(OBJECT_CONSTRUCT(*))
  FROM TABLE(
  INFER_SCHEMA(
  LOCATION=>’@prefraudstage/mol-raw-data.csv’,
  FILE_FORMAT=>’fraud_format’
  )
  ));

See an example in the figure below:

"Create Table_Unprocessed" : {
      "Type" : "Job:Database:SQLScript",
      "ConnectionProfile" : "SNOWFLAKE_JDBC",
      "SQLScript" : "/home/ctmagent/snowflake_sql/create_table2.sql",
      "Host" : “<insert Host IP Address>“,
      "CreatedBy" : “<creator’s email automatically generated>“,
      "RunAs" : "SNOWFLAKE_JDBC",
      "When" : {
        "WeekDays" : [ "NONE" ],
        "MonthDays" : [ "ALL" ],
        "DaysRelation" : "OR"
      },
      "eventsToWaitFor" : {
        "Type" : "WaitForEvents",
        "Events" : [ {
          "Event" : "Infer_Schema_Unprocessed-TO-Create_Table_Unprocessed"
        } ]
      },
      "eventsToAdd" : {
        "Type" : "AddEvents",
        "Events" : [ {
          "Event" : "Create_Table_Unprocessed-TO-Copy_Into_Table_Unprocessed"
        } ]
      },
      "eventsToDelete" : {
        "Type" : "DeleteEvents",
        "Events" : [ {
          "Event" : "Infer_Schema_Unprocessed-TO-Create_Table_Unprocessed"
        } ]
      }
    }

Copy Into Table_Unprocessed Job
Type: Job:Database

Action: Executes a SQL script on Snowflake to copy data into a table that was created for unprocessed data.
Description: This job runs a SQL script located at /home/ctmagent/snowflake_sql/copy_data2.sql using a Snowflake JDBC connection. The script copies data into the unprocessed table that was created in a previous step, populating the table with the data that is ready for further processing or analysis.
Dependencies: The job waits for the event Create_Table_Unprocessed-TO-Copy_Into_Table_Unprocessed, ensuring that the table creation is complete before attempting to load data into it. Upon successful execution, it triggers the event Copy_Into_Table_Unprocessed-TO-Query_Table_Unprocessed, signaling that the data has been loaded and the table is ready for querying.

Key Configuration Details:
• SQL Script:
COPY INTO presamtable FROM @prefraudstage/mol-raw-data.csv
FILE_FORMAT = (
FORMAT_NAME= ‘fraud_format’
)
MATCH_BY_COLUMN_NAME=CASE_INSENSITIVE;

See an example in the figure below:

"Copy Into Table_Unprocessed" : {
      "Type" : "Job:Database:SQLScript",
      "ConnectionProfile" : "SNOWFLAKE_JDBC",
      "SQLScript" : "/home/ctmagent/snowflake_sql/copy_data2.sql",
      "Host" : “<insert Host IP Address>“,
      "CreatedBy" : “<creator’s email automatically generated>“,
      "RunAs" : "SNOWFLAKE_JDBC",
      "When" : {
        "WeekDays" : [ "NONE" ],
        "MonthDays" : [ "ALL" ],
        "DaysRelation" : "OR"
      },
      "eventsToWaitFor" : {
        "Type" : "WaitForEvents",
        "Events" : [ {
          "Event" : "Create_Table_Unprocessed-TO-Copy_Into_Table_Unprocessed"
        } ]
      },
      "eventsToAdd" : {
        "Type" : "AddEvents",
        "Events" : [ {
          "Event" : "Copy_Into_Table_Unprocessed-TO-Query_Table_Unprocessed"
        } ]
      },
      "eventsToDelete" : {
        "Type" : "DeleteEvents",
        "Events" : [ {
          "Event" : "Create_Table_Unprocessed-TO-Copy_Into_Table_Unprocessed"
        } ]
      }
    }

Query Table_Unprocessed Job
Type: Job:Database

Action: Executes a SQL script on Snowflake to query data from an unprocessed data table and outputs the results in CSV format.
Description: This job runs a SQL script located at /home/ctmagent/snowflake_sql/query_data2.sql using a Snowflake JDBC connection. The script queries data from the unprocessed table and outputs the query results in CSV format, enabling further analysis or processing.
Dependencies: The job waits for the event Copy_Into_Table_Unprocessed-TO-Query_Table_Unprocessed, ensuring that the data has been successfully loaded into the table before querying it. Upon successful execution, the job outputs the query results in the specified format.
Key Configuration Details:

• SQL Script:
  select * FROM presamtable;

See an example in the figure below:

"Query Table_Unprocessed" : {
      "Type" : "Job:Database:SQLScript",
      "ConnectionProfile" : "SNOWFLAKE_JDBC",
      "SQLOutputFormat" : "CSV",
      "SQLScript" : "/home/ctmagent/snowflake_sql/query_data2.sql",
      "OutputSQLOutput" : "Y",
      "Host" : “<insert Host IP Address>“,
      "CreatedBy" : “<creator’s email automatically generated>“,
      "RunAs" : "SNOWFLAKE_JDBC",
      "When" : {
        "WeekDays" : [ "NONE" ],
        "MonthDays" : [ "ALL" ],
        "DaysRelation" : "OR"
      },
      "eventsToWaitFor" : {
        "Type" : "WaitForEvents",
        "Events" : [ {
          "Event" : "Copy_Into_Table_Unprocessed-TO-Query_Table_Unprocessed"
        } ]
      },
      "eventsToDelete" : {
        "Type" : "DeleteEvents",
        "Events" : [ {
          "Event" : "Copy_Into_Table_Unprocessed-TO-Query_Table_Unprocessed"
        } ]
      }
    }

PROCESSED DATA WORKFLOW SECTION

Mol-Kubernetes-job

Type: Job

Action: Executes a Kubernetes job that runs a data processing task within a Kubernetes cluster.
Description: This job uses Kubernetes to run a specific data processing task defined in a Kubernetes job specification YAML. The job pulls an image from an AWS ECR repository and executes a Python script to process data from an S3 bucket. After the job completes, it automatically retrieves the pod logs and cleans up the Kubernetes job by deleting it.
Dependencies: The job waits for the event Blob-to-s3-transfer-TO-mol-kubernets-job, ensuring that the necessary data has been transferred from Azure Blob Storage to the S3 bucket before the Kubernetes job starts processing.
Key Configuration Details:

• • Job Specification YAML: The Kubernetes job is defined in a YAML specification, which includes:
  - Container Name: data-processing-container
  - Image: Pulled from 623469066856.dkr.ecr.us-west-2.amazonaws.com/new-mol-kubernetes-plugin-repo:latest
  - Command: Executes the Python script main.py with specified arguments to process data.
  - Restart Policy: Set to Never to prevent the job from restarting automatically.
• Connection Profile: The job uses the MOL-K8S-CONNECTION-PROFILE to connect to the Kubernetes cluster and execute the job.
• Pod Logs: Configured to automatically retrieve logs after the job runs (Get Pod Logs: Get Logs).
• Job Cleanup: The Kubernetes job is deleted after completion to ensure resources are freed (Job Cleanup: Delete Job).

Event Handling:

• Events to Wait For:
  - The job waits for the event Blob-to-s3-transfer-TO-mol-kubernets-job, ensuring the data is available in S3 before the Kubernetes job starts.
• Events to Trigger:
  - After successfully processing the data, the job triggers the event mol-kubernets-job-TO-mol-glue-job, signaling that the data processing is complete, and the next step (likely data transformation or loading) can begin.

Mol-glue-job

For details on how to setup Control-M with AWS glue see the Snowflake Setup section below

Type: Job

Action: Executes an AWS Glue job to process financial data as part of a broader ETL workflow.
Description: This job runs an AWS Glue job named mol-financial-data-processing, which is responsible for processing financial data. AWS Glue is used to perform ETL (Extract, Transform, Load) operations, transforming raw data into a structured format suitable for further analysis or storage. The job is triggered automatically after the completion of a preceding Kubernetes job.
Dependencies: The job waits for the event mol-kubernets-job-TO-mol-glue-job, ensuring that the data processing task handled by Kubernetes is complete before starting the AWS Glue job.
Key Configuration Details:

• Glue Job Name: The AWS Glue job executed by this task is mol-financial-data-processing.
• Connection Profile: The job uses the MOLGLUECONNECTIONPROFILE to connect to AWS Glue and run the job.

Event Handling:

• Events to Wait For:
  - The job waits for the event mol-kubernets-job-TO-mol-glue-job, ensuring the Kubernetes data processing is finished before starting the Glue job.
• Events to Trigger:
  - After successfully completing the Glue job, it triggers the event mol-glue-job-TO-Infer_Schema, signaling that the data processing is complete, and the schema inference step can begin.

Infer Schema Job (Processed Data)

Type: Job:Database

Action: Executes a SQL script on Snowflake to infer the schema of a dataset, preparing the data for subsequent processing.
Description: This job runs a SQL script located at /home/ctmagent/snowflake_sql/infer_schema.sql using a Snowflake JDBC connection. The script infers the schema of a dataset, typically in preparation for creating a corresponding table in Snowflake.
Dependencies: The job waits for the event mol-glue-job-TO-Infer_Schema, ensuring that a prior data processing step is completed before attempting to infer the schema. Upon successful completion, it triggers the event Infer_Schema-TO-Create_Table, signaling that the inferred schema is ready for use in table creation.
Key Configuration Details:

• SQL Script:
  USE DATABASE AUTO_DEMO_DB;
  USE SCHEMA PUBLIC;
  USE WAREHOUSE DEMO_WH;
  USE ROLE SYSADMIN;
  SELECT *
  FROM TABLE(
  INFER_SCHEMA(
  LOCATION=>’@fraudstage/glue-output.csv’
   , FILE_FORMAT=>’fraud_format’
   , IGNORE_CASE=>TRUE
  )
  );

See an example in the figure below:

"Infer Schema" : {
      "Type" : "Job:Database:SQLScript",
      "ConnectionProfile" : "SNOWFLAKE_JDBC",
      "SQLScript" : "/home/ctmagent/snowflake_sql/infer_schema.sql",
      "Host" : “<insert Host IP Address>“,
      "CreatedBy" : “<creator’s email automatically generated>“,
      "RunAs" : "SNOWFLAKE_JDBC",
      "When" : {
        "WeekDays" : [ "NONE" ],
        "MonthDays" : [ "ALL" ],
        "DaysRelation" : "OR"
      },
      "eventsToWaitFor" : {
        "Type" : "WaitForEvents",
        "Events" : [ {
          "Event" : "mol-glue-job-TO-Infer_Schema"
        } ]
      },
      "eventsToAdd" : {
        "Type" : "AddEvents",
        "Events" : [ {
          "Event" : "Infer_Schema-TO-Create_Table"
        } ]
      },
      "eventsToDelete" : {
        "Type" : "DeleteEvents",
        "Events" : [ {
          "Event" : "mol-glue-job-TO-Infer_Schema"
        } ]
      }
    }

Create Table Job
Type: Job:Database

Action: Executes a SQL script on Snowflake to create a table based on a previously inferred schema.
Description: This job runs a SQL script located at /home/ctmagent/snowflake_sql/create_table.sql using a Snowflake JDBC connection. The script creates a new table in Snowflake, structured according to the schema that was inferred in a prior step. This table is prepared to store data that will be loaded in subsequent steps of the workflow. The job uses the SNOWFLAKE_JDBC connection profile to connect to Snowflake and execute the SQL script.
Dependencies: The job waits for the event Infer_Schema-TO-Create_Table, ensuring that the schema inference process is complete before attempting to create the table. Upon successful execution, it triggers the event Create_Table-TO-Copy_Into_Table, indicating that the table is ready for data to be loaded.
Key Configuration Details:

• SQL Script:
  USE DATABASE AUTO_DEMO_DB;
  USE SCHEMA PUBLIC;
  USE WAREHOUSE DEMO_WH;
  USE ROLE SYSADMIN;
  CREATE OR REPLACE TABLE final_table
  USING TEMPLATE (
  SELECT ARRAY_AGG(OBJECT_CONSTRUCT(*))
  FROM TABLE(
  INFER_SCHEMA(
  LOCATION=>’@fraudstage/glue-output.csv’,
  FILE_FORMAT=>’fraud_format’
  )
  ));

See an example in the figure below:

"Create Table" : {
      "Type" : "Job:Database:SQLScript",
      "ConnectionProfile" : "SNOWFLAKE_JDBC",
      "SQLScript" : "/home/ctmagent/snowflake_sql/create_table.sql",
      "Host" : “<insert Host IP Address>“,
      "CreatedBy" : “<creator’s email automatically generated>“,
      "RunAs" : "SNOWFLAKE_JDBC",
      "When" : {
        "WeekDays" : [ "NONE" ],
        "MonthDays" : [ "ALL" ],
        "DaysRelation" : "OR"
      },
      "eventsToWaitFor" : {
        "Type" : "WaitForEvents",
        "Events" : [ {
          "Event" : "Infer_Schema-TO-Create_Table"
        } ]
      },
      "eventsToAdd" : {
        "Type" : "AddEvents",
        "Events" : [ {
          "Event" : "Create_Table-TO-Copy_Into_Table"
        } ]
      },
      "eventsToDelete" : {
        "Type" : "DeleteEvents",
        "Events" : [ {
          "Event" : "Infer_Schema-TO-Create_Table"
        } ]
      }
    }

Copy Into Table Job
Type: Job:Database

Action: Executes a SQL script on Snowflake to load data into a newly created table.
Description: This job runs a SQL script located at /home/ctmagent/snowflake_sql/copy_data.sql using a Snowflake JDBC connection. The script copies or loads data into a table that was previously created in the workflow, making the data available for subsequent querying or processing steps.
Dependencies: The job waits for the event Create_Table-TO-Copy_Into_Table, ensuring that the table has been successfully created before attempting to load data into it. Upon successful execution, it triggers the event Copy_Into_Table-TO-Query_Table, indicating that the data is now loaded and ready for querying.
Key Configuration Details:

• SQL Script:
  USE DATABASE AUTO_DEMO_DB;
  USE SCHEMA PUBLIC;
  USE WAREHOUSE DEMO_WH;
  USE ROLE SYSADMIN;
  COPY INTO final_table FROM @fraudstage/glue-output.csv
  FILE_FORMAT = (
  FORMAT_NAME= ‘fraud_format’
  )
  MATCH_BY_COLUMN_NAME=CASE_INSENSITIVE;

See an example in the figure below:

"Copy Into Table" : {
      "Type" : "Job:Database:SQLScript",
      "ConnectionProfile" : "SNOWFLAKE_JDBC",
      "SQLScript" : "/home/ctmagent/snowflake_sql/copy_data.sql",
      "Host" : “<insert Host IP Address>“,
      "CreatedBy" : “<creator’s email automatically generated>“,
      "RunAs" : "SNOWFLAKE_JDBC",
      "When" : {
        "WeekDays" : [ "NONE" ],
        "MonthDays" : [ "ALL" ],
        "DaysRelation" : "OR"
      },
      "eventsToWaitFor" : {
        "Type" : "WaitForEvents",
        "Events" : [ {
          "Event" : "Create_Table-TO-Copy_Into_Table"
        } ]
      },
      "eventsToAdd" : {
        "Type" : "AddEvents",
        "Events" : [ {
          "Event" : "Copy_Into_Table-TO-Query_Table"
        } ]
      },
      "eventsToDelete" : {
        "Type" : "DeleteEvents",
        "Events" : [ {
          "Event" : "Create_Table-TO-Copy_Into_Table"
        } ]
      }
    }

Query Table Job
Type: Job:Database

Action: Executes a SQL script on Snowflake to query data from a table and output the results in CSV format.
Description: This job runs a SQL script located at /home/ctmagent/snowflake_sql/query_data.sql using a Snowflake JDBC connection. The script queries data from a previously populated table, and the results are output in CSV format. This allows for easy analysis, reporting, or further processing of the queried data.
Dependencies: The job waits for the event Copy_Into_Table-TO-Query_Table, ensuring that data has been successfully loaded into the table before attempting to run the query. Upon completion, it handles the necessary event management to maintain workflow integrity.
Key Configuration Details:

• SQL Script:
  USE DATABASE AUTO_DEMO_DB;
  USE SCHEMA PUBLIC;
  USE WAREHOUSE DEMO_WH;
  USE ROLE SYSADMIN;
  select * FROM final_table;

See an example in the figure below:

"Query Table" : {
      "Type" : "Job:Database:SQLScript",
      "ConnectionProfile" : "SNOWFLAKE_JDBC",
      "SQLOutputFormat" : "CSV",
      "SQLScript" : "/home/ctmagent/snowflake_sql/query_data.sql",
      "OutputSQLOutput" : "Y",
      "Host" : “<insert Host IP Address>“,
      "CreatedBy" : “<creator’s email automatically generated>“,
      "RunAs" : "SNOWFLAKE_JDBC",
      "When" : {
        "WeekDays" : [ "NONE" ],
        "MonthDays" : [ "ALL" ],
        "DaysRelation" : "OR"
      },
      "eventsToWaitFor" : {
        "Type" : "WaitForEvents",
        "Events" : [ {
          "Event" : "Copy_Into_Table-TO-Query_Table"
        } ]
      },
      "eventsToDelete" : {
        "Type" : "DeleteEvents",
        "Events" : [ {
          "Event" : "Copy_Into_Table-TO-Query_Table"
        } ]
      }
    }

This workflow summary outlines the two possible paths the process could take, depending on whether it follows the “Infer Schema_Unprocessed” path or the “Mol-kubernets-job” path, and how each subsequent job is connected and contributes to the overall data processing and querying pipeline.

Workflow Execution

Once the workflow is setup in Control-M it can be executed by an event or a schedule. Included below are some screenshots of the workflow from the Monitoring Domain in Control-M.

Kubernetes Solution Setup

In our fraud analysis workflow, deploying a Kubernetes pod to handle data preprocessing is a crucial step. This pod will run a Python script that the raw data in S3, preparing it for further ETL operations and analysis. Below, we’ll discuss the specifics of the pod deployment, the Docker image used, and the Python script’s functionality in the data preprocessing stage.

Deploying the Pod in Kubernetes

As part of our Kubernetes setup, we deploy a pod that will execute the Python script responsible for preprocessing raw data. This process is automated within the Kubernetes environment, ensuring scalability and efficiency.

Prerequisites
Before diving into the Kubernetes and AWS Glue setup, ensure that you have the following prerequisites in place:

• Control-M Environment Deployed within an EKS cluster or any other Kubernetes environment of your choice.
• Control-M Agent with Kubernetes : Control-M
• AWS S3 Bucket: For storing raw and processed data.
• AWS Glue Service: Configured for ETL processes.
• Docker and Kubernetes: Set up for containerization and orchestration of the preprocessing tasks.
• Amazon ECR (Elastic Container Registry): For storing Docker images used in the Kubernetes pods.

Step 1 : Create the Amazon ECR Repository
Since we are using Amazon ECR to store our Docker images, the first step is to create an ECR repository.

Create ECR Repository:
 — Use the AWS CLI to create a new ECR repository where you’ll push your Docker images.
 — Take note of the repository URI returned by this command, as you’ll need it for the Docker image push process.

aws ecr create-repository --repository-name <Enter Repository Name> --region us-west-2

Step 2: Prepare the Application Files
Your Docker image will include the following essential files.

Dockerfile:

# Use the official Python image from Docker Hub
FROM python:3.9-slim

# Set the working directory in the container
WORKDIR /app

# Copy the requirements file into the container
COPY requirements.txt .

# Install the necessary dependencies
RUN pip install --no-cache-dir -r requirements.txt

# Copy the rest of your application code into the container
COPY . .
# Specify the command to run your script with input and output paths
CMD ["python3", "/app/main.py", "-b", "mol-preprocessed-data", "-i", "input/mol-raw-data.csv", "-o", "output/output.csv"]

The Dockerfile is responsible for setting up the environment in which the Python script will run.

Base Image: The image is based on Python 3.9 slim, which is a lightweight version of Python suitable for our needs.
Working Directory: The working directory inside the container is set to /app.

Dependencies: The dependencies listed in requirements.txt are installed.

Copy Files: The application code is copied into the container.

Execution Command: The command to run the Python script with the necessary input and output paths is specified.

requirements.txt:
This file lists the Python packages required by the script:
These dependencies are crucial for data processing, interaction with S3, and other data processing operations.

pandas
boto3
numpy
scikit-learn

python Script (main.py):

import pandas as pd
from sklearn.preprocessing import MinMaxScaler, LabelEncoder
import argparse
import boto3

def preprocess_data(input_file, output_file):
    # Load the data
    data = pd.read_csv(input_file)

    # Handle missing values
    data = data.dropna()

    # Remove duplicates
    data = data.drop_duplicates()

    # Standardize column names
    data.columns = [col.lower() for col in data.columns]

    # Convert columns to appropriate data types
    data['amount'] = data['amount'].astype(float)
    data['oldbalanceorg'] = data['oldbalanceorg'].astype(float)
    data['newbalanceorig'] = data['newbalanceorig'].astype(float)

    # Create new features
    data['balance_difference'] = data['oldbalanceorg'] - data['newbalanceorig']

    # Filter irrelevant data (example: remove rows where amount is zero)
    data = data[data['amount'] > 0]

    # Normalize/scale numerical features (example: Min-Max Scaling)
    scaler = MinMaxScaler()
    data[['amount', 'oldbalanceorg', 'newbalanceorig', 'balance_difference']] = scaler.fit_transform(
        data[['amount', 'oldbalanceorg', 'newbalanceorig', 'balance_difference']]
    )

    # Encode categorical variables (example: Label Encoding)
    label_encoder = LabelEncoder()
    data['type'] = label_encoder.fit_transform(data['type'])
    data['nameorig'] = label_encoder.fit_transform(data['nameorig'])
    data['namedest'] = label_encoder.fit_transform(data['namedest'])

    # Save the preprocessed data
    data.to_csv(output_file, index=False)

def download_from_s3(bucket_name, s3_file, local_file):
    s3 = boto3.client('s3')
    s3.download_file(bucket_name, s3_file, local_file)

def upload_to_s3(local_file, bucket_name, s3_file):
    s3 = boto3.client('s3')
    try:
        s3.upload_file(local_file, bucket_name, s3_file)
        print(f"Upload Successful: {local_file} to s3://{bucket_name}/{s3_file}")
    except Exception as e:
        print(f"Upload Failed: {e}")

if __name__ == "__main__":
    parser = argparse.ArgumentParser(description="Data preprocessing script.")
    parser.add_argument("-b", "--bucket", required=True, help="S3 bucket name")
    parser.add_argument("-i", "--input", required=True, help="Input S3 file path")
    parser.add_argument("-o", "--output", required=True, help="Output S3 file path")

    args = parser.parse_args()

    local_input_file = '/tmp/input.csv'
    local_output_file = '/tmp/output.csv'

    # Download input file from S3
    download_from_s3(args.bucket, args.input, local_input_file)

    # Preprocess data
    preprocess_data(local_input_file, local_output_file)

    # Upload output file to S3
    upload_to_s3(local_output_file, args.bucket, args.output)

This script performs the data preprocessing tasks necessary for the fraud detection workflow. This is central to the data preprocessing stage in our fraud detection workflow. Here is a detailed breakdown of the script’s functionality.

1. Input Data Handling:
   * The script starts by downloading raw data from an S3 bucket using the download_from_s3 function. The raw data is then loaded into a pandas DataFrame for processing.
2. Data Cleaning:
   * Handling Missing Values: The script removes any rows with missing values to ensure data quality.
   * Removing Duplicates: Duplicate records are also removed to prevent any data redundancy.
   * Standardizing Column Names: Column names are converted to lowercase to maintain consistency.
3. Feature Engineering:
   * Creating New Features: A new feature, balance_difference, is calculated to capture the difference between oldbalanceorg and newbalanceorig. This feature is crucial for detecting discrepancies that might indicate fraudulent activity.
4. Data Filtering:
   * The script filters out any records where the amount is zero, as these are not relevant to the fraud detection process.
5. Data Transformation:
   * Normalization: Numerical features are normalized using MinMaxScaler to scale them between 0 and 1, ensuring that different features contribute equally to the analysis.
   * Label Encoding: Categorical variables like type, nameorig, and namedest are encoded using LabelEncoder to convert them into a numerical format suitable for machine learning models.
6. Output Data Handling:
   * After preprocessing, the script saves the cleaned and processed data to a local file. This file is then uploaded back to the S3 bucket using the upload_to_s3 function, making it available for subsequent processing stages in AWS Glue and Snowflake.

kubernetes YAML file:
The YAML file defines the Kubernetes job that the container will execute when the pod is run. This job is essential because it specifies how the Python script should be executed within the Kubernetes environment. Control-M will trigger this pod, ensuring that the job runs to completion.

apiVersion: batch/v1
kind: Job
metadata:
  name: data-processing-pod
spec:
  template:
    spec:
      containers:
      - name: data-processing-container
        image: <account-id>.dkr.ecr.us-west-2.amazonaws.com/new-mol-kubernetes-plugin-repo:latest
        command: ["/bin/sh", "-c", "python3 /app/main.py -b mol-preprocessed-data -i input/mol-raw-data.csv -o output/output.csv && sleep 300"]
      restartPolicy: Never

Image: Specifies the Docker image stored in ECR that contains the Python script and its dependencies.
Command: Details the command that runs the Python script with the necessary arguments, ensuring that the data is processed as required.
Restart Policy: Set to Never, indicating that the job will not restart after completion.

Step 3: Building and pushing the Docker Image to ECR
Next, we need to package the Python script into a Docker image, build the image locally, and then push it to the newly created ECR repository.

Authenticate Docker to ECR: Authenticate your Docker client to the ECR registry so you can push images.

Build the Docker Image: Navigate to the directory containing your Dockerfile and build the Docker image:

Tag the Docker Image: Tag your Docker image with the ECR repository URI:

Push the Docker Image to ECR: Push the Docker image to your ECR repository:

With the Docker image pushed to ECR, it’s now ready to be used in the Kubernetes job that handles the data preprocessing.

Snowflake Setup:

Given that the data has already been extracted, transformed, and loaded into Snowflake using Kubernetes and AWS Glue, our focus with Snowflake is now on querying the transformed data to detect fraudulent activities. Here’s how you can leverage Snowflake’s capabilities for this purpose:

1. Set Up Your Snowflake Environment: Ensure that your transformed data is properly loaded into Snowflake tables. Verify the table schemas and data quality to ensure that all necessary data points are available for querying.
2. Run Queries to Identify Fraudulent Transactions: In this demo, we only ran one query to display all the contents of the transformed table.

To begin, create a Snowflake database, schema, role, 2 stages* and warehouse.

2 different stages (Fraudstage and Prefraudstage) were created in this scenario to house both the unprocessed data and the processed, transformed data.

*Snowflake stages are locations where data files are stored (staged) for loading and unloading data. See a screenshot of the 2 Snowflake stages that attach the S3 bucket to the Snowflake profile below.

Best practices and considerations for Snowflake implementation:

• Make use of Snowflake stages when loading data from an external system using Control-M. Control-M transfer jobs like the first one we created in this demo (S3_Object_Transfer) require a Snowflake stage be set up. Stages generally make it much easier to repeatedly load data in a specific format.
• Use the “SYSADMIN” role for most of your Snowflake job processes within Control-M. This has a few benefits:
  - Security and principle of least privilege: The Account Admin role has the highest level of access within a Snowflake account, including the ability to manage users and roles, billing, and account settings. Using Sysadmin for data-related jobs adheres more closely to the principle of least privilege, reducing the risk of accidental or malicious modifications to critical account settings.
  - Operational efficiency: The Sysadmin role is optimized for performing and managing data operations, potentially offering more efficient execution of data-related jobs compared to using an account-wide administrative role, which is not specifically tailored for such tasks.
  - Best practices and compliance: Using the Sysadmin role for database and job management aligns with best practices for managing cloud and database environments. It helps organizations to structure their roles and responsibilities in a way that enhances operational efficiency and security posture.

Connection Profiles

Before creating a job in Control-M that can execute the jobs in this workflow, we will need to create connection profiles. A connection profile contains authorization credentials — such as the username, password, and other plug-in-specific parameters — and enables you to connect to the application server with only the connection profile name. Connection profiles can be created using the web interface and then retrieved in JSON format using Control-M’s Automation API. Included below is a sample of the connection profile for Azure Databricks in JSON format. If you create the connection profile directly in JSON before running the job, it should be deployed using the Control-M Automation API CLI.

Snowflake Connection Profile

{
  "SF_JDBC_CONNECTION_PROFILE": {
    "Type": "ConnectionProfile:Database:JDBC",
    "User": "SMM",
    "Driver": "SNOWFLAKE_JDBC",
    "Port": "8443",
    "DatabaseName": "SNOWFLAKE_SAMPLE_DATA",
    "Host": "xyz.snowflakecomputing.com",
    "Password": "*****",
    "Description": "",
    "Centralized": true
  }
}

To connect to Snowflake we are using the Control-M Databases plug-in. Before setting it up you will need to download the JDBC driver for Snowflake which can be found at this link here. After downloading the JDBC driver you will need to place it on the Agent that will execute the jobs. For details, please see this link.

MFT Connection Profile

{
"AZSMB_BLOB": {
    "Type": "ConnectionProfile:FileTransfer:Azure:SharedKey",
    "VerifyBytes": true,
    "AzureAccountName": "sambloblake",
    "VerifyDestination": true,
    "AzureStorageType": "BLOB_STORAGE",
    "AzureAccountAccessKey": "*****",
    "Description": "",
    "Centralized": true
  }
}

Kubernetes Connection Profile

{
"MOL-K8S-CONNECTION-PROFILE": {
  "Type": "ConnectionProfile:Kubernetes",
  "Service Token File": "/var/run/secrets/kubernetes.io/serviceaccount/token",
  "Namespace": "default",
  "Kubernetes Cluster URL": "https://kubernetes.default.svc",
  "Connection Timeout": "50",
  "Description": "",
  "Centralized": true
}
}

AWS Glue Connection Profile:

{
  "MOLGLUECONNECTIONPROFILE": {
    "Type": "ConnectionProfile:AWS Glue",
    "AWS Access key ID": "<Access Key>",
    "AWS Secret": "*****",
    "Authentication": "SECRET",
    "AWS Region": "us-west-2",
    "Glue url": "glue.us-west-2.amazonaws.com",
    "Connection Timeout": "40",
    "Description": "",
    "Centralized": true
  }
}

Orchestrating a multi-cloud fraud analytics pipeline with Control-M was originally published in BMC Innovation on Medium, where people are continuing the conversation by highlighting and responding to this story.

Though digital transformation has become a necessity for enterprises to survive, thrive and succeed, organizations routinely struggle with fragmented and rigid tools, data silos with automation and fragmented workflows that hinder scalability and efficiency.

Language models have unlocked possibilities of improving how business and technical users interact with existing business processes and leverage AI to orchestrate and communicate with each other through natural language.

This two-part blog series discusses the rapidly evolving landscape of LLMOps.

What is LLMOps (Large Language Model Operations)

LLMOps is the emerging practice of operational management of LLMs & LLM-driven AI applications. It comprises of associated processes, tools, frameworks, libraries, infrastructure, orchestration and workflows as part of the lifecycle of AI-driven applications.

This is an emerging field akin to the DevOps/DataOps and MLOps for building and creating a robust deployment and automation process that leverages LLMs for AI applications. The primary focus is on the automation of end-to-end workflows to operationalize, deploy, monitor, optimize and maintain LLM-powered AI applications to ensure scalability, reliability and governance in production.

Why is LLMOps needed

Despite the false notion of LLMs possessing superpowers, LLMs don’t manage themselves. Organizations leveraging LLM-powered applications without LLMOps are running blind.

As enterprises adopt AI practices with home grown or 3rd party AI models, LLMOps brings in the formal discipline of building, deploying and maintaining AI applications in production. It does this by addressing the automation, operational and governance challenges in deploying AI applications to production. LLMOps ensures enterprise AI applications are secure, repeatable, reliable and trustworthy.

The goal of LLMOps is twofold:

• Automate the building, deployment and maintenance of AI applications to production
• Improve end user experience

This is achieved with automated machine or human feedback that continually evaluates the applications, ensuring compliance and safety with appropriate guardrails to minimize risk of bias, manage data privacy and security issues surrounding AI applications. To maintain and continually enhance efficiency, reliability, and scalability of the inference process you need an AI application at runtime.

LLMOps is a critical enabler to ensure quality and prevent adversarial attacks, malicious prompt injections and maintain high relevancy of results by detecting and recalibrating model and data drift. Leveraging LLMs is expensive and LLMOps helps with cost management by analyzing usage and identifying inefficiencies, by tracking token usage.

When should organizations use LLMOps

Now that the importance of LLMOps is understood the next question that arises is when to apply LLMOps and when not to.

Implementing AI-driven applications with LLMs runs into the following key challenges –which LLMOps can effectively overcome.

Managing data is a central tenet of building successful LLM-powered AI solutions. This involves identifying domain-specific/agnostic high-quality diverse data sources, preprocessing it for model training. Storing the metadata associated with these datasets for efficacy and relevance of LLM training, fine-tuning in intelligent automation frameworks.

LLM-powered AI solutions when deployed to production introduces several challenges which require model monitoring with LLMOps. Continuous model monitoring is essential to detect data and model drift to ensure model reliability and accuracy.

For organizations developing in-house LLMs or doing fine-tuning of existing LLM models, managing the size and complexity of LLM models requires robust infrastructure with demanding SLAs across latency, concurrency, scalability, High Availability (HA), and failover. LLMOps is a no-brainer in this case.

Most organizations will not develop in-house LLMs but rather leverage LLM APIs or implement AI solutions for proprietary data using Retrieval Augmented Generation (RAG) pattern to develop in-house AI applications. In the latter cases, guardrails around security, compliance, privacy of sensitive data and precautions against adversarial attacks, automated validation checks, and human-in-the-loop tests are the tasks that need to be orchestrated and coordinated in a reliable workflow with LLMOps processes, frameworks and tools.

As LLMs get increasingly integrated in the enterprises, that involves complex decision-making tasks, LLMOps ensures capabilities like anomaly detection, detecting deviations from expected behavior, error detection and error handling and appropriate altering mechanisms are in place to course correct the system behavior.

LLMOps is a critical part of optimizing costs incurred with using external AI models with cost effective resource management with serverless architectures, auto-scaling and maintaining SLAs.

Automation in complex systems requires LLMs to work in conjunction with other AI powered systems and models as well as agents. LLMOps orchestrates these multi-modal systems, ensuring they coordinate and work synergistically, and that effective and efficient agents communicate with shared context.

RAG-based AI solutions require integration between LLMs and external data and knowledge sources. The complexity of managing LLM workflows with data retrieval, data ranking and data augmentation is handled by LLMOps in making these cross-system integrations reliable, minimizing latency and coordinating the workflows with requisite SLAs.

LLMOps plays a pivotal role in the operational success of Agentic AI by addressing challenges of task execution and managing how agents interact with each other and external systems in a reliable, secure, and efficient manner.

LLMOps ensures that AI systems are deployed efficiently, effectively, with robust monitoring, security, and scalability. LLMOps provides the necessary framework to continuously improve the performance of LLM applications as they interact with dynamic environments, external knowledge bases, and autonomous task execution systems.

DataOps, MLOps, LLMOps

Data Operations (DataOps) is to data engineering what Machine Learning Operations (MLOps) is to ML Engineering and LLMOps is to AI Engineering. DataOps and MLOps are pre-requisites for LLMOPs. LLMOps takes the foundational principles of traditional MLOps and adapts them to the unique context of managing and deploying large-scale language models and applications.

MLOps is complex with its variety of tasks that are needed to integrate with multiple components of an ML engineering pipeline. LLMOps is like MLOps but with higher levels of complexity as it involves cost effectively provisioning and managing infrastructure — expensive resources like GPUs and TPUs and managing large models that span across machines and require extensive computational resources for training and inference.

LLM-based applications also requires extensive data caching (answers, prompts etc.) as well as integrating with external systems for incorporating RAG patterns. LLMOPs orchestrates and coordinates the different tasks and integrates with AI tools and frameworks in the ecosystem, ensures governance, data privacy, and mitigates hallucinations.

LLMOps helps with managing, curation, augmentation of huge volumes of heterogeneous training data to enhance the versatility across contexts multilingually. This includes multi-modal data sets that involve mainly unstructured data in the form of text, image, audio, video etc.

Determining the efficacy and trustability of LLMs in language-related tasks is an intricate process that’s distinct from traditional MLOps evaluation methods. These metrics try to quantify language output quality for coherence, fluency, consistency, conformity and language generation alignment and contextual appropriateness. This is an integral part of LLMOps, something not imperative with MLOps.

The core principles of MLOps are to bring ML applications and algorithms to production reliably. This still applies to LLMOPs but the complexities around LLMs requires LLMOps to go beyond by incorporating tasks like prompt engineering, prompt management, data preparation for fine-tuning, evaluation focused on the specific task, and continuous monitoring for output quality and integrating with RAG platforms and frameworks.

LLMOps Market and Trends

As Generative AI adoption accelerates and permeates into the enterprise, organizations need to adopt LLMOps to address unique challenges of LLMs. The ability to handle complex tasks like RAG, fine-tuning, data and workflow orchestration, governance, and cost efficiency in large-scale AI deployments is increasingly becoming critical for organizations adopting AI.

AI is forcing organizations to rethink their data, ML and AI infrastructure to optimize performance, reduce costs, improve latency and increase high availability along with addressing security and governance issues associated with AI.

LLMOps is a fast-moving field, and the tools and frameworks are continuously improving and adapting to the new developments in the GenAI and AI space. Existing LLMOps frameworks are keeping up with integration of new APIs, integrating with new upcoming models and integrating new protocols to interface with LLMs.

LLMOps is not about deploying a single model or integrating with AI APIs or chaining together agents. It increasingly requires building autonomous self-healing workflows, especially with the rise of agentic systems enabling AI-driven automation to be more effective and seamless. LLMOps tools are integrating with multiple specialized agentic frameworks optimized for different tasks.

LLMOps is evolving to integrate observability in the stack which includes model monitoring to detect model and data drift, performance optimization to handle latency spikes and auto configuring infrastructure based on usage, availability and SLAs. LLMOps tools and frameworks are adopting plug-and-play and modular development techniques to manage the ever-growing list of integrations.

As LLMOps matures, the concept of LLMOps marketplace for LLMOps components and AI models and operators will probably take shape, lower barriers to adoption, encourage standardization, and foster innovation across industries.

Role of workflow & orchestration in DataOps, MLOps, LLMOps

Orchestration and workflow with LLMOps can be looked at 2 different lens — at a macro level and at a micro (granular level).

At a macro level, LLMOps facilitates the seamless integration of LLMs, AI frameworks, tools, agents and automation tool and workflows across data centers. The overarching goal is to ensure LLMs and other modular AI components be effectively utilized across diverse enterprise use cases and business functions across geographical regions.

This includes:

• Prompt Management — prompt versioning and keeping all the metadata associated with prompts for repeatability
• Input, prompt and out processing
• Data and prompt versioning
• Handling of model-specific parameters and configurations
• Chaining of multiple LLM invocations
• Integrating external tools or APIs

At a micro level, orchestration and workflow means building LLMs with data and ML pipelines with fine-grained control, coordination, orchestration across each of the pieces in the LLM development process. This encapsulates the granular details of how data flows into, through and out of language models within a single task or a small set of closely related tasks.

Consider a large insurance company automating its claims processing and reconciliation workflow leveraging LLMs as a part of the workflow and integration with other business processes. It consists of the following steps at a high level.

1. Data Ingestion Layer: Customer data, claims forms, call transcripts, and documents are ingested from various sources like CRM systems, scanned PDFs, emails, and contact center logs.
2. Data Pipeline: The ingestion pipeline cleans, deduplicates, and enriches the data before it’s stored in a data lake or forwarded for downstream processing.
3. LLM Layer:
   - LLM processes text from claims and call transcripts, extract relevant entities (e.g., policy number, date of loss, incident type).
   - LLMs generates summaries and answers to queries about specific claims using context-aware prompts
   - Prompt templates and responses are versioned and tracked for auditing and compliance.
4. Integration with Enterprise Systems:
   - The extracted insights and responses are orchestrated into SAP for financial reconciliation, Oracle ERP for workflow updates, and Core Banking Systems for payment processing.
   - In case of discrepancies, the LLM suggests possible resolutions by comparing current inputs with historical claim data.
5. Automation and Decisioning:
   - If the claim meets certain thresholds, the system automatically routes it for approval.
   - In complex cases, an LLM agent drafts a recommended response for a human adjuster to review, reducing time-to-resolution.

All actions and tasks need to be woven into a workflow that involves DataOps for data pipelines, MLOps for ML and NLP tasks and LLMOps for LLM specific tasks. Enterprise workflow and BPM tools and frameworks like Control-M, seamlessly coordinate and manage interaction, deployment, and integration of various pieces of the end-to-end chain of tasks and operations. This offers a multitude of benefits from scalability, efficiency, reliability, flexibility and consistency of enterprise-level workflows.

Conclusion

Building AI-driven applications powered by LLMs without a well-defined Ops model is relatively easy but quickly becomes challenging in production opening a Pandora’s box of models, challenges with data, and governance.
LLMOps is key to the building blocks of AI applications, that involves developing a structured framework which securely stores and processes data at scale and can safely extract data, and ensure language models do not misinterpret information, expose confidential data or generate potentially harmful answers.

LLMOps streamlines development, reduces risks, protects data, and optimizes costs, ultimately enhancing the quality of LLM apps. As AI usage grows, embracing LLMOps is key for companies to build great user experiences, maintain a competitive edge, and innovate efficiently.
Providing the infrastructure, tooling, and processes necessary to deploy, monitor, and continuously improve LLMs, LLMOps ensures that these models can be operationalized effectively in real-world automation scenarios.

Introduction to LLMOps was originally published in BMC Innovation on Medium, where people are continuing the conversation by highlighting and responding to this story.

When it comes to attracting top-notch millennial talent, innovative onboarding tools aren’t simply a luxury, they’re a necessity. In fact, a 2015 HR Policy Foundation (HRPF) study of millennials in the workforce found 54 percent of companies have revised their onboarding process to be more appealing to millennials.

HRPF researchers say that slow hiring processes and lackluster training programs can cost companies the very talent they’re working so hard to attract. Workers who have grown up in a digital world expect the hiring process to reflect what’s become a standard way of life for them. Fortunately, it’s a demand worth meeting because a digital onboarding experience also frees up HR professionals to be more productive and time-efficient.

A strong digitized onboarding process sets the tone right from the start that you’re a forward-thinking company with a trained eye on what millennials look for in an employer. There are several new hire touchpoints that are ripe for digitized onboarding. Here are three processes you can implement today.

Develop electronic forms for an improved hiring experience

There’s no getting around it, forms and contracts are a part of virtually every new hire workflow. Payroll and tax forms, NDAs, employee contracts and policy manuals are just the beginning of the mountain of physical paperwork associated with onboarding new talent.

It’s a headache for new workers and an expensive compliance, storage and management issue for HR departments. As a result, many companies are turning to digital options, like BMC HR Case Management. The system automates routine HR processes with a digital workspace that includes over 300 best practice templates and more than 60 eForms that new hires can complete and return in the mobile or desktop environment of their choice. HR Case Management also makes quick work of converting and importing paper forms into the digital environment for a seamless transition for an online onboarding experience.

Digitize new hire portals for long-term success

“Did you know 80 percent of millennials sleep with their phone within reach at night? Give them what they want. Ditch the chalkboard and move your onboarding information online,” Leanrcore says of today’s hiring process.

By digitizing the onboarding process, workers become immediately acclimated to HR processes that will follow them throughout the length of their employment with your company. From expense reimbursements and internal transfer requests to exit interview questionnaires, HR Case Management empowers employees to help themselves find the answers they need quickly, reducing the overall workload for HR staff and service center agents.

Create an interactive workspace to engage employees

Once you’ve located top-notch millennial talent, training is the key to keeping them. “To keep Deloitte’s youngest employees satisfied, chief talent officer Mike Preston says he has focused on creating a ‘development culture,” Fortune explains. In order to foster employee loyalty and retention, “Deloitte doubled down on sending employees to training programs, and managers are expected to have frequent check-in conversations.”

Take Deloitte’s idea to the next level by creating an interactive workspace for each employee. Empower them to navigate independently through the system to easily find relevant training information and content. HR Case Management’s data security tools not only protect user privacy, they also make sure employees can only see information and content related to their role in the company. A built-in drag-and-drop process designer lets staff effortlessly create new content or processes in just a few clicks, keeping HR teams agile and independent.

Today’s millennials want contemporary digital services in the workplace. Exceed their expectations with technology that makes people as productive as possible, as quickly as possible, and make sure they enjoy the experience along the way.

3 Steps to Digital Onboarding That Empowers Your Workforce was originally published in BMC Innovation on Medium, where people are continuing the conversation by highlighting and responding to this story.

The digital revolution is well underway. In fact, a range of e-services — from online voting to taxation to e-banking and even an Internet-based Governance Academy — are being supported by countries like Estonia, the Kyiv Post notes. Estonia’s results speak for themselves: The country now saves 2 percent of its GDP each year through paperless initiatives. But this kind of shift doesn’t happen in a vacuum. Detailed, descriptive IT governance is needed to make the move from traditional processes to digital alternatives, and strategy is the key to long-term IT success.

Grounding Governance

What exactly is IT governance? Research firm Gartner defines it as “the processes that ensure the effective and efficient use of IT in enabling an organization to achieve its goals.” Put simply, governance is the planning behind the IT procedures companies utilize every single day. Ideally, strategies ensure that IT aims to both align with corporate goals and deliver measurable returns.

Strategy Sessions

How do you build an effective IT governance plan that is realistic, transparent and supports ongoing improvement? Start by identifying key data:

• How are current IT processes — InfoSec, cloud initiatives and day-to-day operations such as CRM or ERP deployments — performing over time?
• Is there room for improvement?

Next, select a team of key decision makers and assign progressive responsibility. Typically, CTOs or CIOs top the list here, but it’s worth bringing at least one other executive and strong IT managers on board to ensure you get a range of opinions.

With your team in place, determine areas in need of immediate improvement and identify key metrics of success. Consider data security. If your company is stuck in a “reactive” threat model, it may be worth designing an IT governance strategy to address and mitigate this risk. Metrics here could include the total number of successful breaches, malware and DDoS efforts stopped or detected before they cause any damage and any information obtained about new threat vectors from quarantined attacks. And as noted by an ISACA whitepaper, it’s also critical to design a realistic IT governance strategy, one “consistent with your organization’s management style and the way your organization deals with risk management and delivery of IT value.” In other words, going too far off the beaten path makes an IT strategy unsustainable since users will push back and governance efforts won’t sync with everyday processes.

Prioritizing Performance

With a basic strategy outline in place, your team needs to identify key areas of improvement and then adopt specific technology solutions to maximize performance. Consider the emerging problem of server configuration and management; many companies now run multiple server environments across local stacks, public clouds and co-location databases, making provision and troubleshooting a huge time investment. Specific IT governance here might include the adoption of BMC’s BladeLogic Server Automation tool, which streamlines this process and lets IT pros embrace other revenue-positive projects. For example, BladeLogic lets IT pros configure automatic resource provisioning, patching and reporting in addition to role-based access controls to help ensure IT compliance.

Shadow IT is another common problem for governance efforts. The threat of Shadow IT emerges when tech-savvy employees feel IT rules regarding application, service or social media access are needlessly restrictive. These employees end up using cloud computing deployments and mobile devices to circumvent IT controls if the application they prefer isn’t available or approved. By leveraging tools such as MyIT Service Broker, it’s possible to empower employees without encouraging the growth of Shadow IT. Staff get access to a huge catalog of useful and responsive apps, while IT admins gain the ability to manage, deliver and track software and services.

Bottom line? Better strategy means better IT governance. By taking the time to identify areas of concern, build the right team and ensure new initiatives align with existing corporate priorities, it’s possible to draft governance policy which clearly communicates expectations, embraces new technology and ultimately drives ROI.

Executing an Effective IT Governance Strategy was originally published in BMC Innovation on Medium, where people are continuing the conversation by highlighting and responding to this story.