Redshift SQLRunner, no errors but no data either

For engineers
1

Hello,

When running SQLRunner playbook for data modeling, data-models/web/v1 at master · snowplow/data-models (github.com), everything seems to run “fine”, all tables are created, but no data is actually moved around from the atomic events table.

CLI command:

./run_config.sh -b sql-runner -c /configs/datamodeling.json -t /templates/redshift.yml.tmpl


run_config: starting playbook: standard/01-base/01-base-main
2023/05/29 09:34:01 EXECUTING 00-setup-base (in step 00-setup-base @ ): /home/ec2-user/data-models-web-redshift-1.3.1/web/v1/redshift/sql-runner/sql/standard/01-base/01-main/00-setup-base.sql
2023/05/29 09:34:01 SUCCESS: 00-setup-base (step 00-setup-base @ target ), ROWS AFFECTED: -1
2023/05/29 09:34:01 EXECUTING 01-new-events-limits (in step 01-new-events-limits @ ): /home/ec2-user/data-models-web-redshift-1.3.1/web/v1/redshift/sql-runner/sql/standard/01-base/01-main/01-new-events-limits.sql
2023/05/29 09:34:01 SUCCESS: 01-new-events-limits (step 01-new-events-limits @ target ), ROWS AFFECTED: -1
2023/05/29 09:34:01 EXECUTING 02-run-manifest (in step 02-run-manifest @ ): /home/ec2-user/data-models-web-redshift-1.3.1/web/v1/redshift/sql-runner/sql/standard/01-base/01-main/02-run-manifest.sql
2023/05/29 09:34:01 SUCCESS: 02-run-manifest (step 02-run-manifest @ target ), ROWS AFFECTED: -1
2023/05/29 09:34:01 EXECUTING 03-sessions-to-process (in step 03-sessions-to-process @ ): /home/ec2-user/data-models-web-redshift-1.3.1/web/v1/redshift/sql-runner/sql/standard/01-base/01-main/03-sessions-to-process.sql
2023/05/29 09:34:02 SUCCESS: 03-sessions-to-process (step 03-sessions-to-process @ target ), ROWS AFFECTED: -1
2023/05/29 09:34:02 EXECUTING 04-sessions-to-include (in step 04-sessions-to-include @ ): /home/ec2-user/data-models-web-redshift-1.3.1/web/v1/redshift/sql-runner/sql/standard/01-base/01-main/04-sessions-to-include.sql
2023/05/29 09:34:02 SUCCESS: 04-sessions-to-include (step 04-sessions-to-include @ target ), ROWS AFFECTED: -1
2023/05/29 09:34:02 EXECUTING 05-batch-limits (in step 05-batch-limits @ ): /home/ec2-user/data-models-web-redshift-1.3.1/web/v1/redshift/sql-runner/sql/standard/01-base/01-main/05-batch-limits.sql
2023/05/29 09:34:02 SUCCESS: 05-batch-limits (step 05-batch-limits @ target ), ROWS AFFECTED: -1
2023/05/29 09:34:02 EXECUTING 06-events-this-run (in step 06-events-this-run @ ): /home/ec2-user/data-models-web-redshift-1.3.1/web/v1/redshift/sql-runner/sql/standard/01-base/01-main/06-events-this-run.sql
2023/05/29 09:34:03 SUCCESS: 06-events-this-run (step 06-events-this-run @ target ), ROWS AFFECTED: -1
2023/05/29 09:34:03 EXECUTING 07-duplicates (in step 07-duplicates @ ): /home/ec2-user/data-models-web-redshift-1.3.1/web/v1/redshift/sql-runner/sql/standard/01-base/01-main/07-duplicates.sql
2023/05/29 09:34:03 SUCCESS: 07-duplicates (step 07-duplicates @ target ), ROWS AFFECTED: -1
2023/05/29 09:34:03 EXECUTING 08-add-page-view-id (in step 08-add-page-view-id @ ): /home/ec2-user/data-models-web-redshift-1.3.1/web/v1/redshift/sql-runner/sql/standard/01-base/01-main/08-add-page-view-id.sql
2023/05/29 09:34:04 SUCCESS: 08-add-page-view-id (step 08-add-page-view-id @ target ), ROWS AFFECTED: -1
2023/05/29 09:34:04 EXECUTING 09-metadata (in step 09-metadata @ ): /home/ec2-user/data-models-web-redshift-1.3.1/web/v1/redshift/sql-runner/sql/standard/01-base/01-main/09-metadata.sql
2023/05/29 09:34:04 SUCCESS: 09-metadata (step 09-metadata @ target ), ROWS AFFECTED: 1
2023/05/29 09:34:04 EXECUTING 10-commit-base (in step 10-commit-base @ ): /home/ec2-user/data-models-web-redshift-1.3.1/web/v1/redshift/sql-runner/sql/standard/01-base/01-main/10-commit-base.sql
2023/05/29 09:34:04 SUCCESS: 10-commit-base (step 10-commit-base @ target ), ROWS AFFECTED: -1
2023/05/29 09:34:04 SUCCESS: 11 queries executed against 1 targets
run_config: done with playbook: standard/01-base/01-base-main

