Reverse ETL

POST https://mycrm.com/api/contacts
{
  "name": "John Doe",
  "email": "john@doe.com",
  "phone": "+1 555 6666"
}

def transform(source_record, target_record, pq, target_id=None):
    
    # Add nested data
    target_record["address"] = {
        "street": source_record["address_street"]
        "city":   source_record["address_city"]
    }

    # For updates
    if target_id:
        final_payload['id'] = target_id

    return target_record

POST https://mycrm.com/api/contacts
{
  "name": "John Doe",
  "email": "john@doe.com",
  "phone": "+1 555 6666",
  "address": {
     "street": "1 Somestreet",
     "city": "NY"
  }
}

def transform(source_record, target_record, pq, target_id=None):
    final_payload = target_record
    
    if target_id: # meaning an update (not an insert)
        if source_record["status"] == "deleted":
            return None # returning None makes sure the record is skipped
        else:
            final_payload["id"] = target_id
    return final_payload

def transform(source_record, target_record, pq, target_id=None):
    if target_id is None:
        # if target_id is not set: Reverse ETL app did not yet process 
        # this source record, it was not in the link table

        # do look up of company by VAT in target
        vat = source_record["vat"]
        vat = vat.lower().replace(".","") # normalize
        url = f"https://somesaas.com/api/companies/search?vat={vat}"
        response = requests.get(url)
        result = response.json()
        if result and "id" in result:
            # record found: it already exists in target
            target_id = result["id"]
            target_record['id'] = target_id

    return target_record, target_id

WITH matching_companies AS (
  SELECT 
     source.id AS source_id,
     target.id AS target_id
  FROM hubspot.companies AS source
  INNER JOIN odoo.partners AS target
     ON source.vat = target.vat
)
INSERT INTO link_tables.companies_1234 (source_id, target_id, status)
SELECT source_id, target_id, 'matched' AS status
FROM matching_companies;

SELECT * FROM hubspot.companies AS source 
LEFT JOIN odoo.partners AS target
ON source.vat = target.vat
WHERE target.vat IS NULL

id    firstname       lastname      country_code     _sdc_batched_at
1     Bob             Davis         FR               20205-01-01 00:00:00
2     Anne            Crome         UK               20205-01-01 00:00:00
3     ...

fullname        country          category
Bob Davis       France           VIP
Anne Crome      United Kingdom   VIP

SELECT
    id,                                              -- make sure to include id
    firstname || ' ' || lastname as fullname,        -- concatenate
    CASE                                             -- value mapping
    	WHEN country_code = 'FR' THEN 'France'
    	WHEN country_code = 'UK' THEN 'United Kingdom'
    END AS country,
    "VIP" AS contact_category,                       -- static value
    _sdc_batched_at                                  -- make sure to include _sdc_batched_at
FROM contacts

SELECT
  i.id,
  i.ref,
  i.date,
  i.partner_id,
  i.amount_total,
  JSON_AGG(
    JSON_BUILD_OBJECT(
      'Quantity', l.quantity,
      'Name', l.name
    )
  ) AS invoice_lines_json
FROM odoo.account_move AS i
LEFT JOIN odoo.account_move_line AS l
  ON i.id = l.move_id
WHERE
  i.move_type = 'out_invoice' AND line_ids <> '[]'
GROUP BY
  i.id,
  i.ref,
  i.date,
  i.partner_id,
  i.amount_total,

SELECT '{"name": "An", "id": 12}'::jsonb FROM my_table

SELECT
  c.id,
  c.name,
  c.email,
  c.company,
  CASE
    WHEN c.customfield = 'Option A internal name'
    THEN 'Option A value'
    WHEN c.customfield = 'Option B internal name'
    THEN 'Option B value'
    WHEN c.customfield = 'Option C internal name'
    THEN 'Option C value'
    ELSE NULL
  END AS target_option
FROM hubspot.contacts AS c

SELECT
  c.id,
  c.name,
  c.email,
  c.customfield,                         -- e.g. "100"
  source_option.value AS source_option,  -- e.g. "100"
  target_option.value AS target_option   -- e.g. "333" = field option value sent to target
FROM source.contacts AS c
LEFT JOIN source.field_options AS source_option
  ON c.customfield = source_option.value
  AND source_option.field_name = 'source_customfield'
LEFT JOIN target.field_options AS target_option
  ON source_option.label = target_option.label 
  AND target_option.field_name = 'target_customfield'

SELECT
   source_table.id 
FROM hubspot.contacts AS source_table
LEFT JOIN link_tables.contacts_1234 AS link_table ON
    source_table.id = link_table.source_id
LEFT JOIN oodo.partners AS target_table ON
    link_table.target_id = target_table.id
WHERE 
    target_table.id IS NULL