Jyothibasu Karni

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

Do you have JSON and XML tough to parse and read up? Variant data types. I have seen less people talk about it. Let's do an overview of it and check it out. Here are my thoughts on Databricks Variant data types. https://lnkd.in/g9Qrm2rb #DataBricks #Variant #FileTypes #Simplification #DataArchitecture #Beginners #StartHere #PandaLearns

26

How Quora Scaled Writes with MySQL ? 1. Initial Setup: They began with a non-sharding setup, scaling reads using a leader-follower configuration + caching. 2. Increasing Write Throughput: They adopted two main approaches: Vertical Sharding: - They moved high-traffic tables to different partition groups, so multiple partitions ran different sets of tables using a master-follower setup. - They used Zookeeper to store information about partitions, their tables, and master and replica instances. - This method worked well for small tables but faced challenges with large tables, joins, and transactional operations. Horizontal Sharding: - This approach involves splitting a logical table into multiple physical tables, known as shards. - They used range-based sharding as they make frequent use of range queries. eg: If a shard of table T holds values >= x and < y, they named it T_shard_x_y. - They used the tool pt-online-schema-change to modify the schema, which requires 2x space as it creates a copy of the original table. Although it is an "online" schema change tool, it initially acquires a short-duration X lock on the entire table to create triggers. This X lock sometimes impacts production traffic. However, if the table has 10 shards, at any given time only about 10% of the table would be locked. This article provides an in-depth explanation and discusses the challenges involved. https://lnkd.in/gWcz26mY.

3

Data Engineer REMOTE  Skills- Data Bricks, Snowflake, Data Modelling ( Certified candidate )  sohini@cloudingest.com 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

3

7 Comments

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

Snowflake summit 2024 highlights doc for your reference. #snowflake #summit #updates

49

#Parquet stores data in columnar format on disk. #Spark reads it and stores it in row-oriented format in memory for further processing. There is an obvious overhead of conversion from columnar to row-oriented. Apart from #Photon, #Apache #Arrow also stores data in memory in columnar format. "I do know that the Photon engine replaces Spark's row-orientation with a columnar representation." #databricks #bigdata #dataanalytics

2

Snowflake Interview Question. Part 5:Advanced Techniques and Miscellaneous. Asked in #Epam, #Cisco, #Kipi.bi #TCS #Infosys 18. How to exclude double quotes in the file format of Snowflake? 19. What happens to compute in Snowflake? 20. What is the cost computation for cloning in Snowflake? 21. Limitations in a Reader account in Snowflake? 22. How to change the sequence number in Snowflake? 23. How to alter the auto concrete in Snowflake? 24. Syntax to change the datatype in Snowflake? 25. What are the credits for each warehouse in Snowflake? 26. What are credits in Datawarehouse? 27. Different types of credits in Snowflake? 28. Errors we get when loading files to Snowflake tables? 29. How to eliminate the entire row duplicate in the flat file of Snowflake? 30. How to retain one unique record and delete duplicates in Snowflake tables? 31. How to check the long-running step in SQL at Snowflake? 32. What are the limitations we have in using SQL language in Snowflake Java scripts? 33. How to integrate and share data between Unix and Snowflake using SnowSQL on Unix? 34. How to load a large volume of data from on-premise to Snowflake? 35. How do you load data into an external stage in S3 in Snowflake? 36. Python in Snowflake? 37. Does lambda function in Snowflake? 38. Use of any ETL tool in Snowflake? Follow Avinash S. Get Interview Resources: https://lnkd.in/dCcD-kBt Get SQL, Python, Snowflake(25% OFF) https://lnkd.in/dRehzivv

68

2 Comments

Facts about spark !! Do spark inventors realized column orientation is way forward ? Photon ( Databricks Product ) created with learnings ? Let’s read through article.

3

I’ve written a blog post on handling temporary data for subsequent processing in Spark using cache() or persist(). If you’re tempted to write temporary data to storage, this post might save you time, resources, and improve performance. #microsoft #fabric #databricks #spark https://lnkd.in/epd3DXKs

