Optimization Techniques in dbt Core with Spark on EMR and dbt Core with Glue

Introduction
In the world of data engineering, selecting the right tools and frameworks is crucial for efficient data processing and transformation. dbt (data build tool) has become a popular choice for data transformation, providing a simple interface to manage and deploy SQL-based transformations. In this article, we'll explore the optimization techniques in dbt Core when used with Apache Spark on Amazon EMR (Elastic MapReduce) and AWS Glue. We'll also discuss when to use each option, their pros and cons, cost benefits and configuring the profile.yml file for optimized result.

dbt Core with Spark on EMR

Optimization Techniques
Optimizing dbt Core with Spark on EMR involves several techniques to improve performance and efficiency:
• Cluster Configuration: Choose appropriate EMR instance types and cluster configurations based on workload requirements.
Eg: If we require to process 18Gb of data in each run in that case we should select atleast 4 node/VCPU cluster(1 node reserved for Yarn + 3 usable nodes) with minimum 2 Gb of memory per node. So you can choose r or m series cluster.
• Data Partitioning: Partition large datasets to enable parallel processing and reduce shuffle operations. This can significantly speed up data transformations. Partitioning should be done on column with low cardinality and mostly used for filters or joining condition which limits the number of partitions scanned. Eg: date column for sales related data which can be defined in the dbt model’s config parameter:

• Efficient Data Formats: Use columnar data formats like Parquet or ORC, which are optimized for read performance and compression.
• Resource Allocation: Allocate resources dynamically based on the workload. Use auto-scaling features to adjust the cluster size according to the processing demands. At dbt model level you can enable dynamic allocation:

When to Use Spark on EMR
Spark on EMR is suitable when dealing with massive datasets that require distributed processing and advanced analytics. It is ideal for:
• Big data processing with high throughput and low latency.
• Running complex machine learning algorithms and iterative data processing tasks.
• Workloads which require significant computational resources and custom spark configurations.
• When reading data directly from file system like S3 in AWS of local file system. Suppose there is a parquet file stored in an s3 bucket then file to be read should be used in format :
SELECT
col1,
col2,
col3
FROM parquet.s3://<bucket name>/<folder name>/<file name>

Pros and Cons of Spark on EMR
Pros:
• Scalability: Easily scale up or down based on workload requirements.
• Flexibility: Customize cluster configurations and use various instance types.
• Advanced Analytics: Run sophisticated analytics and machine learning workloads.
Cons:
• Cost: High computational resources can lead to increased costs.
• Complexity: Requires expertise in managing and optimizing Spark clusters.
• No instant scalability: Scalability takes time as it spins up an EC2 instance which can cause delay or job failures when Yarn is waiting for the executor to be assigned but its not available.

Cost Benefits
The cost benefits of using Spark on EMR depend on the workload's complexity and scale. EMR's ability to auto-scale and use spot instances(only recommended for short time processing) can help reduce costs. However, high computational requirements can still lead to significant expenses.

Configuring profile.yml for Spark on EMR
To configure the profile.yml file for dbt with Spark on EMR, follow these steps:

[profile name]:
target: dev
outputs:
dev:
type: spark
method: thrift
host: [your-emr-cluster-master-dns]
port: 10000
user: [your-user]
schema: [your-schema]
database: [your-database]
connect_timeout: 30[can be increased or decreased]
connect_retries: 2[can be increased or decreased]
threads: 3[can be increased or decreased for parallel processing]

dbt Core with AWS Glue
Optimization Techniques
Optimizing dbt Core with AWS Glue involves several techniques to improve performance and efficiency:
• Job Bookmarking: Enable job bookmarking to track data processing and only process new or changed data, reducing redundant computations.
• Dynamic Frames: Use AWS Glue Dynamic Frames to handle semi-structured data efficiently and perform schema transformations.
• Partition Pruning: Use partition pruning to limit the data scanned during processing, improving performance for large datasets.
• Parallel Processing: Configure Glue jobs to run in parallel, leveraging multiple workers to speed up data transformations.
• Efficient Data Formats: Use optimized file formats like Parquet or ORC for improved read and write performance.

When to Use AWS Glue
AWS Glue is suitable for serverless data processing and ETL tasks, especially when ease of use and integration with other AWS services are essential. It is ideal for:
• Serverless ETL operations with minimal infrastructure management.
• Integrating with other AWS services like S3, Redshift, and RDS,etc.
• Processing small datasets.
• Data cataloging and metadata management.
• Incremental data loads or merge.

Pros and Cons of AWS Glue
Pros:
• Simplicity: Serverless architecture eliminates infrastructure management.
• Integration: Seamlessly integrates with other AWS services.
• Scalability: Automatically scale up or down based on workload requirements.
Cons:
• Cost: Can be expensive for large-scale data processing.
• Limited Control: Limited customization options compared to Spark on EMR.

Cost Benefits
AWS Glue offers cost benefits for small to medium-scale ETL tasks due to its serverless nature, eliminating the need for dedicated infrastructure. However, for large-scale data processing, costs can escalate based on the amount of data processed and the number of worker nodes used.dbt with glue runs sparkSQL which leverages auto optimization of latest glue version(currently 5.0 which is 10x faster that older versions)

Configuring profile.yml for AWS Glue
To configure the profile.yml file for dbt with AWS Glue, follow these steps:

[profile name]:
target: dev
outputs:
dev:
type: glue
role_arn: [ARN of the role assigned to glue interactive session]
region: [region where glue interactive session need to be created and used]
glue_version: “5.0”[use latest version]
workers: 4[increased or decreased on the basis of compute requirement]
worker_type: G.1X [can be changed on the basis of requirement]
database: [your-database]
schema: [your-schema]
catalog: [your-catalog]
glue_session_reuse: True [set to true in order to reuse existing glue interactive session]
location: [ARN of the s3 location to be used by the interactive session]
datalake_formats: "iceberg"
default_arguments: "--enable-metrics=true" [required to enable metrics in cloudwatch]
conf: --conf <spark configuration name and its value>

Conclusion
Choosing between dbt Core with Spark on EMR and dbt Core with AWS Glue depends on your specific use case, data processing requirements, and budget constraints. Spark on EMR is ideal for large-scale, complex data processing tasks, while AWS Glue offers a serverless, easy-to-use solution for ETL operations with option of incremental data loads for iceberg table format. Understanding the pros and cons, cost benefits, and configuration details for both options will help you make an informed decision and optimize your data transformation workflows.