Shiva Reddy Alguvelly

Snowflake question: on Netezza, you had to be connected to a database to create an object, eg a procedure. on Snowflake, you can compile a proc to a database you are not currently using if the procedure name is qualified with DB and schema. is it possible to prevent this and reciprocate Netezza behaviour which I believe was safer?

1

*Enabling batch+streaming integration in on-premises data platforms* Modern cloud-based analytics platforms support batch & streaming data ingestion, referred to as cold+hot paths or delta/lambda architecture. However, enabling this capability on on-premises data platforms requires careful considerations. Here are key aspects to consider while planning cold+hot data pipelines in a unified on-prem data platform. 1) Assessment This is a fundamental step to assess gaps in tools & skills before the kickoff. 2) Latency A data refresh frequency should be defined based on business needs. e.g., milliseconds-to-seconds for streaming, minutes-to-an-hour for micro-batches & hours-to-a-day for batch integrations. 3) Impact on source systems Properly assessing the impact on source systems & aligning with software engineers is crucial, to avoid any unpleasant surprises during pipelines execution. 4) Design patterns for data curation Various design patterns can be adopted to logically organize the data, such as Databrick's Medallion architecture (bronze, silver, gold), Teradata's IDW architecture (staging, core, semantic), etc. The design pattern should support quick time to market, a unified enterprise-wide view, timely refresh, enhanced data quality, ease of implementation, data transformation & enrichment. 5) Workload management Enterprise data platforms are used by a variety of users. It is vital to ensure proper workload management, especially for frequent jobs with low-latency data requirements & complex jobs with significant data requirements. Allocating an off-peak nightly window outside of peak business hours for heavy batch processing pipelines is usually recommended. For micro-batch & streaming pipelines with distinguishing nature, proper quota & resource pooling should be applied to ensure optimal utilization of system resources. 6) Orchestration & scheduling Batch pipelines have been around for a while, so data engineers are familiar with scheduling best practices. Micro-batch pipelines inherit many features from batch pipelines; however, streaming pipelines need to be continuously "listening" & ingest data (with re-try features) as it is created from source to destination. 7) Data security & privacy The data security & privacy should be implemented by design to ensure regulatory compliance. 8) Scalability The infrastructure & data curation design should ensure scalability across system resources, users & all types of data pipelines. 9) Observability There should be a built-in capability to capture pipeline execution metadata, logs, alerts & other pipeline features to ensure proactive monitoring. 10) Data retention Batch pipelines handle large data volumes, while micro-batch & streaming pipelines handle lower data volumes. To ensure efficient pipeline execution, proper data retention policies must be applied. 11) Documentation Regardless of the refresh frequency, adequate documentation should be created. If I missed anything, pls comment.

52

8 Comments

Here's my latest article on Medium for various aspects of Snowflake Technology Infrastructure which needs to be managed and governed . I will be diving into each of these aspects in my upcoming articles . Please feel free to subscribe , share and comment. Reach out for any professional help or consulting services in these areas . #snowflakecm #snowflakeintrastructure #snowflakedevops

7

❄️We implemented interesting use case in ❄️ and the details are source sql server Target snowflake Source has business process which create /update / delete the data in the transaction table based on business rule for every min . I need to sync this table to snowflake for reporting and wanted to have at least near real time data . Share your thoughts how would you solve this problem . I will share my implementation details as well!! Madhivanan Anbalagan Somen Swain Sachin Mittal Natarajan Ganapathi

60

15 Comments

Data Engineer REMOTE  Skills- Data Bricks, Snowflake, Data Modelling ( Certified candidate )  [email protected] data modelling basics , techniques and scenario-based questions . they will test overall approach on data modelling like star schema , snowflake , fact and dimension table designs and how you design them in delta lake and snowflake DB. approach on loading large volume data , change data capture and schema changes

4

6 Comments

Check out this Q&A with Capital One’s Head of AI Product, @Aparna Sinha, where she shares advice on building enterprise AI/ML capabilities. The conversation touches on the importance of product management in AI, Capital One’s AI/ML priorities, advice for those looking to break into the industry and so much more! Read it now on the Capital One tech blog

2

Serverless task for Python, Java, and Scala UDF/Stored Procs in #Snowflake Preview https://lnkd.in/gEtcQ5t9 #datalake #snowflake #newfeatures

7

There are multiple ways in which Snowflake can handle Data deduplication for you. In my latest article on medium, I did describe few of those options. Feel free to share, comment and subscribe. #snowflakeadvocate #snowflakedata #snowflakenineering

5

