publish_transformation.yml

name: Publish transformation

on:
  push:
    tags:
      - "transformation-*-v*.*.*"

env:
  SNOW_USER: ${{ secrets.SNOW_USER }}
  SNOW_PASSWORD: ${{ secrets.SNOW_PASSWORD }}
  # DBT assumes the account is in the form of <account>.<region>
  SNOW_ACCOUNT: "${{ secrets.SNOW_ACCOUNT }}.${{ secrets.SNOW_REGION }}"
  SNOW_WAREHOUSE: ${{ secrets.SNOW_WAREHOUSE }}
  SNOW_DATABASE: ${{ secrets.SNOW_DATABASE }}
  SNOW_SCHEMA: ${{ secrets.SNOW_SCHEMA }}
  SNOW_REGION: ${{ secrets.SNOW_REGION }}
jobs:
  prepare:
    runs-on: ubuntu-latest
    outputs:
      transformation_dir: ${{ fromJson(steps.set-result.outputs.result).transformation_dir }}
      transformation_version: ${{ fromJson(steps.set-result.outputs.result).transformation_version }}
      zipPath: ${{ fromJson(steps.set-result.outputs.result).zipPath }}
      postgres: ${{ fromJson(steps.set-result.outputs.result).postgres }}
      snowflake: ${{ fromJson(steps.set-result.outputs.result).snowflake }}
      bigquery: ${{ fromJson(steps.set-result.outputs.result).bigquery }}
    steps:
      - name: Checkout
        uses: actions/checkout@v4
      - uses: actions/github-script@v7
        id: set-result
        env:
          TRANSFORMATION_TAG: ${{ github.ref_name }}
        with:
          script: |
            const fs = require('fs/promises');
            const { TRANSFORMATION_TAG } = process.env;
            const [_, topLevelDir, ...rest] = TRANSFORMATION_TAG.split('-');
            const version = rest.pop();
            const transformation_dir = `transformations/${topLevelDir}/${rest.join('-')}`;
            const [postgres, snowflake, bigquery] = await Promise.all([
              fs.access(`${transformation_dir}/tests/postgres.yml`, fs.constants.F_OK).then(() => true).catch(() => false),
              fs.access(`${transformation_dir}/tests/snowflake.yml`, fs.constants.F_OK).then(() => true).catch(() => false),
              fs.access(`${transformation_dir}/tests/bigquery.yml`, fs.constants.F_OK).then(() => true).catch(() => false),
            ]);
            return {
              transformation_dir,
              transformation_version: version,
              postgres,
              snowflake,
              bigquery,
            };
  publish-transformation:
    permissions:
      id-token: 'write'
      contents: 'read'
    needs: prepare
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:11
        env:
          POSTGRES_PASSWORD: pass
          POSTGRES_USER: postgres
          POSTGRES_DB: postgres
        ports:
          - 5432:5432
        # Set health checks to wait until postgres has started
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5
    steps:
      - name: Checkout
        uses: actions/checkout@v4
      # Required for BigQuery targets
      - name: Authenticate to Google Cloud
        uses: 'google-github-actions/auth@v2'
        with:
          workload_identity_provider: 'projects/151868820337/locations/global/workloadIdentityPools/integration-test-pool/providers/integration-test-provider'
          service_account: 'integration-service-account@cq-integration-tests.iam.gserviceaccount.com'
      - name: Update Release Notes
        uses: actions/github-script@v7
        env:
          TRANSFORMATION_DIR: ${{ needs.prepare.outputs.transformation_dir }}
        with:
          script: |
            const fs = require('fs/promises');
            const { TRANSFORMATION_DIR } = process.env;
            const { data } = await github.rest.repos.getReleaseByTag({
              owner: "cloudquery",
              repo: context.repo.repo,
              tag: context.ref.replace('refs/tags/', ''),
            });
            const releaseNotes = data.body;
            const linksRemoved = releaseNotes.replace(/\[([^\]]+)\]\([^)]+\)/g, '$1');
            const changelogPath = `${TRANSFORMATION_DIR}/changelog.md`;
            await fs.writeFile(changelogPath, linksRemoved);
      - uses: actions/setup-python@v5
        with:
          python-version: "3.9"
          cache: "pip"
          cache-dependency-path: "./${{ needs.prepare.outputs.transformation_dir }}/requirements.txt"
      - name: Install dependencies
        working-directory: ${{ needs.prepare.outputs.transformation_dir }}
        run: pip install -r requirements.txt
      - name: Setup CloudQuery
        uses: cloudquery/setup-cloudquery@v3
        with:
          version: v5.1.0
      - name: Migrate DB Postgres
        if: needs.prepare.outputs.postgres == 'true'
        run: cloudquery migrate tests/postgres.yml
        working-directory: ${{ needs.prepare.outputs.transformation_dir }}
        env:
          CQ_DSN: postgresql://postgres:pass@localhost:5432/postgres
      - name: Migrate DB Snowflake
        if: needs.prepare.outputs.snowflake == 'true'
        run: cloudquery migrate tests/snowflake.yml
        working-directory: ${{ needs.prepare.outputs.transformation_dir }}
        env:
          SNOWFLAKE_CONNECTION_STRING: "${{ secrets.SNOW_USER }}:${{ secrets.SNOW_PASSWORD }}@${{ secrets.SNOW_ACCOUNT }}.${{ secrets.SNOW_REGION }}/${{ secrets.SNOW_DATABASE }}/${{ secrets.SNOW_SCHEMA }}?warehouse=${{ secrets.SNOW_WAREHOUSE }}"
      - name: Migrate DB BigQuery
        if: needs.prepare.outputs.bigquery == 'true'
        run: cloudquery migrate tests/bigquery.yml
        working-directory: ${{ needs.prepare.outputs.transformation_dir }}
      - name: Setup Node.js
        uses: actions/setup-node@v4
        with:
          node-version: 'lts/*'
          cache: 'npm'
          cache-dependency-path: scripts/dbt-pack/package-lock.json
      - name: Install Dependencies
        run: npm ci
        working-directory: ./scripts/dbt-pack
      - name: Pack DBT
        working-directory: ./scripts/dbt-pack
        run: node index.js dbt-pack --project-dir=../../${{ needs.prepare.outputs.transformation_dir }}
      - name: Setup CloudQuery
        uses: cloudquery/setup-cloudquery@v3
        with:
          version: v5.1.0
      - name: Publish tranformation
        working-directory: ${{ needs.prepare.outputs.transformation_dir }}
        env:
          CLOUDQUERY_API_KEY: ${{ secrets.CLOUDQUERY_API_KEY }}
        run: |
          cloudquery addon publish manifest.json ${{ needs.prepare.outputs.transformation_version }} -f