### Debug dbt Profile Setup

Source: https://github.com/quarylabs/quary/blob/main/rust/dbt-converter/src/input/README.md

Verify that the dbt profile is correctly configured to connect to the data warehouse. This command checks the connection and settings.

```bash
dbt debug
```

--------------------------------

### Initialize Quary Project CLI

Source: https://context7.com/quarylabs/quary/llms.txt

Creates a new Quary project directory with sample data and configuration files. Supports default DuckDB initialization or specific database types like SQLite. The generated structure includes configuration, SQL models, seed data, tests, and setup scripts.

```bash
# Create new project directory
mkdir my_analytics_project
cd my_analytics_project

# Initialize with DuckDB demo project (default)
quary init

# Initialize with SQLite project
quary init --type sqlite

# Project structure created:
# ├── quary.yaml              # Database connection config
# ├── models/                 # SQL transformation files
# │   ├── shifts.sql
# │   ├── shifts.yaml         # Model metadata and tests
# ├── data/                   # Seed CSV files
# ├── tests/                  # Custom SQL test queries
# ├── pre_run_script.sql      # Setup scripts

```

--------------------------------

### Install Quary CLI via Curl (Linux/Mac)

Source: https://github.com/quarylabs/quary/blob/main/README.md

Installs the Quary command-line interface on Linux and macOS systems by downloading and executing an installation script via curl. This method is suitable for users who prefer script-based installations.

```shell
curl -fsSL https://raw.githubusercontent.com/quarylabs/quary/main/install.sh | bash
```

--------------------------------

### Install Quary CLI via Homebrew

Source: https://github.com/quarylabs/quary/blob/main/README.md

Installs the Quary command-line interface using the Homebrew package manager. This is a straightforward method for macOS users to get the latest version of Quary.

```shell
brew install quarylabs/quary/quary
```

--------------------------------

### Define SQL Models with Cross-Model References

Source: https://context7.com/quarylabs/quary/llms.txt

Creates SQL transformation models where models can reference other defined models using the `q.model_name` syntax. This example demonstrates joining data from `stg_shifts` and `shift_hours` models to derive shift start and end times.

```sql
-- models/shifts.sql
WITH shifts AS (
    SELECT
        employee_id,
        shift_date,
        shift
    FROM q.stg_shifts
),

shift_details AS (
    SELECT
        shift AS shift_name,
        start_time,
        end_time
    FROM q.shift_hours
)

SELECT
    s.employee_id,
    s.shift,
    CAST(s.shift_date AS TIMESTAMP)
    + CAST(sd.start_time AS INTERVAL) AS shift_start,
    CAST(s.shift_date AS TIMESTAMP) + CAST(sd.end_time AS INTERVAL) AS shift_end
FROM shifts AS s
INNER JOIN shift_details AS sd
    ON s.shift = sd.shift_name

```

--------------------------------

### Define Charts with YAML in Quary

Source: https://context7.com/quarylabs/quary/llms.txt

Create data visualizations by referencing models using YAML configuration. This example defines a bar chart showing shifts by month, specifying aggregations, grouping, and display settings.

```yaml
# models/shifts_by_month_bar.chart.yaml
description: Charting shifts by month
tags:
  - monthly
config:
  aggregates: {}
  columns:
    - total_shifts
  columns_config: {}
  expressions: {}
  filter: []
  group_by:
    - shift_month
  plugin: Y Bar
  plugin_config: {}
  settings: true
  sort:
    - - shift_month
      - asc
  split_by: []
  theme: Pro Light
  version: 2.10.0
reference:
  name: shifts_by_month
```

--------------------------------

### Define External Data Sources with Documentation and Tests

Source: https://context7.com/quarylabs/quary/llms.txt

Declares external database tables as sources within Quary projects, including documentation and data quality tests. This YAML example defines the `raw_employees` source with descriptions for the source itself and its columns, along with `not_null` and `unique` tests.

