# DVT-Core: Data Virtualization Tool

**DVT-Core** is a multi-source data federation and transformation platform built on dbt-core architecture. Query and transform data across multiple heterogeneous data sources with intelligent query pushdown and compute layer integration.

## Features

- 🔄 **Multi-Source Queries**: Join data from PostgreSQL, Snowflake, BigQuery, MySQL, and more in a single query
- 🧠 **Intelligent Routing**: Automatically pushes down queries when possible, uses compute layer when needed
- ⚡ **JDBC Performance**: Spark JDBC-based data transfer for maximum efficiency
- 🔧 **Familiar Workflow**: Same dbt commands, same project structure, enhanced capabilities
- 🎯 **Smart Compute Selection**: Automatically chooses between Spark Local (embedded) or Spark Cluster (distributed)
- 🎛️ **Full Control**: Override everything with `target=` and `compute=` config options
- ✅ **100% Compatible**: Works with existing dbt projects and all dbt adapters

## Quick Start

### Installation

```bash
pip install dvt-core
```

Or with uv:

```bash
uv pip install dvt-core
```

### Configure Multi-Connection Profile

```yaml
# profiles.yml
my_project:
  connections:
    postgres_prod:
      type: postgres
      host: prod-db.example.com
      port: 5432
      user: prod_user
      password: "{{ env_var('POSTGRES_PASSWORD') }}"
      database: analytics
      schema: public
      threads: 4

    snowflake_warehouse:
      type: snowflake
      account: abc123
      user: snow_user
      password: "{{ env_var('SNOWFLAKE_PASSWORD') }}"
      database: warehouse
      schema: public
      warehouse: compute_wh
      threads: 8

  default_target: snowflake_warehouse
  threads: 4
```

### Define Sources with Connections

```yaml
# models/sources.yml
sources:
  - name: postgres_data
    connection: postgres_prod
    tables:
      - name: orders
      - name: customers

  - name: snowflake_data
    connection: snowflake_warehouse
    tables:
      - name: products
```

### Create Multi-Source Model

```sql
-- models/combined_sales.sql
{{ config(
    materialized='table',
    target='snowflake_warehouse',  -- Optional: override materialization target
    compute='spark-local'          -- Optional: force compute engine
) }}

SELECT
    o.order_id,
    o.order_date,
    c.customer_name,
    p.product_name,
    o.quantity * p.price as total_amount
FROM {{ source('postgres_data', 'orders') }} o
JOIN {{ source('postgres_data', 'customers') }} c
    ON o.customer_id = c.customer_id
JOIN {{ source('snowflake_data', 'products') }} p
    ON o.product_id = p.product_id
WHERE o.order_date >= '2024-01-01'
```

### Run DVT

```bash
# Standard dbt commands work
dvt run --select combined_sales

# DVT automatically:
# 1. Analyzes query (sees postgres + snowflake sources)
# 2. Determines federated execution needed
# 3. Selects compute engine (Spark Local or Cluster based on workload)
# 4. Loads data from postgres and snowflake via adapters
# 5. Executes join in compute engine
# 6. Materializes result to target (snowflake)
```

## Architecture

```
┌─────────────┐     ┌──────────┐     ┌─────────────┐     ┌──────────┐     ┌──────────────┐
│ Source DBs  │────▶│ Adapters │────▶│  JDBC       │────▶│ Compute  │────▶│ Adapters     │
│(Postgres,   │     │  (Read)  │     │             │     │ (Spark)  │     │  (Write)     │
│ MySQL, etc.)│     │          │     │             │     │          │     │              │
└─────────────┘     └──────────┘     └─────────────┘     └──────────┘     └──────────────┘
                                                                                   │
                                                                                   ▼
                                                                           ┌──────────────┐
                                                                           │  Target DB   │
                                                                           │ (Snowflake,  │
                                                                           │  BigQuery)   │
                                                                           └──────────────┘
```

## Execution Strategies

### Pushdown (Homogeneous Sources)

When all sources come from the same connection, DVT executes the query directly on the source database:

```sql
-- All sources from same connection → Execute on source database
SELECT * FROM {{ source('postgres', 'orders') }}
JOIN {{ source('postgres', 'customers') }} USING (customer_id)
-- Executed directly in PostgreSQL (no data movement)
```

### Federated (Heterogeneous Sources)

When sources come from different connections, DVT uses the compute layer:

```sql
-- Sources from different connections → Use compute layer
SELECT * FROM {{ source('postgres', 'orders') }}
JOIN {{ source('mysql', 'products') }} USING (product_id)
-- Data loaded into Spark, join executed there
```

## CLI Commands

### Standard dbt Commands

All dbt commands work unchanged:

```bash
dvt run
dvt test
dvt build
dvt docs generate
dvt docs serve
```

### DVT-Specific Commands

Manage external Spark clusters:

```bash
# Register external Spark cluster
dvt compute register prod_cluster --master spark://master:7077

# List registered clusters
dvt compute list

# Remove cluster
dvt compute remove prod_cluster
```

## Configuration Options

### Model Configuration

```sql
{{ config(
    materialized='table',
    target='snowflake_analytics',  -- Where to write results
    compute='spark-local'          -- Force Spark Local for processing
) }}
```

### Smart Compute Selection

DVT automatically selects the optimal compute engine:

- **Spark Local**: Small/medium workloads (< 10GB), fast in-process execution
- **Spark Cluster**: Large workloads (> 10GB), distributed processing

Override with `compute='spark-local'` or `compute='spark-cluster'` in config.

## Key Principles

1. **Adapters for I/O only** - Read from sources, write to targets
2. **Compute engines for processing only** - Never materialize
3. **JDBC as universal data format** - Efficient transfer
4. **Backward compatibility** - All dbt projects work unchanged
5. **User configuration always wins** - Override any automatic decision

## Requirements

- Python 3.10+
- dbt-compatible adapters for your data sources
- PySpark (installed automatically)

## License

Apache License 2.0 (same as dbt-core)

## Acknowledgments

Built on [dbt-core](https://github.com/dbt-labs/dbt-core) architecture. DVT extends dbt's capabilities while preserving its excellent design patterns and developer experience.

## Links

- [Documentation](https://github.com/dvt-core/dvt-core#readme)
- [Issues](https://github.com/dvt-core/dvt-core/issues)
- [Repository](https://github.com/dvt-core/dvt-core)

---

**Transform data across any source, materialize to any target, with intelligent query optimization.**