A previous article discussed the ETL process and its evolution with the recent paradigms followed by a discussion on the role of an orchestrator in data engineering. This section focuses on pipeline issues and troubleshooting. The return on investment in data engineering projects is often reduced by how fragile the system becomes and the maintenance it requires. Systems do fail but planning for failure means making it easier to maintain and extend, providing automation for handling errors and learning from experience. The minimum viable product principle and the 80/20 principle are time-honored traditions.

Direct and indirect costs of ETL systems are significant, as they can lead to inefficient operations, long run times, and high bills from providers. Indirect costs, such as constant triaging and data failure, can be even more significant. Teams that win build efficient systems that allow them to focus on feature development and data democratization. SaaS (Software as a Service) offers a cost-effective solution, but it can also lead to loss of trust, revenue, and reputation. To minimize these costs, focus on maintainability, data quality, error handling, and improved workflows. Monitoring and benchmarking are essential for minimizing pipeline issues and expediting troubleshooting efforts. Proper monitoring and alerting can help improve maintainability of data systems and lower costs associated with broken data. Observing data across ingestion, transformation, and storage, handling errors as they arise, and alerting the team when things break, is crucial for ensuring good business decisions.

Data reliability and usefulness are assessed using metrics such as freshness, volume, and quality. Freshness measures the timeliness and relevance of data, ensuring accurate and recent information for analytics, decision-making, and other data-driven processes. Common metrics include the length between the most recent timestamp and the current timestamp, lag between source data and the dataset, refresh rate, and latency. Volume refers to the amount of data needed for processing, storage, and management within a system. Quality involves ensuring data is accurate, consistent, and reliable throughout its lifecycle. Examples of data quality metrics include uniqueness, completeness, and validity.

Monitoring involves detecting errors in a timely fashion and implementing strict measures to improve data quality. Techniques to improve data quality include logging and monitoring, lineage, and visual representations of pipelines and systems. Lineage should be complete and granular, allowing for better insight and efficiency in triaging errors and improving productivity. Overall, implementing these metrics helps ensure data quality and reliability within an organization.

Anomaly detection systems analyze time series data to make statistical forecasts within a certain confidence interval. They can catch errors that might originate outside the systems, such as a bug in a payments processing team that decreases purchases. Data diffs are systems that report on data changes presented by changes in code, ensuring accurate systems remain accurate especially when used as an indicator on data quality. Tools like Datafold and SQLMesh have data diffing functionality. Assertions are constraints put on data outputs to validate source data. They are simpler than anomaly detection and can be found in libraries like Great Expectations aka GX Expectations or systems with built-in assertion definitions.

Error handling is crucial for data systems and recovery from their effects, such as lost data or downtime. Error handling involves automating error responses or boundary conditions to keep data systems functioning or alert the team in a timely and discreet manner. Approaches include conditional logic, retry mechanisms, and pipeline decomposition. These methods help keep the impact of errors contained and ensure the smooth functioning of data systems.

Graceful degradation and error isolation are essential for maintaining limited functionality even when a part of a system fails. Error isolation is enabled through pipeline decomposition, which allows systems to fail in a contained manner. Graceful degradation maintains limited functionality even when a part of the system fails, allowing only one part of the business to notice an error.

Alerting should be a last line of defense, as receiving alerts is reactive. Isolating errors and building systems that degrade gracefully can reduce alarm fatigue and create a good developer experience for the team.

Recovery systems should be built for disasters, including lost data. Staged data, such as Parquet-based formats like Delta Lake and patterns like the medallion architecture, can be used for disaster recovery. Backfilling, the practice of simulating historical runs of a pipeline to create a complete dataset, can save time when something breaks.

Improving workflows is crucial in data engineering, as it is an inherently collaborative job. Data engineering is a question of when things break, not if. Starting with systems that prioritize troubleshooting, adaptability, and recovery can reduce headaches down the line.

In the context of software teams, understanding their motivations and workflows is crucial for fostering healthy relationships and improving efficiency. By focusing on the team's goals and understanding their workflows, you can craft a process to improve efficiency.

Structured, pragmatic approaches can ensure healthy relationships through Service-Level Agreements (SLAs), data contracts, APIs, compassion and empathy, and aligning incentives. SLAs can be used to define performance metrics, responsibilities, response and resolution times, and escalation procedures, improving the quality of data that is outside of your control. Data contracts, popularized by dbt, govern data ingested from external sources, providing a layer of standardization and consistency. APIs can be used to transmit an expected set of data, providing granular access control, scalability benefits, and versioning, which can be useful for compliance.

Compassion and empathy are essential in engineering and psychology, as understanding coworkers' motivations, pain points, and workflows allows for effective communication and appeal to their incentives. In the digital age, it's essential to go the extra mile to understand coworkers and appeal to their incentives.

Setting key performance indicators (KPIs) around common incident management metrics can help justify the time and energy required to do the job right. These metrics include the number of incidents, time to detection (TTD), time to resolution (TTR), and data downtime (N × [TTD + TTR].

Continually iterating and adjusting processes in the wake of failures and enhancing good pipelines to become great are some ways to improve outcomes. Documentation is crucial for understanding how to fix errors and improve the quality of data pipelines. Postmortems are valuable for analyzing failures and learning from them, leading to fewer events that require recovery. Unit tests are essential for validating small pieces of code and ensuring they produce desired results. Continuous integration/continuous deployment (CI/CD) is a preventative practice to minimize future errors and ensure a consistent code base.

Engineers should simplify and abstract complex code to improve collaboration and reduce errors. Building data systems as code, which can be rolled back and reverted to previous states, can improve observability, disaster recovery, and collaboration. Tools that are difficult or impossible to version control or manipulate through code should be exercised with caution. With responsibilities defined, incentives aligned, and a monitoring/troubleshooting toolkit, engineers can automate and optimize data workflows. Balancing automation and practicality is essential in data engineering, ensuring robust, resilient systems ready for scaling.