Documentation

Pipeline Basics

Learn how pipelines move and transform data from sources to destinations using steps, SQL transformations, and sinks.

← Back to Documentation Hub / Data Automation

What is a Pipeline?

A pipeline is a sequence of steps that moves and transforms data from sources to destinations. Think of it as an assembly line for your data: raw data goes in, gets cleaned, enriched, and analyzed, then insights come out.

Every pipeline follows the same basic pattern:

SOURCE → TRANSFORM → SINK (optional)

1. Source: Where Data Comes From

- name: EcommerceData
  kind: source # This step LOADS data
  connection:
    kind: Parquet # Data format
    locator: ecommerce.parquet # Location
  show: -1 # Preview data

Key attributes:

  • name: Reference this data in later steps.
  • kind: source: Identifies the step as a data loader.
  • connection: Defines how to access the data.
  • show: Controls preview display (-1 shows all rows).

Other supported source connections:

# CSV file
connection:
  kind: CSV
  locator: data.csv

# Excel file
connection:
  kind: File
  locator: data.xlsx
  params:
    kind: xlsx
    sheet_name: Sheet1

# HTTP API
connection:
  kind: Rest
  client:
    base_url: https://api.example.com
    auth:
      kind: header
      values:
        x-Key: <<secrets.get('API_KEY')>>
  request:
    method: POST
    endpoint: /data
    body: <<variables.get('MyData')>>

# Database
connection:
  kind: Clickhouse
  locator: sales
  query: SELECT * FROM Clickhouse

2. Transform: How Data Changes

- name: CleanAndAnalyze
  kind: transform # This step MODIFIES data
  query: |
    SELECT 
      InvoiceNo,
      CustomerID,
      Country,
      -- Calculate new fields
      Quantity * UnitPrice as LineTotal,
      -- Add business logic
      CASE 
        WHEN Description LIKE '%POSTAGE%' THEN 'Shipping'
        ELSE 'Product'
      END as LineType
    FROM EcommerceData # Reference the source by name
    WHERE CustomerID IS NOT NULL # Filter out incomplete records
      AND Quantity > 0

Key attributes:

  • name: Give transformations clear, descriptive names.
  • kind: transform: Identifies the step as a data processor.
  • query: SQL query defining the transformation logic.

Use FROM StepName to reference data from any previous step.

Common transformation patterns:

-- 1. Clean data
SELECT 
  TRIM(Name) as CleanName,
  COALESCE(Email, '[email protected]') as Email,
  CAST(Price AS DECIMAL(10,2)) as Price
FROM RawData;

-- 2. Aggregate data
SELECT 
  Country,
  COUNT(*) as OrderCount,
  SUM(Revenue) as TotalRevenue,
  AVG(Revenue) as AvgOrderValue
FROM Orders
GROUP BY Country;

-- 3. Join data
SELECT 
  o.OrderID,
  c.CustomerName,
  p.ProductName,
  o.Quantity * p.Price as LineTotal
FROM Orders o
JOIN Customers c ON o.CustomerID = c.ID
JOIN Products p ON o.ProductID = p.ID;

-- 4. Window functions
SELECT 
  Date,
  Revenue,
  SUM(Revenue) OVER (ORDER BY Date) as RunningTotal,
  AVG(Revenue) OVER (ORDER BY Date ROWS 7 PRECEDING) as WeeklyAvg
FROM DailySales;

3. Sink: Where Data Goes

- name: SaveResults
  kind: sink # This step OUTPUTS data
  connection:
    kind: CSV
    locator: results.csv # Output location

- name: SendToDashboard
  kind: sink
  connection:
    kind: HTTP
    url: https://dashboard.company.com/api/data
    method: POST
    headers:
      Authorization: Bearer <<secrets.get('API_TOKEN')>>

Pipeline Execution Flow

SOURCE                TRANSFORM             SINK
• Load data    ───▶   • Clean      ───▶    • Save
• Validate            • Calculate           • Export
• Cache               • Filter              • Notify
  │                     │                     │
  ▼                     ▼                     ▼
Raw data              Clean data            Business insights

Essential Pipeline Features

  1. Variable Substitution: Dynamically set values at runtime.
- name: SalesData
  kind: source
  connection:
    kind: Parquet
    locator: <<variables.get('YEAR')>>_sales.parquet
  1. Error Handling: Choose how steps react to execution errors.
- name: LoadData
  kind: source
  connection:
    kind: Parquet
    locator: data.parquet
  on_error: skip # Options: skip, fail (default)
  1. Conditional Execution: Skip steps based on logic checks.
- name: CheckDataQuality
  kind: transform
  query: |
    SELECT COUNT(*) as RowCount FROM SalesData
  skip_if: <<yes if variables.get('Sentinel')<=0 else ''>>
  1. Looping: Iterate through values sequentially.
- name: MakeLoop
  kind: transform
  query: SELECT 'US' AS value UNION ALL SELECT 'MX' AS value

- name: Process
  kind: pipeline
  stages:
    - name: Data
      kind: transform
      query: SELECT * FROM SalesData WHERE Country = '<<API.look('loop_control.value', variables)>>'

Best Practices

  1. Clear Naming Conventions:
  • Recommended: Load_Customer_Orders, Clean_Order_Data, Calculate_Monthly_Revenue
  • Avoid: step1, data, transform
  1. Step Documentation: Add descriptions and SQL comments to maintain readable configuration.
- name: EnrichWithCustomerData
  kind: transform
  description: "Join orders with customer profiles"
  tags: [enrichment, customer, join]
  query: |
    -- Purpose: Add customer demographics to orders
    -- Author: Data Team
    SELECT 
      o.*,
      c.CustomerTier,
      c.JoinDate,
      c.Region
    FROM Orders o
    LEFT JOIN Customers c ON o.CustomerID = c.ID
  1. Modular Design: Break long workflows into multiple distinct pipelines managed within a single job for easier troubleshooting and faster runs.

Common Questions

Q: Can I have multiple sources in a single pipeline?

Yes. Define multiple kind: source steps in your pipeline, then reference both step names inside a subsequent SQL transformation (such as a JOIN or UNION).

Q: Can transformations reference multiple previous steps?

Yes. Any step can reference any preceding step by its specified step name.

Q: Do I always need to add a sink step?

No but it is common use case to have at least one. Data gets forgotten the moment the pipeline ends unless you save it somewhere - database, file, or HTTP endpoint. If you are working with one-off analysis, or testing a workflow, you can end the pipeline with a transform step.

Q: Can I generate data without an external source?

Yes. You can use built-in SQL macros to build calendars or calculated sequences directly in a transform step:

-- Generate a date calendar
- name: DateCalendar
  kind: transform
  query: |
    SELECT * FROM Fn.calendar()

-- Run math calculations directly
- name: Sequence
  kind: transform
  query: |
    SELECT 
      Fn.add(1, 2) as Three,
      Fn.minus(10, 3) as Seven,
      Fn.mult(4, 5) as Twenty,
      Fn.div(10, 2) as Five

← Back to Documentation Hub / Data Automation