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

# Handle NULL values

> Replace NULLs with defaults using COALESCE and clean up missing data for downstream consumers.

## When to use this

Raw data almost always has gaps — optional fields left blank, API responses with missing keys, or records that were partially synced. NULLs can break aggregations, cause unexpected JOIN behavior, and confuse BI tools. Handling them explicitly ensures your trusted layer is clean and predictable.

***

## Sample input

A `contacts` table in the **Raw** layer with some missing values:

| contact\_id | name          | email                                         | company | phone       |
| ----------- | ------------- | --------------------------------------------- | ------- | ----------- |
| 1           | Alice Johnson | [alice@acme.com](mailto:alice@acme.com)       | Acme    | +1-555-0101 |
| 2           | Bob Smith     | NULL                                          | NULL    | +1-555-0102 |
| 3           | NULL          | [carol@initech.com](mailto:carol@initech.com) | Initech | NULL        |

We want to replace NULLs with sensible defaults: `"Unknown"` for text fields and `"N/A"` for phone.

***

## Implementation

<Tabs>
  <Tab title="Nekt Express / BigQuery">
    BigQuery supports the same `COALESCE` syntax. You can also use `IFNULL` as a shorthand when there are only two arguments.

    ```sql theme={null}
    SELECT
      contact_id,
      COALESCE(name, 'Unknown')     AS name,
      IFNULL(email, 'Unknown')      AS email,
      COALESCE(company, 'Unknown')  AS company,
      IFNULL(phone, 'N/A')          AS phone
    FROM `raw.contacts`
    ```

    <Tip>
      `IFNULL(a, b)` is equivalent to `COALESCE(a, b)` but only accepts two arguments. Use `COALESCE` when you have more than one fallback.
    </Tip>
  </Tab>

  <Tab title="Athena SQL">
    Use `COALESCE` to return the first non-NULL value from a list of arguments.

    ```sql theme={null}
    SELECT
      contact_id,
      COALESCE(name, 'Unknown')     AS name,
      COALESCE(email, 'Unknown')    AS email,
      COALESCE(company, 'Unknown')  AS company,
      COALESCE(phone, 'N/A')        AS phone
    FROM raw.contacts
    ```

    <Tip>
      You can chain multiple fallbacks: `COALESCE(preferred_email, work_email, personal_email, 'Unknown')` returns the first non-NULL value from left to right.
    </Tip>
  </Tab>

  <Tab title="Python (Nekt SDK)">
    In PySpark, use `fillna` to replace NULLs across multiple columns at once, or `coalesce` for column-level logic.

    ```python theme={null}
    import nekt

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

    clean_df = df.fillna({
        "name": "Unknown",
        "email": "Unknown",
        "company": "Unknown",
        "phone": "N/A"
    })

    nekt.save_table(
        df=clean_df,
        layer_name="Trusted",
        table_name="contacts_clean"
    )
    ```

    <Tip>
      For more complex logic (e.g., falling back to another column), use `F.coalesce`:

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

      df = df.withColumn(
          "email",
          F.coalesce(F.col("email"), F.col("secondary_email"), F.lit("Unknown"))
      )
      ```
    </Tip>
  </Tab>
</Tabs>

***

## Expected output

| contact\_id | name          | email                                         | company | phone       |
| ----------- | ------------- | --------------------------------------------- | ------- | ----------- |
| 1           | Alice Johnson | [alice@acme.com](mailto:alice@acme.com)       | Acme    | +1-555-0101 |
| 2           | Bob Smith     | Unknown                                       | Unknown | +1-555-0102 |
| 3           | Unknown       | [carol@initech.com](mailto:carol@initech.com) | Initech | N/A         |

***

## Tips and gotchas

<Warning>
  Be careful with **empty strings** vs **NULLs**. Some sources return `""` instead of NULL. `COALESCE` and `fillna` will **not** replace empty strings. To handle both:

  * **SQL**: `COALESCE(NULLIF(name, ''), 'Unknown')` — `NULLIF` turns `""` into NULL first.
  * **PySpark**: `F.when(F.col("name").isNull() | (F.col("name") == ""), "Unknown").otherwise(F.col("name"))`
</Warning>

<Note>
  Choose your default values carefully. Using `"Unknown"` or `"N/A"` is common, but in some cases `0` for numbers or a specific sentinel date (e.g., `1970-01-01`) may be more appropriate. Document your conventions so downstream consumers know what to expect.
</Note>