Metadata-Version: 2.4
Name: quackpipe
Version: 0.8.3
Summary: A configuration-driven and programmatic ETL helper for DuckDB.
License: MIT
Requires-Python: >=3.12
Description-Content-Type: text/markdown
License-File: LICENSE
Requires-Dist: pyyaml
Requires-Dist: jsonschema
Requires-Dist: duckdb>=1.4.4
Requires-Dist: pandas
Requires-Dist: python-dotenv
Provides-Extra: dev
Requires-Dist: pytest; extra == "dev"
Requires-Dist: pytest-cov; extra == "dev"
Requires-Dist: quackpipe[fixtures]; extra == "dev"
Requires-Dist: ipdb; extra == "dev"
Requires-Dist: types-pyyaml; extra == "dev"
Requires-Dist: types-jsonschema; extra == "dev"
Provides-Extra: fixtures
Requires-Dist: testcontainers[azurite,minio,mysql,postgres]==4.12.0; extra == "fixtures"
Requires-Dist: sqlalchemy; extra == "fixtures"
Requires-Dist: psycopg2-binary; extra == "fixtures"
Requires-Dist: httpx; extra == "fixtures"
Provides-Extra: lint
Requires-Dist: ruff; extra == "lint"
Requires-Dist: mypy; extra == "lint"
Provides-Extra: logging
Requires-Dist: structlog>=23.0.0; extra == "logging"
Requires-Dist: colorlog>=6.0.0; extra == "logging"
Provides-Extra: postgres
Requires-Dist: psycopg; extra == "postgres"
Provides-Extra: mysql
Requires-Dist: mysql-connector-python; extra == "mysql"
Provides-Extra: s3
Requires-Dist: pyarrow; extra == "s3"
Provides-Extra: kafka
Requires-Dist: confluent-kafka; extra == "kafka"
Provides-Extra: azure
Requires-Dist: azure-storage-blob; extra == "azure"
Dynamic: license-file

# Quackpipe

**The missing link between your Python scripts and your data infrastructure.**

Quackpipe is a powerful ETL helper library that uses **DuckDB** to create a unified, high-performance data plane for Python applications. It bridges the gap between writing raw, complex connection code and adopting a full-scale data transformation framework.

With a simple YAML configuration, you can instantly connect to multiple data sources like **PostgreSQL**, **S3**, **Azure Blob Storage**, and **SQLite**, and even orchestrate complex **DuckLake** setups, all from a single, clean Python interface.

