تست انبار داده و ETL؛ راهنمای عملی با SQL

داشبورد فروش دیروز ۱۲٪ رشد نشان می‌دهد؛ چند ساعت بعد تیم مالی می‌فهمد یک Batch پس از Timeout دوباره اجرا شده و بخشی از پرداخت‌ها دو بار وارد Fact Table شده‌اند. Job سبز بوده، تعداد ردیف‌های مقصد هم بیشتر از صفر است و گزارش بدون خطای فنی باز می‌شود—اما تصمیم کسب‌وکار بر داده نادرست بنا شده است.

تست انبار داده فقط مقایسه تعداد ردیف Source و Target نیست. باید ثابت کند داده درست، با معنای درست، برای بازه درست، دقیقاً به Grain درست رسیده؛ تاریخچه و روابط را خراب نکرده؛ اجرای مجدد نتیجه را دوبرابر نمی‌کند؛ و خروجی داشبورد با قرارداد کسب‌وکار سازگار است. در این راهنما این زنجیره را از Extract تا ETL/ELT، مدل بُعدی، Reconciliation، CI و مانیتورینگ عملی می‌کنیم.

خلاصه عملی: ابتدا سؤال تحلیلی و Grain را تعریف کنید؛ سپس برای هر لایه یک Data Contract، Oracle و Failure Policy بنویسید. Schema، محتوا، رابطه، تاریخچه، Freshness و عملیات را جدا بیازمایید. هر Run باید Source Cut، نسخه کد و Mapping، بازه داده، شمار ورودی/خروجی/ردشده و Artifact آزمون داشته باشد.

تست انبار داده چیست و چه چیزی را پوشش می‌دهد؟

انبار داده سامانه‌ای تحلیلی است که داده جاری و تاریخی چند منبع را برای گزارش، تحلیل و تصمیم‌گیری یکپارچه می‌کند. برخلاف OLTP که پاسخ صحیح یک تراکنش واحد مهم است، در Data Warehouse علاوه بر مقدار هر ردیف، معنای تجمع، بُعد زمان، Grain، نسخه قانون و قابلیت بازتولید یک Snapshot اهمیت دارد.

آزمون انبار داده مجموعه شواهدی است که از Source Contract تا جدول‌های Staging، تبدیل‌ها، Dimension و Fact، Aggregate، Semantic Layer و Dashboard دنبال می‌شود. این صفحه بر خط لوله تحلیلی تمرکز دارد؛ برای Constraint، Transaction، Stored Procedure، Index و Migration در یک پایگاه داده، راهنمای تست پایگاه داده با SQL را بخوانید.

ETL، ELT، Batch، Streaming و CDC

ETL یعنی Extract، سپس Transform و بعد Load؛ در ELT داده خام ابتدا در مقصد بار می‌شود و تبدیل داخل پلتفرم تحلیلی انجام می‌گیرد. Batch یک مجموعه محدود را در یک بازه پردازش می‌کند؛ Streaming جریان پیوسته است؛ و Change Data Capture یا CDC تغییرهای Insert، Update و Delete منبع را منتقل می‌کند. نام معماری، نیاز آزمون را حذف نمی‌کند؛ فقط محل Oracle و مرز Failure را تغییر می‌دهد.

الگو	ریسک برجسته	آزمون ضروری
ETL	تبدیل پیش از مقصد و Staging میانی	ورودی/خروجی هر مرحله، قرنطینه، بازیابی و انتشار اتمیک
ELT	Schema Drift و دسترسی به Raw Data در مقصد	Contract منبع، Isolation لایه‌ها، Unit Test مدل SQL و Data Test خروجی
Batch	مرز بازه، Retry و Backfill	بازه نیمه‌باز، Idempotency، اجرای مجدد و Partition reconciliation
Streaming	ترتیب، تکرار، دیررس و Window	Event Time، Watermark، Deduplication و State Recovery
CDC	Snapshot اولیه، Delete و Duplicate پس از Failure	Offset، Tombstone/Delete، Update ordering، Resume و مصرف‌کننده Idempotent

موفقیت Job با درستی داده برابر نیست

Exit Code صفر فقط می‌گوید فرایند طبق تعریف فنی تمام شده است. ممکن است Source ناقص بوده، یک فیلتر اشتباه همه سفارش‌های لغوشده را حذف کرده، نرخ تبدیل ریال/تومان دو بار اعمال شده یا Dashboard هنوز Partition قبلی را بخواند. «Pipeline ran»، «Data loaded»، «Data passed tests» و «Business output accepted» چهار وضعیت جدا هستند.

از سؤال کسب‌وکار و Grain شروع کنید، نه از ابزار ETL

پیش از نوشتن Test Case بپرسید هر ردیف چه چیزی را نمایندگی می‌کند. Grain جدول Fact باید در یک جمله بدون ابهام نوشته شود؛ مثلاً «یک ردیف به‌ازای هر تلاش پرداخت روی هر سفارش» با «یک ردیف به‌ازای پرداخت موفق نهایی هر سفارش» یکسان نیست. اگر Grain مشخص نباشد، Unique Key، شمار مورد انتظار، Join و Aggregate هم Oracle معتبر ندارند.

Data Contract حداقلی

