<?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 Arihant Shashank on Medium]]></title>
        <description><![CDATA[Stories by Arihant Shashank on Medium]]></description>
        <link>https://medium.com/@arihant.shashank01?source=rss-72b159d37fef------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*I5EZgmxa3q0hP6dw</url>
            <title>Stories by Arihant Shashank on Medium</title>
            <link>https://medium.com/@arihant.shashank01?source=rss-72b159d37fef------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Thu, 19 Nov 2026 17:37:27 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@arihant.shashank01/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[From Data Islands to Universal Semantics — Context is the King]]></title>
            <link>https://medium.com/@arihant.shashank01/from-data-islands-to-universal-semantics-context-is-the-king-942330467f17?source=rss-72b159d37fef------2</link>
            <guid isPermaLink="false">https://medium.com/p/942330467f17</guid>
            <category><![CDATA[agentic-ai]]></category>
            <category><![CDATA[agents]]></category>
            <category><![CDATA[snowflake]]></category>
            <dc:creator><![CDATA[Arihant Shashank]]></dc:creator>
            <pubDate>Thu, 05 Nov 2026 09:51:19 GMT</pubDate>
            <atom:updated>2026-11-05T09:51:19.003Z</atom:updated>
            <content:encoded><![CDATA[<p><em>A practitioner&#39;s blueprint for federated data lakes, fabrics, mesh, and a human-in-the-loop semantic layer that lets every agent answer from the same truth</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/900/1*qxa2nJzEDlz-BGpsORK2tQ.png" /><figcaption><em>Universal semantics in motion: context is discovered from every data island, a steward certifies it, and agents answer with a trust badge. Interactive version linked at the end of the article.</em></figcaption></figure><p><em>Above: the whole idea in fifty seconds. Signals flow from several analytics accounts and a BI tool into a context engine, candidate business terms arrive at a steward gate with a confidence score, the steward approves or rejects what the threshold did not settle, certified terms land in a universal semantic layer, and only then do agents answer with a trust badge. The rest of this article explains why each of those stages exists and how to build them. An interactive version you can drive yourself is linked at the end.</em></p><p>Most of what gets written about AI agents assumes the data problem is solved. It is not. In a large enterprise the data sits in twenty or more analytics accounts across three clouds, several regions, a few acquisitions, one on-premises estate that refuses to die, and at least one country where the law says the data cannot leave. Every one of those places has its own definition of revenue.</p><p>I spend my days designing and running this kind of estate for a global company. For the past two years I have led a program that turns a scattered set of cloud data platforms into one federated data lake: a hub and spoke fabric, a domain-owned mesh on top, and a control plane that every consumer, human or agent, has to go through. The newest part of that work is the piece the animation above shows: a semantic layer that is universal across the estate, where business meaning is discovered automatically but only becomes shared truth once a human steward has certified it.</p><p>This article is the whole blueprint, written without any proprietary detail, so you can take it and apply it to your own estate. It is long because the subject deserves it. I use Snowflake and Databricks as reference points throughout, since those are the platforms most readers will be working with, but the architecture does not depend on either.</p><h3>1. Why this matters now: agents amplify whatever context they are given</h3><p>For a decade the cost of inconsistent semantics was bounded. If Sales and Finance defined net revenue differently, two dashboards disagreed, somebody noticed in a quarterly review, and an analyst reconciled them. Annoying, but survivable.</p><p>Agents remove that bound. An agent does not wait for a quarterly review. It answers thousands of questions a day, it answers them confidently, and it answers them using whatever context it can find. If the context is partial, the answer is partial. If two definitions exist, the agent picks one, and it will not tell you which unless you built it to.</p><p>Snowflake shared internal evaluation figures in a session I attended that make the point better than any argument. On a set of hard analytical questions, a frontier coding agent with a raw SQL connection to the warehouse answered 24 percent correctly. Their own agentic surface, without a semantic model, reached 47 percent. With a managed context layer on top of curated semantics, the same surface reached 86 percent. The model did not change between the second and third numbers. The context did.</p><p>That is the thesis of this article. The bottleneck for trustworthy AI in the enterprise is not the model and it is not the query engine. It is that agents operate with partial or conflicting context. Fixing that is an architecture problem, and it has a layered answer.</p><h3>2. What a large enterprise estate actually looks like</h3><p>Before the layers, the terrain. The picture below is a composite, but anyone who has worked inside a company with more than a few billion in revenue will recognise it.</p><p>There are analytics accounts in one cloud provider’s EU region, more in the same provider’s US region because a business unit sits there, a second cloud provider because manufacturing bought a different platform years ago, an acquired company that arrived with its own stack, an on-premises object store and Hadoop cluster that still collects the highest-volume operational data, an ERP system that is the system of record for everything financial, and a sovereign enclave in a country whose privacy law makes the data effectively unexportable.</p><p>Call each of these a data island. The word is not pejorative. Islands exist for reasons that will not go away:</p><p>• <strong>Data residency and regulation.</strong> Some data cannot leave a region. Some cannot leave a country. Some cannot be processed by a service running elsewhere, even if it never leaves storage.</p><p>• <strong>Business unit ownership.</strong> A unit that pays for its platform wants to run it. Centralising ownership removes accountability more often than it adds efficiency.</p><p>• <strong>Acquisitions.</strong> Every acquisition brings a stack. Migrating it is a multi-year project with no business value of its own.</p><p>• <strong>Cost accounting.</strong> Separate accounts give clean bills. Shared accounts give arguments.</p><p>• <strong>Latency and locality.</strong> Operational data is best processed near where it is produced.</p><p>The goal of a federated architecture is not to eliminate islands. It is to make them interoperate without copying everything into one place, and without each pair of islands inventing its own connection.</p><h3>The three failure modes</h3><p>When islands are left to connect themselves, the same three things always happen.</p><p><strong>Copies.</strong> Each consumer that needs data from another island builds a pipeline and lands a copy. The copy drifts. Within a year nobody knows which copy is current, and the storage bill has doubled for no analytical gain.</p><p><strong>Point to point sharing.</strong> With n islands and ad hoc sharing, the number of possible connections is n(n-1)/2. Seven islands means twenty-one connections, each with its own access model, its own monitoring, and its own owner who has since changed roles. The dashed amber lines in Figure 1 are what that looks like after three years.</p><p><strong>Shadow semantics.</strong> Every pipeline embeds a definition. The copy of revenue in the marketing account is revenue as the marketing engineer understood it on the day they wrote the job. Multiply by the number of pipelines and you have dozens of definitions of the same term, none of them documented, all of them feeding dashboards.</p><p>The architecture that follows is designed around eliminating those three failure modes while keeping the islands. It has four layers.</p><h3>3. Layer 1: Data islands keep their autonomy</h3><p>The first design decision is the most important one, and it is a decision not to do something. The islands keep their autonomy. Each island keeps its own account, its own roles, its own cost centre, its own release cadence, and its own local semantics.</p><p>This sounds like giving up. It is the opposite. The alternative, a single central platform that everyone migrates to, has been tried at every large company I know of, and the migration never finishes. What finishes is the budget. Meanwhile the islands that were supposed to disappear keep producing data, and the central platform becomes one more island with a bigger budget.</p><p>So the rule is: an island owns its data, its roles, and its local meaning. What it gives up is the right to connect to other islands however it likes. Every connection goes through the fabric.</p><p>A few practical consequences:</p><p>• <strong>Role model per island stays.</strong> The federated layer defines a role naming convention (something like &lt;DOMAIN&gt;_ETL, &lt;DOMAIN&gt;_DEVELOPER, each in DEV and PROD variants) but the island’s administrators still grant them.</p><p>• <strong>Local semantic views stay local.</strong> If the supply chain island has defined on-time delivery its own way for its own dashboards, that definition continues to work. Nobody rewrites it. It just does not become the enterprise definition until a steward says so.</p><ul><li><strong>The most sensitive island joins last, and isolated.</strong> In every estate there is one island whose owners are, for good reasons, more security conscious than everyone else: the one holding customer network telemetry, or patient data, or trading positions. Design for it from day one, but onboard it last, and onboard it in a mode where its data never leaves its account. More on that below.</li></ul><h3>4. Layer 2: The data fabric, hub and spoke, zero copy</h3><p>The fabric is the connection layer. Its job is to make data from any island available to any authorised consumer in any other island, without copying it, and with a bounded number of connections.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/900/1*msEdABqTtJcZ36U_h477dA.png" /><figcaption><em>Figure 2. The data fabric: hub and spoke, zero-copy, and three residency classes.</em></figcaption></figure><h3>Hub and spoke, not mesh-of-connections</h3><p>Every island connects to one hub account. The hub is an exchange layer: it holds the catalog of catalogs, the policy library, and views over what each island has shared. It holds no copies. Seven islands means seven connections instead of twenty-one, and when the eighth island arrives it means eight, not twenty-eight.</p><p>The hub lives in the region where most consumers are and where the regulatory position is simplest. In practice that is usually one of the EU regions of the primary cloud provider, but it is a decision to make deliberately and document.</p><h3>Zero-copy as the default</h3><p>The mechanics differ by platform but the principle is the same: the consumer sees the provider’s data in place, governed by the provider’s policies, without a pipeline.</p><p>On Snowflake this is secure data sharing within a region, listings with auto-fulfilment across regions and clouds, and cross-region replication only where residency classification allows it. On Databricks it is Delta Sharing, which also works across platforms. Where the two meet, or where an on-premises object store with a query engine such as Trino needs to participate, Apache Iceberg tables with a REST catalog give open table access across engines. We proved the Iceberg path from an on-premises object store into a cloud warehouse through an Iceberg REST catalog and it works well enough to be a standard pattern now, not an experiment.</p><p>The ERP deserves a special mention. Modern ERP platforms are beginning to offer bidirectional zero-copy integration with cloud data platforms, so that finance data can be read in place by analytics and analytical results can be read in place by the ERP. Where that is available, it replaces a generation of replication tools. Where it is not yet available, the replication tools stay, but the fabric treats the ERP as a spoke either way.</p><h3>Three residency classes from two tags</h3><p>The fabric needs a way to express where data may go. We use two boolean tags on every shared object:</p><p>• CAN_REPLICATE: may this data be copied to another region or cloud?</p><p>• CAN_PROCESS_REMOTE: may a query engine outside this island process this data, with results flowing back?</p><p>Together with a sensitivity level they produce three effective classes:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/926/1*f_52wu0zAH5XszdAFPYQsQ.png" /></figure><p>A sovereign enclave is the extreme case: it joins the fabric as a federated peer that only exchanges compliance-approved aggregates through a gate. It never becomes a spoke in the ordinary sense.</p><p>The two-tag model is worth the effort because it is reused by every layer above. The mesh uses it to decide where a data product may be built. The control plane uses it to decide where a query may run. And, as you will see, the semantic layer uses it to decide what context may leave an island.</p><h3>Governance as code</h3><p>The fabric’s policies, tag taxonomy, masking standard, row access patterns, and the mapping from tags to policies, live in a Git repository and are deployed by CI to every account, hub and islands alike. This is not optional. With more than a handful of accounts, any policy that is applied by hand will be applied inconsistently within a quarter. Enforcement happens twice: on the provider side, where the data lives, and at the consumption edge, where the consumer’s persona and purpose are checked.</p><h3>5. Layer 3: The mesh, domain-owned products moving through zones</h3><p>The fabric moves data. The mesh gives it shape. The mesh is where the data mesh principles (domain ownership, data as a product, self-serve platform, federated governance) are made concrete inside the hub account.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/900/1*mAbn1pvpXC7WhZmeK32IMw.png" /><figcaption><em>Figure 3. The mesh on top of the fabric.</em></figcaption></figure><h3>Zones</h3><p>Every domain’s data moves through the same five zones in the exchange layer:</p><p>1. <strong>RAW</strong>: the island’s share, as received. No transformation.</p><p>2. <strong>STAGE</strong>: typed, deduplicated, with technical keys resolved.</p><p>3. <strong>PREP</strong>: conformed to enterprise reference data, tagged, masked according to policy.</p><p>4. <strong>INTERFACE</strong>: the published data product. This is what consumers see. It has an owner, a contract, a semantic view, and verified queries.</p><p>5. <strong>COMPOUND</strong>: products built from several domains’ INTERFACE products. Customer 360. Supplier risk. Install base health.</p><p>The zone names do not matter. What matters is that every domain uses the same ones, so that a consumer who understands one domain’s product understands all of them, and so that policy can attach to a zone rather than to each object.</p><h3>Views, not copies</h3><p>The exchange layer stores nothing. Every object in every zone is a view over the island’s share, or a view over a view. This is what makes the hub safe to run: it cannot become a second system of record because it has no storage of its own. When an island changes its data, the change is visible everywhere immediately. When an island revokes a share, every dependent view breaks visibly rather than silently going stale.</p><p>The trade-off is query cost. A COMPOUND product that joins views from three islands across two clouds will push work to three places. The residency classes tell you whether that is even allowed; the control plane’s cost tracking tells you whether it is affordable. For the hot compound products, the answer is often a materialisation that the owning domain explicitly approves and pays for, which is a copy, but a governed one with an owner and a refresh contract. That is different in kind from a shadow pipeline.</p><h3>Domain ownership</h3><p>Each domain owns its roles, its products, and its zone objects. The platform team owns the zone structure, the policies, and the hub. The two meet at the INTERFACE zone: the domain decides what to publish and what it means; the platform decides how it is governed and shared. Compound products get an owner too, usually the consuming domain that asked for them.</p><h3>Who pays</h3><p>A mesh without chargeback becomes a tragedy of the commons within two quarters. Every query in the hub is tagged with the consuming persona and purpose, cost is attributed back to the consumer, and the producing domain gets to see who is reading its products. This is also the single best lever you have for getting domains to publish: once a domain sees that a hundred people from other units are reading its product, publishing stops being a favour and becomes a source of standing.</p><h3>6. Layer 4: The control plane</h3><p>Above the mesh sits the control plane. Some people call it the data plane or the data intelligence layer; the name matters less than what it does. It is the one surface that every consumer, human or agent, goes through, and the one place where the estate can be seen as a whole.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/900/1*bHcQEqYNfX7xWdXCo7dykw.png" /><figcaption><em>Figure 4. Four layers, and where governed context lands in each.</em></figcaption></figure><p>The control plane has a fixed set of capabilities:</p><p>• <strong>A catalog of catalogs with a heat map.</strong> Every island’s catalog is connected, so the control plane knows what exists everywhere, who owns it, what it is tagged, and how much it is used. The heat map, which products are read most, by whom, from where, is the single most useful screen in the estate for deciding what to invest in next.</p><p>• <strong>Identity and personas.</strong> Consumers authenticate through the corporate identity provider and are mapped to personas: analyst, data scientist, application, agent, steward, administrator. Each persona has a capability matrix.</p><p>• <strong>Purpose-based access control.</strong> Beyond role-based access, the purpose of the access is checked. The same person may read customer data for a service ticket and may not read it for marketing. Agents declare purpose per request.</p><p>• <strong>A policy library and governance gate.</strong> Policies are code, the library is the deployed state, and the gate is the check that a product is classified and certified before it is published or shared.</p><p>• <strong>Audit and lineage.</strong> Every access is logged with persona, purpose, policy hits, and lineage back to the island object. Platforms give you the raw material (access history, policy references, tag references); the control plane joins it into something a compliance officer can read.</p><p>• <strong>Observability and cost.</strong> Query performance, replication telemetry, and chargeback.</p><p>• <strong>Sharing surfaces for every kind of consumer.</strong> JDBC and ODBC for tools, REST for applications, an agent surface and an MCP endpoint for AI consumers, and a BI endpoint for the dashboard tools.</p><p>On Snowflake much of this is built with Horizon Catalog, the governance primitives, and a native application or a portal running in container services. On Databricks the equivalent foundation is Unity Catalog, with its lineage, tags, and cross-workspace governance. Both are capable. The control plane is the custom layer that makes them one thing across the estate.</p><p>And then there is the capability that for a long time was a line on the slide and nothing more: <strong>the shared semantic layer</strong>.</p><h3>7. The missing piece: context</h3><p>Here is the honest history. We built islands, fabric, mesh, and most of the control plane, and semantics were still rebuilt per project. Every new dashboard, every new agent, every new data science notebook started by rediscovering what the columns meant and what the metrics were. The control plane knew where everything was. It did not know what anything meant.</p><p>The reason is that curating semantics by hand does not scale. Snowflake’s own account of this, from the session mentioned earlier, was striking: an internal effort to hand-curate semantic models covered less than five percent of roughly ten thousand tables, and what was curated went stale as the schema moved. If a platform company with that much motivation could not keep up by hand, nobody can.</p><p>Two things changed in the last year or so that make a different approach possible.</p><h3>The context stack has become a product category</h3><p>Each major platform now ships a layered set of context capabilities. On Snowflake:</p><p>• <strong>Semantic Views</strong> declare metrics, dimensions, facts, joins, filters, verified queries, and synonyms as a first-class database object. Agents generate SQL from the view rather than from raw tables. A studio lets analysts edit them visually, and an autopilot drafts them from query history and BI models.</p><p>• <strong>Business Ontology</strong> (announced, arriving) is a governed place for what business concepts mean, who owns them, and how they relate, linked to the semantic views and tables that implement them. Stewards approve each definition, and the agentic surfaces are designed to use only approved definitions.</p><p>• <strong>Cortex Sense</strong> (in preview as I write) is a managed context runtime. It learns how the business works from schema, query history, dbt projects, BI models, documents, and connectors, keeps that context fresh, and serves it to agents. Crucially, the design intent is that agents prefer governed context and fall back to discovered context for everything else.</p><p>• <strong>Horizon Catalog</strong> underneath, with metadata connectors to dbt, the major BI tools, and external databases.</p><p>On Databricks the equivalent pieces are Unity Catalog metric views for governed metric definitions, Genie spaces for curated conversational context over a set of tables, the knowledge and agent tooling that sits on top, and Unity Catalog’s lineage and tagging as the governed base. The product names differ; the shape of the stack is the same: a governed catalog at the bottom, exact certified semantics in the middle, discovered context filling the gaps, and agentic surfaces consuming all three.</p><p>Neutral options exist too. dbt’s semantic layer, Cube, AtScale, and the emerging Open Semantic Interchange specification (an industry effort to make semantic definitions portable across platforms and tools) all address pieces of the same problem. If your estate is genuinely multi-platform, keep an eye on interchange formats; the definitions are the asset, and you want them to outlive any one vendor’s object model.</p><h3>The second change: discovered context is good enough to be a starting point</h3><p>The older generation of catalog tools inferred descriptions from column names and called it a day. The new context engines mine query history, which is a far richer signal. If two thousand queries in the last ninety days join orders to customers on a particular key and filter on status = ‘booked’, the engine can propose, with evidence, that booked order value is a thing, how it is computed, and who uses it. That proposal is not certified truth. But it is a draft a steward can review in minutes rather than a blank page an analyst has to fill in over weeks.</p><p>That changes the economics of the semantic layer completely. Instead of curating everything, you curate what the engine proposes, in order of importance, with a human in the loop. The rest of this article is about how to organise that.</p><h3>8. The context mesh: local meaning stays local, shared meaning is certified once</h3><p>The first design principle for the semantic layer is the same one that made the fabric work: federate. Context behaves like data.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/900/1*224MqLrlYinrRcLeAQE17w.png" /><figcaption><em>Figure 5. The context mesh.</em></figcaption></figure><p>Each island (each spoke in the fabric) keeps its own semantic views and its own discovered-context index. Those are local. The sales island’s definition of a pipeline stage is the sales island’s business. Nobody outside needs to agree with it, and nobody outside is allowed to change it.</p><p>The hub holds a <strong>universal business ontology</strong>: the set of terms the whole enterprise has agreed to mean one thing. Customer. Contract. Net revenue. Product. Site. Each entry in the ontology has an owner, an approval date, a residency class, and a link to the semantic view in the island that implements it. The hub holds the meaning, not the rows.</p><p>Two flows connect them.</p><p><strong>Up: candidates.</strong> The context engine in each island, and the semantic view autopilot on each island’s query history, surface terms that are used across domains, used often, or used inconsistently. These become candidate shared terms. They arrive at the hub with evidence attached: which queries, which dashboards, which models, how many users, and which conflicting definitions exist.</p><p><strong>Down: certified terms.</strong> Once a candidate has been certified, the ontology entry is published at the hub. Consumers at the hub resolve the term there and are routed to the island semantic view that computes it. The number is always computed where the data lives, by the view the island owns.</p><p>Why does this matter? Because the alternative, a central team that writes the enterprise semantic model, has the same failure mode as a central platform: it never finishes, and the islands keep moving. The context mesh gives the central function a bounded job (certify what is shared) and leaves the unbounded job (define everything) with the people who own the data.</p><h3>9. Trust tiers: every answer carries one</h3><p>The second principle: be honest about what is certified and what is not, and make the honesty machine-readable.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/900/1*tDhxkCJADHoUOToFtlgi3A.png" /><figcaption><em>Figure 6. Trust tiers.</em></figcaption></figure><p>Every piece of context carries a tier, and every answer inherits the lowest tier of the context it used.</p><p>• <strong>T0, Discovered.</strong> Context the engine inferred from schema, query history, dbt, BI models, and documents. Nobody has approved it. It is free, it is available on the first day the engine runs, and it covers the long tail.</p><p>• <strong>T1, Local certified.</strong> A semantic view owned by an island’s data product owner. The island stands behind it for its own purposes. This is what most domains already have today in some form.</p><p>• <strong>T2, Shared certified.</strong> An entry in the universal ontology, approved by a domain steward and (for cross-domain terms) by a semantic council, linked to the semantic view that implements it. This is the new asset the context mesh creates: one certified definition per shared business term.</p><p>• <strong>T3, Verified.</strong> Verified queries: known-good question and SQL pairs attached to a semantic view, for the numbers where a wrong answer gets escalated to the CFO.</p><p>The rule an agent follows is simple. For each term in a question, take the highest tier available. Fall back one tier at a time. Always show the tier in the answer.</p><p>Tiers are better than a single certified/uncertified flag for three reasons. They let you answer the long tail today, with discovered context, instead of waiting for curation. They make the gap visible: a heat map of questions answered at T0 is your curation backlog, ranked by usage. And they let different consumers set different bars: an exploratory notebook can accept T0 with a warning; a board report can refuse anything below T2.</p><h3>10. The lifecycle, and the human in the loop</h3><p>The third principle: automate discovery and drafting, keep humans for judgement, and make the human step a review rather than a project.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/900/1*iTr3WIvn_m_TXAwVFeS4Vg.png" /><figcaption><em>Figure 7. The context lifecycle.</em></figcaption></figure><p>Six steps.</p><p><strong>1. Discover (automated).</strong> The context engine mines every connected account: schema metadata, query history, dbt projects, BI datasets and measures, wikis and documents. It flags terms used across domains, terms used with conflicting logic, and terms used heavily with no semantic view behind them.</p><p><strong>2. Draft (automated).</strong> The autopilot drafts or extends a semantic view from real query history. The engine proposes an ontology entry with its evidence: a definition, synonyms, example queries, the dashboards that use it, the owners seen in usage, and a <strong>confidence score</strong>.</p><p><strong>3. Review (human, with a threshold).</strong> This is the step the animation at the top of the article is built around. Candidates arrive at a steward gate. Each carries the engine’s confidence, which reflects how consistently the sources agree, how heavily the term is used, and how well the draft performs against evaluation questions. The platform team sets a threshold, say 80 percent. Candidates at or above it are certified automatically, with an owner assigned and the decision logged, because asking a human to approve something all sources already agree on wastes the human. Candidates below it wait for the domain data steward, who sees the definition, the evidence, any conflicting definitions side by side, the implementing semantic view, and the proposed sensitivity and residency. The steward can approve, reject, ask the owner a question, or pick one of the conflicting definitions.</p><p><strong>4. Certify (human).</strong> For terms that cross domains, a semantic council (a small standing group with one representative per major domain) approves. Conflicts are settled here, once: one definition wins, the others become documented local variants or are retired. The council meets fortnightly and its decisions are recorded against the term.</p><p><strong>5. Publish (gate).</strong> The governance gate that already checks classification before data is shared now also checks certification before context is published. A term that passes goes live in the ontology and becomes visible to every agentic surface, BI tool, and external consumer.</p><p><strong>6. Monitor (automated).</strong> Evaluation sets run on a schedule. Drift alerts fire when the underlying schema, the semantic view, or the usage pattern changes. A failed evaluation or a user’s thumbs-down reopens the entry for review. Steward answers to the engine’s targeted questions are recorded, so the same question is not asked twice. This is the grounding loop: the human’s judgement feeds back into the discovered context, so T0 gets better over time without anyone curating it directly.</p><h3>On the threshold</h3><p>The threshold is a policy decision, not a technical one, and it is worth saying what it trades off. A high threshold (95 percent) means almost everything goes through a steward: maximum control, slow, and stewards become the bottleneck within weeks. A low threshold (60 percent) means most terms certify automatically: fast, and you will certify some things that are wrong. The right number depends on the domain. Finance will want it high. Marketing will accept it lower. The animation lets you drag the slider and watch how many terms end up waiting for a human; in the demo, at 80 percent, three of seven certify automatically and four go to the steward, one of which is rejected.</p><p>Whatever the number, two things must be true. Auto-certified terms still get an owner, and the decision is logged as automatic, so a human can find and reverse it. And the threshold applies per domain and per sensitivity level, never as one global number.</p><h3>Roles</h3><p>Four roles make this work, and none of them is new:</p><p>• <strong>Data product owner.</strong> Owns the island semantic view (T1) and its verified queries. Answers the steward’s questions about meaning and grain.</p><p>• <strong>Domain data steward.</strong> Reviews candidates below the threshold. Fixes meaning, owner, sensitivity, and residency. Writes the gold evaluation questions. Promotes to the council.</p><p>• <strong>Semantic council.</strong> Certifies shared terms (T2). Settles conflicts between domains. Retires duplicates.</p><p>• <strong>Platform team.</strong> Runs the gate, the evaluation schedule, drift monitoring, and the threshold. Enables the context engine and connectors per account.</p><p>The steward’s time is the scarce resource. Everything in the design is aimed at making the steward’s work a review of evidence, not authorship. Stewards do not write SQL. The engine brings the draft.</p><h3>11. Context residency: the same tags, applied to meaning</h3><p>The fourth principle: context carries the same residency and sensitivity tags as the data it describes.</p><p>This is where most semantic layer designs quietly break the law. A definition of a metric over regulated data, complete with sample values and example queries, is itself data about that data. If the underlying rows cannot leave a country, the sample values cannot either.</p><p>So the two-tag model from the fabric applies to ontology entries and semantic views:</p><p>• A <strong>Portable</strong> term’s full definition, sample values, verified queries, and synonyms are published to the hub ontology. Agents at the hub answer directly.</p><p>• An <strong>Anchored-Accessible</strong> term’s definition and owner are published; computation stays in the island’s semantic view; no sample values leave. Agents route the query to the island and receive the result.</p><p>• An <strong>Anchored-Isolated</strong> term appears at the hub as a name and an owner, nothing more. Its definition, values, and semantic view stay inside the island. Agents answer that term only inside the island, and the hub can tell a consumer that the term exists and who to ask.</p><p>Nothing new is invented here. The classification was done once, for the data, in the fabric. The semantic layer inherits it.</p><p>Four more controls apply at answer time, all of which the control plane already had: agents generate SQL from semantic views, so masking, row access, and projection policies apply unchanged where the data lives; purpose-based access checks the question against the persona; every answer is logged with tier, owner, policy hits, and lineage; and the region where the language model runs is confirmed per island before any agentic capability is enabled there. That last point is not a detail. For the most security-conscious island in the estate it is the first question their security team will ask, and “we will check” is not an acceptable answer.</p><h3>12. How a question travels</h3><p>Put the layers together and this is what happens when someone asks an agent a question.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/900/1*l2x9IbMX7VycQRkgDXRrTQ.png" /><figcaption><em>Figure 8. How a question travels, and what the provenance badge shows.</em></figcaption></figure><p>1. <strong>The consumer asks.</strong> An assistant in the enterprise’s chat tool, a BI tool, or an external agent calling over MCP. Identity and persona are resolved; purpose is checked.</p><p>2. <strong>Ontology lookup.</strong> Each business term in the question is resolved to one certified definition (T2) or, where no shared term exists, to a local semantic view (T1).</p><p>3. <strong>Route to the island.</strong> The query goes to the semantic view in the owning account. Where a term is not modelled at all, the context engine supplies discovered context (T0) and the answer is marked accordingly.</p><p>4. <strong>Policy at source.</strong> Masking, row access, and residency are enforced where the data lives. An Anchored-Isolated term is computed inside its island and only the result returns, if its class allows even that.</p><p>5. <strong>Answer with a provenance badge.</strong> The number, the tier, the owner, the approval date, and a link to lineage.</p><p>In the example in Figure 8, one question mixes four kinds of context. Net revenue resolves to the certified enterprise term and the finance semantic view in the sales island (T2). Customer A resolves to the Customer entity in the ontology, which links the ERP, CRM, and analytics keys. Sites upgraded has no shared term; it is answered by the operations island’s own semantic view, inside that island, because the data is Anchored-Isolated (T1). The fiscal quarter filter comes from a verified query (T3). The badge shows all four, so the reader knows which part of the answer is enterprise-certified and which part is a domain’s local view.</p><p>The consumers do not change. The chat assistant, the BI tool, and the external agent call the same control plane they called before. What changes is that the control plane now has meaning, not just location.</p><h3>13. Reading the animation</h3><p>If you scroll back to the top, the animation is a direct rendering of sections 7 through 12, with three example domains: Sales, Supply, and Network operations, plus a BI tool.</p><p>• <strong>Sources.</strong> Three analytics accounts and a BI workspace. Particles flow out of each as the context engine collects signals through catalog connectors: schema, query history, dbt models, lineage, DAX measures. The counters climb.</p><p>• <strong>Context engine.</strong> Collect, enrich, propose. It ends with seven candidate shared terms, each scored.</p><p>• <strong>Steward gate.</strong> One candidate at a time, with the confidence bar and the threshold marker. Booked order value (91 percent), on-time delivery rate (88 percent), and site availability (85 percent) certify automatically. Net revenue (64 percent) arrives with two conflicting definitions, Finance’s and Sales’, and waits for the steward to pick one. Supplier lead time (79 percent) just misses the threshold and waits. Churn (43 percent) comes from two dashboards that disagree, has no owner, and is rejected: it stays discovered context, and agents will warn when they use it. Spectrum utilisation (72 percent) is a network term, Anchored-Isolated, approved by the steward with its computation staying in the operations account.</p><p>• <strong>Universal semantics.</strong> The ontology fills up by domain, each term tagged with how it was certified.</p><p>• <strong>Agents.</strong> Before the gate, every agent answer is marked unverified. After it, the Sales, Supply, and Network agents answer with a certified badge showing terms, owner, and approval date. The executive assistant’s cross-domain question, which customers had late deliveries and a drop in site availability, can only be answered once both the Supply and Network terms are certified, and the animation makes that dependency visible.</p><p>In the interactive version you can drag the threshold and watch how many terms wait for the steward, and you can approve or reject candidates yourself.</p><h3>14. Sequencing: how to actually build this</h3><p>Everything above can be read as a target state. Here is the order that works, drawn from doing it.</p><p><strong>Hub first.</strong> Stand up the hub account, connect the catalogs, and get the two-tag classification deployed as code to every account. Nothing else is possible without this, and it is the least glamorous work in the program. Budget a quarter.</p><p><strong>Harden for the hardest island, then reuse the hardening.</strong> Your most security-conscious island will have requirements the others do not: restricted downloads, the platform’s web interface behind a corporate access gateway, network policies, their own audit. Meet those requirements in the hub before you try to onboard that island, and then make them the standard for every account. You will have to do the work anyway; doing it first turns a blocker into a blueprint.</p><p><strong>Onboard islands in order of readiness, not importance.</strong> The two or three islands with the cleanest data and the most eager owners go first. They become the reference. The most sensitive island goes last, in isolated mode, after the hardening is proven.</p><p><strong>Semantic views before ontology.</strong> Semantic views are generally available on every major platform today and they give you exact numbers immediately. Start with the two or three products that matter most (a Customer 360, the finance product) and let the autopilot draft them. The ontology, and the business ontology products that implement it, come when they are available on your platform. Until then a governed glossary in the catalog holds the certified terms, and migrating it later is a mechanical job.</p><p><strong>Enable the context engine on the hub first, per island later.</strong> The engine needs the hub’s cross-account view to find terms used in more than one place. Per-island indexes come once the island is onboarded and its language model hosting region is confirmed.</p><p><strong>Name stewards before you need them.</strong> The gate only works if someone is behind it. Four domains, four named stewards, one council, a fortnightly cadence. Agree the threshold per domain in that forum.</p><p><strong>Pilot with real customers of the data, not with the platform team.</strong> The thing that moves an island’s owners from “we will consider it” to “let us do it” is a consumer they care about getting a result they could not get before. Pick two or three pilot consumers whose success the island’s leadership will notice, and fast-track them. Influence follows value.</p><p><strong>Keep the committed scope minimal.</strong> Hardening, one or two master data domains, a pilot with two or three consumers, two or three live use cases, control plane phase one. Everything else is “if capacity and demand allow”. A roadmap that promises everything delivers the slide.</p><h3>15. Lessons from the field</h3><p>A few things I would tell myself two years ago.</p><p><strong>The hub and spoke argument has to be made in connections, not architecture.</strong> Seven versus twenty-one. Eight versus twenty-eight. Executives understand that in one slide. “Federated hub and spoke data fabric” does not land.</p><p><strong>Views-only is the single most important rule and the first one people will ask to break.</strong> The request will come as “just this one table, just for performance”. The answer is a governed materialisation with an owner and a refresh contract, not an exception.</p><p><strong>Residency classification is a one-time cost that pays four times.</strong> Fabric, mesh, control plane, and semantic layer all consume it. Do it properly, do it as code, and do it before the first share.</p><p><strong>Product maturity is a risk you manage, not a reason to wait.</strong> Managed context engines are in preview. Business ontology products are new. Semantic views are not; they carry the plan until the newer pieces land, and the design above does not depend on any one of them being ready on a given date.</p><p><strong>Stewards are the bottleneck, so design for their time.</strong> Review, not authorship. Evidence, not blank forms. A threshold that keeps the obvious cases away from them. A fortnightly council that decides and moves on.</p><p><strong>Consumers must show the tier.</strong> If the chat assistant hides the badge, none of this matters. Agree the rule with whoever owns the consuming surfaces: show the tier, prefer certified, warn on discovered. It is a one-line policy and it is the policy that makes the semantic layer real.</p><p><strong>The most sensitive island’s security team is your best ally.</strong> Their requirements, met in the hub, become the standard that makes every other island’s security team say yes faster.</p><h3>16. Where this is going</h3><p>The layers in this article are stable. Islands will keep existing, fabrics will keep connecting them, meshes will keep giving data shape, and control planes will keep being the one place everyone goes through. What is changing quickly is the top of the stack, where context lives.</p><p>Two developments are worth watching. The first is interchange: as semantic definitions become the asset, the industry is converging on portable formats so that a certified definition can be read by any platform, any BI tool, and any agent framework. If that lands, the universal ontology described here becomes a document the enterprise owns outright, rather than an object inside one vendor’s catalog. The second is the grounding loop: as engines get better at learning from steward decisions and failed evaluations, the share of context that needs a human will fall, and the threshold will rise. The human stays in the loop, but the loop gets shorter.</p><p>Neither development removes the need for the architecture. Both make it more valuable. An enterprise that has done the work of classifying its data, federating its islands, and certifying its shared meaning will be able to adopt whatever comes next in a quarter. One that has not will still be reconciling two definitions of revenue in a spreadsheet.</p><p>That is the whole argument. There is no right AI without the right data foundation, and the data foundation is not finished until the meaning is governed as carefully as the rows.</p><p><em>The interactive version of the animation, where you can set the threshold and approve or reject candidates yourself, is at the link below. Everything in it is example data.</em></p><p><em>Interactive demo: add the link here before publishing.</em></p><p><strong>Glossary</strong></p><p>• <strong>Data island</strong>: an analytics account or platform with its own ownership, residency, and local semantics.</p><p>• <strong>Data fabric</strong>: the connection layer; hub and spoke, zero-copy sharing, residency classes.</p><p>• <strong>Data mesh</strong>: domain-owned data products moving through standard zones on top of the fabric.</p><p>• <strong>Control plane</strong>: the surface every consumer goes through; catalog, identity, purpose-based access, policy, audit, cost, sharing endpoints, and the semantic layer.</p><p>• <strong>Semantic view</strong>: a governed, machine-readable definition of metrics, dimensions, joins, and filters for one domain.</p><p>• <strong>Universal business ontology</strong>: the set of enterprise-wide terms, each with one certified definition, an owner, an approval, and a link to its implementing semantic view.</p><p>• <strong>Context engine</strong>: a managed runtime that discovers context from schema, usage, models, and documents, and serves it to agents behind governed context.</p><p>• <strong>Trust tier</strong>: T0 discovered, T1 local certified, T2 shared certified, T3 verified.</p><p>• <strong>Provenance badge</strong>: the tier, owner, approval date, and lineage link returned with every answer.</p><p>• <strong>Residency classes</strong>: Portable, Anchored-Accessible, Anchored-Isolated, derived from the CAN_REPLICATE and CAN_PROCESS_REMOTE tags.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=942330467f17" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Apache Iceberg as the Foundation for a Multi-Cloud Federated Data Lake]]></title>
            <link>https://medium.com/@arihant.shashank01/apache-iceberg-as-the-foundation-for-a-multi-cloud-federated-data-lake-8c9e1ca6fe38?source=rss-72b159d37fef------2</link>
            <guid isPermaLink="false">https://medium.com/p/8c9e1ca6fe38</guid>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[apache-iceberg]]></category>
            <dc:creator><![CDATA[Arihant Shashank]]></dc:creator>
            <pubDate>Tue, 11 Mar 2025 14:13:47 GMT</pubDate>
            <atom:updated>2025-03-11T14:19:06.759Z</atom:updated>
            <content:encoded><![CDATA[<p><strong>Abstract</strong> Data lakes have become the cornerstone of modern data architecture, yet they often suffer from challenges like data fragmentation, schema evolution complexities, and performance bottlenecks. Apache Iceberg offers a powerful solution to these issues through its open table format, designed for scalability, data consistency, and compatibility with multiple compute engines. This white paper explores how Apache Iceberg can serve as the foundation for a multi-cloud federated data lake, with detailed insights on architecture design, data ingestion strategies, performance optimization techniques, and benchmarking studies. We will delve into advanced metadata management, cataloging techniques, and the interplay between Iceberg and leading cloud services such as AWS, Azure, and on-premises environments.</p><h3>1. Introduction: The Evolution of Data Architecture</h3><p>The evolution of data management systems has transformed from traditional MapReduce architectures to modern data lake frameworks. Each phase introduced new capabilities but also presented unique challenges:</p><ul><li><strong>MapReduce Era:</strong> Batch-oriented processing with limited scalability. Processing was slow, requiring multiple iterations to process large-scale data, making real-time analytics impossible.</li><li><strong>Hive Era:</strong> Enhanced query capabilities but complex maintenance. Query optimization was minimal, and latency was high for interactive queries.</li><li><strong>Spark Era:</strong> Improved performance but lacked a standardized table format for managing schema evolution and time travel. Spark could process data faster but required external solutions to manage schema evolution effectively.</li><li><strong>Iceberg Era:</strong> Solves these challenges by decoupling table storage from compute engines, ensuring scalability, atomic updates, and compatibility with popular query engines.</li></ul><p>Organizations have increasingly adopted a multi-cloud strategy to optimize performance and costs while ensuring business continuity. However, managing a federated data lake across multiple cloud providers introduces new complexities such as inconsistent schema management, governance challenges, and data silos. Apache Iceberg emerges as a robust solution that unifies fragmented data environments.</p><h3>2. Apache Iceberg: A Deep Dive</h3><h3>2.1 Key Architectural Concepts</h3><p>Apache Iceberg’s architecture is designed to address the limitations of traditional data lake storage formats like Apache Hive tables, Apache Hudi, and Delta Lake. It introduces several innovative concepts:</p><ul><li><strong>Metadata Layer:</strong> Tracks dataset versions, partitions, and snapshots without rewriting full datasets. Metadata is stored separately from data files, enabling efficient table operations such as schema evolution, partition pruning, and rollback.</li><li><strong>Snapshots &amp; Time Travel:</strong> Enables point-in-time data access for improved auditing and rollback capabilities. Unlike traditional data lakes that require complex and costly reprocessing for historical data, Iceberg maintains immutable snapshots that provide historical consistency.</li><li><strong>Partition Evolution:</strong> Allows dynamic partitioning without data rewrites. Traditional data lake tables require data migration when partitioning schemes change, but Iceberg’s partition evolution removes this constraint.</li><li><strong>Atomic Commit Model:</strong> Guarantees consistency across concurrent writes and updates. Iceberg’s transaction model ensures ACID compliance without locking tables.</li><li><strong>Hidden Partitioning:</strong> Removes the need for users to define partition columns explicitly, enabling more efficient queries by leveraging Iceberg’s metadata-based partition pruning.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/615/1*yCoRDaEnPhqXmZcf5_WpMw.png" /></figure><p>Detailed explanation of iceberg with file system and code in pyspark</p><p><a href="https://github.com/Arihantbigdata/tutorials/blob/master/Spark_icebergs.ipynb">https://github.com/Arihantbigdata/tutorials/blob/master/Spark_icebergs.ipynb</a></p><h3>2.2 Key Features for Federated Data Lakes</h3><ul><li><strong>Schema evolution without downtime:</strong> Iceberg supports adding, renaming, and removing columns without affecting existing data.</li><li><strong>Support for multiple compute engines:</strong> Compatible with Spark, Snowflake, Presto, Flink, and Trino, making it ideal for multi-cloud environments.</li><li><strong>Efficient query pruning using metadata layers:</strong> Iceberg’s metadata-based pruning significantly reduces scan times compared to traditional partition filtering.</li><li><strong>Optimized data compaction:</strong> Iceberg reduces small file problems by managing data compaction at the table level.</li></ul><h3>3. Multi-Cloud Federated Data Lake Architecture with Iceberg</h3><h3>3.1 Architecture Overview</h3><p>A federated data lake must balance data locality, cost, and governance while providing unified access across cloud providers. The architecture consists of:</p><ul><li><strong>Data Sources:</strong> AWS S3, Azure Blob Storage, Google Cloud Storage, On-Premises MapR Clusters.</li><li><strong>Ingestion Tools:</strong> PySpark, AWS Glue, Snowflake External Tables, Apache Flink.</li><li><strong>Iceberg Catalog Services:</strong> REST-based Iceberg catalog for centralized control, supporting AWS Glue, Hive Metastore, and Nessie.</li><li><strong>Query Engines:</strong> Snowflake for analytical queries, Spark for ETL workloads, Presto/Trino for ad-hoc analysis.</li></ul><h3>3.2 Data Flow Pipeline</h3><ol><li><strong>Ingestion Layer:</strong> PySpark pipelines ingest data from multiple sources into Iceberg tables.</li><li><strong>Metadata Management:</strong> Iceberg’s metadata layer ensures efficient schema evolution and query pruning.</li><li><strong>Query Layer:</strong> Snowflake’s support for Iceberg enables federated querying across AWS, Azure, and on-premises data sources.</li></ol><h3>4. Practical Implementation with PySpark and Iceberg</h3><h3>4.1 Sample PySpark Code for Iceberg Ingestion</h3><pre>from pyspark.sql import SparkSession<br>spark = SparkSession.builder \<br>    .appName(&quot;IcebergIngestion&quot;) \<br>    .config(&quot;spark.sql.catalog.my_catalog&quot;, &quot;org.apache.iceberg.spark.SparkCatalog&quot;) \<br>    .config(&quot;spark.sql.catalog.my_catalog.type&quot;, &quot;hive&quot;) \<br>    .getOrCreate()<br># Ingest data into Iceberg table<br>data = spark.read.format(&quot;csv&quot;).option(&quot;header&quot;, &quot;true&quot;).load(&quot;s3://source-data/events.csv&quot;)<br>data.writeTo(&quot;my_catalog.db.events&quot;).createOrReplace()</pre><h3>4.2 Schema Evolution with Iceberg</h3><p>Iceberg simplifies schema evolution with minimal disruption. Example changes include:</p><ul><li>Adding new columns.</li><li>Changing column data types.</li><li>Renaming columns without rewriting data.</li></ul><h3>5. Performance Benchmarking: Iceberg vs. Delta Lake vs. Hudi</h3><h3>5.1 Benchmark Setup</h3><ul><li>Data Size: 10 TB of IoT data.</li><li>Compute Engines: Snowflake, PySpark, Trino, and Flink.</li><li>Query Types: Aggregations, point lookups, and time travel.</li></ul><h3>5.2 Results Summary</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*J8RWd3D70cI-NeXN6KJVCg.png" /></figure><h3>6. Best Practices for Iceberg in Federated Data Lakes</h3><ul><li>Implement metadata compaction for optimal query performance.</li><li>Adopt partition evolution strategies to minimize data shuffling.</li><li>Use snapshot expiration policies to manage storage efficiently.</li><li>Ensure catalog redundancy to improve high availability in multi-cloud setups.</li></ul><h3>7. Summary of this post</h3><p>Apache Iceberg has emerged as a powerful solution for building scalable, flexible, and high-performance data lakes across multiple cloud platforms. Its seamless integration with PySpark and Snowflake positions it as an optimal choice for modern enterprises. By implementing Iceberg in a federated data lake architecture, organizations can achieve enhanced data governance, improved query performance, and simplified schema evolution.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=8c9e1ca6fe38" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Guide to Data Vault Modeling in Snowflake]]></title>
            <link>https://medium.com/@arihant.shashank01/a-comprehensive-guide-to-data-vault-modeling-in-snowflake-962d0fa9bd83?source=rss-72b159d37fef------2</link>
            <guid isPermaLink="false">https://medium.com/p/962d0fa9bd83</guid>
            <category><![CDATA[data-architecture]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[snowflake]]></category>
            <category><![CDATA[data-vault]]></category>
            <category><![CDATA[data]]></category>
            <dc:creator><![CDATA[Arihant Shashank]]></dc:creator>
            <pubDate>Tue, 03 Dec 2024 19:57:20 GMT</pubDate>
            <atom:updated>2024-12-04T16:06:44.562Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_J_V0SJp4zKqBIXKhuWYLQ.png" /><figcaption>Source: Copilot</figcaption></figure><h3>1. Introduction</h3><h4>What is Data Vault?</h4><p>Data Vault is a hybrid data modeling methodology that combines the best of <strong>3NF (Third Normal Form)</strong> and <strong>Star Schema</strong>. Developed by Dan Linstedt in the late 1990s, it is designed to address the challenges of agility and scalability in large-scale data warehousing projects.</p><p>Unlike traditional models, Data Vault emphasizes <strong>flexibility</strong> and <strong>auditability</strong>, making it highly suitable for modern data environments where change is constant.</p><p>Key benefits include:</p><ul><li><strong>Scalability</strong>: Easy to scale and adapt as business needs evolve.</li><li><strong>Auditability</strong>: Maintains historical data with traceable lineage.</li><li><strong>Agility</strong>: Parallel loading and modular structure accelerate development.</li></ul><h4>Why Snowflake?</h4><p>Snowflake’s <strong>cloud-native architecture</strong> and features align perfectly with the principles of Data Vault. Some highlights:</p><ul><li><strong>Elastic Scaling</strong>: Handle large volumes of data effortlessly.</li><li><strong>Zero-Copy Cloning</strong>: Experiment and version models without impacting production.</li><li><strong>Time Travel</strong>: Seamlessly track changes for audit and recovery.</li></ul><p>By combining Data Vault with Snowflake, businesses can achieve a future-proof, flexible data warehouse.</p><h3>2. Core Concepts of Data Vault 2.0</h3><h3>Basic Components</h3><ol><li><strong>Hubs</strong>: Centralized tables that store unique business keys (e.g., Customer_ID).</li><li><strong>Links</strong>: Capture relationships between Hubs (e.g., Customer-Product purchases).</li><li><strong>Satellites</strong>: Store descriptive attributes and track historical changes.</li></ol><h3>Why Modular Design?</h3><ul><li><strong>Independence</strong>: Changes to one component (e.g., a new Satellite) don’t disrupt others.</li><li><strong>Parallelism</strong>: Teams can work on different parts simultaneously.</li><li><strong>Adaptability</strong>: Easily add new data sources or expand existing structures.</li></ul><h3>Extensibility</h3><p>The modular nature of Data Vault allows for seamless integration of new business domains, data sources, and requirements.</p><h3>3. Setting Up a Data Vault in Snowflake</h3><h4>Step 1: Preparing Your Environment</h4><pre>CREATE DATABASE DataVault_DB;<br>CREATE WAREHOUSE DataVault_WH;<br>CREATE SCHEMA DataVault_Schema;</pre><h4>Step 2: Designing the Data Vault</h4><ol><li>Identify <strong>business keys</strong> for Hubs.</li><li>Determine <strong>relationships</strong> for Links.</li><li>Define <strong>attributes</strong> and their associated Hubs/Links for Satellites.</li></ol><h3>4. SQL-Driven Example: Building the Data Vault</h3><h4>4.1 Customer Hub</h4><p><strong>DDL for Hub and populating it:</strong></p><pre>CREATE TABLE Hub_Customer (<br>    Customer_Key VARCHAR PRIMARY KEY, -- Business Key<br>    Load_Datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- Metadata<br>    Record_Source VARCHAR -- Source system identifier<br>);<br><br>INSERT INTO Hub_Customer (Customer_Key, Load_Datetime, Record_Source)<br>SELECT DISTINCT Customer_ID, CURRENT_TIMESTAMP, &#39;CRM_System&#39;<br>FROM Raw_Customer_Data;</pre><h4>4.2 Product Hub (DDL for hub and inserting the data)</h4><pre>CREATE TABLE Hub_Product (<br>    Product_Key VARCHAR PRIMARY KEY,<br>    Load_Datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,<br>    Record_Source VARCHAR<br>);<br><br>INSERT INTO Hub_Product (Product_Key, Load_Datetime, Record_Source)<br>SELECT DISTINCT Product_ID, CURRENT_TIMESTAMP, &#39;ERP_System&#39;<br>FROM Raw_Product_Data;</pre><h4>4.3 Satellite for Customer Attributes</h4><pre>CREATE TABLE Sat_Customer_Details (<br>    Customer_Key VARCHAR,<br>    Effective_Start_Datetime TIMESTAMP,<br>    Effective_End_Datetime TIMESTAMP,<br>    Customer_Name VARCHAR,<br>    Customer_Email VARCHAR,<br>    Record_Hash VARCHAR,<br>    Load_Datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,<br>    Record_Source VARCHAR,<br>    PRIMARY KEY (Customer_Key, Effective_Start_Datetime)<br>);<br><br>INSERT INTO Sat_Customer_Details (<br>    Customer_Key, Effective_Start_Datetime, Effective_End_Datetime,<br>    Customer_Name, Customer_Email, Record_Hash, Load_Datetime, Record_Source<br>)<br>SELECT <br>    Customer_ID,<br>    CURRENT_TIMESTAMP,<br>    NULL, -- Open-ended effective end date<br>    Customer_Name,<br>    Customer_Email,<br>    MD5(Customer_ID || Customer_Name || Customer_Email), -- Change tracking<br>    CURRENT_TIMESTAMP,<br>    &#39;CRM_System&#39;<br>FROM Raw_Customer_Data;</pre><h4>4.4 Adding a Link Table: Customer-Product Purchases</h4><p><strong>Definition of Links:</strong> Links represent relationships between two or more Hubs. They typically include:</p><ul><li>The <strong>business keys</strong> from the related Hubs.</li><li>Metadata fields like load timestamps and source identifiers.</li><li>Optionally, hash keys for unique identification.</li></ul><p><strong>Use Case:</strong> In our example, we’ll create a Link_Customer_Product table to record which customers purchased which products.</p><pre>CREATE TABLE Link_Customer_Product (<br>    Link_Key VARCHAR PRIMARY KEY, -- Unique hash of the relationship<br>    Customer_Key VARCHAR, -- FK to Hub_Customer<br>    Product_Key VARCHAR, -- FK to Hub_Product<br>    Load_Datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- Metadata<br>    Record_Source VARCHAR -- Source system identifier<br>);<br><br>INSERT INTO Link_Customer_Product (<br>    Link_Key, Customer_Key, Product_Key, Load_Datetime, Record_Source<br>)<br>SELECT <br>    MD5(Customer_ID || Product_ID), -- Generate a unique hash for the relationship<br>    Customer_ID, <br>    Product_ID, <br>    CURRENT_TIMESTAMP, <br>    &#39;POS_System&#39; -- Source of this data<br>FROM Raw_Transaction_Data;</pre><p><strong>Explanation of the Code:</strong></p><ol><li><strong>Link_Key</strong>: A unique identifier generated using a hash function (e.g., MD5) on the concatenation of Customer_ID and Product_ID.</li><li><strong>Customer_Key and </strong><strong>Product_Key</strong>: Foreign keys referencing the Hub_Customer and Hub_Product tables.</li><li><strong>Load_Datetime</strong>: Captures the timestamp when the relationship was loaded into the warehouse.</li><li><strong>Record_Source</strong>: Tracks the origin of the data (e.g., a Point of Sale system).</li></ol><p>Query Example: Linking Customers to Purchased Products</p><pre>SELECT <br>    hc.Customer_Key,<br>    hc.Customer_Name,<br>    lp.Product_Key,<br>    lp.Load_Datetime<br>FROM Link_Customer_Product lp<br>JOIN Hub_Customer hc ON lp.Customer_Key = hc.Customer_Key<br>JOIN Hub_Product hp ON lp.Product_Key = hp.Product_Key;</pre><h3>5. Advantages of Using Data Vault in Snowflake</h3><ol><li><strong>Scalability</strong>: Snowflake’s compute elasticity aligns with Data Vault’s ability to handle large data volumes.</li><li><strong>Flexibility</strong>: The modular structure allows easy integration of new data sources.</li><li><strong>Auditability</strong>: Metadata fields like Load_Datetime and Record_Source ensure complete traceability.</li><li><strong>Parallel Development</strong>: Teams can develop and load Hubs, Links, and Satellites independently.</li></ol><h3>6. Advanced Concepts</h3><h4>Using Snowflake Streams and Tasks</h4><ul><li>Set up <strong>Streams</strong> to track changes in raw data:</li></ul><pre>CREATE STREAM Raw_Data_Changes ON TABLE Raw_Customer_Data;<br><br>--Automate loading using task<br><br>CREATE TASK Load_Hub_Customer<br>WAREHOUSE = DataVault_WH<br>SCHEDULE = &#39;5 MINUTE&#39;<br>AS<br>INSERT INTO Hub_Customer<br>SELECT DISTINCT Customer_ID, CURRENT_TIMESTAMP, &#39;CRM_System&#39;<br>FROM Raw_Data_Changes;</pre><h4>Zero-Copy Cloning for Testing</h4><pre>CREATE CLONE DataVault_Schema AS DataVault_Schema_Test;</pre><h3>7. Common Challenges and Solutions</h3><ul><li><strong>Late-Arriving Data</strong>: Use Satellites with effective start and end dates to handle out-of-sequence records.</li><li><strong>Source System Changes</strong>: Introduce new Hubs or Links to accommodate changes without impacting existing structures.</li><li><strong>Query Performance</strong>: Use Snowflake’s clustering keys and materialized views.</li></ul><h3>8. Real-World Use Case</h3><h3>Scenario: Marketing Analytics Platform</h3><p>A retail company implements a Data Vault to consolidate customer, product, and transaction data:</p><ul><li><strong>Hubs</strong>: Capture Customer_ID and Product_ID.</li><li><strong>Links</strong>: Store relationships between customers and purchased products.</li><li><strong>Satellites</strong>: Store detailed attributes like customer demographics and product descriptions.</li></ul><p>Results:</p><ul><li>Faster onboarding of new data sources.</li><li>Full traceability for compliance audits.</li><li>Improved scalability for growing data volumes.</li></ul><h3>9. Conclusion</h3><p>Data Vault, paired with Snowflake, is a powerful combination for building scalable, flexible, and auditable data warehouses. Its modular design and Snowflake’s cloud-native capabilities enable rapid development and adaptation to ever-changing business requirements.</p><p>Take the leap to implement Data Vault and future-proof your data warehouse for agility and growth!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=962d0fa9bd83" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Native & Custom Data Quality Monitoring in Snowflake]]></title>
            <link>https://medium.com/@arihant.shashank01/native-custom-data-quality-monitoring-in-snowflake-fcf002ec9afb?source=rss-72b159d37fef------2</link>
            <guid isPermaLink="false">https://medium.com/p/fcf002ec9afb</guid>
            <category><![CDATA[data-quality]]></category>
            <category><![CDATA[snowflake]]></category>
            <category><![CDATA[data-engineering]]></category>
            <dc:creator><![CDATA[Arihant Shashank]]></dc:creator>
            <pubDate>Thu, 30 May 2024 18:21:05 GMT</pubDate>
            <atom:updated>2024-05-31T09:27:27.600Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/866/1*f-LdfW37OCqMfdi1zYM6xw.png" /></figure><p>Ensuring data quality is crucial for any data-driven organization. Snowflake provides native capabilities to monitor and maintain high data quality standards within your data warehouse. Here’s how you can leverage Snowflake’s features to monitor data quality:</p><h4>Snowflake offers system Data Monitoring Functions (DMFs) within the CORE schema of the shared SNOWFLAKE database. These DMFs are designed and maintained by Snowflake, ensuring their consistency and reliability. Users are not permitted to modify the names or functionalities of these system DMFs.</h4><p>Each system DMF is tailored to measure a specific data quality attribute, enabling comprehensive monitoring and management of data quality. You can assign multiple DMFs to a table or view to achieve a more thorough assessment of data quality, addressing various governance and compliance requirements.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*fVm43qVt-xuyYGKIVEELmA.png" /><figcaption>Data Quality metrics (Native)</figcaption></figure><p>Let&#39;s do the hands</p><p><strong>Access control setup : </strong>Create the dq_tutorial_role role to use throughout the tutorial:</p><pre>USE ROLE ACCOUNTADMIN;<br>CREATE ROLE IF NOT EXISTS dq_tutorial_role;<br><br>-- Grant privileges, and grant the application role and database roles to the dq_tutorial_role:<br>GRANT CREATE DATABASE ON ACCOUNT TO ROLE dq_tutorial_role;<br>GRANT EXECUTE DATA METRIC FUNCTION ON ACCOUNT TO ROLE dq_tutorial_role;<br>GRANT APPLICATION ROLE SNOWFLAKE.DATA_QUALITY_MONITORING_VIEWER TO ROLE dq_tutorial_role;<br>GRANT DATABASE ROLE SNOWFLAKE.USAGE_VIEWER TO ROLE dq_tutorial_role;<br>GRANT DATABASE ROLE SNOWFLAKE.DATA_METRIC_USER TO ROLE dq_tutorial_role;<br><br>--Create a warehouse to query the table that contains the data and grant the USAGE privilege on the role to the dq_tutorial_role role:<br><br>CREATE WAREHOUSE IF NOT EXISTS dq_tutorial_wh;<br>GRANT USAGE ON WAREHOUSE dq_tutorial_wh TO ROLE dq_tutorial_role;<br><br>-- Confirm the grants to the dq_tutorial_role role:<br>SHOW GRANTS TO ROLE dq_tutorial_role;<br>-- Establish a role hierarchy and grant the role to a user who can complete this tutorial:<br>GRANT ROLE dq_tutorial_role TO ROLE SYSADMIN;<br>GRANT ROLE dq_tutorial_role TO USER jsmith;<br><br><br>Data setup <br>USE ROLE dq_tutorial_role;<br>CREATE DATABASE IF NOT EXISTS dq_tutorial_db;<br>CREATE SCHEMA IF NOT EXISTS sch;</pre><p><strong>Data Setup: </strong>To facilitate managing the data and the DMF for this tutorial, create a dedicated database to contain these objects:</p><pre>CREATE OR REPLACE TABLE Employees (<br>    EmpID INT PRIMARY KEY,<br>    Name VARCHAR(100),<br>    Age INT,<br>    Gender CHAR(1),<br>    EmailID VARCHAR(100),<br>    Location VARCHAR(100),<br>    TIME_FIELD TIMESTAMP_TZ<br>);<br><br>-- here if you can see i have done some intentional dupes in empid, wrong format email id,<br>-- and null in gender, null in age <br>INSERT INTO Employees (EmpID, Name, Age, Gender, EmailID, Location, TIME_FIELD) VALUES<br>(101, &#39;John Doe&#39;, 29, &#39;M&#39;, &#39;john.doe@&#39;, &#39;New York&#39;, &#39;2024-05-30 08:00:00&#39;),<br>(102, &#39;Jane Smith&#39;, 34, &#39;F&#39;, &#39;jane.smith@example.com&#39;, &#39;Los Angeles&#39;, &#39;2024-05-30 09:00:00&#39;),<br>(103, &#39;Sam Johnson&#39;, 41, &#39;M&#39;, &#39;sam.johnson@example.com&#39;, &#39;Chicago&#39;, &#39;2024-05-30 10:00:00&#39;),<br>(103, &#39;Emily Brown&#39;, 27, &#39;F&#39;, &#39;emily.brown@example.com&#39;, &#39;Houston&#39;, &#39;2024-05-30 11:00:00&#39;),<br>(105, &#39;Michael Green&#39;, NULL, &#39;M&#39;, &#39;michael.green@example.com&#39;, &#39;Phoenix&#39;, &#39;2024-05-30 12:00:00&#39;),<br>(105, &#39;Linda White&#39;, 30, &#39;F&#39;, &#39;linda.white@example.com&#39;, &#39;Philadelphia&#39;, &#39;2024-05-30 13:00:00&#39;),<br>(107, &#39;Robert Black&#39;, 45, &#39;M&#39;, &#39;robert.black@example.com&#39;, &#39;San Antonio&#39;, &#39;2024-05-30 14:00:00&#39;),<br>(108, &#39;Patricia Harris&#39;, 33, &#39;&#39;, &#39;patricia.harris@example.com&#39;, &#39;San Diego&#39;, &#39;2024-05-30 15:00:00&#39;),<br>(109, &#39;David Clark&#39;, 50, &#39;M&#39;, &#39;david.clark@example.com&#39;, &#39;Dallas&#39;, &#39;2024-05-30 16:00:00&#39;),<br>(110, &#39;Mary Lewis&#39;, 28, &#39;F&#39;, &#39;mary.lewis@example.com&#39;, &#39;San Jose&#39;, &#39;2024-05-30 17:00:00&#39;);</pre><p><strong>Create and work with Data metric functions: </strong>In the below box, you can see along with native DMF, I have created a custom DMF for validating emails id’s are in the correct format or wrong, and if wrong then it count, you can write N numbers of DMF similar like UDF</p><pre>--Scheduling <br>alter table Employees set DATA_METRIC_SCHEDULE = &#39;5 MINUTE&#39;;<br>-- DUPLICATE_COUNT<br>alter table Employees<br>add data metric function SNOWFLAKE.CORE.DUPLICATE_COUNT <br>on (EmpID);<br><br>-- NULL_COUNT<br>alter table EMPLOYEES<br>add data metric function SNOWFLAKE.CORE.NULL_COUNT<br>ON (Age);<br><br>-- FRESHNESS<br>alter table EMPLOYEES<br>add data metric function SNOWFLAKE.CORE.FRESHNESS<br>ON (TIME_FIELD);<br><br>-- ROW_COUNT <br><br>-- UNIQUE_COUNT <br>alter table EMPLOYEES<br>add data metric function SNOWFLAKE.CORE.UNIQUE_COUNT<br>on (EmpID);<br><br>-- CUSTOM_DATA_METRIC_FUNCTION<br>CREATE DATA METRIC FUNCTION IF NOT EXISTS<br>invalid_email_count (EMPLOYEES table(EMAILID STRING))<br>RETURNS NUMBER AS<br>&#39;SELECT COUNT_IF(FALSE = (EMAILID REGEXP &#39;&#39;^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$&#39;&#39;)) FROM EMPLOYEES&#39;;<br><br>ALTER TABLE EMPLOYEES ADD DATA METRIC FUNCTION<br>invalid_email_count ON (EmailID);</pre><h3><strong>Review your data metrics</strong></h3><p>To check all the Data quality metrics implemented on your data:</p><pre>SELECT * FROM TABLE(INFORMATION_SCHEMA.DATA_METRIC_FUNCTION_REFERENCES(<br>  REF_ENTITY_NAME =&gt; &#39;employees&#39;,<br>  REF_ENTITY_DOMAIN =&gt; &#39;TABLE&#39;));</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*hnbjfMnZSEVxo4ZFRNjqLg.png" /><figcaption>All the 5 DQM on the Employee table</figcaption></figure><ol><li>Custom DQM for email validation</li></ol><pre>SELECT scheduled_time, measurement_time, table_name, metric_name, value<br>FROM SNOWFLAKE.LOCAL.DATA_QUALITY_MONITORING_RESULTS <br>where table_name = &#39;EMPLOYEES&#39; order by measurement_time desc LIMIT 1;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*hJVrBqvplvDNGghVH1mFQQ.png" /></figure><p>2. DUPLICATE_COUNT on empid</p><pre>SELECT SNOWFLAKE.CORE.DUPLICATE_COUNT<br>(SELECT empid as NO_OF_DUPE_EMP FROM employees);</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Qfl1rNPDaEmn1DHTH6zI2g.png" /></figure><p>3. NULL_COUNT on Age</p><pre>select SNOWFLAKE.CORE.NULL_COUNT(SELECT AGE as NO_NULL_AGES FROM EMPLOYEES);</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*fLPSqjceFWdSGD0avStCdg.png" /></figure><p>4. UNIQUE_COUNT on Empid</p><pre>select SNOWFLAKE.CORE.UNIQUE_COUNT(SELECT empid as NO_unique_emp_id FROM EMPLOYEES);</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*7eCXQI7NgR_ybfq9OEdseQ.png" /></figure><p>Each system DMF is tailored to measure a specific data quality attribute, enabling comprehensive monitoring and management of data quality. You can assign multiple DMFs to a table or view to achieve a more thorough assessment of data quality, addressing various governance and compliance requirements.</p><p><strong>Clean up of the schedule of DQM</strong></p><pre>alter table EMPLOYEES unset DATA_METRIC_SCHEDULE;<br><br>alter table employees set DATA_METRIC_SCHEDULE = &#39;TRIGGER_ON_CHANGES&#39;;</pre><p><strong>Conclusion</strong>:</p><p><strong>After setting up and scheduling your data metric functions, you’ll likely aim to automate the monitoring process rather than manually checking data quality results regularly. Typically, you’ll establish thresholds for the outputs of these functions and trigger alerts if they’re exceeded.</strong></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=fcf002ec9afb" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Step-by-Step Guide to Setting Up Data Governance on Snowflake]]></title>
            <link>https://medium.com/@arihant.shashank01/step-by-step-guide-to-setting-up-data-governance-on-snowflake-daf7186d84c5?source=rss-72b159d37fef------2</link>
            <guid isPermaLink="false">https://medium.com/p/daf7186d84c5</guid>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[snowflak]]></category>
            <category><![CDATA[data-governance]]></category>
            <category><![CDATA[data]]></category>
            <dc:creator><![CDATA[Arihant Shashank]]></dc:creator>
            <pubDate>Fri, 08 Mar 2024 13:08:52 GMT</pubDate>
            <atom:updated>2024-03-08T13:17:23.516Z</atom:updated>
            <content:encoded><![CDATA[<p>These are very high level topics, we should deep dive in each, for that refer snowflake documentation step by step</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/604/1*hP1Du7WS3buRN8OaC5SKkQ.png" /></figure><h4>Step 1: Setting Up Role-Based Access Control (RBAC)</h4><ol><li>Create Roles: Start by defining the roles needed within your organization. Roles can represent different job functions or levels of access. For example, you might have roles for data analysts, data engineers, data stewards, and administrators.</li></ol><pre>CREATE ROLE analyst_role;<br>CREATE ROLE data_steward_role;</pre><p>2. Assign Privileges: Once roles are created, grant them appropriate privileges. This involves specifying what actions each role is allowed to perform on specific objects within Snowflake, such as databases, schemas, and tables.</p><pre>GRANT USAGE ON DATABASE my_database TO analyst_role;<br>GRANT SELECT ON ALL TABLES IN SCHEMA my_schema TO analyst_role;<br>GRANT USAGE, CREATE SCHEMA ON DATABASE my_database TO data_steward_role;</pre><p>3. Assign Users to Roles: Create user accounts for individuals who will be accessing Snowflake and assign them to the appropriate roles.</p><pre>CREATE USER analyst1 PASSWORD = &#39;password&#39;;<br>CREATE USER data_steward1 PASSWORD = &#39;password&#39;;<br>GRANT analyst_role TO USER analyst1;<br>GRANT data_steward_role TO USER data_steward1;</pre><h4>Step 2: Data Sharing</h4><ol><li>Enable Data Sharing: Snowflake allows for easy sharing of data between different accounts. Ensure that data sharing is enabled for your account by contacting Snowflake support if necessary</li><li>Share Data: Share specific databases, schemas, or tables with other Snowflake accounts. This can be useful for collaborating with external partners or providing data to subsidiary companies.</li></ol><pre>GRANT USAGE ON DATABASE shared_db TO ACCOUNT &#39;another_account&#39;;<br>GRANT SELECT ON ALL TABLES IN SCHEMA public TO ACCOUNT &#39;another_account&#39;;</pre><p>3. Access Control: Control access to shared data by specifying which users or roles from other accounts have access to the shared objects. This ensures that sensitive data is only accessible to authorized parties.</p><pre>GRANT SELECT ON TABLE my_table TO ROLE analyst_role;</pre><h4>Step 3: Data Masking</h4><ol><li>Define Masking Policies: Masking policies help protect sensitive data by dynamically masking it based on predefined rules. Define masking policies for columns containing sensitive information such as credit card numbers or social security numbers.</li></ol><pre>CREATE MASKING POLICY credit_card_masking_policy AS (val STRING) RETURNS STRING -&gt; CASE WHEN CURRENT_ROLE() = &#39;analyst_role&#39; THEN &#39;XXXX-XXXX-XXXX-&#39; || SUBSTRING(val, -4) ELSE val END;</pre><p>2. Apply Masking Policies: Apply the defined masking policies to relevant columns in your tables. This ensures that sensitive data is masked whenever it’s queried by users who don’t have the necessary privileges.</p><pre>ALTER TABLE my_table MODIFY COLUMN credit_card COLUMN POLICY credit_card_masking_policy;</pre><h4>Step 4: Data Retention Policies</h4><ol><li>Time Travel: Time travel allows you to access historical versions of your data within a specified retention period. Configure the default time travel retention for your database to meet your organization’s requirements.</li></ol><pre>ALTER DATABASE my_database SET DEFAULT_DML_RETENTION_TIME_IN_DAYS = 30;</pre><p>2. Fail-safe: Fail-safe ensures that dropped tables are not immediately deleted, allowing you to recover them within a specified retention period. Configure the default table retention period to ensure that dropped tables can be recovered if needed.</p><pre>ALTER DATABASE my_database SET DEFAULT_TABLE_RETENTION_TIME_IN_DAYS = 365;</pre><h4>Conclusion:</h4><p>Implementing robust data governance practices on Snowflake is crucial for ensuring data security, compliance, and usability. By following this detailed guide and customizing the configurations to fit your organization’s needs, you can effectively manage access to data, share data securely, protect sensitive information, and retain data appropriately.</p><p>Remember to regularly review and update your data governance policies to adapt to changing business requirements and compliance standards.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=daf7186d84c5" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Tag-based Masking in Snowflake]]></title>
            <link>https://medium.com/@arihant.shashank01/tag-based-masking-in-snowflake-c599054bd76a?source=rss-72b159d37fef------2</link>
            <guid isPermaLink="false">https://medium.com/p/c599054bd76a</guid>
            <category><![CDATA[snowflake]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[masking]]></category>
            <category><![CDATA[pii]]></category>
            <category><![CDATA[dataengineeringconsulting]]></category>
            <dc:creator><![CDATA[Arihant Shashank]]></dc:creator>
            <pubDate>Thu, 22 Feb 2024 09:04:50 GMT</pubDate>
            <atom:updated>2024-02-22T09:04:50.613Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*lKZZpUXSM1SnyH-X9sYn8Q.png" /><figcaption>Mask and protect your data</figcaption></figure><p>The tag-based masking policy within Snowflake integrates object tagging and masking policies, streamlining data protection efforts. Through the ALTER TAG command, users can directly apply a masking policy to a tag. When the data type of a column aligns with the specified type in the masking policy signature, the column automatically inherits the protection conditions outlined in the policy.</p><p>This approach simplifies data protection as it eliminates the need to manually apply masking policies to individual columns. Instead, users can set a tag-based masking policy at the database, schema, or table level.</p><p>Key points regarding tag-based masking policies:</p><ol><li>Policy Application: Use the ALTER TAG command to associate a masking policy with a specific tag.</li><li>Automated Protection: Columns tagged with a tag linked to a masking policy are automatically protected according to the policy’s conditions.</li><li>Generic Policies: It’s advisable to create generic masking policies for each supported data type in Snowflake (e.g., STRING, NUMBER, TIMESTAMP_LTZ). These policies typically grant authorized roles access to raw data while unauthorized roles view a fixed masked value.</li><li>Policy Conditions: Masking policy conditions can protect column data based on the policy assigned to the tag or the tag string value assigned to the column. The choice between these approaches depends on factors such as the preferences of the policy administrator, decisions made by tag administrators, and input from data stewards.</li></ol><p>By leveraging tag-based masking policies, organizations can streamline their data protection processes, ensuring sensitive information is appropriately masked with minimal manual intervention.</p><blockquote>Prerequisites.</blockquote><blockquote>1) You have a snowflake trial account. you can read this blog: <a href="https://docs.snowflake.com/en/user-guide/object-tagging">https://docs.snowflake.com/en/user-guide/object-tagging</a></blockquote><blockquote>2) you are aware of tags in Snowflake.</blockquote><blockquote>3) you are aware of the roles and access policies of Snowflake.</blockquote><blockquote>4) you are aware about masking and related concepts</blockquote><blockquote>Note: this post is inspired by <a href="https://www.snowflake.com/blog/protect-sensitive-data-tag-based-masking/">https://www.snowflake.com/blog/protect-sensitive-data-tag-based-masking/</a> , please refer this one before going below</blockquote><p>Let&#39;s get into hands-on</p><h4><strong><em>The agenda here is to understand the masking policy along with tags for roles</em></strong></h4><blockquote>use role accountadmin;</blockquote><blockquote>— using account admin role lets create a user say zeta</blockquote><blockquote>— I have created a role say zeta<br>CREATE USER zeta<br>PASSWORD = ‘put your password’;</blockquote><blockquote>— let’s create a role here.<br>create or replace role governance;</blockquote><blockquote>— lets add this role to the user zeta<br>grant role governance to user zeta;</blockquote><blockquote>— apply masking policy and tag creation policy to role governance <br>grant apply masking policy on account to role governance;<br>grant apply tag on account to role governance;</blockquote><blockquote>— whatever governance role is getting access automatically zeta user will be getting those permissions</blockquote><blockquote>// create a database finance_raw and grant the ownership of the database to governance.<br>create or replace database governance_db;<br>grant ownership on database governance_db to role governance;</blockquote><blockquote>create or replace database financial_data;<br>create schema financial_data.cards;</blockquote><blockquote>CREATE or replace TABLE customer_info (<br> name VARCHAR(50),<br> dob DATE,<br> address VARCHAR(100),<br> credit_card_number VARCHAR(19),<br> cvv VARCHAR(4),<br> bank_name VARCHAR(100),<br> phone_number VARCHAR(15),<br> email VARCHAR(100)<br>);</blockquote><blockquote>INSERT INTO customer_info (name, dob, address, credit_card_number, cvv, bank_name, phone_number, email) VALUES<br>(‘John Smith’, ‘1985–07–15’, ‘123 Main St, Anytown’, ‘1234 5678 9012 3456’, ‘123’, ‘ABC Bank’, ‘123–456–7890’, ‘<a href="/service/mailto:john@example.com">john@example.com</a>’),<br>(‘Jane Doe’, ‘1990–04–25’, ‘456 Elm St, Somewhere’, ‘9876 5432 1098 7654’, ‘456’, ‘XYZ Bank’, ‘234–567–8901’, ‘<a href="/service/mailto:jane@example.com">jane@example.com</a>’),<br>(‘Michael Johnson’, ‘1978–12–03’, ‘789 Oak St, Elsewhere’, ‘5678 1234 8765 4321’, ‘789’, ‘DEF Bank’, ‘345–678–9012’, ‘<a href="/service/mailto:michael@example.com">michael@example.com</a>’),<br>(‘Emily Davis’, ‘1989–09–12’, ‘321 Maple St, Anytown’, ‘2468 1357 8024 6802’, ‘135’, ‘GHI Bank’, ‘456–789–0123’, ‘<a href="/service/mailto:emily@example.com">emily@example.com</a>’),<br>(‘David Brown’, ‘1980–03–28’, ‘654 Pine St, Somewhere’, ‘8765 4321 5432 8765’, ‘246’, ‘JKL Bank’, ‘567–890–1234’, ‘<a href="/service/mailto:david@example.com">david@example.com</a>’);</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*qID4l80HRs0sUKhYC6uh5w.png" /><figcaption>sample select of the data which got loaded</figcaption></figure><blockquote>create role view_pii;<br>grant role view_pii to user zeta;</blockquote><blockquote>// grant appropriate access on the table to the pii_allowed role and public<br>grant usage on database FINANCIAL_DATA to role view_pii;<br>grant usage on schema FINANCIAL_DATA.CARDS to role view_pii;<br>grant select on table FINANCIAL_DATA.CARDS.customer_info to role view_pii;<br>grant usage on database FINANCIAL_DATA to role public;<br>grant usage on schema FINANCIAL_DATA.CARDS to role public;<br>grant select on table FINANCIAL_DATA.CARDS.customer_info to role public;</blockquote><blockquote>GRANT ROLE governance TO ROLE ACCOUNTADMIN;<br>use role governance;</blockquote><blockquote>// create the tag<br>USE database governance_db;<br>create schema governance_db.tags;<br>create tag governance_db.tags.pii_data;<br>create schema governance_db.policies;</blockquote><blockquote>create masking policy governance_db.policies.pii_mask_string as (data string) returns string<br>-&gt; <br>case<br>when is_role_in_session(‘view_pii’) then data<br>else ‘***masked***’<br>end;</blockquote><blockquote>alter tag governance_db.tags.pii_data set masking policy governance_db.policies.pii_mask_string;</blockquote><blockquote>use role governance;</blockquote><blockquote>alter table FINANCIAL_DATA.CARDS.CUSTOMER_INFO modify column email set tag governance_db.tags.pii_data=’EMAIL’;<br>alter table FINANCIAL_DATA.CARDS.CUSTOMER_INFO modify column CREDIT_CARD_NUMBER set tag data_governance.tags.pii_data=’CREDIT_CARD_NUMBER’;</blockquote><blockquote>use role accountadmin;<br>CREATE WAREHOUSE tiny_wh<br> WAREHOUSE_SIZE = ‘XSMALL’<br> WAREHOUSE_TYPE = ‘STANDARD’<br> AUTO_SUSPEND = 60<br> AUTO_RESUME = TRUE<br> MIN_CLUSTER_COUNT = 1<br> MAX_CLUSTER_COUNT = 2;</blockquote><blockquote>GRANT USAGE ON WAREHOUSE tiny_wh TO ROLE GOVERNANCE;<br>GRANT USAGE ON WAREHOUSE tiny_wh TO ROLE public;<br>GRANT USAGE ON WAREHOUSE tiny_wh TO ROLE view_pii;</blockquote><blockquote>use role governance;<br>use warehouse tiny_wh;<br>select * from FINANCIAL_DATA.CARDS.CUSTOMER_INFO;</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*IXxvYdWulBizhwPExvcZSA.png" /><figcaption>now you can see masked data</figcaption></figure><p>Now let&#39;s go login to zeta user and change the role to view_pii</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*eMnHIlWubYrGdy6saTiWfg.png" /><figcaption>this user zeta with view_pii role can see the data</figcaption></figure><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c599054bd76a" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[What are Dynamic Tables in Snowflake]]></title>
            <link>https://medium.com/@arihant.shashank01/what-are-dynamic-tables-in-snowflake-f3e02360db82?source=rss-72b159d37fef------2</link>
            <guid isPermaLink="false">https://medium.com/p/f3e02360db82</guid>
            <dc:creator><![CDATA[Arihant Shashank]]></dc:creator>
            <pubDate>Tue, 06 Feb 2024 09:05:48 GMT</pubDate>
            <atom:updated>2024-03-15T11:40:14.180Z</atom:updated>
            <content:encoded><![CDATA[<p>In <a href="https://www.linkedin.com/company/snowflake-computing/">Snowflake</a>: Choosing the Right Feature for Your Data Needs 🚀<br>Are you navigating the vast landscape of Snowflake’s features, unsure which tool best suits your data processing requirements? Let’s simplify it for you! Here in this Article, I am going to talk about Dynamic Tables.</p><p>When to use snowflake dynamic tables?</p><p>Dynamic Tables: Your go-to for sophisticated data transformations! Opt for Dynamic Tables when:<br>1) Constructing SQL-based transformation pipelines.<br>2) Implementing complex SQL operations like Joins, Aggregates, Window Functions, and more.<br>3) Building transformation pipelines instead of aggregations.<br>4) Requiring precise control over table refresh timing.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*uQSR6WT2dVb92ASHDEjrzw.png" /><figcaption>Dynamic tables architecture</figcaption></figure><p>Dynamic Tables represent an addition to Snowflake’s array of table types, offering a paradigm shift in data pipeline management. With Dynamic Tables, teams can harness the power of simple SQL statements to define data pipeline outcomes with precision. Unlike traditional tables, Dynamic Tables automatically refresh to reflect the latest data changes, focusing solely on updates since the previous refresh.</p><p>Snowflake seamlessly handles scheduling and orchestration in the background, eliminating the need for manual coordination. This automated approach allows teams to focus on extracting insights from their data, rather than grappling with the complexities of pipeline management.</p><p>In summary, Dynamic Tables empower teams to streamline data workflows effortlessly, providing unparalleled efficiency and simplicity in data pipeline management.</p><p><strong>Let&#39;s get into hands-on:</strong></p><p>Table 1 : student which has roll no and name of the student , inserting 20 records (our focus will be on Alice with Roll no: 1 )</p><pre>- using and creating warehouse, database and schema<br>use warehouse COMPUTE_WH;<br>create database arisha_2024;<br>use arisha_2024;<br>create schema dynamic_demo;<br>use schema dynamic_demo;<br>- Creating a table which has rollno , student name of a student<br>CREATE OR REPLACE TABLE STUDENTS(ROLL_NO INT, STUDENT_NAME VARCHAR);<br>- Inserting the data into STUDENTS table which we created above<br>INSERT INTO STUDENTS (ROLL_NO, STUDENT_NAME) VALUES<br>(1, &#39;Alice&#39;),<br>(2, &#39;Bob&#39;),<br>(3, &#39;Charlie&#39;),<br>(4, &#39;David&#39;),<br>(5, &#39;Eve&#39;),<br>(6, &#39;Frank&#39;),<br>(7, &#39;Grace&#39;),<br>(8, &#39;Henry&#39;),<br>(9, &#39;Ivy&#39;),<br>(10, &#39;Jack&#39;),<br>(11, &#39;Karen&#39;),<br>(12, &#39;Larry&#39;);<br><br>select * from STUDENTS limit 5;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/787/1*5kYVdadXI72Ro-jiUkc57w.png" /><figcaption>select * from students limit 5;</figcaption></figure><p>Creating table student_subject where roll no(for joining key will be used), subject_name will be subject’s name , subject_id will subject id for exam purpose, marks is the marks obtained for that student</p><pre>CREATE OR REPLACE TABLE STUDENT_SUBJECTS(<br>ROLL_NO NUMBER,<br>SUBJECT_NAME VARCHAR(50),<br>SUBJECT_ID VARCHAR(50),<br>MARKS INT<br>);<br>INSERT INTO STUDENT_SUBJECTS (ROLL_NO, SUBJECT_NAME, SUBJECT_ID, MARKS) VALUES<br>(1, &#39;Mathematics&#39;, &#39;MATH101&#39;, 80),<br>(1, &#39;Physics&#39;, &#39;PHYS101&#39;,20),<br>(1, &#39;Chemistry&#39;, &#39;CHEM101&#39;,40),<br>(2, &#39;Mathematics&#39;, &#39;MATH101&#39;,50),<br>(2, &#39;Biology&#39;, &#39;BIO101&#39;,10),<br>(3, &#39;Computer Science&#39;, &#39;COMP101&#39;,70),<br>(4, &#39;Physics&#39;, &#39;PHYS101&#39;,100),<br>(5, &#39;Mathematics&#39;, &#39;MATH101&#39;,40),<br>(5, &#39;English&#39;, &#39;ENG101&#39;,22),<br>(6, &#39;Chemistry&#39;, &#39;CHEM101&#39;,55),<br>(6, &#39;History&#39;, &#39;HIST101&#39;,89),<br>(7, &#39;Mathematics&#39;, &#39;MATH101&#39;,77),<br>(7, &#39;Geography&#39;, &#39;GEOG101&#39;,44),<br>(8, &#39;Physics&#39;, &#39;PHYS101&#39;,56),<br>(9, &#39;Mathematics&#39;, &#39;MATH101&#39;,89),<br>(9, &#39;Economics&#39;, &#39;ECON101&#39;,90),<br>(10, &#39;Biology&#39;, &#39;BIO101&#39;,88),<br>(11, &#39;Mathematics&#39;, &#39;MATH101&#39;,77),<br>(12, &#39;Physics&#39;, &#39;PHYS101&#39;,89),<br>(12, &#39;Chemistry&#39;, &#39;CHEM101&#39;,67);<br>select * from STUDENT_SUBJECTS;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*vWG3x0pEmA-U3wHvy1ANhQ.png" /><figcaption>select * from student_subjects limit 10;</figcaption></figure><p>Let&#39;s create the dynamic table where we will be joining the above two loaded tables STUDENTS and STUDENT_SUBJECTS on roll_no</p><p>Here while creating this below dynamic table DYNAMIC_STUDENT where i have kept TARGET_LAG is 60 sec (it is a target that Snowflake attempts to meet. Data in dynamic tables is refreshed as closely as possible within the target lag. However, target lag may be exceeded due to factors such as warehouse size, data size, query complexity, and similar factors.)</p><pre>CREATE OR REPLACE DYNAMIC TABLE DYNAMIC_STUDENT<br> TARGET_LAG = &#39;1 MINUTE&#39;<br> WAREHOUSE = COMPUTE_WH<br> AS<br> SELECT <br> A1.ROLL_NO,<br> A1.STUDENT_NAME, <br> A2.SUBJECT_NAME,<br> A2.SUBJECT_ID,<br> A2.MARKS<br> FROM STUDENTS A1, STUDENT_SUBJECTS A2<br> WHERE A1.ROLL_NO=A2.ROLL_NO<br> ORDER BY A1.ROLL_NO ;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*QQBQ9zXpU-5WCJDfXIxGgQ.png" /><figcaption>lets focus on student Alice where the subject is Physics and marks obtained is 20</figcaption></figure><p>UPDATE STUDENT_SUBJECTS<br>SET MARKS = ‘40’<br>WHERE ROLL_NO = 1 AND SUBJECT_NAME = ‘Physics’;</p><p>— Here I have updated the student_subjects table where roll no is 1 and the subject name is Physics.</p><p>Now let&#39;s query the dynamic table and look at weather the marks for Alice has been updated to 40 or not?</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ZAqMemKkoTTy22hMR8AZfQ.png" /><figcaption>here you can see the data has been updates to 40</figcaption></figure><p>Here we can see without even using any pipeline or stream or any scheduler we can see the data has been updated in the dynamic tables</p><p><strong>A <em>dynamic table</em> materializes the results of a query that you specify. Instead of creating a separate target table and writing code to transform and update the data in that table, you can define the target table as a dynamic table, and you can specify the SQL statement that performs the transformation. An automated process updates the materialized results automatically through regular </strong><a href="https://docs.snowflake.com/en/user-guide/dynamic-tables-refresh"><strong>refreshes</strong></a><strong>.</strong></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=f3e02360db82" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to reduce cost in snowflake?]]></title>
            <link>https://medium.com/@arihant.shashank01/how-to-reduce-cost-in-snowflake-6a1b8bbb37aa?source=rss-72b159d37fef------2</link>
            <guid isPermaLink="false">https://medium.com/p/6a1b8bbb37aa</guid>
            <category><![CDATA[snowflake]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[data-analytics]]></category>
            <dc:creator><![CDATA[Arihant Shashank]]></dc:creator>
            <pubDate>Tue, 16 Jan 2024 11:01:14 GMT</pubDate>
            <atom:updated>2024-01-16T11:10:51.993Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*LSFy-Xkr7pTKunigNK21Xg.png" /><figcaption>general costing</figcaption></figure><p>There are three regions where Snowflake can charge you</p><ol><li><strong>Compute</strong>: This commonly constitutes the majority of your Snowflake spending. The costs tied to processing are intricately connected to both the duration and capacity of the active warehouses. With warehouses available in sizes ranging from x-small to 6X-large, the pricing escalates with each tier, doubling as you move up the scale. Efficiently navigating and selecting the most suitable warehouse size stands out as a crucial stride in the direction of optimizing costs within the Snowflake framework.</li><li><strong>Storage</strong>: Typically more economical compared to computational costs, the charges for data storage are contingent on the volume of data stored across tables, clones, and diverse regions. To enhance the optimization of Snowflake storage costs, engage in a proactive strategy that involves routinely reviewing and eliminating unnecessary or outdated data.</li><li><strong>Data Transfer</strong>: Incurred when relocating data between distinct Snowflake regions or external cloud services, data transfer costs necessitate strategic planning. Streamlining and minimizing unnecessary data transfers can be a pivotal strategy in the pursuit of optimizing these associated costs.</li></ol><p>We will mostly talk about how to reduce the computation cost in Snowflake and I have identified 7 ways that can help you to significantly reduce the computation cost on Snowflake.</p><h3><strong>1. Select the best-suited warehouse size.</strong></h3><p>Rethinking Snowflake warehouse expenses involves dispelling the notion that a larger warehouse always translates to more efficient <br>computing operations. Contrary to common belief, scaling down your warehouse size might not sacrifice performance and <br>could be a viable strategy for cost reduction. Each warehouse size provides a distinct number of nodes for query execution, and smaller queries may not require the full array of nodes, leading to excess resource expenses. To pinpoint the most cost-effective warehouse size tailored to your needs, <br>experimentation with different sizes is paramount. <br>A strategic starting point involves initiating trials with smaller sizes like X-Small or Small, gradually refining your approach based on observed outcomes. Through iterative testing, you can identify the warehouse size that strikes the optimal balance between cost and performance for your specific use case.</p><p>ALTER WAREHOUSE &lt;your_warehouse_name&gt; SET WAREHOUSE_SIZE = ‘&lt;desired_size&gt;’;</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*c0NfkdoI42U3_KCisUirpw.png" /></figure><p>Substitute &lt;your_warehouse_name&gt; with your warehouse’s name and &lt;desired_size&gt; with the preferred size</p><p>(e.g., X-Small, Small). Continuously assess performance and cost implications post-adjustment, making further refinements based on your ongoing experimentation and workload characteristics.</p><h3><strong>2. Always set the auto-suspend period of Warehouses to smaller time frames:</strong></h3><p>To curtail Snowflake expenses, consider fine-tuning the auto-suspend duration for your data warehouses. The auto-suspend feature intelligently puts data warehouses into a dormant state after a period of inactivity, effectively reducing compute costs. While this feature is automatically activated, the default setting is a substantial 10 minutes (600 seconds). This extended auto-suspend interval often results in increased charges attributed to idle compute credits.<br>Upon identifying warehouses with minimal usage and notable idle credits, <br>it’s imperative to scrutinize and potentially revise their auto-suspend duration. Typically, setting the limit to the shortest feasible duration, such as 60 seconds, is advisable. To make adjustments to the warehouse auto-suspend period, navigate to the Edit Warehouses tab and specify the desired time limit for Auto Suspend. Proactively modifying this setting can yield significant cost savings by mitigating charges associated with idle compute time.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/756/1*zbknIyFPakpVGQooGElnKg.png" /><figcaption>Only the account admin can do this</figcaption></figure><h3><strong>3</strong>.<strong> Change the query timeout values:</strong></h3><p>Snowflake comes with a default query timeout setting of 48 hours (172800 seconds), implying that any queries surpassing this duration will be automatically terminated. Adjusting the query timeout value presents an opportunity to mitigate Snowflake costs by averting the overconsumption of compute resources due <br>to prolonged query execution. To implement this optimization, <br>consider reviewing and updating the query timeout setting. This can be particularly beneficial in <br>preventing extended query runtimes from unnecessarily tying up compute resources and incurring additional charges.</p><p>Take note of the potential impact on your specific use case, <br>and carefully assess the balance between query performance and cost implications <br>when fine-tuning the query timeout value. Regular monitoring and adjustment can help strike the right <br>equilibrium for optimal efficiency and cost-effectiveness in your Snowflake environment.</p><h3>4.<strong> Optimize your queries</strong></h3><p>Enhance your query performance in Snowflake by optimizing your SQL queries. This involves refining query structure, indexing, and utilizing appropriate functions, ensuring efficient data retrieval and processing for the best overall performance.</p><blockquote>a) avoid using select * , instead select the columns and get the data displayed</blockquote><blockquote>b) use Explain command to troubleshoot the query performance etc</blockquote><blockquote>c) Always partition the large tables</blockquote><blockquote>d) Utilize effective compression methods to trim storage needs and accelerate query processing times. Employing these techniques not only reduces storage requirements but also enhances the speed of querying operations, leading to improved overall system performance.</blockquote><blockquote>e) Employ materialized views to store precomputed outcomes, mitigating the need for redundant processing time.</blockquote><blockquote>f) Make use of <a href="https://docs.snowflake.com/en/user-guide/ui-query-profile">Snowflake’s Query Profile tool</a>. Query profile tools can identify problems like, <a href="https://docs.snowflake.com/en/user-guide/ui-query-profile.html#exploding-joins">Exploding joins</a>, <a href="https://docs.snowflake.com/en/user-guide/ui-query-profile.html#queries-too-large-to-fit-in-memory">Queries that are too large to fit in memory</a> and <a href="https://docs.snowflake.com/en/user-guide/ui-query-profile.html#inefficient-pruning">Inefficient pruning</a>.</blockquote><blockquote>g) Leverage well-suited clustering keys to group similar data together</blockquote><h3><strong>5. Delete the unused tables of snowflake</strong></h3><p>One highly impactful approach to optimize Snowflake costs involves the deletion of redundant and unused data tables. This action can markedly liberate storage space, resulting in a substantial reduction in overall Snowflake expenses. However, apprehension among data engineers may arise, rooted in uncertainties about future data needs and a lack of clarity regarding the relevance of stored data. Consequently, data engineers may accumulate extensive amounts of unused or new information, ultimately leading to escalating Snowflake storage costs. To circumvent this challenge, regular table cleanup is essential.</p><h3><strong>6. Avoid loading large files using copy-into commands:</strong></h3><p>Typically, when faced with a substantial volume of data to migrate or transfer to the Snowflake data warehouse, there’s a tendency to use large CSV or semi-structured data files for loading into respective tables. However, it’s advisable to avoid uploading hefty files directly onto Snowflake. A more efficient strategy is to fragment a single sizable file into multiple smaller chunks and employ the COPY command for loading. Snowflake optimizes the COPY command by distributing the workload across parallel threads, facilitating the concurrent loading of multiple files. This approach not only reduces the computing load on the virtual warehouse but also translates to lower Snowflake credit expenditure compared to loading a single, massive file.</p><h3><strong>7. Zero Copy cloning in Snowflake:</strong></h3><p>Exploring Snowflake’s unique functionality, cloning emerges as a powerful tool with diverse applications. In the realm of data warehousing projects encompassing distinct environments like DEV, UAT, and PROD, cloning facilitates the seamless transfer of entire tables, schemas, or databases from PROD to UAT for testing purposes that demand the most up-to-date data.</p><p>Notably, the cloning process exclusively generates metadata for the cloned table, linking it to the same storage as the original. This intentional design ensures that cloning doesn’t contribute to additional storage expenses. Beyond its utility in UAT scenarios, the cloning feature exhibits versatility, catering to various use cases where replicating data for multiple purposes is essential.</p><blockquote><strong>By adhering to these techniques we can easily bring down the computation cost to 30–40% less in our snowflake ecosystem.</strong></blockquote><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=6a1b8bbb37aa" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>