Traditional ETL meant writing separate pipelines for each table or source system. With hundreds of tables across multiple enterprise systems, this wasn't scalable. Every new table required code changes, testing, and deployment cycles taking weeks.

The metadata-driven approach eliminates this. Behavior is controlled through configuration, not code. To onboard a new table, we insert a row in the metadata table—no code deployment needed. This reduced our development effort by 70% and cut onboarding time from weeks to hours.

More importantly, it ensures consistency. All tables follow the same transformation patterns, error handling, and audit mechanisms. When we need to update logic (like adding a new validation rule), we change it once in the reusable functions, and all tables benefit automatically. This is the power of metadata-driven engineering.

The load type is defined in metadata, and the framework adapts its behavior accordingly:

Full Load: ADF extracts all data from source, Databricks overwrites the target dataset completely. Used for small reference tables or when complete refresh is needed. Simple and fast for small datasets.

Incremental Load: Most common pattern. Metadata defines a watermark column (like last_updated_ts). ADF reads the last successful watermark from control tables, Databricks queries source for records where watermark > last_watermark, then merges new/updated records based on primary keys. After success, watermark is updated. This handles high-volume, frequently changing tables efficiently.

Snapshot Load: Extracts point-in-time data and appends with effective dates. Metadata defines snapshot frequency and effective date columns. Useful for audit trails or historical state preservation. Each snapshot is preserved, allowing time-travel queries.

The key is that the same PySpark transformation functions handle all three types—they just receive different parameters from metadata (load_type, watermark_column, merge_strategy, etc.).

ADF uses a metadata-driven pipeline pattern. Here's the execution flow:

1. Metadata Read: ADF pipeline starts by querying the metadata repository to get all tables for a source system, along with their load types, dependencies, and configurations.

2. Dependency Resolution: ADF builds a dependency graph from metadata. If Table B depends on Table A, ADF ensures Table A completes successfully before triggering Table B. This is critical for maintaining data integrity.

3. Parallel Execution: Tables without dependencies run in parallel. ADF triggers multiple Databricks notebooks simultaneously, each with table-specific parameters (source path, target path, watermark value, merge strategy, etc.).

4. Parameter Passing: Each Databricks notebook receives parameters like source_table, target_path, load_type, watermark_column, primary_keys. The notebook reads these and executes the appropriate transformation logic.

5. Status Tracking: After each job, ADF updates audit tables with status, row counts, and timestamps. If a job fails, ADF can retry based on retry policies, and dependent jobs wait until parent succeeds.

The PySpark transformations are reusable functions parameterized by metadata:

Schema Alignment: Metadata defines source column names and their mappings to target columns. PySpark dynamically renames columns, handles case sensitivity differences, and maps source data types to target data types (e.g., VARCHAR to STRING, DATETIME to TIMESTAMP).

Data Type Standardization: We have transformation functions that convert data types consistently. For example, all dates are normalized to UTC timestamps, numeric types are standardized (INT vs BIGINT based on metadata), and string types are trimmed and validated.

Deduplication: Based on primary keys defined in metadata, we identify duplicates using window functions partitioned by business keys. For incremental loads, we keep the latest record; for full loads, we remove duplicates before writing.

Data Quality Checks: Validation rules are defined in metadata (nullable columns, value ranges, format checks). PySpark applies these validations and flags records that fail. Failed records are written to a separate error table with error details, while valid records proceed to the target. This ensures data quality without stopping the entire load.

We implemented multiple optimization strategies:

File Compaction: Small files in ADLS cause excessive I/O. We implemented a compaction step that merges files into optimal sizes (128MB-256MB) using coalesce() or repartition(). This reduced read operations by 80% for some tables.

Partitioning Strategy: Metadata defines partition columns (typically date or business keys). We partition data during writes, which dramatically improves query performance. For incremental loads, we only process partitions that changed, not the entire dataset.

Broadcast Joins: For small lookup tables, we use broadcast joins to avoid shuffles. We identify these in metadata and apply broadcast hints automatically.

Pre-partitioning for Merges: Before merge operations, we pre-partition both source and target data by the same key (primary key or business key). This ensures joins happen locally without shuffles, reducing network I/O significantly.

Cluster Sizing: Metadata includes table size estimates. We dynamically size Databricks clusters based on this—small tables use smaller clusters, large tables get more workers. This optimizes cost and performance.

Error handling operates at multiple levels:

Orchestration Level (ADF): ADF has configurable retry policies with exponential backoff. If a Databricks job fails, ADF retries up to 3 times with increasing delays. We also implemented dependency checking—if a parent table fails, dependent tables don't run, preventing partial loads that could cause data inconsistency.

Transformation Level (Databricks): Before processing, we validate watermark consistency (ensuring source max timestamp > last watermark), schema alignment, and data quality. If validation fails, we log detailed errors to audit tables but don't fail the entire job—we write valid records and flag invalid ones separately.

Atomic Operations: For incremental merges, we use transactional writes. We read the target, merge with new data, validate row counts, and only commit if validation passes. If anything fails, we roll back to the previous state. This ensures data consistency even if a job fails mid-execution.