```yaml
# models/schema.yaml
sources:
  - name: raw_employees
    description: Raw employee data from HR system
    path: public.raw_employees
    columns:
      - name: employee_id
        description: Unique identifier for employees
        tests:
          - type: not_null
          - type: unique
      - name: employee_name
        description: Full name of employee
      - name: department
        description: Department assignment

```

--------------------------------

### Create Custom SQL Tests in Quary

Source: https://context7.com/quarylabs/quary/llms.txt

Define custom data quality tests using SQL queries. These queries should return no rows when the test passes. This example checks if the first shift is always before the last shift for each employee.

```sql
-- tests/shifts__first_is_always_before_last.sql
-- Test that first shift is always before last shift for each employee
SELECT c.employee_id
FROM q.shifts_summary AS c
WHERE c.first_shift > c.last_shift
```

--------------------------------

### Define Model Schema and Data Quality Tests in YAML

Source: https://context7.com/quarylabs/quary/llms.txt

Documents Quary models, including descriptions, column metadata, and data quality tests, using YAML files. This example defines tests for the `shifts` model, such as `not_null` and `relationship` tests for specific columns.

```yaml
# models/shifts.yaml
models:
  - name: shifts
    description: Transformation for shift data with derived start/stop times
    columns:
      - name: employee_id
        tests:
          - type: not_null
          - type: relationship
            info:
              model: stg_employees
              column: employee_id
      - name: shift
        tests:
          - type: not_null
          - type: relationship
            info:
              column: shift
              model: shift_hours
      - name: shift_start
        description: Start timestamp of the shift
        tests:
          - type: not_null
      - name: shift_end
        description: End timestamp of the shift
        tests:
          - type: not_null

```

--------------------------------

### Quary CLI Basic Usage Commands

Source: https://github.com/quarylabs/quary/blob/main/js/packages/quary-extension/README.md

Demonstrates fundamental commands for using the Quary CLI to initialize a project, compile, build, and test data models. Assumes a DuckDB demo project.

```shell
mkdir example \
cd example \
quary init \
quary compile \
quary build \
quary test -s
```

--------------------------------

### Serve Project Documentation

Source: https://github.com/quarylabs/quary/blob/main/rust/dbt-converter/src/input/README.md

Serve the generated dbt project documentation locally, allowing you to view it in a web browser. This is useful for exploring the project's structure and data models.

```bash
dbt docs serve
```

--------------------------------

### Initialize and Build a Quary Project

Source: https://github.com/quarylabs/quary/blob/main/README.md

Demonstrates the basic workflow for setting up and compiling a Quary project. It includes initializing a project, compiling to validate structure, building the model against a database, and running tests.

```shell
mkdir example # create an empty project folder
cd example
quary init    # initialize DuckDB demo project with sample data
quary compile # validate the project structure and model references without database
quary build   # build and execute the model views/seeds against target database
quary test -s   # run defined tests against target database
```

--------------------------------

### Run Quary Models and Tests via VS Code

Source: https://github.com/quarylabs/quary/blob/main/rust/core/src/init/README.md

Execute all Quary models or tests using the VS Code extension's command palette. Ensure models are run before tests for successful test execution. Select 'sqlite-in-browser' for database configuration during the first run.

```bash
Cmd + Shift + P or Ctrl + Shift + P

QUARY: Run
QUARY: Test
```

--------------------------------

### Run Models and Tests with Quary CLI

Source: https://github.com/quarylabs/quary/blob/main/rust/core/src/init_duckdb/README.md

Commands to execute all models or tests within a Quary project using the VS Code extension. Requires models to be run before tests. Use 'sqlite-in-browser' for database configuration.

```shell
QUARY: Run
QUARY: Test
```

--------------------------------

### Render Tables and Views with VS Code Extension

Source: https://github.com/quarylabs/quary/blob/main/rust/core/src/init/README.md

