Pipelines with Databrick Delta Live Tables Part 1/2

Medallion Architecture

• Defines three levels for data pipelines: bronze, silver, and gold.
• Bronze represents raw data ingestion and history.
• Silver involves filtering, cleaning, and augmenting data.
• Gold represents business-level data, including aggregates.

Delta Live Tables (DLT)

• Focus on simplifying ETL processes on Delta Lake.
• Provide declarative tools to build batch and streaming data pipelines.
• Offer built-in declarative quality control, allowing users to declare data quality expectations and actions to take when violations occur.
• Enable easy scaling of infrastructure alongside data.

Live Tables

• Materialized views for the lakehouse.
• Defined by SQL queries.
• Created and kept up-to-date by pipelines.
• Provide tools for managing dependencies, controlling quality, automating operations, simplifying collaboration, saving costs, and reducing latency.

Creating Live Tables

• Use SQL syntax: CREATE LIVE TABLE table_name AS SELECT col_name FROM another_table;

Streaming Live Tables (SLT)

• Based on Spark Structured Streaming.
• Ensure exactly-once processing of input rows.
• Inputs are only read once.
• Compute results over appended-only streams, such as Kafka, Kinesis, or Auto Loader.
• Allow for cost and latency reduction by avoiding reprocessing of old data.
• Created with SQL: CREATE STREAMING LIVE TABLE table_name SELECT * FROM cloud_files(files); (using Auto Loader)

Live Table Pipeline

• Requires three steps:
  • Create the Live Table in a notebook.
  • Create the pipeline with one or more notebooks.
  • Run the pipeline.

Development vs Production Mode

• Development Mode: reuses long-running clusters for faster iteration, no retries on errors enabling faster debugging.
• Production Mode: cuts cost by turning off clusters after completion (within 5 minutes), escalating retries including cluster restarts ensures reliability in the face of transient issues.

Dependent Tables and Live Virtual Schema (LVS)

• Dependencies owned by other products are read from the catalog or Spark data sources as normal.
• Live dependencies from the same pipeline are read from the Live schema.
• DLT detects Live dependencies and executes all operations in the correct order.
• DLT handles parallelism and captures the lineage of the data.
• Live dependencies are referenced using SQL: CREATE LIVE TABLE table_name AS SELECT * FROM Live.live_table_name;

Data Quality with Expectations

• Expectations serve as tests ensuring data quality in production.
• Implemented using SQL constraints or Python functions.
• Examples:
  • SQL: CONSTRAINT valid_timestamp EXPECT (timestamp_col > 'timestamp value') ON VIOLATION DROP;
  • Python: @dlt.expect_or_drop("valid_timestamp", col("timestamp_col") > 'timestamp value')
• Can be configured to track bad records, drop bad records, or abort processing for a single bad record.

Pipelines UI

• Allows visualizing all pipeline information.
• Features include:
  • Visualization of data flows between tables.
  • Discovery of metadata and quality of tables.
  • Access to historical updates.
  • Control operations: switching between dev and prod modes, deleting the pipeline, managing user permissions, scheduling, etc.

Event Log

• Automatically records all pipeline operations.
• Records operational statistics, including time and current status of operations, pipeline cluster configurations, and row count.
• Captures provenance, including table schemas, definitions, declared properties, table-level lineage, and query plans used to update tables.
• Tracks data quality through expectations, including pass/fail statistics and input/output rows that caused expectation failures.

SQL Stream Function

• stream() function allows streaming data from any Delta table.
• Reads a stream of new records instead of a snapshot.
• Applicable only to append-only tables.
• Example: CREATE STREAMING LIVE TABLE my_stream AS SELECT * FROM STREAM(table_name);

Restrictions for Streaming Tables

• Cannot be the target of APPLY CHANGES INTO (CDC).
• Cannot define an aggregate function..
• Cannot be a table on which DML operations (delete/update) have been executed.

Configuration Parameters

• Enable modularization by creating variables that can be used in any notebook.
• Stored as key-value pairs.
• Example: "my_vars.etl_path": "s3://path/json/"
• Accessed in SQL: CREATE STREAMING LIVE TABLE table_name SELECT * FROM cloud_files("${my_vars.etl_path}", "json")
• Accessed in Python: input_path = spark.conf.get("my_vars.etl_path") spark.readStream.format("cloud_files").load(input_path)

This quiz covers the Medallion Architecture framework, highlighting the three levels of data processing: bronze, silver, and gold. It also explores Delta Live Tables (DLT), which simplify ETL processes in Delta Lake and enable users to build resilient data pipelines using SQL. Test your knowledge on these essential concepts in data engineering!