run_config: starting playbook: standard/02-page-views/01-page-views-main
2023/05/29 09:34:04 EXECUTING 00-setup-page-views (in step 00-setup @ ): /home/ec2-user/data-models-web-redshift-1.3.1/web/v1/redshift/sql-runner/sql/standard/02-page-views/01-main/00-setup-page-views.sql
2023/05/29 09:34:04 SUCCESS: 00-setup-page-views (step 00-setup @ target ), ROWS AFFECTED: -1
2023/05/29 09:34:04 EXECUTING 01-page-view-limits (in step 01-limits @ ): /home/ec2-user/data-models-web-redshift-1.3.1/web/v1/redshift/sql-runner/sql/standard/02-page-views/01-main/01-page-view-limits.sql
2023/05/29 09:34:04 SUCCESS: 01-page-view-limits (step 01-limits @ target ), ROWS AFFECTED: -1
2023/05/29 09:34:04 EXECUTING 02-page-view-events (in step 02-page-view-events @ ): /home/ec2-user/data-models-web-redshift-1.3.1/web/v1/redshift/sql-runner/sql/standard/02-page-views/01-main/02-page-view-events.sql
2023/05/29 09:34:05 SUCCESS: 02-page-view-events (step 02-page-view-events @ target ), ROWS AFFECTED: -1
2023/05/29 09:34:05 EXECUTING 03-page-view-duplicates (in step 03-page-view-duplicates @ ): /home/ec2-user/data-models-web-redshift-1.3.1/web/v1/redshift/sql-runner/sql/standard/02-page-views/01-main/03-page-view-duplicates.sql
2023/05/29 09:34:05 SUCCESS: 03-page-view-duplicates (step 03-page-view-duplicates @ target ), ROWS AFFECTED: -1
2023/05/29 09:34:05 EXECUTING 04-engaged-time (in step 04-engaged-time @ ): /home/ec2-user/data-models-web-redshift-1.3.1/web/v1/redshift/sql-runner/sql/standard/02-page-views/01-main/04-engaged-time.sql
2023/05/29 09:34:05 SUCCESS: 04-engaged-time (step 04-engaged-time @ target ), ROWS AFFECTED: -1
2023/05/29 09:34:05 EXECUTING 05-scroll-depth (in step 05-scroll-depth @ ): /home/ec2-user/data-models-web-redshift-1.3.1/web/v1/redshift/sql-runner/sql/standard/02-page-views/01-main/05-scroll-depth.sql
2023/05/29 09:34:05 SUCCESS: 05-scroll-depth (step 05-scroll-depth @ target ), ROWS AFFECTED: -1
2023/05/29 09:34:05 EXECUTING 06-a-iab (in step 06-a-iab @ ): /home/ec2-user/data-models-web-redshift-1.3.1/web/v1/redshift/sql-runner/sql/standard/02-page-views/01-main/06-a-iab.sql
2023/05/29 09:34:05 SUCCESS: 06-a-iab (step 06-a-iab @ target ), ROWS AFFECTED: -1
2023/05/29 09:34:05 EXECUTING 06-b-ua-parser (in step 06-b-ua-parser @ ): /home/ec2-user/data-models-web-redshift-1.3.1/web/v1/redshift/sql-runner/sql/standard/02-page-views/01-main/06-b-ua-parser.sql
2023/05/29 09:34:05 SUCCESS: 06-b-ua-parser (step 06-b-ua-parser @ target ), ROWS AFFECTED: -1
2023/05/29 09:34:05 EXECUTING 06-c-yauaa (in step 06-c-yauaa @ ): /home/ec2-user/data-models-web-redshift-1.3.1/web/v1/redshift/sql-runner/sql/standard/02-page-views/01-main/06-c-yauaa.sql
2023/05/29 09:34:05 SUCCESS: 06-c-yauaa (step 06-c-yauaa @ target ), ROWS AFFECTED: -1
2023/05/29 09:34:05 EXECUTING 07-page-views (in step 07-page-views @ ): /home/ec2-user/data-models-web-redshift-1.3.1/web/v1/redshift/sql-runner/sql/standard/02-page-views/01-main/07-page-views.sql
2023/05/29 09:34:05 SUCCESS: 07-page-views (step 07-page-views @ target ), ROWS AFFECTED: -1
2023/05/29 09:34:05 EXECUTING 08-page-views-metadata (in step 08-page-views-metadata @ ): /home/ec2-user/data-models-web-redshift-1.3.1/web/v1/redshift/sql-runner/sql/standard/02-page-views/01-main/08-page-views-metadata.sql
2023/05/29 09:34:05 SUCCESS: 08-page-views-metadata (step 08-page-views-metadata @ target ), ROWS AFFECTED: 1
2023/05/29 09:34:05 EXECUTING 09-commit-page-views (in step 09-commit-page-views @ ): /home/ec2-user/data-models-web-redshift-1.3.1/web/v1/redshift/sql-runner/sql/standard/02-page-views/01-main/09-commit-page-views.sql
2023/05/29 09:34:06 SUCCESS: 09-commit-page-views (step 09-commit-page-views @ target ), ROWS AFFECTED: -1
2023/05/29 09:34:06 SUCCESS: 12 queries executed against 1 targets
run_config: done with playbook: standard/02-page-views/01-page-views-main

