डेटा इंजीनियरिंग इंटरव्यू: एक SCD Type 2 डायमेंशन डिज़ाइन करें

समस्या और दायरा (Problem and Scope)

एक कस्टमर सोर्स प्रतिदिन 50 मिलियन पंक्तियों का एक पूरा स्नैपशॉट भेजता है। हिस्ट्री ट्रैकिंग के लिए चुने गए एट्रिब्यूट्स में से लगभग 1% प्रतिदिन बदलते हैं। ऑर्डर्स लगातार आते रहते हैं, जबकि सोर्स करेक्शन्स तीन दिन तक की देरी से आ सकते हैं। एक Slowly Changing Dimension Type 2 कस्टमर डायमेंशन डिज़ाइन करें जो प्रत्येक ऑर्डर के समय प्रभावी मान (value) को सुरक्षित रखे, डिलीशन्स को संभाले, सुरक्षित री-रन्स (reruns) का समर्थन करे, और बैकफिल्स के बाद भी ऑडिट योग्य बना रहे।

मुख्य कार्य डायमेंशनल मॉडलिंग और टेम्पोरल शुद्धता (temporal correctness) है। Change Data Capture इनपुट इवेंट्स की आपूर्ति कर सकता है, लेकिन यह टार्गेट हिस्ट्री को परिभाषित नहीं करता है। उत्तर में यह तय होना चाहिए कि कौन से एट्रिब्यूट्स Type 2 ट्रीटमेंट के योग्य हैं, एक ड्यूरेबल बिज़नेस की (durable business key) और एक वर्ज़न की सरोगेट की (surrogate key) में अंतर करना चाहिए, गैर-ओवरलैपिंग वैलिडिटी इंटरवल्स को परिभाषित करना चाहिए, और यह समझाना चाहिए कि कोई फैक्ट सही वर्ज़न को कैसे रिज़ॉल्व करता है।

पैमाना (scale) लापरवाह विकल्पों को उजागर कर देता है। 1% दैनिक परिवर्तन दर का अर्थ है प्रति दिन लगभग 500,000 नए वर्ज़न्स और प्रति वर्ष 182.5 मिलियन। यदि एक जोड़े गए वर्ज़न का औसत 300 बाइट्स है, तो यह इंडेक्स, रेप्लिकास, मेटाडेटा और कम्प्रेशन से पहले प्रति वर्ष लगभग 54.75 GB का रॉ रो डेटा है। ये संख्याएँ इंटरव्यू की धारणाएँ और एक साइज़िंग बेसलाइन हैं, कोई स्टोरेज गारंटी नहीं।

इंटरव्यूअर्स क्या मूल्यांकन करते हैं

एक बुनियादी उत्तर कहता है, "पुरानी पंक्ति को एक्सपायर करें और एक नई पंक्ति इन्सर्ट करें।" एक मजबूत उत्तर सबसे पहले हिस्ट्री सेमेंटिक्स को परिभाषित करता है। Type 1 किसी मान को ओवरराइट करता है और उसकी पिछली स्थिति को खो देता है। Type 2 एक नई सरोगेट की के साथ एक नया वर्ज़न बनाता है और पुराने वर्ज़न को सुरक्षित रखता है। प्रत्येक सोर्स कॉलम को वर्ज़न ट्रिगर नहीं करना चाहिए: कैपिटलाइज़ेशन को सही करना Type 1 हो सकता है, जबकि हिस्टोरिकल रिपोर्टिंग में उपयोग किया जाने वाला सेल्स टेरिटरी या प्राइसिंग बैंड Type 2 हो सकता है।

अगला संकेत टेम्पोरल अनुशासन है। उम्मीदवार को एक इंटरवल कन्वेंशन जैसे कि [valid_from, valid_to) बताना चाहिए, यह सुनिश्चित करना चाहिए कि एक बिज़नेस की के वर्ज़न्स कभी ओवरलैप न हों, और केवल एक ही वर्तमान (current) वर्ज़न की अनुमति देनी चाहिए। समय t पर एक फैक्ट उस वर्ज़न से मेल खाता है जिसकी शुरुआत t पर या उससे पहले है और जिसका अंत t के बाद है। जब आवश्यकता बिज़नेस इफेक्टिव टाइम की हो, तब लोड टाइम का उपयोग करना लेट डेटा को चुपचाप गलत तरीके से एट्रिब्यूट कर देता है।

