प्रॉम्प्ट और यह कब लागू होता है
यह डायमेंशनल मॉडलिंग और ऐतिहासिक सिमेंटिक्स (historical semantics) से संबंधित प्रश्न है। जब किसी वित्तीय रिपोर्ट को यह बताना हो कि कोई ग्राहक पिछली किसी तारीख पर किस क्षेत्र से संबंधित था, तब ग्राहक के पते को केवल नवीनतम मान (latest value) के रूप में नहीं माना जा सकता है। मान लें कि दैनिक पूर्ण स्नैपशॉट आते हैं, कोई चेंज लॉग नहीं है, और लक्ष्य प्रणाली वर्तमान स्थिति, ऐतिहासिक लुकअप और रीप्ले का समर्थन करती है। Dataquest डेटा इंजीनियरिंग इंटरव्यू के प्रश्नों में SCD Types 1, 2, और 3 को सूचीबद्ध करता है; AWS बिना CDC के पूर्ण फ़ाइलों से Type 2 को प्रदर्शित करता है।
इंटरव्यूअर क्या जांच रहा है
- किसी प्रकार को चुनने से पहले यह परिभाषित करना कि रिपोर्ट "अब" की स्थिति पूछती है या "उस समय" की।
- ओवरराइट (overwrite), वर्ज़न जोड़ना (append-a-version), और एक पिछला मान रखने के सिमेंटिक्स में अंतर करना।
- नेचुरल की (natural keys), सरोगेट की (surrogate keys), प्रभावी समय (effective times), और करंट फ़्लैग (current flag) के साथ विशिष्टता (uniqueness) बनाए रखना।
- केवल एक
UPDATEलिखने के बजाय विलंबित स्नैपशॉट, डुप्लिकेट फ़ाइलें, डिलीट, रीप्ले और समवर्ती राइट्स (concurrent writes) को संभालना।
पहले पूछे जाने वाले स्पष्टीकरण
- क्या रिपोर्टों को ईवेंट के समय की स्थिति का पुनर्निर्माण करना आवश्यक है, या केवल वर्तमान प्रोफ़ाइल पर्याप्त है?
- क्या स्नैपशॉट में कोई वर्ज़न, एक्सट्रैक्ट का समय, या वॉटरमार्क होता है, और क्या वे क्रम से बाहर (out of order) आ सकते हैं?
- क्या डेटा की अनुपस्थिति का अर्थ बिज़नेस डिलीट है, अस्थायी चूक है, या स्रोत में कोई खराबी है?
- क्या प्रकाशित इतिहास और फ़ैक्ट जॉइन्स को संशोधित किया जा सकता है, और क्या ऑडिट, रीप्ले तथा आइडम्पोटेंट (idempotent) पुनर्प्रयास आवश्यक हैं?
30-सेकंड का उत्तर
"मैं समय के सिमेंटिक्स को स्पष्ट करता हूँ: Type 1 केवल वर्तमान मान रखता है, Type 2 valid_from और valid_to के साथ प्रत्येक बिज़नेस परिवर्तन के लिए एक वर्ज़न जोड़ता है, और Type 3 एक या कुछ पिछले मान रखता है। पॉइंट-इन-टाइम विश्लेषण के लिए मैं Type 2 चुनता हूँ, नेचुरल की द्वारा वर्तमान पंक्ति को खोजता हूँ, सरोगेट की के माध्यम से फ़ैक्ट्स को जोड़ता हूँ, और विलंबित डेटा तथा डिलीट्स की पहचान करने के लिए स्नैपशॉट वॉटरमार्क का उपयोग करता हूँ। मैं प्रत्येक बैच को लैंड और डिडुप्लिकेट करता हूँ, फिर पुराने वर्ज़न को क्लोज़ करके नए वर्ज़न को एटॉमिक रूप से इंसर्ट करता हूँ; उसी बैच को फिर से चलाने पर समान परिणाम मिलना चाहिए।"
चरण-दर-चरण समाधान
चरण 1: ऐतिहासिक प्रश्न को परिभाषित करें
Type 1 बिना इतिहास वाले सुधारों या प्रोफ़ाइलों के लिए उपयुक्त है और यह उसी स्थान पर ओवरराइट करता है। Type 2 प्रत्येक वर्ज़न को सुरक्षित रखकर ऑडिट, रेवेन्यू एट्रिब्यूशन और पॉइंट-इन-टाइम क्वेरीज़ के लिए उपयुक्त है। Type 3 एक निश्चित 'वर्तमान बनाम पिछला' विंडो के लिए उपयुक्त है। यदि आवश्यकता पिछली तिमाही में ग्राहक के क्षेत्र की मांग करती है, तो Types 1 और 3 जानकारी खो देते हैं, इसलिए Type 2 चुनें।
चरण 2: Type 2 के इनवेरिएंट्स सेट करें
एक सरोगेट की, नेचुरल की, एट्रिब्यूट्स, valid_from, valid_to, is_current, और स्रोत-बैच वॉटरमार्क स्टोर करें। प्रत्येक नेचुरल की के लिए, अधिकतम एक पंक्ति में is_current = true होता है; प्रभावी अंतराल (effective intervals) ओवरलैप नहीं हो सकते। AWS पूर्ण इतिहास के लिए प्रारंभ/समाप्ति तिथियों, एक करंट फ़्लैग और लॉजिकल डिलीट्स का उपयोग करता है, जबकि Microsoft के Type 2 कॉन्फ़िगरेशन के लिए एक नेचुरल की, सरोगेट की, दो तिथियों और एक एक्टिव फ़्लैग की आवश्यकता होती है।
चरण 3: पूर्ण स्नैपशॉट से परिवर्तनों का अनुमान लगाएं
फ़ाइल हैश, निष्कर्षण समय और बैच अनुक्रम के साथ फ़ाइल को एक इम्यूटेबल लैंडिंग टेबल में लिखें। वर्तमान डायमेंशन के विरुद्ध प्रत्येक नेचुरल की के लिए एक एट्रिब्यूट हैश की तुलना करें: नई कुंजियों को इंसर्ट करें, परिवर्तित कुंजियों को क्लोज़ करके अपेंड करें, और समान हैश को छोड़ दें। कोई अनुपस्थित कुंजी केवल तभी डिलीट मानी जा सकती है जब पूर्ण-स्नैपशॉट अनुबंध (complete-snapshot contract) सत्य हो; एक इंक्रीमेंटल फ़ाइल को अनुपस्थिति से डिलीट का अनुमान नहीं लगाना चाहिए।
चरण 4: विलंबित और क्रम से बाहर के डेटा को संभालें
प्रत्येक स्रोत के लिए एक मोनोटोनिक वॉटरमार्क बनाए रखें। लागू वॉटरमार्क से नीचे की फ़ाइल को अस्वीकार या क्वारंटाइन करें। यदि ऐतिहासिक सुधार की अनुमति है, तो प्रभावित प्रभावी अंतराल को विभाजित करें और एक ट्रांज़ैक्शन में प्रभावित फ़ैक्ट जॉइन्स की पुनर्गणना करें; यदि प्रकाशित रिपोर्टें अपरिवर्तनीय हैं, तो बैच को सुधार कतार (correction queue) में डालें और इसके प्रभाव को प्रकाशित करें। आगमन समय को वैधता समय के रूप में उपयोग करने से एक पुराना स्नैपशॉट नई स्थिति में बदल जाएगा।
चरण 5: डिलीट, रीप्ले और समवर्तीता
पूर्ण स्नैपशॉट में अनुपस्थित कुंजी एक लॉजिकल-डिलीट वर्ज़न बनाती है या ऑडिट साक्ष्य बनाए रखते हुए वर्तमान पंक्ति को क्लोज़ करती है। फ़ाइल हैश या स्रोत बैच को एक विशिष्ट आइडम्पोटेंसी कुंजी बनाएं; किसी बैच को दोबारा चलाने पर दूसरा वर्ज़न नहीं जुड़ना चाहिए। नेचुरल-की पार्टीशन के लिए ट्रांज़ैक्शन या मर्ज लॉक का उपयोग करें ताकि क्लोज़ करना और इंसर्ट करना एटॉमिक हो। वॉटरमार्क क्रम के अनुसार बैच लागू करें; पुराना बैच किसी बंद अंतराल को फिर से नहीं खोलना चाहिए।
एक सत्यापन योग्य SQL स्केलेटन
-- current_dim: one current row per customer_id
BEGIN;
UPDATE dim_customer AS old
SET valid_to = :as_of,
is_current = FALSE,
source_batch = :batch_id
FROM stage_customer AS incoming
WHERE old.customer_id = incoming.customer_id
AND old.is_current = TRUE
AND old.row_hash <> incoming.row_hash;
INSERT INTO dim_customer (
customer_key, customer_id, city, valid_from, valid_to,
is_current, source_batch, row_hash
)
SELECT nextval('dim_customer_key_seq'), incoming.customer_id,
incoming.city, :as_of, NULL, TRUE, :batch_id, incoming.row_hash
FROM stage_customer AS incoming
LEFT JOIN dim_customer AS old
ON old.customer_id = incoming.customer_id
AND old.is_current = TRUE
WHERE old.customer_id IS NULL
OR old.row_hash <> incoming.row_hash;
COMMIT;प्रोडक्शन में अभी भी batch_id पर एक यूनिक कंस्ट्रेंट की आवश्यकता है और कमिट से पहले जांच की आवश्यकता है कि प्रत्येक नेचुरल की में एक वर्तमान पंक्ति हो, अंतराल ओवरलैप न हों, और इनपुट वॉटरमार्क मान्य हो। यह स्केलेटन मानता है कि stage_customer प्रति बैच डिडुप्लिकेट किया गया है; यह पूरी तरह से आउट-ऑफ-ऑर्डर हैंडलर नहीं है।
एक उच्च-गुणवत्ता वाला नमूना उत्तर
"मैं सबसे पहले पूछता हूँ कि रिपोर्ट को कौन सा इतिहास सुरक्षित रखना चाहिए। केवल वर्तमान मानों के लिए Type 1 का उपयोग करें, किसी भी पिछली तारीख की स्थिति के लिए Type 2 का, और ठीक एक पिछले मान के लिए Type 3 का उपयोग करें। दैनिक पूर्ण स्नैपशॉट के लिए, रॉ फ़ाइल को लैंड करें और बैच वॉटरमार्क तथा फ़ाइल हैश द्वारा डिडुप्लिकेट करें। इंसर्ट, परिवर्तन और नो-ऑप्स (no-ops) को वर्गीकृत करने के लिए नेचुरल कीज़ और एट्रिब्यूट हैश की तुलना करें। Type 2 अपडेट को पुरानी पंक्ति को क्लोज़ करना चाहिए और एक नया वर्ज़न एटॉमिक रूप से इंसर्ट करना चाहिए, जिसमें एक वर्तमान पंक्ति और गैर-ओवरलैपिंग अंतराल हों। अनुपस्थित कीज़ का अर्थ केवल पूर्ण-स्नैपशॉट अनुबंध के तहत डिलीट होता है; विलंबित बैचों को वॉटरमार्क द्वारा ऑर्डर या क्वारंटाइन किया जाता है, और रिपोर्ट अनुबंध यह तय करता है कि इतिहास को सुधारा जाए या नहीं।"
सामान्य गलतियाँ
- प्रत्येक डायमेंशन के लिए डिफ़ॉल्ट रूप से Type 2 चुनना → अधिक स्टोरेज और क्वेरी लागत → पहले इतिहास अनुबंध के बारे में पूछें।
- आगमन समय को प्रभावी समय के रूप में उपयोग करना → क्रम से बाहर के स्नैपशॉट इतिहास को दूषित करते हैं → व्यावसायिक-प्रभावी समय या सुधार कतार का उपयोग करें।
- इंक्रीमेंटल फ़ाइल में अनुपस्थित कुंजियों को डिलीट मानना → वैध इंक्रीमेंट्स डिलीट हो जाते हैं → केवल पूर्ण स्नैपशॉट से ही डिलीट का अनुमान लगाएं।
- सरोगेट-की वर्ज़न के बिना
is_currentको अपडेट करना → फ़ैक्ट्स पॉइंट-इन-टाइम स्थिति से नहीं जुड़ सकते → पुरानी पंक्ति को क्लोज़ करें और एक वर्ज़न जोड़ें। - बैच आइडम्पोटेंसी कुंजी को छोड़ देना → रीप्ले से डुप्लिकेट इतिहास जुड़ जाता है → फ़ाइल हैश या बैच आईडी पर विशिष्टता लागू करें।
फॉलो-अप और प्रभावी उत्तर
क्या किसी विलंबित ईवेंट को प्रकाशित रिपोर्ट को संशोधित करना चाहिए?
पुष्टि करें कि क्या रिपोर्ट संशोधन योग्य है। यदि हाँ, तो व्यावसायिक समय के आधार पर प्रभावी अंतरालों को विभाजित करें और प्रभावित फ़ैक्ट्स की पुनर्गणना करें; यदि नहीं, तो प्रकाशित परिणाम को बनाए रखें और इसके प्रभाव के साथ एक सुधार बैच जारी करें। दोनों अनुबंध रॉ बैच को बनाए रखते हैं ताकि आंकड़े चुपचाप न बदलें।
यदि एक स्नैपशॉट में एक ग्राहक दो बार दिखाई दे तो क्या होगा?
इसे इनपुट-गुणवत्ता त्रुटि मानें। केवल तभी एक पंक्ति चुनें जब कोई बिज़नेस वर्ज़न या स्रोत अनुक्रम विजेता को परिभाषित करता हो; अन्यथा क्वारंटाइन करें और अलर्ट भेजें। एक मनमाना LIMIT 1 इतिहास को गैर-पुनरुत्पादन योग्य बनाता है।
Type 3, Type 2 से बेहतर कब है?
Type 3 का उपयोग तब करें जब केवल वर्तमान बनाम पिछले की तुलना की आवश्यकता हो और पुराने वर्ज़नों को कभी भी क्वेरी न किया जाना हो। यह कम स्टोरेज और सरल क्वेरीज़ का उपयोग करता है। यदि बाद में किसी भी तारीख के इतिहास की आवश्यकता होती है, तो Type 2 पर माइग्रेट करें और बैकफ़िल लागत बताएं।
आप कैसे सत्यापित करते हैं कि अंतराल ओवरलैप नहीं होते हैं?
नेचुरल की द्वारा सॉर्ट करें और जांचें कि प्रत्येक अंतराल अगले के शुरू होने से पहले ही समाप्त हो जाए, फिर पुष्टि करें कि प्रति कुंजी अधिकतम एक वर्तमान पंक्ति हो। इसे पूर्वव्यापी नमूने (retrospective sample) के बजाय बैच रिलीज़ गेट बनाएं।