DEV Community

Sospeter Mong'are
Sospeter Mong'are

Posted on

Understanding Azure Data Factory Pipelines: From Source Systems to Production

#ai #programming #productivity #tutorial

If you're new to Azure Data Factory (ADF), pipelines can initially feel complicated. You may see triggers, linked services, datasets, integration runtimes, copy activities, staging tables, transformations, Airflow, dbt, and several other components working together.

The easiest way to understand ADF is to stop thinking of it as just a tool that "moves data" and instead think of it as part of a data journey.

A typical enterprise data flow might look like this:

Source System
     |
     v
ADF Trigger
     |
     v
ADF Pipeline
     |
     v
Integration Runtime
     |
     v
Extract / Read Source
     |
     v
Load to Staging
     |
     v
Validation
     |
     v
Airflow
     |
     v
dbt Transformations
     |
     v
Fact / Dimension Tables
     |
     v
Reporting / Applications
Enter fullscreen mode Exit fullscreen mode

Let's break down what happens at each stage.

1. The Source System

Everything starts with a source.

The source is the system where the original data exists. It could be:

  • Oracle
  • SQL Server
  • MySQL
  • PostgreSQL
  • REST APIs
  • SFTP servers
  • CSV files
  • Azure Blob Storage
  • Other enterprise applications

For example, imagine an organization stores claims data in an Oracle database.

ADF needs to connect to that Oracle database before it can extract anything.

Conceptually:

Oracle Database
      |
      v
  ADF Pipeline
Enter fullscreen mode Exit fullscreen mode

If ADF cannot establish a connection to Oracle, nothing else can happen.

For example, you might encounter an error such as:

ORA-12170: Cannot connect.
TCP Connect timeout of 20000
for host 10.10.10.30 port 1521
Enter fullscreen mode Exit fullscreen mode

This tells you that the problem occurred while trying to establish the network connection to Oracle. It is not necessarily a problem with the SQL query or the target table.

2. The Trigger

A trigger determines when a pipeline should run.

There are several common ways a pipeline can be triggered.

Scheduled trigger

A pipeline might run every night:

Every day
     |
     v
02:00 AM
     |
     v
Start pipeline
Enter fullscreen mode Exit fullscreen mode

Manual trigger

During development or testing, an engineer might manually start the pipeline.

For example:

ADF
 |
 +--> Trigger Now
Enter fullscreen mode Exit fullscreen mode

Event-based trigger

A pipeline can also start when something happens.

For example:

New file uploaded
       |
       v
Event detected
       |
       v
ADF pipeline starts
Enter fullscreen mode Exit fullscreen mode

So, the trigger essentially answers:

When should this pipeline start?

3. The Pipeline

Once the trigger fires, ADF creates a pipeline run.

The pipeline defines what should happen.

A pipeline can contain multiple activities:

Start
  |
  v
Extract data
  |
  v
Load staging table
  |
  v
Validate data
  |
  v
Complete
Enter fullscreen mode Exit fullscreen mode

Each activity performs a specific task.

For example, a pipeline could contain:

  • Copy Activity
  • Lookup Activity
  • Stored Procedure Activity
  • Data Flow
  • If Condition
  • ForEach
  • Web Activity
  • Execute Pipeline

The pipeline is essentially the workflow that connects these activities together.

4. The Integration Runtime

This is one of the most important concepts to understand in ADF.

The Integration Runtime (IR) provides the infrastructure used by ADF to connect to data sources and move or process data.

There are different types of Integration Runtime, but one that frequently matters in enterprise environments is the Self-hosted Integration Runtime.

Imagine your Oracle database is inside a private network:

              Private Network

            Oracle Database
            10.10.10.30:1234
                   ^
                   |
                   |
          Self-hosted IR
                   ^
                   |
                   |
                  ADF
Enter fullscreen mode Exit fullscreen mode

ADF may not be able to directly access the Oracle database.

The Self-hosted Integration Runtime provides the connection between ADF and the private data source.

This is why errors such as:

ORA-12170
TCP Connect timeout
Enter fullscreen mode Exit fullscreen mode

can be caused by:

  • Network connectivity
  • Firewall rules
  • Oracle listener issues
  • Incorrect host or port
  • Self-hosted IR problems
  • Routing issues
  • Database availability

A useful troubleshooting question is:

Can the machine running the Self-hosted IR reach the Oracle server on port 1521?

For example, from the Self-hosted IR machine, you might test:

Test-NetConnection 10.10.10.30 -Port 1521
Enter fullscreen mode Exit fullscreen mode

If the result is:

TcpTestSucceeded : True
Enter fullscreen mode Exit fullscreen mode

the machine can establish a TCP connection to the Oracle server.

If it returns:

TcpTestSucceeded : False
Enter fullscreen mode Exit fullscreen mode

you have a connectivity problem that needs to be investigated before looking at SQL or transformation logic.

