Handle data I/O reliably in production with error handling, monitoring, and cost optimization
This guide shows you how to read from and write to production data sources with Xorq using S3, GCS, and PostgreSQL. You’ll set up authentication, read and transform data, write results, handle errors, and monitor performance.
Set credentials as environment variables so Xorq can securely access your data sources. The connection reads these automatically when you connect to backends.
Connect to the default backend. The connection handles authentication using environment variables (AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY, etc.) configured earlier.
2
Create a deferred read expression for a CSV file in S3. The path parameter uses the s3:// protocol prefix. The con parameter specifies the backend connection. The table_name parameter helps identify the table in logs and debugging. This operation doesn’t execute until you call .execute().
3
Execute the deferred expression to load data from S3. The operation reads the CSV file, parses it, and returns a pandas DataFrame. The print statement shows how many rows were loaded.
Run the script:
python read_s3.py
You should see a result like this:
Loaded 10000 customers
Variations
Use these variations for different file formats and storage providers:
Parquet files
Parquet format is recommended for production workloads.
Read Parquet files using deferred_read_parquet() instead of deferred_read_csv(). Parquet format is recommended for production because it’s 50-80% smaller than CSV and reads 3-5x faster. The path uses the same s3:// protocol prefix.
Google Cloud Storage
To read from GCS instead of S3, use a gs:// path and ensure GOOGLE_APPLICATION_CREDENTIALS is set. Example:
Read from Google Cloud Storage by using the gs:// protocol prefix instead of s3://. Ensure GOOGLE_APPLICATION_CREDENTIALS is set to your service account key file path for authentication.
Public buckets without credentials
For public S3 buckets you can use the DuckDB backend without setting AWS credentials. Example:
con = xo.duckdb.connect()customers = xo.deferred_read_csv( path="s3://my-bucket/data/customers.csv", con=con, table_name="customers")
1
Connect to DuckDB backend, which can access public S3 buckets without credentials. Use this for public datasets that don’t require authentication.
2
Read from the public S3 bucket. DuckDB handles public bucket access automatically, so no credentials are needed.
If you see connection timeouts or file-not-found errors, then see Troubleshoot data I/O errors. Once data is loading from cloud storage, you can transform it before execution.
Transform data
Apply filters and aggregations to your data before executing. All transformations remain deferred until you call execute().
Connect to the default backend for data processing.
2
Create a deferred read expression for the CSV file. This doesn’t execute until you call .execute().
3
Filter rows where the status column equals “active”. The xo._ syntax provides column access for building predicates. All transformations remain deferred until execution.
4
Aggregate the filtered data to compute summary statistics. The agg() method calculates total_customers (count of customer IDs) and total_revenue (sum of revenue). Multiple aggregations are specified as keyword arguments.
5
Execute the entire pipeline: read → filter → aggregate. The operation processes all transformations in a single execution, returning the aggregated results as a pandas DataFrame.
The pipeline executes when you call execute(): read → filter → aggregate → return results. To persist those results back to cloud storage, write them as Parquet.
Write to cloud storage
Write processed data back to S3 or GCS in Parquet format for optimal storage and performance.
Connect to the default backend for data processing.
2
Create a deferred read expression for the source CSV file in S3.
3
Build a transformation pipeline: filter active customers and aggregate summary statistics. The pipeline remains deferred until execution.
4
Write the results to S3 in Parquet format. The to_parquet() method executes the pipeline, writes the data to the specified S3 path, and automatically handles authentication using the connection’s credentials. Parquet format is recommended for production workloads.
To avoid overwriting existing data, use timestamped paths:
from datetime import datetimetimestamp = datetime.now().strftime("%Y%m%d_%H%M%S")output_path =f"s3://my-bucket/output/summary-{timestamp}.parquet"active_summary.to_parquet(output_path)
1
Import datetime to generate timestamped file paths.
2
Create a timestamp string in YYYYMMDD_HHMMSS format and construct a unique output path. This prevents overwriting existing files when running the pipeline multiple times.
3
Write the results to the timestamped path. Each execution creates a new file with a unique name based on the current timestamp.
The same deferred execution model works with databases.
Read from PostgreSQL
Connect to PostgreSQL to query tables and execute SQL, using the same deferred execution model you’ve seen with cloud files.
Note
Before running PostgreSQL examples, ensure you have:
PostgreSQL server running and accessible
Database created with test tables (example: customers, orders)
# read_postgres.pyimport xorq.api as xopg_con = xo.postgres.connect_env()customers = pg_con.table("customers")active_customers = customers.filter(xo._.status =="active")df = active_customers.execute()print(f"Found {len(df)} active customers")
1
Connect to PostgreSQL using environment variables. The connect_env() method reads connection parameters from environment variables: POSTGRES_HOST, POSTGRES_PORT, POSTGRES_USER, POSTGRES_PASSWORD, POSTGRES_DB.
2
Access a PostgreSQL table using pg_con.table(). This creates a deferred expression that references the table. Apply filters using the same xo._ syntax as with cloud storage data. All operations remain deferred until execution.
3
Execute the query to fetch filtered data from PostgreSQL. The operation translates the Xorq expression into SQL, executes it on the database, and returns results as a pandas DataFrame.
For raw SQL queries, use pg_con.sql() so you can run arbitrary SQL and still get a deferred expression. Example:
recent_orders = pg_con.sql(""" SELECT customer_id, order_date, total FROM orders WHERE order_date > '2024-01-01'""")df = recent_orders.execute()
1
Execute raw SQL queries using pg_con.sql(). This method accepts any valid SQL string and returns a deferred expression. Use this for complex queries that are easier to write in SQL than with Xorq’s expression API.
2
Execute the SQL query to fetch results from PostgreSQL. The operation runs the SQL directly on the database and returns results as a pandas DataFrame. You can also create tables in PostgreSQL from Xorq expressions.
Write to PostgreSQL
Create PostgreSQL tables from Xorq expressions or register temporary tables for intermediate results.
Connect to PostgreSQL using environment variables for authentication.
2
Read data from S3 using a separate connection. You can combine data from different sources (cloud storage, databases) in the same pipeline.
3
Filter the data to keep only active customers. This transformation remains deferred until execution.
4
Create a PostgreSQL table from the Xorq expression. The create_table() method executes the expression, creates the table with the specified name, and writes the results. Set overwrite=True to replace existing tables, or overwrite=False (default); if the table already exists, then an error is raised.
Set overwrite=True to replace existing tables. If the table already exists and you use overwrite=False (default), then an error is raised.
For temporary tables that exist only for the current session:
pg_con.register("temp_table", data)
1
Register a temporary table in PostgreSQL. Temporary tables exist only for the current database session and are automatically dropped when the connection closes. Use this for intermediate results that don’t need to persist across sessions.
Production deployments need reliability. Network failures, timeouts, and transient errors are common in cloud I/O; add retry logic so read and write operations can recover gracefully.
Handle errors
Create retry_read.py:
# retry_read.pyimport timeimport xorq.api as xofrom pyarrow import ArrowIOErrordef read_with_retry(path, con=None, max_attempts=3, initial_backoff=2):if con isNone: con = xo.connect()for attempt inrange(max_attempts):try: data = xo.deferred_read_csv(path=path, con=con)return data.execute()except ArrowIOError as e:if attempt < max_attempts -1: wait_time = initial_backoff ** (attempt +1)print(f"Retry {attempt +1}/{max_attempts} in {wait_time}s...") time.sleep(wait_time)else:raiseRuntimeError(f"Failed after {max_attempts} attempts: {e}")con = xo.connect()df = read_with_retry("s3://my-bucket/data/customers.csv", con=con)
1
Define a retry function that handles transient network errors. The function accepts a file path, connection, maximum retry attempts, and initial backoff time for exponential backoff.
2
If none is provided, then create a connection. This allows the function to work with or without an explicit connection parameter.
3
Loop through retry attempts up to max_attempts. Each iteration attempts to read the file, catching and handling errors as needed.
4
Attempt to read and execute the CSV file. If successful, return the DataFrame immediately. If an ArrowIOError occurs (network timeout, connection reset), proceed to retry logic.
5
Calculate exponential backoff wait time and retry. The wait time increases exponentially: 2s, 4s, 8s for attempts 1, 2, 3. This gives the network time to recover from transient issues.
6
If all retry attempts fail, then raise a runtime error. This provides a clear error message indicating the operation failed after exhausting all retries.
7
Use the retry function to read data with automatic error handling. If network errors occur, then the function will retry up to 3 times with exponential backoff.
Retry network timeouts and connection resets. Don’t retry authentication failures or file-not-found errors. For files too large to load at once, stream in chunks instead.
Handle large files
Stream data in chunks for files larger than 1GB to keep memory usage constant regardless of file size.
Connect to the default backend for data processing.
2
Create a deferred read expression for a large Parquet file. The file isn’t loaded into memory yet.
3
Initialize a counter to track total rows processed across all batches.
4
Stream data in chunks using to_pyarrow_batches(). The chunk_size parameter controls how many rows are loaded per batch (100,000 in this example). Each batch is converted to a pandas DataFrame for processing. This keeps memory usage constant regardless of file size.
5
Print the total number of rows processed. Streaming allows you to process files larger than available memory by processing them in manageable chunks.
If you run out of memory, then reduce chunk_size to 10,000. To see where time and resources go, add monitoring around your I/O.
Monitor performance
Track I/O metrics to identify bottlenecks and optimize pipeline performance.
Configure logging to track I/O operations. Set the log level to INFO and format messages with timestamps for monitoring and debugging.
2
Define a monitoring function that wraps data reads with performance tracking. The function measures execution time, data size, and throughput.
3
If none is provided, then create a connection, allowing flexible usage with or without an explicit connection.
4
Record the start time before executing the read operation. This enables precise timing measurements.
5
Read and execute the CSV file. The operation fetches data from S3 and loads it into memory.
6
Calculate performance metrics: elapsed time, row count, and memory usage. Memory usage is calculated in megabytes by summing all column memory usage and converting bytes to MB.
7
Log performance metrics using the configured logger. The logs include row count, data size, execution time, and throughput (rows per second). These metrics help identify bottlenecks and optimize pipeline performance.
8
Log errors and re-raise exceptions. This ensures errors are tracked in logs while still propagating to calling code for proper error handling.
9
Use the monitoring function to read data with automatic performance tracking. All I/O operations are logged with detailed metrics for analysis. Before going live, consider performance, security, and cost.
Production considerations
Consider these factors when deploying to production:
Performance
Choose backends based on data size:
Data Size
Backend
Rationale
<1GB
Pandas
Simple, fast for small data
1-100GB
DuckDB
Optimized for analytics, efficient file handling
>100GB
PostgreSQL
Handles large datasets, concurrent operations
Use Parquet for production. It’s 50-80% smaller than CSV and reads 3-5x faster.
Security
Performance optimization helps you scale, but security prevents data breaches. Configure authentication and access controls properly before deploying to production.
Use IAM roles on EC2/ECS instead of access keys. If using access keys, then rotate credentials every 90 days and configure least-privilege permissions. Never commit credentials to code.
Monitoring
Security protects your data, but monitoring tells you when systems fail. Track I/O metrics to identify bottlenecks before they impact users.
Metric
What to Track
Read latency
Time to load and execute queries
Write latency
Time to write results to storage
Error rate
Percentage of failed operations
Memory usage
Peak memory during execution
Set alert thresholds based on your application’s service-level agreements (SLAs) and baseline performance. Log authentication failures, file-not-found errors, slow operations, and retry attempts.
Cost optimization
Monitoring shows you what’s happening, but cost optimization ensures you’re not overpaying for it. Storage and compute costs add up quickly at scale.
CSV on S3 Standard: ~$23/month
Parquet on S3 Standard: ~$5-10/month
Use Parquet for 50-80% storage savings. Cache frequently accessed data locally to reduce transfer costs. Keep compute and storage in the same region.
Scaling
Cost optimization reduces waste, but scaling ensures systems handle growing data volumes, such as planning capacity based on your data size and growth rate.
Backend selection for scale:
<1GB: Pandas backend works well for small datasets
1-100GB: DuckDB provides efficient analytics with lower memory overhead
>100GB: PostgreSQL handles large datasets with concurrent access
Performance varies by:
Hardware (CPU, memory, disk I/O)
Network latency between compute and storage
File format (Parquet is 3-5x faster than CSV)
Query complexity and data transformations
Capacity planning: Start with instance memory at least 2x your dataset size. Monitor actual usage and adjust based on your specific workload. When something goes wrong, use the following troubleshooting steps.
Troubleshooting
Here are the most common problems and their solutions.
Authentication fails
Problem: botocore.exceptions.NoCredentialsError: Unable to locate credentials
Cause: AWS credentials not configured or expired.
Solution: Verify environment variables are set:
echo$AWS_ACCESS_KEY_IDecho$AWS_SECRET_ACCESS_KEY
If on EC2, use IAM roles instead of access keys:
# Remove access keys from environmentunsetAWS_ACCESS_KEY_IDunsetAWS_SECRET_ACCESS_KEY# Attach IAM role to EC2 instance via AWS Console# Xorq will use instance metadata to get credentials
File not found
Problem: FileNotFoundError: [Errno 2] No such file or directory
Cause: Incorrect file path or missing file.
Solution: Verify the file exists, and the path is correct:
# List bucket contentsaws s3 ls s3://my-bucket/data/# Test file accessaws s3 cp s3://my-bucket/data/customers.csv -|head
Use absolute paths with bucket prefix:
# Correct: Full path with bucketpath ="s3://my-bucket/data/customers.csv"# Incorrect: Relative pathpath ="data/customers.csv"
1
Use absolute paths with the s3:// protocol prefix and bucket name. This ensures Xorq can locate and access the file in S3.
2
Avoid relative paths without the bucket prefix. Relative paths won’t work with cloud storage and will cause FileNotFoundError.
Out of memory
Problem: MemoryError when reading large files.
Cause: Loading the entire file into memory at once.
Solution: Use streaming for files >1GB:
# Stream data in chunksfor batch in data.to_pyarrow_batches(chunk_size=10000): process_batch(batch)
1
Stream large files in chunks to avoid loading the entire file into memory. The chunk_size parameter controls how many rows are processed per batch (10,000 in this example).
2
Process each batch separately. This keeps memory usage constant regardless of file size, allowing you to handle files larger than available RAM.
If still failing, then reduce chunk size to 1,000 or increase instance memory.
Slow read performance
Problem: Reads take longer than expected for your dataset size.
Cause: Network latency, wrong backend, or inefficient file format.
Import the time module to measure execution duration.
2
Measure the time taken to execute the data read operation. Record the start time before execution, then calculate elapsed time after completion.
3
Print performance metrics showing read time and row count. Use this to identify slow operations and optimize based on the results.
Optimize based on results:
Slow file reads: Switch from CSV to Parquet (3-5x faster)
High network latency: Move compute and storage to the same region
Large datasets: Use DuckDB backend for better memory efficiency
Files >1GB: Use streaming with to_pyarrow_batches()
Next steps
You now have working pipelines that read from production sources, transform data with Xorq, write results with error handling, and track performance metrics.