<?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 Siva Ilango on Medium]]></title>
        <description><![CDATA[Stories by Siva Ilango on Medium]]></description>
        <link>https://medium.com/@sivailango.s?source=rss-539ddb1225b9------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*iFv65H4maZBXxaMrMDXcyA.jpeg</url>
            <title>Stories by Siva Ilango on Medium</title>
            <link>https://medium.com/@sivailango.s?source=rss-539ddb1225b9------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sat, 21 Nov 2026 21:52:38 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@sivailango.s/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[Principles of Data layers in Data Platform]]></title>
            <link>https://medium.com/@sivailango.s/principles-of-data-layers-in-data-platform-a336a0ff9e1e?source=rss-539ddb1225b9------2</link>
            <guid isPermaLink="false">https://medium.com/p/a336a0ff9e1e</guid>
            <category><![CDATA[data-architecture]]></category>
            <category><![CDATA[data-modeling]]></category>
            <dc:creator><![CDATA[Siva Ilango]]></dc:creator>
            <pubDate>Sat, 09 Sep 2023 20:46:42 GMT</pubDate>
            <atom:updated>2025-03-08T09:46:00.046Z</atom:updated>
            <content:encoded><![CDATA[<p>Data organizing principles are vital when we build the data platform to enable data maturity for the business.</p><p>Data management is one of the key aspects of the data platform&#39;s build. Most of the time, analysts build their own transformation logic and build reports after accessing the data from the platform. As a result, data is siloed.</p><p>Data layers help to ensure that the data is organized in a way that is</p><ul><li>consistent</li><li>reliable</li><li>reduced data redundancy</li><li>agility</li><li>and accessible.</li></ul><p>This makes it easier for businesses to use data to make informed decisions.</p><h3>Data layers: deep dive</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*DLLogWqmw-gkrTpD2lTKqg.png" /><figcaption>Data Layers</figcaption></figure><h3>Analogy</h3><p>Throughout this blog, we use the analogy of how restaurants serve food to their customers after they purchase ingredients.</p><blockquote><strong>Medallion architecture — Does it matter?</strong> Not at all. Technically Bronze, Silver and Gold layer do not have distinct features to leverage. It is the data design pattern introduced by Databricks and we use it for naming convention which can be easily understandable. We introduced the Platinum layer!</blockquote><h3>Staging</h3><p>The staging layer is the first level of data storage on a data platform. It stores data in a format that is closely aligned with the data structure of the operational system. The staging layer is divided into two sublayers: the raw layer and the standardized layer.</p><h4>Raw</h4><ul><li>Data from source systems is loaded into the raw layer in the destination data warehouse. The destination can be Snowflake, Google BigQuery, AWS RedShift, Delta Lake, or AWS S3.</li><li>ELT tools such as Fivetran, Stitchdata, Airbyte, and custom connectors can be used to load the data from the source systems into the destination.</li><li>The raw layer should be treated with extra care to ensure that no users or processes modify the schema and table structure. The data engineering team should own the raw layer since its state is managed by ELT tools in the modern data stack.</li><li>Data is loaded to the raw layer as-is, with additional metadata attributes such as data_loaded_at and file_name</li><li>Each source system’s data should have its own schema in the raw layer for auditing purposes. Schema names should follow the following naming convention: &lt;SOURCE_SYSTEM_NAME&gt;_&lt;BRAND_NAME&gt;</li><li>The table name in the raw layer should maintain the source system table name.</li></ul><blockquote><strong>Analogy</strong></blockquote><blockquote>As a first step to preparing the food in restaurants, we need to collect the ingredients from suppliers. It is similar to collecting data from operational systems. This is where <strong>data contracts</strong> play a critical role in ensuring,</blockquote><blockquote>1) Arrival of data as expected on the platform. Any availability, or accessibility issues in the operational system impact the timely delivery of data to businesses (consumers).</blockquote><blockquote>2) Both parties need to agree on how data (ingredients) can be agreed upon between two parties’ data producer and the platform team (supplier and restaurants) on an SLA.</blockquote><blockquote>3) Changes to the structure of the data (changes to the ingredients) should be notified to the platform team (restaurants). Otherwise restaurants cannot make the food as per menu</blockquote><h4>Standardised</h4><p>Similar to raw layer with following definitions,</p><ul><li><strong>Right data type: </strong>ELT tools do not always store columns in the right data type when they load data. We had challenges with a few ELT tools that loaded the data into VARCHAR in your destination.</li><li><strong>Meta-data first: </strong>Tables and attributes in your destination should have a description to enable analysts to easily discover and access the data. When you use the data catalog tool, meta data can be easily sourced from your destination. Also, meta data should be centrally managed in your warehouse. Metadata can also be used to classify your data into PII categories and more. Definitions for the data need to be defined by the data owner in the business, working with the data governance team.</li><li><strong>Manage history: </strong>There are instances where you cannot maintain the history of changes from your sources in the raw layer due to the lack of a way to identify the delta and the limitations of your data pipeline. It is recommended to keep the history of changes as early as possible in the data layer.</li><li><strong>Subset of data (optional):</strong> Unless there is a need for the data to be used by analysts to build ad-hoc analyses, there is no need to bring the data into standardised layer.</li></ul><blockquote><strong>Analogy</strong></blockquote><blockquote>Once the ingredients (data) is landed into restaurants from the data engineers. data should be governed and well organised in the inventory with additional meta data i.e., <strong>when ingredients loaded, units, time of arrival and right level of logical group</strong> for cooking team (analytical engineering) to consume the ingredients to make the tasty food!</blockquote><h3>Core</h3><p>The core layer is the transition layer used to integrate data from multiple sources into conformed entities.</p><h4>Prep</h4><p>Subset of the core layer, which is the integration layer to model the data into a consistent structure.</p><p>What exactly does this layer do?</p><ul><li>identify the attributes from multiple data sources and map them to consistent names</li><li>identify the business keys</li><li>union all the data into single model</li><li>apply universal cleansing rules</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*KmWuWFTzkRw4HelW5OUTxw.png" /><figcaption>Prep layer</figcaption></figure><blockquote><strong>Analogy</strong></blockquote><blockquote>Before cooking, kitchen porter prepares ingredients including cutting the vegetables, peeling the apple and grating the cheese. Then they will keep the ingredients ready for recipes</blockquote><blockquote><strong>Do we need Data Vault?</strong></blockquote><blockquote>Data vault definitely helps to organise the data using hub, link and satellite for large number of systems and enterprise data. If you are a less matured &amp; small data analytics team who is beginning the data journey, you don’t need to adopt to data vault pattern. Please keep it in your parking lot list to revisit in future.</blockquote><h4>Conformed</h4><p>Business rules and complex transformations are applied at the conformed layer to build the enterprise data domains. Conformed entities are commonly understood and used across organizations.</p><p>Data sets will be treated as master data and a single source of truth. It is the entry point for businesses to access the enterprise view of their data.</p><blockquote><strong>Analogy</strong></blockquote><blockquote>Chefs are now ready to cook the food to serve to the customers. It is the key role in the restaurants and there will be different chefs to make the recipes. Similarly in the data platform, analytical engineering build the models to serve the data with the help of departmental analysts.</blockquote><blockquote><strong>Reference data</strong></blockquote><blockquote>How do fit the <strong>reference data</strong> in the data layers? Reference data is the small set of data set which enriches the data. Sometimes this can be static mapping tables which is created for analytical purpose. We recommend to keep the reference data in core layer and use them to build the conformed layer.</blockquote><h3>Presentation</h3><p>Business intelligence analysts and data scientist teams access data products and consumption-ready datasets to create dashboards and insights.</p><h4>Data mart</h4><p>Star schema model to store the data in fact and dimension tables. Follows the Kimball methodology, which is optimized for analytical purposes.</p><p>Dimensional modelling allows the business analyst to build the visualisation rapidly to serve the insights to “C-suite” executives and stakeholders</p><blockquote><strong>Analogy</strong></blockquote><blockquote>Once the recipes are ready, next step is to serve the food. Data mart is similar to buffet for the customers to serve themselves. All the recipes are placed in structured and logical group for the consumers to serve themselves. Data is well structured and documented in data mart layer to build the insights quickly.</blockquote><h4>Report</h4><p>Datasets are created from the data mart layer and sometimes from the staging or conformed layer, which is completely denormalized.</p><ul><li><strong>Pre-joined and Pre-aggregated datasets: </strong>Executives can access the data directly and no joins are required to create the insights</li></ul><blockquote><strong>Analogy</strong></blockquote><blockquote>It is the final stage where servers (data analysts) from restaurant serves the desired food to the customers (executives and stakeholders). Data analysts build the insights for the executives in the business who can just access the insights to make the decision.</blockquote><h3>How can we make the data platform a success?</h3><ul><li><strong>Continual sessions </strong>with analysts and business required to make them understand the data layers and its rationale</li><li><strong>Monitoring usage history </strong>allows data platform team to measure how frequently data is accessed, frequently used dataset and long-running queries to make pro-active steps to improve the performance of platform</li><li><strong>Agility </strong>enables the business to trust in the foundation of the data platform, which should be sustainable, scalable and reliable</li><li><strong>Continual feedback and iteration</strong> help the data platform team identify the challenges from data analyst team and conduct sessions if required to increase the usage of data platform</li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=a336a0ff9e1e" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Snowflake: Role-based access control framework design]]></title>
            <link>https://medium.com/@sivailango.s/snowflake-role-based-access-control-framework-design-3cf8c068dae7?source=rss-539ddb1225b9------2</link>
            <guid isPermaLink="false">https://medium.com/p/3cf8c068dae7</guid>
            <category><![CDATA[snowflake]]></category>
            <category><![CDATA[snowflake-data-cloud]]></category>
            <category><![CDATA[snowflake-computing]]></category>
            <dc:creator><![CDATA[Siva Ilango]]></dc:creator>
            <pubDate>Mon, 04 Sep 2023 10:38:42 GMT</pubDate>
            <atom:updated>2023-09-15T20:51:17.409Z</atom:updated>
            <content:encoded><![CDATA[<p>Managing the access to data in Snowflake is quite a tedious maintenance activity when platform usage grows, and it takes engineering effort to manage the access control for new analysts when they are onboarded.</p><p>As usage of the platform increases in organizations, the data team focuses on delivering data products and self-service to the analyst team to surface the data quickly and easily.</p><p>This post comprehends the design of role-based access control to implement in Snowflake from our experience, and it is proven that it fulfills our requirements to manage the Snowflake roles.</p><h4>Types of roles</h4><p>Snowflake supports the role hierarchy; roles can be granted to other roles without limitation. It enables the data platform team to provide granular privileges to roles and makes the roles re-usable.</p><p>To manage the roles in a flexible way, following logical group roles is appropriate and manageable.</p><ol><li>Object Role (OR)</li><li>Functional Role (FR)</li><li>User role (UR)</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*LlbjU1kL9_9jWzZeLIEowQ.png" /><figcaption>Role Hierarchy</figcaption></figure><h4>Object role</h4><p>Object roles are used to provide access to Snowflake objects such as <strong>databases, schemas, tables, views</strong>, etc. Object roles are helpful for managing access to data.</p><p>Object roles are classified into two broader categories:</p><ul><li>Reader role</li><li>Writer role</li></ul><p>As Snowflake supports role hierarchy, the reader role will be granted to the writer role to inherit the read-level privileges and avoid privilege overlaps between the reader and writer roles.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*V0MRTU3GCqk-UONDs4hQlA.png" /><figcaption>Object role privileges</figcaption></figure><p>The benefits of object roles are</p><ul><li>Granular-level access to objects</li><li>Remove the privilege overlap between reader and writer roles</li></ul><h4>Best practices</h4><ul><li>Create the database-level roles by default</li><li>Create the schema, table or view-level roles when required</li></ul><h4>Naming convention</h4><pre>OR_&lt;OBJECT_TYPE&gt;_&lt;PRIVILEGE_LEVEL&gt;_&lt;FULL_QUALIFIED_OBJECT_NAME&gt;</pre><ul><li>OBJECT_TYPE: Possible values are DB, SHM, TBL, VW</li><li>PRIVILEGE_LEVEL: R = Reader, W = Writer</li></ul><p><strong>Example roles</strong></p><ul><li>OR_DB_R_ANALYTICS_PRESENTATION</li><li>OR_DB_W_ANALYTICS_PRESENTATION</li><li>OR_SHM_W_ANALYTICS_PRESENTATION_REPORT</li><li>OR_TBL_T_ANALYTICS_PRESENTATION_REPORT_ORDERS</li></ul><h4>Functional role</h4><p>Set of privileges assigned to departments, functional units, or geographical locations in the business.</p><p>Functional roles are logically grouped and mapped to the different streams in the organization.</p><h4>Best practices</h4><ul><li>Can be assigned to other functional roles and user roles</li></ul><h4>Naming convention</h4><pre>FR_&lt;ROLE_NAME&gt;</pre><p><strong>Example roles</strong></p><ul><li>FR_SALES_ANALYST</li><li>FR_SALES_ANALYST_EMEA</li><li>FR_COMMERCIAL_ANALYST</li><li>FR_PRODUCT_ANALYST</li></ul><blockquote>Can functional role granted to another functional role? <strong>Yes, In reality — Finance team can have an access to Sales and Finance data.</strong></blockquote><h4>User role</h4><p>Each user will have their <strong>own role</strong> to enable them to query the data across multiple functional roles. One of the challenges in our early days with Snowflake was that users needed to change their roles often to query the data using functional roles. Functional roles are granted to user roles.</p><p>Being pragmatic, there will be requirements to grant access to objects and special privileges to individuals.</p><p>To summarize the benefits,</p><ul><li>Users does not need to change roles to query the data</li><li>To query the data granted to functional roles without switching roles</li><li>To cross-join the dataset from functional roles</li><li>Sandbox-environment access</li></ul><blockquote>Few BI tools currently does not support to switch the roles. <strong>Users no need to remember the funcitonal role name assigned to users to access the data</strong></blockquote><h4>Best practices</h4><ul><li>Each user should have a dedicated role, which should be default role for user</li><li>Role names should have first and last name</li><li>User roles should not be granted to multi-roles</li><li>Each user will have their own schema in the sandbox database with full access</li></ul><blockquote>Sandbox is the database in Snowflake we create for analysts to build tables and views for ad-hoc &amp; exploratory analysis</blockquote><h4>Naming convention</h4><pre>UR_&lt;FIRST_NAME&gt;_&lt;LAST_NAME&gt;</pre><p><strong>Example roles</strong></p><ul><li>UR_JOHN_DOE</li><li>UR_ROSE_GABLER</li></ul><blockquote>Which role should have access to the Snowflake warehouse? <strong>User roles. When we assign the Snowflake warehouse to the user role, users can use sandbox as play ground to explore the Snowflake and no need to wait to grant the functional role.</strong></blockquote><blockquote>To which role the default Snowflake roles (SYSADMIN, SECURITYADMIN) should be granted? <strong>User roles</strong></blockquote><h3>Illustrative role hierarchy</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*sA8wVe6y03jWgqj8ItsaPA.png" /><figcaption>Illustrative role hierarchy</figcaption></figure><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=3cf8c068dae7" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>