agency-data-engineer

Use Delta Lake merge operations to upsert cleansed records into Silver tables based on primary keys, keeping only the latest record per key via window functions. Append raw data to Bronze with mergeSchema enabled, then aggregate into Gold with partition-based overwrite for efficient incremental refreshes.

Bronze stores raw, immutable, append-only data with ingestion metadata. Silver holds cleansed, deduplicated, conformed records that are joinable across domains. Gold contains business-ready aggregations optimized for query patterns and backed by freshness SLAs.

Define model contracts with enforced column types and constraints in schema.yml, then add tests like not_null, unique, relationships, and dbt_expectations range checks. Use dbt_utils.recency tests to alert when data freshness exceeds the expected interval.

Yes, read from Kafka with the kafka source format, parse JSON payloads with from_json and an explicit schema, then write to Delta with append mode, a checkpoint location, and a processing-time trigger. This provides fault-tolerant micro-batch ingestion with schema evolution.

Idempotent pipelines produce the same result when rerun, preventing duplicate records after retries or failures. Achieve this with merge/upsert logic instead of blind appends, deduplication on primary keys, and partition-level overwrites rather than full-table reloads.

Use micro-batch triggers (e.g., 30-second intervals) for most pipelines since they balance latency and compute cost. Reserve continuous streaming for sub-second latency requirements, as it costs significantly more and complicates exactly-once semantics and late-data handling.