Building Data Pipelines for Analytics

The Data Pipeline Difference

Without pipelines, data stays siloed. Marketing has their metrics. Engineering has different metrics. Finance has yet another set. Nobody knows the truth.

Data pipelines combine data from sources (app databases, APIs, logs) into a single source of truth. Analytics, machine learning, and reporting all use it.

ETL vs ELT

ETL (Extract, Transform, Load):

1. Extract data from source
2. Transform in a staging area (clean, deduplicate, join)
3. Load into data warehouse

Advantages: data is clean before warehouse, smaller warehouse size. Disadvantages: slow (transform is the bottleneck), rigid (changing transformations requires pipeline change).

ELT (Extract, Load, Transform):

1. Extract from source
2. Load raw data into warehouse
3. Transform in the warehouse using SQL

Advantages: fast (load is cheap, transform in warehouse with SQL is flexible), simple. Disadvantages: warehouse has redundant data, larger storage cost.

Modern trend: ELT. Warehouses are cheap and fast. Storage is cheap. Transformations in SQL are more maintainable than orchestration tools.

Apache Airflow: Orchestrating Pipelines

Airflow is the standard for pipeline orchestration. Define data workflows as DAGs (Directed Acyclic Graphs).

Example DAG:

download_data → validate → load_warehouse → notify_team

Each step runs after its predecessor succeeds.

Airflow Python code:

from airflow import DAG
from airflow.operators.python import PythonOperator
from datetime import datetime

def download_data():
  # Download from API
  data = fetch_api('https://api.myapp.com/events')
  save_to_s3(data, 's3://data/raw/events.parquet')

def validate_data():
  # Check schema, row counts
  df = load_from_s3('s3://data/raw/events.parquet')
  assert len(df) > 0, "No data downloaded!"
  assert 'user_id' in df.columns, "Missing user_id column"

def load_warehouse():
  # Load into data warehouse
  df = load_from_s3('s3://data/raw/events.parquet')
  df.to_sql('events', warehouse_connection, if_exists='append')

def notify_team():
  send_slack("Data pipeline completed successfully")

with DAG('daily_events_pipeline', start_date=datetime(2025, 1, 1), schedule_interval='@daily') as dag:
  extract = PythonOperator(task_id='extract', python_callable=download_data)
  validate = PythonOperator(task_id='validate', python_callable=validate_data)
  load = PythonOperator(task_id='load', python_callable=load_warehouse)
  notify = PythonOperator(task_id='notify', python_callable=notify_team)

  extract >> validate >> load >> notify

Airflow runs the DAG daily. If validate fails, load doesn't run. If load fails, notify doesn't run. Easy to visualize and debug.

Kafka for Real-Time Pipelines

For low-latency analytics, batch daily jobs are too slow. Kafka enables real-time data pipelines.

Architecture:

App → Kafka topic (events stream)
       ↓
Consumer 1: Analytics warehouse
Consumer 2: Real-time dashboards
Consumer 3: ML model training

Each event is processed immediately by consumers.

Trade-off: Real-time is harder than batch. Message deduplication, exactly-once processing, and ordering are complex. Start with daily Airflow jobs. Upgrade to Kafka when you need real-time.

Data Warehouse: BigQuery, Snowflake, Redshift

Choose where to load data.

BigQuery (Google Cloud):

• Serverless (no infrastructure to manage)
• Pay per query (not per storage)
• Fast (petabyte-scale analytics in seconds)
• Cost-effective for occasional queries
• Best for: startups, cloud-first companies

Snowflake:

• Managed, cloud-agnostic
• Separation of compute and storage (scale independently)
• Expensive (higher $/GB than BigQuery)
• Best for: enterprises with complex requirements

Redshift (AWS):

• Managed data warehouse
• Cheaper than Snowflake, more expensive than BigQuery
• Requires cluster management (less serverless)
• Best for: AWS-first companies with large data volumes

For Indian startups: BigQuery. Lowest cost, easiest to scale.

Data Quality and Validation

Garbage in, garbage out. Data pipelines must validate data.

Validation checks:

• Row count: expected 10k rows, got 100? Something's wrong.
• Schema: all expected columns present and correct types?
• Duplicates: primary key uniqueness?
• Null values: expected nulls or data quality issue?
• Freshness: data older than 24 hours means source is down?

Tool: dbt (data build tool): dbt runs SQL transformations and validates data.

{{ config(materialized='table') }}

with events as (
  select * from {{ source('raw', 'events') }}
  where event_date >= '2025-01-01'
)

select
  user_id,
  event_type,
  count(*) as event_count
from events
group by user_id, event_type

dbt tests:

models:
  - name: event_summary
    tests:
      - unique:
          column_name: user_id
      - not_null:
          column_name: user_id

If tests fail, data is not promoted to production.

Building Reliable Pipelines

Idempotency: Running a pipeline twice should have the same result.

• Don't insert, upsert (insert or update).
• Don't append, replace.
• Use unique load IDs to deduplicate.

Monitoring: Alert if pipeline fails or runs slow.

• Email if pipeline doesn't complete by 6am
• Alert if latency increases 50%
• Monitor warehouse query performance

Disaster recovery: If warehouse is corrupted, restore from backup.

• Daily snapshots of warehouse
• Keep 7-30 days of snapshots
• Test restore quarterly

Data Pipeline Checklist

• Choose ETL or ELT (ELT is usually better)
• Set up orchestration (Airflow, Prefect, or cloud-native)
• Define data sources (databases, APIs, logs)
• Implement extraction
• Implement transformations (SQL, Python, or dbt)
• Load into warehouse (BigQuery, Snowflake, Redshift)
• Validate data quality
• Monitor pipeline performance
• Document transformations
• Test disaster recovery