Data Quality with Pandas

A data anomaly is a record or group of records that violates an expected data rule. An anomaly is not automatically an application failure. It is a signal that the data needs investigation, correction, quarantine, or a documented exception.

This article uses Pandas boolean masks to detect abnormal records, separate them from valid data, and log the anomalous rows for later investigation.

What is a data anomaly?#

An anomaly is a value or relationship that does not satisfy an expected rule. Examples include:

• amount < 0
• quantity <= 0
• a status outside the known set of values
• a missing created_at value
• payment_date earlier than order_date
• a value outside an expected range
• a duplicate record
• an impossible relationship between fields

Not every anomaly is necessarily an error. A valid business exception and a bad source record can look similar until someone investigates the context. The useful first step is to preserve the evidence and label the rule that was violated.

Data quality vs. application errors#

Application logging records what a program did or what happened while it ran:

• task started
• query failed
• connection timeout
• 500 rows processed

Data anomaly logging records problematic data itself:

• negative amount
• invalid status
• unexpected range
• missing required value
• duplicate record
• impossible relationship between fields

The two types of logging complement each other. An application log can show that a validation task completed, while an anomaly log shows which records failed validation and why.

Detecting anomalies with boolean masks#

The expression df["amount"] < 0 returns a boolean Series with one value per row:

negative_amount_mask = df["amount"] < 0
anomaly_df = df[negative_amount_mask]

The mask contains True for rows with a negative amount and False for the other rows. df[mask] keeps only rows where the mask is True.

For multiple conditions, use | for OR and & for AND:

anomaly_mask = (    (df["amount"] < 0)    | (df["quantity"] <= 0))
anomaly_df = df[anomaly_mask]

Use parentheses around each comparison. The operators are:

• & — AND
• | — OR
• ~ — NOT

Pandas combines boolean Series element by element. Python's normal and and or operators are not the right tools for combining whole Series.

Splitting clean and anomalous data#

One mask can both isolate problematic rows and remove them from the downstream dataset:

anomaly_mask = df["amount"] < 0
anomaly_df = df[anomaly_mask]valid_df = df[~anomaly_mask]

The mental model is:

Validation rule      ↓    mask   /    \True    False  ↓        ↓anomaly   valid data

Do not automatically delete the True rows. Log or quarantine them first when the records may be needed for investigation or correction.

Logging a DataFrame to CSV#

For local debugging or a small audit trail, write the anomaly DataFrame to a CSV file:

anomaly_df.to_csv(    "anomalies.csv",    index=False,)

index=False prevents the Pandas index from becoming an extra CSV column.

To append later anomalies instead of overwriting the file:

anomaly_df.to_csv(    "anomalies.csv",    mode="a",    index=False,    header=False,)

Here, mode="a" appends to the file and header=False avoids writing column names on every append. Using header=False blindly is safe only when the file already has a compatible header or the logging strategy intentionally omits headers.

A safer pattern writes the header only when the file does not exist:

from pathlib import Path

file_path = Path("anomalies.csv")
anomaly_df.to_csv(    file_path,    mode="a",    index=False,    header=not file_path.exists(),)

This keeps the first write self-describing and later writes append-only.

A reusable anomaly logger#

The CSV logic is easier to reuse when it lives in a small function:

from pathlib import Pathimport pandas as pd

def log_anomalies_to_csv(    anomaly_df: pd.DataFrame,    condition: str,    directory: str = "anomalies",) -> None:    if anomaly_df.empty:        print(f"No anomalies found: {condition}")        return
    directory_path = Path(directory)    directory_path.mkdir(        parents=True,        exist_ok=True,    )
    file_path = (        directory_path        / f"anomaly_{condition}.csv"    )
    file_exists = file_path.exists()
    anomaly_df.to_csv(        file_path,        mode="a",        index=False,        header=not file_exists,    )

The function has one small responsibility: append a non-empty anomaly DataFrame to a deterministic file and write the header only on the first write.

Line by line:

• The empty check avoids creating empty files and makes no-anomaly runs explicit.
• Path(directory) represents the destination directory.
• mkdir(..., exist_ok=True) creates missing parent directories safely.
• The condition becomes part of a deterministic file name.
• mode="a" preserves earlier anomaly records.
• header=not file_exists writes column names exactly once per file.

Using the logger#

mask = df["amount"] < 0
anomaly_df = df[mask]
log_anomalies_to_csv(    anomaly_df,    "negative_amount",)
df = df[~mask]

The mask is computed once, then reused to log anomalies and continue with valid records.

Multiple anomaly rules#

Validation rules can be represented as data in a dictionary:

rules = {    "negative_amount": df["amount"] < 0,    "invalid_quantity": df["quantity"] <= 0,    "missing_customer": df["customer_id"].isna(),}

Each rule can be logged independently:

for name, mask in rules.items():    anomalies = df[mask]
    log_anomalies_to_csv(        anomalies,        name,    )

This keeps validation rules declarative instead of repeating the same logging block for every condition.

If invalid records must be removed from the clean DataFrame, combine the masks intentionally:

combined_anomaly_mask = (    rules["negative_amount"]    | rules["invalid_quantity"]    | rules["missing_customer"])
clean_df = df[~combined_anomaly_mask]

Logging each rule separately gives more detail. The combined mask defines the records that should not continue to the next stage.

Detecting anomalies with SQL#

Some anomalies are easier to find inside ClickHouse before data reaches Pandas:

anomaly_df = client.query_df("""    SELECT *    FROM staging.orders    WHERE amount < 0""")
log_anomalies_to_csv(    anomaly_df,    "negative_amount",)