run_config: starting playbook: standard/03-sessions/01-sessions-main
2023/05/29 09:34:06 EXECUTING 00-setup-sessions (in step 00-setup-sessions @ ): /home/ec2-user/data-models-web-redshift-1.3.1/web/v1/redshift/sql-runner/sql/standard/03-sessions/01-main/00-setup-sessions.sql
2023/05/29 09:34:06 SUCCESS: 00-setup-sessions (step 00-setup-sessions @ target ), ROWS AFFECTED: -1
2023/05/29 09:34:06 EXECUTING 01-sessions-aggs (in step 01-sessions-aggs @ ): /home/ec2-user/data-models-web-redshift-1.3.1/web/v1/redshift/sql-runner/sql/standard/03-sessions/01-main/01-sessions-aggs.sql
2023/05/29 09:34:06 SUCCESS: 01-sessions-aggs (step 01-sessions-aggs @ target ), ROWS AFFECTED: -1
2023/05/29 09:34:06 EXECUTING 02-sessions-lasts (in step 02-sessions-lasts @ ): /home/ec2-user/data-models-web-redshift-1.3.1/web/v1/redshift/sql-runner/sql/standard/03-sessions/01-main/02-sessions-lasts.sql
2023/05/29 09:34:06 SUCCESS: 02-sessions-lasts (step 02-sessions-lasts @ target ), ROWS AFFECTED: -1
2023/05/29 09:34:06 EXECUTING 03-sessions (in step 03-sessions @ ): /home/ec2-user/data-models-web-redshift-1.3.1/web/v1/redshift/sql-runner/sql/standard/03-sessions/01-main/03-sessions.sql
2023/05/29 09:34:06 SUCCESS: 03-sessions (step 03-sessions @ target ), ROWS AFFECTED: -1
2023/05/29 09:34:06 EXECUTING 04-sessions-metadata (in step 04-sessions-metadata @ ): /home/ec2-user/data-models-web-redshift-1.3.1/web/v1/redshift/sql-runner/sql/standard/03-sessions/01-main/04-sessions-metadata.sql
2023/05/29 09:34:07 SUCCESS: 04-sessions-metadata (step 04-sessions-metadata @ target ), ROWS AFFECTED: 1
2023/05/29 09:34:07 EXECUTING 05-sessions-prep-manifest (in step 05-sessions-prep-manifest @ ): /home/ec2-user/data-models-web-redshift-1.3.1/web/v1/redshift/sql-runner/sql/standard/03-sessions/01-main/05-sessions-prep-manifest.sql
2023/05/29 09:34:07 SUCCESS: 05-sessions-prep-manifest (step 05-sessions-prep-manifest @ target ), ROWS AFFECTED: -1
2023/05/29 09:34:07 EXECUTING 06-commit-sessions (in step 06-commit-sessions @ ): /home/ec2-user/data-models-web-redshift-1.3.1/web/v1/redshift/sql-runner/sql/standard/03-sessions/01-main/06-commit-sessions.sql
2023/05/29 09:34:08 SUCCESS: 06-commit-sessions (step 06-commit-sessions @ target ), ROWS AFFECTED: -1
2023/05/29 09:34:08 SUCCESS: 7 queries executed against 1 targets
run_config: done with playbook: standard/03-sessions/01-sessions-main

