Introduction
Different organizations use different names for these artifacts: business requirements, mapping specifications, source-to-target mappings, data contracts, transformation rules, or semantic definitions. The name matters less than the goal: capturing business intent in a structured form that can be reviewed, governed, and translated consistently into engineering assets.
In many enterprise data-engineering programs, building a Databricks pipeline is not the most difficult part of delivery.
The more difficult part often comes earlier.
A business rule begins in a meeting, appears in a source-to-target mapping document, gets translated into SQL or PySpark, becomes part of a data-quality rule, is repeated in test cases, and is eventually described again in technical documentation.
The same business definition is interpreted many times by different people and across different artifacts.
That repeated translation creates delivery risk:
Transformation logic can differ between a mapping document and implemented code.
Data-quality controls may not cover the actual business rule.
Documentation can drift after the pipeline changes.
New team members need to reconstruct logic from multiple places.
Reconciliation issues take longer because the original intent is difficult to trace.
This article proposes a practical pattern for reducing that risk:
Treat business requirements, mapping specifications, transformation rules, and data definitions as governed metadata, then use that metadata to drive pipeline generation, data quality, documentation, and human review.
Databricks remains the execution, governance, orchestration, and observability platform. The metadata layer becomes the contract between business intent and the assets that run on the platform.
The Practical Problem: One Rule, Many Implementations
Consider a simple reporting requirement:
“Only completed orders with a valid customer identifier should contribute to daily net revenue.”
That business rule may be implemented in several places:
A source-to-target mapping document.
A Spark SQL or PySpark transformation.
A Gold-layer aggregate table.
A data-quality expectation.
A reconciliation query.
A dashboard definition.
A technical specification.
Now imagine the SQL implementation filters COMPLETED orders, but the test case includes both COMPLETED and SHIPPED orders. Or imagine a data-quality rule checks for non-null customer IDs but does not check whether the ID exists in the customer master.
The pipeline may still run successfully.
But the business result can be wrong.
The issue is not that Spark, Delta, or Lakeflow failed. The issue is that the original business intent was manually rewritten in several places.
A Better Pattern: Business Intent as Governed Metadata
The proposed flow is:
Business Requirements + Mapping Specifications + Source Definitions
↓
Canonical Metadata Model
↓
Validation + Human Approval
↓
Generated SQL / PySpark / DQ Rules / Tests / Documentation
↓
Lakeflow Pipelines + Unity Catalog + Monitoring
↓
Governed Data Products
The key idea is simple:
Do not treat the STTM as a static Word document or spreadsheet. Treat it as structured, governed engineering metadata.
The metadata should capture enough detail to describe what is being built, why it is being built, and how it should be validated.
A Synthetic Example: Daily Customer Revenue
Assume we need a Gold-layer table called:
gold.customer_daily_revenue
The table will provide daily revenue by customer using a raw orders source.
Example source-to-target mapping
Target Column Source Transformation Rule Business Rule
| revenue_date | raw.orders.order_date | Cast to date | Revenue is reported by calendar date |
| customer_id | raw.orders.customer_id | Pass through | Customer ID must be present |
| completed_order_count | raw.orders.order_id | Count distinct order ID | Include completed orders only |
| net_revenue | raw.orders.amount | Sum amount | Include completed orders with a positive amount |
| load_timestamp | System generated | Current timestamp | Technical audit field |
The same mapping can be normalized into a canonical metadata model.
Example metadata representation
{
"target_table": "gold.customer_daily_revenue",
"grain": ["revenue_date", "customer_id"],
"sources": [
{
"table": "bronze.orders"
}
],
"filters": [
"order_status = 'COMPLETED'",
"customer_id IS NOT NULL",
"amount > 0"
],
"columns": [
{
"name": "revenue_date",
"expression": "CAST(order_date AS DATE)",
"data_type": "DATE",
"business_definition": "Calendar date used for revenue reporting"
},
{
"name": "customer_id",
"expression": "customer_id",
"data_type": "STRING",
"business_definition": "Unique identifier of the customer"
},
{
"name": "completed_order_count",
"expression": "COUNT(DISTINCT order_id)",
"data_type": "BIGINT",
"business_definition": "Number of completed orders"
},
{
"name": "net_revenue",
"expression": "SUM(amount)",
"data_type": "DECIMAL(18,2)",
"business_definition": "Revenue from completed orders with positive amount"
}
],
"quality_rules": [
{
"name": "valid_customer_id",
"expression": "customer_id IS NOT NULL",
"severity": "fail"
},
{
"name": "positive_revenue",
"expression": "net_revenue >= 0",
"severity": "fail"
}
],
"owner": "Data Engineering",
"business_owner": "Commercial Analytics",
"approval_status": "Approved"
}This is not intended to replace thoughtful architecture or business review.
It creates a structured, reviewable contract before implementation begins.
Generating a Lakeflow SQL Asset
Once the mapping is approved, the transformation logic can be generated as a Lakeflow Spark Declarative Pipelines SQL asset.
CREATE OR REFRESH MATERIALIZED VIEW customer_daily_revenue
(
CONSTRAINT valid_customer_id
EXPECT (customer_id IS NOT NULL)
ON VIOLATION FAIL UPDATE,
CONSTRAINT non_negative_revenue
EXPECT (net_revenue >= 0)
ON VIOLATION FAIL UPDATE
)
AS
SELECT
CAST(order_date AS DATE) AS revenue_date,
customer_id,
COUNT(DISTINCT order_id) AS completed_order_count,
SUM(amount) AS net_revenue,
CURRENT_TIMESTAMP() AS load_timestamp
FROM bronze.orders
WHERE order_status = 'COMPLETED'
AND customer_id IS NOT NULL
AND amount > 0
GROUP BY
CAST(order_date AS DATE),
customer_id;The important point is not the amount of SQL generated.
The important point is traceability:
The target grain comes from approved metadata.
The filter logic comes from approved metadata.
The aggregation comes from approved metadata.
The data-quality rules come from approved metadata.
The documentation describes the same approved definitions.
The generated code is still reviewed by an engineer before promotion.
Generating a Python Version
The same approved metadata could also generate a Python implementation when the use case requires more complex transformations.
from pyspark import pipelines as dp
from pyspark.sql import functions as F
@dp.table(
name="customer_daily_revenue",
comment="Daily completed-order revenue by customer."
)
@dp.expect_or_fail(
"valid_customer_id",
"customer_id IS NOT NULL"
)
@dp.expect_or_fail(
"non_negative_revenue",
"net_revenue >= 0"
)
def customer_daily_revenue():
return (
spark.read.table("bronze.orders")
.filter(
(F.col("order_status") == "COMPLETED")
& F.col("customer_id").isNotNull()
& (F.col("amount") > 0)
)
.groupBy(
F.to_date("order_date").alias("revenue_date"),
"customer_id"
)
.agg(
F.countDistinct("order_id").alias("completed_order_count"),
F.sum("amount").alias("net_revenue")
)
.withColumn("load_timestamp", F.current_timestamp())
)The platform and language can vary.
The metadata contract should remain stable.
That is what makes the architecture reusable across SQL, PySpark, Spark SQL, dbt, Snowflake, or other delivery targets over time.
The Canonical Metadata Model
The central component of this pattern is the canonical metadata model.
It should not be only a list of column mappings.
A useful enterprise metadata model should capture at least the following.
Metadata Area Example Questions
| Target definition | What table, view, or dataset is being created? |
| Grain | What makes one output row unique? |
| Source lineage | Which sources and joins contribute to the target? |
| Transformation logic | How is each target field calculated? |
| Business definition | What does the metric or attribute mean? |
| Data-quality rule | What must be true for the data to be trusted? |
| Reconciliation rule | How should source and target totals be compared? |
| Ownership | Who owns the definition and who approves changes? |
| Change history | What changed, why, and when? |
| Delivery status | Is the asset drafted, reviewed, approved, deployed, or retired? |
This is also where a human approval workflow matters.
A rule should not be generated and deployed simply because an AI model interpreted a document.
The metadata should move through controlled states:
Draft
→ Validated
→ Reviewed
→ Approved
→ Generated
→ Deployed
→ Monitored
That workflow provides an evidence trail for both delivery teams and governance stakeholders.
Quality Rules Should Be First-Class Metadata
In many projects, data quality is added late in the delivery lifecycle.
The mapping is approved. Code is written. A test fails. Then someone adds a check.
A metadata-driven approach treats data quality as part of the original design.
For the revenue example, the metadata may define:
Rule Name Rule Expected Behavior
| valid_customer_id | customer_id IS NOT NULL | Fail the update |
| positive_order_amount | amount > 0 | Exclude invalid source records |
| valid_order_status | Status must be COMPLETED | Include qualifying business records only |
| daily_revenue_reconciliation | Source total equals target total within tolerance | Investigate variance |
This can generate:
Instead of data quality becoming a separate spreadsheet, it becomes part of the same engineering contract.
Where Unity Catalog Fits
Unity Catalog is the governance foundation around the deployed assets.
In this pattern, Unity Catalog is where teams can govern and discover the resulting data products, manage access, understand lineage, and locate the production datasets that were created from approved metadata.
The metadata-driven layer should complement—not duplicate—those governance capabilities.
A practical separation of responsibilities looks like this:
Layer Primary Responsibility
| Business and mapping artifacts | Define business intent |
| Canonical metadata model | Normalize and govern transformation meaning |
| Copilot or rules engine | Generate repeatable delivery artifacts |
| Human approval workflow | Validate semantic correctness and exceptions |
| Lakeflow pipelines | Execute and operationalize transformations |
| Unity Catalog | Govern access, discovery, and lineage |
| Monitoring and event data | Observe run health and quality behavior |
The result is not a replacement for Databricks.
It is a stronger path into Databricks.
Why Human Approval Still Matters
AI can accelerate documentation extraction, identify likely joins, draft SQL, and recommend quality rules.
But business semantics still require accountable review.
For example, “net revenue” may mean different things across organizations:
Gross sales minus returns.
Gross sales minus discounts and returns.
Recognized revenue after invoicing.
Posted revenue after settlement.
Revenue excluding cancelled orders.
No model should silently decide that definition.
The role of the copilot is to make ambiguity visible, generate a structured draft, highlight missing information, and create a reviewable implementation package.
The role of the engineer and business owner is to approve the meaning.
That is the difference between simple code generation and governed engineering automation.
Implementation Approach
A realistic implementation can be incremental.
Phase 1: Metadata standardization
Start with a defined template for:
Phase 2: Validation
Add deterministic validation rules:
Every target column has a data type.
Every derived column has a transformation expression.
Every target table has a defined grain.
Every source alias resolves to a known source.
Every join includes a join condition.
Every critical metric has a business definition.
Every production asset has an owner.
Phase 3: Generation
Generate a small, useful set of artifacts:
Lakeflow SQL or PySpark templates.
Table DDL or table specifications.
Data-quality expectation definitions.
Technical documentation.
Reconciliation queries.
Test cases.
Phase 4: Approval and observability
Track:
Who approved the metadata.
Which version generated the pipeline.
Which generated artifact was deployed.
Which quality expectations failed.
Which source-to-target definitions changed.
This sequence creates value before attempting a fully autonomous delivery system.
Final Thought
Modern data platforms have made execution, scaling, orchestration, and governance far more accessible.
But many delivery teams still lose time translating the same business logic across mappings, code, tests, quality checks, and documentation.
A governed metadata-to-pipeline pattern addresses that gap.
The goal is not to remove data engineers.
The goal is to reduce repeated interpretation work, make delivery more consistent, and allow engineers to focus on the work that requires judgment:
The future of data engineering is not just code generation.
It is intent-driven, reviewable, and governed delivery.
About the Author
Amit Kumar Singh is a data engineering leader focused on metadata-driven delivery, data quality, governance, and AI-assisted engineering workflows.
He is building Data Engineering Copilot, an independent project exploring how structured business and technical metadata can support more consistent, reviewable data delivery.
Learn more: DataEngineeringCopilot.com
https://www.linkedin.com/in/amit-singh-57980030/
Amit Kumar Singh
Lead Data Engineer | AI-Assisted Data Engineering
