Data Integration

01 What it is

From many silos to one view

It matters because it powers everything downstream: analytics and BI, AI/ML and RAG, and customer or entity 360 views. Get it right and every team works from the same numbers; get it wrong and you reconcile spreadsheets forever. The central design choice is where and when transformation happens — before load (ETL) or after (ELT), in batch or in a stream.

02 Core concepts

The moving parts

• ETL vs ELT — transform before load, or load raw and transform in the target. ELT is the cloud default.
• Batch vs streaming — periodic bulk moves vs continuous, low-latency flow.
• CDC — change data capture reads row-level changes from source logs for low-impact sync.
• Pipeline & connector — a source-to-target flow, and the pre-built adapter that taps a source.
• Orchestration — scheduling, dependencies, retries and backfills across tasks.
• Reverse ETL — pushing warehouse data back into operational apps.
• Data virtualisation / federation — querying sources in place, without copying.
• iPaaS & EAI — cloud integration platforms and app-to-app integration.

03 Patterns

Choose the pattern for the latency

04 Key personas

Who integrates data

05 Methodology

Discover, move, model, monitor

06 Best practices

What good looks like

07 2026 trends

The lakehouse era

↻ Refreshed 2026-07-16 by the AI desk

08 In practice

Plumbing that moves the business

Integration is invisible when it works and catastrophic when it doesn't. Here's how enterprises apply it, what the analysts and platforms are doing, and how SCIKIQ delivers it.

What the leaders are doing

ELT on the lakehouse and open table formats now define the category — here's the signal from the standard, the analysts and the platforms.

09 Developer lab

Hands-on: ship a reliable pipeline

# dlt: incremental, idempotent API ingestion
import dlt
@dlt.resource(primary_key="id", write_disposition="merge")
def orders(updated_at=dlt.sources.incremental("updated_at")):
    yield get_orders(since=updated_at.last_value)

pipeline = dlt.pipeline(destination="snowflake")
pipeline.run(orders)

# Land raw, then transform — 3 retries and backfill built in
from airflow.decorators import dag, task
from pendulum import datetime

@dag(schedule="@hourly", start_date=datetime(2026, 1, 1),
     catchup=True, default_args={"retries": 3})
def orders_elt():
    @task
    def extract_load():   ...   # dlt / Airbyte lands raw rows idempotently
    @task
    def transform():      ...   # dbt build --select stg_orders+
    extract_load() >> transform()

orders_elt()

// Stream row-level changes from Postgres into Kafka
{
  "name": "orders-cdc",
  "config": {
    "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
    "database.hostname": "db.internal",
    "table.include.list": "public.orders",
    "plugin.name":  "pgoutput",
    "topic.prefix": "cdc",
    "snapshot.mode": "initial"
  }
}

-- Idempotent, incremental merge — safe to re-run any time
{{ config(materialized='incremental', unique_key='order_id',
          incremental_strategy='merge') }}

select order_id, customer_id, amount, updated_at
from {{ source('cdc', 'orders') }}
{% if is_incremental() %}
  where updated_at > (select max(updated_at) from {{ this }})
{% endif %}

A complete real-time slice: Airflow orchestrates with retries, Debezium captures every change from the source, and the dbt model merges incrementally — re-runnable, deduplicated and cheap.

10 Analyst lab

Hands-on: specify the integration

11 Career path

From ETL developer to head of platform

Data engineering is among the most in-demand data roles. Bands are indicative US figures and vary by market.

12 Skill matrix

Rate yourself, then level up

Find your current row and aim one column right.

13 Glossary

Terms worth knowing

14 Test yourself

Check your understanding

15 Keep learning

Where to go next

Integrated data needs structure, meaning and governance to be useful. Continue the path: