# Transform on Load — Computed Columns, Ignore and Foreign Keys in Starlake

> Add computed columns, ignore sensitive fields (GDPR) and define foreign keys during data ingestion in Starlake. Use Spark SQL functions and file metadata to enrich records.

<script type="application/ld+json">
    {JSON.stringify({
      "@context": "https://schema.org",
      "@type": "FAQPage",
      "mainEntity": [
        {
          "@type": "Question",
          "name": "How do I add a computed column during load in Starlake?",
          "acceptedAnswer": {
            "@type": "Answer",
            "text": "Add an attribute with a script property in the table YAML file. The script accepts any Spark SQL function and can reference other columns in the same record."
          }
        },
        {
          "@type": "Question",
          "name": "What functions are available in the script property?",
          "acceptedAnswer": {
            "@type": "Answer",
            "text": "All Spark SQL functions are available, as well as the hidden _metadata column properties: file_name, file_path, file_size and file_modification_time."
          }
        },
        {
          "@type": "Question",
          "name": "How do I add the source file name as a column?",
          "acceptedAnswer": {
            "@type": "Answer",
            "text": "Define an attribute with script: \"_metadata.file_name\". The name of the file being loaded will be added as the value of that column."
          }
        },
        {
          "@type": "Question",
          "name": "Can I ignore columns during load?",
          "acceptedAnswer": {
            "@type": "Answer",
            "text": "Yes. Set ignore: true on an attribute. The column will not be loaded into the target table but remains usable in other columns' scripts."
          }
        },
        {
          "@type": "Question",
          "name": "Can the ignore attribute help with GDPR compliance?",
          "acceptedAnswer": {
            "@type": "Answer",
            "text": "Yes. Ignoring an attribute containing sensitive data prevents it from being loaded into the target table. The ignored column can still be used in a script for intermediate computation."
          }
        },
        {
          "@type": "Question",
          "name": "How do I define a foreign key on a column?",
          "acceptedAnswer": {
            "@type": "Answer",
            "text": "Use the foreignKey property on the attribute. Supported syntaxes: \"
            \", \"<domain>.
            \", \"<domain>.
            .<column>\", or \"
            .<column>\"."
          }
        },
        {
          "@type": "Question",
          "name": "Are foreign keys enforced on all warehouses?",
          "acceptedAnswer": {
            "@type": "Answer",
            "text": "No. Some data warehouses do not support foreign keys. In that case, the constraint is ignored at runtime but still appears in the auto-generated entity-relational diagram."
          }
        },
        {
          "@type": "Question",
          "name": "What file metadata properties are available during load?",
          "acceptedAnswer": {
            "@type": "Answer",
            "text": "Four properties: file_name (name), file_path (path), file_size (size in bytes), and file_modification_time (last modification date)."
          }
        }
      ]
    })}
  </script>
  <script type="application/ld+json">
    {JSON.stringify({
      "@context": "https://schema.org",
      "@type": "HowTo",
      "name": "How to add computed columns on load in Starlake",
      "description": "Enrich records during ingestion by adding computed columns using Spark SQL functions in the table YAML definition.",
      "step": [
        {
          "@type": "HowToStep",
          "name": "Open the table YAML file",
          "text": "Edit metadata/load/<domain>/
          .sl.yml."
        },
        {
          "@type": "HowToStep",
          "name": "Add an attribute with a script property",
          "text": "The script accepts any Spark SQL expression and can reference other columns or _metadata properties (file_name, file_path, file_size, file_modification_time)."
        },
        {
          "@type": "HowToStep",
          "name": "Optionally ignore source columns",
          "text": "Set ignore: true on any attribute you want to exclude from the target table while still using it in scripts."
        },
        {
          "@type": "HowToStep",
          "name": "Optionally declare foreign keys",
          "text": "Add foreignKey: \"
          .<column>\" on an attribute to create a relationship."
        },
        {
          "@type": "HowToStep",
          "name": "Run the load",
          "text": "Execute starlake load to ingest files with the computed columns applied."
        }
      ]
    })}
  </script>

# Transform on Load

Starlake enriches records during ingestion through three features declared in the table YAML:

- **Computed columns** -- Add new columns using any Spark SQL expression.
- **Ignore attribute** -- Exclude sensitive or unnecessary columns from the target table (useful for GDPR compliance).
- **Foreign keys** -- Declare relationships between tables for documentation and ER diagram generation.

All transformations happen at load time, before data reaches the target table.

## How to add computed columns