With Large Data Volumes and Aggressive SLAs, building low latency Cost Optimum, Declarative and continuous Data Pipelines is need of the hour. Here's my latest article on Snowflake Dynamic Tables. #delcarativepipeline #continouspipeline #dynamicrefresh Please feel free to like, Subscribe and Share my article and Newsletter.

3

More clean and readable in #Snowflake #query Use count_if instead of count with case statement. Ex - SELECT count (CASE WHEN PRODUCT_ID = 1 THEN 0 END) AS CNT_PROD1 ,count (CASE WHEN PRODUCT_ID = 2 THEN 0 END) AS CNT_PROD2 ,count (CASE WHEN PRODUCT_ID = 3 THEN 0 END) AS CNT_PROD3 FROM PRODUCT. Instead use - SELECT count_if (PRODUCT_ID = 1) AS CNT_PROD1 ,count_if (PRODUCT_ID = 2) AS CNT_PROD2 ,count_if (PRODUCT_ID = 3) AS CNT_PROD3 FROM PRODUCT FROM PRODUCT.

7

1 Comment

Here's a comprehensive explanation of Adaptive Query Execution (AQE) in Spark with Scala implementation : Overview Adaptive Query Execution (AQE) is a runtime optimization technique in Spark SQL that dynamically reoptimizes and adjusts query plans based on runtime statistics. It's enabled by default since Spark 3.2.0. Enabling AQE ———————— // Enable AQE spark.conf.set("spark.sql.adaptive.enabled", true) // Enable partition coalescing spark.conf.set("spark.sql.adaptive.coalescePartitions.enabled", true) Key Features ————————— Dynamic Partition Coalescing // Set initial large number of partitions spark.conf.set("spark.sql.adaptive.coalescePartitions.initialPartitionNum", 200) // Set target partition size (default 64MB) spark.conf.set("spark.sql.adaptive.advisoryPartitionSizeInBytes", 67108864) Join Strategy Selection Spark supports different join hints that can be used with AQE: // Example using broadcast hint df1.join(broadcast(df2), Seq("join_column")) // Example using merge hint df1.join(df2.hint("merge"), Seq("join_column")) // Example using shuffle hash join df1.join(df2.hint("shuffle_hash"), Seq("join_column")) Performance Benefits AQE provides several performance improvements: 1. Dynamic Partition Optimization: Automatically coalesces small partitions into larger ones 2. Smart Join Strategy: Switches between broadcast and sort-merge joins based on runtime statistics 3. Skew Handling: Automatically detects and handles data skew in joins Implementation Details The AQE framework works in stages: 1. Initial Execution: Starts with leaf stages that don't depend on other stages 2. Statistics Collection: Gathers runtime statistics after stage completion 3. Plan Reoptimization: Updates query plan based on collected statistics 4. Adaptive Execution: Executes optimized plan for remaining stages // Example showing query with AQE optimization val query = spark.sql(""" SELECT /*+ BROADCAST(small_table) */ big_table.key, small_table.value FROM big_table JOIN small_table ON big_table.key = small_table.key """) // Explain the adaptive plan query.explain(true) Monitoring and Debugging You can monitor AQE behavior through[7]: // View adaptive plan details df.explain("adaptive") // Check if final plan is reached // Look for AdaptiveSparkPlan node with isFinalPlan = true Best Practices 1. Set appropriate initial partition numbers for large datasets 2. Monitor partition sizes using Spark UI 3. Use appropriate data types to optimize memory usage 4. Enable statistics collection for better optimization decisions AQE is particularly effective for complex queries with multiple joins and aggregations, offering significant performance improvements without manual tuning. #spark #scala

6

🚀 With over 7 years of hands-on experience in Hadoop application development and cluster administration, I can attest to its unparalleled strength in managing and processing big data. Hadoop’s scalable architecture and robust performance make it an essential tool for tackling large-scale data challenges. From real-time analytics to comprehensive data processing, its versatility has consistently delivered powerful results. Excited to continue leveraging Hadoop’s capabilities and drive innovation in the big data space! #Hadoop #BigData #DataManagement #TechExpertise

5