جزء	پرسش قرارداد	نمونه Checkout
مالک و مصرف‌کننده	چه کسی معنا را تعیین و چه کسی استفاده می‌کند؟	پرداخت، مالی، BI و عملیات
Grain	هر ردیف دقیقاً چیست؟	یک Attempt پرداخت با شناسه درگاه
Business/Natural Key	هویت پایدار ردیف چیست؟	PSP + payment_reference
Schema	نام، نوع، Nullability و دامنه چیست؟	amount_rial عدد صحیح غیرمنفی
Semantics	مقدار چه معنایی دارد و واحد چیست؟	مبلغ نهایی به ریال، نه تومان
زمان	Event، Effective، Load و Report Time کدام‌اند؟	event_at_utc و business_date_tehran
SLA/SLO	تا چه زمانی و با چه تأخیری باید حاضر باشد؟	تسویه روزانه تا ساعت قراردادی
تغییر	Backward compatibility و Version چگونه است؟	حذف ستون ممنوع؛ Mapping نسخه‌دار
Failure Policy	Reject، Quarantine، Retry یا Stop؟	واحد پول ناشناخته قرنطینه و انتشار متوقف
حریم خصوصی	طبقه‌بندی، Masking و Retention چیست؟	توکن پرداخت مجاز؛ PAN و داده حساس ممنوع

قرارداد فقط Schema نیست. مستندات رسمی dbt Model Contracts نیز Shape شامل نام و نوع ستون را از Data Test محتوایی جدا می‌کند و یادآور می‌شود بعضی Constraintها در برخی Warehouseها صرفاً تعریف می‌شوند و Enforcement ندارند. بنابراین وجود Primary Key در Metadata را بدون آزمون Duplicate، تضمین اجرایی فرض نکنید.

نسخه هر Run را قابل شناسایی کنید

برای بازتولید یک گزارش، این Envelope را کنار نتیجه نگه دارید:

run_id، زمان شروع/پایان و وضعیت نهایی؛
Commit یا نسخه کد، نسخه Mapping و Schema؛
شناسه Snapshot، Offset، Watermark یا Source Cut؛
بازه پردازش با Timezone و قرارداد شمول ابتدا/انتها؛
Environment، پارامتر، Feature Flag و نسخه وابستگی؛
شمار Read، Accepted، Inserted، Updated، Deleted، Rejected و Quarantined؛
نسخه Dataset خروجی و نتایج آزمون‌های مرتبط.

نقشه شش‌لایه آزمون Data Warehouse

به‌جای یک تست End-to-End سنگین، Oracleها را در نزدیک‌ترین لایه به خطا قرار دهید. این رویکرد با تست یکپارچه‌سازی هم‌راستاست: قرارداد مرزها را جدا بسنجید و چند جریان کلیدی را سرتاسری نگه دارید.

۱. منبع و قرارداد ورودی

Schema، نوع، Nullability، Encoding و واحد بدون اطلاع تغییر نکرده‌اند؟
Business Key واقعاً پایدار و یکتا است؟
Source Cut کامل شده یا هنوز Transaction باز دارد؟
فایل ورودی نام، Manifest، تعداد ردیف، حجم و Checksum مورد انتظار دارد؟
Delete و Correction چگونه نمایش داده می‌شوند؟

۲. Extract و Landing/Raw

بازه زمانی یا Key Range هیچ Gap یا Overlap ندارد؟
همه ستون‌های لازم بدون Truncation و تغییر Encoding رسیده‌اند؟
تعداد، Distinct Key، Null Profile و Control Total با Snapshot منبع می‌خواند؟
Retry همان فایل یا Offset را دو بار وارد نمی‌کند؟
Raw Data تغییرناپذیر، نسخه‌دار و قابل ردیابی است؟

۳. Transform

هر Rule کسب‌وکار مثال مثبت، منفی، مرزی و Null دارد؟
Join موجب Fan-out یا حذف بی‌سروصدا نشده است؟
تبدیل نوع، گردکردن، Timezone و Currency دقیقاً طبق قرارداد است؟
Default و «نامشخص» با Missing واقعی اشتباه نشده‌اند؟
رکورد ردشده دلیل، منبع، Run و امکان Reprocess دارد؟

۴. Dimension و Fact

Grain، Key یکتا، Foreign Key و Unknown Member معتبرند؟
Fact به نسخه درست Dimension در Event Time متصل است؟
SCD تاریخچه را طبق Type انتخاب‌شده حفظ یا بازنویسی می‌کند؟
Late-arriving Fact/Dimension پس از Backfill نتیجه درست می‌دهد؟
Aggregate از Fact اتمی قابل Reconcile است؟

۵. Semantic Layer و گزارش

تعریف Metric، فیلتر، Denominator و Calendar نسخه‌دار است؟
Drill-down و Drill-through با جمع سطح بالاتر سازگارند؟
Row-level Security و Masking در Query مصرف‌کننده عمل می‌کنند؟
Cache یا Extract ابزار BI پس از Publish تازه شده است؟
عدد Dashboard با Query مرجع و نمونه مورد توافق کسب‌وکار می‌خواند؟

۶. Orchestration و عملیات

Dependency شکست‌خورده Downstream را متوقف می‌کند؟
Partial Output پیش از موفقیت کامل قابل مشاهده نیست؟
Retry، Resume، Backfill، Cancel و Concurrent Run امن‌اند؟
Alert شامل Dataset، Partition، Run، مالک و Runbook است؟
پس از اصلاح، داده معیوب Reprocess و مصرف‌کننده مطلع می‌شود؟

Oracleهای تست داده؛ «مقدار درست» را چگونه بدانیم؟

سخت‌ترین بخش آزمون ETL اجرای Query نیست؛ دانستن نتیجه مورد انتظار است. یک Oracle واحد برای همه خطاها وجود ندارد. از چند خانواده شاهد استفاده کنید.