इंटरव्यूअर्स प्रोडक्शन बिहेवियर को भी देखते हैं। बार-बार चलाए गए बैच को दूसरा वर्ज़न नहीं बनाना चाहिए। एक अधूरा स्नैपशॉट लाखों कस्टमर्स को डिलीट नहीं करना चाहिए। वर्तमान पंक्ति को बंद करना और उसके रिप्लेसमेंट को इन्सर्ट करना एटॉमिक होना चाहिए। लेट करेक्शन्स के लिए केवल वर्तमान पंक्ति को बदलने के बजाय एक हिस्टोरिकल इंटरवल को विभाजित करने (splitting) की आवश्यकता हो सकती है। यदि बिज़नेस को दोनों की आवश्यकता है—"मान कब प्रभावी था" और "वेयरहाउस को इसके बारे में कब पता चला"—तो सामान्य SCD Type 2 अपर्याप्त है; वह एक बाइटेम्पोरल (bitemporal) आवश्यकता है।

उत्तर देने से पहले स्पष्ट करने योग्य प्रश्न

• कौन से एट्रिब्यूट्स हिस्टोरिकल एनालिसिस को प्रभावित करते हैं? केवल सहमति वाले Type 2 फ़ील्ड्स को ट्रैक करें। ऑडिट फ़ील्ड्स और इनजेशन टाइमस्टैम्प्स को बिज़नेस वर्ज़न्स नहीं बनाना चाहिए।
• क्या customer_id स्थिर है और कभी पुन: उपयोग नहीं किया जाता है? यह बिज़नेस की है। प्रत्येक हिस्टोरिकल वर्ज़न को एक अलग customer_sk सरोगेट की प्राप्त होती है।
• क्या सोर्स एक विश्वसनीय इफेक्टिव टाइम या सीक्वेंस प्रदान करता है? एक दैनिक स्नैपशॉट यह साबित करता है कि कोई मान कब देखा गया था, यह ज़रूरी नहीं कि वह कब सही हुआ। विश्वसनीय सोर्स टाइम के बिना, वेयरहाउस एक सही बैकडेटेड बाउंड्री का आविष्कार नहीं कर सकता।
• क्या प्रत्येक स्नैपशॉट को स्पष्ट रूप से पूर्ण चिह्नित किया गया है? पूर्णता (completeness), रो-काउंट, और कंट्रोल-टोटल चेक्स पास होने के बाद ही अनुपस्थिति को डिलीशन मानें। एक आंशिक एक्सट्रैक्ट कोई डिलीट फ़ीड नहीं है।
• डिलीट का क्या अर्थ होना चाहिए? यह डिज़ाइन सक्रिय वर्ज़न को बंद करता है और is_deleted = true के साथ एक वर्तमान टॉम्बस्टोन (tombstone) वर्ज़न इन्सर्ट करता है, जिससे एक स्पष्ट डिलीशन बाउंड्री सुरक्षित रहती है।
• क्या फैक्ट्स इनजेशन के समय customer_sk स्टोर करते हैं या क्वेरी के समय टाइम द्वारा जॉइन करते हैं? फैक्ट लोडिंग के दौरान सरोगेट की को रिज़ॉल्व करना बाद की क्वेरीज़ को सरल बनाता है। एक टेम्पोरल जॉइन बैकफिल्स और वैलिडेशन के लिए उपयोगी बना रहता है।
• क्या करेक्शन्स को पूर्व बिज़नेस हिस्ट्री को फिर से लिखने की अनुमति है? यदि हाँ, तो रॉ सोर्स वर्ज़न्स और एक ऑडिट ट्रेल रखें क्योंकि एक बैकफ़िल वैध रूप से पहले के विश्लेषणात्मक परिणामों को बदल सकता है।
• क्या सिस्टम को बिज़नेस टाइम के साथ-साथ नॉलेज टाइम को भी बनाए रखना चाहिए? यदि ऑडिटर्स को दोनों की आवश्यकता है, तो दोनों को एक इंटरवल में ज़बरदस्ती डालने के बजाय वैलिड टाइम और सिस्टम टाइम को अलग-अलग मॉडल करें।

30-सेकंड का उत्तर

I would first define the business key, Type 2 attributes, trusted effective time, and delete policy.
Each version gets a surrogate key and a half-open [valid_from, valid_to) interval; one version
per customer is current. I stage and deduplicate a complete snapshot, hash only tracked attributes,
then classify rows as unchanged, new, changed, or deleted. A changed key closes the old version and
inserts the new one atomically. The batch ID and source version make reruns idempotent. Facts resolve
the version effective at order time. Late corrections split the historical interval they affect, and
validation rejects duplicate current rows, overlapping intervals, broken fact references, or an
implausible delete surge.

