登录查看更多内容

How can you balance data querying performance and cost in data engineering?

由人工智能和领英社区提供技术支持

Data querying is a crucial skill for data engineers, as it allows them to extract, transform, and analyze data from various sources and platforms. However, data querying also involves trade-offs between performance and cost, as different queries can have different impacts on the speed, accuracy, and efficiency of data processing. In this article, you will learn some tips and best practices on how to balance data querying performance and cost in data engineering.

此文章中的业界达人

由社区从 8 条内容中精选。了解更多

Ryan Janssen

CEO @ Zenlytic
Delton da Cruz Agostinho

MS Power BI Data Analyst | MS Cloud Azure Certified | IBM iSeries/AS400
Mauricio Cortés

Data Engineering | Analytics | Cloud Computing | M.S. Analytics at Georgia Tech

1 Understand your data sources and platforms

The first step to balance data querying performance and cost is to understand the characteristics and capabilities of your data sources and platforms. Different data sources, such as relational databases, NoSQL databases, data warehouses, data lakes, or streaming data, have different data models, schemas, formats, and query languages. Similarly, different data platforms, such as Amazon Redshift, Google BigQuery, Apache Spark, or Apache Hive, have different architectures, features, and pricing models. You should know the strengths and weaknesses of each data source and platform, and choose the ones that best suit your data needs, budget, and scalability.

添加您的观点

Mauricio Cortés

Data Engineering | Analytics | Cloud Computing | M.S. Analytics at Georgia Tech
举报内容
There's no way around it, platforms with better performance will be more costly. The important thing to choose wisely the technologies to be used based on the specific requirements of the business or end user. This will help you get the best and cheapest option that satisfies that need. There's no need to go every time with the biggest, most expensive platform if you don't need to.

已翻译

赞

2 Optimize your query design and execution

The second step to balance data querying performance and cost is to optimize your query design and execution. This means writing queries that are clear, concise, and correct while avoiding wasting resources or time. To do this, you should use filters, joins, aggregations, and indexes to reduce the amount of data scanned and processed. Additionally, it’s best to avoid nested queries, subqueries, or complex functions that can increase the query complexity and execution time. Additionally, consider using partitions, buckets, or clusters to organize your data into logical groups that can improve the query performance and reduce the storage cost. Furthermore, caching, materialized views, or incremental updates can store frequently used or intermediate results to avoid repeating the same queries. Lastly, use query monitoring, profiling, or testing tools to measure and improve the query performance and cost.

添加您的观点

Ryan Janssen

CEO @ Zenlytic
举报内容
Optimizing SQL queries (usually) only gives incremental cost improvements. This is because modern data warehouses (usually) have sophisticated query planners which already correct inefficient SQL. The real gains to be had here are at the data modeling layer. Pre-execute common joins and filters inside intermediate tables; ensure all tables are properly keyed. This is where the 80:20 of efficiency gains comes from.

已翻译

赞
Delton da Cruz Agostinho

MS Power BI Data Analyst | MS Cloud Azure Certified | IBM iSeries/AS400
举报内容
Aqui também é chamada a aten??o sobre a necessidade de se estabelecer um limite para consulta, quando se pretende por exemplo consultar dados históricos em tabelas com milh?es de linhas é importante, sempre que possível, definir um intervalo de tempo.

已翻译

赞
Mauricio Cortés

Data Engineering | Analytics | Cloud Computing | M.S. Analytics at Georgia Tech
举报内容
SQL optimization plays a big role in cost efficiency, as well as runtime and performance. You can have the best infrastructure for your project but if you don't optimize your queries as much as possible, is going to hurt your wallet and your waiting times.

已翻译

赞

3 Choose the right query mode and pricing model

The third step to balance data querying performance and cost is to choose the right query mode and pricing model for your data platform. You may have different options, such as on-demand, reserved, and flat-rate, each of which has its own advantages and disadvantages depending on your data volume, frequency, complexity, and predictability. When making your selection, consider how the option fits your needs and budget. On-demand requires payment only for the amount of data processed by your queries, while reserved requires payment for a fixed amount of compute resources or time regardless of the amount of data processed. Flat-rate requires a flat fee for unlimited queries. Evaluate each option carefully to determine which one is best for you.

