BigQuery के NOT ENFORCED keys जॉइन ऑप्टिमाइज़ेशन को कैसे प्रभावित करते हैं?

प्रश्न और दायरा

आपके पास एक BigQuery स्टार स्कीमा है: store_sales एक फैक्ट टेबल है और customer एक डाइमेंशन टेबल है। टीम चाहती है कि ऑप्टिमाइज़र uniqueness और relationship मेटाडेटा का उपयोग करके जॉइन को कम कर सके, इसलिए प्राइमरी और फ़ॉरेन key घोषणाएँ की जाएँ। इंटरव्यूअर तीन सवाल पूछता है: एक unenforced constraint क्या गारंटी देता है, कौन-से equivalent rewrites सुरक्षित हैं, और जब डेटा बदलता है तो चुपचाप होने वाली गलतियों को कैसे रोकें।

यह मान लें कि क्वेरी केवल फैक्ट कॉलम प्रोजेक्ट करती है, फैक्ट-टेबल का फ़ॉरेन key nullable है, और डाइमेंशन का प्राइमरी key unique और non-null है। Google Cloud के दस्तावेज़ बताते हैं कि BigQuery इन constraints को enforce नहीं करता; डेटा को वैध रखने की ज़िम्मेदारी मालिक की है, और उल्लंघित constraints वाली क्वेरीज़ गलत परिणाम दे सकती हैं।

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

• क्या आप ऑप्टिमाइज़र मेटाडेटा और write-time integrity checks के बीच फ़र्क करते हैं।
• क्या आप key को index कहने की बजाय uniqueness और optional matching से join elimination निकालते हैं।
• क्या आपको पता है कि NOT ENFORCED डुप्लिकेट keys या orphan foreign keys को अस्वीकार नहीं करता।
• क्या आप contract को load gates, monitoring, और rollback में बदलते हैं, न कि केवल DDL पर रुकते हैं।

एक कमज़ोर जवाब कहता है "प्राइमरी key unique है और फ़ॉरेन key उसे reference करती है।" एक मज़बूत जवाब कहता है कि ऑप्टिमाइज़र गलत घोषणा का उपयोग करके क्वेरी को rewrite कर सकता है, इसलिए गलती सिर्फ धीमे डेटा की नहीं बल्कि गलत डेटा की होती है; हर घोषणा के लिए दोहराने योग्य प्रमाण चाहिए।

उत्तर देने से पहले स्पष्टीकरण

1. क्या यह native BigQuery टेबल है या external टेबल? Constraint support और rewrite rules अलग होते हैं, इसलिए पहले दायरा तय करें।
2. क्या क्वेरी केवल left-side कॉलम प्रोजेक्ट करती है? Right-side कॉलम चुनने पर आमतौर पर join elimination संभव नहीं होती।
3. क्या फ़ॉरेन key nullable है? NULL का मतलब है कि कोई match आवश्यक नहीं है और यह equivalent rewrite में filter को बदल देता है।
4. क्या constraint को एक pipeline या cross-system replication द्वारा maintain किया जाता है? Cross-system loads में टेबल लिखने से पहले और बाद दोनों जगह जाँच ज़रूरी है।

ये उत्तर परिणाम बदल देते हैं: right-side कॉलम चुनना, डुप्लिकेट प्राइमरी keys, या non-null orphan keys elimination को असुरक्षित बना देते हैं। यदि केवल eventual consistency उपलब्ध है, तो जाँच का परिणाम release gate बनना चाहिए।

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

"BigQuery keys declarative मेटाडेटा हैं और डिफ़ॉल्ट रूप से NOT ENFORCED हैं। ये write time पर डुप्लिकेट प्राइमरी keys या orphan foreign keys को अस्वीकार नहीं करते। इनका मूल्य यह है कि ऑप्टिमाइज़र को uniqueness और relationship के तथ्य मिलते हैं; उदाहरण के लिए, जो जॉइन केवल फैक्ट कॉलम लौटाती है उसे कभी-कभी non-null filter में बदला जा सकता है। डेटा को घोषणा के अनुसार होना चाहिए, नहीं तो rewrite गलत परिणाम दे सकती है। मैं पहले projection और NULL semantics की पुष्टि करता हूँ, फिर हर load के बाद duplicate-key, orphan-key, और count-reconciliation जाँच चलाता हूँ। एक असफल जाँच constraint release को रोकती है या batch को rollback करती है।"

