5.3 Verifying Load Jobs and Output
Verifying data load jobs and setting up notifications are essential for ensuring data pipelines are running correctly and for quickly responding to any issues. The process varies depending on the cloud data warehouse
Snowflake
🡆Verifying Data Loads: You can use the Query History interface in the Snowflake web console to review COPY INTO You can also query the COPY_HISTORY view to see a record of all data loading activities . For continuous loading with Snowpipe, you can check the LOAD_HISTORY view and the Snowpipe monitoring logs .
🡆Setting up Notifications: Snowflake allows you to create Alerts to send notifications or perform actions when data meets certain conditions. You can also configure Notification Integrations to route these alerts to external services like email, cloud provider queues (e.g., AWS SNS), or webhooks for platforms like Slack .
Google Cloud BigQuery
🡆Verifying Data Loads: You can use the BigQuery UI or the list REST API to view the status of all executed load jobs. You can also check the status property of a job resource programmatically to see if it completed successfully .
🡆Setting up Notifications: You can set up metric-based alerting policies in Cloud Monitoring to send notifications when a metric (e.g., job slot usage) crosses a threshold . For more advanced notifications, you can create a Pub/Sub topic that receives job status updates, which can then trigger other processes or alerts.
Amazon Redshift
🡆Verifying Data Loads: You can query system tables like STL_LOAD_COMMITS and SVL_S3LOG to review and troubleshoot the history and errors of COPY command jobs . It is also considered a best practice to perform a post-load verification using system views to ensure the data loaded correctly .
🡆Setting up Notifications: You can set up CloudWatch alarms to trigger notifications through Amazon SNS when a performance metric exceeds a certain threshold. Additionally, you can integrate Amazon S3 events with Redshift to automatically load new files