चरण-दर-चरण विस्तृत विश्लेषण (Step-by-Step Deep Dive)

चरण 1: लोड करने से पहले ग्रेन और कॉलम्स को परिभाषित करें

ग्रेन (grain) एक वैलिडिटी इंटरवल के दौरान एक कस्टमर का एक वर्ज़न है। एक व्यावहारिक टेबल में शामिल हैं:

dim_customer {
  customer_sk       // surrogate primary key for this version
  customer_id       // durable business key from the source
  segment
  sales_region
  valid_from
  valid_to           // null means open-ended
  is_current
  is_deleted
  change_hash        // canonical hash of Type 2 attributes only
  source_version
  load_batch_id
}

[valid_from, valid_to) का लगातार उपयोग करें। जब कोई परिवर्तन 2026-07-10T09:00:00Z पर प्रभावी होता है, तो पुरानी पंक्ति उसी क्षण समाप्त हो जाती है और नई पंक्ति उसी क्षण शुरू होती है। हाफ-ओपन कन्वेंशन बाउंड्री को सटीक रूप से एक वर्ज़न से संबंधित बनाता है। टाइमस्टैम्प्स को एक निश्चित टाइमज़ोन और प्रिसिजन में स्टोर करें; डेट्स, लोकल टाइम और UTC को मिलाने से कृत्रिम गैप्स या डबल मैचेस बनते हैं।

बिज़नेस की वर्ज़न्स को समूहित करती है। सरोगेट की एक अपरिवर्तनीय (immutable) हिस्टोरिकल स्थिति की पहचान करती है और फैक्ट्स द्वारा संग्रहीत फॉरेन की होती है। is_current एक सुविधा है, कोई स्वतंत्र सत्य नहीं: इसे valid_to IS NULL से सहमत होना चाहिए। हैश केवल एक तुलना ऑप्टिमाइज़ेशन है। Nulls, टाइप्स, यूनिकोड और कॉलम क्रम को कैनोनिकलाइज़ करें, और फिर भी व्याख्या और ऑडिट के लिए वास्तविक ट्रैक किए गए कॉलम्स को सुरक्षित रखें।

चरण 2: राइट और स्कैन पाथ का आकार निर्धारित करें (Size the write and scan path)

दैनिक स्नैपशॉट 50 मिलियन सोर्स पंक्तियों को स्कैन करता है। 1% परिवर्तन दर पर:

50,000,000 × 1% = 500,000 new versions/day
500,000 × 365 = 182,500,000 new versions/year
182,500,000 × 300 bytes ≈ 54.75 GB/year of raw row data

यह स्कैन लागत को चेंज-राइट लागत से अलग करता है। स्नैपशॉट को एक बार स्टेज करें, केवल आवश्यक कॉलम्स को प्रोजेक्ट करें, और बिज़नेस की द्वारा वर्तमान डायमेंशन पंक्तियों के साथ इसकी तुलना करें। पार्टीशनिंग या क्लस्टरिंग को हज़ारों छोटे दैनिक पार्टीशन्स बनाने के बजाय वास्तविक क्वेरी और मेंटेनेंस पैटर्न्स का पालन करना चाहिए—अक्सर लुकअप के लिए बिज़नेस की और प्रूनिंग के लिए वैलिडिटी टाइम। प्रोडक्शन-आकार के डेटा के साथ इंडेक्स, कॉलमर कम्प्रेशन, रिटेंशन और बैकफ़िल एम्प्लीफिकेशन को मापें।

चरण 3: सामान्य बैच को डिटरमिनिस्टिक और एटॉमिक बनाएं

एक्सट्रैक्ट को एक इम्यूटेबल snapshot_id के तहत लैंड करें। टार्गेट को छूने से पहले, कम्प्लीशन मार्कर, स्कीमा, अपेक्षित की विशिष्टता (uniqueness), रो-काउंट और कंट्रोल टोटल्स को सत्यापित करें। एक विश्वसनीय सोर्स वर्ज़न का उपयोग करके customer_id द्वारा डिडुप्लिकेट करें; दो समान रूप से रैंक की गई लेकिन अलग-अलग पंक्तियाँ एक इनपुट त्रुटि हैं, किसी एक को मनमाने ढंग से चुनने का कारण नहीं।