Display all tables and views within the database using the 'QUARY: Render Tables' command in the VS Code extension.

```bash
Cmd + Shift + P or Ctrl + Shift + P

QUARY: Render Tables
```

--------------------------------

### Build Models with Quary CLI

Source: https://context7.com/quarylabs/quary/llms.txt

Execute SQL transformations to create views or tables in the target database. The `quary build` command processes models in dependency order. Use `--dry-run` to preview SQL without execution.

```bash
# Build all models in dependency order
quary build

# Build with dry-run to see SQL without executing
quary build --dry-run
```

--------------------------------

### Generate Project Documentation

Source: https://github.com/quarylabs/quary/blob/main/rust/dbt-converter/src/input/README.md

Generate documentation for the dbt project, including model descriptions, tests, and lineage. This process creates metadata that can be served and viewed.

```bash
dbt docs generate
```

--------------------------------

### Add a New Table to the Database using SQL

Source: https://github.com/quarylabs/quary/blob/main/rust/core/src/init_duckdb/README.md

SQL statement to create a new table named 'employee_band_table' with specified columns. This table can then be registered as a source in the Quary project.

```sql
CREATE TABLE IF NOT EXISTS employee_band_table (employee_id INTEGER, band_id INTEGER);
```

--------------------------------

### Render Tables and Views with Quary VS Code Extension

Source: https://github.com/quarylabs/quary/blob/main/rust/core/src/init_duckdb/README.md

Command to display all tables and views present in the database. This command is useful for verifying data and schema after running models.

```shell
QUARY: Render Tables
```

--------------------------------

### Quickly Render a Specific Model with Quary VS Code Extension

Source: https://github.com/quarylabs/quary/blob/main/rust/core/src/init_duckdb/README.md

Command to render a single model on demand. This is helpful during development for quick previews and debugging of individual model outputs.

```shell
QUARY: Render Model
```

--------------------------------

### Quickly Render a Specific Model in VS Code

Source: https://github.com/quarylabs/quary/blob/main/rust/core/src/init/README.md

Efficiently render a single model during development using the 'QUARY: Render Model' command. This command will prompt the user to select the desired model for rendering.

```bash
Cmd + Shift + P or Ctrl + Shift + P

QUARY: Render Model
```

--------------------------------

### Execute Snapshot Capture with Quary CLI

Source: https://context7.com/quarylabs/quary/llms.txt

Execute the snapshot capture process defined in YAML configuration files. Use `quary snapshot --dry-run` to preview the generated SQL for snapshot creation without actually running it.

```bash
# Execute snapshot capture
quary snapshot

# Dry-run to preview snapshot SQL
quary snapshot --dry-run
```

--------------------------------

### Generate Sources from Database with Quary CLI

Source: https://context7.com/quarylabs/quary/llms.txt

Automatically generate source definitions by introspecting existing database tables. Redirecting the output to a file (`sources.yaml`) saves the generated source configurations.

```bash
# List all tables and generate source YAML
quary generate-sources > sources.yaml
```

--------------------------------

### Run Data Quality Tests with Quary CLI

Source: https://context7.com/quarylabs/quary/llms.txt

Execute data quality tests defined in model schemas and custom SQL files. `quary test` provides a summary, while `quary test -s` offers detailed output for failures, highlighting specific issues like duplicate entries or failed assertions.

```bash
# Run all tests with summary output
quary test

# Run tests with detailed output for failures
quary test -s
```

--------------------------------

### Define a New Source in Quary Project Configuration

Source: https://github.com/quarylabs/quary/blob/main/rust/core/src/init_duckdb/README.md

YAML configuration to define 'employee_band' as a new source, pointing to the 'employee_band_table' in the database. This source can then be utilized in Quary models and tests.

```yaml
sources:
  - name: employee_band
    path: employee_band_table
```

--------------------------------

