oxy-workflow-builder

Oxy Workflow Builder

Build Oxy data workflows, SQL queries, and AI agents following best practices. Always follow the Oxy Hierarchy: semantic queries first, then SQL/workflows, then agents (see oxy-core rule for the full decision tree).

Before writing SQL or agents, check if semantic layer views exist that can answer the question:

find semantics/views -name "*.view.yml" 2>/dev/null
find semantics/topics -name "*.topic.yml" 2>/dev/null

Essential Commands

# Validation (ALWAYS run after creating/editing YAML)
oxy validate
oxy validate --file=<path>

# SQL execution
oxy run query.sql
oxy run query.sql --dry-run
oxy run query.sql -v key=value

# Workflows
oxy run pipeline.workflow.yml

# Agents
oxy run agent.agent.yml "question"

# Semantic queries (preferred when views exist)
oxy semantic-engine --dev-mode

SQL File Structure

SQL files support Jinja2 templating for dynamic queries:

-- query.sql
-- Description: Brief description of what this query does
-- Variables:
--   - start_date: Start date for the date range
--   - end_date: End date for the date range

SELECT
    date,
    customer_id,
    SUM(amount) as total_amount
FROM {{ databases.clickhouse.schema }}.orders
WHERE date BETWEEN '{{ start_date }}' AND '{{ end_date }}'
GROUP BY date, customer_id
ORDER BY total_amount DESC;

SQL Best Practices

1. Add header comments with description and required variables
2. Use Jinja2 variables for parameterization: {{ variable_name }}
3. Reference databases via context: {{ databases.db_name.schema }}.table
4. Test with dry-run before executing: oxy run query.sql --dry-run
5. Keep queries focused - one clear purpose per file
6. Name descriptively - monthly_revenue_by_restaurant.sql not query1.sql

Common SQL Patterns

Date filtering with variables:

WHERE created_at >= '{{ start_date }}'
  AND created_at < '{{ end_date }}'

Dynamic schema references:

FROM {{ databases.clickhouse.restaurant_analytics }}.orders