Pandas validation is useful when data is already loaded into Python or when a rule depends on Python transformations. SQL validation is useful when the condition can be filtered efficiently inside the database.

Neither approach is universally better. Choose the execution location based on where the data already is, how much data must move, and what the rule needs.

Adding metadata to anomaly logs#

Anomaly files become more useful when each row includes context, not only a condition in the filename:

from datetime import datetime, timezone

anomaly_df = anomaly_df.copy()
anomaly_df["anomaly_type"] = "negative_amount"
anomaly_df["detected_at"] = (    datetime.now(timezone.utc))

Useful metadata may include:

• anomaly_type
• detected_at
• pipeline_run_id
• source_table
• source_layer
• validation_rule

This context remains attached to the record if the file is moved, merged, or loaded into a database later. It is more reliable than depending only on a filename or a surrounding application log.

Avoiding SettingWithCopy problems#

When adding metadata to a filtered DataFrame, create an explicit copy:

anomaly_df = df[mask].copy()
anomaly_df["anomaly_type"] = "negative_amount"

The .copy() makes it clear that anomaly_df is an independent DataFrame. Without it, Pandas may be working with a view of the original data, which can produce a SettingWithCopyWarning or make the mutation behavior unclear.

Anomaly counts and rates#

Because True behaves like 1 and False behaves like 0, summing a boolean mask counts its True values:

anomaly_count = mask.sum()
print(    f"Detected {anomaly_count} anomalous records")

The mean of a boolean mask gives the share of anomalous rows:

anomaly_rate = mask.mean()anomaly_rate_percent = mask.mean() * 100

This is useful for data quality monitoring. A count shows the absolute impact; the rate makes the result comparable across runs with different row counts.

Handling an empty DataFrame#

Avoid writing empty anomaly files when there is nothing to investigate:

if anomaly_df.empty:    return

This is useful in scheduled transformation jobs. A no-anomaly run should be a normal result, not an empty artifact that looks like a failure or a new log file.

CSV vs. database anomaly storage#

CSV is useful for:

• local debugging
• simple audit trails
• small projects
• quick inspection

Production systems may instead store anomalies in a ClickHouse table, PostgreSQL, object storage, or a monitoring system. A conceptual anomaly table might contain:

anomaly_typedetected_atsource_tablerecord_iddetails

A database-backed registry makes it easier to query history, compare rates, assign investigation status, and connect anomalies to pipeline runs. The right storage choice depends on expected volume, retention needs, and how the team investigates records.

Using anomaly logging in a pipeline#

The basic sequence is:

df = client.query_df(sql)
if df.empty:    return
negative_mask = df["amount"] < 0
negative_df = df[negative_mask].copy()
negative_df["anomaly_type"] = (    "negative_amount")
log_anomalies_to_csv(    negative_df,    "negative_amount",)
df = df[~negative_mask]
client.insert_df(    "staging.orders",    column_names=columns,    df=df[columns],)

Read it as:

Read  → Validate  → Isolate anomaly  → Log anomaly  → Remove or quarantine anomaly  → Continue with valid data

The pipeline should define what happens when an anomaly is found. Logging it without changing downstream input may allow the same invalid row to continue into a table that assumes clean data.

Quarantine instead of deletion#

Instead of simply deleting invalid records, data systems often move or copy suspicious records into a quarantine dataset.

Raw Data   ↓Validation   ├──────────────→ Valid → Staging   ↓Invalid   ↓Quarantine / Anomaly Log

Quarantine preserves traceability. A record can be inspected, corrected, and reprocessed later without pretending that it was valid on the first pass.

Airflow#

An anomaly-logging transformation can later run as a task in an orchestration system such as Airflow:

Airflow task      ↓Pandas validation      ↓Anomaly log      ↓ClickHouse Staging

Airflow coordinates when the validation runs and what it depends on. Pandas contains the validation and logging logic. Airflow and DAG design belong in the Orchestration section.

Common mistakes#

Detecting an anomaly but continuing to process it#

Logging a bad row does not remove it from the DataFrame. Decide whether to filter, reject, or quarantine it before downstream insertion.

Deleting anomalous data without preserving evidence#

Deleting the only copy makes investigation and recovery impossible. Preserve the record or its relevant details before removal when the data may matter.

Overwriting the anomaly log on every run#

Writing without append mode removes history. Use an append strategy or a database-backed log with an explicit retention policy.

Writing CSV headers repeatedly#

Repeated headers become data rows when files are appended. Write the header only when creating a new file.

Logging without an anomaly type#

Rows without a rule name are difficult to investigate. Store an explicit anomaly_type or validation_rule.

Modifying df[mask] without .copy()#

Filtered DataFrames may be views. Use .copy() before adding metadata or changing values in the subset.

Mixing application exceptions and data anomalies#

A connection timeout and a negative amount are different events. Keep execution failures in application logs and problematic records in anomaly logs.

Using only print() as anomaly history#

A count in stdout is useful for immediate feedback, but it does not preserve the records needed for investigation.

Omitting timestamp or run metadata#

Without context, the same record may be impossible to associate with a source or pipeline run. Add metadata appropriate to the investigation workflow.

Creating duplicate anomaly records after retries#

Retries can append the same anomaly more than once. Define a stable record key, run identifier, or deduplication strategy when anomaly history must be unique.

Loading a huge dataset into Pandas for a simple SQL condition#

If a simple filter can run efficiently in ClickHouse, applying it there may avoid unnecessary data transfer and memory use. Keep the rule close to the data when that is the clearer and safer choice.

Quick reference#

Data anomaly logging turns a failed validation rule into an inspectable record: preserve the evidence, identify the rule, and make the downstream decision explicit.

See also#