Read and write production data

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.

Prerequisites

Configure authentication

Set credentials as environment variables so Xorq can securely access your data sources. The connection reads these automatically when you connect to backends.

export AWS_ACCESS_KEY_ID=your_access_key
export AWS_SECRET_ACCESS_KEY=your_secret_key
export AWS_DEFAULT_REGION=us-east-1
export GOOGLE_APPLICATION_CREDENTIALS=/path/to/service-account-key.json
export POSTGRES_HOST=your-db-host.com
export POSTGRES_PORT=5432
export POSTGRES_USER=your_username
export POSTGRES_PASSWORD=your_password
export POSTGRES_DB=your_database

For production deployments: Use IAM roles on EC2/ECS instead of access keys. The instance retrieves credentials automatically from metadata.

If you encounter authentication errors, then see Troubleshoot authentication.

Read from cloud storage

Connect to a backend and use deferred reading to load data from S3 or GCS. The operation doesn’t execute until you call execute().

Note

Before running these examples, ensure you have:

  • An S3 or GCS bucket created
  • Test data files uploaded to your bucket (example: customers.csv)
  • Replace my-bucket in the examples with your actual bucket name
  • Network access to AWS/GCS services configured

Create a file called read_s3.py:

# read_s3.py
import xorq.api as xo


con = xo.connect()


customers = xo.deferred_read_csv(
    path="s3://my-bucket/data/customers.csv",
    con=con,
    table_name="customers"
)


df = customers.execute()
print(f"Loaded {len(df)} customers")
1
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.


customers = xo.deferred_read_parquet(
    path="s3://my-bucket/data/customers.parquet",
    con=con,
    table_name="customers"
)
1
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:


customers = xo.deferred_read_parquet(
    path="gs://my-bucket/data/customers.parquet",
    con=con,
    table_name="customers"
)
1
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().

Create transform_data.py:

# transform_data.py
import xorq.api as xo


con = xo.connect()


customers = xo.deferred_read_csv(
    path="s3://my-bucket/data/customers.csv",
    con=con,
    table_name="customers"
)


active_customers = customers.filter(xo._.status == "active")


summary = active_customers.agg(
    total_customers=xo._.customer_id.count(),
    total_revenue=xo._.revenue.sum()
)


df = summary.execute()
print(df)
1
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.

Create write_s3.py:

# write_s3.py
import xorq.api as xo


con = xo.connect()


customers = xo.deferred_read_csv(
    path="s3://my-bucket/data/customers.csv",
    con=con,
    table_name="customers"
)


active_summary = customers.filter(
    xo._.status == "active"
).agg(
    total_customers=xo._.customer_id.count(),
    total_revenue=xo._.revenue.sum()
)


active_summary.to_parquet("s3://my-bucket/output/customer-summary.parquet")
1
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 datetime


timestamp = 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)
  • Environment variables configured (POSTGRES_HOST, POSTGRES_PORT, POSTGRES_USER, POSTGRES_PASSWORD, POSTGRES_DB)

Create read_postgres.py:

# read_postgres.py
import xorq.api as xo


pg_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.

Create write_postgres.py:

# write_postgres.py
import xorq.api as xo


pg_con = xo.postgres.connect_env()


customers = xo.deferred_read_csv(
    path="s3://my-bucket/data/customers.csv",
    con=xo.connect(),
    table_name="customers"
)


active_customers = customers.filter(xo._.status == "active")


pg_con.create_table("active_customers", active_customers, overwrite=True)
1
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.py
import time
import xorq.api as xo
from pyarrow import ArrowIOError


def read_with_retry(path, con=None, max_attempts=3, initial_backoff=2):

    if con is None:
        con = xo.connect()
    

    for attempt in range(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:

                raise RuntimeError(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.

Create stream_large.py:

# stream_large.py
import xorq.api as xo


con = xo.connect()


data = xo.deferred_read_parquet(
    path="s3://my-bucket/large-file.parquet",
    con=con
)


total_rows = 0
for batch in data.to_pyarrow_batches(chunk_size=100000):

    df = batch.to_pandas()
    total_rows += len(df)
    print(f"Processed batch: {len(df)} rows")


print(f"Total: {total_rows} rows")
1
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.

Create monitor_io.py:

# monitor_io.py
import time
import logging
import xorq.api as xo


logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(message)s')
logger = logging.getLogger(__name__)


def monitored_read(path, con=None):

    if con is None:
        con = xo.connect()
    

    start = time.time()
    
    try:

        data = xo.deferred_read_csv(path=path, con=con)
        df = data.execute()
        

        elapsed = time.time() - start
        rows = len(df)
        mb = df.memory_usage(deep=True).sum() / 1024 / 1024
        

        logger.info(f"Read {rows:,} rows ({mb:.2f} MB) in {elapsed:.2f}s")
        logger.info(f"Throughput: {rows / elapsed:,.0f} rows/sec")
        return df
    except Exception as e:

        logger.error(f"Read failed: {e}")
        raise


df = monitored_read("s3://my-bucket/data/customers.csv")
1
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_ID
echo $AWS_SECRET_ACCESS_KEY

If on EC2, use IAM roles instead of access keys:

# Remove access keys from environment
unset AWS_ACCESS_KEY_ID
unset AWS_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 contents
aws s3 ls s3://my-bucket/data/

# Test file access
aws s3 cp s3://my-bucket/data/customers.csv - | head

Use absolute paths with bucket prefix:


# Correct: Full path with bucket
path = "s3://my-bucket/data/customers.csv"


# Incorrect: Relative path
path = "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 chunks
for 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.

Solution: Profile and optimize:


import time


start = time.time()
df = data.execute()
read_time = time.time() - start


print(f"Read time: {read_time:.2f}s for {len(df)} rows")
1
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.