Authenticating...
Skip to main content

0016: AdGem Dasboard - New Redshift Conection

STATUS​

Accepted

CONTEXT​

As part of the old Redshift instance sunset, all the connections and usage that the AdGem Dashboard has with the old Redshift needs to be reviewed, migrated to the new instance, or deprecated and removed. This presents an opportunity to migrate all the data ETL processes that the AdGem Dashboard does to a data-dedicated solution. This also presents an opportunity to define the way the AdGem Dashboard interacts with the Data stack (new Redshift instance, DBT/Airflow solutions).

Considered Options​

  • Migrate all the tables used in the AdGem Dashboard to the new redshift instance
    • Migrate the cronjobs related to data ETL to our DBT/Airflow project.
    • Update queries and references to the new redshift instance
    • Remove the need for a fake Redshift instance for testing
    • Remove old redshift connections

To avoid direct queries to our warehouse:​

1. Intermediate Layer (ETL/Batch Process)​

When to Use:​
  • Pre-Aggregated or Periodic Reports: Reports are updated on a fixed schedule (e.g., daily, hourly).
  • Large Datasets: You want to pre-aggregate data or perform complex computations that would be inefficient to do on-demand.
  • Heavy Queries: Running ad-hoc queries directly on your warehouse may impact performance; pre-calculating reduces this risk.
  • High Scalability: You need to scale reporting independently from your data warehouse.
Advantages:​
  • Efficiency: Heavy data transformations are done upfront, reducing the time required to serve reports.
  • Cost-Effective: Data warehouses like Redshift charge per query, so reducing query volume saves money.
  • Performance: Pre-computed datasets allow for faster response times in the dashboard.
  • Stability: Isolates reporting workloads from the warehouse, reducing the risk of query overload.
Challenges:​
  • Delay in Data Availability: Reports are only as fresh as the last ETL run.
  • Storage Overhead: Requires additional storage for pre-aggregated data.
  • Complexity: Requires managing ETL pipelines and storage.

2. API Gateway + Lambda​

When to Use:​
  • Real-Time Reporting: Data must be queried and displayed on-demand with minimal latency.
  • Dynamic Queries: Users require customized or ad-hoc reports that cannot be pre-aggregated.
  • Lightweight Queries: The reports involve lightweight transformations or smaller data volumes.
  • Flexibility: Ideal for prototyping and quickly building APIs.
Advantages:​
  • Real-Time Access: Delivers fresh data directly from the source or a secondary system.
  • Simpler Setup: Easier to implement for basic reporting use cases.
  • Event-Driven: Scales seamlessly with demand due to AWS Lambda’s serverless architecture.
Challenges:​
  • Warehouse Load: Direct queries, even if mediated, can overload the warehouse if demand spikes.
  • Cost: Frequent invocations of Lambda and queries on Redshift can become expensive.

3. Hybrid Approach: Best of Both Worlds​

  • Use Intermediate Layer for pre-aggregated or high-volume data to support recurring reports.
  • Use API Gateway + Lambda for lightweight, on-demand queries or less frequently accessed data.

To decide which option will be the best option to use for each report and if this report need to be updated in the new redshift instance, we will rely on how frequent the report is used.

Maintaining the direct queries to our data warehouse:​

4. Simplify Redshift Connection​

  • Medallion Architecture: Allow the direct connection to query gold tables and disregard the raw events table connection
    • Pre-Aggregated Tables:
      • Maintain the admin capability of generating reports on event-specific such as outgoing postbacks reports.
      • Keeps the Publisher's report the same, as those already use gold tables.
  • Migrating Admin Reports: Move the admin reports to Metabase, so the AdGem Dashboard won't query any bronze table.
  • ETL Migration: Identify useful cronjobs related to data ETL to our DBT/Airflow project and migrate them. For deprecated ones, remove them.

Advantages:​

  • Time To Live: Quick solution, since it doesn't require any additional infrastructure or new project to maintain.
  • Performance: Simplyfing connections in the dashboard reduces stress for the AdGem Dashboard.
  • Cost-Effective: Data warehouses like Redshift charge per query, so reducing query volume saves money.
  • Publisher Experience: The reporting system for the publishers maintains the same.
  • Single-responsibility principle.

Challenges:​

  • Admins Learning Curve: Admins may need to learn how to use Metabase to generate the reports.
  • Complexity: Requires managing ETL pipelines and storage.

DECISION​

4. Simplify Redshift Connection​

CONSEQUENCES​

  • Once the old Redshift connection gets sunseted, the AdGem Dashboard admins won't be able to generate reports using old data.

Risks​

NOTES​

References​

Original Author​

Quintin Soto, Maria Cornejo

Approval date​

Approved by​

Appendix​