Oracle	چه چیزی را می‌سنجد؟	محدودیت
Contract/Schema	نام، نوع، Nullability و Domain	درستی معنای مقدار را ثابت نمی‌کند
Invariant	قاعده‌ای که باید همیشه برقرار باشد	Invariant ناقص می‌تواند خطای هم‌سو را رد نکند
Reconciliation	حفظ شمار، مجموع یا مجموعه بین دو مرز	دو طرف می‌توانند یک خطای مشترک داشته باشند
Golden Dataset	ورودی کوچک با خروجی دستیِ تأییدشده	همه تنوع Production را پوشش نمی‌دهد
Differential	مقایسه نسخه قدیم/جدید یا دو پیاده‌سازی	قدیمی لزوماً درست نیست؛ تفاوت عمدی باید مشخص باشد
Metamorphic	رابطه خروجی پس از تغییر کنترل‌شده ورودی	نیازمند رابطه واقعاً معتبر کسب‌وکار است
Expert/UAT	معنا و تناسب گزارش برای تصمیم	جای آزمون سیستماتیک تمام ردیف‌ها را نمی‌گیرد

Validity، Accuracy و Consistency را یکی نگیرید

کد ملی ۱۰رقمی ممکن است از نظر قالب Valid باشد ولی متعلق به فرد دیگری باشد و Accurate نباشد. دو جدول ممکن است مقدار اشتباه یکسانی داشته باشند و Consistent باشند. Completeness نیز همیشه ۱۰۰٪ نیست؛ باید نسبت به جمعیت و بازه مورد انتظار تعریف شود. کیفیت داده فقط وقتی قابل آزمون است که Dimension، فرمول، Scope، Threshold و Action داشته باشد.

Data Profiling برای کشف Baseline مفید است؛ مستندات رسمی SQL Server Data Profiling نمونه‌هایی مانند Null، Distinct، Length، Pattern و رابطه کلید را پوشش می‌دهد. اما Profile مشاهده گذشته است، نه قرارداد آینده. اگر دیروز ۲٪ مقدار Null بوده، این عدد به‌تنهایی مجوز ۲٪ Null فردا نیست.

تست Extract؛ شمار ردیف لازم است اما کافی نیست

Source و Landing ممکن است تعداد برابر داشته باشند، اما ستون مبلغ Truncate شده، دو Business Key جابه‌جا شده یا داده از Snapshotهای زمانی متفاوت آمده باشد. Reconciliation را لایه‌ای طراحی کنید:

Identity: منبع، Snapshot/Offset، Partition و بازه یکسان؛
Shape: Schema، نوع، Encoding، تعداد ستون و فایل؛
Population: Count کل و Count به تفکیک روز/وضعیت/منبع؛
Keys: Distinct، Duplicate، Min/Max و Gapهای مورد انتظار؛
Control totals: Sum مبلغ، Count تراکنش و جمع‌های قراردادی؛
Content sample: نمونه مرزی و طبقه‌بندی‌شده با Trace تا Source؛

Checksum را با احتیاط استفاده کنید

Hash فقط وقتی قابل مقایسه است که ترتیب ردیف، Serialization، Null، Encoding، قالب تاریخ، Precision و ترتیب ستون‌ها Canonical شده باشند. یک Hash کل Dataset محل اختلاف را نشان نمی‌دهد و برخورد Hash هرچند کم‌احتمال، «اثبات ریاضی برابری» نیست. Hash پارتیشن‌شده و همراه با Count و Control Total برای Triage مفیدتر است.

مرز بازه را صریح کنید

برای Batch روزانه از بازه نیمه‌باز مانند [start, end) استفاده کنید تا رکورد دقیقاً روی نیمه‌شب در دو Run نیفتد. Timezone را کنار بازه ذخیره کنید. created_at همیشه Watermark مناسبی نیست؛ Update و Delete ممکن است دیده نشوند. تست کنید رکورد در ابتدا، درست پیش از انتها، دقیقاً روی انتها و دیرتر از Watermark چه سرنوشتی دارد.

تست Transform؛ قانون را به مثال اجرایی تبدیل کنید

هر Mapping باید Source Field، Target Field، شرط، تبدیل، Null Policy، Error Policy، Version و مثال داشته باشد. متن مبهم «مبلغ را استاندارد کن» Testable نیست؛ «اگر currency_unit='TOMAN' بود amount را دقیقاً یک بار در ۱۰ ضرب و در amount_rial ذخیره کن؛ واحد ناشناخته را Reject کن» قابل آزمون است.

چهار Data Set کوچک بسازید

Happy path: نمونه معمول هر Branch قانون؛
Boundary: صفر، حداقل/حداکثر، نیمه‌شب، پایان ماه و Precision؛
Adversarial: Duplicate، Null، نوع غلط، ترتیب نامعمول و Encoding ناسازگار؛
Change set: Insert، Update، Delete، Correction و Late arrival.

Unit Testهای dbt ورودی‌های ایزوله و خروجی مورد انتظار مدل SQL را از Data Test روی Dataset ساخته‌شده جدا می‌کنند. این تفکیک مهم است: Unit Test شاخه‌های منطق را سریع می‌سنجد؛ Data Test فرض‌های جاری درباره داده واقعی را پس از Build کنترل می‌کند.

Join Fan-out را آشکار کنید

اگر یک Fact با کلید غیرمنحصربه‌فرد به Dimension وصل شود، مبلغ‌ها چند برابر می‌شوند. پیش و پس از Join این موارد را مقایسه کنید: Count Fact، Distinct Fact Key، Sum Measure و تعداد Matchهای صفر/یک/چند. «Query اجرا شد» یا حتی «هیچ Foreign Key گمشده نیست» Fan-out را رد نمی‌کند.