添加您的观点

Carlos Fernando Chicata

Algunas insignias de community Top Voice | Ingeniero de datos | AWS User Group Perú - Arequipa | AWS x3
举报内容
Some clarifications on the options: 1) On-demand mode: good option for impredicable data volume and frequency cases; but you spend your budget very quickly. 2) Reserved mode: good option when you know data volume and frequency range with possible peaks in specific period of time. Great option to cheap your budget. 3) Flat-rate mode: good option when you know data complexity and volume cases but not data frequency. Depend frequency to be great or not option with your budget.

已翻译

赞

4 Automate and schedule your queries

The fourth step to balance data querying performance and cost is to automate and schedule your queries. This means using tools or scripts to execute the queries at pre-determined intervals or when certain triggers are met, instead of running them manually or randomly. Automating and scheduling your queries can help save time and effort, improve data quality and consistency, and optimize the performance and cost of data querying. Examples of tools or scripts that can assist in automating and scheduling your queries are Apache Airflow, AWS Lambda, Google Cloud Scheduler, and Cron. These services enable the creation, management, monitoring of data pipelines and workflows, running code without provisioning or managing servers, scheduling cloud functions or jobs, and scheduling tasks on Unix-like systems respectively.

添加您的观点

Mauricio Cortés

Data Engineering | Analytics | Cloud Computing | M.S. Analytics at Georgia Tech
举报内容
Definitely using some kind of scheduler or data orchestrator will help you not only run certain queries on specific times depending on their size and complexity but also monitor its performance and isolate costs and issues that might arise in terms of performance and time constraints.

已翻译

赞

5 Monitor and review your queries

Monitoring and reviewing your queries is the fifth step for balancing data querying performance and cost. This process involves tracking and analyzing metrics such as query execution time, data processed, errors, latency, throughput, and billing. Doing so can help identify and troubleshoot any issues or bottlenecks that may affect performance or cost. Additionally, it can evaluate and benchmark the effectiveness and efficiency of your data querying strategies and techniques, as well as discover new ways to improve performance or cost. Lastly, it can report and communicate data querying results and insights to stakeholders or clients. Examples of tools or services that can assist in this process include AWS CloudWatch, Google Cloud Monitoring, Apache Zeppelin, and Tableau.

添加您的观点

Carlos Fernando Chicata

Algunas insignias de community Top Voice | Ingeniero de datos | AWS User Group Perú - Arequipa | AWS x3
(已编辑)
举报内容
Check your query plan periodically to detect possible chances to improve your query engines and database by techniques like index, data sharing, clustering focus, cursor or temporal tables. Remember it happens based how many data need to process. Check business status; maybe changed requirements then you need to query few tables or move deprecated row in tables. Those can be show in costs anyway.

已翻译

赞

6 Here’s what else to consider

This is a space to share examples, stories, or insights that don’t fit into any of the previous sections. What else would you like to add?

添加您的观点

Carlos Fernando Chicata

Algunas insignias de community Top Voice | Ingeniero de datos | AWS User Group Perú - Arequipa | AWS x3
举报内容
Remember this truth: when you trade-off between cost-efficient and performance, there will always be a base cost for set of applied optimization performances techniques; that you except this base cost is your work avoid it.

已翻译

赞

Data Engineering

+ 关注

给文章评分

我们借助人工智能创建了此文章。您认为这篇文章怎么样？

很棒不太好

举报此文章

查看全部

How can you balance data querying performance and cost in data engineering?

1

2

3

4

5

6

1 Understand your data sources and platforms

2 Optimize your query design and execution

3 Choose the right query mode and pricing model

4 Automate and schedule your queries

5 Monitor and review your queries

6 Here’s what else to consider

Data Engineering

给文章评分

感谢您的反馈

更多Data Engineering相关文章

更多相关阅读内容

How can you balance data querying performance and cost in data engineering?

1

2

3

4

5

6

1 Understand your data sources and platforms

2 Optimize your query design and execution

3 Choose the right query mode and pricing model

4 Automate and schedule your queries

5 Monitor and review your queries

6 Here’s what else to consider

Data Engineering

给文章评分

感谢您的反馈

查看其他技能