Feature Request: Dynamic Schema Configuration in DuckLake Secrets

[djouallah]

Problem Statement

DuckDB currently does not support dynamic schema creation using variables in DDL statements (e.g., CREATE SCHEMA IF NOT EXISTS ${schema_name}), which creates a significant limitation for runtime-configurable data pipelines managed through the DuckDB CLI.

Use Case

I'm managing a multi-environment data pipeline using DuckDB CLI where configuration is passed at runtime via environment variables. This allows the same pipeline code to operate across development, staging, and production environments with different lakehouse paths, metadata locations, and target schemas.

However, the schema name must currently be hardcoded in SQL:

CREATE SCHEMA IF NOT EXISTS bronze;  -- Cannot be parameterized
USE bronze;

This breaks the configuration-driven architecture and requires either:

• Maintaining separate SQL files per environment
• Generating SQL dynamically outside of DuckDB (defeating the purpose of pure SQL pipelines)
• Committing environment-specific code

Proposed Solution

Allow schema configuration as part of the DuckLake secret definition, similar to how METADATA_PATH and DATA_PATH are currently configurable:

CREATE OR REPLACE SECRET my_secret (
    TYPE ducklake,
    METADATA_PATH getvariable('METADATA_PATH'),
    DATA_PATH getvariable('PATH_ROOT') || '/Tables',
    SCHEMA getvariable('default_schema')  -- New parameter
);

The DuckLake extension would automatically create and use the specified schema when attaching the database.

Benefits & Unblocked Use Cases

This enhancement would unlock:

1. True environment portability - Single SQL codebase deployable across unlimited environments
2. Multi-tenant pipelines - Same pipeline code processing data for different tenants/schemas based on runtime configuration
3. CI/CD integration - Automated testing with dynamic schema isolation without code generation
4. Simplified deployment - Environment configuration via environment variables only, no code changes
5. Better separation of concerns - Infrastructure configuration (schemas) separated from business logic (SQL)

Alternative Workarounds (All Suboptimal)

• External SQL generation (Python/shell scripts) - defeats pure SQL pipeline goals
• Environment-specific SQL files - maintenance nightmare
• Hardcoded conditional logic - doesn't scale across environments

This feature aligns with DuckDB's philosophy of embedding configuration into data source connections (like secrets do for authentication/paths) and would significantly enhance its utility for production data engineering workflows.