Connections

Connection profiles define how Daana connects to your target database: the data warehouse where source data lives and transformed business entities are stored.

Connection Profile Structure

A connections file defines named profiles for different environments:

connections:
  dev:
    type: postgresql
    host: localhost
    port: 5432
    user: dev
    password: devpass
    database: customerdb
    sslmode: disable
    target_schema: daana_dw

  production:
    type: postgresql
    host: prod.example.com
    port: 5432
    user: "${DW_USER}"
    password: "${DW_PASSWORD}"
    database: analytics
    sslmode: require
    target_schema: daana_dw

Tip: Generate a template with daana-cli generate connections -o connections.yaml to get started quickly.

Environment Variables

Use environment variable syntax ${VAR_NAME} for sensitive values. Daana automatically interpolates these at runtime:

connections:
  production:
    type: postgresql
    host: "${PROD_DB_HOST}"
    user: "${PROD_DB_USER}"
    password: "${PROD_DB_PASSWORD}"
    database: analytics

Set the variables before running commands:

export PROD_DB_HOST="prod.example.com"
export PROD_DB_USER="daana_svc"
export PROD_DB_PASSWORD="secure-password"

daana-cli deploy --connections connections.yaml --connection production

Security: Never commit passwords or credentials to version control. Always use environment variables for sensitive values.

Supported Database Types

Daana supports multiple data warehouse platforms. Each has specific configuration requirements.

PostgreSQL (Default and Recommended)

connections:
  dev:
    type: "postgresql"
    host: "localhost"
    port: 5432
    user: "dev"
    password: "devpass"
    database: "customerdb"
    sslmode: "disable"
    target_schema: "daana_dw"

BigQuery

connections:
  bigquery-prod:
    type: "bigquery"
    project_id: "my-gcp-project"
    dataset: "daana_dw"
    location: "US"
    credentials_file: "${BQ_CREDENTIALS_FILE}"

  # Cross-project: source data in a different GCP project
  bigquery-cross-project:
    type: "bigquery"
    project_id: "target-analytics"              # Target project
    source_project_id: "data-landing-zone"       # Source project
    dataset: "daana_dw"
    location: "EU"
    credentials_file: "${BQ_CREDENTIALS_FILE}"   # SA needs dataViewer on source

Microsoft SQL Server

connections:
  mssql-prod:
    type: "mssql"
    host: "sqlserver.example.com"
    port: 1433
    user: "${MSSQL_USER}"
    password: "${MSSQL_PASSWORD}"
    database: "DataWarehouse"
    encrypt: true
    trust_server_certificate: false
    target_schema: "daana_dw"

Oracle

connections:
  oracle-prod:
    type: "oracle"
    host: "oracle.example.com"
    port: 1521
    user: "${ORACLE_USER}"
    password: "${ORACLE_PASSWORD}"
    service_name: "ORCLPDB1"
    target_schema: "DAANA_DW"

Snowflake

connections:
  snowflake-prod:
    type: "snowflake"
    account: "xy12345.us-east-1.aws"
    user: "${SNOWFLAKE_USER}"
    password: "${SNOWFLAKE_PASSWORD}"
    warehouse: "DAANA_WH"
    database: "ANALYTICS"
    schema: "DAANA_DW"
    role: "TRANSFORMER"

DuckDB

connections:
  duckdb-local:
    type: "duckdb"
    database: "./warehouse.duckdb"
    target_schema: "daana_dw"
    stage_schema: "daana_stage"
    metadata_schema: "daana_metadata"

Using Connection Profiles

Connection profiles are used with commands that interact with your data warehouse:

# Install framework in your target database
daana-cli install --connections connections.yaml --connection dev

# Deploy transformations
daana-cli deploy --connections connections.yaml --connection production

# Execute workflows
daana-cli execute --connections connections.yaml --connection production

Profile Selection

When multiple profiles exist:

# Explicitly select a profile
daana-cli check connections --connection dev

