Adding Audit Columns to Existing Tables: Comparing Approaches for Large Datasets

Introduction

In data engineering, adding audit columns like bd_insert_dtm and bd_updated_dtm to track when records are created or modified is a common requirement. When dealing with large datasets (2-5GB files), choosing the right approach becomes critical for performance and resource utilization.

This post compares four different methods to implement this seemingly simple task, helping you choose the right tool for your specific needs.

The Challenge

We need to add audit timestamp columns to existing tables with file sizes ranging from 2GB to 5GB. Let's explore our options:

Approach 1: PySpark

PySpark leverages distributed computing, making it ideal for large datasets. While it might seem like overkill for 2-5GB files, it scales beautifully as your data grows.

from pyspark.sql import SparkSession

from pyspark.sql.functions import current_timestamp



# Initialize Spark session

spark = SparkSession.builder \

    .appName("Add Audit Columns") \

    .getOrCreate()



# Read your data

df = spark.read.format("csv").option("header", "true").load("your_file.csv")



# Add audit columns

df_with_audit = df.withColumn("bd_insert_dtm", current_timestamp()) \

                  .withColumn("bd_updated_dtm", current_timestamp())



# Write the result

df_with_audit.write.format("csv").mode("overwrite").option("header", "true").save("output_path")

Pros:

• Highly scalable to much larger datasets


• Parallelized processing


• Built-in functions for timestamps

Cons:

• Startup overhead


• Requires Spark environment


• More complex for simple tasks

Approach 2: Pandas

Pandas offers simplicity and ease of use, loading the entire dataset into memory:

import pandas as pd

from datetime import datetime



# Read your data

df = pd.read_csv("your_file.csv")



# Add audit columns

current_time = datetime.now()

df["bd_insert_dtm"] = current_time

df["bd_updated_dtm"] = current_time



# Write the result

df.to_csv("output_path.csv", index=False)

Pros:

• Simple and straightforward


• Familiar API for data scientists


• Great for quick iterations

Cons:

• Loads entire dataset into memory


• May struggle with 2GB+ files on machines with limited RAM


• Single-threaded operations

Approach 3: Dask

Dask combines the familiar Pandas API with out-of-core processing for larger-than-memory datasets:

import dask.dataframe as dd

from datetime import datetime



# Read your data

df = dd.read_csv("your_file.csv")



# Add audit columns

current_time = datetime.now()

df["bd_insert_dtm"] = current_time

df["bd_updated_dtm"] = current_time



# Write the result

df.to_csv("output_path/*.csv", index=False)

Pros:

• Pandas-like API


• Handles larger-than-memory datasets


• Parallel execution

Cons:

• More complex than pandas


• Output is split across multiple files by default


• Some operations require careful consideration

Approach 4: Using Generators

Generators provide the most memory-efficient solution by processing the file line by line:

import csv

from datetime import datetime



def add_audit_columns(input_file, output_file):

    with open(input_file, 'r') as infile, open(output_file, 'w', newline='') as outfile:

        reader = csv.reader(infile)

        writer = csv.writer(outfile)



        # Handle header

        header = next(reader)

        header.extend(["bd_insert_dtm", "bd_updated_dtm"])

        writer.writerow(header)



        # Process rows

        current_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")

        for row in reader:

            row.extend([current_time, current_time])

            writer.writerow(row)



# Usage

add_audit_columns("your_file.csv", "output_file.csv")

Pros:

• Minimal memory footprint


• Works on any machine


• Simple to understand

Cons:

• Sequential processing


• Limited functionality compared to dataframe libraries


• Manual handling of data types

Comparison and Recommendations

For 2-5GB files:

• With sufficient RAM: Pandas offers the simplest solution


• With limited RAM: Dask or generators are better choices


• With an existing Spark environment: PySpark makes sense


• For absolute memory efficiency: Go with generators

Conclusion

When adding audit columns to existing tables, the best approach depends on your specific constraints. For most cases with 2-5GB files, Dask provides an excellent balance between ease of use and performance. However, generators shine when working in extremely memory-constrained environments, while PySpark is the go-to solution if you anticipate scaling to much larger datasets in the future.

What approach are you using for adding audit columns to your tables? Let me know in the comments!