SQL Interview Prep · درس

مخطط النجمة وتصميم مستودع البيانات

جداول الحقائق والأبعاد ومفاضلات نزع التطبيع ونمذجة OLAP

مخطط النجمة وتصميم مستودع البيانات درس مجاني في SQL Interview Prep على CoddyKit. هذا هو الدرس 3 من أصل 4. يمكنك قراءة الدرس كاملاً أدناه مجاناً — ثم تمرن عليه مباشرة في المتصفح باستخدام محرر أكواد مدمج ومدرس ذكاء اصطناعي متاح 24/7. هذا الدرس جزء من مسار التعلم في SQL Interview Prep، وتقدمك يتزامن عبر الويب وتطبيق CoddyKit. تتضمن دورة SQL Interview Prep 4 دروس في المجموع.

OLTP مقابل OLAP

تبدأ أسئلة مستودعات البيانات بتمييز أساسي يتوقع القائمون بالمقابلة أن تتقنوه: OLTP مقابل OLAP.

OLTP (معاملاتي): عمليات قراءة/كتابة صغيرة كثيرة، مع تطبيع عالٍ للحفاظ على السلامة. وهو ما يشغّل التطبيق.
OLAP (تحليلي): عمليات قراءة تجميعية كبيرة قليلة على البيانات التاريخية، مع إلغاء تطبيع متعمد لزيادة السرعة. وهو ما يشغّل التقارير ولوحات المعلومات.

المخططات النجمية هي أحد تصاميم OLAP. فالهدف الأساسي منها هو تنفيذ الاستعلامات التحليلية بسرعة، مع قبول التكرار مقابل ذلك.

الحقائق والأبعاد

يقسّم المخطط النجمي البيانات إلى نوعين من الجداول:

جدول الحقائق: الأحداث أو المعاملات القابلة للقياس، مثل عملية بيع أو نقرة. ويحتوي على المقاييس الرقمية والمفاتيح الأجنبية التي تشير إلى الأبعاد.
جداول الأبعاد: السياق الوصفي الذي تحلّلون البيانات وفقًا له، مثل التاريخ والمنتج والعميل والمتجر.

يوجد جدول الحقائق في المركز، وتحيط به الأبعاد مثل نقاط النجمة، ومن هنا جاءت التسمية.

بنية جدول الحقائق

يتكوّن جدول الحقائق في الغالب من مفاتيح أجنبية ومقاييس رقمية. وهو طويل وضيّق وينمو باستمرار.

المقاييس أعداد قابلة للجمع تُستخدم في التجميع، مثل الكمية والإيرادات والتكلفة. ويجب تحديد درجة التفصيل (ما الذي يمثله الصف الواحد؟) بوضوح؛ ففي هذا المثال يمثّل الصف الواحد بند منتج واحدًا في عملية بيع واحدة.

CREATE TABLE fact_sales (
  sale_id      BIGINT PRIMARY KEY,
  date_key     INT  NOT NULL,   -- FK to dim_date
  product_key  INT  NOT NULL,   -- FK to dim_product
  customer_key INT  NOT NULL,   -- FK to dim_customer
  store_key    INT  NOT NULL,   -- FK to dim_store
  quantity     INT,             -- measure
  revenue      DECIMAL(12,2),   -- measure
  cost         DECIMAL(12,2)    -- measure
);

بنية جدول الأبعاد

تكون الأبعاد قليلة الصفوف وعريضة، إذ تحتوي على أعمدة وصفية كثيرة تُستخدم في التصفية والتجميع. ويُلغى تطبيعها عمدًا حتى يحتاج الاستعلام إلى عملية JOIN واحدة فقط لكل بُعد.

لاحظوا أن dim_product يحتفظ بالفئة والعلامة التجارية في الصف نفسه بدلًا من وضعهما في جدولين منفصلين. وهذا التكرار مقصود، لأنه يتجنب عمليات JOIN الإضافية وقت تنفيذ الاستعلام.

CREATE TABLE dim_product (
  product_key  INT PRIMARY KEY,   -- surrogate key
  product_id   INT,              -- natural/business key
  product_name VARCHAR(100),
  category     VARCHAR(50),      -- denormalized
  brand        VARCHAR(50),      -- denormalized
  unit_price   DECIMAL(10,2)
);

استعلام في مخطط نجمي