चरण-दर-चरण समाधान

1. key की दो भूमिकाओं को अलग करें

प्राइमरी key घोषणा का मतलब है कि प्रत्येक row unique और non-null है। फ़ॉरेन key घोषणा का मतलब है कि हर non-null value referenced प्राइमरी key में दिखनी चाहिए। BigQuery उन घोषणाओं को ऑप्टिमाइज़ेशन के लिए पढ़ सकता है, लेकिन write validation नहीं करता। NOT ENFORCED एक स्पष्ट contract है; दोष invalid syntax नहीं, invalid data है।

2. inner-join elimination निकालें

एक ऐसी क्वेरी पर विचार करें जो केवल फैक्ट कॉलम चुनती है:

SELECT ss.*
FROM store_sales AS ss
JOIN customer AS c
  ON ss.sales_customer = c.customer_name;

यदि customer.customer_name एक unique non-null प्राइमरी key है और हर non-null ss.sales_customer एक customer से मेल खाती है, तो जॉइन किसी फैक्ट row को duplicate नहीं कर सकती। ऑप्टिमाइज़र इसे इस तरह rewrite कर सकता है:

SELECT *
FROM store_sales
WHERE sales_customer IS NOT NULL;

यह rewrite दो तथ्यों पर निर्भर करती है: non-null foreign key का एक match है, और वह match अधिकतम एक row है। यह तब equivalent नहीं है जब क्वेरी को right-side कॉलम चाहिए, unmatched rows अलग करने हों, या प्राइमरी key duplicate हो।

3. outer joins और join order की सीमाएँ

Left outer join को भी हटाया जा सकता है जब right join key unique हो और केवल left-side कॉलम प्रोजेक्ट हों। Multi-join क्वेरी में, key मेटाडेटा join reordering के लिए cardinality जानकारी दे सकता है। ये metadata-आधारित निष्कर्ष हैं; BigQuery runtime पर uniqueness साबित करने के लिए right टेबल scan नहीं करता।

4. load gate में correctness रखें

हर batch के लिए कम से कम तीन जाँचें चलाएँ:

-- Duplicate or null primary keys
SELECT customer_name, COUNT(*) AS n
FROM customer
GROUP BY customer_name
HAVING customer_name IS NULL OR n > 1;

-- Orphan foreign keys
SELECT COUNT(*) AS orphan_count
FROM store_sales AS ss
LEFT JOIN customer AS c
  ON ss.sales_customer = c.customer_name
WHERE ss.sales_customer IS NOT NULL
  AND c.customer_name IS NULL;

Batch row counts, non-null foreign-key counts, और matched-key counts का मिलान करें। परिणाम को data-quality टेबल में store करें। केवल वही version जो gate पास करे, नई constraint घोषणा publish करे या downstream क्वेरीज़ को ऑप्टिमाइज़ेशन पर निर्भर करने दे।

5. घोषणा, validation, या rewrite चुनें

घोषणाएँ stable, testable dimension keys के लिए उपयुक्त हैं और ऑप्टिमाइज़र को relationship स्वतः उपयोग करने देती हैं। Application validation उन ingress paths के लिए उपयुक्त है जहाँ खराब डेटा जल्दी reject होना चाहिए। Explicit query rewrite migration अवधि के लिए उपयुक्त है जब contract पर भरोसा नहीं है, लेकिन इसमें duplicated logic की लागत है। ये सह-अस्तित्व में रह सकते हैं: pipeline में validate करें, trusted relationship declare करें, और critical reports को reconcile करें।

6. failure path डिज़ाइन करें

जाँच विफल होने पर, अंतिम trusted टेबल या view रखें, वर्तमान batch को not publishable mark करें, और data owner को alert करें। Constraint को केवल "fix" के रूप में delete न करें; इससे कारण छुप जाता है। Duplicate-key sources, replication lag, या delete ordering को trace करें, फिर replay, deduplication, या dimension repair चुनें।

उच्च-गुणवत्ता वाला नमूना उत्तर

