# Load CSV and DSV Files

> Load CSV and delimiter-separated files into BigQuery, Snowflake, Databricks or DuckDB with Starlake. Configure separators, encoding, headers, column validation, and write strategies in YAML.

<script type="application/ld+json">
    {JSON.stringify({
      "@context": "https://schema.org",
      "@type": "FAQPage",
      "mainEntity": [
        {
          "@type": "Question",
          "name": "What delimiters does Starlake support for CSV files?",
          "acceptedAnswer": {
            "@type": "Answer",
            "text": "Starlake supports any single-character delimiter through the 'separator' property in the metadata configuration. Common delimiters include semicolons (;), commas (,), pipes (|), and tabs. The default separator is semicolon (;). For multi-character delimiters, use the manual load configuration."
          }
        },
        {
          "@type": "Question",
          "name": "What encoding does Starlake use for CSV files?",
          "acceptedAnswer": {
            "@type": "Answer",
            "text": "The default encoding is UTF-8. You can change it by setting the 'encoding' property in the metadata section of the table configuration file. Any encoding supported by Java can be used (e.g., ISO-8859-1, Windows-1252)."
          }
        },
        {
          "@type": "Question",
          "name": "Does Starlake require CSV files to have headers?",
          "acceptedAnswer": {
            "@type": "Answer",
            "text": "No, headers are optional. By default, Starlake expects headers (withHeader: true). If your file has no header, set 'withHeader' to false and the attribute names in the schema will refer to column indices instead."
          }
        },
        {
          "@type": "Question",
          "name": "How does Starlake infer the schema of a CSV file?",
          "acceptedAnswer": {
            "@type": "Answer",
            "text": "Use the 'starlake infer-schema' command to automatically detect the schema from a sample data file. This generates a YAML configuration file with detected column names, types, and parsing options that you can then customize."
          }
        },
        {
          "@type": "Question",
          "name": "What write strategies are available for CSV loading?",
          "acceptedAnswer": {
            "@type": "Answer",
            "text": "Starlake supports APPEND, OVERWRITE, OVERWRITE_BY_PARTITION, UPSERT_BY_KEY, UPSERT_BY_KEY_AND_TIMESTAMP, and SCD2 write strategies for CSV files."
          }
        },
        {
          "@type": "Question",
          "name": "What happens to rows that fail validation?",
          "acceptedAnswer": {
            "@type": "Answer",
            "text": "Rows that do not match the defined schema (wrong type, missing required field) are sent to the audit.rejected table with the rejection reason."
          }
        }
      ]
    })}
  </script>

  <script type="application/ld+json">
    {JSON.stringify({
      "@context": "https://schema.org",
      "@type": "HowTo",
      "name": "How to Load CSV and DSV Files with Starlake",
      "description": "Load CSV and delimiter-separated files into your data warehouse using Starlake by inferring the schema and configuring parsing options.",
      "step": [
        {
          "@type": "HowToStep",
          "position": 1,
          "name": "Infer the schema",
          "text": "Run 'starlake infer-schema --input incoming/<domain>' to auto-detect column names, types, and separator from a sample file."
        },
        {
          "@type": "HowToStep",
          "position": 2,
          "name": "Configure parsing options",
          "text": "Edit the generated
          .sl.yml file to set format, separator, encoding, withHeader, quote, and escape properties."
        },
        {
          "@type": "HowToStep",
          "position": 3,
          "name": "Define attribute validation",
          "text": "Review the attributes section and set column types (int, double, date, etc.), required constraints, and trim strategies."
        },
        {
          "@type": "HowToStep",
          "position": 4,
          "name": "Choose a write strategy",
          "text": "Set writeStrategy.type to APPEND, OVERWRITE, UPSERT_BY_KEY, or SCD2 depending on your loading pattern."
        },
        {
          "@type": "HowToStep",
          "position": 5,
          "name": "Run the load command",
          "text": "Execute 'starlake load' to parse, validate, and load CSV data into the target warehouse table."
        }
      ]
    })}
  </script>

# Load CSV and Delimiter-Separated Files into Your Data Warehouse

Starlake loads CSV and delimiter-separated value (DSV) files into BigQuery, Snowflake, Databricks, DuckDB, and other warehouses. It supports configurable separators, encodings, headers, and [write strategies](load-strategies). Schema inference auto-detects column names and types. Every record is validated against the schema -- rejected records are stored in the `audit.rejected` table for review.

You can use [autoload](autoload) for zero-config loading, or configure the manual [load](load) workflow for full control over parsing options.

## Infer CSV Schema Automatically with infer-schema

Start by inferring the schema from a sample data file. The `infer-schema` command detects column names, types, separator, and encoding, then generates a YAML configuration file.

```bash
starlake infer-schema --input incoming/starbake
```