هذا هو العائد من هذا التصميم. فعادةً ما يربط استعلام تحليلي جدول الحقائق ببضعة أبعاد، ثم يرشّح النتائج ويجري التجميع. عملية JOIN واحدة لكل بُعد، دون سلاسل ربط عميقة.

يطلب القائمون بالمقابلة منكم كتابة هذا النوع تحديدًا من الاستعلامات على مخطط نجمي.

SELECT d.category,
       t.year,
       SUM(f.revenue) AS total_revenue
FROM fact_sales f
JOIN dim_product d ON d.product_key = f.product_key
JOIN dim_date    t ON t.date_key    = f.date_key
WHERE t.year = 2025
GROUP BY d.category, t.year
ORDER BY total_revenue DESC;

المفاتيح البديلة

تستخدم الأبعاد مفتاحًا بديلًا: وهو مفتاح أساسي صحيح بلا معنى تجاري، مثل product_key، يولّده مستودع البيانات ويكون منفصلًا عن المفتاح الطبيعي في النظام المصدر.

لماذا يهتم القائمون بالمقابلة بذلك؟

يفصل مستودع البيانات عن مفاتيح الأعمال التي قد تتغير.
يحافظ على ضيق جداول الحقائق، إذ تكون عمليات JOIN على الأعداد الصحيحة سريعة.
وهو ضروري لتتبع السجل التاريخي باستخدام الأبعاد المتغيرة ببطء (في المشهد التالي).

الأبعاد المتغيرة ببطء

هذا موضوع مفضل في مقابلات مستودعات البيانات: عندما تتغير سمة في أحد الأبعاد، كأن ينتقل عميل إلى مدينة أخرى، كيف تتعاملون مع ذلك؟ تُسمى هذه الحالات الأبعاد المتغيرة ببطء (SCD):

النوع 1: استبدال القيمة القديمة. بلا سجل تاريخي.
النوع 2: إضافة صف جديد مع تواريخ السريان ومؤشر يحدد السجل الحالي. يحتفظ بالسجل الكامل، ويتطلب مفاتيح بديلة.
النوع 3: الاحتفاظ بعمود للقيمة السابقة. سجل تاريخي محدود.

يُعد النوع 2 الإجابة الأكثر شيوعًا والمتوقعة لتتبع التغييرات بمرور الوقت.

-- SCD Type 2 dimension
CREATE TABLE dim_customer (
  customer_key INT PRIMARY KEY,   -- surrogate
  customer_id  INT,              -- natural key
  city         VARCHAR(50),
  valid_from   DATE,
  valid_to     DATE,
  is_current   BOOLEAN
);

المخطط النجمي مقابل Snowflake

توقعوا سؤال المقارنة. يعمل مخطط snowflake على تطبيع الأبعاد في جداول فرعية (المنتج -> الفئة -> القسم)، بينما يُبقيها المخطط النجمي مسطّحة.

المخطط النجمي: عمليات JOIN أقل، وقراءات أسرع، مع بعض التكرار. وهو المفضل لأداء الاستعلامات.
Snowflake: مساحة تخزين أقل وصيانة أسهل للأبعاد، لكنه يتطلب عمليات JOIN أكثر في كل استعلام.

قولوا: «اجعلوا المخطط النجمي هو الخيار الافتراضي لسرعة الاستعلامات، واستخدموا Snowflake فقط عندما تكون الأبعاد كبيرة ويُعاد استخدامها.»

بُعد التاريخ

يحتوي كل مخطط نجمي تقريبًا على بُعد تاريخ مخصص بدلًا من عمود تاريخ خام. ويحسب مسبقًا السنة والربع والشهر ويوم الأسبوع ومؤشرات العطلات والفترات المالية.

يتيح ذلك للمحللين التجميع حسب «الربع المالي» أو is_weekend باستخدام عملية JOIN بسيطة بدلًا من نثر دوال التاريخ في الاستعلامات. وذكر بُعد التاريخ دون أن يُطلب منكم ذلك مؤشر قوي على خبرتكم في بناء مستودعات البيانات.

CREATE TABLE dim_date (
  date_key   INT PRIMARY KEY,   -- e.g. 20250131
  full_date  DATE,
  year       INT,
  quarter    INT,
  month      INT,
  day_of_week VARCHAR(10),
  is_weekend BOOLEAN,
  fiscal_qtr VARCHAR(6)
);

اختيار درجة التفصيل

أهم قرار منفرد في جدول الحقائق هو درجة التفصيل: ما الذي يمثله الصف الواحد؟ حدّدوا ذلك قبل أي شيء آخر.