run_config: starting playbook: standard/04-users/01-users-main
2023/05/29 09:34:08 EXECUTING 00-setup-users (in step 00-setup-users @ ): /home/ec2-user/data-models-web-redshift-1.3.1/web/v1/redshift/sql-runner/sql/standard/04-users/01-main/00-setup-users.sql
2023/05/29 09:34:08 SUCCESS: 00-setup-users (step 00-setup-users @ target ), ROWS AFFECTED: -1
2023/05/29 09:34:08 EXECUTING 01-userids-this-run (in step 01-userids-this-run @ ): /home/ec2-user/data-models-web-redshift-1.3.1/web/v1/redshift/sql-runner/sql/standard/04-users/01-main/01-userids-this-run.sql
2023/05/29 09:34:08 SUCCESS: 01-userids-this-run (step 01-userids-this-run @ target ), ROWS AFFECTED: -1
2023/05/29 09:34:08 EXECUTING 02-users-limits (in step 02-users-limits @ ): /home/ec2-user/data-models-web-redshift-1.3.1/web/v1/redshift/sql-runner/sql/standard/04-users/01-main/02-users-limits.sql
2023/05/29 09:34:08 SUCCESS: 02-users-limits (step 02-users-limits @ target ), ROWS AFFECTED: -1
2023/05/29 09:34:08 EXECUTING 03-users-sessions-this-run (in step 03-users-sessions-this-run @ ): /home/ec2-user/data-models-web-redshift-1.3.1/web/v1/redshift/sql-runner/sql/standard/04-users/01-main/03-users-sessions-this-run.sql
2023/05/29 09:34:08 SUCCESS: 03-users-sessions-this-run (step 03-users-sessions-this-run @ target ), ROWS AFFECTED: -1
2023/05/29 09:34:08 EXECUTING 04-users-aggs (in step 04-users-aggs @ ): /home/ec2-user/data-models-web-redshift-1.3.1/web/v1/redshift/sql-runner/sql/standard/04-users/01-main/04-users-aggs.sql
2023/05/29 09:34:09 SUCCESS: 04-users-aggs (step 04-users-aggs @ target ), ROWS AFFECTED: -1
2023/05/29 09:34:09 EXECUTING 05-users-lasts (in step 05-users-lasts @ ): /home/ec2-user/data-models-web-redshift-1.3.1/web/v1/redshift/sql-runner/sql/standard/04-users/01-main/05-users-lasts.sql
2023/05/29 09:34:09 SUCCESS: 05-users-lasts (step 05-users-lasts @ target ), ROWS AFFECTED: -1
2023/05/29 09:34:09 EXECUTING 06-users (in step 06-users @ ): /home/ec2-user/data-models-web-redshift-1.3.1/web/v1/redshift/sql-runner/sql/standard/04-users/01-main/06-users.sql
2023/05/29 09:34:09 SUCCESS: 06-users (step 06-users @ target ), ROWS AFFECTED: -1
2023/05/29 09:34:09 EXECUTING 07-users-metadata (in step 07-users-metadata @ ): /home/ec2-user/data-models-web-redshift-1.3.1/web/v1/redshift/sql-runner/sql/standard/04-users/01-main/07-users-metadata.sql
2023/05/29 09:34:09 SUCCESS: 07-users-metadata (step 07-users-metadata @ target ), ROWS AFFECTED: 1
2023/05/29 09:34:09 EXECUTING 08-commit-users (in step 08-commit-users @ ): /home/ec2-user/data-models-web-redshift-1.3.1/web/v1/redshift/sql-runner/sql/standard/04-users/01-main/08-commit-users.sql
2023/05/29 09:34:09 SUCCESS: 08-commit-users (step 08-commit-users @ target ), ROWS AFFECTED: -1
2023/05/29 09:34:09 SUCCESS: 9 queries executed against 1 targets
run_config: done with playbook: standard/04-users/01-users-main

run_config: starting playbook: standard/01-base/99-base-complete
2023/05/29 09:34:09 EXECUTING 98-base-manifest (in step 98-base-manifest @ ): /home/ec2-user/data-models-web-redshift-1.3.1/web/v1/redshift/sql-runner/sql/standard/01-base/99-complete/98-base-manifest.sql
2023/05/29 09:34:10 SUCCESS: 98-base-manifest (step 98-base-manifest @ target ), ROWS AFFECTED: -1
2023/05/29 09:34:10 EXECUTING 99-base-cleanup (in step 99-base-cleanup @ ): /home/ec2-user/data-models-web-redshift-1.3.1/web/v1/redshift/sql-runner/sql/standard/01-base/99-complete/99-base-cleanup.sql
2023/05/29 09:34:10 SUCCESS: 99-base-cleanup (step 99-base-cleanup @ target ), ROWS AFFECTED: -1
2023/05/29 09:34:10 SUCCESS: 2 queries executed against 1 targets
run_config: done with playbook: standard/01-base/99-base-complete