Audit Trail: Every error is logged with context—which table, which date range, error message, row counts before/after. This allows us to identify patterns, fix root causes, and reprocess specific date ranges without affecting other loads.

Backdated processing was a critical requirement. Here's how we handle it:

Watermark Override: Metadata allows manual watermark overrides. If we need to reprocess data from a specific date, we update the watermark value in metadata to that date. The next run will extract all data from that date forward, effectively reprocessing historical data.

Audit Tracking: When backdated processing runs, audit tables clearly mark it as a "reprocess" run with the original date range and the reprocess timestamp. This prevents confusion about which data is current vs. reprocessed.

Isolation: Backdated processing doesn't affect current production loads. Current loads continue using their normal watermark values, while backdated runs use overridden values. Both can run in parallel if needed, writing to the same target but with different effective dates or partitions.

Data Integrity: For incremental loads, we handle backdated updates carefully. If a record was updated in the past, we merge it correctly based on effective dates, ensuring the final state reflects all changes chronologically. This is especially important for snapshot loads where we preserve historical states.

The audit framework is comprehensive and mandatory for every run:

Row Count Metrics: Source row count (how many records extracted), target row count before (existing records), target row count after (final state), and difference (inserts/updates/deletes). This validates data completeness and identifies data loss or duplication issues.

Timing Metrics: Load start timestamp, end timestamp, and duration. This helps identify performance degradation, bottlenecks, and plan capacity. We also track time per million rows for normalization.

Status Tracking: SUCCESS, FAILURE, or PARTIAL_SUCCESS with detailed error messages. For partial success, we track which partitions or date ranges succeeded vs. failed, allowing targeted reprocessing.

Data Quality Metrics: Number of records that failed validation, number of duplicates found, schema mismatches detected. This helps identify data quality issues at the source.

Reprocessing Flags: Clear indicators for backdated processing—original date range, reprocess timestamp, and reason for reprocessing. This maintains a clear audit trail for compliance and troubleshooting.

All metrics are stored in SQL Server audit tables, queryable for reporting, monitoring dashboards, and compliance requirements.

Schema evolution is handled through metadata updates and flexible PySpark logic:

Metadata-Driven Schema Mapping: When source schema changes, we update the metadata table with new column mappings. The PySpark logic reads these mappings dynamically, so no code changes are needed. New columns are added to the target, removed columns are handled gracefully (either dropped or preserved based on metadata flags).

Schema Validation: Before processing, we validate that source schema matches metadata expectations. If there's a mismatch (new column not in metadata, or expected column missing), we log a warning but don't fail the job. This allows us to detect schema changes and update metadata accordingly.

Backward Compatibility: For incremental loads, we handle schema changes carefully. If a new column appears, we add it to existing records with null values. If a column is removed, we preserve it in target with nulls or default values, ensuring downstream systems aren't broken.

Versioning: Metadata includes schema version numbers. When schema changes, we increment the version, allowing us to track which runs used which schema version. This is critical for troubleshooting and understanding data lineage.

Incremental loads use watermark columns (typically last_updated_ts) defined in metadata. The process is:

Standard Flow: ADF reads last successful watermark from control tables, Databricks queries source for records where watermark > last_watermark, merges new/updated records, then updates watermark to source max value.

Edge Cases Handled:
• Timezone Differences: Source and target may be in different timezones. We normalize all timestamps to UTC before comparison, ensuring no records are missed due to timezone mismatches.

• Clock Skew: Source system clocks may be slightly off. We add a small buffer (e.g., 1 minute) when querying, ensuring we don't miss records that were updated just before the watermark timestamp.

• Null Watermarks: Some records may have null watermark values. We handle these separately—either exclude them, use a default date, or flag them for manual review based on metadata configuration.

• Watermark Column Updates: If the watermark column itself is updated (e.g., a record is backdated), we ensure we still capture it. We use ">=" instead of ">" and handle duplicates in merge logic.

• Large Gaps: If there's a large gap between last watermark and current time (e.g., system was down for days), we can process in chunks to avoid memory issues, or metadata can flag it for full reload instead.

The business impact was significant and measurable:

Development Efficiency: Reduced development effort by 70%. What used to take 2-3 weeks to onboard a new table (requirements, design, coding, testing, deployment) now takes 2-3 hours (just adding metadata rows). We onboarded 200+ tables in 6 months vs. the estimated 2+ years with traditional approach.

Operational Cost Reduction: Single framework means single set of operational procedures, monitoring, and support. Instead of maintaining hundreds of individual pipelines, we maintain one framework. This reduced operational overhead by 60% and freed up team capacity for new projects.

Time to Market: New data sources can be onboarded in hours, enabling faster analytics and business insights. Business teams can request new data sources and have them available within days, not months.

Data Quality: Consistent transformation logic and validation rules across all tables improved data quality. Audit framework provides transparency, building trust with business users.

Scalability: Framework handles hundreds of tables without performance degradation. As business grows, we can add more tables without proportional increase in development or operational costs.

Success Metrics: We track tables onboarded per month, average onboarding time, job success rate (target: >99%), data freshness (time from source update to analytics availability), and developer productivity (tables per developer per month). All metrics showed dramatic improvements.