ट्रैक किए गए एट्रिब्यूट्स को कैनोनिकलाइज़ करें और प्रत्येक स्टेज की गई पंक्ति की तुलना वर्तमान टार्गेट वर्ज़न से करें:

1. कोई वर्तमान बिज़नेस की नहीं: इसका पहला वर्ज़न इन्सर्ट करें।
2. समान ट्रैक किए गए मान: कुछ न करें; loaded_at को रीफ्रेश करने से इतिहास का निर्माण नहीं होना चाहिए।
3. भिन्न ट्रैक किए गए मान: विश्वसनीय इफेक्टिव टाइम पर वर्तमान पंक्ति को बंद करें और एक नया वर्तमान वर्ज़न इन्सर्ट करें।
4. सत्यापित पूर्ण स्नैपशॉट से वर्तमान टार्गेट की अनुपस्थित है: इसे बंद करें और एक वर्तमान टॉम्बस्टोन वर्ज़न इन्सर्ट करें।

प्रत्येक बदले गए की के लिए, क्लोज़ और इन्सर्ट एक टार्गेट ट्रांज़ैक्शन या एक एटॉमिक टेबल ऑपरेशन में होते हैं। वर्तमान वर्ज़न पर एक विशिष्टता नियम (uniqueness rule) दो समवर्ती (concurrent) लोडर्स से बचाता है। snapshot_id, load_batch_id, और source_version रिकॉर्ड करें, और पहले से लागू सोर्स वर्ज़न को अस्वीकार करें। खोए हुए पावती (acknowledgement) के बाद एक पुनः प्रयास इतिहास को डुप्लिकेट किए बिना कन्वर्ज हो जाता है।

चरण 4: बिज़नेस इवेंट टाइम पर फैक्ट्स को रिज़ॉल्व करें

ऑर्डर लोड करते समय, ऑर्डर के बिज़नेस टाइमस्टैम्प का उपयोग करके कस्टमर वर्ज़न को रिज़ॉल्व करें, फिर फैक्ट में customer_sk को बनाए रखें (persist)। एक वेंडर-न्यूट्रल पॉइंट-इन-टाइम लुकअप का यह आकार होता है:

SELECT d.customer_sk
FROM dim_customer AS d
WHERE d.customer_id = :customer_id
  AND d.is_deleted = false
  AND d.valid_from <= :order_time
  AND (d.valid_to > :order_time OR d.valid_to IS NULL);

असमानताएँ (inequalities) हाफ-ओपन इंटरवल को एनकोड करती हैं। लुकअप को सटीक रूप से एक पंक्ति लौटानी चाहिए। शून्य मैचेस के लिए एक स्पष्ट अज्ञात-सदस्य (unknown-member) या क्वारंटाइन नीति की आवश्यकता होती है; मल्टीपल मैचेस दूषित इतिहास को साबित करते हैं। देर से आने वाले फैक्ट के लिए, उसके इवेंट टाइम का उपयोग करें, न कि उसके इनजेशन टाइम का। किम्बल का Type 2 पैटर्न फैक्ट्स में वर्ज़न की सरोगेट की का उपयोग सटीक रूप से इसलिए करता है ताकि पहले लोड किए गए फैक्ट्स समकालीन हिस्टोरिकल प्रोफाइल को बनाए रखें।

चरण 5: लेट करेक्शन्स को इंटरवल सर्जरी के रूप में समझें

मान लीजिए कि वेयरहाउस में वर्तमान में 1 जुलाई से सिएटल और 12 जुलाई से डेनवर है। 14 जुलाई को इसे एक विश्वसनीय करेक्शन प्राप्त होता है जिसमें कहा गया है कि डेनवर 10 जुलाई को प्रभावी हुआ था। केवल वर्तमान पंक्ति को अपडेट करने से 10 जुलाई और 11 जुलाई गलत रह जाते हैं। 10 जुलाई वाले वर्ज़न को खोजें, सिएटल को 10 जुलाई को बंद करें, और डेनवर की शुरुआत को 10 जुलाई पर ले जाएँ। यदि करेक्शन किसी मौजूदा इंटरवल के अंदर एक तीसरा मान पेश करता है, तो उस इंटरवल को विभाजित करें और अगली ज्ञात बाउंड्री को सुरक्षित रखें।