إذا كانت الدرجة خشنة جدًا، مثل صف واحد لكل يوم ولكل متجر، فستفقدون التفاصيل.
إذا كانت دقيقة جدًا، مثل صف واحد لكل عنصر ممسوح ضوئيًا، فسيصبح الجدول ضخمًا جدًا.

يحدد بيان واضح لدرجة التفصيل، مثل «صف واحد لكل منتج في كل بند طلب»، الأبعاد والمقاييس التي تنتمي إلى الجدول. وينتبه القائمون بالمقابلة إلى هذا الانضباط.

متى نلغي التطبيع

اربطوا ذلك بالتطبيع. تعمل أنظمة OLTP على التطبيع إلى 3NF للحفاظ على السلامة، بينما تلغي مستودعات البيانات تطبيع الأبعاد عمدًا لزيادة سرعة القراءة.

الموازنة التي يجب أن توضحوها:

تكرار بيانات الأبعاد مقبول لأن مستودع البيانات يُحمّل من خلال عمليات ETL مضبوطة، وليس من خلال عمليات كتابة عشوائية من التطبيق.
عدد عمليات JOIN الأقل يعني تجميعات أسرع على مليارات الصفوف في جدول الحقائق.

إنما يميز الإجابات المتقدمة هنا هو حسن التقدير، لا اتباع قاعدة جامدة.

تحقق سريع

أنتم تصممون مستودع بيانات للمبيعات، وتحتاجون إلى الاحتفاظ بالسجل الكامل لمدينة العميل عندما ينتقل إلى مدينة أخرى.

مراجعة: المخطط النجمي وتصميم مستودعات البيانات

يمكنكم الآن الإجابة عن أسئلة نمذجة مستودعات البيانات:

يعمل OLTP على التطبيع للحفاظ على السلامة، بينما يلغي OLAP التطبيع لزيادة سرعة القراءة.
يحتوي المخطط النجمي على جدول حقائق مركزي (مفاتيح أجنبية ومقاييس رقمية) تحيط به أبعاد مسطّحة.
استخدموا مفاتيح بديلة وبُعد تاريخ مخصصًا.
تتبعوا التغييرات باستخدام SCD من النوع 2، وحددوا درجة تفصيل الحقائق أولًا.
فضّلوا المخطط النجمي على Snowflake لتحسين أداء الاستعلامات.

الأسئلة الشائعة

هل درس «مخطط النجمة وتصميم مستودع البيانات» مجاني؟

نعم — نص درس «مخطط النجمة وتصميم مستودع البيانات» كامل متاح مجاناً هنا على الويب. لتمرينه بشكل تفاعلي (محرر أكواد مدمج ومدرس ذكاء اصطناعي متاح 24/7) وفتح باقي دورة SQL Interview Prep، انتقل إلى CoddyKit PRO. تتضمن دورة SQL Interview Prep 4 دروس في المجموع.

ماذا ستتعلم في «مخطط النجمة وتصميم مستودع البيانات»؟

جداول الحقائق والأبعاد ومفاضلات نزع التطبيع ونمذجة OLAP تتمرن على SQL Interview Prep مع أكواد عملية تشغلها مباشرة في المتصفح، ومدرس ذكاء اصطناعي متاح 24/7 يجيب على أسئلتك أثناء عملك.

هل أحتاج إلى خبرة سابقة لأبدأ SQL Interview Prep؟

لا تُشترط خبرة سابقة. SQL Interview Prep على CoddyKit منظم للمبتدئين حتى المتقدمين، لذا يمكنك البدء من هنا أو من البداية والتقدم بسرعتك الخاصة. هذا هو الدرس 3 من أصل 4.

كم من الوقت يستغرق درس «مخطط النجمة وتصميم مستودع البيانات»؟

معظم دروس CoddyKit تستغرق حوالي 5–10 دقائق. كل منها موجز وتفاعلي، لذا تحرز تقدماً مستمراً وتستأنف من حيث توقفت عبر الويب والتطبيق.

هل يمكنني كتابة وتشغيل أكواد في درس SQL Interview Prep هذا؟

نعم. كل درس في SQL Interview Prep يتضمن محرر أكواد مدمج، لذا تكتب وتشغل أكواداً حقيقية مباشرة في متصفحك وتحصل على تعليقات فورية من الذكاء الاصطناعي — بدون إعداد محلي.

جميع الدروس في هذه الدورة

← العودة إلى SQL Interview Prep