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 (-1shows 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
- Variable Substitution: Dynamically set values at runtime.
- name: SalesData
kind: source
connection:
kind: Parquet
locator: <<variables.get('YEAR')>>_sales.parquet
- 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)
- 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 ''>>
- 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
- Clear Naming Conventions:
- Recommended:
Load_Customer_Orders,Clean_Order_Data,Calculate_Monthly_Revenue - Avoid:
step1,data,transform
- 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
- 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