تست Load؛ اتمیک، Idempotent و قابل بازیابی

Idempotency یعنی اجرای دوباره همان Run با همان ورودی و نسخه منطق، وضعیت نهایی یکسان بسازد. این ویژگی برای Retry و Backfill حیاتی است. راهنمای رسمی Airflow Best Practices نیز بر نتیجه یکسان در Re-run، Partition مشخص و پرهیز از Insert تکراری تأکید می‌کند.

سناریوهای اجباری Failure Injection

شکست پس از نوشتن ۴۰٪ Partition؛
Timeout پس از Commit مقصد ولی پیش از ثبت Success در Orchestrator؛
اجرای هم‌زمان دو Run برای یک بازه؛
قطع اتصال Source یا Warehouse در میانه Read/Write؛
پرشدن Storage یا Quota؛
رکورد Poison که فقط یک Partition را می‌شکند؛
Backfill قدیمی هم‌زمان با Batch روز جاری؛

برای هر Failure مشخص کنید خروجی موقت کجاست، چه چیزی Visible می‌شود، Lock یا Run Fence چگونه عمل می‌کند، Retry از کجا ادامه می‌دهد و Artifact چه چیزی را ثابت می‌کند. «Rollback دارد» بدون آزمایش Crash Boundary ادعا است.

Publish را از Build جدا کنید

الگوی امن معمولاً خروجی را در Table/Partition موقت می‌سازد، آزمون‌ها و Reconciliation را اجرا می‌کند و فقط پس از موفقیت، Pointer/View/Partition را اتمیک منتشر می‌کند. اگر پلتفرم Atomic Swap ندارد، قرارداد Visibility و رفتار مصرف‌کننده هنگام Partial Load باید روشن باشد.

تست Incremental Load و CDC

در بارگذاری افزایشی، خطا معمولاً در «چه چیزی تغییر کرده» است نه خود Transform. ماتریس رویداد حداقلی شامل Insert جدید، چند Update پشت‌سرهم، Delete، Update بدون تغییر مؤثر، Event تکراری، Event خارج از ترتیب، Late event، Schema change و Resume پس از Crash است.

مستندات رسمی Debezium درباره تحویل Exactly-once توضیح می‌دهد حالت پایه At-least-once می‌تواند رکورد تکراری تحویل دهد و Exactly-once به پیکربندی و پشتیبانی زیرساخت وابسته است. حتی با تضمین Transport، منطق Sink، Side Effectهای بیرونی و Backfill باید جدا Idempotent آزمایش شوند.

Oracleهای CDC

هر Event یک شناسه/Offset پایدار و Source metadata دارد؛
ترتیب لازم برای یک Key حفظ یا با Version مقایسه می‌شود؛
Event تکراری وضعیت مقصد را تغییر دوم نمی‌دهد؛
Delete به حذف، Tombstone یا Soft-delete قراردادی منجر می‌شود؛
Snapshot اولیه و Stream مرز بدون Gap/Double-count دارند؛
Resume از Offset قدیمی و از‌دست‌رفته رفتار تعریف‌شده دارد؛
Backfill و Live Stream یک Record را ناسازگار نمی‌نویسند؛

تست مدل بُعدی؛ Fact، Dimension و SCD

فهرست تکنیک‌های مدل‌سازی بُعدی Kimball Typeهای مختلف Slowly Changing Dimension را از Type ۰ تا الگوهای ترکیبی تفکیک می‌کند. در تست، نام Type مهم‌تر از قرارداد تاریخی نیست: کسب‌وکار باید بگوید گزارش «as-was» می‌خواهد یا «as-is».

آزمون Fact Table

یک ردیف دقیقاً با Grain اعلام‌شده برابر است؛
Business Key یا Degenerate Dimension طبق قرارداد یکتا است؛
Measure Additive، Semi-additive یا Non-additive درست Aggregation می‌شود؛
همه Foreign Keyها به Dimension معتبر یا Unknown Member قراردادی وصل‌اند؛
Event Date، Load Date و Effective Date با هم اشتباه نشده‌اند؛
Correction و Reversal حسابداری به‌جای حذف تاریخچه مدل درست دارند؛

آزمون SCD Type ۱

Type ۱ مقدار قبلی را بازنویسی می‌کند. آزمایش کنید Correction فقط Attributeهای Type ۱ را تغییر دهد، همه نسخه‌های مرتبط طبق طراحی هم‌سو شوند، Aggregate یا Cache وابسته بازسازی شود و Fact Key نشکند. سپس صریحاً نشان دهید Query تاریخی مقدار جدید را می‌بیند؛ این پیامد باید مورد پذیرش کسب‌وکار باشد.

آزمون SCD Type ۲

برای هر Natural Key دقیقاً یک نسخه Current وجود دارد؛
بازه‌های Effective هم‌پوشانی ندارند و قرارداد Gap روشن است؛
نسخه قبلی در زمان درست Expire و نسخه جدید با Surrogate Key تازه Insert می‌شود؛
تغییر Attribute غیرتاریخی نسخه اضافی نمی‌سازد؛
Event دقیقاً روی مرز به نسخه قراردادی متصل می‌شود؛
Correction گذشته، Factهای متأثر را طبق سیاست Re-key/Reprocess می‌کند؛

Late-arriving Dimension و Fact

