mmodarre/lakehouse_plumber/lhp

Lakehouse Plumber (LHP)

Modern YAML-driven ETL framework for Databricks Lakeflow Spark Declarative Pipelines.

Use the technical references bundled with this installed release. Read only those needed for the current development task. Website access is optional. Complete the requested work and explain relevant decisions; do not introduce course checkpoints.

For environment installation, use setup.md.

Before You Start (read this first)

• Reuse before authoring. On any "create a flowgroup / pipeline / ingestion" request, scan templates/ and blueprints/ first. If an existing template (one parameterised flowgroup) or blueprint (a whole-flowgroup pattern repeated per site/region/tenant) already fits, reuse it via use_template: / use_blueprint: when it matches the request. Ask only if the choice is ambiguous or changes the requested behaviour. Only author fresh YAML when nothing fits.
• Write-target rule of thumb: incremental/append/streaming ingest → streaming_table; full recompute, joins, aggregations, or dimensional rebuilds → materialized_view.
• New project from scratch? Load quickstart.md for the init → lhp.yaml/substitutions/pipeline_config → validate → generate path.
• Common mistakes to avoid:
• Deprecated {token} braces — always ${token} (only %{local_var} uses non-$ braces).
• Missing stream(view) wrapper in a SQL transform that reads a streaming source.
• Secrets inline in YAML — always ${secret:scope/key}.
• operational_metadata at the write level — apply it at the load/transform (action) level.
• Suggesting lhp show (removed) or lhp deps / --force (deprecated). Use lhp dag, lhp diff; every generate is a full regenerate.

Read Project Context

Read the user's existing project files before generating new configurations:

1. lhp.yaml — project config, operational metadata definitions
2. substitutions/ — environment tokens and secret scopes
3. presets/ — reusable defaults (action-type config)
4. templates/ — reusable action patterns (one parameterised flowgroup)
5. blueprints/ — reusable whole-flowgroup patterns, instantiated per site/region/tenant
6. Existing pipelines/ YAML files — match naming/structure conventions

Core Architecture

Three main actions: Load, Transform, Write.
- Load: Load data from a source into a view.
- Transform: Transform data in a view into a new view.
- Write: Write data from a view into a table or sink.
 
One auxiliary action: Test.
- Test: For testing only in test environment using expectations.

• Pipeline: Logical grouping; generated files organized by pipeline name. All files in a pipeline run in a single Spark Declarative Pipeline.
• FlowGroup: One source entity; becomes one Python file. A flowgroup is a logical grouping of actions.
• Action: Individual operation (load, transform, write, test)

Minimal FlowGroup

pipeline: <pipeline_name>
flowgroup: <flowgroup_name>
 
actions:
  - name: <action_name>
    type: load
    readMode: stream          # stream or batch
    source:
      type: cloudfiles        # cloudfiles|delta|sql|jdbc|python|kafka|custom_datasource
      path: "${landing_volume}/folder/*.csv"
      format: csv
    target: v_raw_data
 
  - name: transform_data
    type: transform
    transform_type: sql       # sql|python|schema|data_quality|temp_table
    source: v_raw_data
    target: v_cleaned
    sql: |
      SELECT * FROM stream(v_raw_data)
 
  - name: write_table
    type: write
    source: v_cleaned
    write_target:
      type: streaming_table   # streaming_table|materialized_view
      database: "${catalog}.${schema}"
      table: "my_table"

Substitution Syntax (Processing Order)

Template and Flowgroup Naming Conventions

When creating reusable templates:

Template Files: TMPL<number>_<source_type>_<function>.yaml

• <number>: Sequential identifier (001, 002, etc.)
• <source_type>: Source type from the load action (delta, cloudfiles, jdbc, kafka, sql, etc.)
• <function>: What the template does (scd2, bronze, incremental, etc.)
• Examples:
• TMPL001_delta_scd2.yaml - SCD Type 2 from Delta source
• TMPL002_cloudfiles_bronze.yaml - Bronze ingestion from CloudFiles
• TMPL003_jdbc_incremental.yaml - Incremental load from JDBC

Flowgroups Using Templates: <domain>_<final_table>_TMPL<number>

• <domain>: Business domain or subject area (billing, orders, customers, etc.)
• <final_table>: The final target table name
• TMPL<number>: Template number being used (must match template file)
• Examples:
• billing_invoice_TMPL001 - Uses TMPL001 for invoice table
• orders_customer_TMPL002 - Uses TMPL002 for customer table
• analytics_fact_sales_TMPL003 - Uses TMPL003 for fact_sales table

Quick Reference: Action Types

Each sub-type has its own leaf reference file. Load only the one(s) you need.

Key Rules

1. `stream(view_name)` required in SQL transforms reading from streaming sources
2. CloudFiles `_metadata.*` columns only available in views, not downstream transforms
3. Preset lists are replaced, not merged; nested dicts are deep-merged
4. All-or-nothing job_name: if any flowgroup has job_name, all must have it
5. Never put secrets in YAML values — always use ${secret:scope/key}
6. Validate before generating: lhp validate --env <env>
7. `readMode: stream` -> spark.readStream, `batch` -> spark.read
8. Monitoring requires event_log — monitoring: {} won't work without event_log section
9. `catalog` and `schema` are REQUIRED in `pipeline_config.yaml` — set them per-pipeline or in a top-level project_defaults block. Missing either fails generation. See project-config.md.
10. `resources/lhp/` is exclusively managed by LHP — every lhp generate wipes it and rewrites it. Place custom resource YAMLs (hand-written jobs, dashboards, secret scopes) under resources/ at the top level or any non-lhp subdirectory.
11. Every `lhp generate` is a full regenerate — there is no incremental mode and no --force flag (it was removed and is a no-op). Never suggest --force.

Best Practice Defaults (apply unless the user overrides)

These defaults reflect LHP's published enterprise best practices. Load best-practices.md for full rules and rationale.