सोर्स-वर्ज़न क्रम में करेक्शन्स लागू करें और प्रति बिज़नेस की अपडेट्स को लॉक या सीरियलाइज़ करें। रॉ इनपुट और एक करेक्शन ऑडिट रखें जिसमें पिछले इंटरवल्स, नए इंटरवल्स, सोर्स वर्ज़न, बैच और कारण शामिल हों। प्रभावित फैक्ट्स को फिर से रिज़ॉल्व करें जब बिज़नेस कॉन्ट्रैक्ट कहता है कि उनकी फॉरेन कीज़ को सही किए गए इतिहास को प्रतिबिंबित करना चाहिए।

एक सूचना सीमा है: 14 जुलाई को पहली बार देखा गया स्नैपशॉट यह साबित नहीं कर सकता कि कोई मान 10 जुलाई को प्रभावी हुआ था, जब तक कि सोर्स में भरोसेमंद बिज़नेस टाइम या कोई अन्य ऑर्डर्ड रिकॉर्ड न हो। 14 जुलाई को ऑब्जर्वेशन टाइम के रूप में उपयोग करें या करेक्शन को क्वारंटाइन करें; इसे चुपचाप बैकडेट करने से मनगढ़ंत सटीकता (fabricated precision) पैदा होगी। यदि वेयरहाउस को 10 जुलाई के वैलिड टाइम और 14 जुलाई के नॉलेज टाइम दोनों को बनाए रखना चाहिए, तो एक दूसरा सिस्टम-टाइम इंटरवल जोड़ें और मॉडल को बाइटेम्पोरल कहें।

चरण 6: इनवेरिएंट्स और ऑपरेशनल गार्डरेल्स को मान्य करें

प्रत्येक बैच के बाद और प्रकाशन से पहले टार्गेट का परीक्षण करें:

• प्रत्येक सरोगेट की अद्वितीय (unique) है;
• प्रत्येक बिज़नेस की का सटीक रूप से एक वर्तमान वर्ज़न होता है, जिसमें डिलीट की गई की के लिए टॉम्बस्टोन भी शामिल है;
• is_current एक ओपन valid_to से सहमत है;
• एक बिज़नेस की के लिए इंटरवल्स कभी ओवरलैप नहीं होते हैं और जब अंत मौजूद होता है तो प्रत्येक पंक्ति में valid_from, valid_to से पहले होता है;
• अपरिवर्तित सोर्स मान कोई वर्ज़न नहीं जोड़ते हैं;
• समान सोर्स वर्ज़न या बैच रीप्ले से कोई नई पंक्तियाँ उत्पन्न नहीं होती हैं;
• प्रत्येक नॉन-अज्ञात फैक्ट सरोगेट की फैक्ट के इवेंट टाइम पर प्रभावी डायमेंशन पंक्ति को संदर्भित करती है;
• इन्सर्ट, चेंज्ड, अनचेंज्ड और डिलीटेड काउंट्स स्टेज किए गए स्नैपशॉट से मेल खाते हैं (reconcile)।

स्नैपशॉट पूर्णता, डुप्लिकेट कीज़, परिवर्तन दर, डिलीट दर, वर्ज़न वृद्धि, अस्वीकृत लेट करेक्शन्स, शून्य- और मल्टी-मैच फैक्ट लुकअप्स, लोड लेटेंसी और री-रन डेल्टा की निगरानी करें। 40 मिलियन कीज़ का अचानक गायब होना इससे पहले कि वह 40 मिलियन टॉम्बस्टोन बन जाए, फेल क्लोज्ड (fail closed) होना चाहिए। एक नई की, बार-बार समान स्नैपशॉट, सामान्य परिवर्तन, डिलीट और रीक्रिएट, तीन दिन की देरी से करेक्शन, आउट-ऑफ-ऑर्डर सोर्स वर्ज़न, आंशिक स्नैपशॉट, और क्लोज़ व इन्सर्ट के बीच क्रैश का परीक्षण करें।

मजबूत नमूना उत्तर

"मैं प्रति कस्टमर एक पंक्ति के बजाय प्रति कस्टमर वर्ज़न एक पंक्ति मॉडल करूँगा। customer_id ड्यूरेबल बिज़नेस की है, जबकि प्रत्येक वर्ज़न को एक नया customer_sk मिलता है। विश्लेषकों के साथ Type 2 फ़ील्ड्स पर सहमति होती है—जैसे सेगमेंट और सेल्स रीजन—ताकि लोड टाइमस्टैम्प या फॉर्मेटिंग सुधार इतिहास न बनाए। प्रत्येक पंक्ति हाफ-ओपन इंटरवल [valid_from, valid_to) का उपयोग करती है, और प्रति कस्टमर सटीक रूप से एक पंक्ति वर्तमान होती है।

