Deep Dive into Dremio's File-based Auto Ingestion into Apache Iceberg Tables

Manually orchestrating data pipelines to handle ever-increasing volumes of data can be both time-consuming and error-prone. Enter Dremio Auto-Ingest, a game-changing feature that simplifies the process of loading data into Apache Iceberg tables. With Auto-Ingest, you can create event-driven pipelines that automatically respond to changes in your object storage systems, such as new files being uploaded to Amazon S3. This approach eliminates the need for constant manual intervention, enabling real-time or near-real-time updates to your Iceberg tables. Whether you’re ingesting structured CSV data, semi-structured JSON files, or compact Parquet formats, Dremio Auto-Ingest ensures a seamless, reliable pipeline. But why choose Auto-Ingest over traditional methods? The answer lies in its ability to handle ingestion challenges like deduplication, error handling, and custom formatting, all while integrating smoothly with modern cloud infrastructure. Understanding Auto-Ingest for Apache Iceberg To fully appreciate the power of Dremio Auto-Ingest, it’s important to understand the core components and how they work together. At its heart, Auto-Ingest is designed to create a seamless pipeline that transfers files from object storage into Apache Iceberg tables with minimal manual intervention. Let’s break it down. What is a Pipe Object? The pipe object is the central feature enabling Auto-Ingest. Think of it as a pre-configured connection between your cloud storage and an Iceberg table. The pipe listens for events, such as the arrival of a new file, and automatically triggers the ingestion process. This eliminates the need for periodic manual data loads or complex batch scripts. Here’s what makes a pipe object powerful: Notification Provider: Specifies the mechanism for event detection, such as AWS SQS for Amazon S3. Notification Queue Reference: Points to the event queue where file changes are registered. Deduplication: Ensures no duplicate files are ingested, even if files are re-uploaded or processed multiple times. Flexible Configuration: Allows you to define file formats, custom settings, and error-handling rules. How Does Auto-Ingest Work? Auto-Ingest leverages an event-driven model: A file is added or updated in the storage location (e.g., an S3 bucket). A notification is sent to the queue specified in the pipe configuration. The pipe detects the notification and triggers the ingestion process using the COPY INTO command to move data into the Iceberg table. This approach is both reactive and efficient, ensuring that your data remains fresh without the overhead of constant polling or manual triggers. Benefits of Using Auto-Ingest Why choose Auto-Ingest for your Iceberg tables? Here are some key benefits: Real-Time Updates: Ensure your Iceberg tables always reflect the latest data. Simplified Pipeline Management: Replace complex, custom ingestion scripts with a single declarative configuration. Data Quality Assurance: Built-in deduplication and error-handling mechanisms help maintain clean, accurate datasets. Scalability: Auto-Ingest works seamlessly with cloud-native object storage, enabling pipelines that scale with your data. By combining the power of Apache Iceberg with Dremio’s Auto-Ingest, you can build modern, efficient pipelines that support both analytical and operational workloads with ease. Step-by-Step Guide: Setting Up Auto-Ingest By following these steps, you can automate data ingestion from cloud storage and ensure seamless integration with your data lakehouse. 1. Prerequisites Before creating an Auto-Ingest pipeline, ensure the following: Cloud Storage Setup: Configure your storage location (e.g., Amazon S3) as a source in Dremio. Notification Service: Set up an event notification provider, such as AWS SQS, to monitor changes in the storage location. Apache Iceberg Table: Ensure the target table exists and is properly configured in Dremio. Supported File Formats: Verify that your files are in one of the supported formats: CSV, JSON, or Parquet. 2. Creating a Pipe Object The CREATE PIPE command is the foundation of the Auto-Ingest setup. It connects your storage location to an Iceberg table, specifying ingestion parameters. Syntax CREATE PIPE [ IF NOT EXISTS ] [ DEDUPE_LOOKBACK_PERIOD ] NOTIFICATION_PROVIDER NOTIFICATION_QUEUE_REFERENCE AS COPY INTO [ AT BRANCH ] FROM '@ ' FILE_FORMAT ' ' [( )] Key Parameters DEDUPE_LOOKBACK_PERIOD: Defines the time window (in days) for deduplication. Default is 14 days. NOTIFICATION_PROVIDER: Specifies the event notification system, such as AWS_SQS for Amazon S3. NOTIFICATION_QUEUE_REFERENCE: Points to the notification queue (e.g., the ARN of an SQS queue). COPY INTO: Specifies the target Iceberg table and optional branch. @ : Refers to the source storage location configured in Dremio. File Format Options: Custom configurations for CSV, JSON, or Parquet files. Examples Basic Pipe for CSV Files CREATE PIPE my_pipe NOTIFICATION_PROVIDER AWS_SQS NOTIFICATION_QUEUE_REFERENCE 'arn:aws:sqs:us-east-1:123456789012:my-queue' AS COPY INTO sales_data FROM '@s3_source/data_folder' FILE_FORMAT 'csv'; Pipe with Deduplication CREATE PIPE deduped_pipe DEDUPE_LOOKBACK_PERIOD 7 NOTIFICATION_PROVIDER AWS_SQS NOTIFICATION_QUEUE_REFERENCE 'arn:aws:sqs:us-east-1:123456789012:dedupe-queue' AS COPY INTO analytics_table FROM '@s3_source/analytics' FILE_FORMAT 'parquet'; 3. Customizing File Formats Dremio allows you to tailor the ingestion process based on your file type and data requirements. Here’s how to configure each format: CSV Options: Delimiters (FIELD_DELIMITER) Null handling (EMPTY_AS_NULL) Header extraction (EXTRACT_HEADER) Error handling (ON_ERROR) JSON Options: Date and time formatting (DATE_FORMAT, TIME_FORMAT) Null replacements (NULL_IF) Parquet Options: Simplified setup with error handling (ON_ERROR) Example for CSV with custom settings: CREATE PIPE custom_csv_pipe NOTIFICATION_PROVIDER AWS_SQS NOTIFICATION_QUEUE_REFERENCE 'arn:aws:sqs:us-east-1:123456789012:csv-queue' AS COPY INTO transactions_table FROM '@s3_source/csv_data' FILE_FORMAT 'csv' (FIELD_DELIMITER '|', EXTRACT_HEADER 'true', ON_ERROR 'skip_file'); 4. Error Handling Errors during ingestion are inevitable, but Dremio’s Auto-Ingest provides robust handling options: ON_ERROR Options: abort: Stops the process at the first error (default for JSON and Parquet). continue: Skips faulty rows but processes valid ones (CSV only). skip_file: Skips the entire file if any error occurs. For example: CREATE PIPE error_handling_pipe NOTIFICATION_PROVIDER AWS_SQS NOTIFICATION_QUEUE_REFERENCE 'arn:aws:sqs:us-east-1:123456789012:error-queue' AS COPY INTO error_log_table FROM '@s3_source/faulty_data' FILE_FORMAT 'json' (ON_ERROR 'skip_file'); With your pipe configured, Dremio automatically monitors your storage for changes and ingests new files into the target Iceberg table. This setup provides a scalable, reliable pipeline for all your data ingestion needs. Real-World Use Cases for Dremio Auto-Ingest Dremio’s Auto-Ingest for Apache Iceberg tables offers significant advantages across a variety of data engineering scenarios. Whether you’re building real-time pipelines or automating batch data processing, Auto-Ingest provides the flexibility and automation necessary to simplify workflows. Here are some real-world use cases to illustrate its impact. 1. Streaming Data Pipelines Scenario: A smart city project collects real-time sensor data (e.g., temperature, traffic flow, air quality) from IoT devices. This data is stored as JSON files in an S3 bucket, and analytics teams require instant updates in their data warehouse for real-time dashboards. Solution: Use Dremio Auto-Ingest with a pipe object that listens to the S3 bucket. Configure the pipe to process JSON files and load them into an Iceberg table. Leverage ON_ERROR settings to gracefully handle malformed sensor data. Example Configuration: CREATE PIPE streaming_pipe NOTIFICATION_PROVIDER AWS_SQS NOTIFICATION_QUEUE_REFERENCE 'arn:aws:sqs:us-west-2:123456789012:sensor-queue' AS COPY INTO smart_city.sensor_data FROM '@iot_source/live_data' FILE_FORMAT 'json' (ON_ERROR 'skip_file'); Outcome: Real-time dashboards reflect the latest sensor data without manual intervention. Faulty data is isolated for later analysis, ensuring system stability. 2. Batch Data Processing Scenario: A retail company ingests daily sales logs in CSV format from its regional branches into a central data lake. These logs must be processed nightly and appended to a historical sales Iceberg table. Solution: Configure an Auto-Ingest pipe to monitor the S3 bucket where sales logs are uploaded. Set a deduplication lookback period to avoid reprocessing files if logs are accidentally re-uploaded. Example Configuration: CREATE PIPE daily_batch_pipe DEDUPE_LOOKBACK_PERIOD 7 NOTIFICATION_PROVIDER AWS_SQS NOTIFICATION_QUEUE_REFERENCE 'arn:aws:sqs:us-east-1:123456789012:sales-queue' AS COPY INTO retail.sales_history FROM '@s3_source/sales_logs' FILE_FORMAT 'csv' (EXTRACT_HEADER 'true', EMPTY_AS_NULL 'true'); Outcome: Daily sales logs are automatically appended to the historical table. The deduplication window ensures no duplicate records are ingested. 3. Data Lakehouse Modernization Scenario: A financial services firm is transitioning from a traditional data warehouse to a modern lakehouse architecture. The team wants to automate ingestion from various sources (e.g., transactional Parquet files and JSON logs) into Iceberg tables for unified analytics. Solution: Use multiple Auto-Ingest pipes to handle ingestion for different file types and schemas. Configure branch-specific ingestion for staging and production environments. Example Configuration: Parquet Transactions: CREATE PIPE transactions_pipe NOTIFICATION_PROVIDER AWS_SQS NOTIFICATION_QUEUE_REFERENCE 'arn:aws:sqs:us-east-2:123456789012:transactions-queue' AS COPY INTO finance.transactions FROM '@finance_source/transactions' FILE_FORMAT 'parquet'; JSON Application Logs: Copy code CREATE PIPE logs_pipe NOTIFICATION_PROVIDER AWS_SQS NOTIFICATION_QUEUE_REFERENCE 'arn:aws:sqs:us-east-2:123456789012:logs-queue' AS COPY INTO finance.app_logs FROM '@logs_source/application' FILE_FORMAT 'json' (DATE_FORMAT 'YYYY-MM-DD', TIME_FORMAT 'HH24:MI:SS'); Outcome: Unified, structured Iceberg tables ready for analytical queries. Improved agility with automated pipelines for different data sources. 4. Event-Driven Reporting Scenario: A marketing team tracks user engagement metrics (e.g., clicks, time on site, purchases) stored as CSV files in real-time. Reports must be updated immediately after new data arrives. Solution: Use an Auto-Ingest pipe with an AWS_SQS notification provider to ensure new engagement files are ingested as soon as they are uploaded. Example Configuration: CREATE PIPE engagement_pipe NOTIFICATION_PROVIDER AWS_SQS NOTIFICATION_QUEUE_REFERENCE 'arn:aws:sqs:us-west-1:123456789012:engagement-queue' AS COPY INTO marketing.user_engagement FROM '@engagement_source/metrics' FILE_FORMAT 'csv' (FIELD_DELIMITER ',', EXTRACT_HEADER 'true'); Outcome: Marketing reports are updated in near-real-time, enabling faster decision-making. Automated ingestion removes the need for manual ETL processes. These use cases showcase how Dremio Auto-Ingest can be a versatile and powerful tool for a wide range of data engineering challenges. Whether your focus is on real-time data processing, batch workflows, or transitioning to a lakehouse architecture, Auto-Ingest simplifies and enhances your pipeline capabilities. Best Practices and Considerations for Dremio Auto-Ingest To get the most out of Dremio Auto-Ingest for Apache Iceberg tables, it's essential to follow best practices and understand key considerations. These guidelines will help ensure your ingestion pipelines are reliable, efficient, and optimized for performance. 1. Optimize Deduplication Settings What It Does: The DEDUPE_LOOKBACK_PERIOD parameter ensures that duplicate files (e.g., files with the same name uploaded multiple times) are not ingested repeatedly. Best Practices: Set an appropriate lookback period based on your ingestion frequency: For high-frequency updates (e.g., hourly ingestion), a shorter period (1–3 days) is sufficient. For batch workflows with periodic reuploads, a longer window (7–14 days) may be needed. Avoid setting the period to 0 unless you are certain duplicates are not an issue, as it disables deduplication. Example: CREATE PIPE deduped_pipe DEDUPE_LOOKBACK_PERIOD 7 NOTIFICATION_PROVIDER AWS_SQS NOTIFICATION_QUEUE_REFERENCE 'arn:aws:sqs:us-east-1:123456789012:dedupe-queue' AS COPY INTO my_table FROM '@s3_source/folder' FILE_FORMAT 'json'; 2. Organize Storage for Better Performance Why It Matters: Properly structured storage locations improve ingestion speed and reduce processing overhead. Best Practices: Use folder-based organization in your storage buckets (e.g., /year/month/day/) for easier file management and regex-based ingestion. Keep related files in the same folder to avoid ingesting unrelated data by mistake. Avoid deeply nested directory structures, as they can slow down file scanning. 3. Choose the Right File Format Impact of File Format: Different file formats affect storage size, query performance, and ingestion speed. Best Practices: Use Parquet for columnar storage and analytics-heavy workloads due to its efficient storage and compression. Opt for CSV or JSON for semi-structured data but ensure proper formatting (e.g., consistent delimiters, headers, and escaping). Test ingestion performance with small sample files before committing to large-scale pipelines. 4. Leverage Error Handling Options Why It Matters: Errors during ingestion can interrupt pipelines or lead to data inconsistencies. Best Practices: Use ON_ERROR 'skip_file' to bypass files with errors and prevent pipeline interruptions. Regularly monitor the sys.copy_errors_history table for ingestion errors and address recurring issues. For non-critical pipelines, consider ON_ERROR 'continue' (CSV only) to process valid rows even if some are faulty. Example: CREATE PIPE error_handling_pipe NOTIFICATION_PROVIDER AWS_SQS NOTIFICATION_QUEUE_REFERENCE 'arn:aws:sqs:us-east-1:123456789012:error-queue' AS COPY INTO my_table FROM '@s3_source/folder' FILE_FORMAT 'csv' (ON_ERROR 'continue'); 5. Monitor and Troubleshoot Pipelines Monitoring Tools: System Tables: Query sys.copy_errors_history to review errors during ingestion. Job Logs: Check job logs in Dremio for detailed error messages and ingestion stats. Common Troubleshooting Tips: Notification Issues: Ensure the SQS queue ARN matches the one specified in the NOTIFICATION_QUEUE_REFERENCE. File Format Mismatches: Double-check that the specified file format aligns with the actual file type (e.g., don’t label a Parquet file as CSV). Deduplication Failures: Verify that the deduplication period is set correctly and files aren’t inadvertently re-ingested due to naming conflicts. 6. Optimize Regex and File Selection Why It Matters: Using overly broad regex patterns or processing unnecessary files can impact pipeline performance. Best Practices: Write regex patterns that are as specific as possible to match only the files you need. Avoid processing large directories unless required. Use the FILES clause or specific folder paths to limit scope. Example: CREATE PIPE regex_pipe NOTIFICATION_PROVIDER AWS_SQS NOTIFICATION_QUEUE_REFERENCE 'arn:aws:sqs:us-west-2:123456789012:regex-queue' AS COPY INTO my_table FROM '@s3_source/folder' REGEX '^2024/11/.*.csv' FILE_FORMAT 'csv'; 7. Plan for Schema Evolution Why It Matters: Iceberg tables support schema evolution, but it’s crucial to manage changes thoughtfully to avoid ingestion failures. Best Practices: Test schema changes in a staging environment before applying them to production pipelines. Use Iceberg’s branching capabilities to isolate schema updates during development. Validate data types and formats in source files to avoid mismatches with the target table schema. 8. Integrate with Data Lakehouse Workflows Why It Matters: Auto-Ingest simplifies transitioning to a lakehouse architecture, but aligning with broader workflows ensures smooth integration. Best Practices: Combine Auto-Ingest with Dremio’s SQL-based querying to enable seamless analytics on ingested data. Use Iceberg’s time-travel feature to track historical changes and validate pipeline performance over time. By following these best practices and considerations, you can ensure your Dremio Auto-Ingest pipelines are robust, efficient, and well-suited to your data engineering needs. These guidelines will help you avoid common pitfalls and fully leverage the power of automated ingestion for Apache Iceberg tables. Troubleshooting and Debugging Auto-Ingest Pipelines Even with a robust Auto-Ingest setup, you may encounter issues during the ingestion process. Dremio’s system tables, such as SYS.COPY_ERRORS_HISTORY, provide detailed insights into ingestion errors, making it easier to diagnose and resolve problems. This section outlines common issues and how to effectively use the system table to debug your pipelines. 1. Common Issues and Resolutions Notification Configuration Problems Symptom: The pipe does not respond to new files being uploaded. Resolution: Verify the NOTIFICATION_PROVIDER is configured correctly (e.g., AWS_SQS for S3). Ensure the NOTIFICATION_QUEUE_REFERENCE points to the correct ARN of your event notification queue. File Format Mismatch Symptom: The pipeline fails with file parsing errors. Resolution: Double-check that the FILE_FORMAT in your pipe configuration matches the actual format of the uploaded files. Validate format-specific options (e.g., delimiter, null handling) for correctness. Partial or Skipped File Loads Symptom: Some files are partially loaded or skipped entirely. Resolution: Use the SYS.COPY_ERRORS_HISTORY table to identify problematic files and the reasons for rejection. Adjust error-handling options (ON_ERROR) in your pipe to match your tolerance for bad records. 2. Using the SYS.COPY_ERRORS_HISTORY Table The SYS.COPY_ERRORS_HISTORY table logs detailed information about COPY INTO jobs where records were rejected due to parsing or schema issues. This includes jobs configured with ON_ERROR 'continue' or ON_ERROR 'skip_file'. Key Columns in the Table executed_at: The timestamp when the job was executed. job_id: The unique identifier of the COPY INTO job. table_name: The target Iceberg table for the job. user_name: The username of the individual who ran the job. file_path: The path of the file with rejected records. file_state: PARTIALLY_LOADED: Some records were loaded, but others were rejected. SKIPPED: No records were loaded due to file-level errors. records_loaded_count: The number of successfully ingested records. records_rejected_count: The number of records rejected due to errors. Example Query: Identifying Problematic Files To view details about rejected files for a specific table: SELECT executed_at, job_id, file_path, file_state, records_rejected_count FROM SYS.COPY_ERRORS_HISTORY WHERE table_name = 'my_table' ORDER BY executed_at DESC; This query highlights: When ingestion errors occurred. Which files were affected. Whether files were partially loaded or skipped. 3. Drilling into Error Details Once you identify a problematic job using the job_id, you can use the copy_errors() function to extract detailed error information. Example: Retrieving Error Details SELECT * FROM copy_errors('1aacb195-ca94-ec4c-2b01-ecddac81a900', 'my_table'); This query provides granular information about errors encountered during the ingestion process for the specified job. 4. Best Practices for Debugging Proactive Monitoring Regularly query the SYS.COPY_ERRORS_HISTORY table to track ingestion health. Set alerts for high records_rejected_count values to identify recurring issues. Validate Source Data Audit source files for schema inconsistencies or formatting errors. Ensure files match the expected format (e.g., proper delimiters for CSV). Tuning Error Handling Use ON_ERROR 'skip_file' for critical pipelines where partial loads are unacceptable. Opt for ON_ERROR 'continue' in cases where maximum data recovery is desired, especially for CSV files. Housekeeping the System Table The SYS.COPY_ERRORS_HISTORY table can grow significantly over time. Manage its size using these configuration keys: dremio.system_iceberg_tables.record_lifespan_in_millis: Retains history for a specified number of days (default is 7). dremio.system_iceberg_tables.housekeeping_thread_frequency_in_millis: Controls how frequently old records are removed (default is daily). 5. Common Query Patterns for Debugging Find Recently Skipped Files SELECT file_path, file_state, records_rejected_count FROM SYS.COPY_ERRORS_HISTORY WHERE file_state = 'SKIPPED' ORDER BY executed_at DESC; Analyze Partially Loaded Files SELECT file_path, records_loaded_count, records_rejected_count FROM SYS.COPY_ERRORS_HISTORY WHERE file_state = 'PARTIALLY_LOADED' ORDER BY executed_at DESC; By leveraging the SYS.COPY_ERRORS_HISTORY table and related debugging tools, you can effectively monitor and resolve issues in your Auto-Ingest pipelines. These capabilities ensure your pipelines are resilient and capable of handling a wide variety of data ingestion scenarios with minimal disruption. Conclusion Dremio Auto-Ingest for Apache Iceberg tables brings a new level of automation and simplicity to data ingestion workflows. By leveraging event-driven pipelines, you can reduce manual intervention, ensure data freshness, and streamline the integration of your object storage systems with Iceberg tables. From real-time updates to batch processing, Auto-Ingest handles diverse use cases with ease, offering powerful features like deduplication, error handling, and format-specific customization. By following best practices, monitoring your pipelines, and troubleshooting effectively, you can create reliable and efficient data ingestion workflows that scale with your business needs. Whether you're modernizing your data lakehouse architecture or building advanced analytics pipelines, Dremio Auto-Ingest is a must-have tool to unlock the full potential of your data. Blog: What is a Data Lakehouse and a Table Format? Free Copy of Apache Iceberg the Definitive Guide Free Apache Iceberg Crash Course Lakehouse Catalog Course Iceberg Lakehouse Engineering Video Playlist Manually orchestrating data pipelines to handle ever-increasing volumes of data can be both time-consuming and error-prone. Enter Dremio Auto-Ingest , a game-changing feature that simplifies the process of loading data into Apache Iceberg tables. Dremio Auto-Ingest Apache Iceberg With Auto-Ingest, you can create event-driven pipelines that automatically respond to changes in your object storage systems, such as new files being uploaded to Amazon S3. This approach eliminates the need for constant manual intervention, enabling real-time or near-real-time updates to your Iceberg tables. Whether you’re ingesting structured CSV data, semi-structured JSON files, or compact Parquet formats, Dremio Auto-Ingest ensures a seamless, reliable pipeline. But why choose Auto-Ingest over traditional methods? The answer lies in its ability to handle ingestion challenges like deduplication, error handling, and custom formatting, all while integrating smoothly with modern cloud infrastructure. Understanding Auto-Ingest for Apache Iceberg To fully appreciate the power of Dremio Auto-Ingest, it’s important to understand the core components and how they work together. At its heart, Auto-Ingest is designed to create a seamless pipeline that transfers files from object storage into Apache Iceberg tables with minimal manual intervention. Let’s break it down. Apache Iceberg tables What is a Pipe Object? The pipe object is the central feature enabling Auto-Ingest. Think of it as a pre-configured connection between your cloud storage and an Iceberg table. The pipe listens for events, such as the arrival of a new file, and automatically triggers the ingestion process. This eliminates the need for periodic manual data loads or complex batch scripts. pipe object Here’s what makes a pipe object powerful: Notification Provider: Specifies the mechanism for event detection, such as AWS SQS for Amazon S3. Notification Queue Reference: Points to the event queue where file changes are registered. Deduplication: Ensures no duplicate files are ingested, even if files are re-uploaded or processed multiple times. Flexible Configuration: Allows you to define file formats, custom settings, and error-handling rules. Notification Provider : Specifies the mechanism for event detection, such as AWS SQS for Amazon S3. Notification Provider Notification Queue Reference : Points to the event queue where file changes are registered. Notification Queue Reference Deduplication : Ensures no duplicate files are ingested, even if files are re-uploaded or processed multiple times. Deduplication Flexible Configuration : Allows you to define file formats, custom settings, and error-handling rules. Flexible Configuration How Does Auto-Ingest Work? Auto-Ingest leverages an event-driven model : event-driven model A file is added or updated in the storage location (e.g., an S3 bucket). A notification is sent to the queue specified in the pipe configuration. The pipe detects the notification and triggers the ingestion process using the COPY INTO command to move data into the Iceberg table. A file is added or updated in the storage location (e.g., an S3 bucket). A notification is sent to the queue specified in the pipe configuration. The pipe detects the notification and triggers the ingestion process using the COPY INTO command to move data into the Iceberg table. COPY INTO This approach is both reactive and efficient, ensuring that your data remains fresh without the overhead of constant polling or manual triggers. Benefits of Using Auto-Ingest Why choose Auto-Ingest for your Iceberg tables? Here are some key benefits: Real-Time Updates: Ensure your Iceberg tables always reflect the latest data. Simplified Pipeline Management: Replace complex, custom ingestion scripts with a single declarative configuration. Data Quality Assurance: Built-in deduplication and error-handling mechanisms help maintain clean, accurate datasets. Scalability: Auto-Ingest works seamlessly with cloud-native object storage, enabling pipelines that scale with your data. Real-Time Updates : Ensure your Iceberg tables always reflect the latest data. Real-Time Updates Simplified Pipeline Management : Replace complex, custom ingestion scripts with a single declarative configuration. Simplified Pipeline Management Data Quality Assurance : Built-in deduplication and error-handling mechanisms help maintain clean, accurate datasets. Data Quality Assurance Scalability : Auto-Ingest works seamlessly with cloud-native object storage, enabling pipelines that scale with your data. Scalability By combining the power of Apache Iceberg with Dremio’s Auto-Ingest, you can build modern, efficient pipelines that support both analytical and operational workloads with ease. Step-by-Step Guide: Setting Up Auto-Ingest By following these steps, you can automate data ingestion from cloud storage and ensure seamless integration with your data lakehouse. 1. Prerequisites Before creating an Auto-Ingest pipeline, ensure the following: Cloud Storage Setup: Configure your storage location (e.g., Amazon S3) as a source in Dremio. Notification Service: Set up an event notification provider, such as AWS SQS, to monitor changes in the storage location. Apache Iceberg Table: Ensure the target table exists and is properly configured in Dremio. Supported File Formats: Verify that your files are in one of the supported formats: CSV, JSON, or Parquet. Cloud Storage Setup : Configure your storage location (e.g., Amazon S3) as a source in Dremio. Cloud Storage Setup Notification Service : Set up an event notification provider, such as AWS SQS, to monitor changes in the storage location. Notification Service Apache Iceberg Table : Ensure the target table exists and is properly configured in Dremio. Apache Iceberg Table Supported File Formats : Verify that your files are in one of the supported formats: CSV, JSON, or Parquet. Supported File Formats 2. Creating a Pipe Object The CREATE PIPE command is the foundation of the Auto-Ingest setup. It connects your storage location to an Iceberg table, specifying ingestion parameters. CREATE PIPE Syntax CREATE PIPE [ IF NOT EXISTS ] [ DEDUPE_LOOKBACK_PERIOD ] NOTIFICATION_PROVIDER NOTIFICATION_QUEUE_REFERENCE AS COPY INTO [ AT BRANCH ] FROM '@ ' FILE_FORMAT ' ' [( )] CREATE PIPE [ IF NOT EXISTS ] [ DEDUPE_LOOKBACK_PERIOD ] NOTIFICATION_PROVIDER NOTIFICATION_QUEUE_REFERENCE AS COPY INTO [ AT BRANCH ] FROM '@ ' FILE_FORMAT ' ' [( )] Key Parameters DEDUPE_LOOKBACK_PERIOD: Defines the time window (in days) for deduplication. Default is 14 days. NOTIFICATION_PROVIDER: Specifies the event notification system, such as AWS_SQS for Amazon S3. NOTIFICATION_QUEUE_REFERENCE: Points to the notification queue (e.g., the ARN of an SQS queue). COPY INTO: Specifies the target Iceberg table and optional branch. @ : Refers to the source storage location configured in Dremio. File Format Options: Custom configurations for CSV, JSON, or Parquet files. DEDUPE_LOOKBACK_PERIOD: Defines the time window (in days) for deduplication. Default is 14 days. DEDUPE_LOOKBACK_PERIOD: NOTIFICATION_PROVIDER: Specifies the event notification system, such as AWS_SQS for Amazon S3. NOTIFICATION_PROVIDER: NOTIFICATION_QUEUE_REFERENCE: Points to the notification queue (e.g., the ARN of an SQS queue). NOTIFICATION_QUEUE_REFERENCE: COPY INTO: Specifies the target Iceberg table and optional branch. COPY INTO: @ : Refers to the source storage location configured in Dremio. @ : File Format Options: Custom configurations for CSV, JSON, or Parquet files. File Format Options: Examples Basic Pipe for CSV Files CREATE PIPE my_pipe NOTIFICATION_PROVIDER AWS_SQS NOTIFICATION_QUEUE_REFERENCE 'arn:aws:sqs:us-east-1:123456789012:my-queue' AS COPY INTO sales_data FROM '@s3_source/data_folder' FILE_FORMAT 'csv'; CREATE PIPE my_pipe NOTIFICATION_PROVIDER AWS_SQS NOTIFICATION_QUEUE_REFERENCE 'arn:aws:sqs:us-east-1:123456789012:my-queue' AS COPY INTO sales_data FROM '@s3_source/data_folder' FILE_FORMAT 'csv'; Pipe with Deduplication CREATE PIPE deduped_pipe DEDUPE_LOOKBACK_PERIOD 7 NOTIFICATION_PROVIDER AWS_SQS NOTIFICATION_QUEUE_REFERENCE 'arn:aws:sqs:us-east-1:123456789012:dedupe-queue' AS COPY INTO analytics_table FROM '@s3_source/analytics' FILE_FORMAT 'parquet'; CREATE PIPE deduped_pipe DEDUPE_LOOKBACK_PERIOD 7 NOTIFICATION_PROVIDER AWS_SQS NOTIFICATION_QUEUE_REFERENCE 'arn:aws:sqs:us-east-1:123456789012:dedupe-queue' AS COPY INTO analytics_table FROM '@s3_source/analytics' FILE_FORMAT 'parquet'; 3. Customizing File Formats Dremio allows you to tailor the ingestion process based on your file type and data requirements. Here’s how to configure each format: CSV Options: CSV Options: Delimiters (FIELD_DELIMITER) Null handling (EMPTY_AS_NULL) Header extraction (EXTRACT_HEADER) Error handling (ON_ERROR) Delimiters ( FIELD_DELIMITER ) Delimiters ( FIELD_DELIMITER Null handling ( EMPTY_AS_NULL ) Null handling ( EMPTY_AS_NULL Header extraction ( EXTRACT_HEADER ) Header extraction ( EXTRACT_HEADER Error handling ( ON_ERROR ) Error handling ( ON_ERROR JSON Options: JSON Options: Date and time formatting (DATE_FORMAT, TIME_FORMAT) Null replacements (NULL_IF) Date and time formatting ( DATE_FORMAT , TIME_FORMAT ) Date and time formatting ( DATE_FORMAT TIME_FORMAT Null replacements ( NULL_IF ) Null replacements ( NULL_IF Parquet Options: Parquet Options: Simplified setup with error handling (ON_ERROR) Simplified setup with error handling ( ON_ERROR ) Simplified setup with error handling ( ON_ERROR Example for CSV with custom settings: CREATE PIPE custom_csv_pipe NOTIFICATION_PROVIDER AWS_SQS NOTIFICATION_QUEUE_REFERENCE 'arn:aws:sqs:us-east-1:123456789012:csv-queue' AS COPY INTO transactions_table FROM '@s3_source/csv_data' FILE_FORMAT 'csv' (FIELD_DELIMITER '|', EXTRACT_HEADER 'true', ON_ERROR 'skip_file'); CREATE PIPE custom_csv_pipe NOTIFICATION_PROVIDER AWS_SQS NOTIFICATION_QUEUE_REFERENCE 'arn:aws:sqs:us-east-1:123456789012:csv-queue' AS COPY INTO transactions_table FROM '@s3_source/csv_data' FILE_FORMAT 'csv' (FIELD_DELIMITER '|', EXTRACT_HEADER 'true', ON_ERROR 'skip_file'); 4. Error Handling Errors during ingestion are inevitable, but Dremio’s Auto-Ingest provides robust handling options: ON_ERROR Options: abort: Stops the process at the first error (default for JSON and Parquet). continue: Skips faulty rows but processes valid ones (CSV only). skip_file: Skips the entire file if any error occurs. abort: Stops the process at the first error (default for JSON and Parquet). abort: continue: Skips faulty rows but processes valid ones (CSV only). continue: skip_file: Skips the entire file if any error occurs. skip_file: For example: CREATE PIPE error_handling_pipe NOTIFICATION_PROVIDER AWS_SQS NOTIFICATION_QUEUE_REFERENCE 'arn:aws:sqs:us-east-1:123456789012:error-queue' AS COPY INTO error_log_table FROM '@s3_source/faulty_data' FILE_FORMAT 'json' (ON_ERROR 'skip_file'); CREATE PIPE error_handling_pipe NOTIFICATION_PROVIDER AWS_SQS NOTIFICATION_QUEUE_REFERENCE 'arn:aws:sqs:us-east-1:123456789012:error-queue' AS COPY INTO error_log_table FROM '@s3_source/faulty_data' FILE_FORMAT 'json' (ON_ERROR 'skip_file'); With your pipe configured, Dremio automatically monitors your storage for changes and ingests new files into the target Iceberg table. This setup provides a scalable, reliable pipeline for all your data ingestion needs. Real-World Use Cases for Dremio Auto-Ingest Dremio’s Auto-Ingest for Apache Iceberg tables offers significant advantages across a variety of data engineering scenarios. Whether you’re building real-time pipelines or automating batch data processing, Auto-Ingest provides the flexibility and automation necessary to simplify workflows. Here are some real-world use cases to illustrate its impact. 1. Streaming Data Pipelines Streaming Data Pipelines Scenario : A smart city project collects real-time sensor data (e.g., temperature, traffic flow, air quality) from IoT devices. This data is stored as JSON files in an S3 bucket, and analytics teams require instant updates in their data warehouse for real-time dashboards. Scenario Solution : Solution Use Dremio Auto-Ingest with a pipe object that listens to the S3 bucket. Configure the pipe to process JSON files and load them into an Iceberg table. Leverage ON_ERROR settings to gracefully handle malformed sensor data. Use Dremio Auto-Ingest with a pipe object that listens to the S3 bucket. Configure the pipe to process JSON files and load them into an Iceberg table. Leverage ON_ERROR settings to gracefully handle malformed sensor data. ON_ERROR Example Configuration : Example Configuration CREATE PIPE streaming_pipe NOTIFICATION_PROVIDER AWS_SQS NOTIFICATION_QUEUE_REFERENCE 'arn:aws:sqs:us-west-2:123456789012:sensor-queue' AS COPY INTO smart_city.sensor_data FROM '@iot_source/live_data' FILE_FORMAT 'json' (ON_ERROR 'skip_file'); CREATE PIPE streaming_pipe NOTIFICATION_PROVIDER AWS_SQS NOTIFICATION_QUEUE_REFERENCE 'arn:aws:sqs:us-west-2:123456789012:sensor-queue' AS COPY INTO smart_city.sensor_data FROM '@iot_source/live_data' FILE_FORMAT 'json' (ON_ERROR 'skip_file'); Outcome: Outcome: Real-time dashboards reflect the latest sensor data without manual intervention. Faulty data is isolated for later analysis, ensuring system stability. Real-time dashboards reflect the latest sensor data without manual intervention. Faulty data is isolated for later analysis, ensuring system stability. 2. Batch Data Processing Scenario: A retail company ingests daily sales logs in CSV format from its regional branches into a central data lake. These logs must be processed nightly and appended to a historical sales Iceberg table. Scenario: Solution: Solution: Configure an Auto-Ingest pipe to monitor the S3 bucket where sales logs are uploaded. Set a deduplication lookback period to avoid reprocessing files if logs are accidentally re-uploaded. Example Configuration: Configure an Auto-Ingest pipe to monitor the S3 bucket where sales logs are uploaded. Set a deduplication lookback period to avoid reprocessing files if logs are accidentally re-uploaded. Example Configuration: CREATE PIPE daily_batch_pipe DEDUPE_LOOKBACK_PERIOD 7 NOTIFICATION_PROVIDER AWS_SQS NOTIFICATION_QUEUE_REFERENCE 'arn:aws:sqs:us-east-1:123456789012:sales-queue' AS COPY INTO retail.sales_history FROM '@s3_source/sales_logs' FILE_FORMAT 'csv' (EXTRACT_HEADER 'true', EMPTY_AS_NULL 'true'); CREATE PIPE daily_batch_pipe DEDUPE_LOOKBACK_PERIOD 7 NOTIFICATION_PROVIDER AWS_SQS NOTIFICATION_QUEUE_REFERENCE 'arn:aws:sqs:us-east-1:123456789012:sales-queue' AS COPY INTO retail.sales_history FROM '@s3_source/sales_logs' FILE_FORMAT 'csv' (EXTRACT_HEADER 'true', EMPTY_AS_NULL 'true'); Outcome: Outcome: Daily sales logs are automatically appended to the historical table. The deduplication window ensures no duplicate records are ingested. Daily sales logs are automatically appended to the historical table. The deduplication window ensures no duplicate records are ingested. 3. Data Lakehouse Modernization Scenario: A financial services firm is transitioning from a traditional data warehouse to a modern lakehouse architecture. The team wants to automate ingestion from various sources (e.g., transactional Parquet files and JSON logs) into Iceberg tables for unified analytics. Scenario: Solution: Solution: Use multiple Auto-Ingest pipes to handle ingestion for different file types and schemas. Configure branch-specific ingestion for staging and production environments. Example Configuration: Parquet Transactions: Parquet Transactions: CREATE PIPE transactions_pipe NOTIFICATION_PROVIDER AWS_SQS NOTIFICATION_QUEUE_REFERENCE 'arn:aws:sqs:us-east-2:123456789012:transactions-queue' AS COPY INTO finance.transactions FROM '@finance_source/transactions' FILE_FORMAT 'parquet'; CREATE PIPE transactions_pipe NOTIFICATION_PROVIDER AWS_SQS NOTIFICATION_QUEUE_REFERENCE 'arn:aws:sqs:us-east-2:123456789012:transactions-queue' AS COPY INTO finance.transactions FROM '@finance_source/transactions' FILE_FORMAT 'parquet'; JSON Application Logs: JSON Application Logs: Copy code CREATE PIPE logs_pipe NOTIFICATION_PROVIDER AWS_SQS NOTIFICATION_QUEUE_REFERENCE 'arn:aws:sqs:us-east-2:123456789012:logs-queue' AS COPY INTO finance.app_logs FROM '@logs_source/application' FILE_FORMAT 'json' (DATE_FORMAT 'YYYY-MM-DD', TIME_FORMAT 'HH24:MI:SS'); Copy code CREATE PIPE logs_pipe NOTIFICATION_PROVIDER AWS_SQS NOTIFICATION_QUEUE_REFERENCE 'arn:aws:sqs:us-east-2:123456789012:logs-queue' AS COPY INTO finance.app_logs FROM '@logs_source/application' FILE_FORMAT 'json' (DATE_FORMAT 'YYYY-MM-DD', TIME_FORMAT 'HH24:MI:SS'); Outcome: Outcome: Unified, structured Iceberg tables ready for analytical queries. Improved agility with automated pipelines for different data sources. Unified, structured Iceberg tables ready for analytical queries. Improved agility with automated pipelines for different data sources. 4. Event-Driven Reporting Scenario: A marketing team tracks user engagement metrics (e.g., clicks, time on site, purchases) stored as CSV files in real-time. Reports must be updated immediately after new data arrives. Scenario: Solution: Solution: Use an Auto-Ingest pipe with an AWS_SQS notification provider to ensure new engagement files are ingested as soon as they are uploaded. Example Configuration: Example Configuration: CREATE PIPE engagement_pipe NOTIFICATION_PROVIDER AWS_SQS NOTIFICATION_QUEUE_REFERENCE 'arn:aws:sqs:us-west-1:123456789012:engagement-queue' AS COPY INTO marketing.user_engagement FROM '@engagement_source/metrics' FILE_FORMAT 'csv' (FIELD_DELIMITER ',', EXTRACT_HEADER 'true'); CREATE PIPE engagement_pipe NOTIFICATION_PROVIDER AWS_SQS NOTIFICATION_QUEUE_REFERENCE 'arn:aws:sqs:us-west-1:123456789012:engagement-queue' AS COPY INTO marketing.user_engagement FROM '@engagement_source/metrics' FILE_FORMAT 'csv' (FIELD_DELIMITER ',', EXTRACT_HEADER 'true'); Outcome: Outcome: Marketing reports are updated in near-real-time, enabling faster decision-making. Automated ingestion removes the need for manual ETL processes. Marketing reports are updated in near-real-time, enabling faster decision-making. Automated ingestion removes the need for manual ETL processes. These use cases showcase how Dremio Auto-Ingest can be a versatile and powerful tool for a wide range of data engineering challenges. Whether your focus is on real-time data processing, batch workflows, or transitioning to a lakehouse architecture, Auto-Ingest simplifies and enhances your pipeline capabilities. Best Practices and Considerations for Dremio Auto-Ingest To get the most out of Dremio Auto-Ingest for Apache Iceberg tables, it's essential to follow best practices and understand key considerations. These guidelines will help ensure your ingestion pipelines are reliable, efficient, and optimized for performance. 1. Optimize Deduplication Settings Optimize Deduplication Settings What It Does : The DEDUPE_LOOKBACK_PERIOD parameter ensures that duplicate files (e.g., files with the same name uploaded multiple times) are not ingested repeatedly. What It Does DEDUPE_LOOKBACK_PERIOD Best Practices : Best Practices Set an appropriate lookback period based on your ingestion frequency: For high-frequency updates (e.g., hourly ingestion), a shorter period (1–3 days) is sufficient. For batch workflows with periodic reuploads, a longer window (7–14 days) may be needed. Avoid setting the period to 0 unless you are certain duplicates are not an issue, as it disables deduplication. Set an appropriate lookback period based on your ingestion frequency: For high-frequency updates (e.g., hourly ingestion), a shorter period (1–3 days) is sufficient. For batch workflows with periodic reuploads, a longer window (7–14 days) may be needed. For high-frequency updates (e.g., hourly ingestion), a shorter period (1–3 days) is sufficient. For batch workflows with periodic reuploads, a longer window (7–14 days) may be needed. For high-frequency updates (e.g., hourly ingestion), a shorter period (1–3 days) is sufficient. For batch workflows with periodic reuploads, a longer window (7–14 days) may be needed. Avoid setting the period to 0 unless you are certain duplicates are not an issue, as it disables deduplication. 0 Example : Example CREATE PIPE deduped_pipe DEDUPE_LOOKBACK_PERIOD 7 NOTIFICATION_PROVIDER AWS_SQS NOTIFICATION_QUEUE_REFERENCE 'arn:aws:sqs:us-east-1:123456789012:dedupe-queue' AS COPY INTO my_table FROM '@s3_source/folder' FILE_FORMAT 'json'; CREATE PIPE deduped_pipe DEDUPE_LOOKBACK_PERIOD 7 NOTIFICATION_PROVIDER AWS_SQS NOTIFICATION_QUEUE_REFERENCE 'arn:aws:sqs:us-east-1:123456789012:dedupe-queue' AS COPY INTO my_table FROM '@s3_source/folder' FILE_FORMAT 'json'; 2. Organize Storage for Better Performance Why It Matters: Properly structured storage locations improve ingestion speed and reduce processing overhead. Why It Matters: Best Practices: Best Practices: Use folder-based organization in your storage buckets (e.g., /year/month/day/) for easier file management and regex-based ingestion. Keep related files in the same folder to avoid ingesting unrelated data by mistake. Avoid deeply nested directory structures, as they can slow down file scanning. Use folder-based organization in your storage buckets (e.g., /year/month/day/ ) for easier file management and regex-based ingestion. /year/month/day/ Keep related files in the same folder to avoid ingesting unrelated data by mistake. Avoid deeply nested directory structures, as they can slow down file scanning. 3. Choose the Right File Format Impact of File Format: Different file formats affect storage size, query performance, and ingestion speed. Impact of File Format: Best Practices: Best Practices: Use Parquet for columnar storage and analytics-heavy workloads due to its efficient storage and compression. Opt for CSV or JSON for semi-structured data but ensure proper formatting (e.g., consistent delimiters, headers, and escaping). Test ingestion performance with small sample files before committing to large-scale pipelines. Use Parquet for columnar storage and analytics-heavy workloads due to its efficient storage and compression. Opt for CSV or JSON for semi-structured data but ensure proper formatting (e.g., consistent delimiters, headers, and escaping). Test ingestion performance with small sample files before committing to large-scale pipelines. 4. Leverage Error Handling Options Why It Matters: Errors during ingestion can interrupt pipelines or lead to data inconsistencies. Why It Matters: Best Practices: Best Practices: Use ON_ERROR 'skip_file' to bypass files with errors and prevent pipeline interruptions. Regularly monitor the sys.copy_errors_history table for ingestion errors and address recurring issues. For non-critical pipelines, consider ON_ERROR 'continue' (CSV only) to process valid rows even if some are faulty. Use ON_ERROR 'skip_file' to bypass files with errors and prevent pipeline interruptions. ON_ERROR 'skip_file' Regularly monitor the sys.copy_errors_history table for ingestion errors and address recurring issues. sys.copy_errors_history For non-critical pipelines, consider ON_ERROR 'continue' (CSV only) to process valid rows even if some are faulty. ON_ERROR 'continue' Example: Example: CREATE PIPE error_handling_pipe NOTIFICATION_PROVIDER AWS_SQS NOTIFICATION_QUEUE_REFERENCE 'arn:aws:sqs:us-east-1:123456789012:error-queue' AS COPY INTO my_table FROM '@s3_source/folder' FILE_FORMAT 'csv' (ON_ERROR 'continue'); CREATE PIPE error_handling_pipe NOTIFICATION_PROVIDER AWS_SQS NOTIFICATION_QUEUE_REFERENCE 'arn:aws:sqs:us-east-1:123456789012:error-queue' AS COPY INTO my_table FROM '@s3_source/folder' FILE_FORMAT 'csv' (ON_ERROR 'continue'); 5. Monitor and Troubleshoot Pipelines Monitoring Tools: Monitoring Tools: System Tables: Query sys.copy_errors_history to review errors during ingestion. Job Logs: Check job logs in Dremio for detailed error messages and ingestion stats. System Tables: Query sys.copy_errors_history to review errors during ingestion. System Tables: Job Logs: Check job logs in Dremio for detailed error messages and ingestion stats. Job Logs: Common Troubleshooting Tips: Common Troubleshooting Tips: Notification Issues: Ensure the SQS queue ARN matches the one specified in the NOTIFICATION_QUEUE_REFERENCE. File Format Mismatches: Double-check that the specified file format aligns with the actual file type (e.g., don’t label a Parquet file as CSV). Deduplication Failures: Verify that the deduplication period is set correctly and files aren’t inadvertently re-ingested due to naming conflicts. Notification Issues: Ensure the SQS queue ARN matches the one specified in the NOTIFICATION_QUEUE_REFERENCE . Notification Issues: NOTIFICATION_QUEUE_REFERENCE File Format Mismatches: Double-check that the specified file format aligns with the actual file type (e.g., don’t label a Parquet file as CSV). File Format Mismatches: Deduplication Failures: Verify that the deduplication period is set correctly and files aren’t inadvertently re-ingested due to naming conflicts. Deduplication Failures: 6. Optimize Regex and File Selection Why It Matters: Using overly broad regex patterns or processing unnecessary files can impact pipeline performance. Why It Matters: Best Practices: Best Practices: Write regex patterns that are as specific as possible to match only the files you need. Avoid processing large directories unless required. Use the FILES clause or specific folder paths to limit scope. Example: FILES CREATE PIPE regex_pipe NOTIFICATION_PROVIDER AWS_SQS NOTIFICATION_QUEUE_REFERENCE 'arn:aws:sqs:us-west-2:123456789012:regex-queue' AS COPY INTO my_table FROM '@s3_source/folder' REGEX '^2024/11/.*.csv' FILE_FORMAT 'csv'; CREATE PIPE regex_pipe NOTIFICATION_PROVIDER AWS_SQS NOTIFICATION_QUEUE_REFERENCE 'arn:aws:sqs:us-west-2:123456789012:regex-queue' AS COPY INTO my_table FROM '@s3_source/folder' REGEX '^2024/11/.*.csv' FILE_FORMAT 'csv'; 7. Plan for Schema Evolution Why It Matters: Iceberg tables support schema evolution, but it’s crucial to manage changes thoughtfully to avoid ingestion failures. Why It Matters: Best Practices: Best Practices: Test schema changes in a staging environment before applying them to production pipelines. Use Iceberg’s branching capabilities to isolate schema updates during development. Validate data types and formats in source files to avoid mismatches with the target table schema. Test schema changes in a staging environment before applying them to production pipelines. Use Iceberg’s branching capabilities to isolate schema updates during development. Validate data types and formats in source files to avoid mismatches with the target table schema. 8. Integrate with Data Lakehouse Workflows Why It Matters: Auto-Ingest simplifies transitioning to a lakehouse architecture, but aligning with broader workflows ensures smooth integration. Why It Matters: Best Practices: Best Practices: Combine Auto-Ingest with Dremio’s SQL-based querying to enable seamless analytics on ingested data. Use Iceberg’s time-travel feature to track historical changes and validate pipeline performance over time. Combine Auto-Ingest with Dremio’s SQL-based querying to enable seamless analytics on ingested data. Use Iceberg’s time-travel feature to track historical changes and validate pipeline performance over time. By following these best practices and considerations, you can ensure your Dremio Auto-Ingest pipelines are robust, efficient, and well-suited to your data engineering needs. These guidelines will help you avoid common pitfalls and fully leverage the power of automated ingestion for Apache Iceberg tables. Troubleshooting and Debugging Auto-Ingest Pipelines Even with a robust Auto-Ingest setup, you may encounter issues during the ingestion process. Dremio’s system tables, such as SYS.COPY_ERRORS_HISTORY , provide detailed insights into ingestion errors, making it easier to diagnose and resolve problems. This section outlines common issues and how to effectively use the system table to debug your pipelines. SYS.COPY_ERRORS_HISTORY 1. Common Issues and Resolutions Common Issues and Resolutions Notification Configuration Problems Notification Configuration Problems Symptom: The pipe does not respond to new files being uploaded. Resolution: Verify the NOTIFICATION_PROVIDER is configured correctly (e.g., AWS_SQS for S3). Ensure the NOTIFICATION_QUEUE_REFERENCE points to the correct ARN of your event notification queue. Symptom : The pipe does not respond to new files being uploaded. Symptom Resolution : Verify the NOTIFICATION_PROVIDER is configured correctly (e.g., AWS_SQS for S3). Ensure the NOTIFICATION_QUEUE_REFERENCE points to the correct ARN of your event notification queue. Resolution Verify the NOTIFICATION_PROVIDER is configured correctly (e.g., AWS_SQS for S3). Ensure the NOTIFICATION_QUEUE_REFERENCE points to the correct ARN of your event notification queue. Verify the NOTIFICATION_PROVIDER is configured correctly (e.g., AWS_SQS for S3). NOTIFICATION_PROVIDER AWS_SQS Ensure the NOTIFICATION_QUEUE_REFERENCE points to the correct ARN of your event notification queue. NOTIFICATION_QUEUE_REFERENCE File Format Mismatch File Format Mismatch Symptom: The pipeline fails with file parsing errors. Resolution: Double-check that the FILE_FORMAT in your pipe configuration matches the actual format of the uploaded files. Validate format-specific options (e.g., delimiter, null handling) for correctness. Symptom : The pipeline fails with file parsing errors. Symptom Resolution : Double-check that the FILE_FORMAT in your pipe configuration matches the actual format of the uploaded files. Validate format-specific options (e.g., delimiter, null handling) for correctness. Resolution Double-check that the FILE_FORMAT in your pipe configuration matches the actual format of the uploaded files. Validate format-specific options (e.g., delimiter, null handling) for correctness. Double-check that the FILE_FORMAT in your pipe configuration matches the actual format of the uploaded files. FILE_FORMAT Validate format-specific options (e.g., delimiter, null handling) for correctness. Partial or Skipped File Loads Partial or Skipped File Loads Symptom: Some files are partially loaded or skipped entirely. Resolution: Use the SYS.COPY_ERRORS_HISTORY table to identify problematic files and the reasons for rejection. Adjust error-handling options (ON_ERROR) in your pipe to match your tolerance for bad records. Symptom : Some files are partially loaded or skipped entirely. Symptom Resolution : Use the SYS.COPY_ERRORS_HISTORY table to identify problematic files and the reasons for rejection. Adjust error-handling options (ON_ERROR) in your pipe to match your tolerance for bad records. Resolution Use the SYS.COPY_ERRORS_HISTORY table to identify problematic files and the reasons for rejection. Adjust error-handling options (ON_ERROR) in your pipe to match your tolerance for bad records. Use the SYS.COPY_ERRORS_HISTORY table to identify problematic files and the reasons for rejection. SYS.COPY_ERRORS_HISTORY Adjust error-handling options ( ON_ERROR ) in your pipe to match your tolerance for bad records. ON_ERROR 2. Using the SYS.COPY_ERRORS_HISTORY Table Using the SYS.COPY_ERRORS_HISTORY The SYS.COPY_ERRORS_HISTORY table logs detailed information about COPY INTO jobs where records were rejected due to parsing or schema issues. This includes jobs configured with ON_ERROR 'continue' or ON_ERROR 'skip_file' . SYS.COPY_ERRORS_HISTORY COPY INTO ON_ERROR 'continue' ON_ERROR 'skip_file' Key Columns in the Table Key Columns in the Table executed_at: The timestamp when the job was executed. job_id: The unique identifier of the COPY INTO job. table_name: The target Iceberg table for the job. user_name: The username of the individual who ran the job. file_path: The path of the file with rejected records. file_state: PARTIALLY_LOADED: Some records were loaded, but others were rejected. SKIPPED: No records were loaded due to file-level errors. records_loaded_count: The number of successfully ingested records. records_rejected_count: The number of records rejected due to errors. executed_at : The timestamp when the job was executed. executed_at job_id : The unique identifier of the COPY INTO job. job_id COPY INTO table_name : The target Iceberg table for the job. table_name user_name : The username of the individual who ran the job. user_name file_path : The path of the file with rejected records. file_path file_state : PARTIALLY_LOADED: Some records were loaded, but others were rejected. SKIPPED: No records were loaded due to file-level errors. file_state PARTIALLY_LOADED: Some records were loaded, but others were rejected. SKIPPED: No records were loaded due to file-level errors. PARTIALLY_LOADED : Some records were loaded, but others were rejected. PARTIALLY_LOADED SKIPPED : No records were loaded due to file-level errors. SKIPPED records_loaded_count : The number of successfully ingested records. records_loaded_count records_rejected_count : The number of records rejected due to errors. records_rejected_count Example Query: Identifying Problematic Files Example Query: Identifying Problematic Files To view details about rejected files for a specific table: SELECT executed_at, job_id, file_path, file_state, records_rejected_count FROM SYS.COPY_ERRORS_HISTORY WHERE table_name = 'my_table' ORDER BY executed_at DESC; SELECT executed_at, job_id, file_path, file_state, records_rejected_count FROM SYS.COPY_ERRORS_HISTORY WHERE table_name = 'my_table' ORDER BY executed_at DESC; This query highlights: This query highlights: When ingestion errors occurred. Which files were affected. Whether files were partially loaded or skipped. When ingestion errors occurred. Which files were affected. Whether files were partially loaded or skipped. 3. Drilling into Error Details Once you identify a problematic job using the job_id, you can use the copy_errors() function to extract detailed error information. copy_errors() Example: Retrieving Error Details SELECT * FROM copy_errors('1aacb195-ca94-ec4c-2b01-ecddac81a900', 'my_table'); SELECT * FROM copy_errors('1aacb195-ca94-ec4c-2b01-ecddac81a900', 'my_table'); This query provides granular information about errors encountered during the ingestion process for the specified job. 4. Best Practices for Debugging Proactive Monitoring Regularly query the SYS.COPY_ERRORS_HISTORY table to track ingestion health. Set alerts for high records_rejected_count values to identify recurring issues. Regularly query the SYS.COPY_ERRORS_HISTORY table to track ingestion health. SYS.COPY_ERRORS_HISTORY Set alerts for high records_rejected_count values to identify recurring issues. Validate Source Data Audit source files for schema inconsistencies or formatting errors. Ensure files match the expected format (e.g., proper delimiters for CSV). Audit source files for schema inconsistencies or formatting errors. Ensure files match the expected format (e.g., proper delimiters for CSV). Tuning Error Handling Use ON_ERROR 'skip_file' for critical pipelines where partial loads are unacceptable. Opt for ON_ERROR 'continue' in cases where maximum data recovery is desired, especially for CSV files. Use ON_ERROR 'skip_file' for critical pipelines where partial loads are unacceptable. ON_ERROR 'skip_file' Opt for ON_ERROR 'continue' in cases where maximum data recovery is desired, especially for CSV files. ON_ERROR 'continue' Housekeeping the System Table The SYS.COPY_ERRORS_HISTORY table can grow significantly over time. Manage its size using these configuration keys: SYS.COPY_ERRORS_HISTORY dremio.system_iceberg_tables.record_lifespan_in_millis: Retains history for a specified number of days (default is 7). dremio.system_iceberg_tables.housekeeping_thread_frequency_in_millis: Controls how frequently old records are removed (default is daily). dremio.system_iceberg_tables.record_lifespan_in_millis : Retains history for a specified number of days (default is 7). dremio.system_iceberg_tables.record_lifespan_in_millis dremio.system_iceberg_tables.housekeeping_thread_frequency_in_millis : Controls how frequently old records are removed (default is daily). dremio.system_iceberg_tables.housekeeping_thread_frequency_in_millis 5. Common Query Patterns for Debugging Find Recently Skipped Files SELECT file_path, file_state, records_rejected_count FROM SYS.COPY_ERRORS_HISTORY WHERE file_state = 'SKIPPED' ORDER BY executed_at DESC; SELECT file_path, file_state, records_rejected_count FROM SYS.COPY_ERRORS_HISTORY WHERE file_state = 'SKIPPED' ORDER BY executed_at DESC; Analyze Partially Loaded Files SELECT file_path, records_loaded_count, records_rejected_count FROM SYS.COPY_ERRORS_HISTORY WHERE file_state = 'PARTIALLY_LOADED' ORDER BY executed_at DESC; SELECT file_path, records_loaded_count, records_rejected_count FROM SYS.COPY_ERRORS_HISTORY WHERE file_state = 'PARTIALLY_LOADED' ORDER BY executed_at DESC; By leveraging the SYS.COPY_ERRORS_HISTORY table and related debugging tools, you can effectively monitor and resolve issues in your Auto-Ingest pipelines. These capabilities ensure your pipelines are resilient and capable of handling a wide variety of data ingestion scenarios with minimal disruption. SYS.COPY_ERRORS_HISTORY Conclusion Dremio Auto-Ingest for Apache Iceberg tables brings a new level of automation and simplicity to data ingestion workflows. By leveraging event-driven pipelines, you can reduce manual intervention, ensure data freshness, and streamline the integration of your object storage systems with Iceberg tables. From real-time updates to batch processing, Auto-Ingest handles diverse use cases with ease, offering powerful features like deduplication, error handling, and format-specific customization. By following best practices, monitoring your pipelines, and troubleshooting effectively, you can create reliable and efficient data ingestion workflows that scale with your business needs. Whether you're modernizing your data lakehouse architecture or building advanced analytics pipelines, Dremio Auto-Ingest is a must-have tool to unlock the full potential of your data. Blog: What is a Data Lakehouse and a Table Format? Free Copy of Apache Iceberg the Definitive Guide Free Apache Iceberg Crash Course Lakehouse Catalog Course Iceberg Lakehouse Engineering Video Playlist Blog: What is a Data Lakehouse and a Table Format? Blog: What is a Data Lakehouse and a Table Format? Free Copy of Apache Iceberg the Definitive Guide Free Copy of Apache Iceberg the Definitive Guide Free Apache Iceberg Crash Course Free Apache Iceberg Crash Course Lakehouse Catalog Course Lakehouse Catalog Course Iceberg Lakehouse Engineering Video Playlist Iceberg Lakehouse Engineering Video Playlist