This generates a `<table>.sl.yml` file under `metadata/load/<domain>/`. Review and customize it before loading. See the [autoload format detection](autoload#how-autoload-detects-file-format-csv-json-xml) documentation for details on how detection works.

## CSV Parsing Options: Separator, Encoding, Quote, Escape

Most of the time, `infer-schema` generates a working configuration. Customize the `metadata` section when you need to override parsing behavior.

```yaml title="metadata/load/<domain>/<table>.sl.yml - parsing section"
table:
  pattern: "order_line.*.csv"
  metadata:
    format: "DSV"
    withHeader: true
    separator: ";"
    ...
  attributes:
    - ...
```

### Available Parsing Options

| Option | Description | Default |
|---|---|---|
| `encoding` | File encoding (e.g., `UTF-8`, `ISO-8859-1`, `Windows-1252`) | `UTF-8` |
| `withHeader` | Whether the file contains a header row | `true` |
| `separator` | Column delimiter character | `;` |
| `quote` | Quote character for wrapping field values | `"` |
| `escape` | Escape character inside quoted fields | `\` |
| `filter` | Condition/regex to filter lines. Matching lines are loaded; others are discarded | None |
| `ack` | Load the file only if a file with the same name and this extension exists in the same directory | None |
| `options` | Map of options passed to the parser. See the [Apache Spark CSV documentation](https://spark.apache.org/docs/latest/sql-data-sources-csv.html#data-source-option) for all available options | None |
| `emptyIsNull` | Treat empty column values as NULL | `false` |
| `fillWithDefaultValue` | Fill NULL values with the column's default value | `false` |

## Column Validation: Types, Required Fields, Transforms

The `attributes` section defines column-level validation rules. Each attribute maps to a column in the source file and the target table.

```yaml title="metadata/load/<domain>/<table>.sl.yml - attributes section"
table:
  pattern: "order_line.*.csv"
  metadata:
    - ...
  attributes:
  - name: "order_id"
    type: "int"
  - name: "product_id"
    type: "int"
  - name: "quantity"
    type: "int"
  - name: "sale_price"
    type: "double"
```

### Available Attribute Properties

| Property | Description | Default |
|---|---|---|
| `name` | Column name from the file header. If no header, refers to the column index (0-based). Also used as the target table column name unless `rename` is set. | (required) |
| `type` | Column type: `string`, `int`, `long`, `double`, `float`, `boolean`, or any `date`/`timestamp` variant defined in `metadata/types/default.sl.yml`. You can add custom types in the `metadata/types/` directory. | `string` |
| `required` | Whether the column must have a non-null value | `false` |
| `privacy` | Privacy transformation to apply (e.g., masking, hashing) | None |
| `script` | SQL transformation to apply to the column value | None |
| `comment` | Column comment | None |
| `rename` | Target column name in the database (overrides `name`) | `name` |
| `default` | Default value when the column is NULL | None |
| `trim` | Trim strategy: `None`, `Left`, `Right`, `Both` | None |

To add, replace, or ignore attributes during loading, see the [Transform on load](transform) documentation.

## Complete CSV Load Configuration Example

The configuration below describes a complete setup for loading `order_line` CSV files with APPEND write strategy:

```yaml title="metadata/load/<domain>/<table>.sl.yml"
table:
  pattern: "order_line.*.csv"
  metadata:
    format: "DSV"
    withHeader: true
    separator: ";"
    writeStrategy:
      type: "APPEND"
  attributes:
  - name: "order_id"
    type: "int"
  - name: "product_id"
    type: "int"
  - name: "quantity"
    type: "int"
  - name: "sale_price"
    type: "double"
```

The `writeStrategy.type` controls how data is written to the target table. Available strategies: `APPEND`, `OVERWRITE`, `OVERWRITE_BY_PARTITION`, `UPSERT_BY_KEY`, `UPSERT_BY_KEY_AND_TIMESTAMP`, and `SCD2`.

## Frequently Asked Questions

### What delimiters does Starlake support for CSV files?
Starlake supports any single-character delimiter through the `separator` property in the metadata configuration. Common delimiters include semicolons (`;`), commas (`,`), pipes (`|`), and tabs. The default separator is semicolon (`;`). For multi-character delimiters, use the [manual load](load) configuration.

### What encoding does Starlake use for CSV files?
The default encoding is UTF-8. Change it by setting the `encoding` property in the metadata section. Any Java-supported encoding is accepted (e.g., `ISO-8859-1`, `Windows-1252`).

### How do I load a CSV file without a header row?
Set `withHeader: false` in the `metadata` section. Column names in the `attributes` section then refer to column indices (0-based) instead of header names.

### How does Starlake infer the schema of a CSV file?
Run `starlake infer-schema --input incoming/<domain>`. This generates a `.sl.yml` YAML file with detected column names, types, and parsing options.

### What write strategies are available for CSV loading?
Starlake supports APPEND, OVERWRITE, OVERWRITE_BY_PARTITION, UPSERT_BY_KEY, UPSERT_BY_KEY_AND_TIMESTAMP, and SCD2.

### Can I rename columns during CSV loading?
Yes. Use the `rename` property on any attribute to set a different column name in the target table.

### How does Starlake handle empty values in CSV files?
By default, empty values are kept as empty strings. Set `emptyIsNull: true` in the metadata section to treat empty values as NULL.

### What happens to rows that fail validation?
Rows that do not match the defined schema (wrong type, missing required field) are sent to the `audit.rejected` table with the rejection reason.

### Can I apply transformations during CSV loading?
Yes. Use the `script` and `privacy` properties on individual attributes, or see the [Transform on load](transform) documentation for adding, replacing, or ignoring attributes.