सोर्स प्रतिदिन 50 मिलियन पंक्तियों को स्कैन करता है लेकिन लगभग 500,000 को बदलता है। इसका मतलब है प्रति वर्ष लगभग 182.5 मिलियन नए वर्ज़न्स; प्रति वर्ज़न 300 बाइट्स के उदाहरण पर, फिजिकल ओवरहेड से पहले रॉ ग्रोथ लगभग 54.75 GB है। मैं स्नैपशॉट को एक बार स्टेज करूँगा और पूरी हिस्ट्री को बार-बार जॉइन करने के बजाय केवल वर्तमान डायमेंशन पंक्तियों के साथ इसकी तुलना करूँगा।

प्रत्येक स्नैपशॉट में एक स्थिर ID होती है और इसे पूर्णता, विशिष्टता, स्कीमा, रो-काउंट और कंट्रोल-टोटल चेक्स पास करने होंगे। मैं केवल ट्रैक किए गए Type 2 कॉलम्स को कैनोनिकलाइज़ और हैश करता हूँ। नई कीज़ इन्सर्ट की जाती हैं, समान हैश कुछ नहीं करते हैं, और बदली हुई कीज़ पुराने इंटरवल को बंद करती हैं और एक एटॉमिक ऑपरेशन में एक नया वर्ज़न इन्सर्ट करती हैं। एक सत्यापित गायब की पुराने वर्ज़न को बंद करती है और एक डिलीशन टॉम्बस्टोन इन्सर्ट करती है। सोर्स वर्ज़न प्लस बैच ID लोड को आइडम्पोटेंट बनाता है, और एक करंट-रो विशिष्टता नियम समवर्ती डुप्लिकेट वर्ज़न्स को रोकता है।

ऑर्डर्स customer_sk को valid_from <= order_time और valid_to > order_time के साथ रिज़ॉल्व करते हैं, नल एंड को ओपन मानते हैं। लुकअप को एक नॉन-डिलीटेड वर्ज़न लौटाना चाहिए। लेट फैक्ट्स ऑर्डर टाइम का उपयोग करते हैं, अराइवल टाइम का नहीं। लेट डायमेंशन करेक्शन्स उनके विश्वसनीय इफेक्टिव टाइम वाले इंटरवल पर लागू होते हैं: उस इंटरवल को विभाजित या समायोजित करें और बाद की ज्ञात बाउंड्रीज़ को सुरक्षित रखें। यदि सोर्स केवल अराइवल टाइम देता है, तो मैं तीन दिन पहले का प्रभावी समय नहीं बनाऊँगा। यदि बिज़नेस टाइम और वेयरहाउस-नॉलेज टाइम दोनों को क्वेरी योग्य होना चाहिए, तो मैं एक बाइटेम्पोरल मॉडल का प्रस्ताव करूँगा।

प्रकाशन से पहले, मैं प्रति बिज़नेस की एक वर्तमान वर्ज़न, कोई इंटरवल ओवरलैप नहीं, वैध बाउंड्रीज़, फैक्ट रेफरेंशियल इंटीग्रिटी, सोर्स-टू-टार्गेट काउंट्स और एक समान री-रन पर शून्य-रो परिवर्तन की जांच करता हूँ। मैं असामान्य डिलीट या परिवर्तन दरों पर अलर्ट करता हूँ और रॉ स्नैपशॉट्स के साथ-साथ एक करेक्शन ऑडिट रखता हूँ ताकि बैकफ़िल व्याख्या योग्य और प्रतिवर्ती (reversible) हो।"

सामान्य गलतियाँ (Common Mistakes)