5. Linked Services

A Linked Service defines how ADF connects to an external system.

Think of it as the connection configuration.

For example, an Oracle Linked Service may contain:

Host
Port
Service Name / SID
Username
Password
Integration Runtime
Enter fullscreen mode Exit fullscreen mode

Conceptually:

Linked Service
      |
      +--> Oracle
      |
      +--> Host
      +--> Port
      +--> Credentials
      +--> Integration Runtime
Enter fullscreen mode Exit fullscreen mode

The Linked Service answers:

How do I connect to this system?

6. Datasets

A Dataset represents the data you want to work with.

For example:

Oracle Linked Service
        |
        v
     Dataset
        |
        v
tableusers
Enter fullscreen mode Exit fullscreen mode

There is an important distinction between a Linked Service and a Dataset.

Linked Service:

How do I connect to the system?

Dataset:

What data do I want to access?

For example:

Linked Service
      |
      v
Oracle Database

Dataset
      |
      v
tableusers
Enter fullscreen mode Exit fullscreen mode

7. Copy Activity

The Copy Activity is one of the most commonly used ADF activities.

Its primary purpose is to move data from a source to a destination.

For example:

Oracle
   |
   | Copy Activity
   v
SQL Server
   |
   v
Staging Table
Enter fullscreen mode Exit fullscreen mode

Imagine the Oracle source contains:

tableusers
Enter fullscreen mode Exit fullscreen mode

ADF could copy that data into:

stgschema.stg_table
Enter fullscreen mode Exit fullscreen mode

The flow becomes:

Oracle
   |
   v
ADF Copy Activity
   |
   v
stgschema.stg_table
Enter fullscreen mode Exit fullscreen mode

This is often the first major data movement step.

8. Staging

Many enterprise data platforms use staging tables as an intermediate landing area.

Instead of transforming everything directly from the source, the organization first lands the data into staging.

For example:

Oracle
   |
   v
ADF
   |
   v
Staging
   |
   v
Transformation
Enter fullscreen mode Exit fullscreen mode

The staging layer provides a controlled location where the incoming data can be stored before further processing.

For example:

Oracle:
tableusers

        |
        v

ADF

        |
        v

SQL Server:
stgschema.stg_table
Enter fullscreen mode Exit fullscreen mode

9. Transformation

After data has been loaded into staging, it often needs to be transformed.

For example, you may need to:

  • Clean data
  • Rename columns
  • Remove duplicates
  • Join tables
  • Apply business rules
  • Convert data types
  • Create calculated fields
  • Build dimensions
  • Build fact tables

ADF itself can perform transformations using Data Flows and other activities.

However, many modern data platforms separate data movement from data transformation.

For example:

ADF
 |
 v
Staging
 |
 v
Airflow
 |
 v
dbt
 |
 v
Fact / Dimension Tables
Enter fullscreen mode Exit fullscreen mode

This is where understanding the entire ecosystem becomes important.

10. Airflow

In some enterprise environments, ADF is not responsible for the entire data pipeline.

Instead, ADF handles part of the workflow, while Apache Airflow handles downstream orchestration.

You might have:

Oracle
   |
   v
ADF
   |
   v
Staging Tables
   |
   v
Airflow
   |
   v
dbt
Enter fullscreen mode Exit fullscreen mode

Airflow can coordinate what happens after the data has landed.

For example:

ADF finishes
     |
     v
Airflow starts
     |
     v
Run dbt models
     |
     v
Run validations
     |
     v
Complete pipeline
Enter fullscreen mode Exit fullscreen mode

This is why it is important to understand the dependency between ADF and Airflow in your organization's architecture.

11. dbt

dbt is commonly used for the transformation layer.

You might have staging tables such as:

stg_tbltableusers
stg_tblcustomer
stg_tblpolicy
Enter fullscreen mode Exit fullscreen mode

Then dbt transforms them into analytical models:

staging
   |
   v
dbt
   |
   +--> dim_customer
   |
   +--> dim_policy
   |
   +--> fct_claims
Enter fullscreen mode Exit fullscreen mode

This is where raw or staged data becomes structured, business-ready data.

For example:

stg_tbltableusers
            |
            v
       dbt model
            |
            v
        fct_claims
Enter fullscreen mode Exit fullscreen mode

12. Validation

A good data pipeline should not simply say:

"The data moved successfully."

It should also verify that the data is correct.

Validation might include checking:

  • Record counts
  • Null values
  • Duplicate records
  • Referential integrity
  • Data types
  • Business rules
  • Source-to-target counts

For example:

Source count:
1,500,000

Target count:
1,500,000

Result:
PASS
Enter fullscreen mode Exit fullscreen mode

But if you get:

Source count:
1,500,000

Target count:
1,200,000

Result:
FAIL
Enter fullscreen mode Exit fullscreen mode

the pipeline may need to stop or trigger an alert.

