Retail Intelligence Platform — Full Databricks AI/BI POC

Why This POC

Modern data platforms promise to accelerate development — but how much time do AI-assisted tools actually save in practice? This POC was built to answer that question head-on by using the newest Databricks capabilities — Genie Code, Lakeflow Connect, and Spark Declarative Pipelines — to build a complete retail analytics platform end to end. The goal was not just to get data flowing, but to validate whether the entire stack, from ingestion to natural language consumption, could be delivered in a fraction of the usual time.

Architecture

Tech Stack

Layer 1 — Ingestion

Lakeflow Connect — Zero Code for Salesforce and PostgreSQL

Lakeflow Connect is Databricks' native managed ingestion service. For this POC it handled both Salesforce and PostgreSQL sources without a single line of custom connector code. The setup is entirely point-and-click — you authenticate the source, select tables, configure a schedule, and Lakeflow Connect handles the rest including initial full load, incremental updates via Change Data Capture (CDC), Slowly Changing Dimension Type 2 history tracking, schema evolution, and automatic error handling. For PostgreSQL, Lakeflow Connect used CDC to capture every insert, update, and delete with low latency. For Salesforce, it connected natively to the Sales Cloud and replicated objects directly into the Bronze Delta tables.

AutoLoader — File Ingestion from Azure Blob Storage

For the Azure Blob Storage source, AutoLoader was used to continuously detect and ingest new files as they arrive. AutoLoader automatically infers schema, handles malformed records, and processes only new files incrementally. The AutoLoader notebook was generated entirely by Genie Code in Agentic mode — the only input was a natural language description of the source path, file format, and destination Delta table. Genie Code produced the complete notebook, ran it, and resolved a minor schema mismatch automatically without manual intervention.

Layer 2 — Transformation (Spark Declarative Pipelines + Genie Code)

How Genie Code Built the Entire Pipeline

The most significant finding of this POC was in the transformation layer. The entire Bronze to Silver to Gold pipeline — across 6 to 10 source tables — was built using Genie Code in Agentic mode inside the Lakeflow Pipelines Editor. The process was conversational: describe what the Bronze tables look like, describe what clean Silver data should look like, describe the star schema required in Gold. Genie Code planned the pipeline, generated each dataset definition, added appropriate data quality expectations, ran the pipeline, identified failures, proposed fixes, and applied them — all autonomously with approval checkpoints at each step.

Bronze Layer — Raw Landing

The Bronze layer stores data exactly as it arrives from each source — no transformations, no filtering. Lakeflow Connect and AutoLoader write directly to Bronze Delta tables. All raw records are preserved here for full replayability and audit purposes.

Silver Layer — Cleaned and Validated

The Silver layer applies all cleaning and validation logic using Spark Declarative Pipeline expectations. Genie Code generated quality checks covering null values on key columns, referential integrity between tables, value range validations, and duplicate detection. Records failing critical expectations are quarantined rather than silently dropped — a pattern Genie Code applied automatically based on the expectation configuration. The output is a clean, trusted dataset ready for modelling.

Gold Layer — Star Schema

The Gold layer implements a star schema centred on a fact_sales table surrounded by five dimension tables — dim_customer, dim_product, dim_date, dim_geography, and dim_channel. Genie Code generated all five dimension tables and the central fact table with correct foreign key relationships, grain definitions, and surrogate key handling. The Gold layer is optimised for analytical query patterns — wide, denormalised, and partitioned by date.

Layer 3 — Semantic Layer (Metric Views)

Rather than creating separate SQL views for each KPI the business needed, a single Metric View was created on top of the Gold star schema. Metric Views in Databricks allow you to define measures and dimensions once in a governed, reusable object that all downstream consumers — dashboards, Genie Space, ad-hoc queries — reference. For this POC, 6 measures and 15 dimensions were defined covering revenue totals, order counts, product performance, customer segments, and geographic breakdowns. Any new KPI simply adds a measure to the existing Metric View rather than creating a new SQL object.

Layer 4 — Consumption

Databricks Dashboard

A Databricks Dashboard was built on top of the Metric Views showing key retail KPIs — total revenue by quarter and month, top selling products, revenue distribution by country and region, customer segment breakdown, and channel performance over time. Because the dashboard reads from Metric Views rather than raw Gold tables, all numbers are automatically consistent with what Genie Space returns for the same questions.

Genie Space — Natural Language Analytics

The Genie Space is the most business-facing layer of the platform. It connects to the Metric Views and allows any business user — no SQL knowledge required — to ask questions in plain English and receive answers instantly. The Genie Space was configured with sample questions and their corresponding verified SQL expressions so Genie can answer common questions accurately without spending time reasoning from scratch. Join rules were also configured to control how tables can be combined, preventing incorrect cross-joins.

• ▸What was the total revenue for Q1 2025 compared to Q4 2024?
• ▸Which products had the highest sales volume last month?
• ▸Show me the revenue distribution across countries for this year
• ▸Which customer segment contributed the most to revenue in the last quarter?
• ▸What are the top 10 performing regions by order count this year?
• ▸How did sales trend week over week in Q2 2025?

Each question returns a result table and an optional chart. Business users can ask follow-up questions, filter by time period or region, and explore the data without waiting for an analyst to write a query or update a dashboard.

Key Findings from the POC

• ▸Genie Code in Agentic mode genuinely compressed the transformation development timeline — the entire Bronze to Gold pipeline across 6–10 tables was built and validated in a fraction of the time manual coding would have required
• ▸Lakeflow Connect eliminated all ingestion boilerplate for Salesforce and PostgreSQL — no connector code, no custom API handling, no schema management scripts
• ▸Spark Declarative Pipelines with quality expectations provided confidence in Silver layer data quality without needing a separate validation framework
• ▸Metric Views as a semantic layer resolved the 'multiple definitions of the same KPI' problem that plagues most analytics platforms — one definition, all consumers
• ▸Genie Space lowered the barrier to data access significantly — business users could retrieve retail insights in seconds using plain English without analyst support
• ▸Genie Code's agentic loop — plan, generate, run, fix errors, re-run — worked without manual intervention for most tasks, only pausing for approval at key decision points

Results