**TL;DR:** We built an LLM-powered agent in Databricks that generates **analytical SQLs** for Salesforce data. It:

* Discovers schemas from Unity Catalog (no column name guessing)
* Generates advanced SQL (CTEs, window functions, YoY, etc.)
* Validates queries against a SQL Warehouse
* Self-heals most errors
* Deploys Materialized Views for the L3 / Gold layer

All from a natural language prompt!

BTW: If you are interested in the Full suite of Analytics Solutions from Ingestion to Dashboards, we have **FREE** and readily available Accelerators on the Marketplace! Feel free to check them out as well! [https://marketplace.databricks.com/provider/3e1fd420-8722-4ebc-abaa-79f86ceffda0/Dataplatr-Corp](https://marketplace.databricks.com/provider/3e1fd420-8722-4ebc-abaa-79f86ceffda0/Dataplatr-Corp)

# The Problem

Anyone who has built analytics on top of Salesforce in Databricks has probably seen some version of this:

* Inconsistent naming: `TRX_AMOUNT` vs `TRANSACTION_AMOUNT` vs `AMOUNT`
* Tables with 100+ columns where only a handful matter for a specific analysis
* Complex relationships between AR transactions, invoices, receipts, customers
* **2–3 hours** to design, write, debug, and validate a single Gold table
* Frequent `COLUMN CANNOT BE RESOLVED` errors during development

By the time an L3 / Gold table is ready, a lot of engineering time has gone into just “translating” business questions into reliable SQL.

For the Databricks hackathon, we wanted to see how much of that could be automated safely using an agentic, human-in-the-loop approach.

# What We Built

We implemented an **Agentic L3 Analytics System** that sits on top of Salesforce data in Databricks and:

* Uses **MLflow’s native ChatAgent** as the orchestration layer
* Calls **Databricks Foundation Model APIs (Llama 3.3 70B)** for reasoning and code generation
* Uses **tool calling** to:
   * Discover schemas via Unity Catalog
   * Validate SQL against a SQL Warehouse
* Exposes a lightweight **Gradio UI** deployed as a Databricks App

From the user’s perspective, you describe the analysis you want in natural language, and the agent returns **validated SQL and a Materialized View** in your Gold schema.

# How It Works (End-to-End)

Example prompt:

>

The agent then:

1. **Discovers the schema**
   * Identifies relevant L2 tables (e.g., `ar_transactions`, `ra_customer_trx_all`)
   * Fetches exact column names and types from **Unity Catalog**
   * Caches schema metadata to avoid redundant calls and reduce latency
2. **Plans the query**
   * Determines joins, grain, and aggregations needed
   * Constructs an internal “spec” of CTEs, group-bys, and metrics (quarterly sums, YoY, filters, etc.)
3. **Generates SQL**
   * Builds a multi-CTE query with:
      * Data cleaning and filters
      * Deduplication via `ROW_NUMBER()`
      * Aggregations by year and quarter
      * Window functions for prior-period comparisons
4. **Validates & self-heals**
   * Executes the generated SQL against a **Databricks SQL Warehouse**
   * If validation fails (e.g., incorrect column name, minor syntax issue), the agent:
      * Reads the error message
      * Re-checks the schema
      * Adjusts the SQL
      * Retries execution
   * In practice, this self-healing loop resolves \~70–80% of initial errors automatically
5. **Deploys as a Materialized View**
   * On successful validation, the agent:
      * Creates or refreshes a **Materialized View** in the L3 / Gold schema
      * Optionally enriches with metadata (e.g., created timestamp, source tables) using the Databricks Python SDK

Total time: typically **2–3 minutes**, instead of 2–3 hours of manual work.

# Example Generated SQL

Here’s an example of SQL the agent generated and successfully validated:

    CREATE OR REFRESH MATERIALIZED VIEW salesforce_gold.l3_sales_quarterly_analysis AS
    WITH base_data AS (
      SELECT 
        CUSTOMER_TRX_ID,
        TRX_DATE,
        TRX_AMOUNT,
        YEAR(TRX_DATE) AS FISCAL_YEAR,
        QUARTER(TRX_DATE) AS FISCAL_QUARTER
      FROM main.salesforce_silver.ra_customer_trx_all
      WHERE TRX_DATE IS NOT NULL 
        AND TRX_AMOUNT > 0
    ),
    deduplicated AS (
      SELECT *, 
        ROW_NUMBER() OVER (
          PARTITION BY CUSTOMER_TRX_ID 
          ORDER BY TRX_DATE DESC
        ) AS rn
      FROM base_data
    ),
    aggregated AS (
      SELECT
        FISCAL_YEAR,
        FISCAL_QUARTER,
        SUM(TRX_AMOUNT) AS TOTAL_REVENUE,
        LAG(SUM(TRX_AMOUNT), 4) OVER (
          ORDER BY FISCAL_YEAR, FISCAL_QUARTER
        ) AS PRIOR_YEAR_REVENUE
      FROM deduplicated
      WHERE rn = 1
      GROUP BY FISCAL_YEAR, FISCAL_QUARTER
    )
    SELECT 
      *,
      ROUND(
        ((TOTAL_REVENUE - PRIOR_YEAR_REVENUE) / PRIOR_YEAR_REVENUE) * 100,
        2
      ) AS YOY_GROWTH_PCT
    FROM aggregated;

This was produced from a natural language request, grounded in the actual schemas available in Unity Catalog.

# Tech Stack

* **Platform:** Databricks Lakehouse + Unity Catalog
* **Data:** Salesforce-style data in `main.salesforce_silver`
* **Orchestration:** MLflow **ChatAgent** with tool calling
* **LLM:** Databricks Foundation Model APIs – **Llama 3.3 70B**
* **UI:** Gradio app deployed as a **Databricks App**
* **Integration:** Databricks Python SDK for workspace + Materialized View management

# Results

So far, the agent has been used to generate and validate **50+ Gold tables**, with:

* ⏱️ \~**90% reduction** in development time per table
* 🎯 **100% of deployed SQL validated** against a SQL Warehouse
* 🔄 Ability to re-discover schemas and adapt when tables or columns change

It doesn’t remove humans from the loop; instead, it takes care of the mechanical parts so data engineers and analytics engineers can focus on definitions and business logic.

# Key Lessons Learned

* **Schema grounding is essential** LLMs will guess column names unless forced to consult real schemas. Tool calling + Unity Catalog is critical.
* **Users want real analytics, not toy SQL** CTEs, aggregations, window functions, and business metrics are the norm, not the exception.
* **Caching improves both performance and reliability** Schema lookups can become a bottleneck without caching.
* **Self-healing is practical** A simple loop of “read error → adjust → retry” fixes most first-pass issues.

# What’s Next

This prototype is part of a broader effort at **Dataplatr** to build metadata-driven ELT frameworks on Databricks Marketplace, including:

* CDC and incremental processing
* Data quality monitoring and rules
* Automated lineage
* Multi-source connectors (Salesforce, Oracle, SAP, etc.)

For this hackathon, we focused specifically on the **“agent-as-SQL-engineer”** pattern for L3 / Gold analytics.

# Feedback Welcome!

* Would you rather see this generate **dbt models** instead of Materialized Views?
* Which other data sources (SAP, Oracle EBS, Netsuite…) would benefit most from this pattern?
* If you’ve built something similar on Databricks, what worked well for you in terms of prompts and UX?

—

Happy to answer questions or go deeper into the architecture if anyone’s interested!

Hackathon Submission: Built an AI Agent that Writes Complex Salesforce SQL using all native Databricks features