Guidewire Best Practices study Guide
For
From
Infometry Inc
39111 Paseo Padre Pkwy, Ste 218
Fremont, CA, 94538
Tel: (510) 793 5859
,1. Introduction
1.1. Document Purpose
The purpose of the document is helpful for Snowflake basic command and Reference
1.2. Audience and Ownership
BI and Support Team.
The intended audience for the BI Naming Conventions is the ETL Developers, Tableau Developers, BI
project and Support team. The intended audience should understand from this document how their
development naming requirements will be met.
2. References
1.1. Glossary of Terms
Term / Acronym Definition
EDW Enterprise Data Warehouse
EDL Enterprise Data Lake
IDW Integration Data Warehouse
CDM common data model
1.1. Related Documents
Document Name Description
Size_dateType_Snowflake Size , Object Details
Information of SQL
Server
Snowflake Best Practices
, 3. Snowflake Conventions
1.2. Meta Data Table
The below specified tables are available in EDW. AUDIT schema
No. Meta Data Tables Comments
1 SQLSERVER_OBJECT_INFO SQL Server Object Information
2 SQLSERVER_TABLE_INFO SQL Server Table Count
3 SP_ERROR_LOG Stored Procedure Error Log
4 ETL_CONTROL_PARAM ETL Control Parameter
5 ETL_AUDIT_LOG ETL Audit Log
For Example:
To know number of views and corresponding snowflake database schema
select * from EDW.AUDIT.sqlserver_object_info
where databasename ='EDW'
and schemaname='ORCL'
and object_type='VIEW'
QA test Query to verify the records count information
EDL Salesforce
with b as (select * from edw.audit.sqlserver_table_info
where databasename = 'SFDC'
and schemaname='STG')
select distinct a.table_schema,a.table_name,a.row_count snow-
flake_count,b.numberofrecords sqlserver_count, a.row_count-b.numberofrecords
from (select table_schema,table_name,row_count from
"EDL"."INFORMATION_SCHEMA"."TABLES"
where table_schema= 'SALESFORCE') a, b
where trim(b.tablename)=a.table_name
and b.snf_schemaname=a.table_schema
and a.row_count <> b.numberofrecords
IDW Stage
with b as (select * from edw.audit.sqlserver_table_info
where databasename = 'IDH_INTG'
Snowflake Best Practices
For
From
Infometry Inc
39111 Paseo Padre Pkwy, Ste 218
Fremont, CA, 94538
Tel: (510) 793 5859
,1. Introduction
1.1. Document Purpose
The purpose of the document is helpful for Snowflake basic command and Reference
1.2. Audience and Ownership
BI and Support Team.
The intended audience for the BI Naming Conventions is the ETL Developers, Tableau Developers, BI
project and Support team. The intended audience should understand from this document how their
development naming requirements will be met.
2. References
1.1. Glossary of Terms
Term / Acronym Definition
EDW Enterprise Data Warehouse
EDL Enterprise Data Lake
IDW Integration Data Warehouse
CDM common data model
1.1. Related Documents
Document Name Description
Size_dateType_Snowflake Size , Object Details
Information of SQL
Server
Snowflake Best Practices
, 3. Snowflake Conventions
1.2. Meta Data Table
The below specified tables are available in EDW. AUDIT schema
No. Meta Data Tables Comments
1 SQLSERVER_OBJECT_INFO SQL Server Object Information
2 SQLSERVER_TABLE_INFO SQL Server Table Count
3 SP_ERROR_LOG Stored Procedure Error Log
4 ETL_CONTROL_PARAM ETL Control Parameter
5 ETL_AUDIT_LOG ETL Audit Log
For Example:
To know number of views and corresponding snowflake database schema
select * from EDW.AUDIT.sqlserver_object_info
where databasename ='EDW'
and schemaname='ORCL'
and object_type='VIEW'
QA test Query to verify the records count information
EDL Salesforce
with b as (select * from edw.audit.sqlserver_table_info
where databasename = 'SFDC'
and schemaname='STG')
select distinct a.table_schema,a.table_name,a.row_count snow-
flake_count,b.numberofrecords sqlserver_count, a.row_count-b.numberofrecords
from (select table_schema,table_name,row_count from
"EDL"."INFORMATION_SCHEMA"."TABLES"
where table_schema= 'SALESFORCE') a, b
where trim(b.tablename)=a.table_name
and b.snf_schemaname=a.table_schema
and a.row_count <> b.numberofrecords
IDW Stage
with b as (select * from edw.audit.sqlserver_table_info
where databasename = 'IDH_INTG'
Snowflake Best Practices