46

ETL developers, here's why you should learn #python NOW! 𝑵𝒆𝒘 𝑩𝒂𝒕𝒄𝒉 𝒕𝒐 𝒔𝒕𝒂𝒓𝒕 𝑱𝒖𝒍𝒚 27𝒕𝒉; - 𝐉𝐨𝐢𝐧 𝐚𝐥𝐨𝐧𝐠 𝐰𝐢𝐭𝐡 39 𝐚𝐦𝐚𝐳𝐢𝐧𝐠 𝐩𝐞𝐨𝐩𝐥𝐞 𝐰𝐡𝐨 𝐡𝐚𝐯𝐞 𝐞𝐧𝐫𝐨𝐥𝐥𝐞𝐝. ETL workloads are getting migrated to Cloud 𝐘𝐨𝐮 𝐰𝐢𝐥𝐥 𝐛𝐞 𝐚𝐬𝐤𝐞𝐝 - How do you move on prem etl workloads to #aws? Let's say you have your on prem etl workloads in ETL tooks like #informatica, #datastage, #abinitio, etc. which are primarily drag and drop tools. What do you do? You understand them and rebuild the entire code in python or spark and then use services such as lambda ecs fargate glue python shell glue spark There are some more you can use. doc credit -respective owner #aws #dataengineering ✅ Enrollments for Version 3 of 𝐬𝐜𝐞𝐧𝐚𝐫𝐢𝐨 𝐛𝐚𝐬𝐞𝐝 𝐬𝐭𝐫𝐮𝐜𝐭𝐮𝐫𝐞𝐝 𝐡𝐚𝐧𝐝𝐬-𝐨𝐧 𝐑𝐞𝐚𝐥-world AWS Data Engineering ( RADE™) program is ON ! 𝑵𝒆𝒘 𝑩𝒂𝒕𝒄𝒉 𝒕𝒐 𝒔𝒕𝒂𝒓𝒕 𝑱𝒖𝒍𝒚 27𝒕𝒉- 𝐉𝐨𝐢𝐧 𝐚𝐥𝐨𝐧𝐠 𝐰𝐢𝐭𝐡 39 𝐚𝐦𝐚𝐳𝐢𝐧𝐠 𝐩𝐞𝐨𝐩𝐥𝐞 𝐰𝐡𝐨 𝐡𝐚𝐯𝐞 𝐞𝐧𝐫𝐨𝐥𝐥𝐞𝐝. ✅𝐈𝐟 𝐲𝐨𝐮 𝐚𝐫𝐞 𝐚𝐜𝐭𝐢𝐨𝐧-𝐨𝐫𝐢𝐞𝐧𝐭𝐞𝐝, fill the form below to be get invited for the webinar for RADE™ program V3 𝐔𝐒𝐏: 𝐑𝐞𝐚𝐥-𝐰𝐨𝐫𝐥𝐝 𝐬𝐜𝐞𝐧𝐚𝐫𝐢𝐨 𝐛𝐚𝐬𝐞𝐝 𝐬𝐭𝐫𝐮𝐜𝐭𝐮𝐫𝐞𝐝 𝐡𝐚𝐧𝐝𝐬-𝐨𝐧 program !

213

6 Comments

