Back to Portfolio
JM
Case Study

Multi-Platform Enterprise Deployment & Notification Orchestrator

The Challenge

Manual code releases and documentation approvals caused operational delays. Development teams lacked immediate visibility into build outcomes or pipeline failures. Additionally, self-hosted build nodes lacked pre-installed runtimes, secret variables occasionally contained corrupting byte order marks (BOM), and execution processes had to dynamically handle multi-stage database deployments.

Technical Stack

Azure DevOps PipelinesGitHub ActionsApache Airflow (Composer)Power AutomateTeams WebhooksSharePointGoogle BigQueryPowerShell / Bash

The Solution: Dual-Platform Pipeline & Automated Gates

I architected a continuous delivery pipeline triggered by SharePoint approvals. It orchestrates downstream data processing workflows using Apache Airflow and publishes status cards straight to Microsoft Teams channels. For code validation and pull requests, I integrated a robust GitHub Actions workflow pipeline to execute dry-runs against Google BigQuery.

Azure DevOps CI/CD

Constructed build definitions (`azure-pipelines-build.yml`) that bootstrap isolated execution runners to run validation scripts manually.

GitHub Actions Validation

Designed validation flows (`bigquery-validate.yml`) utilizing GitHub repository secrets to trigger dry-run SQL syntax tests.

Airflow Orchestration

Schedules daily ETL tasks and executes downstream database refreshes inside an Airflow DAG once deployment finishes.

SharePoint Approval Gates

Triggers automated deployment cycles instantly when configuration or release documents receive approvals.

Teams & Power Automate Alerts

Format status block adaptive cards in Teams, alerting engineers of pipeline state transitions, deployment success, or build warnings.

Engineering Highlights & Fixes

1
Self-Hosted Python BootstrappingResolved python-less self-hosted Windows build nodes by dynamically downloading and configuring embeddable Python distributions, enabling site modules, and initializing package installers (`get-pip.py`) at execution time.
2
Byte Order Mark (BOM) Secret SanitizationFixed JSON decode crashes in the service account credentials loader by scripting BOM-stripping hooks, purging hidden UTF-8 BOM prefixes from secret variables prior to compiling auth credentials.
3
Recursive SQL DiscoveryReplaced hard-coded pipeline execution scripts with dynamic folder scans (`Get-ChildItem`), recursively discovering and sorting new SQL queries inside `dags/sql/` to execute in a stable, deterministic order.

The Impact

  • 80% Time Reduction: Replaced manual approval processes and release hand-offs with immediate automated CD triggers.
  • Strict Compliance: Formed a complete audit trail syncing deployment gates with SharePoint records.
  • Real-Time Visibility: Reduced incident resolution times by alerting engineering teams within seconds of build disruptions.
Deployment Architecture

NPD Deployment Automation Process Flow

To minimize Google Cloud Platform infrastructure overhead, I architected a transient pipeline deployment workflow. Instead of maintaining a costly 24/7 Apache Airflow (Cloud Composer) instance, the environment is dynamically provisioned, executed, and torn down. Below is the end-to-end automation flowchart.

How It Works: Step-by-Step
1
Ingestion & TriggeringRuns on a scheduled trigger (every deployment date at 9:00 PM) or manually on-demand using a chat trigger (`#deployadhoc`). Power Automate synchronizes the registry list (`DE Document.csv`) across repositories.
2
Phase 1: ADO Patching & Resource ProvisioningAn Azure DevOps pipeline parses the CSV, checks if an ad-hoc or standard deployment is scheduled for today, and patches the relevant target files. Once patching is complete, it builds and releases a transient Cloud Composer environment.
3
Phase 2: Airflow DAG ExecutionInside the freshly initialized Cloud Composer instance, the automated trigger DAG (`deployment_date_trigger_dag`) boots up, detects the deployment category (Ad-hoc or Daily Main DAGs), and executes the data pipelines in sequence.
4
Teardown & CleanupImmediately following successful database executions, the pipeline triggers a self-destruct command (`Trigger Destroy Composer`) to dissolve the Composer environment, saving thousands of dollars in monthly cloud running costs.
Key Data Engineering Highlight

By coordinating Power Automate triggers with Azure DevOps conditional pipelines and Airflow environment lifecycle managers, this architecture achieves serverless efficiency for large-scale enterprise workflows.

NPD Deployment Automation Process Flowchart

Enterprise Automation Architecture & AI Pipeline Topology

High-level architectural blueprint visualizing the end-to-end event-driven orchestration layer. Integrates SharePoint document triggers, Azure DevOps build runners, Google Cloud Composer (Airflow) container lifecycle managers, and automated Teams notifications.

Enterprise Automation Architecture Diagram

Figure 1.1: Event-Driven Automation Topology — SharePoint → Azure DevOps → Apache Airflow → BigQuery → Teams Notifications

Video Walkthrough: Connecting Azure DevOps to Google BigQuery

A 4-minute technical walkthrough demonstrating the continuous integration and delivery (CI/CD) workflow. The video showcases configuring pipeline variables, running the Azure DevOps pipeline build definitions (`Virenesis_Build`), executing automated SQL data modeling tasks (`02_sample_ecommerce_data.sql` to generate a 10,000 row mock dataset), and verifying the schema deployment inside Google BigQuery Studio.