### Create Snapshots with YAML and Quary CLI

Source: https://context7.com/quarylabs/quary/llms.txt

Capture point-in-time snapshots of changing data for historical analysis. Define snapshot configurations in YAML, specifying the unique key and strategy (e.g., timestamp-based updates). The `quary snapshot` command executes the capture process.

```yaml
# models/customer_snapshot.yaml
snapshots:
  - name: customer_snapshot
    description: Daily snapshot of customer data
    unique_key: customer_id
    strategy:
      timestamp:
        updated_at: updated_timestamp
```

--------------------------------

### Load Demo Data with dbt Seed

Source: https://github.com/quarylabs/quary/blob/main/rust/dbt-converter/src/input/README.md

Load the CSV data files (customers, orders, payments) as tables in the target data warehouse. This step is typically not required for production dbt projects where raw data is assumed to be pre-loaded.

```bash
dbt seed
```

--------------------------------

### Execute RPC Calls with Quary CLI

Source: https://context7.com/quarylabs/quary/llms.txt

Perform low-level database operations via the RPC interface for programmatic control. Commands include listing tables, querying data, listing columns, and executing arbitrary SQL statements.

```bash
# List all tables in database
quary rpc ListTables '{}'

# Query database and return results
quary rpc Query '{"query": "SELECT COUNT(*) FROM q.shifts"}'

# List columns for specific table
quary rpc ListColumns '{"table_name": "shifts"}'

# Execute statement without returning results
quary rpc Execute '{"query": "CREATE INDEX idx_employee ON q.shifts(employee_id)"}'
```

--------------------------------

### Configure Database Connections in quary.yaml

Source: https://context7.com/quarylabs/quary/llms.txt

Defines database connection settings within the `quary.yaml` configuration file. Supports various database types like DuckDB, PostgreSQL, BigQuery, and Snowflake, allowing for specific configurations such as schemas, project IDs, and environment variables.

```yaml
# DuckDB in-memory configuration
duckdbInMemory: {}
pre_run_scripts:
  - pre_run_script.sql

---
# PostgreSQL configuration
postgres:
  schema: public

---
# BigQuery configuration
bigQuery:
  projectId: "my-gcp-project"
  datasetId: "analytics_dataset"
vars:
  - name: environment
    value: production

---
# Snowflake configuration
snowflake:
  accountUrl: "https://myaccount.snowflakecomputing.com"
  clientId: "my_client_id"
  clientSecret: "${SNOWFLAKE_SECRET}"
  role: "ANALYST_ROLE"
  database: "ANALYTICS_DB"
  schema: "PUBLIC"
  warehouse: "COMPUTE_WH"

```

--------------------------------

### Run dbt Models

Source: https://github.com/quarylabs/quary/blob/main/rust/dbt-converter/src/input/README.md

Execute all the dbt models defined in the project. This transforms the raw data into the desired analytical models. May require SQL adjustments for specific database flavors.

```bash
dbt run
```

--------------------------------

### Navigate to Jaffle Shop Directory

Source: https://github.com/quarylabs/quary/blob/main/rust/dbt-converter/src/input/README.md

Change the current directory to the 'jaffle_shop' project folder. This is a prerequisite for running dbt commands within the project.

```bash
cd jaffle_shop
```

--------------------------------

### Generate Raw Shifts Data in Go

Source: https://github.com/quarylabs/quary/blob/main/rust/core/src/init/README.md

This Go program generates a CSV string representing shift data for employees and stores. It uses the 'time' and 'math/rand' packages. The output is a formatted CSV string that can be printed to the console.