اگر Fact پیش از Dimension برسد، انتخاب‌ها شامل توقف، قرنطینه یا ساخت Inferred/Unknown Member است. بعد از رسیدن Dimension، تست کنید Placeholder تکمیل شود، Duplicate Dimension نسازد و Fact به کلید درست متصل بماند. اگر Fact دیررس متعلق به نسخه تاریخی است، Join به Current Row خطاست؛ Effective Time باید Oracle باشد.

مثال SQL؛ مجموعه تست برای Fact پرداخت

الگوی رایج Data Test این است که Query ردیف‌های ناقض Assertion را برگرداند؛ صفر ردیف یعنی در Scope اجراشده نقضی پیدا نشده است. مستندات Data Tests در dbt نیز تست‌های آماده unique، not_null، accepted_values و relationships را از تست‌های سفارشی SQL جدا می‌کند.

نمونه زیر چهار Invariant را روی fct_payment یکجا گزارش می‌کند. نام جدول و Dialect را با Warehouse خود تطبیق دهید:

WITH failed_checks AS (
  SELECT
    'duplicate_payment_id' AS test_name,
    CAST(payment_id AS TEXT) AS row_key
  FROM fct_payment
  GROUP BY payment_id
  HAVING COUNT(*) != 1

  UNION ALL

  SELECT
    'orphan_customer_key' AS test_name,
    CAST(f.payment_id AS TEXT) AS row_key
  FROM fct_payment AS f
  LEFT JOIN dim_customer AS d
    ON f.customer_key = d.customer_key
  WHERE d.customer_key IS NULL

  UNION ALL

  SELECT
    'invalid_amount_equation' AS test_name,
    CAST(payment_id AS TEXT) AS row_key
  FROM fct_payment
  WHERE net_amount_rial != gross_amount_rial - discount_amount_rial
     OR gross_amount_rial < 0
     OR discount_amount_rial < 0

  UNION ALL

  SELECT
    'load_before_event' AS test_name,
    CAST(payment_id AS TEXT) AS row_key
  FROM fct_payment
  WHERE loaded_at_utc < event_at_utc
)
SELECT test_name, row_key
FROM failed_checks
ORDER BY test_name, row_key;

این Query به‌تنهایی کافی نیست. معادله مبلغ باید با Product/Finance تأیید شود؛ Refund ممکن است مدل جدا داشته باشد؛ Unknown Customer شاید طبق قرارداد مجاز باشد؛ و Clock Skew می‌تواند برای بعضی منابع نیاز به Tolerance داشته باشد. تست خوب «قاعده کسب‌وکار نسخه‌دار» را اجرا می‌کند، نه حدس تستر را.

Reconciliation را در سطح Partition انجام دهید

برای هر business_date و PSP، منبع و مقصد را با Count، Distinct Reference، Sum مبلغ و Count وضعیت مقایسه کنید. اختلاف را به فهرست Business Key فروبکاهید؛ فقط «مجموع‌ها برابرند» کافی نیست، چون یک رکورد کم و رکورد دیگری زیاد می‌تواند جمع یکسان بسازد.

مثال ایرانی؛ فروشگاه، PSP و تسویه مالی

فرض کنید Checkout مبلغ را به تومان می‌گیرد، سرویس پرداخت مبلغ ریالی به PSP می‌فرستد، Callback نتیجه را ثبت می‌کند و فایل تسویه روز بعد می‌رسد. Warehouse سه Source دارد: سفارش، رویداد پرداخت و Settlement File. ریسک‌ها فقط فنی نیستند؛ تفاوت تقویم، Timezone، رقم و معنای وضعیت مستقیماً عدد مالی را تغییر می‌دهد.

قرارداد پیشنهادی

واحد Canonical همه Measureهای Fact، ریال است؛ مقدار Source و واحد خام جدا حفظ می‌شوند.
Grain جدول Attempt با Grain جدول Payment Success و Settlement یکی نیست.
Business Key تسویه ترکیبی از PSP، مرجع، تاریخ/Batch قراردادی است.
زمان رویداد UTC و Business Date تهران دو ستون جدا هستند.
اعداد فارسی/عربی در ورودی متنی پیش از تطبیق Canonical می‌شوند، ولی مقدار خام برای Trace باقی می‌ماند.
وضعیت‌های «موفق»، «ناموفق»، «مبهم»، «برگشت‌خورده» و «تسویه‌شده» Mapping نسخه‌دار دارند.

سناریوهای پرریسک

Callback موفق دوبار و با فاصله زمانی می‌رسد؛ Fact تکراری ساخته نشود.
کاربر تومان وارد می‌کند؛ تبدیل دقیقاً یک بار و بدون Overflow/Rounding نامجاز انجام شود.
PSP موفق است اما Callback قطع می‌شود؛ Reconciliation بعدی وضعیت مبهم را حل کند.
Refund پس از بسته‌شدن روز مالی می‌رسد؛ گزارش تاریخی طبق سیاست Correction به‌روزرسانی شود.
فایل تسویه یک ردیف کمتر ولی Control Total برابرِ اشتباه دارد؛ Key-level diff اختلاف را نشان دهد.
رویداد نزدیک نیمه‌شب تهران در روز کسب‌وکار درست قرار گیرد.
«ی/ی» یا «ک/ک»، نیم‌فاصله و رقم فارسی باعث شکست Join مشتری/پذیرنده نشوند.
تاریخ شمسی نمایشی از تاریخ Canonical مشتق شود؛ رشته شمسی Oracle محاسبات زمانی نباشد.