1. **Open the table YAML file** -- Edit `metadata/load/<domain>/<table>.sl.yml`.
2. **Add an attribute with a `script` property** -- The script accepts any Spark SQL expression and can reference other columns or `_metadata` properties.
3. **Optionally ignore source columns** -- Set `ignore: true` on any attribute you want to exclude from the target table while still using it in scripts.
4. **Optionally declare foreign keys** -- Add `foreignKey: "<table>.<column>"` on an attribute to create a relationship.
5. **Run the load** -- Execute `starlake load` to ingest files with the computed columns applied.

## Computed columns with the `script` property

Add extra attributes that do not exist in the source file. The `script` property accepts any [Spark SQL function](https://spark.apache.org/docs/latest/api/sql/index.html#hex) and can reference any column in the same record, including ignored columns.

### File metadata properties

In addition to Spark SQL functions, you can reference properties from the hidden `_metadata` column:

| Property | Type | Description |
| --- | --- | --- |
| `file_name` | string | Name of the file being loaded |
| `file_path` | string | Path of the file being loaded |
| `file_size` | long | Size of the file in bytes |
| `file_modification_time` | timestamp | Last modified date of the file |

### Example: computed columns and file metadata

```yaml title="metadata/load/<domain>/<table>.sl.yml" {8,10,12}
table:
  pattern: "order_line.*.csv"
  ...
  attributes:
  ...
  - name: "total_price"
    script: "quantity * sale_price"
  - name: "quantity_in_hexadecimal"
    script: "hex(quantity)"
  - name: "file_name"
    script: "_metadata.file_name"

```

In this example:
- `total_price` multiplies two source columns.
- `quantity_in_hexadecimal` applies the `hex()` Spark SQL function.
- `file_name` captures the name of the ingested file.

## Ignore columns

Set `ignore: true` on an attribute to exclude it from the target table. The column is still available in other attributes' `script` expressions. This supports GDPR compliance by preventing sensitive data from reaching the data warehouse. See also [column-level security](/starflow/guides/security) for access control on BigQuery.

:::tip
You may ignore a column and still use it inside the `script` attribute of another column.
:::

```yaml title="metadata/load/<domain>/<table>.sl.yml" {8,10,12}
table:
  pattern: "order_line.*.csv"
  ...
  attributes:
  ...
  - name: "product_id"
    type: "int"
    ignore: true
```

## Foreign key declarations

Declare foreign keys on attributes using the `foreignKey` property. Starlake sets the constraint on warehouses that support it and generates an entity-relational diagram in all cases.

```yaml title="metadata/load/<domain>/<table>.sl.yml" {8}
table:
  pattern: "order_line.*.csv"
  ...
  attributes:
  ...
  - name: "product_id"
    type: "int"
    foreignKey: "product.id"

```

### Supported `foreignKey` syntax

| Syntax | Description |
| --- | --- |
| `foreignKey: "<table>"` | Foreign key on the primary key of the specified table in the same domain. |
| `foreignKey: "<domain>.<table>"` | Foreign key on the primary key of the specified table in the specified domain. |
| `foreignKey: "<domain>.<table>.<column>"` | Foreign key on a specific column in the specified table and domain. |
| `foreignKey: "<table>.<column>"` | Foreign key on a specific column in the specified table in the same domain. |

:::note
Some data warehouses do not support foreign keys. The constraint is ignored at runtime but still appears in the auto-generated entity-relational diagram.
:::

## Frequently Asked Questions

### How do I add a computed column during load in Starlake?
Add an attribute with a `script` property in the table YAML file. The script accepts any Spark SQL function and can reference other columns in the same record.

### What functions are available in the script property?
All Spark SQL functions are available, as well as the hidden `_metadata` column properties: `file_name`, `file_path`, `file_size` and `file_modification_time`.

### How do I add the source file name as a column?
Define an attribute with `script: "_metadata.file_name"`. The name of the file being loaded will be added as the value of that column.

### Can I ignore columns during load?
Yes. Set `ignore: true` on an attribute. The column will not be loaded into the target table but remains usable in other columns' scripts.

### Can the ignore attribute help with GDPR compliance?
Yes. Ignoring an attribute containing sensitive data prevents it from being loaded into the target table. The ignored column can still be used in a script for intermediate computation.

### How do I define a foreign key on a column?
Use the `foreignKey` property on the attribute. Supported syntaxes: `"<table>"`, `"<domain>.<table>"`, `"<domain>.<table>.<column>"`, or `"<table>.<column>"`.

### Are foreign keys enforced on all warehouses?
No. Some data warehouses do not support foreign keys. In that case, the constraint is ignored at runtime but still appears in the auto-generated entity-relational diagram.

### What file metadata properties are available during load?
Four properties: `file_name` (name), `file_path` (path), `file_size` (size in bytes), and `file_modification_time` (last modification date).