Back to all blogs
    4 min read

    🚰 Data Engineering & Pipelines

    #databricks#data-engineering#dlt#etl

    Data Engineering & Pipelines

    🏗️ The Evolution of ETL: Imperative vs. Declarative

    What Existed Previously: Traditionally, Data Engineers built Imperative ETL (Extract, Transform, Load) pipelines. The Imperative approach means you must manually write the code to orchestrate how every single task runs. You have to manually track which files were processed yesterday, write custom logic for retry mechanisms if an API times out, manage the execution order of dependencies, and manually spin up cluster compute.

    Problems Faced:

    • Brittle Code: Pipelines easily broke due to unexpected upstream data changes.
    • Massive Overhead: Engineers spent 80% of their time writing boilerplate orchestration and error-handling code, and only 20% writing actual business logic.

    How Present Technology Solves It: Databricks introduces the Declarative Paradigm via Lakeflow Declarative Pipelines and Delta Live Tables (DLT). The Declarative approach means you simply define what data transformations should happen (using standard SQL or Python). Databricks automatically figures out how to execute it—automatically managing cluster compute, mapping table dependencies, determining execution order, and tracking incremental processing.

    🌊 Delta Live Tables (DLT)

    Delta Live Tables (DLT) is the native declarative ETL framework within Databricks used to reliably build and manage data pipelines.

    Core Capabilities:

    • Automated Lineage: Because you simply declare relationships (e.g., Table B is built by joining Table A and Table C), Databricks automatically graphs the data lineage (Source → Bronze → Silver → Gold) visually in the UI.
    • Streaming & Batch Unified: DLT seamlessly handles both streaming (using Streaming Tables) and batch updates (using Materialized Views) with the exact same syntax.
    • Auto-Scaling Compute: You don't manage clusters. DLT requests compute when needed and tears it down when the pipeline finishes.
    • Simplified CDC: DLT utilizes the APPLY CHANGES INTO syntax to effortlessly handle Change Data Capture (CDC), automatically managing complex upserts, out-of-order events, and Slowly Changing Dimensions (SCD Type 2).

    🛡️ Data Quality Validation (Expectations)

    "Garbage in, garbage out" is the enemy of Analytics and AI. If bad data sneaks into the Silver or Gold layers, downstream Machine Learning models will fail silently.

    The DLT Expectation Framework: DLT embeds data quality rules—called Expectations—directly into the pipeline code. You do not need separate validation scripts.

    When defining a table, you declare your expectations (e.g., EXPECT (revenue > 0) AND (user_id IS NOT NULL)). You also declare the Automated Action Databricks should take if a record violates that expectation:

    1. Drop: (EXPECT ... ON VIOLATION DROP ROW) The pipeline continues, but the bad row is dropped and recorded in the event log.
    2. Fail: (EXPECT ... ON VIOLATION FAIL UPDATE) The entire pipeline instantly halts, preventing bad data from moving downstream and alerting the team.
    3. Warn/Alert: (EXPECT ...) The row is allowed to pass, but the violation is aggressively flagged in the Data Quality observability dashboard.

    🔄 Data Transformation & Update Patterns

    ETL vs. ELT:

    • ETL (Extract, Transform, Load): Data is transformed before it is loaded into the target system. Best for strict compliance or protecting sensitive data (Anonymization) before it hits the lake.
    • ELT (Extract, Load, Transform): Data is loaded raw into the Lakehouse (Bronze), and transformed using the sheer power of Databricks compute (Silver/Gold). This is the standard, high-performance Lakehouse pattern.

    Common Transformation Patterns:

    • Filtering (Removing invalid records)
    • Enrichment (Joining third-party data to internal data)
    • Anonymization (Masking PII like credit card numbers)
    • Deduplication (Removing duplicate rows)

    Delta Update Patterns (MERGE INTO): Instead of manually tracking updates and inserts, Databricks relies heavily on the MERGE INTO SQL command to handle Upserts (Update if it exists, Insert if it's new).

    • Hard Delete: Permanently removing a record from the Delta table.
    • Soft Delete: Keeping the record but flagging it as is_deleted = True (SCD Type 2). This maintains perfect historical accuracy.

    🧪 Practice Drill

    Q1. A Data Engineer spends 3 hours writing custom Python code to track which raw JSON files were processed yesterday so they don't ingest them twice today. Are they using an Imperative or Declarative pipeline strategy?

    Q2. You are using Delta Live Tables to ingest sensor data. If a sensor temporarily breaks and sends a negative temperature value, you want that specific row deleted from the pipeline, but you want the rest of the valid data to continue flowing. Which DLT Expectation action should you use?

    Q3. What specific SQL command does DLT use to drastically simplify complex Change Data Capture (upserts and deletes) from transactional databases?

    💡 Click for Solutions

    A1. They are using an Imperative strategy (manually managing the "how"). If they were using a Declarative strategy (like DLT with Auto Loader), Databricks would track the incremental file processing automatically.

    A2. You should use the Drop action (ON VIOLATION DROP ROW). This isolates and removes the bad record while allowing the pipeline to succeed.

    A3. APPLY CHANGES INTO.

    ← ⚙️ The Processing Layer | Next Topic → 🔐 Data Governance (Unity Catalog)