Metadata-Version: 2.4
Name: sqlcarbon
Version: 0.3.0
Summary: Reliable, deterministic SQL Server table-to-table copy tool
Project-URL: Homepage, https://github.com/TroBeeOne/SQLCarbon
Project-URL: Issues, https://github.com/TroBeeOne/SQLCarbon/issues
License: MIT
Keywords: data-migration,database,etl,sql,sqlserver
Classifier: Development Status :: 4 - Beta
Classifier: Intended Audience :: Developers
Classifier: Intended Audience :: System Administrators
Classifier: License :: OSI Approved :: MIT License
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.10
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Topic :: Database
Requires-Python: >=3.10
Requires-Dist: click>=8.0
Requires-Dist: pyarrow>=16.0
Requires-Dist: pydantic>=2.0
Requires-Dist: pyodbc>=4.0
Requires-Dist: pyyaml>=6.0
Provides-Extra: dev
Requires-Dist: pytest>=8.0; extra == 'dev'
Description-Content-Type: text/markdown

# SQLcarbon

**Reliable, deterministic SQL Server table-to-table copy tool.**

Copy tables between SQL Server instances with a single command or a few lines of Python — no SSIS, no BCP scripts, no fuss.

> Created by **TroBeeOne LLC**

---

## Features

- Copy tables across different SQL Server instances (same or different versions)
- Supports trusted (Windows) and SQL authentication
- Recreates schema: columns, identity columns (with correct seed/increment), computed columns
- Optionally copies indexes, check/default constraints, and extended properties
- Three copy modes: **full**, **schema_only**, **data_only**
- **Export directly to Parquet files** — use SQL Server as a source and write `.parquet` output
- **Large exports split automatically** — one file for small tables, a folder of `part-00000.parquet, part-00001.parquet, …` once a size threshold (default 512 MB) is reached
- **Never overwrites by accident** — SQL tables are never overwritten, and Parquet output is only replaced with an explicit `overwrite: true`
- Chunked streaming reads with `fast_executemany` inserts — handles tables of any size
- Continues to the next job when one fails (configurable `stop_on_failure`)
- Clear, structured log files written to your working directory, with a run summary that repeats every warning
- Version compatibility warnings (e.g., using `datetime2` against a SQL Server 2005 target)
- Use as a **CLI tool** or a **Python library**

---

## Installation

```bash
pip install sqlcarbon
```

