How to Use PythonScript Connector Without Loading All Data into a DataFrame (Avoid Memory Issues & Data Duplication)
Published:
This article explains how to handle large datasets in the Bold BI PythonScript (Bold Data Hub) connector without loading all data into a single Pandas DataFrame. It covers batching and incremental strategies to prevent memory issues, along with root cause analysis and solutions for avoiding duplicate records.
Problem Statement
Memory Issue
- Loading large datasets into a single DataFrame:
- High memory usage
- Possible crashes or failures
Duplication Issue (After Fixing Memory)
- Switching to batching solves memory issues but may cause:
- Duplicate records in the destination
Root Cause of Duplication
- Duplication typically occurs due to:
- Data being processed in loops (batch inserts)
- Overlapping data ranges between batches
- Missing or ineffective primary key enforcement
- Reprocessing previously loaded records (no watermark control)
How to Identify It
- Compare:
- Total record count
- Distinct primary key count
If:Total rows ≠ Distinct primary key rows, then: Duplicate data exists.
Step-by-Step Solution
1. Create a Bold Data Hub Pipeline
- Sign it to Bold BI and select Data Hub
- Click on the + icon to Add Pipeline
- Give your Pipeline a name and select PythonScript as the template
2. Choose a Data Processing Strategy
- Select the most suitable approach:
Incremental Load (Recommended)- Load only new/updated rows using a watermark
Chunked/Batched Load - Process data in fixed sizes (e.g., 50k rows per batch)
Direct-to-Destination Writes - Write each batch directly without building a full dataset in memory
- Load only new/updated rows using a watermark
The PythonScript connector does not provide built-in selection for batching or incremental loading. These strategies must be implemented directly within the Python script using custom logic such as loops (for batching) and filters (for incremental loading).
3. Implement Batch Processing (Avoid Full DataFrame)
- Sample script:
import pandas as pd
TABLE_NAME = "sample_table"
BATCH_SIZE = 10000
# Example: Replace this with your actual data source
def fetch_rows_in_batches(batch_size):
data = [
{"id": i, "value": f"row_{i}"}
for i in range(1, 50001)
]
for i in range(0, len(data), batch_size):
yield data[i:i + batch_size]
# Optional: watermark tracking
last_processed_id = 0
for rows in fetch_rows_in_batches(BATCH_SIZE):
df_batch = pd.DataFrame(rows)
if df_batch.empty:
continue
# Incremental filtering (example)
df_batch = df_batch[df_batch["id"] > last_processed_id]
# Deduplication safeguard
df_batch = df_batch.drop_duplicates(subset=["id"])
# Load batch into destination
pipeline.run(df_batch, table_name=TABLE_NAME)
# Update watermark
last_processed_id = df_batch["id"].max()
What This Script Demonstrates
| Feature | Why It Matters |
|---|---|
| Batch loop (fetch_data) | Prevents memory overload |
| Small DataFrames (df_batch) | Keeps memory usage stable |
| pipeline.run() per batch | Required by Bold BI |
| Deduplication (drop_duplicates) | Prevents duplicate inserts |
| Incremental filter | Avoids reprocessing old data |
The PythonScript connector requires data to be passed as a DataFrame. However, instead of loading all data at once, you should create and process small DataFrames in batches within the script.
4. Prevent Duplication (Critical Step)
- Choose one:
Option A: Upsert (Recommended)- Use a primary key to merge records
Option B: Incremental Load - Filter only new data using a watermark
- Use a primary key to merge records
WHERE updated_at > last_processed_value
Option C: Staging + Deduplication
- Load into staging table
- Deduplicate using SQL merge
-- Example (conceptual)
MERGE INTO final_table t
USING staging_table s
ON t.id = s.id
WHEN MATCHED THEN UPDATE
WHEN NOT MATCHED THEN INSERT;
5. Use pipeline.run() Per Batch Only
Important:
- The connector requires a DataFrame
- Do NOT combine all data into one DataFrame
Correct usage: Create → load → discard each batch
6. Upload and Configure Script
- Upload .py file using Upload File
- Update filePath in configuration
7. Run and Validate
- Click Run Now
- Check:
- Logs
- Record counts
- Performance
Troubleshooting Guide
| Issue | Symptoms | Possible Cause | Recommended Solution |
|---|---|---|---|
| Duplicate records in destination | Total row count is higher than distinct primary key count | Batch inserts reprocessing the same data | Implement upsert (merge) using a primary key or deduplicate after load |
| Duplicate _id / primary key values | total rows ≠ distinct rows for key column | Missing or improperly enforced primary key constraint | Ensure destination table has a unique/primary key constraint |
| Data duplication across batches | Same records appear in multiple batches | Overlapping pagination or incorrect batch logic | Fix batching logic to ensure non-overlapping data retrieval |
| Reprocessing old data | Previously loaded records are inserted again | No incremental (watermark) filtering | Use incremental queries (e.g., updated_at > last_processed_value) |
| Memory errors during execution | Script crashes or slows significantly | Loading entire dataset into a single DataFrame | Switch to chunked processing (small DataFrames per batch) |
| Inconsistent row counts | Destination count differs from source count | Partial batch failures or re-runs | Review logs and ensure idempotent loads (safe re-runs) |
| Pipeline runs slower over time | Increasing execution time | Growing dataset without batching or indexing | Optimize with batch size tuning and indexed columns |
| Missing or incomplete data | Some expected records are not loaded | Incorrect pagination or batch termination logic | Validate loop conditions and ensure all batches are processed |
| Repeated batch execution in logs | Same batch appears multiple times in logs | Loop logic error or retry mechanism duplication | Fix loop control logic and ensure state tracking is correct |
| Watermark not updating | Same data processed every run | Watermark value not saved or updated | Persist watermark in destination and update after each batch |
Best Practices
- Use batch processing for large datasets
- Always define a primary key
- Prefer incremental loading
- Validate data with distinct vs total counts
- Use staging tables + merge for reliability
- Monitor logs after each run
- Upgrade to the latest Bold BI build if issues persist
Conclusion
- Using the PythonScript connector with batching ensures efficient memory usage and scalable data ingestion. However, to avoid duplication, you must configure primary keys, batching logic, and incremental loading correctly.
Combining these practices results in a stable, production-ready ETL pipeline in Bold BI.
Related Articles
EO
