Overview
A nine-stage production data pipeline that moves ServiceNow ITSM data (Incidents, Change Requests, Problems, CMDB) into Microsoft Power BI for operational reporting on incident volume, SLA compliance and MTTR. The pipeline follows a medallion architecture (Bronze → Silver layers) and is fully event-driven: each successful extraction automatically triggers transformation and report refresh with no manual intervention.
Pipeline architecture
ServiceNow REST APIstage 1
Source system: table-level read via a dedicated API service account.
AWS AppFlowstage 2
Managed ETL connector, with daily incremental extraction per table.
Amazon Redshift (Bronze)stage 3
Raw landing zone: timestamped, unmodified source data.
Amazon EventBridgestage 4
Event-driven trigger on AppFlow S3 success events.
Amazon SQSstage 5
Message queue decoupling EventBridge from Jenkins.
Jenkins + Bitbucketstage 6
CI/CD pipeline that polls SQS and executes DBT transformation jobs.
Amazon Redshift (Silver)stage 7
Clean, modeled reporting layer managed by DBT.
Microsoft Power Automatestage 8
HTTP-triggered dataset refresh orchestration.
Microsoft Power BI Servicestage 9
Semantic model, dashboards, and row-level security.
Data extraction and ingestion
AWS AppFlow acts as the managed ETL connector between ServiceNow and Redshift, abstracting API authentication, pagination, and incremental load logic. One AppFlow flow is created per source table for independent scheduling and targeted reruns. The incremental load strategy uses a watermark field (sys_updated_on) to extract only new or changed records, minimizing API quota consumption.
AppFlow stages data in S3 before Redshift ingestion. All records land in the Bronze schema (bronze.incident, bronze.change_request, etc.) with ingestion timestamps for lineage tracking and reprocessing support.
Transformation layer
Jenkins polls SQS for flow-completion messages. On receipt, it pulls the latest DBT project from Bitbucket and executes the relevant DBT models against the Bronze schema. Transformed output is written to the Silver schema (silver.servicenow_incidents, etc.): clean, typed, and joined to reference data.
DBT enforces data quality at run time using built-in tests (unique, not_null, accepted_values). On a successful DBT run, Jenkins sends an HTTP POST to a Power Automate webhook to trigger the Power BI dataset refresh, then deletes the SQS message.
Reporting and access control
Power BI Desktop connects to the Redshift Silver schema via JDBC/ODBC through an on-premises data gateway (hosted on an EC2 instance in the same VPC). Reports and semantic models are published to a shared Power BI workspace. Row-Level Security (RLS) roles are defined in Power BI Desktop and validated with the "View as role" feature before go-live, ensuring each team sees only their relevant data.
Key design decisions
One flow per table
Independent scheduling and error isolation; failures in one table do not cascade.
SQS as buffer
Decouples EventBridge from Jenkins, guaranteeing delivery even during transient consumer downtime. A Dead Letter Queue captures messages that exceed max receive count.
Least-privilege IAM
Each stage uses a scoped service role (AppFlow role, DBT IAM user, M365 service account) with only the permissions required for that stage.
Private networking
Redshift clusters reside in a private subnet. The on-premises data gateway on EC2 allows Power BI Service to reach Silver without public cluster exposure.
Stack
- ServiceNow
- AWS AppFlow
- Amazon S3
- Amazon Redshift
- Amazon EventBridge
- Amazon SQS
- Jenkins
- Bitbucket
- DBT
- Power Automate
- Power BI