🧱 ELT Pipelines & Medallion Architecture

Bronze/Silver/Gold, Auto Loader, Delta Live Tables

Databricks Data Engineer · DE-Associate · ⏱️ 25 min · ❓ 6 savol

🥉🥈🥇 Medallion Architecture

Bronze — raw data ingestion, hech narsa o'zgartirmasdan (append-only). Silver — tozalangan, validated, deduplicated data. Gold — business-level aggregations, reporting uchun tayyor. Har qatlam Delta format.

📥 Auto Loader

Cloud storage (S3/ADLS/GCS) dan yangi fayllarni avtomatik incremental ingestion. cloudFiles format. Schema inference va evolution. Checkpoint orqali exactly-once semantics. Millions of files — efficient.

🔄 Delta Live Tables (DLT)

Declarative pipeline framework. `@dlt.table` decorator. Live Tables (complete refresh) va Streaming Live Tables (incremental). Expectations — data quality constraints. Automated dependency management, error handling.

MERGE INTO (UPSERT)

Delta'da MERGE — upsert (update + insert). Source bilan target ni match qilib: mos kelsa update, mos kelmasa insert. SCD Type 1 (overwrite) va Type 2 (history) uchun ishlatiladi.

💡 Asosiy nuqtalar

Bronze: raw, Silver: clean, Gold: aggregated
Auto Loader: new file detection, incremental, checkpoint
DLT: declarative, expectations, auto dependency
MERGE INTO: upsert operatsiyasi
Streaming pipeline: Auto Loader → Bronze → Silver → Gold

📋 Kod misoli

-- Auto Loader (Bronze ingestion)
CREATE OR REFRESH STREAMING TABLE bronze_orders
AS SELECT
  current_timestamp() as ingestion_time,
  *
FROM cloud_files(
  "s3://bucket/orders/",
  "json",
  map("cloudFiles.inferColumnTypes", "true")
);

-- DLT Silver table (with expectations)
@dlt.table
@dlt.expect_or_drop("valid_amount", "amount > 0")
@dlt.expect_or_fail("not_null_id", "id IS NOT NULL")
def silver_orders():
  return (dlt.read_stream("bronze_orders")
    .dropDuplicates(["id"])
    .filter("status != 'CANCELLED'")
  )

-- MERGE INTO (upsert)
MERGE INTO silver_customers AS target
USING new_customers AS source
ON target.id = source.id
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *;

🎯 Imtihon maslahatlari

Auto Loader vs COPY INTO: Auto Loader — scalable (millions of files), streaming; COPY INTO — batch, simpler
DLT expect_or_drop: yomon qator filter qilinadi pipeline davom etadi. expect_or_fail: pipeline to'xtaydi
Bronze = raw data, schema minimal yoki yo'q. Silver = validated, typed. Gold = business aggregations
MERGE INTO (upsert): CDC (Change Data Capture) data va SCD Type 1/2 uchun asosiy pattern
DLT pipeline — DLT-specific cluster da ishlaydi (alohida boshqariladi, manual cluster emas)

⚠️ Ko'p adashadigan

Bronze layer da transformation qilish — Bronze raw, hech narsa o'zgartirmasdan saqlash kerak
DLT `@dlt.table` va streaming `@dlt.append_flow` farqini bilmaslik
Auto Loader checkpoint yo'qolsa — barcha fayllarni qayta o'qiydi (exactly-once buziladi)

🧠 Eslab qolish: "BSG Medals" = Bronze (raw silver Gold — medallar kabi, har biri oldingiidan toza). "MERGE = Match then Update or Insert"