Out of everything that it was, Arghyam holds a special place in my professional journey as it was the very first project I worked on after joining Project Tech4Dev. Starting at a new place always comes with its own mix of emotions—excitement, nervousness, curiosity, and a strong drive to prove oneself. Arghyam provided the perfect platform for me to channel these emotions into something productive. The project wasn’t just a task to complete but an opportunity to immerse myself in tools, workflows, and concepts that I had only read about before. If you were to ask me what my favorite thing about this project was, I’d say without hesitation: the immense learning curve it offered.
Working on Arghyam was an intensive learning experience in data engineering, analytics, and visualization. I had the opportunity to deeply explore and apply tools like dbt (Data Build Tool), Apache Superset, and the Dalgo platform, while also enhancing my SQL proficiency significantly. Beyond the technical aspects, I gained a nuanced understanding of the social sector’s operational dynamics, focusing on workflows that directly benefit individuals in need.
Anurakshaks and Their Role
In Arghyam’s case, this was particularly reflected in the support provided to Anurakshaks, the field operators responsible for managing the financial aspects of water connections within their designated areas. Their responsibilities include tracking consumer details, billing records, and payment histories for these connections. Additionally, the system equips Anurakshaks to oversee operational and maintenance expenses related to piped water supply systems, as well as tank cleaning, ensuring sustainability and efficiency.
Beyond financial management, Anurakshaks also monitor community engagement activities, such as the number of Jal Chaupal meetings (community gatherings to address water supply issues) and Ward Implementation and Management Committee (WIMC) meetings. They assess and report on the participation rates in these gatherings, fostering greater community involvement in water management initiatives.
Digging into my contribution to the project
Taking a breadth-first approach to explain my contributions to the project, I’ll first outline the comprehensive insights provided by the Superset dashboard for Arghyam. These include:
- Water Availability Analytics: Tracking the number of days with water availability and unavailability in each village.
- Water Quality Monitoring: Analyzing the frequency of water quality testing and the outcomes of those tests.
- Tank Maintenance: Recording the number of instances tank cleaning operations were performed.
- Community Engagement Metrics:
- Frequency of Jal Chaupal (community discussions on water issues) and Ward Implementation and Management Committee (WIMC) meetings.
- Participation metrics segmented by male and female attendees in these meetings.
- Financial Analytics:
- Monthly demand generation and payment collection trends for water connections.
- Monthly expenditure tracking for villages on water supply systems.
- Monitoring advance payments and arrears at both monthly and cumulative levels.
- Performance Index: An aggregate index evaluating the overall performance across all the above parameters to provide a holistic view of water management efficiency and community engagement.
My work began with designing and building dbt models to create tables containing direct, pre-processed data for all the parameters mentioned above. These tables were designed to serve as the foundation for creating specific visualizations in Superset.
The dbt models involved creating transformations by joining multiple columns across various source tables while applying specific conditions to ensure data accuracy and relevance.
The Demand Collection dbt model is designed to aggregate and consolidate payment and demand data for water connections. Key features include:
Overview of dbt Models and Their Integration with Superset
1. Demand Collection Model
- Data Integration:
- Combines data from source tables: payment details and demand details.
- Utilizes a full outer join on consumercode and tenantid to ensure complete data coverage across records.
- Tenant Identification:
- Uses tenantid as a unique identifier, generated by concatenating district name, block name, gp name (Gram Panchayat), ward name, and ward code, ensuring accurate location-based data tracking.
- Metadata Enrichment:
- Links additional fields, such as connection status and usernames, via LEFT JOINs with relevant tables.
- Output:
- Produces a materialized table containing financial details (payments and dues) and metadata for analytics. The model is materialized as a table in a specific schema, ensuring persistent storage for downstream use.
The Expenditure dbt model focuses on tenant-level financial data aggregation. Key details include:
2. Expenditure Model
- Data Integration:
- Combines data from the demand collection table (created via dbt) and the source tenant expenses table.
- Merges records using a full outer join on tenantid and date.
- Output:
- Generates a materialized table with aggregated totals for expenditures, amounts collected (paid), and tenant-associated usernames.
3. Performance Index Model
The Performance Index dbt model calculates a composite score to evaluate user-level water management performance. Features include:
- Key Metrics:
- Water Availability: % of days with water and availability scores.
- Water Quality: Recency of water quality testing.
- Community Engagement: Attendance at WIMC and Jal Chaupal meetings.
- Financial Contributions: Tariff collection against target dues.
- Score Calculation:
- Computes a total_score by aggregating individual scores for each metric.
- Output:
- Produces a materialized table with user-specific scores and detailed data on water days, testing, tank cleaning, and community meetings, etc.
For Integration with Superset for Visualization –
-
- Direct Source Tables:
- For metrics or analytics that can be derived directly from source tables, these are used in Superset to reduce transformation overhead.
- Aggrgated Tables:
- When specific and pre-processed data is needed for visualization (e.g., composite scores or financial summaries), dbt-created tables like demand_collection, expenditure, and performance_index are utilized.
- Chart Types Used:
- Big Numbers: Used when numerical answers or summary statistics were required, such as total payments, expenditures, or number of Jal Chaupal meetings.
- Bar Charts: Utilized to visualize distributions or comparisons, such as attendance in WIMC/jal chaupal meetings or tariff collection percentages.
- Direct Source Tables:
A view of the tab for “Payments and Demand” from the dashboard
Additional Enhancements for Improved Dashboards:
- Localized Titles: All dashboard titles, tabs, and chart labels were presented in Hindi to ensure accessibility and usability for Anurakshaks, many of whom are more comfortable with Hindi.
- Time Range Filter: A 6-month time range was applied to all charts, focusing on recent data and insights for more specific and relevant observations.
Challenges Faced
- Data Inconsistencies: Frequent confusion arose due to the way data was collected and updated, leading to discrepancies in the outputs. This required iterative refinements and the application of specific conditions to the dbt models and chart configurations to align with the desired results.
- Data Gaps: In some cases, significant portions of data were missing for certain usernames (linked to specific Anurakshaks), while others had an abundance of data. This created initial doubts about the correctness of the tables and required thorough investigation and adjustments to ensure data accuracy.
- Problem-Solving: Several other issues surfaced during the process, but with systematic approaches, debugging, and excellent guidance from supervisors, I was able to address and resolve them effectively.
To Conclude it all out
Overcoming these challenges not only improved the quality and accuracy of the dashboards but also enhanced my understanding of data engineering workflows. This experience taught me the importance of adaptability, attention to detail, and collaboration in solving complex problems, all of which were key to the successful completion of this project.
Additional resources are mentioned here for any further queries:
