Simplifying data warehousing for marketing analytics using GCP Big Query

Is it ELT or ETL?

Architecture

Process

  1. SFMC Automation runs to extract relevant data from DEs, convert into csv files (Data Extracts), transfer the files to SFMC’s SFTP directory (File Transfers).
  2. Cloud Function runs to copy SFMC SFTP folder content to GCP Cloud Storage
  3. BigQuery Transfer service imports data into BQ staging tables (Bronze Project)
    ** — these fields would all be string format, you will need to read from these to reformat to the desired field types later on
    — 1 transfer per table.
  4. Scheduled Big Query SQL Queries read from the Bronze Project and write clean, reformatted data into the Silver project tables
  5. Scheduled Big Query SQL Queries read from the Silver Project and write de-normalised, aggregated data into the Gold project (may not be necessary depending on who’s using the solution).

Solution Components

Cloud Function to copy files from SFMC SFTP

Big Query Transfers

Scheduled Queries

Data visualisation

Summary

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Oscar McCall

Oscar McCall

I’m a SFMC & Cloud Architect (AWS,GCP) with a curiosity for enhancing Martech capabilities by utilising Cloud solutions