```go
package main

import (
	"fmt"
	"math/rand"
	"time"
)

type Shift struct {
	EmployeeID int
	StoreID    int
	Date       time.Time
	Shift      string
}

func main() {
	rand.Seed(42)

	// Start from the beginning of 2022
	startDate := time.Date(2022, time.January, 1, 0, 0, 0, 0, time.UTC)
	// End at the end of June 2022
	endDate := time.Date(2022, time.July, 1, 0, 0, 0, 0, time.UTC)

	// shifts is a slice of Shifts
	var shifts []Shift

	// For the managers (1-4) they work Monday to Friday every week, morning and afternoon
	// Iterate over the days
	managersToStore := map[int]int{
		4: 1,
		5: 2,
		6: 3,
		7: 4,
	}
	for manager, store := range managersToStore {
		for d := startDate; d.Before(endDate); d = d.AddDate(0, 0, 1) {
			// Check if the day is Monday to Friday
			if d.Weekday() >= time.Monday && d.Weekday() <= time.Friday {
				// Print the date in day-month-year format
				shifts = append(shifts, Shift{manager, store, d, "morning"})
				shifts = append(shifts, Shift{manager, store, d, "afternoon"})
			}
		}
	}

	// For the employees (5-10) they work 3 days a week, morning and afternoon
	// Iterate over the days
	employeesToStoreThatWorkIn := map[int][]int{
		1:  []int{1},
		2:  []int{2, 3},
		3:  []int{4, 1, 2},
		8:  []int{3, 4},
		9:  []int{2},
		10: []int{1},
	}
	for employee, stores := range employeesToStoreThatWorkIn {
		// Iterate over the days
		for d := startDate; d.Before(endDate); d = d.AddDate(0, 0, 1) {
			// is weekday
			if d.Weekday() >= time.Monday && d.Weekday() <= time.Friday {
				// Do a 3/5 chance of working on the day
				isWorkingMorning := rand.Intn(5) < 3
				isWorkingAfternoon := rand.Intn(5) < 3
				// Pick one of the stores at random of that employee
				store := stores[rand.Intn(len(stores))]
				if isWorkingMorning {
					shifts = append(shifts, Shift{employee, store, d, "morning"})
				}
				if isWorkingAfternoon {
					shifts = append(shifts, Shift{employee, store, d, "afternoon"})
				}
			}
		}
	}

	csv := "employee_id,store_id,date,shift\n"
	for _, shift := range shifts {
		csv += fmt.Sprintf("%d,%d,%s,%s\n", shift.EmployeeID, shift.StoreID, shift.Date.Format("2006-01-02"), shift.Shift)
	}
	fmt.Println(csv)
}

```

--------------------------------

### Generate Raw Shifts Data in Go

Source: https://github.com/quarylabs/quary/blob/main/rust/core/src/init_duckdb/README.md

This Go program generates a CSV string of synthetic shift data. It defines a Shift struct and populates a slice of shifts based on employee roles (manager vs. regular employee) and store assignments within a specified date range. Dependencies include the standard Go 'fmt', 'math/rand', and 'time' packages. The output is a CSV formatted string.