[![codecov](https://codecov.io/github/ekiourk/quackpipe/graph/badge.svg?token=5LF2QD9MEW)](https://codecov.io/github/ekiourk/quackpipe)

## What Gap Does Quackpipe Fill?

In the modern data stack, you often face a choice:

* **Low-Level:** Write boilerplate code with multiple database drivers (`psycopg2`, `boto3`, etc.) to connect and move data manually. This is flexible but repetitive and error-prone.
* **High-Level:** Adopt a full DataOps framework like **SQLMesh** or **dbt**. These are powerful for building production-grade data warehouses but can be overkill for ad-hoc analysis, rapid prototyping, or simple scripting.

**Quackpipe provides the perfect middle ground.** It gives you the power of a unified query engine and the simplicity of a Python library, allowing you to:

* **Prototype Rapidly:** Spin up a multi-source data environment in seconds.
* **Simplify ETL Scripts:** Replace complex driver code with a single, clean `session` or a one-line `move_data` command.
* **Explore Data Interactively:** Use the built-in CLI to launch a web UI with all your sources pre-connected for instant ad-hoc querying.
* **Bridge to Production:** Automatically generate configuration for frameworks like **SQLMesh** when you're ready to graduate from a script to a versioned data model.

## Core Capabilities

* **Unified Data Access:** Query across PostgreSQL, S3, Azure, and SQLite as if they were all schemas in a single database.
* **Declarative Configuration:** Define all your data sources in one human-readable `config.yml` file.
* **Powerful ETL Utilities:** Move data between any two configured sources with the `move_data()` function.
* **Programmatic API:** Use the `QuackpipeBuilder` for dynamic, on-the-fly connection setups in your code.
* **Secure Secret Management:** Load credentials safely from `.env` files, keeping them out of your code and configuration.
* **Interactive UI:** Launch an interactive DuckDB web UI with all your sources pre-connected using a single CLI command.
* **Framework Integration:** Automatically generate a `sqlmesh_config.yml` file to seamlessly transition your project to a full DataOps framework.

## Installation

```bash
pip install quackpipe
```

Install support for the sources you need:

```bash
# Example: Install support for Postgres, S3, Azure, and the UI
pip install "quackpipe[postgres,s3,azure,ui]"
```

## Configuration

`quackpipe` uses a simple `config.yml` file to define your sources and an `.env` file to manage your secrets.

### Configuration Priority & Hierarchical Merging

Quackpipe supports loading configuration from multiple sources, merging them into a single final configuration. This allows you to have a base configuration and override specific parts for different environments (e.g., `base.yml` + `dev.yml`).

The configuration is loaded and merged in the following order (later sources override earlier ones):

1.  **Environment Variable (`QUACKPIPE_CONFIG_PATH`):** You can set this to a single path or multiple paths separated by your system's path separator (e.g., `:` on Linux/macOS, `;` on Windows).
2.  **CLI Arguments / Function Arguments:** When using the CLI, you can pass one or more file paths using `-c/--config`. When using the Python API, you can pass a list of paths to `config_path`.
3.  **Direct `configs` list:** In the Python API, passing a list of `SourceConfig` objects directly always takes the highest priority.

**Merging Logic:**
*   **Dictionaries (e.g., `sources`):** Deep merged. Keys in later configs overwrite keys in earlier ones. New keys are added.
*   **Lists (e.g., `before_all_statements`):** Replaced. If a later config defines a list, it completely replaces the list from earlier configs.
*   **Values:** Overwritten.

### `config.yml` Example

```yaml
# config.yml
sources:
  # A writeable PostgreSQL database.
  pg_warehouse:
    type: postgres
    secret_name: "pg_prod" # See Secret Management section below
    read_only: false       # Allows writing data back to this source

  # S3 with path-based scoping (Highly Recommended)
  s3_datalake:
    type: s3
    secret_name: "aws_prod"
    region: "us-east-1"
    scope: "s3://my-bucket/production-data/" # Credentials only work for this path

  # Azure using Managed Identity (Zero-Key Security)
  azure_datalake:
    type: azure
    provider: managed_identity
    account_name: "mystorageaccount"

  # A composite DuckLake source.
  my_lake:
    type: ducklake
    catalog:
      type: sqlite
      path: "/path/to/lake_catalog.db"
      # automatic_migration: true   # one-off, see "Upgrading DuckDB with existing DuckLakes"
    storage:
      type: local
      path: "/path/to/lake_storage/"
```

### Upgrading DuckDB with existing DuckLakes

Quackpipe supports the DuckDB 1.4 LTS line and the current stable line, and its CI runs the test suite against both. The DuckLake **catalog format**, however, is tied to the DuckDB version you install: DuckDB 1.4 ships DuckLake 0.3, DuckDB 1.5.2 and later ship DuckLake 1.0. Upgrading quackpipe alone never touches a catalog. Upgrading DuckDB does.

When a DuckDB 1.5+ session opens a lake created under DuckDB 1.4, quackpipe stops with a `DuckLakeMigrationError` instead of touching the catalog. To migrate, set the opt-in flag on the catalog for one run:

```yaml
sources:
  my_lake:
    type: ducklake
    catalog:
      type: postgres
      secret_name: lake_catalog
      automatic_migration: true   # remove again after the first successful session
    storage:
      type: s3
      path: "s3://my-bucket/lake/"
```

Before you do, note that the migration is **one-way**: once migrated, clients still running DuckDB 1.4 can no longer open that lake. Data files are not modified; only the catalog is.

1. Back up the catalog: copy the catalog file, or `pg_dump` the catalog database.
2. Upgrade **every** client that uses the lake (services, notebooks, SQLMesh runs) to the same DuckDB version.
3. Run one quackpipe session with `automatic_migration: true`, then remove the flag.

If you need to stay on DuckDB 1.4 for now, pin it (`duckdb<1.5`) and leave the flag out; quackpipe keeps generating exactly the same SQL as before. Setting the flag on DuckDB older than 1.5.2 is rejected at validation time, because that DuckLake version does not know the option.

### Encrypted Databases

Quackpipe supports DuckDB's native **AES-256-GCM** encryption for local and remote databases. You can provide an `encryption_key` directly in your configuration for SQLite, PostgreSQL, and MySQL sources:

```yaml
sources:
  secure_sqlite:
    type: sqlite
    path: "path/to/encrypted.db"
    encryption_key: "your-32-byte-key-here"
```

### Cloud Security & Hardening (DuckDB 1.4+)

Quackpipe leverages DuckDB 1.4's Secrets Manager for enhanced cloud security:

*   **Scoping:** Use the `scope` parameter to restrict credentials to a specific bucket or path.
*   **Credential Chains:** Set `provider: credential_chain` (Azure) or `use_credential_chain: true` (S3) to use your environment's native authentication (e.g., AWS IAM Roles, Azure CLI login) without storing keys in your configuration.
*   **Temporary Credentials:** S3 sources now support `session_token` for use with AWS STS tokens.

### Secret Management with `.env`

Quackpipe uses a `secret_name` in the config to refer to a bundle of credentials. These are loaded from `.env` files using a simple prefix convention: `SECRET_NAME_KEY`.

Create an `.env` file in your project root:

```dotenv
# .env

# Secrets for secret_name: "pg_prod"
PG_PROD_HOST=db.example.com
PG_PROD_USER=myuser
PG_PROD_PASSWORD=mypassword
PG_PROD_DATABASE=production

# Secrets for secret_name: "aws_prod"
AWS_PROD_ACCESS_KEY_ID=YOUR_AWS_ACCESS_KEY
AWS_PROD_SECRET_ACCESS_KEY=YOUR_AWS_SECRET_KEY

# Secrets for secret_name: "azure_prod"
AZURE_PROD_CONNECTION_STRING="DefaultEndpointsProtocol=https..."
```

**Multiple Environment Files:**
You can load secrets from multiple files. This is useful for separating default/shared configuration from local secrets.

```bash
# Load base.env first, then override with .env.local
quackpipe ui --env-file base.env .env.local
```

The "last one wins" rule applies: variables in later files will overwrite those in earlier files.

## Usage Highlights

### 1. Interactive Querying with `session`

Need to join a CSV in S3 with a table in Postgres? `quackpipe` makes it trivial.

```python
import quackpipe

# quackpipe automatically loads your .env file
with quackpipe.session(config_path="config.yml", env_file=".env") as con:
    df = con.execute("""
        SELECT u.name, o.order_total
        FROM pg_warehouse.users u
        JOIN read_parquet('s3://my-bucket/orders/*.parquet') o ON u.id = o.user_id
        WHERE u.signup_date > '2024-01-01';
    """).fetchdf()

    print(df.head())
```

### 2. One-Line Data Movement with `move_data`

Archive old records from your production database to your data lake with a single command.

```python
from quackpipe.etl_utils import move_data

move_data(
    config_path="config.yml",
    env_file=".env",
    source_query="SELECT * FROM pg_warehouse.logs WHERE timestamp < '2024-01-01'",
    destination_name="s3_datalake",
    table_name="logs_archive_2023"
)
```

**Sync data using `merge` (upsert) mode:**

```python
# Update existing records and insert new ones based on a primary key
move_data(
    source_query="SELECT * FROM new_data",
    destination_name="pg_warehouse",
    table_name="users",
    mode="merge",
    primary_key="user_id"
)
```

### 3. Instant Data Exploration with the CLI

Launch a web browser UI with all your sources attached and ready for ad-hoc queries.

```bash
# This command reads your config.yml and .env file
quackpipe ui

# Or connect to specific sources
quackpipe ui pg_warehouse s3_datalake
```

### 4. Validate Your Configuration

Quackpipe provides a two-layer validation system to catch errors before you run your ETL scripts:

1.  **Structural Validation:** Checks your YAML files against the built-in JSON schema (e.g., "is the port an integer?").
2.  **Semantic (Pre-flight) Validation:** Checks if the configuration makes sense for the source type (e.g., "does this Postgres source have a host?").

By default, the `validate` command only performs structural and static semantic checks:

```bash
# Validate the default config.yml
quackpipe validate

# Or validate multiple files (they will be merged)
quackpipe validate --config base.yml dev.yml
```

#### Deep Validation (Secret Resolution)

If you use `secret_name` in your config, you can perform a "deep check" to ensure the required environment variables are actually set:

```bash
# Check that all required secrets for your sources are present in the environment
quackpipe validate --resolve-secrets

# You can also specify an .env file to load for the check
quackpipe validate --resolve-secrets --env-file .env.prod
```

If the configuration is valid, you'll see a success message:
```
✅ Configuration from '['base.yml', 'dev.yml']' is valid.
```

If it's invalid, `quackpipe` will provide a descriptive error:
```
❌ Configuration is invalid.
   Reason: Postgres source requires 'host', 'database' (Checked both config and environment variables for secret name: 'pg_prod')
```

### 5. Inspect Merged Configuration

When using multiple configuration files, it can be helpful to see the final result after merging.

```bash
quackpipe preview-config -c base.yml dev.yml
```

This will print the full, merged YAML configuration to the console.

## Development

To set up the development environment for `quackpipe`, we recommend using [uv](https://github.com/astral-sh/uv).

1.  **Clone the repository:**
    ```bash
    git clone https://github.com/ekiourk/quackpipe.git
    cd quackpipe
    ```

2.  **Install dependencies:**
    This command will create a virtual environment and install the package in editable mode along with all optional dependencies (including dev tools, fixtures, linting, etc.):
    ```bash
    uv sync --all-extras
    ```

3.  **Run tests:**
    Validate your setup by running the test suite with `pytest`:
    ```bash
    uv run pytest
    ```
    The integration tests use [Testcontainers](https://testcontainers-python.readthedocs.io/) and need a running Docker daemon.

    **S3 test image:** MinIO Inc. no longer publishes container images and removed `minio/minio` from Docker Hub. The S3 tests therefore run against [PGSTY Silo](https://github.com/pgsty/silo), a maintained, API-compatible MinIO fork, pinned in `src/quackpipe/test_utils/minio_fixtures.py`. To use a different image (for example a mirror inside your network), set:
    ```bash
    export QUACKPIPE_TEST_MINIO_IMAGE="quay.io/minio/minio:RELEASE.2025-09-07T16-13-09Z"
    uv run pytest
    ```

