Tutorial: From Zero to Dashboard

This tutorial will guide you through building a data warehouse for a sample Sales dataset using SqlCAD. By the end, you will have a fully compiled "Gold" layer ready for analytics.

Prerequisites

• Python 3.13+
• uv installed.
• A basic understanding of SQL.

1. Setup

Create a new directory for your project:

mkdir my_project
cd my_project
mkdir models

2. Define Sources (Bronze/Base)

First, we need to tell SqlCAD about our raw data. Create a file models/sources.sqlcad.

We define a TABLE block for each raw file. We can point directly to CSV or Parquet files, allowing SqlCAD to handle data loading automatically.

DEFINE TABLE RawOrders {
    source: {
        path: "data/orders.csv",
        format: "csv"
    },
    columns: {
        OrderID: INTEGER,
        CustomerID: TEXT,
        OrderDate: TIMESTAMP
    }
}

DEFINE TABLE RawCustomers {
    source: {
        path: "data/customers.csv",
        format: "csv"
    },
    columns: {
        CustomerID: TEXT,
        CompanyName: TEXT,
        ContactName: TEXT
    }
}

3. Initialize the Warehouse

SqlCAD is a compiler, meaning it generates SQL for you to run. The build command (which we run in step 5) generates an init.sql file in the output directory.

You can run this SQL script against your database to create all necessary schemas, tables, and views.

duckdb my_warehouse.db < output/init.sql

4. Define Entities (Silver)

Entities represent the "clean" business objects. In the Silver layer, we standardize names and define Primary Keys.

Create models/orders.sqlcad:

DEFINE ENTITY Orders
    primary_key: OrderID
    type: fact
    temporal:
        event_time_col: OrderDate
AS (
    SELECT 
        OrderID,
        CustomerID,
        OrderDate
    FROM RawOrders
)

Create models/customers.sqlcad. Note that Customers is a dimension (default type), so it's stateful.

DEFINE ENTITY Customers
    primary_key: CustomerID
AS (
    SELECT * FROM RawCustomers
)

5. Define Relationships

This is where SqlCAD shines. Instead of writing joins in every report, we define them once.

Create models/graph.sqlcad:

DEFINE JOIN customer_link ON
    type: many_to_one
    from: Orders
    to: Customers
AS (
    LEFT JOIN Customers c ON c.CustomerID = Orders.CustomerID
);

Note: The AS (...) block defines how to join. SqlCAD will use this logic whenever an Order needs to access Customer attributes.

6. Create a Mart (Gold)

Now we want a "Dashboard" table that contains Order details AND the Customer Name. In standard SQL, you'd write the join manually. In SqlCAD, you just select fields from the related entity.

Create models/marts.sqlcad:

DEFINE REPORT Dashboard
    columns:
        OrderID: INTEGER
        OrderDate: TIMESTAMP
        CompanyName: TEXT
        ContactName: TEXT
    temporal:
        event_time_col: OrderDate
AS (
    SELECT
        o.OrderID,
        o.OrderDate,
        -- Access column from the related Customers entity
        c.CompanyName, 
        c.ContactName
    FROM Orders o
    -- We just mention the entity we want. SqlCAD finds the path.
    LEFT JOIN Customers c 
);

7. Build

Run the compiler:

sqlcad build --directory models --output output

8. Inspect Output

Check output/gold/dashboard.sql. You will see that SqlCAD has generated the full SQL, injecting the join logic we defined in step 5.

SELECT
  o.OrderID,
  o.OrderDate,
  c.CompanyName,
  c.ContactName
FROM Orders AS o
LEFT JOIN Customers AS c
  ON c.CustomerID = o.CustomerID

Congratulations! You've built your first SqlCAD model.