combine

data management

slug: step-combine

Combine Step

Purpose

Integrates multiple datasets into a single unified dataset using either vertical stacking (append) or relational joins. Provides flexible options for merging data from disparate sources.

When to Use

• Consolidate data from multiple sources
• Integrate data across different time periods
• Combine complementary datasets for comprehensive analysis
• Enrich base data with additional attributes from lookup tables
• Create master datasets from specialized extracts
• Implement SQL-like joins in the data pipeline

How It Works

1. Takes the base dataset and one or more additional datasets
2. Applies one of two combination methods:
   • Append: Stacks datasets vertically (adding rows)
   • Join: Merges datasets horizontally (adding columns) based on key relationships
3. Handles metadata merging for PDV (Physical Data View)
4. Tracks detailed statistics about the combination process
5. Returns a unified dataset with consolidated structure

Parameters

Required

• datasets (array) - Datasets to combine with the base dataset

Optional

• method (string) - Combination method: append (default) or join
• join_type (string) - For join method: inner, left, right, or full (default: inner)
• join_on (string|array) - Join key(s) or conditions:
  • Simple string: "customer_id" (assumes same column name in both datasets)
  • Explicit mapping: {"left": "id", "right": "customer_id"}
  • Multiple conditions: [{"left": "id", "right": "customer_id"}, {"left": "region", "right": "territory"}]
• join_prefix (boolean) - Add dataset name prefix to columns to prevent conflicts (default: true)
• keep_duplicates (boolean) - For append method: keep duplicate records (default: true)
• strict_mode (boolean) - Throw exceptions on errors (default: false)
• preserve_types (boolean) - Maintain data types during combination (default: true)

Input Requirements

• Base dataset must be properly structured
• Additional datasets must be available and compatible for the chosen combination method
• For joins, specified join keys must exist in respective datasets
• At least one additional dataset must be specified in datasets parameter

Output

Data

• For append: Vertically stacked records from all datasets
• For join: Base dataset enriched with columns from other datasets based on join relationships

PDV

• Combined metadata for all columns in the result dataset
• For joins with join_prefix: true, column names include dataset prefixes
• Latest metadata definition wins for overlapping column names

Extras

• combineData_method - Method used (append or join)
• combineData_applied - Timestamp when operation was performed
• records_in - Number of records in the base dataset
• records_out - Number of records in the result dataset
• datasets_combined - Number of datasets combined

For append operations:

• append_stats - Detailed statistics about the append operation:
  • base_records - Count of records in base dataset
  • datasets_appended - Array of appended datasets with record counts

For join operations:

• join_type - Type of join performed
• join_stats - Detailed statistics about the join operation:
  • base_records - Count of records in base dataset
  • joins_performed - Array of join operations with before/after counts

Example Usage

Append Example

combine:
  method: append
  datasets:
    - q1_sales
    - q2_sales
    - q3_sales
    - q4_sales
  keep_duplicates: false

Join Example

combine:
  method: join
  datasets:
    customers: customers_dataset
    orders: orders_dataset
    products: products_lookup
  join_type: left
  join_on:
    - {left: "customer_id", right: "id"}
    - {left: "product_id", right: "sku"}
  join_prefix: true

Example Output

Append Example

Input Datasets

Base Dataset (q1_sales) | quarter | region | product | revenue | |---------|--------|----------|---------| | Q1 | North | Widgets | 12500 | | Q1 | South | Gadgets | 8750 |

Additional Dataset (q2_sales) | quarter | region | product | revenue | |---------|--------|----------|---------| | Q2 | North | Widgets | 15000 | | Q2 | East | Gadgets | 11250 |

Appended Output

quarter	region	product	revenue
Q1	North	Widgets	12500
Q1	South	Gadgets	8750
Q2	North	Widgets	15000
Q2	East	Gadgets	11250

Join Example

Input Datasets

Base Dataset (orders) | order_id | customer_id | product_id | quantity | |----------|-------------|------------|----------| | 1001 | C123 | P456 | 2 | | 1002 | C789 | P345 | 1 |

Additional Dataset (customers) | id | name | segment | |-------|-------------|----------| | C123 | John Smith | Premium | | C789 | Sarah Jones | Standard |

Joined Output (with join_prefix: true)

order_id	customer_id	product_id	quantity	customers_name	customers_segment
1001	C123	P456	2	John Smith	Premium
1002	C789	P345	1	Sarah Jones	Standard

Related Documentation

• load step - Load data before combining
• filter step - Filter combined datasets
• calculate step - Add calculated columns to combined data