برای داده آزمون این جریان، از یک Strategy طبقه‌بندی‌شده استفاده کنید: Golden Dataset مصنوعی برای Ruleها، داده تولیدی Profileشده بدون مقدار حساس برای توزیع، و در صورت ضرورت یک Subset کنترل‌شده با مجوز و Masking. تفاوت این گزینه‌ها در مقایسه داده تست واقعی و مصنوعی آمده است.

Freshness، Completeness و Timeliness را قابل اقدام کنید

Freshness پاسخ می‌دهد آخرین داده قابل اتکا چقدر قدیمی است؛ Pipeline Duration زمان اجرای Job است؛ Event-to-Availability Latency فاصله رویداد تا قابل‌مصرف‌شدن آن؛ و Completeness می‌گوید جمعیت مورد انتظار تا Cutoff رسیده یا نه. Job سریع می‌تواند داده کهنه را سریع پردازش کند.

در تنظیمات Freshness در dbt آستانه Warn و Error با فیلد یا Query زمان بارگذاری تعریف می‌شود. Threshold را از SLA منبع و نیاز مصرف‌کننده بگیرید، نه یک عدد ثابت برای همه جدول‌ها.

Metric	فرمول/تعریف	Action نمونه
Source freshness	Now منهای آخرین Event/Load معتبر	هشدار به مالک Source یا توقف Downstream
Arrival completeness	کلیدهای رسیده تقسیم بر جمعیت مورد انتظار تا Cutoff	صبر، Quarantine یا انتشار با برچسب ناقص
Reject rate	Rejected تقسیم بر Read با Denominator ثابت	Triage بر اساس Rule و منبع
Reconciliation delta	Target control total منهای Source control total	Block publish و تولید Key diff
Late-arrival rate	رکورد پس از Watermark تقسیم بر کل	تنظیم Watermark یا اصلاح Source
Test failure age	زمان از نخستین Failure حل‌نشده	Escalation طبق SLO

Data Lineage و Observability؛ شاهد مسیر، نه اثبات صحت

Lineage کمک می‌کند بدانیم کدام Job از چه Dataset ورودی چه خروجی ساخته و تغییر یک ستون چه مصرف‌کنندگانی را متأثر می‌کند. مشخصات OpenLineage Facets Context را برای Run، Job، Input و Output مدل می‌کند و می‌تواند Schema، کیفیت، آمار و خطا را به رویداد Lineage متصل کند.

اما وجود فلش Source→Target ثابت نمی‌کند Mapping درست است. Observability نیز Anomaly را نشان می‌دهد، نه الزاماً Defect را؛ افزایش واقعی فروش می‌تواند Spike معتبر باشد و Load ناقص می‌تواند داخل محدوده تاریخی بماند. Contract Tests و Invariantهای قطعی را کنار Profile و Anomaly Detection نگه دارید.

Artifact یک رخداد داده

Dataset و ستون متأثر، Partition و Business Date؛
نخستین Run خراب و آخرین Run سالم؛
Upstream/Downstream Lineage و Dashboardهای متأثر؛
Rule، انتظار، مشاهده، Failure rows و Control totals؛
دامنه زمانی/کاربری، تصمیم انتشار و مالک ریسک؛
اصلاح، Backfill Plan، Validation و اطلاع‌رسانی مصرف‌کننده؛

محیط و داده آزمون انبار داده

کپی کامل Production معمولاً هم پرهزینه است و هم ریسک حریم خصوصی دارد. از مدیریت داده تست برای تعریف Provision، Isolation، Reset، Retention و Delete Evidence استفاده کنید.

ترکیب پیشنهادی داده

Fixture کوچک: برای Unit Test قانون تبدیل؛ سریع و Deterministic.
Golden Dataset: چند جریان End-to-End با خروجی تأییدشده کسب‌وکار.
Synthetic scale: حجم، Skew، Cardinality و Partitionهای بزرگ بدون داده شخصی.
Masked/subset: فقط وقتی Fidelity لازم با مصنوعی حاصل نمی‌شود و Gate حریم خصوصی پاس شده است.
Production profile: Count/Null/Distinct/Distribution بدون استخراج مقدار حساس برای کالیبراسیون.

شباهت محیط را هدف‌محور بسنجید

برای تست منطق، Engine و Dialect سازگار مهم‌اند؛ برای Performance، حجم، Partition، Cluster، Concurrency و Resource Class باید نماینده باشند؛ برای Recovery، Orchestrator و Storage semantics اهمیت دارند. یک Environment نمی‌تواند هم‌زمان ارزان، کاملاً هم‌اندازه Production و کاملاً ایزوله باشد؛ تفاوت‌ها و اثرشان را ثبت کنید.

استراتژی اتوماسیون و Quality Gate

همه Queryها را در یک Job شبانه نریزید. بازخورد را بر اساس هزینه و ریسک لایه‌بندی کنید و با تست مداوم در CI/CD هماهنگ سازید.

مرحله	آزمون	Gate
Pull Request	Lint SQL، Contract diff، Unit Test و Fixture کوچک	Breaking Schema و منطق غلط متوقف
Build مدل	not_null، unique، accepted_values، relationships و Custom invariants	Violation قطعی متوقف؛ Threshold قراردادی Warn/Error
پیش از Publish	Reconciliation، SCD، Row/Key/Amount و Freshness	Partition ناسازگار منتشر نشود
پس از Publish	Smoke Query، Semantic Metric، Cache refresh و Permission	نسخه مصرف‌پذیر تأیید یا Rollback
دوره‌ای	Backfill rehearsal، Recovery، Full/stratified scan و UAT	ریسک تجمعی و Drift بازبینی
Production	Freshness، volume، distribution، lineage و feedback	Alert، quarantine، incident یا consumer notice