• प्रत्येक सोर्स कॉलम को हैश करना → ऑडिट टाइमस्टैम्प्स अर्थहीन वर्ज़न्स बनाते हैं → कैनोनिकलाइज़ेशन के बाद केवल अनुबंधित रूप से ट्रैक किए गए Type 2 एट्रिब्यूट्स को हैश करें।
• नेचुरल की को डायमेंशन प्राइमरी की के रूप में उपयोग करना → कई हिस्टोरिकल वर्ज़न्स आपस में टकराते हैं → बिज़नेस की द्वारा समूहित करें और प्रत्येक वर्ज़न को एक सरोगेट की से पहचानें।
• समावेशी (inclusive) इंटरवल एंड्स को मिलाना → परिवर्तन बाउंड्री पर एक फैक्ट दो पंक्तियों से मेल खाता है → शुरू से अंत तक [valid_from, valid_to) को अपनाएं।
• इनजेशन टाइम को इफेक्टिव टाइम के रूप में उपयोग करना → लेट अपडेट्स और फैक्ट्स गलत हिस्टोरिकल स्थिति से जुड़ते हैं → विश्वसनीय बिज़नेस टाइम का उपयोग करें और अनुपलब्ध होने पर फॉलबैक बताएं।
• प्रत्येक गायब स्नैपशॉट पंक्ति को डिलीटेड मानना → एक आंशिक एक्सट्रैक्ट डायमेंशन को मिटा सकता है → अनुपस्थिति सेमेंटिक्स लागू करने से पहले एक पूर्णता मार्कर और विसंगति चेक्स की आवश्यकता होती है।
• अलग-अलग कमिट्स में क्लोज़ और इन्सर्ट करना → एक क्रैश शून्य या दो वर्तमान पंक्तियाँ छोड़ देता है → वर्ज़न ट्रांज़िशन को एटॉमिक रूप से लागू करें और करंट-रो विशिष्टता लागू करें।
• प्रत्येक री-रन पर एक वर्ज़न बनाना → पुनः प्रयास इतिहास को बढ़ाते हैं और पिछले उत्तरों को बदलते हैं → सोर्स वर्ज़न्स और बैच पहचान को बनाए रखें, फिर समान इनपुट को नो-ऑप (no-op) बनाएं।
• लेट करेक्शन के लिए केवल वर्तमान पंक्ति की मरम्मत करना → पहले के फैक्ट्स गलत स्थिति से जुड़े रहते हैं → प्रभावित हिस्टोरिकल इंटरवल को विभाजित या समायोजित करें और प्रभावित फैक्ट विंडो को फिर से रिज़ॉल्व करें।
• किन्हीं दो टाइमस्टैम्प्स को "बाइटेम्पोरल" कहना → उनके अर्थ अस्पष्ट रहते हैं → वैलिड टाइम और सिस्टम टाइम का नाम दें, और दोनों इंटरवल्स और करेक्शन नियमों को परिभाषित करें।
• केवल रो काउंट्स की जांच करना → ओवरलैप्स और डुप्लिकेट वर्तमान पंक्तियाँ बच सकती हैं → टेम्पोरल, रेफरेंशियल, आइडम्पोटेंसी और रीकंसीलिएशन इनवेरिएंट्स को मान्य करें।

फॉलो-अप प्रश्न और उत्तर

फॉलो-अप 1: प्रत्येक एट्रिब्यूट के लिए Type 1 का उपयोग क्यों नहीं किया जाता?

Type 1 उन मानों के लिए सही है जिनके पूर्व रूप का कोई विश्लेषणात्मक अर्थ नहीं है, जैसे कि टाइपो सुधार। यह पुराने मान को नष्ट कर देता है, इसलिए यह उत्तर नहीं दे सकता कि "उस समय इस ऑर्डर का स्वामित्व किस क्षेत्र के पास था?" रिपोर्टिंग सेमेंटिक्स द्वारा एट्रिब्यूट्स को वर्गीकृत करें। एक ही डायमेंशन कुछ कॉलम्स के लिए Type 1 और दूसरों के लिए Type 2 का उपयोग कर सकता है, बशर्ते अपडेट नियम स्पष्ट हों।

फॉलो-अप 2: क्या वर्तमान पंक्ति को valid_to = NULL या दूर के भविष्य के प्रहरी (sentinel) का उपयोग करना चाहिए?

दोनों काम कर सकते हैं। NULL "ओपन-एंडेड" को स्पष्ट बनाता है लेकिन नल-अवेयर प्रेडिकेट्स की आवश्यकता होती है। एक प्रहरी जैसे कि अधिकतम समर्थित टाइमस्टैम्प रेंज फिल्टर को सरल बना सकता है लेकिन रिपोर्टों में लीक हो सकता है या किसी अन्य इंजन की डेट रेंज से अधिक हो सकता है। एक प्रतिनिधित्व चुनें, is_current को इसके अनुरूप बनाएं, और उसी कन्वेंशन पर प्रत्येक क्वेरी और कनेक्टर का परीक्षण करें।

