Supported Warehouses

SqlCAD supports running your data warehouse on multiple backends. This guide explains how to configure and run SqlCAD for each supported dialect.

DuckDB (Local & Embedded)

DuckDB is the default dialect for SqlCAD and is excellent for local development, testing, and single-node workloads.

Configuration

No extra configuration is required. By default, SqlCAD assumes DuckDB.

Running

To run against a local DuckDB file:

sqlcad build --dialect duckdb
sqlcad schema plan --target duckdb --db-path ./warehouse.duckdb
sqlcad schema apply --db-path ./warehouse.duckdb

File Sources

DuckDB supports reading directly from local files (CSV, Parquet, JSON) using SourceGenerator logic.

DEFINE TABLE RawUsers {
    source: {
        path: "data/users_*.csv",
        format: "csv"
    } ...
}

---

BigQuery

SqlCAD fully supports Google BigQuery for scalable, serverless warehousing.

Prerequisities

1. GCP Project: You must have a Google Cloud Project.
2. Authentication: Ensure you have Application Default Credentials (ADC) set up.

   gcloud auth application-default login

3. Dependencies: Install with BigQuery extras:

   pip install "sqlcad[bigquery]"

Configuration

SqlCAD uses your active gcloud project by default. You can override this with environment variables if necessary (GOOGLE_CLOUD_PROJECT).

Running

Specify --dialect bigquery when building:

sqlcad build --dialect bigquery

Naming Conventions

• Schemas: SqlCAD Schemas (e.g., silver, gold) map to BigQuery Datasets (e.g., project_id.silver).
• Tables: Entities map to BigQuery Tables/Views.

Sources

For BigQuery, DEFINE TABLE sources typically refer to existing BigQuery tables (e.g., loaded via Fivetran/Airbyte) rather than local files.

DEFINE TABLE RawUsers {
    source: {
        table: "raw_zone.users_stream" 
    }
    columns: { ... }
}

Note: While BigQuery can query external files (GCS), SqlCAD currently treats file-based source.path as a DuckDB-primary feature. For BigQuery, use source.table pointing to an external table or native table.

---

Snowflake

SqlCAD has Beta support for Snowflake.

Prerequisites

1. Snowflake Account: You need a Snowflake account and warehouse.
2. Dependencies: Install with Snowflake extras:

   pip install "sqlcad[snowflake]"

Configuration

Set the following environment variables to authenticate:

• SNOWFLAKE_USER
• SNOWFLAKE_PASSWORD
• SNOWFLAKE_ACCOUNT (e.g., xyz123.us-east-1)
• SNOWFLAKE_WAREHOUSE
• SNOWFLAKE_DATABASE
• SNOWFLAKE_SCHEMA (Default schema, though SqlCAD manages its own schemas)

Running

sqlcad build --dialect snowflake

Type Mapping

SqlCAD automatically maps generic types to Snowflake equivalents:

• TEXT -> VARCHAR
• DOUBLE -> FLOAT
• INTEGER -> NUMBER

---

Features Matrix

Feature	DuckDB	BigQuery	Snowflake
Materialization	✅	✅	✅
Incremental Load	✅	✅	✅
SCD Type 2	✅	✅	✅
File Sources	✅	⚠️ (via External Tables)	⚠️ (via Stages)
Schema Evolution	✅	✅	✅