Cloud DW Reference Architecture


Cloud adoption is changing the technology landscape of organizations. Companies are adopting more cloud platform whether for enterprise systems, marketing systems or any other 3rd party systems. This has led to a change in the data flow and architecture for DW and analytics.

Some of the key changes in the each layers of data and analytics are as below :

Source Systems

Lot of  companies are using Salesforce for Quote to Cash (Q2C). Similarly for financial HR systems, cloud platforms like Workday are being considered.

Almost all new age marketing systems are on cloud

Impact : API Management is the key!

Instead of moving the data in flat files, the cloud companies provide the APIs from where the DW teams need to fetch the data. This can be done by using cloud ETL tools or python scripts.

Data Extraction, Load & Transformation (ELT/ETL)

ETL/ELT layer has gone the maximum transformation in the DW space. Some of the key ones are as low:

  • Extracting source system data using APIs using Fivetran or cloud ETL technologies like Matillion that have built-in connectors for source systems like Salesforce, Zendesk, Workday etc.
  • Airflow is being used a data orchestration tool along with cloud ETL tool.
  • Mulesoft and similar technologies are used to integrate cloud systems data with Cloud DW in real-time.
  • Python is increasingly being used for data extraction and integration.
  • Companies are adopting multiple technologies that are purpose-fit for different type of integrations.

EDW, ODS, Data Lake Layer

EDW and data storage layer has gone transformation with blurring of boundaries between structured and non structured data. Platforms like Snowflake lets you store any type of data with ease.

  • Storage is being segregated from data being used for analytics keeping the costs in control
  • Data lake is increasingly used for storing large amounts of data
  • Only the data which is used for analytics purposes is sent to EDW layer like Snowflake
  • Data sharing is increasingly being used to share the same data across organization and even external vendor reducing cost and redundancy.

BI and Analytics layer

  • BI is becoming more pervasive with the organization with cloud adoption

  • BI now can be accessed anywhere and across all devices easily from anywhere

  • BI can now be truly offered as-a-services with the flexibility of scaling up or down anytime

  • Cloud adoption is leading to Collaborative Business Intelligence (CBI) solutions  beyond the organization’s boundaries