# If only one profile exists, it's auto-selected
daana-cli check connections

Validating Connection Profiles

Before deploying, validate your connection profiles to catch configuration errors:

# Validate all profiles in the file
daana-cli check connections

# Validate a specific profile
daana-cli check connections --connection dev

# Strict mode (treat warnings as errors)
daana-cli check connections --connection prod --strict

# JSON output for CI/CD
daana-cli check connections --format json

What Validation Checks

The validator verifies:

Example Validation Output

Valid profile with warnings:

✓ Profile 'dev' valid
  Type: postgresql

⚠  Profile 'dev' warnings:
  • [dev] root.sslmode='disable' is insecure for production. Use 'require' or 'verify-full'

Invalid profile:

✘ Profile 'prod' errors:
  • [prod] root: PostgreSQL requires field 'port'
  • [prod] root: PostgreSQL requires field 'user'
  • [prod] root: PostgreSQL requires field 'database'

Generating a Template

Generate a connections template with examples for all supported dialects:

daana-cli generate connections -o connections.yaml

This creates a well-commented template with:

• Development PostgreSQL profile (uncommented, ready to use)
• Production PostgreSQL example (commented)
• Examples for BigQuery, MSSQL, Oracle, Snowflake (commented)

Complete Example

Here's a complete connections file for a multi-environment setup:

# Connection profiles for my-project
connections:

  # Development - local Docker PostgreSQL
  dev:
    type: postgresql
    host: localhost
    port: 5432
    user: dev
    password: devpass
    database: customerdb
    sslmode: disable
    target_schema: daana_dw

  # Staging - AWS RDS PostgreSQL
  staging:
    type: postgresql
    host: staging-db.example.com
    port: 5432
    user: "${STAGING_DB_USER}"
    password: "${STAGING_DB_PASSWORD}"
    database: analytics_staging
    sslmode: require
    target_schema: daana_dw

  # Production - AWS RDS PostgreSQL
  production:
    type: postgresql
    host: prod-db.example.com
    port: 5432
    user: "${PROD_DB_USER}"
    password: "${PROD_DB_PASSWORD}"
    database: analytics
    sslmode: verify-full
    target_schema: daana_dw
    connect_timeout: 30
    pool_max_conns: 20

Cross-Project BigQuery

When your source data lives in a different GCP project than where Daana writes output, use the source_project_id field:

connections:
  cross-project:
    type: bigquery
    project_id: target-analytics              # Where Daana creates target tables
    source_project_id: data-landing-zone      # Where source data lives
    dataset: daana_dw
    location: EU
    credentials_file: "${BQ_CREDENTIALS_FILE}"  # SA needs dataViewer on source project

When source_project_id is set, generated SQL uses three-part references for source tables:

-- Without source_project_id (two-part, resolves to target project):
FROM `post_staging.ps__order_events__enriched`

-- With source_project_id (three-part, reads from source project):
FROM `data-landing-zone.post_staging.ps__order_events__enriched`

Target tables (in daana_dw) always use the target project; only source reads are affected.

IAM Setup

The service account in credentials_file needs bigquery.dataViewer on the source project:

gcloud projects add-iam-policy-binding data-landing-zone \
  --member="serviceAccount:sa@target-analytics.iam.gserviceaccount.com" \
  --role="roles/bigquery.dataViewer"

No second credentials file is needed; a single service account with cross-project IAM works. The service account must already have BigQuery Data Editor and Job User roles on the target project (see the BigQuery section above).

Note: Source dataset names (e.g., post_staging) come from your mapping YAML table references (only the project is overridden by source_project_id). The dataset field in the connection profile is the target dataset where Daana creates target tables.

Note: When source_project_id is omitted, behavior is unchanged. Two-part references resolve against project_id as before.

Best Practices

Next Steps

• Define your workflow to orchestrate transformations
• Create mappings to define data transformations
• Follow the tutorial for a complete end-to-end example