Karthik Raj
07/22/2025, 3:58 PMCREATE TABLE dbo.TreatmentProcedure
(
AccountId INT,
TreatmentProcedureId INT,
LocationId INT,
PatientId INT,
PatientEstimate DECIMAL(19, 4),
InsuranceEstimate DECIMAL(19, 4),
DateOfService BIGINT,
UCRFee DECIMAL(19, 4),
AppointmentId INT,
DeleteStatusId INT,
ProviderId INT,
ProcedureCodeId INT,
Status STRING,
LastUpdatedOn BIGINT,
TreatmentPlanId INT,
Fee DECIMAL(19, 4),
CheckedOutDate BIGINT,
SpecialtyId INT,
IsOrthoProcedure BOOLEAN,
ICDCodeIds STRING,
Tooth STRING,
ProposedDate BIGINT,
B INT,
L INT,
M INT,
O INT,
F INT,
D INT,
I INT,
QuadrantId INT,
AppliedMembershipDiscount DECIMAL(19, 4),
IsDiscountApplied BOOLEAN,
DiscountAmount DECIMAL(19, 4),
DiscountType INT,
SalesTaxPercentage DECIMAL(5, 2),
DiscountPercentage DECIMAL(5, 2),
BillingOrder STRING,
PrimaryDentalInsuranceId INT,
PrimaryMedicalInsuranceId INT,
ProcTime AS PROCTIME(),
PRIMARY KEY (AccountId, TreatmentProcedureId) NOT ENFORCED
) WITH ( 'connector' = 'kafka', 'topic' = 'dbo.TreatmentProcedure' );
CREATE TABLE dw.FactProcedure
(
AccountId INT,
TreatmentProcedureId INT,
LocationId INT,
PatientId INT,
PatientEstimate DECIMAL(18, 4),
...
CheckedOutDate TIMESTAMP(3),
AppointmentDate DATE,
IsActiveAppointment BOOLEAN,
ProviderType STRING,
SpecialtyId INT,
CDTCategory STRING,
IsOrthoProcedure BOOLEAN,
OrthoCodeWithoutPaymentPlan BOOLEAN,
ServiceTypeId INT,
c_CheckedOutDate DATE,
AppointmentProviderId INT,
AppointmentProviderType STRING,
AppointmentSpecialtyId INT,
AppointmentStatus STRING,
AppointmentLocationId INT,
ICDCodeIds STRING,
...
PRIMARY KEY (AccountId, TreatmentProcedureId) NOT ENFORCED
) WITH ( 'connector' = 'doris', 'table' = 'FactProcedure' );
My load scripts looks something like this :
INSERT INTO dw.FactProcedure
SELECT src.AccountId,
src.TreatmentProcedureId,
src.LocationId,
src.PatientId,
src.PatientEstimate,
...
TO_DATE(CONVERT_TZ(FROM_UNIXTIME(src.CheckedOutDate / 1000000000), 'UTC',
da.DWHReportTimeZone)) AS c_CheckedOutDate,
COALESCE(sdap.ProviderId, lfa.PrimaryProviderId) AS AppointmentProviderId,
COALESCE(sdap.ProviderType, lfa.ProviderType) AS AppointmentProviderType,
COALESCE(sdap.SpecialtyId, lfa.SpecialtyId) AS AppointmentSpecialtyId,
COALESCE(ap.Status, lfa.Status) AS AppointmentStatus,
COALESCE(ap.LocationID, lfa.LocationId) AS AppointmentLocationId,
src.ICDCodeIds,
src.Tooth,
IF(src.Status = 'Completed' OR src.Status = 'Rejected', FALSE, TRUE) ASIsPlanned,
...
TO_DATE(CONVERT_TZ(FROM_UNIXTIME(ltsh.CreatedOn / 1000000000), 'UTC',
da.DWHReportTimeZone)) AS TreatmentStatusChangeDate
FROM dbo.TreatmentProcedure src
LEFT JOIN dbo.ProcedureCode pc ON pc.AccountID = src.AccountId AND pc.ProcedureCodeID = src.ProcedureCodeId
LEFT JOIN dwl.DimProcedureCode FOR SYSTEM_TIME AS OF src.ProcTime AS ldpc
ON pc.AccountID IS NULL AND ldpc.AccountId = src.AccountId AND
ldpc.ProcedureCodeId = src.ProcedureCodeId
LEFT JOIN scheduler.Appointment ap ON ap.AccountID = src.AccountId AND ap.AppointmentID = src.AppointmentId
LEFT JOIN dwl.FactAppointment FOR SYSTEM_TIME AS OF src.ProcTime AS lfa
ON ap.AccountID IS NULL AND lfa.AccountId = src.AccountId AND lfa.AppointmentId = src.AppointmentId
LEFT JOIN staging.DimAppointmentProvider sdap
ON sdap.AccountId = src.AccountId AND sdap.AppointmentId = src.AppointmentId AND sdap.ProviderIndex = 1
LEFT JOIN dbo.Provider dp ON dp.AccountID = src.AccountId AND dp.ProviderID = src.ProviderId
LEFT JOIN dbo.vLatestTreatmentStatusHistory ltsh ON src.AccountId = ltsh.AccountId AND src.TreatmentProcedureId = ltsh.TreatmentProcedureId
/*+ BROADCAST(da) */
INNER JOIN staging.DimAccount da ON da.AccountId = src.AccountId;
Here, scheduler.Appointment is also a topic that is mapped to a flink table.
The problem is that I'm seeing that certain columns being populated from scheduler.Appointment (like AppointmentStatus and AppointmentLocationId) are not being populated properly during my initial sync (ie with a high event influx). What could be the problem?