फॉलो-अप 3: आप किसी डिलीट और उसके बाद उसी बिज़नेस की के पुन: निर्माण (recreation) को कैसे संभालते हैं?

हटाने के समय सक्रिय बिज़नेस वर्ज़न को बंद करें और एक डिलीशन टॉम्बस्टोन जोड़ें। पुन: निर्माण के समय, टॉम्बस्टोन को बंद करें और एक नई सरोगेट की के साथ एक नया सक्रिय वर्ज़न इन्सर्ट करें। पहले पुष्टि करें कि क्या सोर्स वास्तव में समान एंटिटी पहचान का पुन: उपयोग करता है; यदि पहचानकर्ता को किसी भिन्न व्यक्ति के लिए पुनर्चक्रित (recycled) किया गया था, तो एक ड्यूरेबल की प्रस्तुत करें जो एंटिटीज़ में अंतर करती है।

फॉलो-अप 4: क्या होगा यदि सोर्स के पास कोई विश्वसनीय updated_at नहीं है?

कैनोनिकल ट्रैक किए गए कॉलम्स की एक स्पष्ट सूची की तुलना करें, जैसा कि स्नैपशॉट टूल्स की चेक रणनीतियाँ करती हैं। परिणामी इंटरवल तब शुरू होता है जब वेयरहाउस परिवर्तन को देखता है, जरूरी नहीं कि जब बिज़नेस परिवर्तन हुआ हो। उस सीमा का दस्तावेजीकरण करें। यदि सटीक बिज़नेस-इफेक्टिव इतिहास की आवश्यकता है, तो टाइमस्टैम्प बनाने के बजाय एक सोर्स सीक्वेंस, ऑडिट लॉग, CDC फ़ीड, या डोमेन इवेंट प्राप्त करें।

फॉलो-अप 5: यह CDC पाइपलाइन से किस प्रकार भिन्न है?

CDC उत्तर देता है कि कौन से कमिट किए गए इन्सर्ट, अपडेट और डिलीट हुए और किस सोर्स क्रम में हुए। SCD Type 2 उत्तर देता है कि कैसे चयनित डायमेंशन एट्रिब्यूट्स हिस्टोरिकल वर्ज़न बनते हैं और एक फैक्ट को किस सरोगेट की को संदर्भित करना चाहिए। एक CDC फ़ीड SCD लोडर को चला सकती है, और एक स्नैपशॉट भी इसे चला सकता है। विश्वसनीय कैप्चर अपने आप में ओवरलैपिंग वैलिडिटी इंटरवल्स या गलत पॉइंट-इन-टाइम जॉइन्स को नहीं रोकता है।

फॉलो-अप 6: आपको Type 2 रो ग्रोथ से कब बचना चाहिए?

तेजी से बदलते एट्रिब्यूट्स के लिए इससे बचें जिनकी हिस्टोरिकल ग्रुपिंग के लिए आवश्यकता नहीं है, बड़े फ्री-फॉर्म पेलोड्स, और ऑपरेशनल स्थिति जिन्हें इवेंट्स या फैक्ट्स के रूप में बेहतर दर्शाया जाता है। क्वेरी के अनुसार Type 1, एक अलग मिनी-डायमेंशन, एक एक्युमुलेटिंग या पीरियोडिक फैक्ट, या एक समर्पित हिस्ट्री टेबल का उपयोग करें। निर्णय विश्लेषणात्मक सेमेंटिक्स और मापी गई राइट/क्वेरी लागत का पालन करता है।

फॉलो-अप 7: आप कैसे साबित करते हैं कि देर से किए गए बैकफ़िल ने इतिहास को दूषित नहीं किया?

स्टेजिंग या शैडो टेबल में बैकफ़िल चलाएं, बिज़नेस की द्वारा इंटरवल सेट की तुलना करें, और इन्सर्टेड, स्प्लिट, शॉर्टन्ड, एक्सटेंडेड और डिलीटेड वर्ज़न्स की रिपोर्ट करें। पुष्टि करें कि कोई ओवरलैप नहीं है, एक वर्तमान पंक्ति है, स्थिर अप्रभावित सरोगेट कीज़ हैं, और केवल सही किए गए समय विंडो के अंदर अपेक्षित फैक्ट-की परिवर्तन हैं। रीप्ले और रोलबैक के लिए रॉ सोर्स वर्ज़न और बैच मेनिफेस्ट बनाए रखें।