Hello All,
Here, we are running the Databricks Lakebridge Analyzer for Snowflake Dialect, But the Analyzer is not able to analyze the SnowSQL Query and Generating the incorrect count as 0 for all the required MetaData Analysis report.
The source Queries:
--Query 1
insert into CURATED.Fact_VoyageItinerary_W_NK(
period_sk,
port_sk,
VESSEL_SK,
Fixture_No,
Remarks_Fixture,
Remarks_Notice,
Remarks_Operations,
Port_Func,
Seq,
Tankerman_Short_Name,
Tankerman_Type,
Voyage_No,
Arrival_Local,
Eta_Gmt,
Extra_Sea_Days,
Miles,
Port_Days,
Port_Name,
Sea_Days,
Speed_From_Port,
Speed_to_Port,
Departure_Local,
Last_Update_Gmt,
Rob_Arrival,
Rob_Departure,
Fuel_Type,
Weather_Factor_to_Port,
Vessel_Code,
Port_No)
WITH dim_vessel_data
AS (select VESSEL_CODE as vscode,
VESSEL_SK
from CURATED.DIM_VESSEL
Where ACTIVE_FLAG='Y' and END_DATE='9999-12-31'
QUALIFY ROW_NUMBER() OVER (PARTITION BY VESSEL_CODE ORDER BY VESSEL_CODE DESC)=1
),
dim_port_data
AS (select PORT_SK as port_sk,
PORT_NO as port_no
from CURATED.DIM_PORT
Where ACTIVE_FLAG='Y' and END_DATE='9999-12-31'
QUALIFY ROW_NUMBER() OVER (PARTITION BY PORT_NO ORDER BY PORT_NO DESC)=1
),
gn_voyage_iter
AS (select "Fixture No" as Fixture_No,
"Remarks (Fixture)" as Remarks_Fixture,
"Remarks (Notice)" as Remarks_Notice,
"Remarks (Operations)" as Remarks_Operations,
"Port Func" as Port_Func,
"Seq_itinerary" as Seq,
"Tankerman Short Name" as Tankerman_Short_Name,
"Tankerman Type" as Tankerman_Type,
"Voyage No_itinerary" as Voyage_No,
"Arrival Local" as Arrival_Local,
"Eta Gmt" as Eta_Gmt,
"Extra Sea Days" as Extra_Sea_Days,
"Miles" as Miles,
"Port Days" as Port_Days,
"Port Name" as Port_Name,
"Sea Days" as Sea_Days,
"Speed From Port" as Speed_From_Port,
"Speed to Port" as Speed_to_Port,
"Departure Local" as Departure_Local,
"Last Update Gmt" as Last_Update_Gmt,
"Rob Arrival" as Rob_Arrival,
"Rob Departure" as Rob_Departure,
"Fuel Type" as Fuel_Type,
"Port No" as port_no,
"Vessel Code_itinerary" as vessel_code,
substring("Last Update Gmt", 0, 10)::date as period_sk_date,
"Weather Factor To Port" as Weather_Factor_to_Port
from RAW."GN_VoyageItinerary"
where Last_Update_GMT is not null
or vessel_code is not null
or port_no is not null
),
fact_vessel
AS (SELECT fact.*,
dim.VESSEL_SK
FROM gn_voyage_iter fact
LEFT OUTER JOIN dim_vessel_data dim
ON fact.vessel_code = dim.vscode
),
fact_vessel_port
AS (SELECT fact.*,
dim.port_sk
FROM fact_vessel fact
LEFT OUTER JOIN dim_port_data dim
ON fact.port_no = dim.port_no
),
data
AS (select period_sk,
port_sk,
VESSEL_SK,
Fixture_No,
Remarks_Fixture,
Remarks_Notice,
Remarks_Operations,
Port_Func,
Seq,
Tankerman_Short_Name,
Tankerman_Type,
Voyage_No,
Arrival_Local,
Eta_Gmt,
Extra_Sea_Days,
Miles,
Port_Days,
Port_Name,
Sea_Days,
Speed_From_Port,
Speed_to_Port,
Departure_Local,
Last_Update_Gmt,
Rob_Arrival,
Rob_Departure,
Fuel_Type,
Weather_Factor_to_Port,
vessel_code,
port_no
from fact_vessel_port val ,CURATED.dim_period period
where period.period_date = val.period_sk_date
)
select * from data;
--Query 2
create or replace view PRESENTATION."Dash - Variance Estimated Details 2"(
"Vessel Code",
"Voyage No",
"Data Type",
"Description",
"Amount Base",
"Bill Code",
"Voy. No."
) as (
with Query AS (
SELECT DISTINCT
Q1.VESSEL_CODE,
Q1.VOYAGE_NO,
Q1.DATA_TYPE,
Q1.DESCRIPTION,
Q1.AMOUNT_BASE,
Q1.BILL_CODE,
Q1.VESSEL_TYPE,
Q1.PORT_FUNCTION
FROM
( SELECT DISTINCT
F_VPD.VESSEL_CODE,
F_VPD.VOYAGE_NO,
F_VPD.DESCRIPTION,
F_VPD.AMOUNT_BASE,
F_VPD.BILL_CODE,
F_VPD.PORT_FUNCTION,
F_VPD.VESSEL_SK,
F_VPD.SNAPSHOT_REF_Date_Local,
VPVy.DATA_TYPE,
VPVy.COMPLETE_DATE_GMT,
VPVy.VOYAGE_STATUS,
VPVy.VESSEL_TYPE
FROM schema.table
)
)
)
.png){kind=link}