# Statement DB to Snowflake PoC

## Todo

## Questions

## Notes

- Localstack requires a license for MWAA and ECR
- No way to get results from ECS tasks
- The `dig_sales_testfile_abacus` table has no index on `batch_id`
- The `dig_sales_testfile_abacus` table on QA doesn't have real data
- We could use a dedicated SF warehouse to load the data

## Performance

- Run 1
    - 50M rows
    - No index
    - DEV_OWS_WAREHOUSE
    - Split size: 10M rows
    - Outfile size: 9GB
    - Parts size: 30MB
    - Timings:
        - Checking: 3s
        - Extracting: 5m45s
            - Generating outfile: 4min
            - Counting lines: 35s
            - Splitting and compressing: 1min
            - Uploading: 1s
            - Deleting: 1s
        - Loading: 3min12s
        - Copying: 1m27s
        - Total: 10min40s
- Run 2
    - 50M rows
    - Index on batch_id
    - QA_ABACUS_WH
    - Split size: 50M rows
    - Outfile size: 9GB
    - Parts size: 146MB
    - Timings:
        - Checking: 3s
        - Extracting: 7m21s
            - Generating outfile: 5min
            - Counting lines: 40s
            - Splitting and compressing: 1min
            - Uploading: 3s
            - Deleting: 1s
        - Loading: 5min14s
        - Copying: 1m22s
        - Total: 14min13s
- Run 3
    - 100M rows
    - Index on batch_id and `--quick`
    - QA_ABACUS_WH
    - Split size: 50M rows
    - Outfile size: 18GB
    - Parts size: 146MB
    - Timings:
        - Checking: 3s
        - Extracting: 14m52s
            - Generating outfile: 10min
            - Counting lines: 2min30s
            - Splitting and compressing: 2min30s
            - Uploading: 3s
            - Deleting: 1s
        - Loading: 7min02s
        - Copying: 1m22s
        - Total: 23min32s
- Run 4
    - 500M rows
    - Index on batch_id and `--quick`
    - QA_ABACUS_WH
    - Split size: 50M rows
    - Outfile size: 91GB
    - Parts size: 146MB
    - Timings:
        - Checking: 3s
        - Extracting: 1h30min
            - Generating outfile: 1h05min
            - Counting lines: 12min
            - Splitting and compressing: 13min
            - Uploading: 13s
            - Deleting: 2s
        - Loading: 11min48s
        - Copying: 1m27s
        - Total: 1h43min
- Run 5
    - 800M rows
    - Index on batch_id and `--quick`
    - QA_ABACUS_WH
    - Split size: 50M rows
    - Outfile size: 145GB
    - Parts size: 146MB
    - Timings:
        - Checking: 3s
        - Extracting: 2h34min
            - Generating outfile: 1h54min
            - Counting lines: 20min
            - Splitting and compressing: 20min
            - Uploading: 20s
            - Deleting: 2s
        - Loading: 12min14s
        - Copying: 1m26s
        - Total: 2h48min
- Run 6
    - 1B rows
    - Index on batch_id and `--quick`
    - QA_ABACUS_WH
    - Split size: 50M rows
    - Outfile size: 181GB
    - Parts size: 146MB
    - Timings:
        - Checking: 3s
        - Extracting: 3h15min
            - Generating outfile: 2h24min
            - Counting lines: 25min
            - Splitting and compressing: 25min
            - Uploading: 25s
            - Deleting: 2s
        - Loading: 12min20s
        - Copying: 1m36s
        - Total: 3h29min
- Run 7
    - 1.5B rows
    - Index on batch_id and `--quick` and `ripgrep` and `pigz`
    - QA_ABACUS_WH
    - Split size: 50M rows
    - Outfile size: 272GB
    - Parts size: 146MB
    - Timings:
        - Checking: 3s
        - Extracting: 4h56min
            - Generating outfile: 3h40min
            - Counting lines: 36min
            - Splitting and compressing: 38min
            - Uploading: 37s
            - Deleting: 3s
        - Loading (directly to staging in one step): 15min05s
        - Total: 5h12min
- Run 8
    - 1B rows
    - Index on batch_id and `--quick` and `pigz`
    - QA_ABACUS_WH
    - Split size: 10G
    - Outfile size: 272GB
    - Parts size: 146MB
    - Timings:
        - Checking: 3s
        - Extracting: 3h02min
            - Generating outfile: 2h37min
            - Splitting and compressing: 25min
            - Uploading: 26s
            - Deleting: 2s
        - Loading (directly to staging in one step): 11min05s
        - Total: 3h13min