💰 💵 𝐂𝐥𝐨𝐮𝐝 𝐂𝐨𝐬𝐭 𝐒𝐚𝐯𝐢𝐧𝐠 𝐓𝐢𝐩: 𝐕𝐀𝐂𝐔𝐔𝐌 𝐲𝐨𝐮𝐫 𝐃𝐞𝐥𝐭𝐚 𝐝𝐚𝐭𝐚𝐬𝐞𝐭𝐬. Managing Delta tables with frequent DML operations can be a storage intensive work, especially when cloud providers charge for all stored data, regardless of its relevance. ⚡ But there is a solution and I have tested it out myself.– 𝐃𝐞𝐥𝐭𝐚 𝐭𝐚𝐛𝐥𝐞 𝐯𝐚𝐜𝐮𝐮𝐦𝐢𝐧𝐠. For those unfamiliar, every operation on a delta table spawns a new version, bloating the underlying data files. Enter vacuuming – a solution to reclaim wasted space by removing older, unused data files beyond a specified retention period. Default? Seven days. The result? Astounding!!. Here are the numbers from one of my tests: 📊 Pre-Vacuum Table Size: 212 TB 📉 Post-Vacuum Table Size: 3 TB 💡 Yes, you read that right – a staggering 98% reduction in storage overhead! 🚨 But, like any solution, there are trade-offs. Vacuuming sacrifices the ability to time-travel back beyond the retention period. However, let's be real – how often do we really need to dig into ancient table versions? 📍 To maximize the impact, it's crucial to complement vacuuming with appropriate storage lifecycle policies. Actively managing versioned data, perhaps by archiving or shifting to infrequent storage tiers, ensures ongoing optimization. Above results speak for themselves. Let's optimize, economize, and innovate together! 💡💻 📚 Reference reads: https://lnkd.in/gVWjF4ew #dataengineering #deltalake #storageoptimization #cloudcomputing #databricks #costsavings #optimization #bigdata #cloud #aws #gcp #azure

6

Serialization and deserialization (SerDes) in #Kafka #Streams convert data between binary and native types. Let's explore these concepts with examples: Serialization (Object to Binary) Serialization transforms data objects into a binary format for transmission or storage. Kafka stores and transmits data in binary format for efficiency. Serialization converts objects into bytes for network transmission or storage in Kafka topics. When producing messages to a Kafka topic, you serialize key and value objects into byte arrays using serializers like `StringSerializer`, `IntegerSerializer`, `JsonSerializer`, or `AvroSerializer`. Deserialization (Binary to Object) Deserialization converts binary data back into its original object form. When consuming messages from Kafka topics, deserialization converts byte arrays back into their original data types. When consuming messages from a Kafka topic, you deserialize byte arrays back into key and value objects using deserializers like `StringDeserializer`, `IntegerDeserializer`, `JsonDeserializer`, or `AvroDeserializer`. Importance of #SerDes SerDes are crucial in Kafka for efficient data transmission, storage, and processing. SerDes convert data objects into compact binary formats, reducing message size and improving network performance. Serialized data occupies less disk space, optimizing storage in Kafka brokers. SerDes enable compatibility between producers and consumers using different languages or platforms. Serialization frameworks like Avro, Protobuf, or JSON Schema support schema evolution, ensuring compatibility between evolving data structures. Custom SerDes Custom serializers and deserializers are necessary when default Kafka SerDes do not meet specific requirements. Custom Data Formats:For unsupported data formats (e.g., XML, Protocol Buffers), create custom SerDes. Complex Data Types: Custom SerDes handle complex data types requiring specialized serialization. Performance Optimization: Custom SerDes can optimize performance for specific use cases. Integration with External Systems: Ensure compatibility with external systems using custom data formats. Data Transformation: Implement custom transformations before serialization or after deserialization. Schema Registry A Schema Registry manages schemas for data exchanged in Kafka. It ensures schema validation, compatibility, and evolution, providing centralized schema management and versioning. Advantages: - Centralized schema management - Schema validation and compatibility checks - Versioning of schemas - Reduced data inconsistencies Conclusion SerDes in Kafka Streams are essential for efficient data handling, enabling seamless integration, compatibility, and schema management. Custom SerDes handle specific data formats and complex data types, while a Schema Registry ensures schema validation and evolution.

10

While preparing for Databricks Gen AI certification, I realized how much transformation they have been able to do since their inception! From a Spark/Hadoop company to Lakehouse/ Datawarehouse and now to a ML/ Gen AI company, is a good case study in leadership and change management. I am bullish on Databricks skills for next few years...

27