Severity را به اثر Dataset وصل کنید

Block: Contract شکسته، Reconciliation مالی نابرابر، Duplicate در Grain یا داده حساس افشاشده؛
Warn: Drift در محدوده توافق‌شده یا تأخیر پیش از SLA سخت؛
Observe: Anomaly بدون نقض قرارداد که نیاز به Triage دارد؛
Accepted exception: Scope، دلیل، مالک، Ticket و تاریخ انقضا دارد.

Threshold عمومی مانند «تا ۵٪ Null مجاز» خطرناک است. Null در کد تخفیف شاید طبیعی و در شناسه پرداخت شاید مسدودکننده باشد. Gate را در سطح ستون، Segment، زمان و مصرف‌کننده تعریف کنید.

تست عملکرد انبار داده؛ عدد بدون Workload معنایی ندارد

Performance شامل مدت Extract/Load، Throughput، Query latency، Concurrency، Queue time، Spill، Partition pruning، Cost و Recovery time است. Dataset کوچک منطق را می‌سنجد ولی گلوگاه Skew یا Shuffle را نشان نمی‌دهد. Dataset بزرگ هم اگر Distribution واقعی نداشته باشد، شاهد معتبری نیست.

Workload نماینده بسازید

Batch عادی، اوج پایان ماه و Backfill تاریخی؛
Query تعاملی کوتاه، گزارش سنگین و Refresh هم‌زمان BI؛
Skew روی مشتری/پذیرنده پرتراکنش؛
Cold/Warm cache طبق الگوی واقعی؛
رقابت ETL و مصرف تحلیلی برای Resource؛
SLO صدکی مانند p95 همراه با Error و Cost guardrail.

جزئیات طراحی Load، Stress، Spike، Soak و Scalability در راهنمای تست عملکرد آمده است. اینجا Performance Gate باید به Freshness و زمان Publish متصل باشد: Batch سریع اما نادرست یا Batch درست پس از Deadline هر دو ممکن است برای مصرف‌کننده نامناسب باشند.

نمونه‌گیری و اولویت‌بندی ریسک

حجم بالا به معنی تست تصادفی چند ردیف نیست. Invariantهای ارزان مانند Null، Key و Domain را روی همه داده اجرا کنید؛ Reconciliation را در سطح همه Partitionهای حیاتی نگه دارید؛ و بررسی گران را با نمونه طبقه‌بندی‌شده، مرزی و ریسک‌محور انجام دهید.

چه چیزهایی را Oversample کنیم؟

مبالغ بسیار بزرگ/کوچک، Refund و وضعیت مبهم؛
ابتدا/انتهای روز، ماه، سال و تغییر تقویم؛
Sourceهای کم‌حجم اما مالی یا قراردادی؛
Null، Duplicate، Outlier و Keyهای چندمنبعی؛
Late arrival، Backfill، Retry و Partition اصلاح‌شده؛
کاربران/سگمنت‌های حساس و گزارش‌های تصمیم‌ساز.

برای وزن‌دادن Probability، Impact، Detectability و Exposure از تست مبتنی بر ریسک استفاده کنید. «تست کامل همه مقادیر» معمولاً ممکن نیست، اما پوشش Oracleهای قطعی و فرایندهای حیاتی قابل دفاع است.

قالب Test Plan برای آزمون ETL

هدف تصمیم: چه گزارش/فرایندی باید قابل اعتماد شود؟
Scope: Sourceها، Datasetها، Partitionها، مدل‌ها و Dashboardها
Out of scope: موارد خارج همراه با دلیل و ریسک
Grain و Business Key: تعریف یک‌جمله‌ای و مثال
Source cut: Snapshot/Offset/Watermark، بازه و Timezone
Contracts: Schema، semantics، unit، null، freshness، privacy
Transformation rules: شناسه نسخه، مثال مثبت/مرزی/منفی
Oracles: invariant، reconciliation، golden، differential، UAT
Incremental cases: insert/update/delete/duplicate/late/retry/backfill
Dimensional cases: fact grain، keys، SCD، inferred member
Failure cases: partial write، timeout-after-commit، concurrent run
Performance workload: volume، skew، concurrency، SLO و cost
Environment/data: نسخه‌ها، fidelity، masking، reset و retention
Entry/exit criteria: gateها، thresholdها و استثناهای منقضی
Evidence: run identity، query، failure rows، control totals، lineage
Decision rights: مالک داده، مالک pipeline، QA، BI و risk acceptor
Rollback/backfill: روش، زمان، validation و consumer notice

گزارش نهایی فقط تعداد Test Case نیست. وضعیت Contract، اختلاف Source/Target، Datasetهای Not Evaluated، Freshness، استثنا، ریسک باقیمانده و پیشنهاد Publish/Hold را نشان دهید. برای ساخت Artifact تصمیم‌محور، از قالب گزارش تست حرفه‌ای کمک بگیرید.

برنامه ۳۰روزه برای استقرار تست Data Warehouse

هفته اول: قرارداد و خط مبنا

یک Data Product حیاتی و یک Dashboard تصمیم‌ساز انتخاب کنید.
Grain، Key، Metric Definition، Source Cut و مالکان را بنویسید.
Profile منبع/مقصد و پنج ریسک اصلی را ثبت کنید.

هفته دوم: تست نزدیک تبدیل

برای Ruleهای مهم Golden Dataset و Unit Test بسازید.
Schema Contract و تست‌های Key/Null/Domain/Relationship را در CI بگذارید.
نسخه Run و Failure rows را به Artifact اضافه کنید.

هفته سوم: Incremental و Recovery