Title: “Exploring the Absence of Native Views Support in Apache Spark within Hadoop Ecosystem” Apache Spark and Apache Hadoop are two popular technologies in the big data ecosystem, each with its own strengths and limitations. One limitation of Spark in Hadoop is that Spark does not directly support Hadoop's traditional views. While Hadoop supports views through tools like Hive or HBase, Spark does not have built-in support for views. Here are some reasons why Spark may not directly support views when used in conjunction with Hadoop: 1. Architecture Differences: Spark and Hadoop have different architectural designs and data processing methodologies. Hadoop uses MapReduce for processing data, which allows for defining views through tools like Hive. In contrast, Spark operates on an in-memory processing model with its own APIs and processing engine, which may not have native support for views. 2. Focus on Performance :Spark is known for its high-performance, in-memory processing capabilities that prioritize speed and efficiency. While views can be beneficial for certain use cases, they can introduce additional overhead and complexity to the processing pipeline. Spark’s design philosophy may prioritize speed and flexibility over features like views, which could impact performance. 3. Flexibility and Customization :Spark provides a more flexible and customizable data processing framework compared to traditional Hadoop tools. Users can programmatically define complex data transformations and processing logic using Spark APIs like RDDs, DataFrames, and Datasets. This flexibility allows users to implement view-like functionalities through code rather than relying on predefined views. 4. External Tools Integration :While Spark itself may not offer built-in support for views, users can leverage external tools and libraries to achieve similar functionality. For example, tools like Apache Hive or Apache HBase can be integrated with Spark to define views and manage structured data, providing a workaround for the lack of native view support in Spark. 5. Evolution of Spark: Spark is a rapidly evolving technology with a vibrant open-source community driving innovation and enhancements. Future releases of Spark may introduce features or optimizations that address the need for views or provide alternative mechanisms for managing and querying structured data within the Spark ecosystem. In summary, Spark’s lack of direct support for views in Hadoop is primarily due to architectural differences, performance considerations, focus on flexibility, and the evolving nature of the Spark framework. While this limitation exists, users can explore alternative approaches or external integrations to achieve similar functionalities within their Spark-based big data processing workflows.

20

1 Comment

Hadoop Vs Spark Interview Question!! Interviewer: Let's discuss the evolution of the Hadoop ecosystem and the rise of Apache Spark. One significant change that occurred was the replacement of Hadoop MapReduce with Spark in many data processing workflows. Can you explain why this transition happened? Candidate: Certainly! The transition from Hadoop MapReduce to Apache Spark can be attributed to several factors. One key reason is Spark's superior performance compared to MapReduce. Spark's in-memory processing model allows it to execute tasks much faster by minimizing data shuffling and disk I/O, leading to significant speed improvements for data processing tasks. Interviewer: That's an important point. Can you elaborate on how Spark's in-memory processing differs from Hadoop MapReduce's disk-based processing? Candidate: Of course! In Hadoop MapReduce, intermediate data between map and reduce tasks is typically written to disk, which can incur overhead due to disk I/O operations. In contrast, Spark keeps intermediate data in-memory whenever possible, reducing the need for disk reads and writes. This in-memory processing model enables Spark to achieve faster processing speeds, especially for iterative algorithms and interactive analytics workloads. Interviewer: I see. So, apart from performance, are there any other reasons why Spark replaced MapReduce in many data processing workflows? Candidate: Absolutely. Another factor is Spark's versatility. While Hadoop MapReduce is primarily designed for batch processing, Spark supports a wide range of processing workloads, including batch processing, interactive querying, streaming data processing, and machine learning. This versatility makes Spark a more attractive choice for organizations looking to address diverse analytical use cases within a single framework. Interviewer: That's a compelling advantage. How did Spark's programming model contribute to its popularity? Candidate: Spark offers a more developer-friendly programming model compared to Hadoop MapReduce. With its rich set of high-level APIs in languages like Scala, Python, and Java, Spark simplifies the development of complex data processing workflows. Additionally, Spark's interactive shell (Spark REPL) facilitates rapid prototyping and experimentation, enhancing developer productivity. Interviewer: Thank you for the insightful explanation. It's clear that Spark's performance, versatility, and ease of use have made it a preferred choice for many organizations seeking to modernize their data processing infrastructure. Candidate: My pleasure! #DataEngineering #PySpark #ETLProject #pyspark #python #azuredataengineer #databricks #awscloud #googlecloud #dataengineer #dataengineerjobs #dataanalysis #dataanalytics #glue #azuredatabricks #azuredatafactory #synapse #sql #sqlserver #powerbi #database #etl #etldeveloper #Python #Pandas #BeginnertoAdvance #Dataframe #Sorting #Joins #Grouping #RealTimeScenarios #sql #awscloud #powerbi #databricks

5

All Data Engineers - I Would like to share learning of different types of languages in Data World today i learnt 😎 : DDL --> Data Definition Language. Ex: Create, drop, alter table DML --> Data Manipulation Language. Ex: update, insert to table All these years most of us aware above but interestingly there are few others are there.. DCL --> Data Control Language. Ex: Grant and revoke TCL --> Transaction Control Language. Ex: Commit, rollback DQL --> Data Query Language. Ex: Select How many of you thought there are only two types DDL,DML only?

22