<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Sean Zhang on Medium]]></title>
        <description><![CDATA[Stories by Sean Zhang on Medium]]></description>
        <link>https://medium.com/@seanzhang-data?source=rss-9c42db9f952b------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*PfDeHApiUKZQaqTucCNFxw.jpeg</url>
            <title>Stories by Sean Zhang on Medium</title>
            <link>https://medium.com/@seanzhang-data?source=rss-9c42db9f952b------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Thu, 19 Nov 2026 18:35:03 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@seanzhang-data/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Understanding HDFS from the Perspective of Supply and Demand]]></title>
            <link>https://seanzhang-data.medium.com/understanding-hdfs-from-the-perspective-of-supply-and-demand-3689d52a44b2?source=rss-9c42db9f952b------2</link>
            <guid isPermaLink="false">https://medium.com/p/3689d52a44b2</guid>
            <category><![CDATA[data-lake]]></category>
            <category><![CDATA[hdfs]]></category>
            <category><![CDATA[big-data]]></category>
            <category><![CDATA[hadoop]]></category>
            <dc:creator><![CDATA[Sean Zhang]]></dc:creator>
            <pubDate>Sun, 23 Feb 2025 23:01:46 GMT</pubDate>
            <atom:updated>2025-02-23T23:01:46.321Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/629/0*9r3iq5ym1CqqT63Q.png" /></figure><p>The Hadoop Distributed File System (HDFS) is designed to address the challenges of storing and processing large-scale data efficiently. From a supply-demand perspective, the demand arises from the need to handle massive data volumes, ensure fault tolerance, and automate storage management. The supply is provided by HDFS, which distributes data across multiple machines, ensures reliability through replication, and enables efficient access to large files.</p><p><strong>HDFS Architecture and Solutions</strong></p><p><strong>Storage Challenges and Solutions</strong></p><p>1. <strong>Storing Large Files That Exceed a Single Disk Capacity</strong></p><p>Large files that cannot fit on a single disk must be stored efficiently.</p><p>• <strong>Solution:</strong> HDFS splits large files into fixed-size blocks (default 128MB in Hadoop 2.x) and distributes these blocks across multiple machines (DataNodes).</p><p>2. <strong>Optimizing Disk Utilization and Reducing Wasted Space</strong></p><p>Storing multiple small files individually can cause inefficiencies.</p><p>• <strong>Solution:</strong> The block-based architecture of HDFS ensures better utilization of disk space by storing multiple blocks per disk, rather than requiring entire files to fit within a single storage unit.</p><p>3. <strong>Ensuring Fault Tolerance and Data Availability</strong></p><p>If a single block stored on one drive is lost, the entire file could become unreadable.</p><p>• <strong>Solution:</strong> HDFS replicates each block across multiple DataNodes. By default, each block has <strong>three copies</strong> stored on different machines. If one copy is lost due to a hardware failure, other copies remain accessible.</p><p>4. <strong>Efficiently Managing File Storage and Retrieval</strong></p><p>A mechanism is required to track where file blocks are stored and how they are retrieved.</p><p>• <strong>Solution:</strong> HDFS employs a <strong>master-slave architecture</strong> with a <strong>NameNode (master)</strong> that maintains the file system’s metadata, including file paths, block locations, and access permissions. <strong>DataNodes (slaves)</strong> store the actual blocks and periodically report their status to the NameNode.</p><p><strong>Role of the NameNode and DataNodes</strong></p><p>• <strong>NameNode:</strong></p><p>• Stores and manages metadata, such as file paths, file sizes, block locations, and permissions.</p><p>• Maintains a hierarchical file system structure (similar to a directory tree).</p><p>• Does not store actual file data, only metadata about stored blocks.</p><p>• Uses an in-memory metadata store for faster access.</p><p>• <strong>DataNodes:</strong></p><p>• Store actual data blocks.</p><p>• Periodically send “heartbeats” and block reports to the NameNode to confirm availability.</p><p>• Perform read and write operations requested by clients.</p><p><strong>Ensuring High Availability and Recovery</strong></p><p>1. <strong>Protecting NameNode Data Loss</strong></p><p>Since the NameNode holds critical metadata in memory, a failure can cause system-wide issues.</p><p>• <strong>Solution:</strong> HDFS writes a full snapshot of metadata, called <strong>fsimage</strong>, to persistent storage.</p><p>2. <strong>Handling Metadata Changes in Real-Time</strong></p><p>If a failure occurs right after writing fsimage to disk but before the next snapshot, recent changes could be lost.</p><p>• <strong>Solution:</strong> HDFS maintains an <strong>EditLog</strong>, which records metadata updates. Upon restart, the NameNode loads <strong>fsimage</strong> and replays the EditLog to restore the most recent state.</p><p>3. <strong>Ensuring NameNode High Availability (HA)</strong></p><p>If the NameNode fails, the entire file system can become inaccessible.</p><p>• <strong>Solution:</strong> Modern Hadoop deployments use <strong>Active and Standby NameNodes</strong> to ensure high availability. The active NameNode processes requests while the standby NameNode synchronizes metadata and can take over in case of failure.</p><p>4. <strong>Addressing Namenode Scalability</strong></p><p>A single NameNode may not be sufficient for very large-scale clusters.</p><p>• <strong>Solution:</strong> <strong>HDFS Federation</strong> allows multiple NameNodes, each managing a separate namespace and block pool, to improve scalability and eliminate bottlenecks.</p><p><strong>How HDFS Handles Data Storage and Retrieval</strong></p><p><strong>Writing a Large File to HDFS</strong></p><p>1. A client sends a request to the NameNode via the HDFS API.</p><p>2. The NameNode checks the file path, verifies user permissions, and updates metadata in the EditLog.</p><p>3. The NameNode selects suitable DataNodes for storing the file’s blocks based on system load and fault tolerance policies.</p><p>4. The client writes data directly to the assigned DataNodes. Each DataNode stores the data and replicates it according to the replication factor.</p><p>5. DataNodes report their block status to the NameNode periodically. The NameNode updates the EditLog and periodically merges it into the fsimage.</p><p><strong>Reading a Large File from HDFS</strong></p><p>1. A client requests a file from the NameNode.</p><p>2. The NameNode returns a list of blocks and the addresses of DataNodes storing them.</p><p>3. The client establishes a TCP connection with the nearest DataNode to read the blocks in parallel.</p><p>4. Data integrity is verified using checksums stored separately from the data blocks. If a block is corrupted, the client requests another copy from a different DataNode.</p><p>5. The client reassembles the blocks to reconstruct the original file.</p><p><strong>Bonus:</strong></p><p>HDFS is not as dominant as it was in the early days of big data, and its decline in popularity can be attributed to several key factors:</p><ol><li>Cloud Storage Solutions: automatic scaling and pay-as-you-go and easier integration to cloud-based streaming services like AWS Kinesis, and Google Pub/Sub. GCS and AWS S3 are object storage systems, Compared to distributed file storage systems, GCS and AWS S3 have lower storage costs, decoupled compute and storage, and a stronger API ecosystem. Some files storages are achieved by Data Lakes. Data Lakes allow schema-on-read, multi-format storage, AI/ML, and integrate with modern query engines like Apache Spark, Presto, Trino, and Google BigQuery.</li><li>HDFS has complex NameNode management and Security and Compliance management.</li></ol><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=3689d52a44b2" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Understanding Kafka vs RocketMQ from the Perspective of “Supply-Demand”]]></title>
            <link>https://seanzhang-data.medium.com/understanding-kafka-vs-rocketmq-from-the-perspective-of-supply-demand-e845cb2e2b4c?source=rss-9c42db9f952b------2</link>
            <guid isPermaLink="false">https://medium.com/p/e845cb2e2b4c</guid>
            <dc:creator><![CDATA[Sean Zhang]]></dc:creator>
            <pubDate>Sun, 23 Feb 2025 14:49:07 GMT</pubDate>
            <atom:updated>2025-02-23T14:49:07.938Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/473/0*YMjhHSbGE5ndT0BG" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/474/0*j0C5EgjoYHOHSI1q" /></figure><p>From a supply-demand perspective, demand is driven by the need for low-latency, high-throughput, and scalable data streaming. At the same time, supply is provided by efficient data transfer mechanisms like Kafka and RocketMQ, leveraging zero-copy techniques to optimize performance.</p><p>1. The Demand: High-Performance Streaming &amp; Messaging</p><p>With the growth of big data platforms, event-driven architectures, and real-time analytics, the key challenges include:</p><p>• Real-time processing of large data volumes.</p><p>• Minimizing latency in data transfer.</p><p>• Optimizing CPU and memory usage to improve throughput.</p><p>Traditional data transfer mechanisms involve multiple copies of data, which can reduce efficiency. Let’s analyze the original data transfer architecture before introducing zero-copy techniques.</p><p>2. The Traditional Data Transfer Process</p><p>Before zero-copy optimizations, data transfer involved multiple copies between disk, kernel space, and user space. The process typically followed these steps:</p><p>1. Reading from Disk to Kernel Buffer</p><p>• The application initiates a read operation, causing data to be copied from disk storage to a kernel buffer.</p><p>2. Copying from Kernel Buffer to User Buffer</p><p>• The kernel buffer creates a copy of the data in a user-space buffer, so the application can process it.</p><p>3. Copying from User Buffer to Socket Buffer</p><p>• If the application wants to send the data over a network, it copies the data from the user buffer to the socket buffer (still within kernel space).</p><p>4. Copying from Socket Buffer to Network Interface</p><p>• The network interface card (NIC) reads from the socket buffer and sends the data over the network.</p><p>Each of these copies consumes CPU and memory bandwidth, reducing overall performance. The sequence of operations is:</p><p>Read() -&gt; Read() Return -&gt; Write() -&gt; Write() Return</p><p>3. The Supply: Zero-Copy Technology</p><p>To optimize performance, modern message queue systems like Kafka and RocketMQ leverage zero-copy technology, which reduces unnecessary data copying between user space and kernel space.</p><p>3.1 mmap() (Memory-Mapped File I/O)</p><p>Instead of copying data from kernel space to user space, mmap() creates a shared memory mapping between disk files and an application’s memory space. This allows applications to access file contents directly as if they were in memory, eliminating an explicit read-copy operation.</p><p>Advantage: Avoids an additional copy from kernel space to user space.</p><p>RocketMQ primarily uses mmap() for message storage, improving efficiency in handling large message logs.</p><p>3.2 sendfile() (Zero-Copy Network Transmission)</p><p>The sendfile() system call allows data to be transferred directly from disk to the network socket buffer, bypassing the user space completely. Instead of copying data into an application’s buffer, the kernel directly streams it from the file system to the network stack.</p><p>Advantage: No CPU involvement in copying from user space to the socket buffer, as DMA (Direct Memory Access) handles the transfer.</p><p>Kafka uses sendfile() to efficiently send log segments over the network, reducing CPU load and improving performance.</p><p>4. Why the Difference?</p><p>• Kafka prioritizes efficient log replication and event streaming, making sendfile() the best choice for its use case.</p><p>• RocketMQ prioritizes reliable message storage and delivery, so mmap() improves its performance.</p><p>5. Enhancements: Batching &amp; Compression</p><p>To further increase throughput, both Kafka and RocketMQ use:</p><p>5.1 Batch Processing: Instead of processing individual messages, they aggregate multiple messages into batches, reducing overhead.</p><p>5.2 Data Compression: Techniques like Snappy, Gzip, and LZ4 are used to minimize network transmission costs.</p><p>6. Final Thoughts</p><p>• Zero-copy technology significantly improves message queue performance by reducing CPU and memory overhead.</p><p>• Kafka and RocketMQ use different zero-copy techniques (sendfile() vs. mmap()) to optimize their respective workloads.</p><p>• Kafka excels in high-throughput data streaming, while RocketMQ is designed for enterprise messaging with built-in reliability features.</p><p>Which One Should You Use?</p><p>• If your focus is big data pipelines, event streaming, or log aggregation, Kafka is the best choice.</p><p>• If you need enterprise messaging with reliable delivery and dead-letter queues, RocketMQ is a strong option.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=e845cb2e2b4c" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Common Distributed System Patterns]]></title>
            <link>https://medium.com/geekculture/common-distributed-system-patterns-4cae3b010c06?source=rss-9c42db9f952b------2</link>
            <guid isPermaLink="false">https://medium.com/p/4cae3b010c06</guid>
            <category><![CDATA[distributed-systems]]></category>
            <dc:creator><![CDATA[Sean Zhang]]></dc:creator>
            <pubDate>Wed, 19 Feb 2025 01:11:21 GMT</pubDate>
            <atom:updated>2025-02-24T12:47:49.674Z</atom:updated>
            <cc:license>https://creativecommons.org/publicdomain/mark/1.0/</cc:license>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/440/0*Sz5-XGwiVW1HZN6-.png" /><figcaption>Concept of Distribtued Systems</figcaption></figure><h3><strong>1. Ambassador Pattern</strong></h3><p><strong>Best Use Case</strong></p><p>• Used when <strong>external services</strong> need to be <strong>proxies</strong> for internal services, handling cross-cutting concerns like authentication, logging, and monitoring.</p><p>• Helps services <strong>offload</strong> specific tasks (e.g., security, rate limiting) to a sidecar proxy.</p><p><strong>Real-Life Example</strong></p><p>• <strong>Envoy Proxy</strong> acts as an Ambassador for microservices in <strong>Service Mesh</strong> architectures (e.g., Istio).</p><p><strong>Key Features</strong></p><p>• Uses a <strong>proxy (sidecar pattern)</strong> to interact with external systems.</p><p>• Implements <strong>circuit breaking</strong>, <strong>retries</strong>, <strong>security</strong>, and <strong>observability</strong>.</p><p><strong>Advantages</strong></p><p>✅ <strong>Encapsulation</strong> — Abstracts communication complexity from core services.</p><p>✅ <strong>Scalability</strong> — Helps manage API calls efficiently.</p><p>✅ <strong>Improved Security</strong> — Handles authentication before requests hit core services.</p><p><strong>Disadvantages</strong></p><p>❌ <strong>Increased Latency</strong> — Extra network hops may slow performance.</p><p>❌ <strong>Complexity in Configuration</strong> — Requires proper management of proxy configurations.</p><h3>2. Circuit Breaker Pattern</h3><p><strong>Best Use Case</strong></p><p>• Used to <strong>prevent cascading failures</strong> in microservices by <strong>temporarily blocking requests</strong> to failing services.</p><p>• Ideal for systems with <strong>unstable dependencies</strong>, preventing excessive retries.</p><p><strong>Real-Life Example</strong></p><p>• <strong>Netflix Hystrix</strong> implements circuit breakers to prevent excessive load on failing services.</p><p><strong>Key Features</strong></p><p>• <strong>Open State:</strong> Stops calls when failures exceed a threshold.</p><p>• <strong>Half-Open State:</strong> Periodically tests if the service has recovered.</p><p>• <strong>Closed State:</strong> Resumes normal traffic after recovery.</p><p><strong>Advantages</strong></p><p>✅ <strong>Fault Isolation</strong> — Protects services from failures.</p><p>✅ <strong>Prevents Resource Exhaustion</strong> — Avoids excessive retries.</p><p>✅ <strong>Faster Failure Recovery</strong> — Gives services time to recover.</p><p><strong>Disadvantages</strong></p><p>❌ <strong>Potential False Positives</strong> — A temporary spike in failures might trigger it unnecessarily.</p><p>❌ <strong>Extra Configuration Complexity</strong> — Needs careful tuning for threshold limits.</p><h3>3. CQRS — Command Query Responsibility Segregation</h3><p><strong>Best Use Case</strong></p><p>• Used when <strong>reading and writing operations</strong> have <strong>different performance/scaling requirements</strong>.</p><p>• Common in <strong>event-driven architectures</strong> and <strong>high-traffic systems</strong>.</p><p><strong>Real-Life Example</strong></p><p>• <strong>E-commerce systems</strong> (e.g., Amazon) separate <strong>order placement (write-heavy)</strong> from <strong>inventory lookup (read-heavy)</strong>.</p><p><strong>Key Features</strong></p><p>• <strong>Command Side</strong> — Handles writes (mutations).</p><p>• <strong>Query Side</strong> — Optimized for reads.</p><p>• <strong>Eventual Consistency</strong> — Read and write models may be slightly out of sync.</p><p><strong>Advantages</strong></p><p>✅ <strong>Optimized Performance</strong> — Separate read/write scaling improves efficiency.</p><p>✅ <strong>Better Security &amp; Compliance</strong> — Prevents unauthorized updates through read models.</p><p>✅ <strong>Supports Event Sourcing</strong> — Complements an event-driven approach.</p><p><strong>Disadvantages</strong></p><p>❌ <strong>Complex Data Management</strong> — Requires synchronization between read and write stores.</p><p>❌ <strong>Higher Development Overhead</strong> — Needs more coding effort compared to CRUD.</p><h3>4. Event Sourcing Pattern</h3><p><strong>Best Use Case</strong></p><p>• Used when <strong>capturing a complete history of changes</strong> in a system is important.</p><p>• Ideal for <strong>financial transactions, auditing, and compliance</strong>.</p><p><strong>Real-Life Example</strong></p><p>• <strong>Banking systems</strong> record every transaction as an immutable event instead of updating account balances directly.</p><p><strong>Key Features</strong></p><p>• <strong>Event Log as Source of Truth</strong> — Stores every state change as an event.</p><p>• <strong>Immutable Data Store</strong> — Cannot alter past events.</p><p>• <strong>Event Replay</strong> — Can reconstruct the system’s state at any point.</p><p><strong>Advantages</strong></p><p>✅ <strong>Complete History &amp; Auditability</strong> — Every change is recorded permanently.</p><p>✅ <strong>Easy Debugging &amp; Rollbacks</strong> — Can reconstruct past states if needed.</p><p>✅ <strong>Supports CQRS &amp; Microservices</strong> — Complements distributed systems well.</p><p><strong>Disadvantages</strong></p><p>❌ <strong>High Storage Requirement</strong> — Logs grow indefinitely.</p><p>❌ <strong>Complex Event Processing</strong> — Requires careful design for event ordering and replaying.</p><h3>5. Leader Election Pattern</h3><p><strong>Best Use Case</strong></p><p>• Used when <strong>a distributed system needs a single coordinator</strong> for consistency.</p><p>• Ideal for <strong>distributed databases, consensus algorithms, and load balancers</strong>.</p><p><strong>Real-Life Example</strong></p><p>• <strong>Apache Zookeeper</strong> manages leader election in distributed systems like <strong>Kafka</strong>.</p><p><strong>Key Features</strong></p><p>• <strong>Consensus Mechanism</strong> — Uses algorithms like <strong>Raft</strong> or <strong>Paxos</strong>.</p><p>• <strong>Automatic Failover</strong> — Promotes a new leader if the current one fails.</p><p>• <strong>Single Point of Control</strong> — Leader manages coordination tasks.</p><p><strong>Advantages</strong></p><p>✅ <strong>Consistency &amp; Coordination</strong> — Ensures no conflicting changes in distributed systems.</p><p>✅ <strong>Fault Tolerance</strong> — Can quickly recover from leader failures.</p><p><strong>Disadvantages</strong></p><p>❌ <strong>Single Point of Failure Risk</strong> — If not properly handled, the system might get stuck without a leader.</p><p>❌ <strong>Election Overhead</strong> — Consensus protocols introduce delays.</p><h3>6. Pub/Sub (Publish-Subscribe) Pattern</h3><p><strong>Best Use Case</strong></p><p>• Used when <strong>services need to communicate asynchronously</strong> without tight coupling.</p><p>• Ideal for <strong>event-driven microservices, real-time notifications, and IoT systems</strong>.</p><p><strong>Real-Life Example</strong></p><p>• <strong>Google Cloud Pub/Sub</strong> enables real-time messaging in distributed applications like <strong>YouTube notifications</strong>.</p><p><strong>Key Features</strong></p><p>• <strong>Decoupled Communication</strong> — Publishers and subscribers operate independently.</p><p>• <strong>Scalability</strong> — Can handle millions of messages per second.</p><p>• <strong>Asynchronous Processing</strong> — Supports event-driven architecture.</p><p><strong>Advantages</strong></p><p>✅ <strong>Loose Coupling</strong> — Services can evolve independently.</p><p>✅ <strong>High Scalability</strong> — Easily supports large-scale event distribution.</p><p>✅ <strong>Resilient to Failures</strong> — Message queues buffer events for delayed processing.</p><p><strong>Disadvantages</strong></p><p>❌ <strong>Event Ordering Complexity</strong> — Ensuring messages are processed in order can be tricky.</p><p>❌ <strong>Potential Message Duplication</strong> — Requires deduplication mechanisms.</p><h3>7. Sharding Pattern</h3><p><strong>Best Use Case</strong></p><p>• Used when <strong>databases need to scale horizontally</strong> to handle large datasets.</p><p>• Ideal for <strong>high-traffic applications like social media platforms</strong>.</p><p><strong>Real-Life Example</strong></p><p>• <strong>Facebook</strong> sharded its user database to distribute traffic across multiple servers.</p><p><strong>Key Features</strong></p><p>• <strong>Partitioning Data Across Multiple Servers</strong> — Each shard stores a subset of data.</p><p>• <strong>Independent Scaling</strong> — Different shards can grow independently.</p><p>• <strong>Improved Performance</strong> — Reduces bottlenecks in large datasets.</p><p><strong>Advantages</strong></p><p>✅ <strong>Better Performance</strong> — Reduces query load on a single database.</p><p>✅ <strong>Scalability</strong> — Can handle growing user bases efficiently.</p><p>✅ <strong>Fault Isolation</strong> — Failure in one shard doesn’t affect others.</p><p><strong>Disadvantages</strong></p><p>❌ <strong>Complex Data Management</strong> — Requires logic to route queries to the correct shard.</p><p>❌ <strong>Rebalancing Challenges</strong> — Moving data between shards can be difficult.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=4cae3b010c06" width="1" height="1" alt=""><hr><p><a href="https://medium.com/geekculture/common-distributed-system-patterns-4cae3b010c06">Common Distributed System Patterns</a> was originally published in <a href="https://medium.com/geekculture">Geek Culture</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to pass Google Certified Professional Data Engineer Exam]]></title>
            <link>https://seanzhang-data.medium.com/how-to-pass-google-certified-professional-data-engineer-exam-54ee589468dd?source=rss-9c42db9f952b------2</link>
            <guid isPermaLink="false">https://medium.com/p/54ee589468dd</guid>
            <category><![CDATA[gcp-certification]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[data-engineer]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[gcp]]></category>
            <dc:creator><![CDATA[Sean Zhang]]></dc:creator>
            <pubDate>Thu, 30 Mar 2023 22:30:59 GMT</pubDate>
            <atom:updated>2023-03-30T22:30:59.224Z</atom:updated>
            <content:encoded><![CDATA[<p>I obtained my certifications of GCP Associate Cloud Engineer and Professional Data Engineer with 3 years of work experience and 1 year of cloud experience. Here are some thoughts on the preparation for them:</p><ol><li><strong>Pick the exam that you are specialized in. </strong>I am a data engineer so<strong> </strong>I find two exams with similar difficulty. I do not have a lot of DevOp/Architect experience so the Associate Cloud Engineer exam was not easy for me compared to the Professional Data Engineer Exam. I have experience in the Data Science field so my next certification will be ML. Similarly, if you come from a Linux IT/Cybersecurity background, go for security certification, if you are a developer, go for DevOps.</li><li><strong>Go with the lowest level of Certification if you do not have Cloud Experience in Azure, GCP, or AWS. </strong>Before obtaining these two certifications, I had an AWS Cloud Practitioner certification. The exam was easy and in the process of preparing, you will have a great understanding of the services the cloud platforms provide. It will also give you a sense of feeling the gap between all the study materials and the actual exam. The exam questions are generally harder than the practice exams.</li><li><strong>Hands-On is more important than the exam itself. </strong>I do not recommend taking classes on Udemy and then just taking the exam. I finished my Udemy class on Associate Cloud Engineer and I found it hard to remember all the concepts without utilizing them. Enterprises are hiring cloud experts to do the job instead of telling the concepts. I recommend <strong>ACloudGuru</strong> instead because they provide hands-on labs and very detailed step-by-step instructions on their own sandbox environment. The sandbox is exactly the same as the real cloud so you do not have to worry about the expenses generated. ACloudGuru also provides essential skills beyond the concepts: Linux Command, CLI command, SDK setup, Containers Deployment, Network Setup, etc.</li></ol><p>Below is how I structured my knowledge on the Data Engineer Exam:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*jgcLF8UKPIohla6pvqM4zw.png" /><figcaption>GCP Data Engineer Knowledge Structure</figcaption></figure><p>Data Engineer Structure: Data Source -&gt; ETL -&gt; Storage / Database Management-&gt; Analysis/Application Feed-&gt; Security&amp;Monitoring. With these 5 concepts in mind, you can expand your knowledge in a systematic way.</p><p><strong>Most of the questions will come in the formats of :</strong></p><p><strong>In this situation, with these requirements, what should you do?</strong></p><p>The requirements usually indicate answers:</p><blockquote>Real-Time = Pub/Sub, Data Warehouse = BigQuery, Unstructured = DataProc, Beam = Dataflow, Global=Cloud Spanner, Under 30TB relational = Cloud SQL, , Non-Developer = Data Prep etc.</blockquote><blockquote>Note: Little details will show up too(actual BigQuery query and actual YAML files). These tricks are here to help you if you do not know the answer to the question at all.</blockquote><p>Very Important Concepts to pass the exam:</p><ol><li>Apache Beam: Dataflow will show up <strong>multiple times</strong> in the exam. It is important to understand Beam programming.</li><li>BigQuery: as the data warehouse solution, query performance questions will show up multiple times too. You should learn the concept of partitioning, clustering, and table design.</li><li>DataProc: Serverless Datalake solution on top of Spark.</li><li>It is highly recommended you read Google Docs on these services as some questions are directly from the doc page. They are under the Product tab of <a href="https://cloud.google.com/alloydb">https://cloud.google.com/</a></li></ol><p>Final Tips:</p><ol><li>If you have taken any basic machine learning class, there is no need to review. Questions on machine learning are very basic.</li><li>If you do not have experience with the Apache Family(I believe a lot of data analysts do not), learn the concept before you move to the preparation for the Data Engineer exam.</li></ol><blockquote>sharpening the axe won’t delay the cutting of firewood; good preparation saves work time.</blockquote><p>3. Do-It-On-GCP: actual deployment will help you with remembering the concepts and truly show your skills in the organization.</p><p>At last, I wish you pass the exam soon!</p><p>Please leave a thumb if you like my article.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=54ee589468dd" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Python Tips OLD Pythoners Don’t Know]]></title>
            <link>https://seanzhang-data.medium.com/python-tips-oldpythoners-don-t-know-d9b8b4429966?source=rss-9c42db9f952b------2</link>
            <guid isPermaLink="false">https://medium.com/p/d9b8b4429966</guid>
            <category><![CDATA[python]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[data-analytics]]></category>
            <dc:creator><![CDATA[Sean Zhang]]></dc:creator>
            <pubDate>Sat, 28 Jan 2023 17:00:16 GMT</pubDate>
            <atom:updated>2023-01-28T17:03:39.464Z</atom:updated>
            <content:encoded><![CDATA[<p>Please follow and star as this page will be updated in the future.</p><p>No small talk:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/36149d84edf41a2922a2dc9ed893b165/href">https://medium.com/media/36149d84edf41a2922a2dc9ed893b165/href</a></iframe><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d9b8b4429966" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Create your First SSIS Project Step by Step]]></title>
            <link>https://seanzhang-data.medium.com/create-your-first-ssis-project-step-by-step-199c22a21b8f?source=rss-9c42db9f952b------2</link>
            <guid isPermaLink="false">https://medium.com/p/199c22a21b8f</guid>
            <category><![CDATA[ssis-package-sql]]></category>
            <category><![CDATA[ssis-training]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[sql]]></category>
            <dc:creator><![CDATA[Sean Zhang]]></dc:creator>
            <pubDate>Wed, 14 Sep 2022 01:34:50 GMT</pubDate>
            <atom:updated>2022-11-14T18:10:42.558Z</atom:updated>
            <content:encoded><![CDATA[<p>SSIS as an “outdated” ETL tool, is still massively used by organizations. Today we are going to install SSIS and build the first SSIS project to bring any text file in a folder into our MSSQL database.</p><p>First of all, please leave a thumb and follow me for more “straight to work” articles.</p><p>Second of all, don’t get frustrated if VS 2019 crashes on your Windows 11.</p><h3>Installation of Visual Studio and SSIS</h3><ol><li>Visual Studio Community 2019(Free) download link: <a href="https://visualstudio.microsoft.com/vs/older-downloads/">Click Here</a></li></ol><p><strong>It seems like the SSIS extension is not available on the 2022 version yet.</strong></p><p>2. After the download: Create a new project -&gt; search blank project</p><p>Navigate to the menu bar: extension -&gt; Search SSIS and it will bring you to the SSIS installation page.</p><p><strong>IMPORTANT: CLOSE VS WINDOW BEFORE INSTALLING SSIS EXTENSION</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/860/0*iLuJl_sNMi0o1Z-3.png" /></figure><p>3. After SSIS installation, reopen VS, create a new Project, and search Integration Services to create the project.</p><p>4. Create a text file and copy the data source from below.</p><pre>Name, Rating, Num_People_Read,Category,Date<br>DynamicReader,3.5,268,Books,712014<br>DynamicReader,3.5,268,Books,712014<br>DynamicReader,3.5,268,Books,712014</pre><p>Create a table with column names and types as nvarchar(50) for convenience.</p><p>5. Right-click Connection Manager and add a new connection: ABO.NET.</p><p>C# and VB are supported languages in VS. You can run Python or R script as well, which will require CMD to play the role instead of native features in SSIS (Hooray Microsoft Family). Use windows authentication or service account to establish the Server and Database connection. Rename the connection to DBConnection(so I can mask server in the code, thanks).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/347/1*4WdXTU8lsYjsBczN6LaqQg.png" /></figure><p>6. Control Flow -&gt; Right Click Canvas -&gt;Variables -&gt; Add Variables Icon(YES THEY DON’T HAVE ICON HINT BUT IT IS THE FIRST ONE).</p><p>Var1: Filepath, String, YOUR_FILE_PATH</p><p>Var2: TableName, String, YOUR_TABLE_NAME</p><p>7. Right-Click canvas and add a Script Task. Edit the task and embed the two variables we created into ReadOnlyVariables.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/803/1*got9_ZadwRPRd8msjRTJig.png" /></figure><p>8. Replace the main function with the following code (with comments of course).</p><pre><a href="https://gist.github.com/zhangzixuan1997/e850c1fc904b470769a9e578cdaab537">https://gist.github.com/zhangzixuan1997/e850c1fc904b470769a9e578cdaab537</a></pre><p>9. Save and execute the Script Task.</p><p>10. SSMS check:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/834/1*Zq74yD9M7WYmj1zGoE9aYg.png" /></figure><h3>References:</h3><p><a href="https://docs.microsoft.com/en-us/sql/integration-services/deploy-packages-with-ssis?view=sql-server-ver16">Deploy Packages with SSIS - SQL Server Integration Services (SSIS)</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=199c22a21b8f" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[BigQuery 102-Practical Use Cases]]></title>
            <link>https://seanzhang-data.medium.com/bigquery-102-practical-use-cases-735ff9c0f5de?source=rss-9c42db9f952b------2</link>
            <guid isPermaLink="false">https://medium.com/p/735ff9c0f5de</guid>
            <category><![CDATA[gcp]]></category>
            <category><![CDATA[data-analtyics]]></category>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[database]]></category>
            <dc:creator><![CDATA[Sean Zhang]]></dc:creator>
            <pubDate>Fri, 05 Aug 2022 01:40:52 GMT</pubDate>
            <atom:updated>2022-08-05T01:40:52.507Z</atom:updated>
            <content:encoded><![CDATA[<p>If you find this article useful, please follow me. <strong>I write articles about skills you use in your daily work.</strong></p><h3><strong>102.1 — I want my table back</strong></h3><p>To retrieve a table at a timestamp, use SYSTEM_TIME:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/679/1*tFuB_Ak2FyEm-BSHAV_plA.png" /></figure><p>To restore a table:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/704/1*fmrbPgpHZC3tzAX4AHaCRA.png" /></figure><p>To get the table back to the prod environment:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/564/1*RHO-tqtLeUbfo-dSHr7nxA.png" /></figure><p>If the table has been deleted:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*dCSSmQVkKunl0e2p4AaNTg.png" /></figure><h3>102.2 I need to control access to the tables I own.</h3><p>When you give a service account dataset level, users have access to all</p><p>the tables in the dataset.</p><p>— To grant on a table level:</p><pre>Click Table, Share Table instead of Share dataset</pre><p>— If there are certain columns you don’t want to share with the user:</p><pre>Use SELECT to filter columns and use WHERE to filter rows.<br>Save result: Set a destination table for query results.</pre><pre>Downside: This derived table is a PoT snapshot table, so there is a lag.</pre><p>— You can create a view instead</p><pre>After you created the view, if the user does not have access to the original table, they cannot retrieve data from the view. So you will have to grant the view to run the query on your behalf following the steps below:</pre><pre>Go to original table </pre><pre>1. Click “Share” — Authorized Views — Column level permission (E.g. PII)— Click Through Schema, Set, Data Catalog — Policy Tags.</pre><pre>2. Create tags like ‘high (e.g. SSN)’, ‘medium(e.g. Customer Name)’</pre><pre>3. Go back to table, select schema, add policy tag to the table.</pre><pre>Note: IAM like BigQuery Admin will be overriden by Policy Tags.</pre><pre>To make that tag’s data avaialble to a user:</pre><pre>— Data Catalog — Add members to a tag with Fine-Grained Reader role.</pre><h3>102.3 Querying is slow or generates a lot of costs.</h3><p>Usually, there is a trade-off between a normalized table and a de-normalized table. Normalized tables use the Snowflake/Star schema, which requires a lot of joins thus increasing the cost of querying. De-normalized tables (Storing as many fields as possible in one table)require fewer joins and usually generate less cost in BigQuery.</p><p>BigQuery offers better approaches: <strong>line. item, partitions, and clusters.</strong></p><p>For nested structure in big query, here is the definition on Google Doc(It is really not as intuitive):</p><pre>To create a column with nested data, set the data type of the column to RECORD in the schema. A RECORD can be accessed as a <a href="https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#struct_type">STRUCT</a> type in standard SQL. A STRUCT is a container of ordered fields.</pre><pre>To create a column with repeated data, set the <a href="https://cloud.google.com/bigquery/docs/schemas#modes">mode</a> of the column to REPEATED in the schema. A repeated field can be accessed as an <a href="https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#array_type">ARRAY</a> type in standard SQL.</pre><pre>A RECORD column can have REPEATED mode, which is represented as an array of STRUCT types. Also, a field within a record can be repeated, which is represented as a STRUCT that contains an ARRAY. An array cannot contain another array directly. For more information, see <a href="https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#declaring_an_array_type">Declaring an </a><a href="https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#declaring_an_array_type">ARRAY type</a>.</pre><p>Full Use Cases as Below:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*INBRRPh3YHlGkfL7Ui5lLg.png" /></figure><h3>References:</h3><blockquote>“BigQuery: Converting Normalized Datasets to Alternative Schema.” <em>BigQuery: Converting Normalized Datasets to Alternative Schema</em>, 15 Nov. 11AD, <a href="https://www.youtube.com/watch?v=XKpXBvdOOM4&amp;list=PLuJRcdtonlDCzqZZ-t5-yoxcO97_I6P6B.">https://www.youtube.com/watch?v=XKpXBvdOOM4&amp;list=PLuJRcdtonlDCzqZZ-t5-yoxcO97_I6P6B.</a></blockquote><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=735ff9c0f5de" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Advanced SQL Performance Tuning]]></title>
            <link>https://seanzhang-data.medium.com/advanced-sql-performance-tuning-6553e2f4afd9?source=rss-9c42db9f952b------2</link>
            <guid isPermaLink="false">https://medium.com/p/6553e2f4afd9</guid>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[sql]]></category>
            <dc:creator><![CDATA[Sean Zhang]]></dc:creator>
            <pubDate>Mon, 25 Jul 2022 20:07:36 GMT</pubDate>
            <atom:updated>2022-07-25T20:07:36.344Z</atom:updated>
            <content:encoded><![CDATA[<p>I post articles that are related to daily work and not commonly shown in textbooks. Please follow me or throw me a thumb if you find this article useful.</p><p>If you are looking for quick tips to tune the SQL performance, please check my first article: <a href="https://seanzhang-data.medium.com/sql-performance-tuning-d57c7740e6c7#11d8-32f52b5a6280">SQL Performance Tuning</a>.</p><p><strong>Effectiveness:</strong></p><p>Hardware Upgrade &lt; System Design &lt; Database Design and Architecture.</p><p><strong>Cost:</strong></p><p>Hardware Upgrade &gt; System Design &gt; DB D&amp;A &gt; SQL Query &amp; Index</p><p>For API-based databases, there are principles for optimizing SQL performance:</p><ol><li>Reduce data request: proper data types and compress set up and proper index utilization.</li><li>CPU: Avoid sorting and whole table scan.</li><li>Reduce data returns: only return data that are needed to reduce Disk IO and Internet IO.</li><li>Reduce interactions: DML sets and stored procedures help reduce DB connection times.</li></ol><p>Summarize them into 2 topics: Index Management and Volume Reduce. We will focus on Index Management.</p><h3>Index Management</h3><p><a href="https://seanzhang-data.medium.com/sql-performance-tuning-d57c7740e6c7#11d8-32f52b5a6280">SQL Performance Tuning</a> listed situations where SQL does not use an index.</p><p>First, let us go through the underlying concept of the Index.</p><p>Note: Morden DB has upgrades and tuning of this concept. This article only illustrates the basic architectural design of the index system.</p><p>Index is like the “content” of the book. A table without a primary key is organized randomly row by row on the disk, instead, a table with a primary key index(or any other clustered index) is organized based on the b+ tree algorithm.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*xmBu5GBX1KGB2y_mSIfSGw.png" /><figcaption>Clustered Index — B+ Tree for Index</figcaption></figure><p>The tree algorithm will reduce the complexity from <em>O(n) </em>to <em>O(Log n)</em> as <em>math.log(number of rows, number of branches)</em>.</p><p>A Non-Clustered index logical order of index does not match physical stored order on the disk. The indexes on the above charts are all connected, thus leading to an organized physical order. A Non-Clustered Index is independent of other Non-Clustered indexes. It is also slower but smaller compared to Clustered Index.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*xRWvpnqx171M5BNvflw-RA.png" /><figcaption>Non-Clustered Index</figcaption></figure><p>However, DB has to rebuild the index after DML of update, insert and delete. We can dive deeper into this topic:</p><p><strong>Insert: </strong>insert will insert the data into the table and then into the index, thus insert will require index update for each option.</p><p>Not necessary to read: During the operation, SQL will check this value in the index. If the value is there, the new value will be inserted into the free space right behind; if no value matches, the value will be added to the leaf, then the value and the pointer will be added to the branch node. During this process, if the page is full, a new page will be created and half the index will be moved to the new page.</p><p><strong>Delete: </strong>When a record is deleted, all related index will be deleted. The cost is smaller compared to insert.</p><p><strong>Update: </strong>Only the field that has index will be updated. The cost is smaller compared to insert.</p><p>Please google for “creating automatic rebuilding SQL Index maintenance plan”.</p><p>As a DBA, we need to evaluate the considerations and priorities of DML operations(OLAP/OLTP) and choose the amount, choice, and maintenance plan for the index.</p><p><strong>To debug long queries and see how index can help with SSMS execution plan:</strong></p><p><strong>Identity Operations in the plan: </strong>Clustered Index Scan, Clustered Index Seek, Compute Scalar, Delete, Filter, Hash Match, Insert.</p><p><strong>Optimize Related Operations:</strong></p><p>Table Scan: Create clutered index.</p><p>Clustered Index Scan: Happens when there is non-clustered index inactive. Create a non-clustered index to help.</p><p>Hash Join: No index when two tables are joined. Created index on the columns used to join tables.</p><p>Nested Loops: Non-Clustered Index does not cover all the columns in the select.</p><p>RID Lookup: In the table, you have a non-clustered index but do not have clustered index. In this case, engine will search based on row ID. The clustered index should be created on this table.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=6553e2f4afd9" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[SQL Performance Tuning]]></title>
            <link>https://seanzhang-data.medium.com/sql-performance-tuning-d57c7740e6c7?source=rss-9c42db9f952b------2</link>
            <guid isPermaLink="false">https://medium.com/p/d57c7740e6c7</guid>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[mysql]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[data-analytics]]></category>
            <dc:creator><![CDATA[Sean Zhang]]></dc:creator>
            <pubDate>Mon, 18 Jul 2022 18:05:11 GMT</pubDate>
            <atom:updated>2022-07-21T15:22:47.765Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/700/1*ybMg0mvkOVx9btzgGoC78g.png" /></figure><p>SQL Textbooks teach us how to get the results by writing queries. In the production environment, query execution time matters just as much since it influences user experience or even leads to a production incident of “Runtime Out” due to platform threshold setup. For example, Tableau Server Admin usually set up a “runtime threshold” to automatically fail the backgrounder extract refresh if a query runs too long.</p><p>There are two major topics when we talk about SQL performance: Index Utilization and Avoiding Whole Table Reference. Before you apply any of these practices to your slow query, try to use EXPLAIN or EXPLAIN QUERY PLAN on the query to see how the database interprets it.</p><p>Here are the tips/tricks/best practices I have utilized in my SQL journey:</p><ol><li>String search</li></ol><p>When we perform LIKE %%, SQL automatically gives up index reference.</p><pre>--Before Tuning<br>SELECT * FROM USER WHERE USER_NAME LIKE &#39;%Sean%&#39;<br>--After Tuning<br>SELECT * FROM USER WHERE USER_NAME LIKE &#39;Sean%&#39;</pre><p>2. Avoid OR and IN Operator</p><p>OR operator is the “behind logic” of IN operator. E.g.</p><p>USER_ID IN (1, 3, 5) is translated into USER_ID = 1 OR USER_ID = 3 OR USER_ID = 5. Both IN and OR operators require SQL to rebuild the execution plan, which can be an expensive step sometimes.</p><pre>--For OR Operator<br>--Before Tuning<br>SELECT * FROM USER WHERE USER_ID = 1 OR USER_ID = 5</pre><pre>--After Tuning<br>SELECT * FROM USER WHERE USER_ID = 1<br>UNION<br>SELECT * FROM USER WHERE USER_ID = 5</pre><pre>--For IN or NOT IN operators<br>----If it is numeric filed:<br>--Before Tuning:<br>SELECT * FROM USER WHERE USER_ID IN (1,2,3,4,6)<br>--After Tuning:<br>SELECT * FROM USER WHERE USER_ID BETWEEN (1,6) AND USER_ID!=5</pre><pre>----If it is string field:<br>USE EXISTS in a subquery</pre><p>Utilizing these practices can be grammarly redundant sometimes. You don’t have to abandon the SQL-told way if the query runs within seconds. These practices can help if you have a long execution time.</p><p>3. WHERE 1=1</p><p>This query has been used by me for quite a long time since it makes the indent looks much neater in SQL. I just learned that WHERE 1=1 can actually cause SQL to do a table scan instead of an index scan. Need to validate this one!</p><p>4. ORDER BY aligning with WHERE</p><p>When the fields in ORDER BY and WHERE are the same, the index can be utilized more efficiently compared to when they are not.</p><p>A lot of times ORDER BY can be unnecessary when your query result is just fed into another platform like Tableau, where they will be sorted again, either on the same field or even a different field. If ORDER BY is needed, the numeric field is faster than the string(varchar) field.</p><p>5. UNION ALL vs UNION</p><p>UNION ALL is faster since UNION removes duplicates. If you are sure that there is no duplicate, use UNION ALL. If you are not sure about the duplicate, you can use UNION ALL and then remove duplicates in your code later, especially in the situation when you have to perform multiple UNIONs.</p><p>6. Multiple JOINs</p><p>When you are joining multiple tables together, try to put the big tables at first and smaller tables at last. MS SQL and Oracle SQL read tables from the right to the left and reading larger tables first makes the query faster. However, MySQL uses the opposite read sequence: you should put smaller tables first for MySQL.</p><p>7. Functions</p><p>A stored Procedure generally performs faster than a function. It is also more powerful compared to functions.</p><p>8. The WHERE magic</p><p>SQL interprets WHERE clause from the bottom to the top. Putting the WHERE clause which can filter out most data at last.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d57c7740e6c7" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Crack A/B Testing Interviews]]></title>
            <link>https://seanzhang-data.medium.com/crack-a-b-testing-interviews-6a7d91b4e12c?source=rss-9c42db9f952b------2</link>
            <guid isPermaLink="false">https://medium.com/p/6a7d91b4e12c</guid>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[digital-marketing]]></category>
            <category><![CDATA[a-b-testing]]></category>
            <category><![CDATA[jobs]]></category>
            <category><![CDATA[data-science]]></category>
            <dc:creator><![CDATA[Sean Zhang]]></dc:creator>
            <pubDate>Mon, 28 Jun 2021 23:35:51 GMT</pubDate>
            <atom:updated>2021-06-28T23:35:51.682Z</atom:updated>
            <content:encoded><![CDATA[<p>As a data science enthusiast, A/B Testing has always been in my interview questions. In this job-hunting process, I interviewed with TikTok (Product Marketing Analyst), Whole Food (Digital Marketing Campaign Result Analyst), and Sega of America (Data Analyst). I am writing this article to organize my thoughts on the interview questions I had.</p><p>A/B Test, AKA, split test, conversion rate optimization, multivariate testing, digital optimization, online experiment, growth-hacking, has been part of the job of Data Analysts. In this article, I will generalize and illustrate A/B testing concepts and tricks based on interview situations. There will be three topics: “Where &amp; What to A/B test”, “How to A/B test”, and “Result Analysis”. I also attached the learning road map of A/B testing tech at the end of the article if you want to dive deeper.</p><h3>1. Where &amp; What to A/B Test?</h3><p>1) Website Experience 2) Paid Search 3) Mobile App 4) Social Media Content Design 5) Email &amp; Push Notifications 6) Marketing Campaigns</p><p>Each of these topics can be expanded based on your understanding of customer journey mappings under different circumstances. There would be 4 types of elements to test on:</p><p><strong>1) Design Related:</strong> Site Page, Flow, Elements, etc.</p><p><strong>2) Model Related: </strong>Internal KPI and Business Models</p><p><strong>3) Algorithms Related:</strong> Backend Functionality &amp; Algorithms</p><p><strong>4) New Product/ Feature Launch</strong></p><p>It would be best if you can see the A/B testing objectives in the job descriptions. Before the interview, if you have figured out “what to A/B test”, you are already halfway there. So far, the lovely HRs already have two questions for you:</p><p><strong>What will be the metrics to deliver results?</strong></p><p>a. Based on “what to test”, you already have sets of metrics to answer. E.g., CTR for web design, Open Rate for Email, ROI for Ads Design, etc. You can check out the essential digital marketing concepts if you cannot list 5 KPIs for each situation.</p><p>b. Clarify the business objective with your interviewer. E.g., impressions and traffic would be great metrics for new product launching; DAU would be great for product development; ROAS and CTR would be great for getting money out of “Cash Cow”; Subscribe Rate &amp; Churn would be great for building customer loyalty.</p><p>c. Do not forget to mention guard metrics like bounce rate or churn rate. It still takes a lot to decide when data analysts already got a significant p-value. Guard metrics can help managers to balance short-term and long-term goals.</p><p>d. I usually simply ensure the interviewer knows my thought process instead of throwing out answers in 15 seconds, which decides the performance of a project.</p><h3>2.</h3><h3>How would you design this experiment?</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/684/1*v66e9lBbCVAKJ3hKzIiP2Q.png" /></figure><p>Tons of questions can deviate from each subtopic in each step. A general answer could be good enough for the interviewer to ask the next question. From here, I want to pick out several high-frequency questions in A/B testing:</p><p><strong>1.</strong> <strong>How do you decide on sample size and experiment length?</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/224/1*AG5zDBOa9XerfmURWWrD2A.png" /></figure><p>· Large-scale firms have their mature A/B testing documentations. You can either do research or make a reasonable guess to deliver more specific answers. For example, to test a button color on an Amazon page, you probably only need to run the experiment in seconds; to test a promotion strategy, you need to consider a lot more: time of day, day of the week, holidays, trends, news, platforms, spillovers, competitor behaviors. To avoid going off the topic, it is always a better solution to research before the interview.</p><p>· There are three factors influencing sample size: choice of metric, choice of diversion (page view or something with latency), and choice of population.</p><p><strong>2.</strong> <strong>How do you do power analysis?</strong></p><p>First question: What is Power? Power refers to the probability we correctly reject the null hypothesis. It equals to 1-Type II error (FN).</p><p>Welcome to statistics 101:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/830/1*-qIcisnSryNOZ9aTExEIsg.png" /></figure><p>If you want to figure out the math behind it, check my reference on StatQuest at the end of the article. If you want the sample size: google “Statistical Power Calculator”. Say I set the power to 0.8, and the calculator gives me a sample size of 9. I can say if I get 9 measurements per group, I will have an 80% chance of correctly rejecting the null hypothesis.</p><p>List of Statistics Parameters:</p><p>T-Test</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/403/1*qgypqUXbuxGiObFO6C28-g.png" /></figure><p><strong>Three types of T-Tests:</strong></p><p>a. Independent t-test: compare means of two groups.</p><p>b. Pair-Sample t-test: compare mean of one group before and after treatment.</p><p>c. One-Sample t-test: compare mean to a specific value.</p><p><strong>3.</strong></p><p><strong>What could be interferences between experiment and control groups?</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/330/1*bmaujoIaPpyNqa9779D7qw.png" /></figure><p>You can classify this problem into two types for the US internet industry: O2O or two-sided markets like Airbnb, Uber, eBay, and social network markets like Facebook, Instagram, and LinkedIn.</p><p><strong>For two-sided markets, the ATE is overestimated.</strong> For example, you want to launch a new feature in Airbnb so that customers can get a coupon from the host, and the goal is to help hosts get attention from customers. The hosts in the control group will spill over to the experiment group to participate, which leads to an overestimated ATE.</p><p><strong>How to solve it?</strong></p><p>You can combine DiD and Geo/Time-based randomization to solve this issue.</p><p><strong>For social network markets, the ATE is underestimated.</strong> For example, you launch a feature to drive customer engagements. After we randomize the samples, the objectives in the control group could also have access to the feature because of spillover in social networks, which leads to an underestimated ATE.</p><p><strong>How to solve it?</strong></p><p>1. You can create clusters to isolate users on the social network.</p><p>2. You can utilize the Ego-Network randomization strategy, which is a cluster strategy invented by LinkedIn.</p><p><strong>4.</strong> <strong>What other reasons might you have an incorrect result?</strong></p><p><strong>1)</strong> <strong>Effects:</strong></p><p>a) Novelty Effect: People welcome the changes now, but they might get less interested in the future. In this case, the ATE is overestimated.</p><p>b) Primary Effect: People are reluctant to the changes now, but they might get more interested in later stages. In this case, the ATE is underestimated.</p><p><strong>How to address this issue:</strong></p><p>a) Get novelty effect by comparing new users’ results from two groups.</p><p>b) DiD: Estimate novelty effect and primary effect by calculated the difference between new users and existing users first.</p><p><strong>2)</strong> <strong>Multiple testing Problem(p-hacking):</strong></p><p>If we have more than two variants, the p-value threshold should be smaller than 0.05 because we can reject the null hypothesis easier since we have more treatment groups.</p><p><strong>How to address this issue:</strong></p><p>a). Bonferroni correction: Divide the P-Value threshold by the number of tests.</p><p>b). Control FDR (False Discovery Rate).</p><p>c). Bootstrap.</p><p>d). FWER: Familywise Error Rate analysis.</p><p><strong>3)</strong> <strong>Back to obstacles in Causality Analysis (confounding relationships):</strong></p><p>a) Sample Selection Bias</p><p>b) Reverse Causality / Simultaneity</p><p>c) Omitted Variable Bias</p><p>Any “obstacles” you find in a later stage might require you to redesign the entire A/B test. There are techniques to solve these problems like DiD (Difference in Difference) analysis, RDD (Regression Discontinuity Design), blocking and clustering, etc.</p><h3>2. How do you interpret the result?</h3><p><strong>1.</strong> <strong>What if the result is not significant?</strong></p><p><strong>1). Break down </strong>the tests into groups: different platforms, regions, time of day or day of the week, and customer segmentation if applicable.</p><p><strong>2). Expand the test duration:</strong> this could lead to p-hacking, but it is feasible if there is barely any cost on the FN/FP objectives.</p><p><strong>3). Cross Checking:</strong> blocking strategy and non-parametric sign tests. Simpson’s paradox tells us when groups combine, the estimated ATE decreases or even disappears.</p><p><strong>2.</strong> <strong>Launch or not?</strong></p><p>This is more of a product sense interview question. These are the 4 aspects you can dive into when answering this question:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/456/1*oA_gHDuKMbqNZmQMuOgVbw.png" /></figure><p><strong>1). What will be the good business impact?</strong> After we conclude, the CTR will increase by 20%: what will be influenced positively: revenue, profit, brand awareness, brand image?</p><p><strong>2). Conflicting Result:</strong> When our CTR increased in the experiment group, are there conflicting results like a decrease in payment amount, or an increased cost in M&amp;S. A very typical example would be for website design, session duration is usually not a great choice because sometimes customers stay longer because they are interested, but sometimes they are just spending extra time looking for what they need, which leads to a decrease in CTR.</p><p><strong>3). Measure the risk:</strong> whether this launch involves any business ethics topics like customer privacies or other physical, emotional, or social concerns.</p><p><strong>4). Balance the short-term and long-term goals.</strong> Sometimes a short-term impression increase can conflict with the brand image or company’s mission in the long run.</p><h3>Study Guild to Dive Deeper</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/468/1*7dGnN5Jr71WaGqKl5PSv4Q.png" /></figure><p>This was the road map when I learned how to use R to perform A/B testing analysis. I have my sample code on my GitHub Repo.</p><h3>References</h3><p>[1] Georgiev, G. Z. (2019). <em>Statistical Methods in Online A/B Testing: Statistics for data-driven business decisions and risk management in e-commerce (1st ed.).</em> Independently published.</p><p>[2] Amy Gallo, (2017/06/28), <em>A Refresher on A/B Testing, Harvard Business Review</em>, (2021/06/22). <a href="https://hbr.org/2017/06/a-refresher-on-ab-testing">https://hbr.org/2017/06/a-refresher-on-ab-testing</a></p><p>[3] Kelly Peng, (2017/11/12), <em>A Summary of Udacity A/B Testing Course, TowardsDataScience, </em>(06/22/2021), <a href="https://towardsdatascience.com/a-summary-of-udacity-a-b-testing-course-9ecc32dedbb1">https://towardsdatascience.com/a-summary-of-udacity-a-b-testing-course-9ecc32dedbb1</a></p><p>[4] Emma Ding, (2021/01/17), <em>7 A/B Testing Questions and Answers in Data Science Interviews, TowardsDataScience</em>, (2021/06/22), <a href="https://towardsdatascience.com/a-summary-of-udacity-a-b-testing-course-9ecc32dedbb1">https://towardsdatascience.com/a-summary-of-udacity-a-b-testing-course-9ecc32dedbb1</a></p><p>[5] Shanelle Mullin, (2020/04/10), <em>The Complete Guide to A/B Testing: Expert Tips from Google, HubSpot and More, Shopify</em>, (2021/06/22), <a href="https://www.shopify.com/blog/the-complete-guide-to-ab-testing">https://www.shopify.com/blog/the-complete-guide-to-ab-testing</a></p><p>[6] YouTube. (2021/06/22), <em>Testing Theory, A/B Testing Intro: Why, What, Where, &amp; How to A/B Test, </em>(2019/01/03). <a href="https://www.youtube.com/watch?v=CH89jd4haRE">https://www.youtube.com/watch?v=CH89jd4haRE</a></p><p>[7] YouTube. (2021/06/22)<em>, Testing DataSciencePro, A/B Testing Problems in Data Science Interviews | Product Sense | Case Interview</em> (2021/01/13). <a href="https://www.youtube.com/watch?v=X8u6kr4fxXc">https://www.youtube.com/watch?v=X8u6kr4fxXc</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=6a7d91b4e12c" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>