Insert/Update/Delete/Duplicate/Late را اجرا کنید.
Timeout پس از Commit، Partial Write و Concurrent Run را القا کنید.
Idempotency و Backfill یک Partition گذشته را اثبات کنید.

هفته چهارم: Publish و عملیات

Reconciliation پیش از Publish و Smoke پس از Publish را Gate کنید.
Freshness، Completeness، Lineage و Alert/Runbook را فعال کنید.
با Finance/BI خروجی Golden و ریسک باقیمانده را Sign-off کنید.

اشتباهات رایج در تست انبار داده

فقط Count را مقایسه می‌کنید: Key، Control Total، Segment و محتوای مرزی را اضافه کنید.
Grain نوشته نشده است: پیش از Unique Test معنای هر ردیف را تثبیت کنید.
Production را Oracle مطلق می‌دانید: Source هم می‌تواند ناقص یا غلط باشد.
Schema برابر را کافی می‌دانید: Semantics، Unit و Rule محتوایی را جدا بسنجید.
Retry را فقط Happy path اجرا می‌کنید: Timeout-after-commit و Partial Write را القا کنید.
CDC را Exactly-once فرض می‌کنید: Duplicate، ordering، offset و Sink idempotency را آزمایش کنید.
SCD را با Row Count می‌سنجید: Current uniqueness، overlap، gap و point-in-time join را کنترل کنید.
Freshness را با Duration یکی می‌گیرید: Event-to-availability و Completeness را جدا ثبت کنید.
Anomaly را خودکار Defect می‌نامید: نقض Contract را از تغییر واقعی کسب‌وکار تفکیک کنید.
Lineage را اثبات درستی می‌دانید: مسیر شناخته‌شده بدون Oracle می‌تواند مسیر خطا باشد.
Threshold یکسان می‌دهید: حساسیت ستون، Segment و مصرف‌کننده را وارد Gate کنید.
داده حساس را بی‌ضابطه کپی می‌کنید: حداقل Fidelity و چرخه حذف را تعریف کنید.

پرسش‌های متداول

تست انبار داده با تست پایگاه داده چه تفاوتی دارد؟

تست پایگاه داده معمولاً Schema، Constraint، Transaction، Query، Stored Procedure و Migration یک Store را می‌سنجد. تست انبار داده زنجیره چندمنبعی Extract/Transform/Load، Grain، تاریخچه، Dimension/Fact، Reconciliation، Freshness، Lineage و خروجی تحلیلی را نیز پوشش می‌دهد. این دو هم‌پوشانی دارند اما Scope یکسان نیست.

مهم‌ترین تست‌های ETL کدام‌اند؟

Schema و Contract، کامل‌بودن Extract، صحت Ruleهای Transform، Unique/Null/Domain/Relationship، Grain و Fact/Dimension، SCD، Reconciliation Count/Key/Amount، Incremental Insert/Update/Delete، Idempotency، Retry/Recovery، Freshness و Semantic Metric. اولویت هرکدام به اثر Dataset و مصرف‌کننده بستگی دارد.

آیا برابر بودن تعداد رکورد Source و Target کافی است؟

خیر. ممکن است مقدارها Truncate یا جابه‌جا شده باشند، یک رکورد کم و دیگری Duplicate باشد یا Filter اشتباه با Count برابر نتیجه دهد. Count را با Distinct Key، Control Total، Min/Max، Null/Domain، Hash Canonical پارتیشن‌شده و Key-level diff ترکیب کنید.

بارگذاری افزایشی و CDC را چگونه تست کنیم؟

Insert، چند Update، Delete، Event تکراری و خارج‌ترتیب، Late arrival، Snapshot-to-stream boundary، Resume پس از Crash، Offset از‌دست‌رفته، Schema change و Backfill هم‌زمان را اجرا کنید. سپس ثابت کنید مصرف‌کننده Idempotent است و Source/Target برای بازه مشخص Reconcile می‌شوند.

بهترین ابزار تست ETL چیست؟

ابزار واحدی برای همه معماری‌ها بهترین نیست. SQL و Constraint برای Invariantهای نزدیک داده، dbt یا مشابه آن برای مدل/Contract/Data Test، Orchestrator برای Gate و Recovery، ابزار Lineage برای Impact و ابزار Observability برای Drift مفیدند. انتخاب باید با Dialect، Scale، CI، حریم خصوصی، Skill تیم و هزینه Triage در یک PoC واقعی سنجیده شود.

جمع‌بندی؛ داده قابل اعتماد یک زنجیره شاهد است

آزمون انبار داده از یک سؤال روشن آغاز می‌شود: «این Dataset قرار است چه تصمیمی را با چه Grain و Cutoff پشتیبانی کند؟» بعد Contract، Oracle و Failure Policy در هر مرز Source، Raw، Transform، Dimension/Fact، Semantic و Operations قرار می‌گیرند. نتیجه خوب فقط Job سبز نیست؛ Dataset نسخه‌دار، Reconciled، تازه، قابل ردیابی و قابل بازتولید است.

برای شروع یک Partition مالی را انتخاب کنید. Grain و Business Key را بنویسید، یک Golden Dataset کوچک بسازید، چهار Invariant SQL و یک Reconciliation مبلغ/کلید اجرا کنید، همان Run را دوبار و یک بار با Failure پس از Commit آزمایش کنید و خروجی را پیش از Publish Gate کنید. این تمرین کوچک، شکاف‌های واقعی معماری را بسیار زودتر از یک چک‌لیست صدردیفی آشکار می‌کند.

تست نرم افزار, سطوح تست