> ## Documentation Index
> Fetch the complete documentation index at: https://docs.nekt.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Filter invalid rows

> Remove or flag rows that fail validation rules before they reach your trusted layer.

## When to use this

Raw data often contains rows that shouldn't make it into your analytics — test records, incomplete entries, or data that violates business rules. Rather than letting bad data silently corrupt dashboards and reports, filter it out (or flag it) during the transformation step.

Common scenarios include:

* Removing test or sandbox records (e.g., emails ending in `@test.com`)
* Excluding rows with missing required fields
* Filtering out records outside a valid date range
* Flagging anomalous values for review

***

## Sample input

An `orders` table in the **Raw** layer with some invalid records:

| order\_id | customer\_email                               | amount  | order\_date         | status    |
| --------- | --------------------------------------------- | ------- | ------------------- | --------- |
| 1001      | [alice@acme.com](mailto:alice@acme.com)       | 249.99  | 2024-03-15 10:30:00 | completed |
| 1002      | [test@test.com](mailto:test@test.com)         | 0.01    | 2024-03-16 14:22:00 | completed |
| 1003      | [bob@globex.com](mailto:bob@globex.com)       | -50.00  | 2024-03-17 09:15:00 | completed |
| 1004      | NULL                                          | 1200.00 | 2024-03-18 11:00:00 | pending   |
| 1005      | [carol@initech.com](mailto:carol@initech.com) | 89.50   | 2024-03-19 16:45:00 | completed |

We want to keep only rows where: the email is not a test address, the amount is positive, and the email is not NULL.

***

## Implementation

<Tabs>
  <Tab title="Nekt Express / BigQuery">
    Apply the same `WHERE` filters. BigQuery's `REGEXP_CONTAINS` can be useful for more complex patterns.

    ```sql theme={null}
    SELECT
      order_id,
      customer_email,
      amount,
      order_date,
      status
    FROM `raw.orders`
    WHERE customer_email IS NOT NULL
      AND customer_email NOT LIKE '%@test.com'
      AND amount > 0
    ```

    <Tip>
      For more complex email validation, use regex:

      ```sql theme={null}
      WHERE REGEXP_CONTAINS(customer_email, r'^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$')
      ```
    </Tip>
  </Tab>

  <Tab title="Athena SQL">
    Use `WHERE` clauses to filter out invalid rows based on your business rules.

    ```sql theme={null}
    SELECT
      order_id,
      customer_email,
      amount,
      order_date,
      status
    FROM raw.orders
    WHERE customer_email IS NOT NULL
      AND customer_email NOT LIKE '%@test.com'
      AND amount > 0
    ```

    <Tip>
      To keep a record of what was filtered out (useful for debugging), create a separate "rejected rows" table:

      ```sql theme={null}
      SELECT *, 'invalid_email' AS rejection_reason
      FROM raw.orders
      WHERE customer_email IS NULL OR customer_email LIKE '%@test.com'

      UNION ALL

      SELECT *, 'negative_amount' AS rejection_reason
      FROM raw.orders
      WHERE amount <= 0
      ```
    </Tip>
  </Tab>

  <Tab title="Python (Nekt SDK)">
    In PySpark, chain `filter` conditions to remove invalid rows.

    ```python theme={null}
    import nekt
    from pyspark.sql import functions as F

    df = nekt.load_table(layer_name="Raw", table_name="orders")

    valid_df = (
        df
        .filter(F.col("customer_email").isNotNull())
        .filter(~F.col("customer_email").like("%@test.com"))
        .filter(F.col("amount") > 0)
    )

    nekt.save_table(
        df=valid_df,
        layer_name="Trusted",
        table_name="orders_valid"
    )
    ```

    <Tip>
      To flag instead of remove, add a validation column so downstream consumers can decide:

      ```python theme={null}
      flagged_df = df.withColumn(
          "is_valid",
          (F.col("customer_email").isNotNull())
          & (~F.col("customer_email").like("%@test.com"))
          & (F.col("amount") > 0)
      )
      ```
    </Tip>
  </Tab>
</Tabs>

***

## Expected output

| order\_id | customer\_email                               | amount | order\_date         | status    |
| --------- | --------------------------------------------- | ------ | ------------------- | --------- |
| 1001      | [alice@acme.com](mailto:alice@acme.com)       | 249.99 | 2024-03-15 10:30:00 | completed |
| 1005      | [carol@initech.com](mailto:carol@initech.com) | 89.50  | 2024-03-19 16:45:00 | completed |

Rows 1002 (test email), 1003 (negative amount), and 1004 (NULL email) are excluded.

***

## Tips and gotchas

<Warning>
  Filtering is destructive — once rows are removed, they won't appear in downstream tables. If you're unsure whether certain records should be excluded, consider **flagging** them with a boolean column (`is_valid`) instead of removing them. This lets analysts make the final call.
</Warning>

<Note>
  Keep your validation rules documented and versioned. When business rules change (e.g., a new test domain gets added), you'll want to update the filter in one place rather than hunting for hardcoded values across multiple transformations.
</Note>