Incremental sync from the dedicated Redshift cluster into another warehouse can be accomplished by making use of the fact and dimension tables. It cannot be done directly from the consolidated data sources. If consolidated data sources are required in the warehouse, the business can perform incremental sync using the fact and dimension tables and then use the SQLs to generate the consolidated data sources from the facts & dimensions. This incremental sync can be done once a day during overnight hours. It cannot be done multiple times a day.

The following are the steps to perform incremental sync of the fact and dimension tables:

  1. Do a one-time full load sync of the data.

  2. From then onwards, note down the time stamp that the last sync was initiated and use that for the next incremental cycle.

  3. Before pulling incremental load, the records that have been reprocessed since the last sync have to be deleted. To do this:
    a. Identify all the PKs that got updated in the Bi_DimFactChanges table since the time the last sync was done. Use the etlcreated timestamp to identify the updates.

    b. Identify all the PKs that got updated in the fact and dimension tables in the dedicated Redshift since the time the last sync was done. Use the etlcreated timestamp in each table to identify the updates.

    c. Compile all the distinct PKs from steps a and b and delete these records from the tables in your warehouse.

  4. Next, pull in the incremental load by identifying all the PKs that got updated in the fact and dimension tables in the dedicated Redshift since the time the last sync was done. Use the etlcreated timestamp in each table to identify the updates. Append these records to the tables in your warehouse. Note: Records are never deleted from dimension tables, they are only updated.

Maintain a log of the timestamps (in UTC) when each sync is initiated to help identify the delta load for the next sync.

Use this table to see how to join each fact to the Bi_DimFactChanges table.

Fact Table

Column from Fact to be joined with Bi_DimFactChanges (factpk)

Bi_FactEmployeeCommission

FactEmployeeCommissionId

Bi_FactEmployeeSalary

FactEmployeeSalaryId

Bi_FactEmployeeSchedule

FactEmployeeScheduleId

Bi_FactEmployeePay

FactEmployeePayId

Bi_FactIssues

IssuePK

Bi_FactInvoiceItem

factinvoiceitemid

Bi_FactCollections

factcollectionid

Bi_FactAppointment

factAppointmentid

Bi_FactTurnAways

factsegmentid

Bi_FactOpportunityCustomFieldValues

opportunityfieldvalueid

Bi_FactScheduledCollections

factsegmentid

Bi_FactPackageUserRedemptions

factsegmentid

Bi_FactDeletedAppointment

factsegmentid

Bi_FactEmployeeTipsAndSSGDetails

factsegmentid

Bi_FactMultipleEmployeeCommission

empcommissionpk

Bi_FactCampaignSummary

FactCampaignId

Bi_FactMembershipUserRedemptions

factsegmentid

Bi_FactPettyCashDetails

FactSegmentID

Bi_FactCashRegister

factcashregisterid

Bi_FactItemCommissionDeductions

deductionpk

Bi_FactEmployeeItemLevelCommissions

employeeitemid

Bi_FactEmployeeMembershipCommissions

empmembershippk

Bi_FactFeedBackV2

FeedBackId

Bi_FactLoyaltyPoints

LoyaltyPointsId

Bi_FactGuestTierChanges

GuestTierChangesId

Bi_FactGuestMergeLog

GuestMergeHistoryPK

Bi_FactGiftCards

factgiftcardid

Bi_FactCampaignDiscount

factcampaigndiscountid

Bi_FactEmpSalary

FactEmployeeSalaryId

Bi_FactEmployeeInvoiceItemRevenue

FactEmployeeInvoiceItemRevenueID

Bi_DimUserMembership

DimUserMembershipId

Did this answer your question?