Hi @emma_s  Thank you for your reply. Attached is the sample code. 

-- ============================================================
-- 15-Minute OR Utilization Fact Build (Sanitized)
-- Intended grain:
-- (ORLogKey, FifteenMinuteTimeSegmentType, SegmentStartDate)
-- ============================================================

-- ==========================
-- Base Case Data
-- ==========================
WITH BaseLogData AS (
SELECT
f.LogID AS ORLogKey,
f.InORDateTime AS InORDateTime,
f.OutORDateTime AS OutORDateTime,
f.ProcedureStartDateTime AS ProcedureStartDateTime,
f.ProcedureCompleteDateTime AS ProcedureCompleteDateTime,
d.ORKey AS ORKey,
s.SurgeonKey AS PrimarySurgeonKey,
p.ProcedureKey AS PrimaryProcedureKey
FROM OR_Log o
JOIN OR_Fact f
ON o.LogID = f.LogID
LEFT JOIN OR_Dimension d
ON o.RoomID = d.ORRoomID
LEFT JOIN Surgeon_Dimension s
ON f.PrimarySurgeonID = s.SurgeonID
LEFT JOIN Procedure_Dimension p
ON f.PrimaryProcedureID = p.ProcedureID
WHERE f.InORDateTime IS NOT NULL
AND f.OutORDateTime > f.InORDateTime
),

-- ==========================
-- 15-Minute Number Generator
-- ==========================
Numbers AS (
SELECT explode(sequence(0, 191)) AS n
),

-- ==========================
-- Generate 15-Minute Segments
-- ==========================
FannedSegments AS (
SELECT
b.ORLogKey,
b.ORKey,
b.PrimarySurgeonKey,
b.PrimaryProcedureKey,
b.InORDateTime,
b.OutORDateTime,
date_add(
MINUTE,
15 * n.n,
from_unixtime(
floor(unix_timestamp(b.InORDateTime) / 900) * 900
)
) AS SegmentStartDate,
'Room' AS FifteenMinuteTimeSegmentType
FROM BaseLogData b
JOIN Numbers n
ON date_add(
MINUTE,
15 * n.n,
from_unixtime(
floor(unix_timestamp(b.InORDateTime) / 900) * 900
)
) < b.OutORDateTime
),

-- ==========================
-- Join to Time Dimension
-- ==========================
StageData AS (
SELECT
s.ORLogKey,
s.ORKey,
s.PrimarySurgeonKey,
s.PrimaryProcedureKey,
s.SegmentStartDate,
s.FifteenMinuteTimeSegmentType,
t.TimeOfDayID
FROM FannedSegments s
LEFT JOIN TimeOfDay_Dimension t
ON hour(s.SegmentStartDate) = t.HourNumber
AND minute(s.SegmentStartDate) = t.MinuteNumber
)

-- ==========================
-- Validate Intended Grain
-- ==========================
SELECT
ORLogKey,
FifteenMinuteTimeSegmentType,
SegmentStartDate,
COUNT(*) AS cnt
FROM StageData
GROUP BY 1,2,3
HAVING COUNT(*) > 1;

-- ==========================
-- MERGE INTO FACT
-- ==========================
MERGE INTO OR_Utilization_Fact target
USING StageData source
ON target.ORLogKey = source.ORLogKey
AND target.SegmentStartDate = source.SegmentStartDate
AND target.FifteenMinuteTimeSegmentType = source.FifteenMinuteTimeSegmentType

WHEN MATCHED THEN UPDATE SET
target.ORKey = source.ORKey,
target.PrimarySurgeonKey = source.PrimarySurgeonKey,
target.PrimaryProcedureKey = source.PrimaryProcedureKey,
target.TimeOfDayID = source.TimeOfDayID

WHEN NOT MATCHED THEN INSERT (
ORLogKey,
ORKey,
PrimarySurgeonKey,
PrimaryProcedureKey,
SegmentStartDate,
FifteenMinuteTimeSegmentType,
TimeOfDayID
)
VALUES (
source.ORLogKey,
source.ORKey,
source.PrimarySurgeonKey,
source.PrimaryProcedureKey,
source.SegmentStartDate,
source.FifteenMinuteTimeSegmentType,
source.TimeOfDayID
);