Hi Team,
I was able to successfully install and use the Lakebridge code conversion tool to convert my SQL Server script into a Databricks SQL script. However, the generated script contains several syntax errors. Could you please let me know if I might have missed any steps during the installation or setup process? Below are the scripts used and generated. The stored procedure generated is completely incorrect. Highlighted few syntax errors for reference.
Source Code:
USE [lakebridge_test]
GO
/****** Object: Table [dbo].[CEAT_TIRES_COST] Script Date: 26-08-2025 15:07:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CEAT_TIRES_COST](
[ID] [int] NULL,
[PROD_NAME] [varchar](255) NULL,
[COST] [decimal](18, 2) NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[DimCustomer] Script Date: 26-08-2025 15:07:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DimCustomer](
[CustomerKey] [int] IDENTITY(1,1) NOT NULL,
[CustomerID] [varchar](50) NULL,
[CustomerName] [varchar](100) NULL,
[Region] [varchar](50) NULL,
[Country] [varchar](50) NULL,
[Segment] [varchar](50) NULL,
PRIMARY KEY CLUSTERED
(
[CustomerKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[DimDate] Script Date: 26-08-2025 15:07:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DimDate](
[DateKey] [int] NOT NULL,
[FullDate] [date] NULL,
[Day] [int] NULL,
[Month] [int] NULL,
[MonthName] [varchar](20) NULL,
[Quarter] [int] NULL,
[Year] [int] NULL,
[WeekdayName] [varchar](20) NULL,
PRIMARY KEY CLUSTERED
(
[DateKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[DimDealer] Script Date: 26-08-2025 15:07:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DimDealer](
[DealerKey] [int] IDENTITY(1,1) NOT NULL,
[DealerID] [varchar](50) NULL,
[DealerName] [varchar](100) NULL,
[Region] [varchar](50) NULL,
[Zone] [varchar](50) NULL,
[Territory] [varchar](50) NULL,
PRIMARY KEY CLUSTERED
(
[DealerKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[DimProduct] Script Date: 26-08-2025 15:07:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DimProduct](
[ProductKey] [int] IDENTITY(1,1) NOT NULL,
[ProductCode] [varchar](50) NULL,
[ProductName] [varchar](100) NULL,
[Category] [varchar](50) NULL,
[TireType] [varchar](50) NULL,
[Size] [varchar](50) NULL,
[Brand] [varchar](50) NULL,
PRIMARY KEY CLUSTERED
(
[ProductKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[FactSales] Script Date: 26-08-2025 15:07:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[FactSales](
[SalesKey] [bigint] IDENTITY(1,1) NOT NULL,
[DateKey] [int] NULL,
[ProductKey] [int] NULL,
[CustomerKey] [int] NULL,
[DealerKey] [int] NULL,
[InvoiceNumber] [varchar](50) NULL,
[QuantitySold] [int] NULL,
[UnitPrice] [decimal](10, 2) NULL,
[TotalRevenue] AS ([QuantitySold]*[UnitPrice]) PERSISTED,
PRIMARY KEY CLUSTERED
(
[SalesKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[FactSales] WITH CHECK ADD FOREIGN KEY([CustomerKey])
REFERENCES [dbo].[DimCustomer] ([CustomerKey])
GO
ALTER TABLE [dbo].[FactSales] WITH CHECK ADD FOREIGN KEY([DateKey])
REFERENCES [dbo].[DimDate] ([DateKey])
GO
ALTER TABLE [dbo].[FactSales] WITH CHECK ADD FOREIGN KEY([DealerKey])
REFERENCES [dbo].[DimDealer] ([DealerKey])
GO
ALTER TABLE [dbo].[FactSales] WITH CHECK ADD FOREIGN KEY([ProductKey])
REFERENCES [dbo].[DimProduct] ([ProductKey])
GO
/****** Object: StoredProcedure [dbo].[usp_LoadSalesData] Script Date: 26-08-2025 15:07:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_LoadSalesData]
@InvoiceNumber VARCHAR(50),
@ProductCode VARCHAR(50),
@CustomerID VARCHAR(50),
@DealerID VARCHAR(50),
@Quantity INT,
@UnitPrice DECIMAL(10,2)
AS
BEGIN
SET NOCOUNT ON;
-- 1. Get or insert Date
DECLARE @DateKey INT = CONVERT(INT, FORMAT(@Date, 'yyyyMMdd'));
IF NOT EXISTS (SELECT 1 FROM DimDate WHERE DateKey = @DateKey)
BEGIN
INSERT INTO DimDate (DateKey, FullDate, Day, Month, MonthName, Quarter, Year, WeekdayName)
VALUES (
@DateKey,
DAY(@Date),
MONTH(@Date),
DATEPART(QUARTER,
@date),
YEAR(@Date),
);
END
-- 2. Lookup keys (these would normally have proper surrogate key mapping logic)
DECLARE @ProductKey INT = (SELECT ProductKey FROM DimProduct WHERE ProductCode = @ProductCode);
DECLARE @CustomerKey INT = (SELECT CustomerKey FROM DimCustomer WHERE CustomerID = @CustomerID);
DECLARE @DealerKey INT = (SELECT DealerKey FROM DimDealer WHERE DealerID = @DealerID);
-- 3. Insert into FactSales
INSERT INTO FactSales (DateKey, ProductKey, CustomerKey, DealerKey, InvoiceNumber, QuantitySold, UnitPrice)
VALUES (@DateKey, @ProductKey, @CustomerKey, @DealerKey, @InvoiceNumber, @Quantity, @UnitPrice);
END;
GO
Converted Code:
USE `lakebridge_test`
/****** Object: Table [dbo].[CEAT_TIRES_COST] Script Date: 26-08-2025 15:07:36 ******/
;
CREATE OR REPLACE TABLE `dbo`.`CEAT_TIRES_COST`(
`ID` INT,
`PROD_NAME` STRING,
`COST` decimal(18, 2)
)
/****** Object: Table [dbo].[DimCustomer] Script Date: 26-08-2025 15:07:37 ******/
;
CREATE OR REPLACE TABLE `dbo`.`DimCustomer`(
`CustomerKey` int GENERATED ALWAYS AS IDENTITY NOT NULL, #Datatype must be bigint
`CustomerID` STRING,
`CustomerName` STRING,
`Region` STRING,
`Country` STRING,
`Segment` STRING,
CLUSTERED
(
`CustomerKey` ASC
)WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF)
) #Not supported by databricks SQL
/****** Object: Table [dbo].[DimDate] Script Date: 26-08-2025 15:07:37 ******/
;
CREATE OR REPLACE TABLE `dbo`.`DimDate`(
`DateKey` int NOT NULL,
`FullDate` TIMESTAMP ,
`Day` INT,
`Month` INT,
`MonthName` STRING,
`Quarter` INT,
`Year` INT,
`WeekdayName` STRING,
CLUSTERED
(
`DateKey` ASC
)WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF)
)
/****** Object: Table [dbo].[DimDealer] Script Date: 26-08-2025 15:07:37 ******/
;
CREATE OR REPLACE TABLE `dbo`.`DimDealer`(
`DealerKey` int GENERATED ALWAYS AS IDENTITY NOT NULL,
`DealerID` STRING,
`DealerName` STRING,
`Region` STRING,
`Zone` STRING,
`Territory` STRING,
CLUSTERED
(
`DealerKey` ASC
)WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF)
)
/****** Object: Table [dbo].[DimProduct] Script Date: 26-08-2025 15:07:37 ******/
;
CREATE OR REPLACE TABLE `dbo`.`DimProduct`(
`ProductKey` int GENERATED ALWAYS AS IDENTITY NOT NULL,
`ProductCode` STRING,
`ProductName` STRING,
`Category` STRING,
`TireType` STRING,
`Size` STRING,
`Brand` STRING,
CLUSTERED
(
`ProductKey` ASC
)WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF)
)
/****** Object: Table [dbo].[FactSales] Script Date: 26-08-2025 15:07:37 ******/
;
CREATE OR REPLACE TABLE `dbo`.`FactSales`(
`SalesKey` `bigint` GENERATED ALWAYS AS IDENTITY NOT NULL,
`DateKey` INT,
`ProductKey` INT,
`CustomerKey` INT,
`DealerKey` INT,
`InvoiceNumber` STRING,
`QuantitySold` INT,
`UnitPrice` decimal(10, 2),
`TotalRevenue` AS (`QuantitySold`*`UnitPrice`) PERSISTED,
CLUSTERED
(
`SalesKey` ASC
)WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF)
)
;
ALTER TABLE `dbo`.`FactSales` ;
WITH CHECK ADD FOREIGN KEY(`CustomerKey`)
REFERENCES `dbo`.`DimCustomer` (`CustomerKey`)
;
ALTER TABLE `dbo`.`FactSales` ;
WITH CHECK ADD FOREIGN KEY(`DateKey`)
REFERENCES `dbo`.`DimDate` (`DateKey`)
;
ALTER TABLE `dbo`.`FactSales` ;
WITH CHECK ADD FOREIGN KEY(`DealerKey`)
REFERENCES `dbo`.`DimDealer` (`DealerKey`)
;
ALTER TABLE `dbo`.`FactSales` ;
WITH CHECK ADD FOREIGN KEY(`ProductKey`)
REFERENCES `dbo`.`DimProduct` (`ProductKey`)
/****** Object: StoredProcedure [dbo].[usp_LoadSalesData] Script Date: 26-08-2025 15:07:37 ******/
CREATE OR REPLACE PROCEDURE `dbo`.`usp_LoadSalesData`(
IN V_InvoiceNumber STRING,
IN V_Date DATE,
IN V_ProductCode STRING,
IN V_CustomerID STRING,
IN V_DealerID STRING,
IN V_Quantity INT,
IN V_UnitPrice DECIMAL(10,2))
LANGUAGE SQL
SQL SECURITY INVOKER
AS
BEGIN
DECLARE VARIABLE V_DateKey INT ;
DECLARE VARIABLE V_ProductKey INT ;
DECLARE VARIABLE V_CustomerKey INT ;
DECLARE VARIABLE V_DealerKey INT ;
-- 1. Get or insert Date
SET V_DateKey = CAST(date_format(V_Date, 'yyyyMMdd') AS INT);
IF NOT EXISTS (SELECT 1 FROM DimDate WHERE DateKey = V_DateKey) THEN
THEN
END IF;
INSERT INTO DimDate (DateKey, FullDate, Day, Month, MonthName, Quarter, Year, WeekdayName)
VALUES (
V_DateKey,
V_Date,
DAY(V_Date),
MONTH(V_Date),
cast(EXTRACT(MONTH from V_Date) as string),
EXTRACT(QUARTER from V_Date),
YEAR(V_Date),
cast(EXTRACT(DOW from V_Date) as string)
);
END IF
-- 2. Lookup keys (these would normally have proper surrogate key mapping logic)
;
SET V_ProductKey = (SELECT ProductKey FROM DimProduct WHERE ProductCode = V_ProductCode);
SET V_CustomerKey = (SELECT CustomerKey FROM DimCustomer WHERE CustomerID = V_CustomerID);
SET V_DealerKey = (SELECT DealerKey FROM DimDealer WHERE DealerID = V_DealerID);
-- 3. Insert into FactSales
INSERT INTO FactSales (DateKey, ProductKey, CustomerKey, DealerKey, InvoiceNumber, QuantitySold, UnitPrice)
VALUES (V_DateKey, V_ProductKey, V_CustomerKey, V_DealerKey, V_InvoiceNumber, V_Quantity, V_UnitPrice);
END;
