Course contentsShow
← Python
Lesson 846 of 1,06013. Data Integration and Web APIsFree lesson

Enforcing Data Integrity During Transformation

Apply validation logic and type casting to ensure incoming API data adheres to strict database constraints before persistence.

In this lesson, you will learn how to enforce strict data integrity by validating and casting incoming API data before it reaches your database.

When consuming data from a Web API, you cannot assume the incoming JSON matches your database schema. Relying on "raw" data leads to IntegrityError exceptions, malformed records, or corrupted data. To maintain a robust persistent data access layer, you must implement a "validation gate" during your transformation phase.

The Transformation Gate

Think of your ETL pipeline as having a filter between the extraction step and the persistence step. As you map API fields to your Python objects (ORM models), you must perform two critical tasks: Type Casting and Constraint Validation.

Type casting ensures that even if an API sends a number as a string (e.g., "105.50" instead of 105.50), your system forces it into the correct database type before the SQL statement is prepared. Validation logic checks if the data violates business rules—such as ensuring a price isn't negative or a user_email follows a required format—before you even attempt a transaction.

Consider this transformation pattern:

def transform_user_data(raw_api_data):
    # 1. Type Casting
    try:
        user_id = int(raw_api_data.get("id"))
        price = float(raw_api_data.get("price"))
    except (TypeError, ValueError):
        raise ValueError("Invalid data format received from API")

    # 2. Integrity Validation
    if price < 0:
        raise ValueError("Price cannot be negative; integrity check failed.")

    return {"id": user_id, "price": price}

# Usage in your persistence layer
clean_data = transform_user_data(api_response)
db.execute("INSERT INTO products (id, price) VALUES (?, ?)", 
           (clean_data['id'], clean_data['price']))

By intercepting the data here, you avoid database-level IntegrityError exceptions that can trigger rollbacks or complicate your transaction logic. Always perform these checks after extraction but before calling your persistence layer. This ensures that only high-quality, schema-compliant data ever reaches your SQL commands, keeping your database clean and your application state predictable.

Key Takeaway: Enforce data integrity during the transformation phase by explicitly casting types and applying validation logic to catch anomalies before they reach your database.