Estou projetando um sistema Staging+NDS+DDS Data Warehouse, onde um ETL irá normalizar os dados [Staging]e carregá-los em [NDS], que irá armazenar todo o histórico.
Eu praticamente terminei o script T-SQL que vai criar as tabelas e restrições no [NDS]banco de dados, que contém as tabelas Master e Transactional , que vão alimentar respectivamente as tabelas [DDS] Dimension e Fact no que pretendo ser um esquema em estrela.
Eu me dei as seguintes regras a seguir:
- As dimensões de fornecimento de tabelas
[DDS]são prefixadas comDWD_ - Os fatos de fornecimento de tabelas
[DDS]são prefixados comDWF_ - As colunas de chave estrangeira são prefixadas com
DWK_ - A coluna de chave substituta é prefixada com o mesmo prefixo da tabela. O que significa que a chave substituta é sempre:
DWD_KeyparaDWD_mesa ouDWF_Keypara umaDWF_mesa.
- As colunas de controle são prefixadas com o mesmo prefixo da tabela. Por exemplo...
- A
DWD_Customerstabela possui colunas de controle:DWD_IsLastImageDWD_EffectiveFromDWD_EffectiveToDWD_DateInsertedDWD_DateUpdatedDWD_DateDeleted
- A
DWF_InvoiceHeaderstabela possui colunas de controle:DWF_DateInsertedDWF_DateUpdatedDWF_DateDeleted
- A
- As chaves primárias (/chaves substitutas) são sempre prefixadas com
PK_seguido pelo nome da tabela (incluindo o prefixo da tabela) - por exemploPK_DWD_Customers, ePK_DWF_InvoiceHeaders. - Eu também adicionei uma
uniquerestrição nas chaves naturais , e essas são sempre prefixadas comNK_seguidas pelo nome da tabela (incluindo o prefixo da tabela) - por exemploNK_DWD_Customers, eNK_DWF_InvoiceHeaders. - As colunas de chave estrangeira são sempre prefixadas com
DWK_seguido pelo nome da tabela referenciada (sem seu prefixo) e a palavra "Chave" - por exemplo,DWK_CustomerKey. - As restrições de chave estrangeira são sempre nomeadas
FK_[ParentTableNameWithPrefix]_[ChildTableNameWithPrefix]. - Quando uma tabela possui vários FKs na mesma tabela, o nome da coluna FK é anexado ao nome da restrição, por exemplo,
FK_DWD_FiscalCalendar_DWF_OrderDetails_DeliveryDate.
Todas as colunas prefixadas não têm significado comercial e nunca devem aparecer nas exibições; isso me deixa com um design bastante limpo e consistente, e create tablescripts parecidos com este:
create table DWD_SubCategories (
DWD_Key int not null identity(1,1)
,DWD_DateInserted datetime not null
,DWD_DateUpdated datetime null
,DWK_CategoryKey int not null
,Code nvarchar(5) not null
,Name nvarchar(50) not null
,constraint PK_DWD_SubCategories primary key clustered (DWD_Key asc)
,constraint NK_DWD_SubCategories unique (Code)
);
Então, minha pergunta é: há algo que eu deva saber (ou desaprender ) antes de continuar e implementar o ETL para carregar dados neste banco de dados? Alguém herdando este banco de dados iria querer me perseguir e arrancar minha cabeça no futuro? O que devo mudar para evitar isso? A razão pela qual estou perguntando sobre prefixos é porque estou usando DWDe DWF, mas as tabelas não são tecnicamente tabelas de "dimensão" e "fato". Isso é confuso?
Além disso, não tenho certeza sobre o conceito de chave natural - estou correto ao presumir que deve ser uma combinação única de colunas que o sistema de origem pode considerar suas colunas "chave", que posso usar no processo ETL para localizar, digamos , um registro específico para atualizar?
Sempre há pelo menos algo mais que você também deve saber e quase igualmente, sempre algo mais que você deve conscientemente colocar um fim. Especificamente no contexto de data warehousing, que é um setor relativamente incipiente, alavancando tecnologias relativamente novas.
Em relação ao que vi no mundo real, entrar em uma empresa pela primeira vez e ver o que entendo sobre seu design seria genuinamente indutor de lágrimas: lágrimas de alegria e alívio. Desde o início, você está no caminho certo para começar o que parece ser um sistema ETL/data warehousing bem pensado (bem projetado). Assim como na implementação de qualquer produto de software, sua milhagem pode variar conforme a solução cresce e é consumida pela empresa, mas, fundamentalmente, você está no caminho certo™ (e sim, você sabe o que é uma chave natural ).
Descobri que existem vários desafios com esse tipo de solução, que abordarei para reforçar algumas de suas decisões e talvez dar algumas dicas sobre o caminho à sua frente. Em primeiro lugar, o número de vezes que me encontrei em uma situação difícil por conta de um desenvolvedor (mesmo colegas administradores de banco de dados/profissionais de dados) entendendo mal o contexto de uma coluna de controle (usando, por exemplo, executando um processo contra a
DateInsertedcoluna, um mero tempo carimbo de inserção, sobre oDateReceivedou coluna com nome semelhante, com a intenção de relacionar uma linha a uma data específica de ocorrência ), embora eu concorde completamente com os cuidados que @Aaron Bertrand levanta, sinto que os prefixos para suas colunas de controle podem realmente ser aproveitados como uma espécie de sinalizador para ajudar a evitar seu uso indevido. Óbvio deve ser óbvio, é claro, mas assim como escrever código em geral, explícito é preferível. Dito isso, eu quase certamente deixaria esses prefixos fora dos índices e tal (provavelmente atéPKos tipos de chaves podem e devem permanecer na minha opinião, mas, a menos que haja uma ameaça realDWD_SubCategorieseDWF_SubCategoriesexistente no mesmo esquema, eles realmente são apenas fofos) . Acho válida a preocupação com os prefixosDWDe , mas eles estarão morando noDWF[NDS]catálogo e serviria para indicar a intenção, tornando perfeitamente aceitável usar a nomenclatura dessa maneira.O segundo (e talvez o mais irritante) desafio é treinar seus colegas de trabalho. Toda a engenharia de software, sinalizadores de uso e regras de prática de design são completamente inúteis se seus colegas que lutam por salário acima da excelência se envolverem e fizerem menos do que o melhor (ou, para ser justo, estão simplesmente tendo um mau dia ). Tenha em mente que grandes projetos geralmente têm muitos dedos na panela, então é imperativo que esses dedos estejam se comportando bem.
A última coisa que abordarei aqui é sempre ter em mente o valor real de qualquer sistema ETL para uma empresa. No paradigma Extrair, Transformar e Carregar, a primeira e a última letra não têm absolutamente nenhum valor comercial; ser feito na fase de Transformação, portanto, você desejará automatizar as etapas E e L o máximo possível para que possa se concentrar em tornar (e manter) sua solução valiosa para a unidade de negócios trabalhando ativamente nas transformações.
Dito isso, só tive a oportunidade de trabalhar em um punhado de soluções de armazenamento diferentes, então talvez um usuário mais experiente possa intervir e tirar meu pé da boca se eu precisar de correção. Como disse inicialmente, esta é uma daquelas áreas onde sempre se pode aprender ou desaprender algo, e eu não sou absolutamente exceção.
Ah, mais uma coisa (e provavelmente a mais importante) - Teste Unitário! Depois que seu E e L estiverem funcionando conforme o esperado e você tiver a oportunidade de colocar alguns domínios em sua solução T, peça a alguém para examinar os resultados. Se forem bons, salve o conjunto de resultados em algum lugar, para que, quando fizer alterações (e você fará, sem dúvida), possa garantir que não quebrou algo em outro lugar. Mais uma vez, automatize esse processo o máximo que puder (é outro processo de valor 0 para o negócio, até que eles fiquem sem ele pelo menos;)). Eu geralmente configuro um esquema ou catálogo separado para essa finalidade.
Espero que um pouco do que eu disse seja útil para você!
Como uma atualização, a separação de esquema de @Aaron Bertrand parece ser uma boa maneira de evitar prefixos desnecessários também, então certamente considere isso (eu sei que vou haha).