5 Comments

Converting a list to string, with a delimiter. Implementation of inbuilt mkString() function in SCALA. #SCALA #SCALADEVELOPER #BIGDATADEVELOPER #BIGDATA Karthik K. def main(args: Array[String]): Unit ={ //var a = "Tanuj" //lowerCase(a) var s = "" var l = List(1,2,3,4,5,6,7,8) for( i <- 0 until l.length) { if( i == l.length -1) { s = s.concat(l(i).toString) } else { s = s.concat(l(i).toString) s = s.concat(':'.toString) } } print("The elements of list when converted to string are:-" + s) } } OUTPUT:

3

Data Architect (c2c) 12 years of experience REMOTE sohini@cloudingest.com  Skills- DataBricks, Snowflake, Data Modelling  ((certification preferred )) 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

3

I'm super pumped about Delta Lake UniForm and Apache XTable (Incubating)! The introduction of Delta Lake UniForm and Apache XTable, which now support Delta Lake, Apache Hudi, and Apache Iceberg formats, is a significant advancement in data management. This development allows for seamless interoperability between these formats, reducing the risk of vendor lock-in and enhancing flexibility. The impact is substantial, as it simplifies data workflows, promotes open standards, and ultimately leads to more efficient and effective data management practices.

9

🚀 Optimise INSERT, UPDATE and DELETE in PostgreSQL using best indexing strategy! 📊 Struggling with sluggish DML operations due to indexes? Refer my new medium blog https://lnkd.in/gTxmyWaY which elaborates best indexing strategies to boost your PostgreSQL performance, especially for tables in the TB range. 📈 1. Evaluate and Refine Index Types:    - 🧹 Review Index Usage: Identify and drop unused indexes using `pg_stat_all_indexes`.    - 🔍 Choose the Right Index Type: Use B-tree for high-cardinality columns and BRIN for range queries on naturally sorted columns.    - ✂️ Partial Indexes: Index only frequently queried subsets to reduce size and improve speed.    - 📚 Covering Indexes: Ensure indexes cover common queries to minimize extra lookups. 2. Remove Redundant Indexes:    - 🗑️ Unused indexes can slow down operations. Regularly review and drop them to save space and enhance performance. 3. Advanced Index Types:    - ⚡ Expression Indexes: Index results of expressions or functions for efficient querying based on calculations.    - 🧩 Partial Indexes: Optimize for specific data subsets to reduce index size. 4. Use Efficient Index Types:    - 🏷️ BRIN Indexes: Ideal for large, naturally sorted tables. Efficient for range queries.    - 🔢 HASH Indexes: Perfect for exact-match queries on large datasets. 5. Reorganize Indexes:    - 🔄 Reindex During Off-Peak Hours: Schedule maintenance to minimize impact.    - ⚙️ REINDEX CONCURRENTLY: Reduce downtime by using PostgreSQL's concurrent reindexing. 6. Consider Index Maintenance Tools:    - 🛠️ pg_repack: Reorganize indexes without locking tables using this powerful extension.    - 🌐 Logical Replication: Perform maintenance on a replica, then promote it to primary. 7. Tune Configuration Parameters:    - 🔧 Increase maintenance_work_mem: Speed up index operations by allocating more memory. By following these strategies, you can ensure your PostgreSQL database performs optimally, even with massive datasets. Stay tuned for more tips on table structure optimization, partitioning, and sharding in our future posts! 🚀📈 #SQL #DatabaseOptimization #PostgreSQL #DataManagement #TechTips

44

Snowflake Vs MS-SQL Server Drop Stored Procedure https://lnkd.in/gUSZeC4f #snowflake #snowflakedevelopers #snowflakedatacloud #snowflakedataengineer #snowflakedeveloper #SnowflakeTips #sql #sqlserver #sqlserverdeveloper #sqlcode #SQLTips #HappyLearning #HappyCoding #LearnToLearn #Drop #DropProcedure #StoredProcedure #Procedure

12