Enhancing DWH Announcement Management: A Targeted Approach
Hello, I’m Tugay Nehir, currently leading as a data warehouse developer in the Data Management tribe at Trendyol. Together with my colleagues, Aylin Öndersev and Fikri Cem Yılmaz from the data warehouse team, we’re excited to share insights into our latest venture — the development of a dwh announcement management . This article aims to unfold the reason behind its creation and the transformative impact it has on our data-driven strategies, illustrating our journey in enhancing Trendyol’s analytical prowess in the competitive landscape of e-commerce.
In the ever-evolving landscape of data warehousing, effective communication with end-users is crucial. The Data Warehouse (DWH) team has developed a sophisticated solution for managing announcements, ensuring that the right information reaches the right users at the right time. This post delves into the goals, architecture, and the pros and cons of the new solution compared to an alternative approach using Google Colab.
Project Goals
The DWH Announcement Management project is strategically designed to achieve the following primary objectives:
Efficient Delivery of Informative Messages:
- Objective: Streamline the process of delivering timely and relevant messages to target users and teams.
- Reason: Enhance communication efficiency, ensuring that critical information reaches both individual users and collaborative teams promptly.
Awareness-Raising Notifications:
- Objective: Create notifications that effectively raise awareness among both users and teams.
- Reason: Foster a proactive and informed user community by delivering announcements that capture attention and convey essential information to individuals and groups.
Targeted Tagging for Teams:
- Objective: Implement a tagging system to direct announcements specified teams.
- Reason: Ensure announcements are precisely targeted, reaching relevant teams based on their roles, responsibilities, and collaborative workflows.
Private Messages for User Engagement:
- Objective: Send private messages to users identified through table tracking.
- Reason: Facilitate personalized communication, fostering users, and providing a direct channel for clarifications or additional information.
Prompt Notification of Table Changes:
- Objective: Promptly notify users and teams of changes in tables they actively use.
- Reason: Minimize downtime for users and teams by keeping them informed about alterations to the tables within their scope of work.
Evolution from Colab: Learning from Challenges
Previous System: Colab
In our previous system, the announcement management process was facilitated through a Python script hosted on Colab, leveraging the ‘Colaboratory’ platform’s convenient features. This script seamlessly connected to Slack, a popular team collaboration tool, enabling the automated dispatch of messages to specified channels.The script, requiring zero configuration, allowed users to write and execute Python code directly in their browsers. The added advantage of free compute power access provided computational power without additional charges, making it an ideal environment for various tasks, including our announcement management. The simplicity of sharing Colab notebooks made collaborative coding effortless. While it had its merits, we encountered several challenges that prompted us to reevaluate our approach.
Challenges and Disadvantages:
Single-User Limitation:
- Challenge: Only one person could post an announcement at a time.
- Impact: Hindered collaboration and efficiency, especially during periods of increased announcement activity.
Manual Errors and Lack of History:
- Challenge: Manual errors were possible, such as sending the wrong message or posting to the wrong channel.
- Impact: Increased the risk of misinformation, and the absence of a history log made error rectification challenging.
Undefined Announcement Reporter:
- Challenge: The system lacked a defined mechanism for identifying the reporter of an announcement.
- Impact: Reduced accountability and clarity regarding the source of announcements.
Risk of Duplicate Announcements:
- Challenge: Colab’s engine occasionally got stuck, leading to the unintended posting of duplicate announcements.
- Impact: Created confusion and disrupted the communication flow within the team.
Onboarding and Security Risks:
- Challenge: Onboarding was required, and there was a security risk as the Slack token could be externally accessed.
- Impact: Hindered seamless integration and posed potential security vulnerabilities.
New and Improved System:
In response to the challenges faced with Colab, we have transitioned to a more powerful and streamlined announcement management system, leveraging Slack as the primary interface.
Advantages and Enhancements:
Efficient Channel Notifications:
- Channel notifications facilitate efficient team-wide awareness without limitations on simultaneous postings.
Private Messages for Personalized Communication:
- Private messages ensure personalized communication with individual users, fostering direct interaction.
Structured Data Entry via Excel:
- Users can input announcement details into Excel sheets, streamlining data entry and facilitating a smooth transition to backend data storage.
Dynamic Configuration Parameters:
- Dynamic configuration parameters provide flexibility and customization, allowing users to tailor announcement delivery based on specific criteria.
Integration with BigQuery and Airflow:
- Seamless integration with BigQuery and Airflow ensures efficient data management, task automation, and scalability.
Security Measures and Access Controls:
- The new architecture incorporates enhanced security measures, mitigating risks associated with external token access.
By transitioning to this new system, we aim to address the limitations of the previous approach, providing a more powerful, collaborative, and secure environment for announcement management.
Architecture
This adjusted architecture ensures a smooth and integrated experience for users or teams within Slack, offering a centralized and user-friendly interface for viewing announcements. It seamlessly combines the capabilities of Slack, BigQuery, and Airflow for efficient data management, task automation, and effective communication.
Process Flows:
- Test Flow: This is a preliminary stage where the announcement’s effectiveness and accuracy are evaluated in a controlled environment. The flow includes generating and sending test messages, running control queries, and posting affected user lists to a test channel thread for review.
- Prod Flow: In this stage, the announcement is prepared for production. It encompasses similar steps to the Test Flow but includes additional options like sending messages directly to users or channels, and tagging service account owners in threads for more targeted communication. This flow culminates with updating the history table for archival purposes.
Content Entry
To create announcements, users need to input information into an Excel sheet with specific fields:
- Id: A unique identifier for the announcement.
- Workspace: The workspace of Trendyol.
- Database: The affected related environment, e.g., Looker, BQ.
- TableName: Relevant tables being announced.
- Deadline: The deadline for the announcement.
- Option: The category of the announcement.
- Description: A free text area for describing the announcement.
- Reporter: The sender of the announcement.
- Channel: The channel to which the announcement will be sent. Test channel is the default.
Schedule
After filling out the excel where we entered the announcement information, we need to run the dwh_announcement_management dag in Airflow in order to see the announcement first in the test and then in the prod slack channel. This is not a scheduled dag, it must be run manually.
We need to edit the configuration parameters here. You can see the definition of parameters on the table.
- id: The value in the id column of your announcement in Excel (Example: 5).
- no_user: It ensures that the announcement posted on the prod channel will only be sent to the channel, with no notification sent to the user (Example: false).
- no_channel: It ensures that the announcement sent to the prod channel is sent only to the user, with no notification sent to the channel (Example: false).
- interval_day: The day range for users to query tables (Example: 45).
- consecutive_day_count: How many consecutive days the user should query (Example: 3).
- count_in_the_interval: How many times the user should query within the interval_day value (Example: 10).
- operation: The default value is test. For production, the value is prod (Example: prod).
The DAG should be run with “Trigger Dag w/ config” after editing these parameters.
Announcement
Channel Notification and Team Tagging:
In the first part of the announcement process, the system broadcasts announcements to a specific Slack channel, ensuring that the information is readily accessible to all members of that channel. This approach serves as a collective awareness mechanism, fostering team cohesion and enabling efficient dissemination of essential information.
Process Overview:
- Channel Selection: The announcement originator selects a specific Slack channel to broadcast the announcement.
- Tagging Relevant Teams: The system intelligently tags relevant teams within the selected channel, ensuring that the announcement reaches the appropriate audience.
- Collective Awareness: Team members in the specified channel receive notifications, promoting collective awareness and providing a platform for group discussions or clarifications.
Private Messages for Individual Users:
The second part of the announcement process involves sending private messages to individual users. This personalized approach ensures that users receive targeted information directly, allowing for tailored communication and a more individualized user experience.
Process Overview:
- User Identification: The system identifies individual users based on their interactions with relevant tables, as tracked within the BigQuery database.
- Private Message Delivery: Personalized private messages are sent to each identified user, containing specific details related to the announcement.
- Direct Interaction: Users can engage directly with the system, seeking clarification, asking questions, or acknowledging receipt of the announcement.
This dual approach to announcement delivery combines the efficiency of channel notifications for team-wide awareness with the personalized and direct nature of private messages for individual users. It ensures that information is disseminated comprehensively across the organization, catering to both collaborative team dynamics and the specific needs of individual users.
Future Perspectives
In our ongoing commitment to enhancing the DWH Announcement Management product, we envision a roadmap that unfolds a series of feature enrichments. These strategic advancements will not only solidify the product’s existing capabilities but also introduce cutting-edge functionalities to meet the evolving needs of our users. The forthcoming product developments include:
User-Friendly Interface:
- Design a user-friendly interface that allows users to easily create, manage, and view announcements. Intuitive navigation enhances the user experience.
Multichannel Communication:
- Consider supporting multiple communication channels such as in-app notifications, email, and possibly even integrations with collaboration tools like Slack or Microsoft Teams. This ensures that announcements reach users through their preferred channels.
Rich Media Support:
- If applicable, consider supporting rich media such as images or videos within announcements. This can help convey information more effectively and engage users.
Announcement Categories:
- Allow users or teams to categorize announcements based on topics or urgency. This helps in organizing information and allows users to filter announcements based on their interests or priorities.
Feedback Mechanism:
- Implement a feedback mechanism to gather responses or reactions from users. This could include features like comments, likes, or an emoji-based reaction system. Feedback helps gauge the impact and relevance of announcements.
Analytics and Reporting:
- Incorporate analytics to track the performance of announcements. This could include metrics like the number of views, engagement rates, and feedback statistics. Analytics can provide insights into the effectiveness of your communication strategy.
By embracing these futuristic enhancements, the DWH Announcement Management project can evolve into a more intelligent, adaptive, and user-centric solution, setting new benchmarks in the realm of data warehousing communication.
Conclusion
The DWH Announcement Management project offers a powerful solution for targeted communication with users. By leveraging BigQuery, Airflow, and Excel integration, the DWH team ensures efficient delivery of announcements, reducing downtime for affected users and promoting awareness of changes in the data environment. The project strikes a balance between automation and user control, providing a scalable and secure solution for managing informative messages in the data warehousing domain.
Join Us
We’re building a team of the brightest minds in our industry. Interested in joining us? Visit the pages below to learn more about our open positions.