run_config: starting playbook: standard/02-page-views/99-page-views-complete
2023/05/29 09:34:10 EXECUTING 98-drop-base-staged (in step 98-drop-base-staged @ ): /home/ec2-user/data-models-web-redshift-1.3.1/web/v1/redshift/sql-runner/sql/standard/02-page-views/99-complete/98-drop-base-staged.sql
2023/05/29 09:34:10 SUCCESS: 98-drop-base-staged (step 98-drop-base-staged @ target ), ROWS AFFECTED: -1
2023/05/29 09:34:10 EXECUTING 99-page-views-cleanup (in step 99-page-views-cleanup @ ): /home/ec2-user/data-models-web-redshift-1.3.1/web/v1/redshift/sql-runner/sql/standard/02-page-views/99-complete/99-page-views-cleanup.sql
2023/05/29 09:34:10 SUCCESS: 99-page-views-cleanup (step 99-page-views-cleanup @ target ), ROWS AFFECTED: -1
2023/05/29 09:34:10 SUCCESS: 2 queries executed against 1 targets
run_config: done with playbook: standard/02-page-views/99-page-views-complete

run_config: starting playbook: standard/03-sessions/99-sessions-complete
2023/05/29 09:34:10 EXECUTING 98-truncate-page-views-staged (in step 98-truncate-page-views-staged @ ): /home/ec2-user/data-models-web-redshift-1.3.1/web/v1/redshift/sql-runner/sql/standard/03-sessions/99-complete/98-truncate-page-views-staged.sql
2023/05/29 09:34:10 SUCCESS: 98-truncate-page-views-staged (step 98-truncate-page-views-staged @ target ), ROWS AFFECTED: -1
2023/05/29 09:34:10 EXECUTING 99-sessions-cleanup (in step 99-sessions-cleanup @ ): /home/ec2-user/data-models-web-redshift-1.3.1/web/v1/redshift/sql-runner/sql/standard/03-sessions/99-complete/99-sessions-cleanup.sql
2023/05/29 09:34:10 SUCCESS: 99-sessions-cleanup (step 99-sessions-cleanup @ target ), ROWS AFFECTED: -1
2023/05/29 09:34:10 SUCCESS: 2 queries executed against 1 targets
run_config: done with playbook: standard/03-sessions/99-sessions-complete

run_config: starting playbook: standard/04-users/99-users-complete
2023/05/29 09:34:10 EXECUTING 98-manifest-and-truncate (in step 98-manifest-and-truncate @ ): /home/ec2-user/data-models-web-redshift-1.3.1/web/v1/redshift/sql-runner/sql/standard/04-users/99-complete/98-manifest-and-truncate.sql
2023/05/29 09:34:10 SUCCESS: 98-manifest-and-truncate (step 98-manifest-and-truncate @ target ), ROWS AFFECTED: -1
2023/05/29 09:34:10 EXECUTING 99-users-cleanup (in step 99-users-cleanup @ ): /home/ec2-user/data-models-web-redshift-1.3.1/web/v1/redshift/sql-runner/sql/standard/04-users/99-complete/99-users-cleanup.sql
2023/05/29 09:34:10 SUCCESS: 99-users-cleanup (step 99-users-cleanup @ target ), ROWS AFFECTED: -1
2023/05/29 09:34:10 SUCCESS: 2 queries executed against 1 targets
run_config: done with playbook: standard/04-users/99-users-complete

run_config: Removing playbook file

No rows are affected on the entire playbook and there’s plenty of data on atomic.events.

Am I missing something here?

2

Hey there!

Could you maybe share the contents of the datamodeling.json file you’re using? It may be the case that the time window for which the SQL-runner model is looking for events doesn’t overlap with any events you have in your warehouse. When did you start using Snowplow and what is the earliest collector_tstamp for a Snowplow event in your atomic events table?

Can I also ask why you’re looking to use our SQL-runner data models instead of our dbt data models (like here)? Our dbt data models support many more use cases and are updated frequently, whereas SQL-runner is only updated if we find glaring bugs with the data models. The last commit for our SQL-runner data models was in December of 2022, whereas our dbt snowplow-web model was updated last week!

Let me know,
Emiel

3

I think the base module playbook is what you’d be after to debug but also agree that using the dbt models is preferable!