13. Dependencies

ADF pipelines can contain dependencies between activities.

For example:

Extract Data
     |
     v
Load Staging
     |
     v
Validate
     |
     v
Continue
Enter fullscreen mode Exit fullscreen mode

The next activity only executes when the previous activity meets its dependency condition.

You can also have different paths:

             Extract
                |
        +-------+-------+
        |               |
     Success           Failure
        |               |
        v               v
     Continue        Error Flow
Enter fullscreen mode Exit fullscreen mode

Understanding these dependencies is extremely important when troubleshooting.

When a pipeline fails, don't simply look at the pipeline's overall status.

Look at:

Which activity failed?

That usually tells you where the problem actually occurred.

14. Monitoring Long-Running Pipelines

This is particularly important when you're working with enterprise pipelines.

Not every pipeline takes five minutes.

You might encounter pipelines that run for:

10 minutes
1 hour
6 hours
12 hours
Several days
Enter fullscreen mode Exit fullscreen mode

If someone tells you:

"Run this pipeline. It may take several days."

you need to know how to monitor it.

In ADF, you can go to:

Monitor -> Pipeline runs

You can inspect things such as:

Pipeline
Status
Start Time
End Time
Duration
Trigger
Parameters
Enter fullscreen mode Exit fullscreen mode

You can then drill down into the individual activities.

For example:

Activity              Status       Duration
------------------------------------------------
Extract Claims        Succeeded    2h 14m
Load Staging          Running      5h 32m
Validation            Pending      -
Enter fullscreen mode Exit fullscreen mode

This helps you distinguish between:

"The pipeline is still running"

and:

"The pipeline is actually stuck or failing."

That distinction is especially important with long-running data processes.

15. QA and Production

In an enterprise environment, you generally don't make changes directly in production.

A simplified deployment flow might look like:

Development
     |
     v
QA ADF
     |
     | Test
     v
Production ADF
     |
     v
Production Execution
Enter fullscreen mode Exit fullscreen mode

The QA environment allows you to test whether the pipeline behaves as expected.

Once the changes are validated, they can be promoted to production according to the organization's deployment process.

However, don't assume that because something works in QA it will automatically work in production.

The environments can have different:

  • Databases
  • Credentials
  • Integration Runtimes
  • Network configurations
  • Firewall rules
  • Permissions
  • Connection strings
  • Parameters

Therefore, a pipeline can succeed in QA and fail in production because of an environmental difference.

Putting Everything Together

A typical enterprise data flow might therefore look like this:

                 SOURCE SYSTEM
                      |
                      v
                   Oracle
                      |
                      v
                 ADF Trigger
                      |
                      v
                 ADF Pipeline
                      |
                      v
            Integration Runtime
                      |
                      v
                Copy Activity
                      |
                      v
                  STAGING
                      |
                      v
                   Airflow
                      |
                      v
                    dbt
                      |
             +--------+--------+
             |                 |
             v                 v
       Dimensions            Facts
             |                 |
             +--------+--------+
                      |
                      v
             Reporting / Apps
Enter fullscreen mode Exit fullscreen mode

The important thing is that ADF may only be one part of the overall data platform.

ADF might handle:

Source connectivity + extraction + data movement + initial orchestration

Airflow might handle:

Workflow orchestration + scheduling + dependencies

dbt might handle:

Data transformation + business logic + analytical models

A Simple Mental Model

If you're learning this ecosystem, remember these questions:

Component Question
Trigger When should it run?
Pipeline What should happen?
Linked Service How do I connect?
Dataset What data am I accessing?
Integration Runtime Where/how does the connection execute?
Copy Activity How do I move the data?
Data Flow How do I transform data in ADF?
Staging Where does the incoming data land?
Airflow How do I orchestrate downstream processes?
dbt How do I transform the data into business models?
Validation Did the process produce correct data?
Monitor What happened during execution?

The bigger picture is:

SOURCE
  |
  | Oracle / API / Files / etc.
  v
ADF
  |
  | Integration Runtime
  v
EXTRACT
  |
  v
STAGING
  |
  v
AIRFLOW
  |
  v
DBT
  |
  v
FACTS / DIMENSIONS
  |
  v
REPORTING / CONSUMPTION
Enter fullscreen mode Exit fullscreen mode

Once you understand this flow, ADF errors become much easier to reason about.

For example, an error like:

ORA-12170
TCP Connect timeout
Enter fullscreen mode Exit fullscreen mode

points you toward the connectivity layer.

An error like:

ORA-00942
table or view does not exist
Enter fullscreen mode Exit fullscreen mode

points more toward the database object or permissions layer.

A dbt UNION column-count error points toward the transformation layer.

And an Airflow DAG failure points toward the orchestration layer.

The key skill is therefore not just learning how to click through ADF. It is learning to identify which layer of the data pipeline is failing and why.

Top comments (0)