Requires **Python 3.10+** and the [Microsoft ODBC Driver for SQL Server](https://learn.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server).

---

## Quick Start — CLI

### 1. Generate a sample config

```bash
sqlcarbon init > plan.yaml
```

The sample includes a SQL-to-SQL copy and a SQL-to-Parquet export, with every Parquet setting shown.

### 2. Edit `plan.yaml`

```yaml
connections:
  my_source:
    server: "sql01.example.com"
    database: "Sales"
    auth:
      mode: "trusted"

  my_dest:
    server: "sql02.example.com"
    database: "Archive"
    auth:
      mode: "sql"
      username: "sa"
      password: "yourpassword"

defaults:
  batch_size: 100000
  stop_on_failure: false
  create_indexes: false
  create_constraints: false
  include_extended_properties: false
  copy_mode: "full"
  nolock: true

jobs:
  - name: CopyCustomers
    source_connection: my_source
    destination_connection: my_dest
    source_table: dbo.Customers
    destination_table: dbo.Customers_Archive
```

### 3. Validate your config (no database changes)

```bash
sqlcarbon validate plan.yaml
```

```
OK: Config is valid — 2 connection(s), 1 job(s).
```

### 4. Run it

```bash
sqlcarbon run plan.yaml
```

```
2026-03-08 10:00:01 INFO     ============================================================
2026-03-08 10:00:01 INFO     Starting Job: [CopyCustomers]
2026-03-08 10:00:01 INFO     ============================================================
2026-03-08 10:00:01 INFO     [CopyCustomers] Source:      sql01.example.com / Sales
2026-03-08 10:00:01 INFO     [CopyCustomers] Destination: sql02.example.com / Archive
2026-03-08 10:00:01 INFO     [CopyCustomers] Copy mode:   full
2026-03-08 10:00:02 INFO     [CopyCustomers] Source: SQL Server 2019 | Destination: SQL Server 2019
2026-03-08 10:00:02 INFO     [CopyCustomers] Creating table [dbo].[Customers_Archive]...
2026-03-08 10:00:02 INFO     [CopyCustomers] Table created.
2026-03-08 10:00:02 INFO     [CopyCustomers] Starting data copy (batch_size=100,000, nolock=True)...
2026-03-08 10:00:04 INFO     [CopyCustomers]   ... 100,000 rows inserted.
2026-03-08 10:00:05 INFO     [CopyCustomers]   ... 185,432 rows inserted.
2026-03-08 10:00:05 INFO     [CopyCustomers] SUCCESS | rows=185,432 | duration=3.84s
```

A log file is also written to your current directory: `sqlcarbon_20260308_100001.log`

Every run ends with a summary. Warnings are repeated under the job they belong to, so they are easy to find in a long log:

```
2026-03-08 10:05:12 INFO     ============================================================
2026-03-08 10:05:12 INFO     RUN SUMMARY
2026-03-08 10:05:12 INFO     ============================================================
2026-03-08 10:05:12 INFO     Total jobs : 2
2026-03-08 10:05:12 INFO     Succeeded  : 2
2026-03-08 10:05:12 INFO     Failed     : 0
2026-03-08 10:05:12 INFO     Warnings   : 1 job(s) — see below
2026-03-08 10:05:12 INFO       [OK] ExportOrdersToParquet — 300,000 rows in 0.91s
2026-03-08 10:05:12 INFO              Output: C:\exports\Orders (5 files)
2026-03-08 10:05:12 INFO       [OK] ExportPricingToParquet — 8 rows in 0.23s
2026-03-08 10:05:12 INFO              Output: C:\exports\Pricing.parquet (1 file)
2026-03-08 10:05:12 INFO              WARNING: Column [Value] is sql_variant: its values are transferred as string text; the original base type (int, datetime2, varbinary, ...) is not preserved.
2026-03-08 10:05:12 INFO     ============================================================
```

The exit code is `0` when every job succeeded and `1` otherwise.

---

## Quick Start — Python Library

```python
from sqlcarbon import MigrationPlan, run_plan

plan = MigrationPlan.from_yaml("plan.yaml")
summary = run_plan(plan)

print(f"Succeeded: {summary.succeeded} / {summary.total_jobs}")
for result in summary.results:
    print(f"  {result.job_name}: {result.rows_copied:,} rows in {result.duration_seconds:.2f}s")
    for warning in result.warnings:
        print(f"    warning: {warning}")
    if result.output_path:                      # Parquet jobs
        print(f"    wrote {result.files_written} file(s) to {result.output_path}")
```

`JobResult` fields: `job_name`, `success`, `rows_copied`, `partial`, `error`, `duration_seconds`, `warnings`, and — for Parquet jobs — `output_path` and `files_written`. After a failed Parquet export, `output_path` is the staging folder that holds the incomplete output (see [If an export fails part-way](#if-an-export-fails-part-way)).

### Load from a Python dict

```python
from sqlcarbon import MigrationPlan, run_plan

plan = MigrationPlan.from_dict({
    "connections": {
        "src": {
            "server": "sql01.example.com",
            "database": "Sales",
            "auth": {"mode": "trusted"},
        },
        "dst": {
            "server": "sql02.example.com",
            "database": "Archive",
            "auth": {"mode": "sql", "username": "sa", "password": "yourpassword"},
        },
    },
    "jobs": [
        {
            "name": "CopyCustomers",
            "source_connection": "src",
            "destination_connection": "dst",
            "source_table": "dbo.Customers",
            "destination_table": "dbo.Customers_Archive",
        }
    ],
})

summary = run_plan(plan)
```

### Load from a YAML string

```python
from sqlcarbon import MigrationPlan, run_plan

yaml_text = """
connections:
  src:
    server: "sql01.example.com"
    database: "Sales"
    auth:
      mode: "trusted"
  dst:
    server: "sql02.example.com"
    database: "Archive"
    auth:
      mode: "trusted"
jobs:
  - name: CopyOrders
    source_connection: src
    destination_connection: dst
    source_table: dbo.Orders
    destination_table: dbo.Orders_Archive
"""

plan = MigrationPlan.from_yaml_string(yaml_text)
summary = run_plan(plan)
```

---

## Configuration Reference

A plan has three parts: `connections` (where the servers are), `defaults` (settings shared by every job) and `jobs` (the copies to run).

### `connections`

Each named connection supports:

| Field | Required | Default | Description |
|-------|----------|---------|-------------|
| `server` | Yes | — | Server name, IP, or `server,port` / `server:port` |
| `database` | Yes | — | Target database name |
| `auth.mode` | No | `trusted` | `trusted` (Windows auth) or `sql` (SQL auth) |
| `auth.username` | If `sql` | — | SQL login username |
| `auth.password` | If `sql` | — | SQL login password |
| `driver` | No | `ODBC Driver 17 for SQL Server` | ODBC driver name |
| `trust_server_certificate` | No | `false` | Set `true` to bypass SSL certificate validation (equivalent to SSMS "Trust server certificate") |

**Windows (trusted) authentication** — the default:
```yaml
connections:
  prod:
    server: "sql01.example.com"
    database: "Sales"
    auth:
      mode: "trusted"
```

**SQL authentication:**
```yaml
connections:
  prod:
    server: "sql01.example.com"
    database: "Sales"
    auth:
      mode: "sql"
      username: "report_reader"
      password: "yourpassword"
```

**Custom port:**
```yaml
connections:
  my_conn:
    server: "sql01.example.com,1445"
    database: "MyDB"
    auth:
      mode: "trusted"
```

**ODBC Driver 18** (needed for newer SQL Server / Azure SQL):
```yaml
connections:
  my_conn:
    server: "sql01.example.com"
    database: "MyDB"
    auth:
      mode: "trusted"
    driver: "ODBC Driver 18 for SQL Server"
    trust_server_certificate: true   # bypass cert validation (like SSMS checkbox)
```

**What permissions are needed?** No `sysadmin` and no `db_owner`. SQLcarbon only reads catalog views (`sys.columns`, `sys.indexes`, …) on the **source**, and never reads the destination's schema — it just runs `CREATE TABLE` and `INSERT` there.

| Where | Login needs | Notes |
|-------|-------------|-------|
| Source | `SELECT` on the table | Enough to copy the data and recreate the columns and identity, plus `create_indexes` (primary key and indexes) and `include_extended_properties`. |
| Source | plus `VIEW DEFINITION` on the table | Also required to recreate **computed columns** and, with `create_constraints`, **check and default constraints**. `VIEW DEFINITION` is a read-only metadata permission: it lets a login see the text of those expressions, nothing more. Without it SQL Server hides them, and SQLcarbon stops **before creating anything** with a message that names the columns or constraints and the `GRANT` your DBA would need to run. |
| SQL destination | `db_ddladmin` + `db_datawriter` (or the equivalent `CREATE TABLE`, `ALTER` and `INSERT` grants) | A login without DDL rights fails cleanly with `CREATE TABLE permission denied`. |
| Parquet destination | no database permissions | Only write access to the output folder (and `SELECT` on the source). |

**SQLcarbon never grants or changes permissions.** It only reads. When a permission is missing it says which one and stops; whether to grant it is up to whoever administers the source server. If your DBA would rather not grant `VIEW DEFINITION`, you can still copy a table that has computed columns: create the destination table yourself (for example by scripting it from SSMS) and run the job with `copy_mode: data_only`, which does not need the definitions. Or simply leave `create_constraints` off.

The message looks like this:

```
Cannot read the definition of computed column(s) [NameUpper] on [dbo].[Customers]. SQL Server only shows these to logins
that have VIEW DEFINITION on the table, and the source login does not. Nothing was created on the destination. Ask your
DBA to run: GRANT VIEW DEFINITION ON OBJECT::[dbo].[Customers] TO <source user>; or create the destination table
yourself and use copy_mode: data_only.
```

---

### `defaults`

Global defaults applied to all jobs unless overridden at the job level.

| Field | Default | Description |
|-------|---------|-------------|
| `batch_size` | `100000` | Rows per read/insert chunk |
| `stop_on_failure` | `false` | Stop all remaining jobs if one fails |
| `create_indexes` | `false` | Recreate indexes on destination (SQL destinations) |
| `create_constraints` | `false` | Recreate check and default constraints (SQL destinations) |
| `include_extended_properties` | `false` | Copy extended properties (SQL destinations) |
| `copy_mode` | `full` | `full`, `schema_only`, or `data_only` |
| `nolock` | `true` | Use `WITH (NOLOCK)` on source reads (global only — cannot be set per job) |
| `parquet_layout` | `auto` | Parquet only: `auto`, `folder` or `single` — see [Output layout](#output-layout-parquet_layout) |
| `parquet_max_file_size` | `512MB` | Parquet only: size at which a file is closed and a new part started — see [File size](#file-size-parquet_max_file_size) |
| `overwrite` | `false` | Parquet only: replace existing output — see [Replacing existing output](#replacing-existing-output-overwrite). **Never applies to SQL tables.** |

---

### `jobs`

Each job represents one table copy operation. A job writes to either a **SQL Server table** or **Parquet output** — specify one, not both.

**SQL Server destination:**

| Field | Required | Description |
|-------|----------|-------------|
| `name` | Yes | Friendly name shown in logs |
| `source_connection` | Yes | Name of a connection defined under `connections` |
| `source_table` | Yes | Source table, e.g. `dbo.Customers` |
| `destination_connection` | Yes (SQL) | Name of a connection defined under `connections` |
| `destination_table` | Yes (SQL) | Destination table, e.g. `dbo.Customers_Archive` |
| `options` | No | Per-job overrides (see below) |

**Parquet destination:**

| Field | Required | Description |
|-------|----------|-------------|
| `name` | Yes | Friendly name shown in logs |
| `source_connection` | Yes | Name of a connection defined under `connections` |
| `source_table` | Yes | Source table, e.g. `dbo.Customers` |
| `destination_file` | Yes (Parquet) | Path of the output `.parquet` file. Large exports become a folder named after this file (without the extension) — see [Output layout](#output-layout-parquet_layout) |
| `options` | No | `batch_size`, `stop_on_failure`, `parquet_layout`, `parquet_max_file_size`, `overwrite` apply; `copy_mode` must be `full` or `data_only`. Index/constraint options are ignored. |

### Options at a glance

Every option can be set in `defaults` (all jobs) and in a job's `options` (that job only), except `nolock`, which is `defaults` only.

| Option | Applies to | Default | What it does |
|--------|-----------|---------|--------------|
| `copy_mode` | SQL, Parquet | `full` | `full`, `schema_only` or `data_only` — see [Copy Modes](#copy-modes) |
| `batch_size` | SQL, Parquet | `100000` | Rows read and written per chunk |
| `nolock` | SQL, Parquet | `true` | `WITH (NOLOCK)` on source reads (`defaults` only) |
| `stop_on_failure` | SQL, Parquet | `false` | Halt the remaining jobs if this one fails |
| `create_indexes` | SQL | `false` | Recreate primary key and indexes |
| `create_constraints` | SQL | `false` | Recreate check and default constraints |
| `include_extended_properties` | SQL | `false` | Copy table and column extended properties |
| `parquet_layout` | Parquet | `auto` | `auto`, `folder` or `single` |
| `parquet_max_file_size` | Parquet | `512MB` | Size at which to start a new part (ignored by `single`) |
| `overwrite` | Parquet | `false` | Replace existing output (rejected on SQL destinations) |

Setting a Parquet-only option (`parquet_layout`, `parquet_max_file_size`) on a SQL job, or `overwrite: true` on a SQL job, is a configuration error and is reported by `sqlcarbon validate`.

---

## Option Guide

A worked example for each option. In these examples `prod` and `archive` are connections defined under `connections`.

### `copy_mode`

**`full` (the default)** — create the table and copy the rows:

```yaml
jobs:
  - name: ArchiveOrders
    source_connection: prod
    destination_connection: archive
    source_table: dbo.Orders
    destination_table: dbo.Orders_2026
```

**`schema_only`** — create the empty table and stop. Useful to review or adjust the structure before loading:

```yaml
jobs:
  - name: CreateOrdersShell
    source_connection: prod
    destination_connection: archive
    source_table: dbo.Orders
    destination_table: dbo.Orders_2026
    options:
      copy_mode: "schema_only"
      create_indexes: true
```

**`data_only`** — load rows into a table that already exists. The two-step pattern — create the structure, then load it — is just the two jobs above in sequence:

```yaml
jobs:
  - name: CreateOrdersShell
    source_connection: prod
    destination_connection: archive
    source_table: dbo.Orders
    destination_table: dbo.Orders_2026
    options:
      copy_mode: "schema_only"

  - name: LoadOrders
    source_connection: prod
    destination_connection: archive
    source_table: dbo.Orders
    destination_table: dbo.Orders_2026
    options:
      copy_mode: "data_only"
```

`data_only` fails if the destination table does not exist, and it **appends**: running it twice loads the rows twice, because SQLcarbon never truncates. The INSERT uses the source column names, so the destination table must have columns with the same names.

**Parquet:** both `full` and `data_only` write all rows to the output; `schema_only` is rejected for Parquet destinations.

```yaml
jobs:
  - name: ExportOrders
    source_connection: prod
    source_table: dbo.Orders
    destination_file: "C:/exports/orders.parquet"
    options:
      copy_mode: "data_only"     # same result as "full" for Parquet
```

### `batch_size`

Rows fetched from the source and written to the destination per chunk (default `100000`). Smaller batches use less memory; for very wide tables or large `varchar(max)` columns try a lower value. For Parquet, each batch becomes a row group, and the batch size also bounds how far a part file can overshoot its size limit.

```yaml
defaults:
  batch_size: 250000          # applies to every job...

jobs:
  - name: CopyEvents
    source_connection: prod
    destination_connection: archive
    source_table: dbo.Events
    destination_table: dbo.Events
    options:
      batch_size: 20000       # ...except this one, which has wide rows
```

### `nolock`

Reads the source with `WITH (NOLOCK)` so the export does not block writers (default `true`). It is a global setting:

```yaml
defaults:
  nolock: false               # take normal shared locks instead

jobs:
  - name: CopyLedger
    source_connection: prod
    destination_connection: archive
    source_table: dbo.Ledger
    destination_table: dbo.Ledger
```

> **Heads-up for long exports:** `NOLOCK` reads can return duplicate or missing rows if the table is being modified while it is read, and can fail with SQL Server error 601. For a consistent copy of a busy table, set `nolock: false`, or read from a snapshot or a quiet replica.

### `create_indexes`, `create_constraints`, `include_extended_properties`

By default SQLcarbon creates the table and data only — no primary key, indexes, constraints or properties, which keeps bulk loads fast. Turn on what you need:

```yaml
jobs:
  - name: CopyCustomersWithEverything
    source_connection: prod
    destination_connection: archive
    source_table: dbo.Customers
    destination_table: dbo.Customers_Archive
    options:
      create_indexes: true                 # primary key + clustered/nonclustered indexes
      create_constraints: true             # check and default constraints
      include_extended_properties: true    # e.g. MS_Description on the table and its columns
```

| Option | Recreates |
|--------|-----------|
| `create_indexes` | Primary key and all indexes, including unique, descending keys and `INCLUDE` columns |
| `create_constraints` | Check constraints and default constraints (with their original names) |
| `include_extended_properties` | Table-level and column-level extended properties |

Identity columns (with their real seed and increment) and computed columns are always recreated; you don't need an option for them. Reading constraint and computed-column expressions needs `VIEW DEFINITION` on the source table — see [What permissions are needed?](#connections).

Primary key, check and default constraints keep their original names, and those names must be unique within a schema. Before creating anything, SQLcarbon looks in the destination schema for objects that already use those names (constraints, tables, views, …). If it finds any it stops, creates nothing, and lists them:

```
Cannot create [dbo].[Orders_2026] with create_indexes / create_constraints: schema [dbo] already contains object(s)
with the same name as the primary key / constraint(s) that would be created: PK_Orders (PRIMARY_KEY_CONSTRAINT),
CK_Orders_Total (CHECK_CONSTRAINT). Constraint names must be unique within a schema. Nothing was created. Copy into a
different schema, or turn off create_indexes / create_constraints.
```

This is not limited to copying within one database: an archive database that already holds `dbo.Orders` (with `PK_Orders`) clashes with a copy called `dbo.Orders_2026` in the same schema. The fix is to copy into a different schema (which must already exist) or to leave those options off. Ordinary (non-primary-key) index names belong to their table and never clash.

### `stop_on_failure`

By default a failed job is logged and the next job still runs. Set `stop_on_failure` to halt everything after a failure.

```yaml
defaults:
  stop_on_failure: false      # keep going by default

jobs:
  - name: CopyReferenceData
    source_connection: prod
    destination_connection: archive
    source_table: dbo.Currencies
    destination_table: dbo.Currencies

  - name: CopyOrders
    source_connection: prod
    destination_connection: archive
    source_table: dbo.Orders
    destination_table: dbo.Orders
    options:
      stop_on_failure: true   # if this one fails, do not run the jobs below

  - name: CopyOrderLines      # only runs if CopyOrders succeeded
    source_connection: prod
    destination_connection: archive
    source_table: dbo.OrderLines
    destination_table: dbo.OrderLines
```

The last job does not appear in the summary when the run is halted before it. `sqlcarbon run` exits with code `1` if any job failed.

### Overriding defaults per job

Anything in `defaults` can be overridden for a single job under `options`:

```yaml
defaults:
  batch_size: 100000
  create_indexes: true
  copy_mode: "full"

jobs:
  - name: CopyCustomers           # uses the defaults above
    source_connection: prod
    destination_connection: archive
    source_table: dbo.Customers
    destination_table: dbo.Customers

  - name: CopyAuditLog            # fast bulk load: no indexes, smaller batches
    source_connection: prod
    destination_connection: archive
    source_table: dbo.AuditLog
    destination_table: dbo.AuditLog
    options:
      create_indexes: false
      batch_size: 25000
```

---

## Copy Modes

| Mode | Creates Table | Copies Data | Use When |
|------|:---:|:---:|----------|
| `full` (default) | Yes | Yes | Normal table archiving / migration |
| `schema_only` | Yes | No | Pre-create table structure before a data load |
| `data_only` | No | Yes | Destination table already exists; just load rows |

> **Safety:** SQLcarbon will **never** drop or truncate an existing table, and there is no option to make it do so. If a destination table already exists when running `full` or `schema_only`, the job hard-fails with a clear error message and no data is touched. `overwrite: true` does not change this — it applies to Parquet output only, and setting it on a SQL destination is a configuration error.
>
> For `data_only`, if the destination table does **not** exist, the job hard-fails with a clear error message.

---

## Parquet Export

Export a SQL Server table directly to Parquet — no destination connection needed:

```yaml
connections:
  prod:
    server: "sql01.example.com"
    database: "Sales"
    auth:
      mode: "trusted"

defaults:
  batch_size: 100000
  nolock: true
  parquet_layout: "auto"            # one file, becomes a folder of parts when large (default)
  parquet_max_file_size: "512MB"    # the default; shown here so you know it is there
  overwrite: false                  # the default: fail if the output already exists

jobs:
  - name: ExportCustomersToParquet
    source_connection: prod
    source_table: dbo.Customers
    destination_file: "C:/exports/customers.parquet"

  - name: ExportOrdersToParquet
    source_connection: prod
    source_table: dbo.Orders
    destination_file: "C:/exports/orders.parquet"
    options:
      batch_size: 50000
```

With `auto` layout and the default 512 MB limit, a small table produces `C:/exports/customers.parquet`. A table too large for one 512 MB file produces the folder `C:/exports/orders/` instead — see below. **If you don't set `parquet_layout`, `parquet_max_file_size` or `overwrite`, you get `auto`, `512MB` and `false`.**

Parent directories are created automatically. You can mix SQL and Parquet destinations in the same plan:

```yaml
jobs:
  - name: ArchiveCustomers          # SQL → SQL
    source_connection: prod
    destination_connection: archive
    source_table: dbo.Customers
    destination_table: dbo.Customers_Archive

  - name: ExportCustomers           # SQL → Parquet
    source_connection: prod
    source_table: dbo.Customers
    destination_file: "C:/exports/customers.parquet"
```

### Output layout: `parquet_layout`

| Layout | Result |
|--------|--------|
| `auto` (default) | A single file. If it reaches `parquet_max_file_size` and more rows remain, the output becomes a **folder** named after the file (extension removed) holding `part-00000.parquet`, `part-00001.parquet`, … |
| `folder` | **Always** a folder of part files — even a 1 KB table gets a folder with one `part-00000.parquet`. Each part rolls at `parquet_max_file_size`. |
| `single` | **Always** exactly one file, however big. **`parquet_max_file_size` is ignored.** |

For `destination_file: "C:/exports/orders.parquet"`, the three possible results look like this:

```
single file               folder of parts
C:/exports/               C:/exports/
└── orders.parquet        └── orders/
                              ├── part-00000.parquet
                              ├── part-00001.parquet
                              ├── part-00002.parquet
                              └── _SUCCESS
```

`_SUCCESS` is an empty marker file written last; tools that read the folder ignore it, and it tells you the export finished.

**`auto` — let SQLcarbon decide:**

```yaml
jobs:
  - name: ExportOrders
    source_connection: prod
    source_table: dbo.Orders
    destination_file: "C:/exports/orders.parquet"
    options:
      parquet_layout: "auto"        # the default — same as leaving it out
```

**`folder` — always a folder, with a part size you choose.** Handy when downstream tools expect a folder every time, so the path never changes shape:

```yaml
jobs:
  - name: ExportOrders
    source_connection: prod
    source_table: dbo.Orders
    destination_file: "C:/exports/orders.parquet"     # writes C:/exports/orders/part-00000.parquet, ...
    options:
      parquet_layout: "folder"
      parquet_max_file_size: "1GB"                     # each part is about 1 GB
```

**`single` — one file, no matter what:**

```yaml
jobs:
  - name: ExportOrders
    source_connection: prod
    source_table: dbo.Orders
    destination_file: "C:/exports/orders.parquet"
    options:
      parquet_layout: "single"
      parquet_max_file_size: "1GB"     # ignored, because the layout is single
```

With `folder` (and `auto`) the folder name is the file name without its extension, so `destination_file` needs an extension such as `.parquet`. With `single` it does not.

### File size: `parquet_max_file_size`

The size at which a part file is closed and the next one started. Write a number with a unit — `KB`, `MB`, `GB` or `TB` (1 KB = 1024 bytes; decimals such as `1.5GB` are fine). A bare number such as `512` is rejected, because it is too easy to mean megabytes and get bytes. The default is **`512MB`**.

```yaml
defaults:
  parquet_max_file_size: "1GB"        # every Parquet job rolls at about 1 GB

jobs:
  - name: ExportOrders
    source_connection: prod
    source_table: dbo.Orders
    destination_file: "C:/exports/orders.parquet"
                                      # 1GB from the defaults

  - name: ExportEvents
    source_connection: prod
    source_table: dbo.Events
    destination_file: "C:/exports/events.parquet"
    options:
      parquet_max_file_size: "256MB"  # smaller parts for this table

  - name: ExportLedger
    source_connection: prod
    source_table: dbo.Ledger
    destination_file: "C:/exports/ledger.parquet"
    options:
      parquet_max_file_size: "50GB"   # you really do want one huge file
```

To (almost) never split, use a very large size such as `"1TB"` — or, to *guarantee* a single file, use `parquet_layout: single`.

How the limit works:

- It applies to the **compressed size of each file on disk**, not to the source table's size. Parquet is usually much smaller than SQL Server's storage, so a 300 GB table may become only 60–100 GB of Parquet.
- It is a **soft limit**. A part is closed once it has *reached* the limit, so it can overshoot by up to one batch (`batch_size` rows). With the default 100,000-row batches that is typically a few tens of MB; lower `batch_size` for tighter sizes. The last part is whatever is left.
- SQLcarbon only starts a new part when more rows are waiting, so there is never an empty trailing part. A table whose final batch just tips it over the limit is still written as one file.
- Very large files are not always better: most query engines read many 128 MB–1 GB files in parallel more easily than a few 10 GB ones, and smaller files are easier to move and upload.

### Replacing existing output: `overwrite`

By default, if the output already exists the job **fails before reading any rows** and touches nothing. "Exists" means the file (`orders.parquet`), the folder (`orders/`) or an incomplete export left by an earlier failed run — whichever form the new export would take, a stale copy of the other form would be confusing, so either one blocks the job.

Set `overwrite: true` to replace the previous output:

```yaml
jobs:
  - name: NightlyOrdersExport
    source_connection: prod
    source_table: dbo.Orders
    destination_file: "C:/exports/orders.parquet"
    options:
      overwrite: true               # replace last night's export
```

`overwrite` is safe by design:

- The new export is written to a staging folder first, and the previous output is only removed **after the new export has completed**. If the new run fails, last night's export is still there.
- It replaces whichever form existed, so a run that used to be a folder of parts can become a single file and back, and stale parts from a bigger earlier export can never be mixed into a smaller new one.
- It deletes only what SQLcarbon itself writes: the `.parquet` file, and inside a part folder only `part-*.parquet` and `_SUCCESS`. If the folder contains anything else, the job fails and **nothing is deleted**.
- It can be set in `defaults` for every Parquet job:

```yaml
defaults:
  overwrite: true                   # all Parquet jobs replace their previous output

jobs:
  - name: NightlyOrdersExport
    source_connection: prod
    source_table: dbo.Orders
    destination_file: "C:/exports/orders.parquet"

  - name: OneOffSnapshot
    source_connection: prod
    source_table: dbo.Customers
    destination_file: "C:/exports/customers_snapshot.parquet"
    options:
      overwrite: false              # this one must never replace anything
```

**`overwrite` never applies to SQL Server tables.** SQLcarbon does not drop, truncate or replace tables, so a SQL job whose destination table exists always fails. Setting `overwrite: true` on a SQL job is a configuration error; if `defaults.overwrite` is `true`, SQL jobs ignore it and say so in their warnings.

### If an export fails part-way

Output is always written to a staging folder next to the destination (`orders.sqlcarbon-staging`) and renamed to its final name only when the export has finished. So a failed export never leaves something that looks like a complete file or folder.

If the failure comes after some rows were written:

- The job is reported as **PARTIAL**, with the number of rows written.
- The staging folder is **kept** so hours of work are not thrown away. Its parts are valid Parquet files, but the set is incomplete, and it is never renamed. The log and the run summary show its path.
- Re-running is refused while it exists. Delete the folder, or run with `overwrite: true`, which removes it first.

If it fails before any rows are written, nothing is left behind.

### Reading the output back

Any Parquet-aware tool (pandas, Spark, DuckDB, Power BI, …) can read either shape. A folder of parts is one logical table:

```python
import pyarrow.parquet as pq

table = pq.read_table("C:/exports/customers.parquet")   # a single file
table = pq.read_table("C:/exports/orders")               # a folder of parts, read as one table
```

### Column types

| SQL Server | Parquet |
|------------|---------|
| `bigint`, `int`, `smallint` | `int64`, `int32`, `int16` |
| `tinyint` | `uint8` (SQL Server's `tinyint` is 0–255) |
| `bit` | `bool` |
| `float`, `real` | `float64`, `float32` |
| `decimal(p,s)`, `numeric(p,s)` | `decimal128(p,s)` |
| `money`, `smallmoney` | `decimal128(19,4)`, `decimal128(10,4)` |
| `date` | `date32` |
| `datetime`, `datetime2`, `smalldatetime` | `timestamp[us]` (microseconds — Python's `datetime` resolution) |
| `datetimeoffset` | `timestamp[us, UTC]` — converted to UTC, so the original offset is **not** kept (a warning is logged; see [Data type notes](#data-type-notes)) |
| `time` | `time64[us]` |
| `char`, `varchar`, `nchar`, `nvarchar`, `text`, `ntext`, `xml`, `uniqueidentifier` | string |
| `binary`, `varbinary`, `image`, `rowversion` | binary |
| `sql_variant` | string (see [Data type notes](#data-type-notes)) |

Computed columns are not exported. `geography`, `geometry` and `hierarchyid` columns are not supported yet (see [Known limitations](#known-limitations)). Strings and binaries use Arrow's 64-bit-offset ("large") types while a batch is being built, so a batch of very large `varchar(max)` or `varbinary(max)` values cannot hit Arrow's 2 GB per-array limit; the resulting Parquet file is the same as with the regular types.

---

## Data type notes

**`sql_variant`** columns are copied as text: `nvarchar` on a SQL Server destination, string in Parquet. The original base type is **not** preserved — an `int` inside the variant arrives as the text `'42'`. Each such column is announced in the run log when the job starts, and again under the job in the final summary:

```
WARNING [ExportPricing] Column [Value] is sql_variant: its values are transferred as string text; the original base type (int, datetime2, varbinary, ...) is not preserved.
```

The text is chosen per base type so that nothing is lost:

| Base type inside the variant | Written as |
|------------------------------|------------|
| `binary`, `varbinary` | hex, e.g. `0xDEADBEEF` |
| `date`, `datetime`, `smalldatetime`, `datetime2`, `datetimeoffset`, `time` | ISO style with full precision, e.g. `2026-01-02 03:04:05.1234567`, `2026-02-03 04:05:06.1234567 +05:30` |
| `money`, `smallmoney` | four decimal places, e.g. `1234567.5678` |
| `float`, `real` | scientific notation with 16 significant digits, e.g. `1.000000000000000e-001` |
| everything else (`int`, `decimal`, `bit`, `uniqueidentifier`, character types, …) | the value's normal text form |

`NULL` stays `NULL`.

**`datetimeoffset`** columns are copied without loss between SQL Server tables: the value is read as text with its full precision and offset (`2026-02-03 04:05:06.1234567 +05:30`) and SQL Server converts it back on insert, so offsets, fractional seconds and the extremes of the range survive. In Parquet, which stores instants rather than offsets, the value is converted to UTC and written as `timestamp[us, UTC]` — the *moment* is exact, but the original offset (for example `+05:30`) is not kept. Each such column is announced with a warning in the run log and the final summary, the same way as `sql_variant`.

**`rowversion` / `timestamp`** columns are plumbing that SQL Server fills in itself (a database-wide counter, used for concurrency checks), and SQL Server rejects explicit values for them. So a SQL-to-SQL copy recreates the column but does not copy its values: the destination generates its own. A warning says so — keep it in mind if something compares those values between the two databases. A Parquet export writes the source values as binary.

### Known limitations

- **`geography`, `geometry`, `hierarchyid` (CLR types):** the ODBC layer used by SQLcarbon (pyodbc) cannot read them. A job that would read such a column stops immediately, before creating or writing anything, and names the column. `copy_mode: schema_only` still works for SQL destinations (it creates the table structure and reads no data).
- **Computed columns and constraints need `VIEW DEFINITION`** on the source table. Without it the job stops before creating anything and tells you what to ask your DBA for; `copy_mode: data_only` into a table you create yourself is the alternative. See [What permissions are needed?](#connections).
- **A failure after the table was created leaves that table in place.** SQLcarbon never drops anything, so if a job fails part-way (for example a permission error on `CREATE INDEX`, or a lost connection) the empty or partly filled destination table stays, and a re-run fails with "already exists" until you drop it yourself. The checks above exist precisely to catch the common causes before any table is created.

---

## Multiple Jobs Example

```yaml
connections:
  prod:
    server: "sql-prod.example.com"
    database: "Operations"
    auth:
      mode: "trusted"

  archive:
    server: "sql-archive.example.com"
    database: "Archive2026"
    auth:
      mode: "trusted"

defaults:
  batch_size: 100000
  stop_on_failure: false
  create_indexes: true
  copy_mode: "full"
  nolock: true
  parquet_layout: "auto"
  parquet_max_file_size: "512MB"
  overwrite: false

jobs:
  - name: CopyCustomers
    source_connection: prod
    destination_connection: archive
    source_table: dbo.Customers
    destination_table: dbo.Customers

  - name: CopyOrders
    source_connection: prod
    destination_connection: archive
    source_table: dbo.Orders
    destination_table: dbo.Orders
    options:
      stop_on_failure: true     # stop everything if Orders fails

  - name: CopyOrderLines
    source_connection: prod
    destination_connection: archive
    source_table: dbo.OrderLines
    destination_table: dbo.OrderLines

  - name: SchemaOnlyProducts
    source_connection: prod
    destination_connection: archive
    source_table: dbo.Products
    destination_table: dbo.Products
    options:
      copy_mode: "schema_only"
      create_indexes: true
      create_constraints: true

  - name: ExportOrderLinesToParquet      # a big table: split into 1 GB parts
    source_connection: prod
    source_table: dbo.OrderLines
    destination_file: "D:/lake/order_lines.parquet"
    options:
      parquet_layout: "folder"
      parquet_max_file_size: "1GB"
      overwrite: true
```

---

## Behavior Notes

- **Identity columns** — SQLcarbon reads the exact seed and increment from the source and recreates them on the destination. `SET IDENTITY_INSERT ON/OFF` is handled automatically, and the original identity values are preserved.
- **Computed columns** — Detected and recreated as computed columns on the destination. They are excluded from the data `INSERT` (SQL Server recalculates them automatically) and from Parquet output.
- **Rowversion columns** — Recreated on the destination but left out of the `INSERT`: SQL Server generates their values there. See [Data type notes](#data-type-notes).
- **Checks before anything is created** — For SQL destinations SQLcarbon checks up front that it can read the definitions it needs (`VIEW DEFINITION`), that no column has an unreadable CLR type, and that no primary key / constraint name is already taken in the destination schema. If a check fails the job stops with a message, and nothing has been created.
- **Partial failures (SQL)** — If a batch insert fails mid-copy, SQLcarbon logs a clear `PARTIAL FAILURE` warning with the number of rows already committed. The partial data is left in place for inspection; SQLcarbon does not attempt cleanup.
- **Partial failures (Parquet)** — Output is staged and only renamed when complete; an incomplete export is kept in its staging folder and reported. See [If an export fails part-way](#if-an-export-fails-part-way).
- **Empty tables** — A table with no rows produces a valid Parquet file that contains the schema and zero rows.
- **Warnings** — Non-fatal notices (such as `sql_variant` columns) are logged when the job starts and repeated in the final run summary.
- **Version compatibility** — If the source uses a data type not available on the destination (e.g., `datetime2` targeting SQL Server 2005), a warning is logged before the job runs. SQLcarbon does not attempt type transformations.

---

## Upgrading to 0.3.0

Version 0.3.0 changes the defaults of Parquet exports and adds several checks that run before any table is created. If you use Parquet destinations, please read this before upgrading:

- **Existing output is no longer silently overwritten.** Earlier versions replaced an existing `.parquet` file. Now the job fails unless you set `overwrite: true`. Scheduled exports that rewrite the same file every run need `overwrite: true` (per job or in `defaults`).
- **Large exports become a folder.** With the default `parquet_layout: auto` and `parquet_max_file_size: 512MB`, a table whose Parquet output reaches 512 MB is written as a folder `name/` of part files, not as `name.parquet`. Small tables still produce a single file. To keep the old behaviour of always writing one file, set `parquet_layout: single`.
- **Empty tables now produce a file.** Previously a table with zero rows wrote no file at all.
- **Parquet column types:** `tinyint` is now `uint8` (it was `int8`, which failed for values above 127). `datetimeoffset` columns can now be exported (as UTC timestamps), and `sql_variant` columns are supported (as text).
- **Library users:** `write_parquet()` now returns a `ParquetResult` (rows, files, layout, path) instead of a row count and raises `ParquetWriteError` when an export does not complete. `JobResult` gained `warnings`, `output_path` and `files_written`.

SQL Server destinations behave as before, with these differences:

- Tables with a `rowversion`/`timestamp` column, a `datetimeoffset` column or a `sql_variant` column can now be copied. (Previously those jobs failed, and left an empty destination table behind.)
- Three problems are now caught before any table is created, with a clear message instead of a database error: missing `VIEW DEFINITION` for computed columns or constraints, unreadable CLR-type columns, and primary key / constraint names that are already taken in the destination schema.

---

## CLI Reference

```
sqlcarbon --help
sqlcarbon run <config.yaml>       Run all jobs in the plan
sqlcarbon validate <config.yaml>  Validate config without touching any database
sqlcarbon init                    Print a sample plan.yaml to stdout
```

---

## Development

```bash
pip install -e ".[dev]"
pytest
```

The tests need no database. Every YAML example in this README is loaded by the test suite, so the documentation cannot drift from the config format.

---

## License

MIT License — see `LICENSE` for details.

---

*SQLcarbon is an open-source project initially created by **TroBeeOne LLC**.*