"मैं BigQuery के प्राइमरी और फ़ॉरेन keys को transaction constraint नहीं, बल्कि optimization contract मानता हूँ। पहले मैं पुष्टि करता हूँ कि क्वेरी केवल फैक्ट कॉलम प्रोजेक्ट करती है, nullable foreign keys का अर्थ परिभाषित करता हूँ, और dimension-key uniqueness verify करता हूँ। उन परिस्थितियों में inner join एक non-null fact filter बन सकती है, कुछ left joins हट सकती हैं, और join order declared cardinality का उपयोग कर सकता है। जोखिम यह है कि BigQuery contract enforce नहीं करता: duplicate primary keys या orphan foreign keys ऑप्टिमाइज़र को गलत मेटाडेटा के आधार पर rewrite करने देते हैं, जिससे परिणाम चुपचाप बदल सकते हैं।

"रिलीज़ से पहले मैं हर batch के लिए null और duplicate primary keys, orphan foreign keys, और row-count reconciliation जाँचता हूँ। एक असफल batch टेबल या नई constraint publish नहीं कर सकता; पिछला trusted version active रहता है। Untrusted मेटाडेटा वाले migration के दौरान, मैं dependent rewrites disable करता हूँ या कई batch पास होने तक explicit join उपयोग करता हूँ। इससे ऑप्टिमाइज़ेशन का लाभ बना रहता है और correctness auditable रहती है।"

सामान्य गलतियाँ

• गलती: यह कहना कि PRIMARY KEY duplicates को अस्वीकार करता है → यह क्यों विफल होता है: BigQuery इस constraint को unenforced document करता है → सुधार: duplicate detection को load gate में रखें।
• गलती: हर उस जॉइन को हटाना जिसमें foreign key हो → यह क्यों विफल होता है: key orphaned हो सकती है और क्वेरी को right-side कॉलम चाहिए हो सकते हैं → सुधार: पहले data और projection conditions verify करें।
• गलती: ऐतिहासिक डेटा की एक बार जाँच करना → यह क्यों विफल होता है: incremental loads, backfills, और replication lag खराब keys फिर से ला सकते हैं → सुधार: batch checks लगातार चलाएँ और metrics retain करें।
• गलती: गलत परिणाम के बाद constraint delete करना → यह क्यों विफल होता है: optimization मेटाडेटा गायब हो जाता है जबकि source defect बना रहता है → सुधार: release freeze करें, कारण खोजें, और trusted version restore करें।

अनुवर्ती सवाल और जवाब

यदि आज dimension में duplicate keys आ जाएँ तो क्या होगा?

उन क्वेरीज़ को रोकें जो घोषणा पर निर्भर हैं, explicit deduplicated या trusted snapshot पर switch करें, प्रभावित batches mark करें, और reconciliation replay करें। Dimension ठीक होने और जाँच पास होने के बाद ही घोषणा restore करें।

Nullable foreign key के लिए non-null filter क्यों चाहिए?

NULL का मतलब है कि फैक्ट row का कोई customer match नहीं है। Inner join उस row को drop करती है, इसलिए equivalent rewrite में WHERE sales_customer IS NOT NULL रखना ज़रूरी है; इसे छोड़ने से परिणाम बदल जाता है।

आप कैसे साबित करते हैं कि join elimination परिणाम सुरक्षित रखती है?

मूल और rewritten क्वेरीज़ को representative partitions पर साथ-साथ चलाएँ। Row counts, primary-key sets, और aggregates की तुलना करें, और constraint-check version record करें। Rewrite को तभी promote करें जब data-quality checks और result reconciliation दोनों पास हों।

आप constraint-based optimization कब टालेंगे?

तब टालें जब constraints slow या unauditable replication से आ रहे हों, backfills बार-बार हों, या कोई batch gate न हो। एक extra scan उस सस्ती क्वेरी से बेहतर है जो गलत मेटाडेटा पर भरोसा करती है।

क्या BigQuery constraints cross-table transaction की जगह ले सकते हैं?

नहीं। ये write consistency enforce नहीं करते और टेबलों के पार atomic commit नहीं देते। Transaction semantics upstream system या load orchestrator में होनी चाहिए, और अंतिम validation परिणाम warehouse में लाया जाना चाहिए।

संदर्भ

• Google Cloud Documentation: BigQuery primary and foreign keys।
• Google Cloud Blog: Join Optimizations with BigQuery Primary and Foreign Keys।