1 / 28

Defensive ETL

The World’s Largest Community of SQL Server Professionals. Defensive ETL. Tim Mitchell Artis Consulting. Session Objectives. What is Defensive ETL? Review threats and challenges Discuss countermeasures in SSIS. Tim Mitchell. Business Intelligence Consultant – Artis Consulting, Dallas TX

briar
Download Presentation

Defensive ETL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. The World’s Largest Communityof SQL Server Professionals Defensive ETL Tim Mitchell Artis Consulting

  2. Session Objectives What is Defensive ETL? Review threats and challenges Discuss countermeasures in SSIS The World’s Largest Community of SQL Server Professionals

  3. Tim Mitchell Business Intelligence Consultant – Artis Consulting, Dallas TX Contributing author, MVP Deep Dives 2 Coauthor, SSIS Design Patterns SQL Server MVP TimMitchell.net | Twitter.com/Tim_Mitchell The World’s Largest Community of SQL Server Professionals

  4. Housekeeping Questions Surveys The World’s Largest Community of SQL Server Professionals

  5. Common Data Challenges • Accidental • Data corruption • Data loss • Transactional inconsistency • Malicious • Data theft • SQL injection The World’s Largest Community of SQL Server Professionals

  6. Common Data Challenges • System • Hardware/software failures unrelated to the operation of the ETL • Unexpected downtime • Resource contention The World’s Largest Community of SQL Server Professionals

  7. ETL Challenges • Data sources/destination challenges • Inconsistent types • Inconsistent lengths • Unreliable connections • Just plain bad data The World’s Largest Community of SQL Server Professionals

  8. ETL Challenges • Transformation challenges • Business logic errors • Generated duplicates • “Lost” data • Time allotment overrun The World’s Largest Community of SQL Server Professionals

  9. What Is Defensive ETL? Two universal truths: • I’m doing it right. • Everybody else is crazy. The World’s Largest Community of SQL Server Professionals

  10. What Is Defensive ETL? Assumptions of a Defensive ETL professional: • Any input/output over which I do not have complete control cannot be trusted. • Any process that relies on either hardware or software cannot be trusted. • Murphy’s Law is inescapable The World’s Largest Community of SQL Server Professionals

  11. Defensive ETL in SSIS Multifaceted approach: • Package • Sources/destinations • Transformations • Error/event handling • Restartability • Validation The World’s Largest Community of SQL Server Professionals

  12. Defensive ETL in SSIS Package-Level Defenses The World’s Largest Community of SQL Server Professionals

  13. Defensive ETL in SSIS Transactions • Package, container, and/or task The World’s Largest Community of SQL Server Professionals

  14. Defensive ETL in SSIS Checkpoints • Task or container level restart • Avoid use with transactions The World’s Largest Community of SQL Server Professionals

  15. Defensive ETL in SSIS Logging The World’s Largest Community of SQL Server Professionals

  16. Defensive ETL in SSIS ETL Controller ETL Framework • Structured approach • Shared logging and error handling • Restartability The World’s Largest Community of SQL Server Professionals

  17. Defensive ETL in SSIS Event Handling The World’s Largest Community of SQL Server Professionals

  18. Defensive ETL in SSIS Control Flow Defenses The World’s Largest Community of SQL Server Professionals

  19. Defensive ETL in SSIS Precedence Constraints • Alternate paths The World’s Largest Community of SQL Server Professionals

  20. Defensive ETL in SSIS Script Task • Wait/pause • Retry The World’s Largest Community of SQL Server Professionals

  21. Defensive ETL in SSIS Data Flow Defenses The World’s Largest Community of SQL Server Professionals

  22. Defensive ETL in SSIS Connections • Metadata is configurable for some connection types The World’s Largest Community of SQL Server Professionals

  23. Defensive ETL in SSIS Data Sources • Error or Truncation • Column by column The World’s Largest Community of SQL Server Professionals

  24. Defensive ETL in SSIS Inline Cleansing • Conditional Split • Derived Column transform The World’s Largest Community of SQL Server Professionals

  25. Defensive strategies • Incoming data • Inline cleansing • Post-ETL cleansing • Third-party cleansing • DQS in SQL Server Denali The World’s Largest Community of SQL Server Professionals

  26. Defensive strategies • Incoming data • Data type/length compensation • Loops/retry The World’s Largest Community of SQL Server Professionals

  27. Defensive strategies • Transformations • Error/lookup outputs • Branching • Rowcount validations The World’s Largest Community of SQL Server Professionals

  28. The World’s Largest Communityof SQL Server Professionals Thanks for AttendingVisit www.sqlservercentral.com for free SQL ServereBooks, articles, videos, blogs, news, and more. Please Don’t Forget to Turn in Your Evaluations

More Related