databricks-data-engineering

Use watermark-based incremental loads that track the maximum processed timestamp, then apply MERGE operations to upsert new and changed records into Delta tables. Auto Loader with checkpointing handles file-based sources with exactly-once guarantees.

Implement SCD Type 2 with a two-step MERGE: first expire current records by setting is_current to false and an end date, then insert new versions with effective start dates. Use surrogate keys for fact table joins and query current state with the is_current flag.

Liquid clustering is the recommended default over legacy partitioning for Delta tables on Databricks. It avoids small-partition problems, adapts to query patterns automatically, and can be changed with ALTER TABLE without rewriting data.

Yes, Auto Loader supports automatic schema inference and evolution when reading files from cloud storage. Provide schema hints for critical columns and set a schema location so new columns are tracked safely across runs.

Slow Delta queries usually stem from small files, missing clustering, or data skew. Run OPTIMIZE to compact files, enable liquid clustering on filter columns, and check AQE and Photon settings for join and aggregation performance.

Validate records in the Silver layer and route failures to a quarantine table instead of dropping them. Track validation errors in an array column and reconcile row counts between source, target, and quarantine to detect silent data loss.