```go
package main

import (
	"fmt"
	"math/rand"
	"time"
)

type Shift struct {
	EmployeeID int
	StoreID    int
	Date       time.Time
	Shift      string
}

func main() {
	rand.Seed(42)

	// Start from the beginning of 2022
	startDate := time.Date(2022, time.January, 1, 0, 0, 0, 0, time.UTC)
	// End at the end of June 2022
	endDate := time.Date(2022, time.July, 1, 0, 0, 0, 0, time.UTC)

	// shifts is a slice of Shifts
	var shifts []Shift

	// For the managers (1-4) they work Monday to Friday every week, morning and afternoon
	// Iterate over the days
	managersToStore := map[int]int{
		4: 1,
		5: 2,
		6: 3,
		7: 4,
	}
	for manager, store := range managersToStore {
		for d := startDate; d.Before(endDate); d = d.AddDate(0, 0, 1) {
			// Check if the day is Monday to Friday
			if d.Weekday() >= time.Monday && d.Weekday() <= time.Friday {
				// Print the date in day-month-year format
				shifts = append(shifts, Shift{manager, store, d, "morning"})
				shifts = append(shifts, Shift{manager, store, d, "afternoon"})
			}
		}
	}

	// For the employees (5-10) they work 3 days a week, morning and afternoon
	// Iterate over the days
	employeesToStoreThatWorkIn := map[int][]int{
		1:  []int{1},
		2:  []int{2, 3},
		3:  []int{4, 1, 2},
		8:  []int{3, 4},
		9:  []int{2},
		10: []int{1},
	}
	for employee, stores := range employeesToStoreThatWorkIn {
		// Iterate over the days
		for d := startDate; d.Before(endDate); d = d.AddDate(0, 0, 1) {
			// is weekday
			if d.Weekday() >= time.Monday && d.Weekday() <= time.Friday {
				// Do a 3/5 chance of working on the day
				isWorkingMorning := rand.Intn(5) < 3
				isWorkingAfternoon := rand.Intn(5) < 3
				// Pick one of the stores at random of that employee
				store := stores[rand.Intn(len(stores))]
				if isWorkingMorning {
					shifts = append(shifts, Shift{employee, store, d, "morning"})
				}
				if isWorkingAfternoon {
					shifts = append(shifts, Shift{employee, store, d, "afternoon"})
				}
			}
		}
	}

	csv := "employee_id,store_id,date,shift\n"
	for _, shift := range shifts {
		csv += fmt.Sprintf("%d,%d,%s,%s\n", shift.EmployeeID, shift.StoreID, shift.Date.Format("2006-01-02"), shift.Shift)
	}
	fmt.Println(csv)
}
```

--------------------------------

### Convert DBT Projects to Quary Format

Source: https://context7.com/quarylabs/quary/llms.txt

Migrate existing DBT Core projects to Quary format using the `quary convert-dbt-project` command. This command generates the necessary Quary project structure, including converted SQL models, schema definitions, and data seed files.

```bash
# Convert DBT project to Quary
quary convert-dbt-project ./output_quary_project
```

--------------------------------

### Compile Quary Project CLI

Source: https://context7.com/quarylabs/quary/llms.txt

Validates the Quary project structure, model references, and SQL syntax without executing queries against the database. The command provides feedback on the parsing of models, sources, and seeds, indicating successful validation.

```bash
# Validate all models and references
quary compile

# Output shows validation results:
# │ Parsed 12 models
# │ Parsed 3 sources
# │ Parsed 5 seeds

```

--------------------------------

### Test dbt Model Outputs

Source: https://github.com/quarylabs/quary/blob/main/rust/dbt-converter/src/input/README.md

Run the tests defined for the dbt models to ensure the quality and integrity of the transformed data. This helps validate the accuracy of the models.

```bash
dbt test
```

--------------------------------

### Use Variables in SQL Models with Quary

Source: https://context7.com/quarylabs/quary/llms.txt

Reference variables defined in `quary.yaml` within SQL models using Jinja templating syntax `{{ var.variable_name }}`. This allows for dynamic configuration of SQL queries based on environment or other parameters.

```yaml
# quary.yaml
duckdbInMemory: {}
vars:
  - name: start_date
    value: "2024-01-01"
  - name: environment
    value: "production"
```

```sql
-- models/filtered_data.sql
SELECT *
FROM q.raw_data
WHERE created_at >= '{{ var.start_date }}'
  AND environment = '{{ var.environment }}'
```

--------------------------------

### Configure Model Materialization in Quary

Source: https://context7.com/quarylabs/quary/llms.txt

Control how models are materialized in the database by specifying the `materialization` property in model YAML files. Options typically include `view` (default) or `table` for performance-intensive aggregations.

```yaml
# models/large_aggregation.yaml
models:
  - name: large_aggregation
    description: Heavy aggregation materialized as table
    materialization: table
    columns:
      - name: date
      - name: total_revenue
      - name: total_orders
```