Project - Sales DWH
PDI Lifecycle Management ..
Workshop - Sales DWH
This comprehensive workshop guides you through setting up a professional-grade Sales Data Warehouse project using Pentaho Data Integration (PDI). You'll learn not just HOW to build ETL solutions, but WHY certain architectural decisions matter for long-term success.
What You'll Accomplish:
By the end of this workshop, you will be able to:
Set up a structured PDI project with proper governance
Integrate PDI with Git for version control
Implement environment-agnostic configurations
Build a reusable DI framework for logging, monitoring, and error handling
Apply enterprise-grade best practices to your ETL projects
Deploy solutions across multiple environments
Target Audience:
ETL/Data Integration Developers
Data Engineers transitioning to Pentaho
Technical Leads planning large-scale DI projects
DevOps engineers supporting PDI deployments
Estimated Time:
Full Workshop: 2 days (16 hours)
Core Modules: 1 day (8 hours)
Set the scene ..
Imagine you've been tasked with building a Sales Data Warehouse that needs to integrate data from multiple source systems like your CRM, ERP, and various flat files. This isn't a small project - you'll have a team of 5-8 developers working together, and the solution needs to work seamlessly across multiple environments: Development, Test, UAT, and Production.
The warehouse will require frequent updates and deployments as business requirements evolve, and it must include comprehensive logging and error recovery mechanisms. Most importantly, this system needs to remain maintainable for years to come, surviving team changes and organizational growth.
Without proper project setup and governance from the beginning, even well-intentioned projects quickly run into serious problems. These aren't theoretical issues - they're pain points that plague countless ETL projects in production today. Let's examine what goes wrong and why establishing the right foundation from day one is critical to long-term success.
x
x
x
Common Pitfalls That Derail Projects
The first major problem teams encounter is what's known as the "Works on My Machine" syndrome. This happens when developers hard-code paths directly into their transformations, such as writing C:\Users\John\Documents\ETL\sales_data.csv as the file location. Everything works perfectly on John's laptop, but when another developer checks out the code or when the job needs to run on a server, it fails immediately because that exact path doesn't exist. This problem multiplies across teams, with each developer maintaining their own slightly different version of the same transformations, making collaboration nearly impossible.
Configuration chaos represents another critical failure point. In many projects, database passwords end up stored directly in transformation files, visible to anyone who can access the code repository. Connection strings are copied and pasted into every job that needs database access, which means updating a password or server name requires hunting through potentially hundreds of files. When it's time to deploy to a new environment, teams realize they have production connection details scattered throughout development code, creating both security vulnerabilities and deployment nightmares. The result is either massive search-and-replace operations before each deployment or, worse, accidental connections to production databases from test environments.
Version control disasters occur when teams fail to properly integrate their ETL code with systems like Git. Without version control, there's no reliable backup of previous working versions. When something breaks, there's no way to compare the current code with what was working last week to identify what changed. Multiple developers end up overwriting each other's work, or competing versions of the same transformation exist in different locations with no clear indicator of which is the "real" one. Teams waste days recovering lost work or attempting to merge conflicting changes manually, and the project timeline slips while morale suffers.
Deployment headaches emerge when the only deployment process is manually copying files to production servers. Inevitably, someone forgets to copy a critical SQL script, or a configuration file gets left behind. Dependencies aren't documented, so the operations team doesn't know that the new customer dimension load requires a specific shell script or lookup table. Deployments that should take minutes stretch into hours of troubleshooting, often during planned downtime windows. Rollbacks become exercises in archaeology as teams try to remember exactly which files changed and which versions need to be restored.
Finally, operational blind spots leave teams flying blind in production. Jobs fail silently with no notifications sent to operations staff. When data quality issues are discovered weeks later, there's no audit trail showing what ran, when it ran, or why it failed. Root cause analysis becomes guesswork because there's no detailed logging of which records were processed, which were rejected, and what errors occurred. The business loses confidence in the data warehouse, and the technical team spends more time firefighting than building new features.
x
Key Concepts
Understanding these four core concepts will transform how you build and maintain ETL solutions. Each concept addresses specific pain points we've just discussed while enabling professional-grade data integration practices.
Separation of Content and Configuration is the principle of keeping your ETL logic completely separate from environment-specific settings. Your transformation should contain the data transformation rules, not hard-coded database connection strings or file paths. Instead, use variables like ${DB_CONNECTION} or ${SOURCE_FILE_PATH} throughout your code. These variables get their actual values from configuration files that exist outside your ETL code - one configuration file per environment.
When the same transformation runs in development, it reads the dev configuration file and connects to the development database at localhost:3306/sales_dev.
When that exact same transformation runs in production, it reads the production configuration file and connects to prod-db-01:3306/sales. You never modify the transformation itself when moving between environments.
This approach eliminates the "works on my machine" problem entirely, makes deployments straightforward, and allows you to update connection details in one place rather than hunting through hundreds of files.
Version Control Integration means storing all your ETL artifacts- jobs, transformations, SQL scripts, documentation—in a Git repository just like software development teams do with application code. Every change is tracked with information about who made the change, when they made it, and why.
You can compare the current version of any transformation with previous versions to see exactly what changed. If a job that was working yesterday suddenly fails today, you can quickly identify that someone modified the customer validation logic this morning and review those specific changes.
Teams can work in parallel using feature branches without stepping on each other's toes, and you can tag specific versions as releases for deployment to production. When something breaks in production, you can roll back to the previous known-good version in minutes. This eliminates version control disasters and provides the foundation for professional deployment practices.
The Framework Pattern provides a reusable layer of infrastructure code that wraps around your business logic. Think of it like the framework that web developers use - Django developers don't write user authentication code for every project; the framework provides it. Similarly, your ETL developers shouldn't write logging code, error handling, and job control logic in every transformation.
Instead, you build a framework once that provides these services, and all your ETL jobs use this framework. When a job runs, it doesn't execute directly; instead, a launcher job loads the configuration, initializes logging by creating a record in the job_control table, executes your business logic, handles any errors that occur, updates the job completion status, and sends notifications if configured.
Your developers write only the transformations that implement business rules—loading customers, calculating metrics, or updating dimensions. The framework handles everything else. This means consistent behavior across all jobs, centralized improvements (fix a logging bug once and all jobs benefit), and developers who can focus on solving business problems rather than wrestling with infrastructure.
Job Restartability enables failed jobs to resume from their point of failure rather than starting over from scratch. Consider a staging job that loads ten large tables sequentially. Everything runs perfectly through the first six tables - customer, product, store, time, promotion, and sales header have all loaded successfully, taking about thirty minutes total. Then the network hiccups, and the sales detail table fails after loading five million of ten million records. Without restartability, you fix the network issue and re-run the entire job, wasting thirty minutes reloading those six tables that already succeeded. With restartability, the framework tracks which steps completed successfully in the job_control and step_control tables. When you restart the job, it checks which tables already finished, skips them entirely, and resumes execution starting with the sales detail table that failed. The restart takes five minutes instead of thirty-five. This pattern saves enormous amounts of time when dealing with large data volumes, reduces the impact of transient failures like network timeouts, and makes your batch processing windows much more predictable. The step_control table shows you exactly what succeeded and what failed, making troubleshooting straightforward rather than mysterious.
These four concepts work together synergistically. Configuration separation enables the same code to run reliably in any environment. Version control tracks all changes and enables professional deployment practices. The framework provides consistent infrastructure services without cluttering business logic. Restartability minimizes the cost of failures, which inevitably occur in complex data integration scenarios. Together, they transform ETL development from an ad-hoc, error-prone process into a professional engineering discipline with predictable outcomes and maintainable solutions.
x
x
x
x
x
Last updated
Was this helpful?